Description

NAME: ARNAB SEN
EN.NO: 510519006 (Gx) SUBJECT: DBMS Lab
Assignment No: 8
Write the SQL commands to create the following relational schemas:
Customer ( cust_id, cust_name, annual_revenue, cust_type ) Cust_id must be between 100 and 10,000.
Cust_type must be ‘MANUFACTURER’, ‘WHOLESALER’, or ‘RETAILER’.
query:
CREATE TABLE customer( cust_id numeric(6) PRIMARY KEY CHECK(
(cust_id > 100)
AND (cust_id < 10000)
), annual_income numeric(8), cust_namen varchar(20), cust_type varchar(24) NOT NULL CHECK( cust_type IN(‘MANUFACTURER’, ‘WHOLESALER’, ‘RETAILER’)
)
);

Truck ( truck_no, driver_name ) query:
CREATE TABLE truck( truck_no VARCHAR(10) PRIMARY KEY, driver_name VARCHAR (20)
);

City ( city_name, population) query:
CREATE TABLE city( city_name VARCHAR(20) PRIMARY KEY, population NUMERIC(10)
);

Shipment ( shipment_no, cust_id, weight, truck_no, destination, ship_date )
Foreign keys: cust_id references customer on delete cascade, truck_no references truck on delete set null, destination references city. Weight must be under 1000. query:
PRIMARY KEY(shipment_no, cust_id) );

1) Give names of customer who have sent packages (shipments) to Kolkata, Chennai and Munbai. query:
SELECT
DISTINCT cust_name
FROM shipment s, customer c WHERE
s.cust_id = c.cust_id
AND destination IN (‘Chennai’, ‘Kolkata’, ‘Mumbai’);

2. List the names of the driver who have delivered shipmentsweighing over 200 pounds. query:
SELECT
DISTINCT driver_name
FROM shipment s, truck t
WHERE
s.truck_no = t.truck_no AND s.weight > 200;

3. Retrieve the maximum and minimum weights ofthe shipments. Rename the output as
Max_Weight and Min_Weight respectively.
query:
SELECT max(weight) AS MaxWeight, min(weight) AS MinWeight
FROM shipment;

4. For each customer, what is the average weight ofpackage sent by the customer? query:
SELECT cust_id,
avg(weight) AS AverageWeight
FROM shipment
GROUP BY cust_id
ORDER BY cust_id;

5. List the names and populations of cities that have received ashipment weighing over 100 pounds. query:
SELECT city_name, population
FROM shipment s, city c
WHERE
s.destination = c.city_name
AND weight > 100
GROUP BY city_name;

6. List cities that have received shipments from every customer. query:
SELECT city_name FROM shipment s, city c
WHERE
s.destination = c.city_name
GROUP BY city_name
HAVING count(DISTINCT cust_id) = (
SELECT count(*)
FROM customer
);

7. For each city, what is the maximum weight of apackage sent to that city? query:
SELECT city_name, max(weight)
FROM city c, shipment s
WHERE
c.city_name = s.destination
GROUP BY city_name;

8. List the name and annual revenue of customers whoseshipments have been delivered by truck driver ‘Kiara’. query:
SELECT cust_name, annual_revenue
FROM customer c, shipment s, truck t
WHERE
c.cust_id = s.cust_id AND s.truck_no = t.truck_no
AND driver_name = ‘Kiara’;

9. List drivers who have delivered shipments to every city. query:
SELECT
t.truck_no,
t.driver_name
FROM shipment s, truck t
WHERE
s.truck_no = t.truck_no
GROUP BY
t.truck_no
HAVING count(DISTINCT(destination)) = (
SELECT count(*)
FROM city
);

10. For each city, with a population of over 1 million, what is the minimum weight of a package sent to that city. query:
SELECT city_name, min(weight)
FROM shipment, city
WHERE destination = city_name
AND population > 1000000
GROUP BY city_name;

Assignment No: 9
Write SQL commands to create the following tables as well as to insert sufficient number of values in the tables:
EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) EMPNO must be between 7000 and 8000.
ENAME must not exceed 10 characters.
JOB must be in (‘Clerk’,‘Salesman’,‘Manager’,‘Analyst’,‘President’).
MGR is the manager’s EMPNO.
COMM must be under 1500 and defaults to 0.
CREATE TABLE EMP(
EMPNO numeric(10) PRIMARY KEY CHECK (
EMPNO BETWEEN 7000
AND 8000
),
ENAME varchar(10),
JOB varchar(10) CHECK (
JOB IN(
‘President’,
‘Manager’,
‘Clerk’,
‘Salesman’,
‘Analyst’
)
),
SAL numeric(10),
COMM numeric(5) DEFAULT 0 CHECK(COMM < 1500),
DEPTNO varchar(5) REFERENCES DEPT(DEPTNO)
);
DEPT ( DEPTNO, DNAME, LOC ) DEPTNO must start with ‘D’.
DNAME must be ‘Accounting’ or ‘Sales’ or ‘Research’ or ‘Operations’.
CREATE TABLE DEPT(
DEPTNO varchar(5) PRIMARY KEY CHECK (DEPTNO LIKE ‘D%’),
DNAME varchar(10) CHECK (
DNAME IN (‘Accounting’, ‘Sales’, ‘Research’, ‘Operations’)
),
LOC varchar(10)
);
1. Display the difference between the highest and lowest salaries of each department in descending order. Label the column as “Difference”. query:
SELECT DNAME,
max(SAL) – min(SAL) AS Difference
FROM DEPT d,
EMP e
WHERE
d.DEPTNO = e.DEPTNO
GROUP BY
D.DEPTNO
ORDER BY
Difference DESC;

2. List all the employees’ employee numbers and names alongwith their immediate managers’ employee numbers and names. query:
SELECT
a.EMPNO AS employee_id,
a.ENAME AS employee_name,
b.EMPNO AS manager_id,
b.ENAME AS manager_name
FROM EMP a,
EMP b
WHERE
a.MGR = b.EMPNO;

SELECT
TOTAL,
TOTAL_2020
FROM
(
SELECT count(*) AS TOTAL
FROM
EMP
) AS hd1, (
SELECT count(*) AS TOTAL_2020
FROM
EMP
WHERE
EXTRACT( YEAR
FROM
HIREDATE

4. Display the manager number and the salary of the lowest-paid employee under that manager. Exclude anyone whose manager is not known. Exclude any group where the minimum salary is less than 1000. Sort the output in descending order of salary. query:
SELECT
MGR AS manager_id, min(SAL) AS minSalary FROM
EMP
GROUP BY
MGR
HAVING
MGR IS NOT NULL
AND min(SAL) < 300000
ORDER BY minSalary DESC;

SELECT
DNAME AS “DNAME”, LOC AS “LOCATION”, count(*) AS “NUMBER OF PEOPLE”,
coalesce(round(avg(SAL), 2), 0.0) AS “AVERAGE SALARY”
FROM
DEPT
LEFT JOIN EMP ON (EMP.DEPTNO = DEPT.DEPTNO)
GROUP BY DNAME,
LOC;