Dbms Subqueries
Essay by sihmar2000 • March 21, 2017 • Coursework • 391 Words (2 Pages) • 1,048 Views
- Find Carriers who have not delivered to more than 1 city (5 pts)
SELECT C.CARRIER_NAME
FROM CARRIER C
WHERE EXISTS (
SELECT s1.carrier_name,
count (DISTINCT s1.DESTINATION_CITY) CNT
FROM shipment s1
WHERE S1.CARRIER_NAME = C.CARRIER_NAME
GROUP BY s1.carrier_name
HAVING CNT <= 1
);
[pic 1]
- Find businesses who have never shipped to Seattle (5 pts)
SELECT b1.business_name
FROM business b1
WHERE b1.business_name NOT IN (
SELECT b.business_name
FROM Business b,
Shipment S
WHERE b.business_id = s.business_id AND
s.destination_city = "Seattle"
);
[pic 2]
- Find Carrier(s) with the most number of packages shipped (5 pts)
SELECT MAX(mycount),
s.carrier_name
FROM (
SELECT s.carrier_name,
count(s.carrier_name) mycount
FROM shipment s
GROUP BY s.carrier_name
);
[pic 3]
- Find total employee count of businesses who have shipped at least once to Jacksonville (5 pts)
SELECT sum(b.num_employees) Total_employees
FROM business b
WHERE EXISTS (
SELECT s.business_id
FROM shipment s
WHERE s.destination_city = "Jacksonville" AND
s.business_id = b.business_id
);
[pic 4]
- Find businesses who work with carriers who have never delivered to Santa Clara (5 pts)
SELECT b.business_name
FROM business b
WHERE b.business_id IN (
SELECT DISTINCT (s1.business_id)
FROM shipment s1
WHERE s1.carrier_name IN (
SELECT c.carrier_name
FROM carrier c,
shipment s
WHERE c.carrier_name NOT IN (
SELECT s.carrier_name
FROM shipment s
WHERE s.destination_city = "Santa Clara"
)
)
);
[pic 5]
PART –II
Provide a real life example of a schema that demonstrates a multi-valued dependency. Do not use the example presented in the class or your text book. Show how you convert this schema to 4th Normal form to eliminate this multi-valued dependency. (5 pts)
Ans: A simple example of MVD(Multi Valued Dependency) can be of Favourite Movie and Favourite snack during movie
Cust_Id | Favourite Movie | Fav_snack_during_movie |
401 | Inception | Pop Corn |
401 | Hateful Eight | Pop Corn |
401 | Inception | Cheese Ball |
401 | Hateful Eight | Cheese Ball |
The above Database shows that Favourite Movie and Favourite snack are independent multi-valued facts for the same customer(Cust_id ) so it has MVD(Multi Valued Dependency).
...
...