Lab Table :

Let's use the EmployeeHours table to illustrate how SQL IN works: Date Hours 5/6/2004 8 Employee John Smith Allan Babel Tina Crown John Smith 5/6/2004 8 8 8 5/6/2004 5/7/2004 9 Allan Babel 5/7/2004 8 Tina Crown 5/7/2004 10 5/8/2004 8 John Smith Allan Babel 5/8/2004 8 Tina Crown 5/8/2004 9 9 1 N N 3 4 Results Messages id fn In dept sal 1 amir wajid sales 10000 2 2 irfan wajahat sales 100000 3 anaya khurram sales 20000 4 walian najib engg 25000 5 muntaha noor engg 55000 6 kashif saif engo 45000 7 7 nousheen irfan CS 145000 8 sohail atif cs 110000 9 najam afnan it 110000 10 shakir shujabadiit 100000 5 6 7 8 9 10 SELECT commission, COUNT() FROM agents GROUP BY commission HAVING COUNT(*) > 3; GROUP BY commission COUNT(*) COMMISSION 15 agents AGENT NAME COMMISSION Alex 13 Subbarao Benjamin Ramasundar 15 Alford 12 Ravi Kumar 15 Santakumar 14 Lucida 12 Anderson 13 Mukesh 11 McDen Ivan ..14 . 13 12 NNNN HAVING COUNT)>3 COUNT) COMMISSION 15 .11 COUNT(*) NNNN 13 COMMISSION .16 4 - 12 wresource.com LAB TASK 1. Returns the list of departments having an Average Gross Salary is more than 25L. 2. finds the products whose list price is one of the following values: 89.99, 109.99, and 159.99 3. Create a query that displays mgr, average salary of the employees according to their jobs. Exclude anyone whose mgr is not known (that is NULL). Exclude values where the minimum salary is even less than 800. Let's use the EmployeeHours table to illustrate how SQL IN works: Date Hours 5/6/2004 8 Employee John Smith Allan Babel Tina Crown John Smith 5/6/2004 8 8 8 5/6/2004 5/7/2004 9 Allan Babel 5/7/2004 8 Tina Crown 5/7/2004 10 5/8/2004 8 John Smith Allan Babel 5/8/2004 8 Tina Crown 5/8/2004 9 9 1 N N 3 4 Results Messages id fn In dept sal 1 amir wajid sales 10000 2 2 irfan wajahat sales 100000 3 anaya khurram sales 20000 4 walian najib engg 25000 5 muntaha noor engg 55000 6 kashif saif engo 45000 7 7 nousheen irfan CS 145000 8 sohail atif cs 110000 9 najam afnan it 110000 10 shakir shujabadiit 100000 5 6 7 8 9 10 SELECT commission, COUNT() FROM agents GROUP BY commission HAVING COUNT(*) > 3; GROUP BY commission COUNT(*) COMMISSION 15 agents AGENT NAME COMMISSION Alex 13 Subbarao Benjamin Ramasundar 15 Alford 12 Ravi Kumar 15 Santakumar 14 Lucida 12 Anderson 13 Mukesh 11 McDen Ivan ..14 . 13 12 NNNN HAVING COUNT)>3 COUNT) COMMISSION 15 .11 COUNT(*) NNNN 13 COMMISSION .16 4 - 12 wresource.com LAB TASK 1. Returns the list of departments having an Average Gross Salary is more than 25L. 2. finds the products whose list price is one of the following values: 89.99, 109.99, and 159.99 3. Create a query that displays mgr, average salary of the employees according to their jobs. Exclude anyone whose mgr is not known (that is NULL). Exclude values where the minimum salary is even less than 800