What are the queries for the following using this schema: 1. What are the names
ID: 3763031 • Letter: W
Question
What are the queries for the following using this schema:
1. What are the names of customers who have sent packages (shipments) to Sioux City?
QUERY:
2. To what destinations have companies with annual revenue less than $1 million sent packages? Remove duplicates from the list.
QUERY:
3. What are the names and populations of cities that have received shipments weighing over 100 pounds?
QUERY:
4. Who are the customers (cust_id and cust_name) having over $5 million in annual revenue who have sent shipments weighing less than 1 pound? Remove duplicates from the list.
QUERY:
5. Who are the customers (cust_id and name) having over $5 million in annual revenue who have sent shipments weighing less than 1 pound or have sent a shipment to San Francisco? Remove duplicates from the list.
QUERY:
6. Who are the drivers who have delivered shipments for customers with annual revenue over $20 million to cities with populations over 1 million? Remove duplicates from the list.
QUERY
7. List the cities that have received shipments from customers having over $15 million in annual revenue. Remove duplicates from the list.
QUERY:
8. List the names of drivers who have delivered shipments weighing over 100 pounds.
QUERY:
9. What is the average weight of a shipment?
QUERY:
10. What is the average weight of a shipment going to Atlanta?
QUERY:
11. How many shipments has customer 433 sent?
QUERY:
12. Which city in the database has the largest population?
QUERY:
13. Which city in the database has the smallest population?
QUERY:
14. What is the total weight of packages (shipments) carried in truck 82?
QUERY:
15. Give a list of customers and annual revenue for those customers in the database whose annual revenue is maximum. (Hint: Use a subquery)
QUERY:
Explanation / Answer
1.
SELECT distinct cust_name FROM customer c,shipment s
where c.cust_id = s.cust_id
and s.destination = 'Sioux City';
2.
select destination from shipment s , customer c
where s.cust_id = c.cust_id
and annual_revenue < 1000000;
3.
select distinct c.city_name , population from city c,shipment s
where c.city_name = s.destination
and weight > 100;
4.SELECT CUST_NAME
FROM CUSTOMER,SHIPMENT
WHERE CUSTOMER.CUST_ID=SHIPMENT.CUST_ID AND
ANNUAL_REVENUE>5000000 AND WEIGHT<1;
5.
SELECT CUST_NAME
FROM CUSTOMER,SHIPMENT
WHERE CUSTOMER.CUST_ID=SHIPMENT.CUST_ID AND
ANNUAL_REVENUE>5000000 AND
( DESTINATION='San Francisco'OR WEIGHT<1 ) ;
6.SELECT DRIVER_NAME
FROM TRUCK
WHERE TRUCK_#
IN
(SELECT TRUCK_#
FROM SHIPMENT,CUSTOMER,CITY
WHERE SHIPMENT.CUST_ID=SHIPMENT.CUST_ID AND ANNUAL_REVENUE>20000000 AND CITY.CITY_NAME=SHIPMENT.DESTINATION AND POPULATION>1000000 );
7.
SELECT DESTINATION
FROM SHIPMENT,CUSTOMER
WHERE CUSTOMER.CUST_ID=SHIPMENT.CUST_ID
AND ANNUAL_REVENUE>15000000;
8.
SELECT DRIVER_NAME
FROM TRUCK
WHERE TRUCK_#
IN
(SELECT TRUCK_#
FROM SHIPMENT
WHERE WEIGHT>100);
9.
Select avg(weight) from shipment
10.Select avg(weight) from shipment where destination='atlanta'
11.
SELECT count distinct cust_name FROM customer c,shipment s
where c.cust_id = s.cust_id
and custom_id=433;
12.select city_name from city
where population= (select max(population) from city);
13.select city_name from city
where population= (select min(population) from city);
14. select SUM(weight) from shipment where truck#='82';
15.select cus_name, annual_revenue from customer
WHERE annual_revenue-(select max(annual_revenue) from customer);
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.