Reference no: EM131950737 , Length: 29 pages

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