RDBMS ASSIGNMENT NO – 18
A. What are the names of customers who have sent packages (shipments) to Sioux City? SELECT distinct cust_name FROM customer c,shipment s where c.cust_id = s.cust_id and s.destination = 'Sioux City'
B. To what destinations have companies with revenue less than $1 million sent packages? select destination from shipment s , customer cus tomer c where s.cust_id = c.cust_id and annual_revenue < 1000000
C. What are the names and populations of cities that have received shipments weighing over 100 pounds? select distinct c.city_name , population from city c,shipment s where c.city_name = s.destination and weight > 100
D. Who are the customers having having over $5 million in annual revenue revenue who have sent sent shipments weighing less than 1 pound? SELECT cust_name FROM customer c,shipment c,shipment s where c.cust_id = s.cust_id and annual_revenue > 5000000 and weight <= 1 ;
E. 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? SELECT cust_name FROM customer c,shipment c,shipment s where c.cust_id = s.cust_id and annual_revenue > 5000000 and (weight <= 1 or destination = 'San Francisco')
F. Who are the drivers who have delivered shipments for customers with annual revenue over $20 million to cities with populations over 1 million? SELECT distinct driver_name from truckn t,customer c,shipment s,city ci where t.truckid = s.truckid and s.cust_id = c.cust_id and ci.city_name = s.destination and annual_revenue > 20000000 and population > 1000000
G. List the cities that have received shipments from customers having over $15 million in annual revenue. select city_name from city,customer c,shipment s where city_name = destination and c.cust_id = s.cust_id and annual_revenue > 15000000
H. List the names of drivers who have delivered shipments weighing over 100 pounds. SELECT distinct driver_name from truckn t,shipment s where t.truckid = s.truckid and weight > 100
I. List the name and annual revenue of customers who have sent shipments weighing over 100 pounds. select distinct cust_name , annual_revenue from customer c,shipment s where c.cust_id = s.cust_id and weight > 100
J. List the name and annual revenue of customers whose shipments have been delivered by truck driver Jensen. SELECT distinct cust_name,annual_revenue from customer c,truckn t,shipment s where t.truckid = s.truckid and c.cust_id = s.cust_id
and driver_name = 'Jensen'
K. List customers who had shipments delivered by every truck. ( use NOT EXISTS) select cust_name, cust_id from customer where not exists (select truckid from truckn where not exists (select * from shipment where shipment.truckid= truckn.truckid and shipment.cust_id=customer.cust_id))
L. List cities that have received shipments from every customer. ( use NOT EXISTS) select city_name from city where not exists (select * from customer where not exists (select destination from shipment where shipment.destination = city.city_name and shipment.cust_id=customer.cust_id))
M. List drivers who have delivered shipments to every city. (use NOT EXISTS) select driver_name from truckn where not exists (select * from city where not exists (select destination from shipment where shipment.destination = city.city_name and shipment.truckid=truckn.truckid))
N. Customers who are manufacturers or have sent a package to St. Louis. select distinct cust_name from customer c,shipment s where c.cust_id=s.cust_id and (cust_type = 'manufacturer' or destination = 'St. Louis')
O. Cities of population over 1 million which have received a 100pound package From customer 311. select city_name from city c,shipment s where population > 1000000 and c.city_name = s.destination and s.weight = 100 and s.cust_id = 311
P. Trucks driven by Jake Stinson which have never delivered a shipment to Denver. SELECT t.truckid,t.driver_name From truckn t, shipment s where s.truckid = t.truckid and t.driver_name='Jake Stinson' and s.destination != 'Denver'
Q. Customers with annual revenue over $10 million which have sent packages under 1 pound to cities with population less than 10,000. select cust_name from customer c,shipment s,city ci where annual_revenue > 10000000 and c.cust_id = s.cust_id and ci.city_name = s.destination and weight < 1 and population < 1000
R. 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)CREATE VIEW cust_under AS select * from customer where (annual_revenue < 1000000) b)CREATE VIEW cust_bet AS select * from customer where (annual_revenue between 1000000 and 5000000) c)CREATE VIEW cust_under AS select * from customer where (annual_revenue > 5000000)
S. 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)select driver_name from truckn t,shipment s,cust_over c where t.truckid = s.truckid and s.cust_id = c.cust_id and destination = 'Los Angeles' b)select distinct population from city c,shipment s,cust_bet cu where c.city_name = s.destination and s.cust_id = cu.cust_id c)select distinct driver_name, population from city c,truckn t,shipment s,cust_view cu where t.truckid = s.truckid and s.destination = c.city_name and s.cust_id = cu.cust_id
