ASSIGNMENT-1
1. Create a database called COMPANY consisting of two tables - EMP & DEPT
2. Perform the following queries on the tables just created:
1. List the names of analysts and salesmen.
2. List details of employees who have joined before 30 Sep 81.
3. List names of employees who are not managers.
4. List the names of employees whose employee numbers are 7369, 7521, 7839,
7934, 7788.
5. List employees not belonging to department 30, 40, or 10.
6. List employee names for those who have joined between 30 June and 31 Dec. 81.
7. List the different designations in the company.
8. List the names of employees who are not eligible for commission.
9. List the name and designation of the employee who does not report to anybody.
10. List the employees not assigned to any department.
11. List the employees who are eligible for commission.
12. List employees hose names either start or end with "S".
13. List names of employees whose names have "i" as the second character.
14. List the number of employees working with the company.
15. List the number of designations available in the EMP table.
16. List the total salaries paid to the employees.
17. List the maximum, minimum and average salary in the company.
18. List the maximum salary paid to a salesman.
ASSIGNMENT-2
1) Please refer to the tables created as a part of Assignment 1. Perform the following queries against those tables:
1. List the number of employees and average salary for employees in department 20.
2. List name, salary and PF amount of all employees. (PF is calculated as 10% of basic salary)
3. List names of employees who are more than 2 years old in the company.
4. List the employee details in the ascending order of their basic salary.
5. List the employee name and hire date in the descending order of the hire date.
6. List employee name, salary, PF, HRA, DA and gross; order the results in the
ascending order of gross. HRA is 50% of the salary and DA is 30% of the salary.
7. List the department numbers and number of employees in each department.
8. List the department number and total salary payable in each department.
9. List the jobs and number of employees in each job. The result should be in the descending order of the number of employees.
10. List the total salary, maximum and minimum salary and average salary of the employees jobwise.
11. List the total salary, maximum and minimum salary and average salary of the employees, for department 20.
12. List the total salary, maximum and minimum salary and average salary of the employees jobwise, for department 20 and display only those rows having an average salary > 1000
2) The following questions pertain to a database with the following tables.
Suppliers - S (S#, Name, Status, City)
Parts - P (P#, Pname, Colour, Weight, City)
Projects - J (J#, Jname, City)
Shipment - SPJ (S#, P#, J#, Qty)
The significance of an SPJ record is that the specified supplier supplies the specified part to the specified project in the specified quantity (and the combination S#-P#-J# uniquely identifies such a record).
1. Get full details of all projects in London.
2. Get S# for suppliers who supply project J1.
3. Get all part-color/part-city combinations.
4. Get all S#/P#/J# triples such that all are co-located.
5. Get al S#, P#, J# triples such that they are not all co-located.
6. Get P# for parts supplied by a supplier in London.
7. Get all pairs of cities such that a supplier in the first city supplies to a Project in the second city.
8. Get J# for projects supplied by at least one supplier not in the same city.
9. Get all pairs of part numbers such that some supplier supplies both the indicated parts.
10. Get the total quantity of part P1 supplied by S1.
11. For each part supplied to a project, get the P#, J# and corresponding total quantity.
12. Get P# of parts supplied to some project in an average quantity > 320.
13. Get project names for projects supplied by supplier S1.
14. Get colors of parts supplied by S1.
15. Get J# for projects using at least one part available from supplier S1.
16. Get supplier numbers for suppliers supplying at least one part supplied by at least one supplier who supplies at least one red part.
17. Get supplier numbers for suppliers with a status lower than that of supplier S1.
18. Get project numbers for projects not supplied with any red part by any London supplier.