| plsql [message #370057] |
Thu, 07 December 2000 23:14  |
sujathaThogachedu
Messages: 3
Registered: December 2000
|
Junior Member |
|
|
I have a simple Employee table that contains three fields: Emp ID, Last Name and First Name. The table’s primary key is Emp ID
Sample Employee Table:
EMP_ID LAST_NAME FIRST_NAME
1 Smith Janet
2 Jones Jim
3 Johnson Mike
4 Carlos Frank
5 Brown Jerry
Also, I have a simple Family_Member table that contains: Member ID, Emp ID, Last Name, First Name, Relationship. The table’s primary key is Member ID. This table contains the name of an employee’s family members, if any. If the employee has no family members, there is no entry in this table
Sample Family Member Table:
MEMBER_ID EMP_ID LAST_NAME FIRST_NAME RELATIONSHIP
1 1 Smith Mike Spouse
2 1 Smith Jennifer Daughter
3 4 Carlos Maria Spouse
4 4 Carlos Michael Son
5 4 Carlos Sonya Daughter
6 5 Griggs Jane Spouse
7 5 Griggs Mary Daughter
How to write a single select statement to retrieve all employees and their spouses, if any. Be sure to include employees who do not have any family members. (See sample select output below).
Emp_ID Emp Last Name Emp First Name Spouse Last Name Spouse First Name
1 Smith Janet Smith Mike
2 Jones Jim
3 Johnson Mike
4 Carlos Frank Carlos Maria
5 Brown Jerry Griggs (or Brown) Jane
|
|
|
|
| Re: plsql [message #370063 is a reply to message #370057] |
Fri, 08 December 2000 05:16  |
Cynic
Messages: 2
Registered: December 2000
|
Junior Member |
|
|
This is the 3rd time this question has appeared on this board in 2 days, always from different people.
Have you lot got a project to do for a course or something?
|
|
|
|
| Re: plsql [message #370139 is a reply to message #370057] |
Wed, 20 December 2000 06:10 |
Kannan
Messages: 29
Registered: September 2000
|
Junior Member |
|
|
use the same sql and replace the columns and table name according to ur requirement.
Example :
Dept table
DNO DNAME
--------- ---------------
1 Admin
2 Finance
3 Accounts
5 XXXX
6 XXXXXXXXXXX
Emp table
ENO ENAME DNO
--------- --------------- ---------
1 AAAAAAAAAA 1
2 BBBBBBBBBB 1
3 CCCCCCCCCC 2
4 DDDDDDDDD 2
5 EEEEEEEEE 3
result
SQL> select a.dno,dname,ename from dept a, emp b
2 where a.dno = b.dno (+);
DNO DNAME ENAME
--------- --------------- ---------------
1 Admin AAAAAAAAAA
1 Admin BBBBBBBBBB
2 Finance CCCCCCCCCC
2 Finance DDDDDDDDD
3 Accounts EEEEEEEEE
5 XXXX
6 XXXXXXXXXXX
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
