The SAMPLE database from your DB2 installation contains many tables. By using the STAFF table (35 rows) & ORG table (8 rows), answer each of the following questions using a single SQL statement. You need to put all the working SQL statements in a file named h2.sql. A sample of h2.sql will be given.

1.  Find all the employees who has at least 10 years of experiences with the output order by the ones with the most experiences first. (Output column name: ID, Name, Years)
2. Show all the commission employees (24 of them) and their total compensation (salary + comm.) in descending order based on their total compensation. (Output columns: ID, Name, Total_Compensation)
3. Find the 5 lowest paid non-commissioned employees based on their salary in lowest order first. (Output columns: ID, Name, Salary)
4. Find all the employees who worked in the department named Mountain, Plains, or New England in ascending order of the deptname and then name (Output columns: Deptname, Location, Name). (Hint – JOIN)
5.  There are many job roles among the 35 employees. Find the number of people in each job role (at least 1) order by highest to lowest. Your SQL statement must produce the output as the one shown below. (Hint - GROUP BY)
JOB EMPLOYEE_COUNT
----- --------------
Clerk 12
Sales 12
Mgr 11
3 record(s) selected.
6. There are several divisions in the company. Find the number of employees in each division (at least
1) order from lowest to highest. Your SQL statement must produce the output as the one shown below. (Hint –
JOIN & GROUP BY)
DIVISION EMPLOYEE_COUNT
---------- --------------
Corporate 4
Midwest 9
Western 9
Eastern 13
4 record(s) selected.

h2_sample.sql file:
 

Transcribed Image Text:

-- h2_sample.sql file Connect to sample; select from org; select count (division) as Division_Count from org; select * from staff order by salary asc; select count(*) as Non_Comm_Employee from staff where comm is null; terminate; -- h2_sample.sql file Connect to sample; select from org; select count (division) as Division_Count from org; select * from staff order by salary asc; select count(*) as Non_Comm_Employee from staff where comm is null; terminate;

Answer rating: 100% (QA)

Solution Here are the SQL statements for the given questions in the file h2sql 1 Find all the employ... View the full answer