| A question - need feedback [message #373452] |
Tue, 17 April 2001 18:54  |
Jini
Messages: 7
Registered: April 2001
|
Junior Member |
|
|
Hello,
My professor was telling me something in the class which I didn't get it. He was saying that the following sql statement is correct but not accurate.
SQL> select ename, job, deptno, sal, grade
2 from employee, salgrade
3 where sal between losal and hisal;
ENAME JOB DEPTNO SAL GRADE
---------- --------- --------- --------- ---------
James Clerk 30 950 1
Smith Clerk 20 800 1
Martin Salesman 30 1250 2
Ward Salesman 30 1250 2
Miller Clerk 10 1300 2
Allen Salesman 30 1600 3
Turner Salesman 30 1500 3
Blake Manager 30 2850 4
Clark Manager 10 2450 4
Jones Manager 20 2975 4
Ford Analyst 20 3000 4
Scott Analyst 20 3000 4
King President 10 5000 5
King President 10 5000 5
14 rows selected.
But the following statement given by him would be more accurate.
SQL> select e.ename, e.job, d.deptno, e.sal, s.grade
2 from employee e, department d, salgrade s
3 where e.deptno = d.deptno
4 and e.sal between s.losal and s.hisal;
ENAME JOB DEPTNO SAL GRADE
---------- --------- --------- --------- ---------
Miller Clerk 10 1300 2
Clark Manager 10 2450 4
King President 10 5000 5
Smith Clerk 20 800 1
Scott Analyst 20 3000 4
Ford Analyst 20 3000 4
Jones Manager 20 2975 4
James Clerk 30 950 1
Allen Salesman 30 1600 3
Blake Manager 30 2850 4
Turner Salesman 30 1500 3
Martin Salesman 30 1250 2
Ward Salesman 30 1250 2
13 rows selected.
I think my professor's code would be more redundant but he might be right. I need some feedback as to which one is redundant and which one is not. Why should I do it my professor's way? My professor is trying to link employee table with department table but I don't think so there is need for department table because I am getting data from salgrade table.
Please reply
Thank you in advance
Jinisha
|
|
|
|
| Re: A question - need feedback [message #373455 is a reply to message #373452] |
Tue, 17 April 2001 20:10  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
both of the queries look wrong to me. If you don't perform joins between the tables you get a cartesean product. If one of the tables only has one row, then there isn't a cartesean product problem.
For 3 tables, you should have at least 2 joins (if your PK's and FK's are composite, then each of the components needs to be joined).
It seem you need to join e to d and e to s. Sometimes you'll need outer joins (e.g. return a dept even if there are no employees in that dept).
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
