Question

Please answer each question using these tables above, it is SQL

1. Display the descriptions and prices of the 5 most expensive parts, in descending order by price.
2. For each part, display the On Hand Value, which is calculated by multiplying the quantity on hand by price.
3. Display a summary with part number, description, on hand and warehouse for all parts in class HW or AP, and in warehouse 1 or 2.
4. Display the names of customers who do NOT live in the cities of Sheldon, Grove, or Fillmore.
5. Display all of the cities in which customers live, omitting duplicates.
6. Display the names of customers that do not have a zip code that starts with 33.

Ecustomer E Columns IB New balance I city D| credit_limit 4 customer_name customer_num rep_num Dstate street zip Indexes orders HColumns INew customer_num I| order_date | order_num Indexes order_line EHHColumns IB New num_ordered order_num part_num 4I quoted_price Opart E Columns I New class description 4on_hand l part_num price warehouse Indexes rep EHColumns 4 New city commision first_name last_name rate rep_num state street zip

Answer 1

1. Display the descriptions and prices of the 5 most expensive parts, in descending order by price.

SELECT Top 5 description, price FROM part ORDER BY price DESC;

Explaination: Top will fetch top 5 from the list and we are ordering the list by price in descending order.

1. For each part, display the On Hand Value, which is calculated by multiplying the quantity on hand by price.

SELECT on_hand * price as on_hand from part;

Exp: We can use * to multiply two columns and rename that column as on_hand.

1. Display a summary with part number, description, on hand and warehouse for all parts in class HW or AP, and in warehouse 1 or 2.

Select part_num, description, on_hand, warehouse where class in ('HW','AP') and warehouse in (1,2);

Exp: IN function is used to check if the element is present in the given set.

1. Display the names of customers who do NOT live in the cities of Sheldon, Grove, or Fillmore.

Select customer_name from customer where city NOT IN ('Sheldon', 'Grove', 'Fillmore')

Exp: NOT IN is reverse of IN clause.

1. Display all of the cities in which customers live, omitting duplicates.

Select Distinct(city) from customer;

Exp: Distinct is used to omit duplicates.

1. Display the names of customers that do not have a zip code that starts with 33.

Select customer_name from customer where zip like '33%'

Exp: LIKE is used as regular expression and % is used as all the characters. Here I have used '33%' which means match with expression that starts with 33.

Regards.