MyEducator SQL Exam Chap 2 4||Newest Upload ||Graded A+

MyEducator SQL Exam Chap 2-
4||Newest Upload ||Graded A+
Exam Chap 2 - LorenzoShippingDB -CORRECT ANSWER

What is the name of the customer whose Customer ID is 989? -CORRECT
ANSWERSelect cust_name from customer where cust_id = 989

What is the customer name, the annual revenue and customer type of customer with ID
of 1592? -CORRECT ANSWERSelect cust_name, annual_revenue, cust_type from
customer where cust_id = 1592

What are the truck ID's of trucks that have carried small shipments. Small shipments are
defined as those that weigh less than 800 pounds? (HInt: Avoid duplcates.) -CORRECT
ANSWERSelect distinct truck_id from shipment where weight < 800

Give all the data for shipments weighing over 15000 pounds. (Use *) -CORRECT
ANSWERSelect * from shipment where weight > 15000

Create a list in alphabetical order of names of customers with more than $10 million in
annual revenue -CORRECT ANSWERSelect cust_name from customer where
annual_revenue > 10000000 order by cust_name

What is the customer ID and type of customer for the customer named "Autoware Inc"?
-CORRECT ANSWER

Give names and average monthly revenue of customers having annual revenue
exceeding $20 million. Name the column Average_Monthly_Revenue with a literal (note
the underscores). (Hint: Use division.) -CORRECT ANSWERSelect cust_name,
annual_revenue/12 as Average_Monthly_Revenue from customer where
annual_revenue > 20000000

Using the IN operator, give the city names and their populations of the cities in the
database in North or South Dakota, Wyoming, or Montana. -CORRECT
ANSWERSelect city_name, population from city where state IN ('North Dakota', 'South
Dakota ','Wyoming','Montana')

Give ID's, names, and states of cities with names starting with 'C'. -CORRECT
ANSWERSelect city_id, city_name, state from city where city_name like 'C%'

Give names, states, and areas of cities with names ending with 'City' and with areas
greater than 10 but less than 50. Use the BETWEEN operator. -CORRECT
ANSWERSelect city_name, state, area from city where city_name like '%City' and area
between 10 and 50

, Give names and states of customers that have a "D" as the third character in their
names. (Note: SQL Server data is not case sensitive.) -CORRECT ANSWERSelect
cust_name, state from customer where cust_name like '__D%'

Give names of all customers that are retailers. -CORRECT ANSWERSelect cust_name
from customer where cust_type = 'retailer'

List without duplication all the customer types from the customer table. -CORRECT
ANSWERSelect distinct cust_type from customer

Give names of all customers who are not retailers. -CORRECT ANSWERselect
cust_name
from customer
where NOT cust_type = 'retailer'

Give names of all customers whose type is not retailer or wholesaler and who have over
$20 million in annual revenue. Use the NOT IN keywords. -CORRECT ANSWERSelect
cust_name from customer where cust_type NOT IN ('retailer', 'wholesaler') AND
annual_revenue > 20000000

Exam Chap 3 LorenzoShippingDB -CORRECT ANSWER

What are the names of customers who have sent shipments to El Paso? (Hint: Avoid
duplicates.) -CORRECT ANSWERSelect distinct cust_name from customer c join
shipment s on c.cust_id = s.cust_id join city ci on s.city_id = ci.city_id where
ci.city_name = 'El Paso'

To what cities have customers with revenue less than $5 million sent shipments? (Hint:
Avoid duplicates.) -CORRECT ANSWERSelect distinct city_name from city ci join
shipment s on ci.city_id = s.city_id join customer c on s.cust_id = c.cust_id where
annual_revenue < 5000000

What are the names and populations of cities that have received shipments weighing
over 16000 pounds? (Hint: Avoid duplicates.) -CORRECT ANSWERSelect distinct
city_name, population from city ci join shipment s on ci.city_id = s.city_id where weight
> 16000

List without duplication the model years of trucks that have delivered shipments to New
York. -CORRECT ANSWERSelect distinct model_year from truck t join shipment s on
t.truck_id = s.truck_id join city ci on s.city_id = ci.city_id where city_name = 'New York'

List without duplication the makes of trucks that have delivered shipments for customers
with less than $1 million in annual revenue. -CORRECT ANSWERSelect distinct make
from truck t join shipment s on t.truck_id = s.truck_id join customer c on s.cust_id =
c.cust_id where annual_revenue < 1000000