Question 9:
Show the average unit price rounded to 2 decimal places, the total units in stock, total discontinued products from the products table.
Solution:
select convert(decimal(18,2),avg(unit_price)),sum(units_in_stock),sum(discontinued) from products
Question 10:
Show the ProductName, CompanyName, CategoryName from the products, suppliers, and categories table
Solution:
select p.product_name,s.company_name,c.category_name from products as p
join categories as c on p.category_id = c.category_id
join suppliers as s on p.supplier_id = s.supplier_id
Question 11:
Show the category_name and the average product unit price for each category rounded to 2 decimal places.
Solution:
select c.category_name,convert(decimal(18,2),avg(p.unit_price)) from categories as c
join products as p on c.category_id = p.category_id
group by c.category_name
Question 12:
Show the city, company_name, contact_name from the customers and suppliers table merged together.
Create a column which contains 'customers' or 'suppliers' depending on the table it came from.
Solution:
select City, company_name, contact_name, 'customers' as relationship from customers
union
select city, company_name, contact_name, 'suppliers' from suppliers