ASSIGNMENT-1
1. Create a database called COMPANY consisting of two attached tables - EMP & DEPT
2. Perform the following queries on the tables just created:
1. List the names of analysts and salesmen.
SQL> select ename from emp where job='analyst' and job='salesman';
2. List details of employees who have joined before 30 Sep 81.
SQL> select * from emp where hiredate < '30-sep-81';
3. List names of employees who are not managers.
SQL> select ename from emp where job is not manager;
4. List the names of employees whose employee numbers are 7369, 7521, 7839, 7934, 7788.
SQL> select ename from from emp where empno in (7369,7521,7839,7934,7788);
5. List employees not belonging to department 30, 40, or 10.
SQL> select ename from emp where deptno not in (30,40,10);
6. List employee names for those who have joined between 30 June and 31 Dec. 81.
SQL> select ename from emp where hiredate between '30-jun-81' and '31-dec-81';
7. List the different designations in the company.
SQL> select distinct job from emp;
8. List the names of employees who are not eligible for commission.
SQL> select ename from emp where comm = NULL;
9. List the name and designation of the employee who does not report to anybody.
SQL> select ename,job from emp where job = ‘president';
10. List the employees not assigned to any department.
SQL> select ename from emp where job = NULL;
11. List the employees who are eligible for commission.
SQL> select ename from emp where comm. is not NULL;
12. List employees hose names either start or end with "S".
SQL> select ename from emp where ename like ‘S%' and like ‘%S';
13. List names of employees whose names have "i" as the second character.
SQL> select ename from emp where ename like ‘_i%';
14. List the number of employees working with the company.
SQL> select ename from emp;
15. List the number of designations available in the EMP table.
SQL> select distinct job from emp;
16. List the total salaries paid to the employees.
SQL> select sum(sal) from emp;
17. List the maximum, minimum and average salary in the company.
SQL> select max(sal),min(sal),avg(sal) from emp;
18. List the maximum salary paid to a salesman.
SQL> select max(sal) from emp where job = ‘salesman';
ASSIGNMENT-2
1) Please refer to the tables created as a part of Assignment 3.
Perform the following queries against those tables:
1. List the number of employees and average salary for employees in department 20.
SQL> select count(ename),avg(sal) from emp where deptno = 20;
2. List name, salary and PF amount of all employees. (PF is calculated as 10% of basic salary)
SQL> select ename,sal,sal((sal/100)*10) from emp;
3. List names of employees who are more than 2 years old in the company.
SQL>
4. List the employee details in the ascending order of their basic salary.
SQL> select * from emp order by sal;
5. List the employee name and hire date in the descending order of the hire date.
SQL> select ename,hiredate from emp order by hiredate desc;
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.
SQL> select ename,sal, ((sal/100)*10)"PF", ((sal/100)*50)"HRA", ((sal/100)*30)"DA", (sal+((sal/100)*10)+((sal/100)*50)+((sal/100)*30))"Gross" from emp;
7. List the department numbers and number of employees in each department.
SQL> select deptno,count(ename) from emp group by deptno;
8. List the department number and total salary payable in each department.
SQL> select deptno,sum(sal) from emp group by deptno;
9. List the jobs and number of employees in each job. The result should be in the descending order of the number of employees.
SQL> select job,count(empno) from emp group by job;
10. List the total salary, maximum and minimum salary and average salary of the employees jobwise.
SQL> select sum(sal),max(sal),min(sal),avg(sal) from emp group by job;
11. List the total salary, maximum and minimum salary and average salary of the employees, for department 20.
SQL> select sum(sal),max(sal),min(sal),avg(sal) from emp where deptno = 20 group by job;
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
SQL> select sum(sal),max(sal),min(sal),avg(sal) from emp where deptno = 20 having avg(sal) > 1000 group by job;
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.
SQL> select * from J where city = ‘London';
2. Get S# for suppliers who supply project J1.
SQL> select S# from S where S# in (select S# from SPJ where J# = J1);
3. Get all part-color/part-city combinations.
SQL> select city,colour from P group by (city,colour);
4. Get all S#/P#/J# triples such that all are co-located.
SQL> select S#,P#,J# from S,P,J where S.city = P.city and P.city = J.city and J.city = S.city and (S#,P#,J#) in (select S#,P#,J# from SPJ);
5. Get al S#, P#, J# triples such that they are not all co-located.
SQL> select S#,P#,J# from S,P,J where (S.city <> J.city or J.city <> P.city or S.city <> P.city) and (S#,P#,J#) in (select S#,P#,J# from SPJ);
6. Get P# for parts supplied by a supplier in London.
SQL> select distinct (P#) from SPJ where S# in (select S# from A where city = ‘London');
7. Get all pairs of cities such that a supplier in the first city supplies to a Project in the second city.
SQL> select S.city,J.city from S,J where S.city <> J.city and (S#,J#) in (select S#,J# from SPJ);
8. Get J# for projects supplied by at least one supplier not in the same city.
SQL> select distinct (J#) from J where exist (select S# from S where S.city <> J.city and (J#,S#) in (select J#,S# from SPJ));
9. Get all pairs of part numbers such that some supplier supplies both the indicated parts.
SQL> select SPJ.P#,P.P# from SPJ, P where P.P# <> SPJ.P# and (S#,P.P#) in (select S#,P# from SPJ) group by (SPJ.P#,P.P#);
10. Get the total quantity of part P1 supplied by S1.
SQL> select sum(qty) from SPJ where P# = ‘P1' and S# = ‘S1';
11. For each part supplied to a project, get the P#, J# and corresponding total quantity.
SQL> select P#,J#,sum(qty) from SPJ group by (P#,J#);
12. Get P# of parts supplied to some project in an average quantity > 320.
SQL> select P#,avg(qty) from SPJ goup by P# having avg(qty)>320;
13. Get project names for projects supplied by supplier S1.
SQL> select Pname from P where P# in (select P# from SPJ where S# = ‘S1');
14. Get colors of parts supplied by S1.
SQL> select colour from P where P# in (select P# from SPJ where S# = ‘S1');
15. Get J# for projects using at least one part available from supplier S1.
SQL> select J# from J where J# in (select J# from SPJ where S# = ‘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.
SQL> select S# from S where S# in (select S# from SPJ where P# in (select P# from SPJ where S# in (select S# from SPJ where P# in (select P# from SPJ where P# in (select P# from P where colour ='red')))));
17. Get supplier numbers for suppliers with a status lower than that of supplier S1.
SQL> select S# from S where status < (select status from S where S# = ‘S!);
18. Get project numbers for projects not supplied with any red part by any London supplier.
SQL> select J# from J where J# in (select J# from SPJ where P# not in (select P# from P where colour = ‘red')) and S# in ( select S# from S where city ='London');
ASSIGNMENT-3
1) Write the SQL commands to create a database schema for the following relational schema:
CUSTOMER (CUST_ID, CUST_NAME, ANNUAL_REVENUE, CUST_TYPE)
CUST_ID must be between 100 and 10,000
ANNUAL_REVENUE defaults to $20,000
CUST_TYPE must be manufacturer, wholesaler, or retailer
SHIPMENT (SHIPMENT_#, CUST_ID, WEIGHT, TRUCK_#,
DESTINATION, SHIP_DATE)
Foreign Key: CUST_ID REFERENCES CUSTOMER, on deletion cascade
Foreign Key: TRUCK_# REFERENCES TRUCK, on deletion set to null
Foreign Key: DESTINATION REFERENCES CITY, on deletion set to null
WEIGHT must be under 1000 and defaults to 10
TRUCK (TRUCK_#, DRIVER_NAME)
CITY (CITY_NAME, POPULATION)
Perform the following queries:
1. What are the names of customers who have sent packages (shipments) to Sioux City?
SQL> select cust_id,cust_name from customer where cust_id in (select cust_id from shipment where designation = ‘Sioux');
2. To what destinations have companies with revenue less than $1 million sent
packages?
SQL> select distinct(designation) from shipment where cust_id in (select cust_id from customer where annual_revenue < $1);
3. What are the names and populations of cities that have received shipments
weighing over 100 pounds?
SQL> select city_name,population from city where city_name in (select designation from shipment where weight > 100);
4. Who are the customers having over $5 million in annual revenue who have sent shipments weighing less than 1 pound?
SQL> select cust_id,cust_name from customers where annual_revenue > $5 and cust_id in ( select cust_id from shipment where weight <1);
5. Who are the customers having over $5 million in annual revenue who have sent shipments weighing less than 1 pound or have sent a shipment to San Francisco?
SQL> select cust_id,cust_name from customers where annual_revenue > $5 and cust_id in (select cust_id from shipment where weight < 1 or designation = 'San Francisco');
6. Who are the drivers who have delivered shipments for customers with annual revenue over $20 million to cities with populations over 1 million?
SQL> select truck_#,driver_name from truck where truck_# in (select truck_# from shipment where cust_id in (select cust_id from customer where annual_revenue > $20) and destination in (select city_name from city where position > 1000000));
7. List the cities that have received shipments from customers having over $15
million in annual revenue.
SQL> select distinct(designation) from shipment where cust_id in ( select cust_id from customer where annual_revenue > $15);
8. List the names of drivers who have delivered shipments weighing over 100
pounds.
SQL> select truck_#,driver_name from truck where truck_# in (select truck_# from shipment where weight > 100);
9. List the name and annual revenue of customers who have sent shipments
weighing over 100 pounds.
SQL> select cust_name,annual_revenue from customer where cust_id in (select cust_id from shipment where weight > 100);
10. List the name and annual revenue of customers whose shipments have been
delivered by truck driver Jensen.
SQL> select cust_name,annual_revenue from customer where cust_id in (select cust_id from shipment where truck_# in (select truck_# from truck where driver_name = ‘Jensen'));
11. List customers who had shipments delivered by every truck.
SQL> select cust_id,cust_name from customer where ( select count(distinct(truck_#)) from shipment where shipment.cust_id = customer.cust_id) >= (select count(*) from truck);
12. List cities that have received shipments from every customer.
SQL> select city_name from city where (select count (distinct ( cust_id)) from shipment where designation = city.city_name) >= (select count (*) from customer);
13. List drivers who have delivered shipments to every city.
SQL> select driver_name from truck where truck_# in (select truck_# from truck where (select count(distinct(designation)) from shipment where shipment.truck_# = truck.truck#) >= (select count (*) from city));
14. Customers who are manufacturers or have sent a package to St. Louis.
SQL> select cust_id,cust_name from customer where cust_type = ‘manufacturer' or cust_id in (select cust_id from shipment where designation = ‘St. Louis');
15. Cities of population over 1 million which have received a 100-pound package From customer 311.
SQL> select city_name from city where population > 1000000 and city_name in (select designation from shipment where weight = 100 and cust_id = 311);
16. Trucks driven by Jake Stinson which have never delivered a shipment to Denver.
SQL> select truck_# from truck where driver_name = ‘Jake Stinson' and truck_# in (select truck_# from shipment where designation <> ‘Denver');
17. Customers with annual revenue over $10 million which have sent packages under 1 pound to cities with population less than 10,000.
SQL> select cust_id,cust_name from customer where annual_revenue > 10000000 and cust_id in (select cust_id from shipment where designation in (select city_name from city where population < 10000) and weight < 1);
18. Create views for each of the following:
a. Customers with annual revenue under $1 million.
b. Customers with annual revenue between $1 million and $5 million.
c. Customers with annual revenue over $5 million.
a. SQL> create view revenue_1 as select cust_id,cust_name from customer where annual_revenue < 1000000;
b. SQL> create view revenue_2 as select cust_id,cust_name from customer where annual_revenue > 1000000 and annual_revenue < 5000000;
c. SQL> create view revenue_3 as select cust_id, cust_name from customer where annual_revenue > 5000000;
19. Use these views to answer the following queries:
a. Which drivers have taken shipments to Los Angeles for customers with revenue over $5 million?
b. What are the populations of cities which have received shipments from customers with revenue between $1 million and $5 million?
c. Which drivers have taken shipments to cities for customers with revenue under $1 million, and what are the populations of those cities?
a. SQL> select * from revenue_3 where cust_id in (select cust_id from shipment where designation = ‘Los Angeles');
b. SQL> select city_name,population from city where city_name in (select designation from shipment where cust_id in (select cust_id from revenue_2));
c. SQL> select driver_name,population from truck,city where ( truck_#,city_name) in (select truck_#, designation from shipment where cust_id in (select cust_id from revenue_1));
Attachment:- ASSIGNMENT.rar