| sql queries [message #645015] |
Mon, 23 November 2015 12:44  |
 |
sabsac
Messages: 9
Registered: October 2015
Location: I
|
Junior Member |
|
|
using the scott scheme please give the syntax for the following 2 queries
1.display the employee number, name, and salary for all employees
who earn more than the average salary and who work in a department with any employee
with a T in their name.
2.Write a query that will display the employee number and name for all employees who work in a department with any employee whose name contains a T.
|
|
|
|
|
|
| Re: sql queries [message #645018 is a reply to message #645015] |
Mon, 23 November 2015 13:48  |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
Let me guess, you get the answer to this and you go tell a prospective employer that you have high level sql skills.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: sql queries [message #645123 is a reply to message #645120] |
Fri, 27 November 2015 00:47 |
 |
Rishab_le_noob
Messages: 12
Registered: November 2015
Location: Kolkata
|
Junior Member |
|
|
@Michel Cadot
The O/P he has posted is the output of his query and not what he desires.
Am pretty sure he got the Output wrong, and that is because he didn't understand why the 'T' is needed.
Quote:
1.display the employee number, name, and salary for all employees
who earn more than the average salary and who work in a department with any employee
with a T in their name.
2.Write a query that will display the employee number and name for all employees who work in a department with any employee whose name contains a T.
He later wrote
Quote:
I have the output of both the queries and do not understand what the t is supposed to mean here.
So some of the final output might have names without T in them but the initial check on Dept will be if they have an employee with a 'T' in their name.
Which I dont think he is doing... anyways he needs to share his query 1st.
[Updated on: Fri, 27 November 2015 00:53] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: sql queries [message #645168 is a reply to message #645166] |
Sat, 28 November 2015 01:17 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
But solutions are not for me, they are for people who learn SQL and search for help in the web.
OK, if this is so trivial for you, improve your queries:
1/ SELECT EMPNO,ENAME,SAL
FROM EMP
WHERE SAL > (SELECT AVG(SAL) FROM EMP)
AND DEPTNO IN (SELECT DEPTNO FROM EMP WHERE ENAME LIKE '%T%');
This query scan 3 times the table EMP:
SQL> set autotrace traceonly explain
SQL> SELECT EMPNO,ENAME,SAL
2 FROM EMP
3 WHERE SAL > (SELECT AVG(SAL) FROM EMP)
4 AND DEPTNO IN (SELECT DEPTNO FROM EMP WHERE ENAME LIKE '%T%');
Execution Plan
----------------------------------------------------------
Plan hash value: 828433562
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 10 (10)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 1 | 26 | 7 (15)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | EMP | 1 | 17 | 3 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 4 | | |
| 4 | TABLE ACCESS FULL| EMP | 14 | 56 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 1 | 9 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPTNO"="DEPTNO")
2 - filter("SAL"> (SELECT AVG("SAL") FROM "EMP" "EMP"))
5 - filter("ENAME" LIKE '%T%')
2/ SELECT EMPNO,ENAME,DEPTNO
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE ENAME LIKE '%T%');
This query scans twice the EMP table:
SQL> SELECT EMPNO,ENAME,DEPTNO
2 FROM EMP
3 WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE ENAME LIKE '%T%');
Execution Plan
----------------------------------------------------------
Plan hash value: 977554918
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 110 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 5 | 110 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 1 | 9 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPTNO"="DEPTNO")
3 - filter("ENAME" LIKE '%T%')
Oracle introduces in version 8.1.6 (in 1999) analytic functions to avoid so much scans, can you provide queries that scan only once the table EMP?
[Updated on: Sat, 28 November 2015 01:17] Report message to a moderator |
|
|
|
|
|
| Re: sql queries [message #645173 is a reply to message #645171] |
Sat, 28 November 2015 02:31 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
I already post them and I posted them FORMATTED. See the difference between your posts and mine. Which one is easier to read?
Now find the optimized queries.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
