Question

Import the northwind database and write sql statements for the following:

1. List all supplier ids (no duplicates) for all products.
2. List the product name and supplier id for all products that cost more than $20.
3. List all products whose names start with letter "c" and are not discontinued.
4. List each country name and the number of suppliers located in the country.
5. List all supplier names, the number of products that each supplier provides (this column should be named as NumOfProducts), and the average price for the products provided by the same supplier (this column should be named as AvgPrice).
6. Find all suppliers' name, city, and country for those who provide non-discontinued products.
7. List all supplier names who provide at least two different products.
8. Add a new product record to the products table. You must provide values for the following fields in the new product record: ProductID, productName, SupplierID, and CategoryID.
9. Increase the reorder level by 20% and reduce the prices by 10% for all products
10. Delete discontinued products.

Answer 1

• List all supplier ids (no duplicates) for all products.

select DISTINCT SupplierID from Products;

• List the product name and supplier id for all products that cost more than $20.

select ProductName,SupplierID from Products where UnitPrice > 20;

• List all products whose names start with letter "c" and are not discontinued.

select * from Products where ProductName like 'c%' and Discontinued = 0;

• List each country name and the number of suppliers located in the country.

select Country,COUNT(SupplierID) from Suppliers group by Country;

• List all supplier names, the number of products that each supplier provides (this column should be named as NumOfProducts), and the average price for the products provided by the same supplier (this column should be named as AvgPrice).

select s.SupplierID,s.ContactName,COUNT(p.ProductID) as "NumOfProducts",AVG(p.UnitPrice) as "AvgPrice" from Products as p,Suppliers as s where p.SupplierID=s.SupplierID group by s.SupplierID;

• Find all suppliers' name, city, and country for those who provide non-discontinued products.

select s.ContactName,s.City,s.Country from Products as p,Suppliers as s where p.SupplierID=s.SupplierID and p.Discontinued=0;

• List all supplier names who provide at least two different products.

select s.SupplierID,s.ContactName from Products as p,Suppliers as s where p.SupplierID=s.SupplierID group by s.SupplierID having COUNT(p.ProductID)>=2;

• Add a new product record to the products table. You must provide values for the following fields in the new product record: ProductID, productName, SupplierID, and CategoryID.

Here I am inserting sample value:

insert into Products(ProductID,ProductName,SupplierID,CategoryID) values(78,'Coffee',1,1);

• Increase the reorder level by 20% and reduce the prices by 10% for all products

update Products set ReorderLevel=ReorderLevel+(0.2*ReorderLevel),UnitPrice=UnitPrice-(0.1*UnitPrice);

• Delete discontinued products.

delete from Products where Discontinued=1;