Question

The Board of Directors of Northwind have appointed Doug Hanus at their new CEO and Jeff Hogan as their operational manager. They are planning to devote their first two weeks in office to gain a better understanding of Northwind’s supply chain and marketing processes. As senior database analyst for Northwind, it is your responsibility to code appropriately structured SQL statements for retrieving the following information requested by Doug and Jeff. They need it on or before 09/29/2019.

1. A listing of Northwind’s suppliers. [1 pt.]

2. A listing of Northwind’s suppliers based in Norway and Sweden. [2 pts]

3. A listing of Northwind’s product categories and the description of each product category. [2 pts]

4. A clearer picture of the geographical footprint of their customers and suppliers - they want separate listings of the countries in which their customers and suppliers are located. [2 pts]

5. The average, sum, maximum, and minimum per unit cost across all products in their product line. They would also like to know the names of Northwind’s products having per unit cost between 30 and 50 (both inclusive). [2 pts]

6. A count and a listing of the cities and corresponding countries in North America (USA, Canada, Mexico) where they have a customer base. [3 pts]

7. A listing of the product names and its corresponding category for products from suppliers based in Germany and France. [4 pts]

8. A list of countries outside of North America (i.e. USA, Canada, Mexico) where they have less than 3 suppliers. [4 pts]

9. For OrderIDs 10258, 10259, and 10260, a listing of the corresponding customer, employee who accepted the order, and the shipper. The listed must be sorted alphabetically by customer name. [5 pts]

10. A listing of the product names and supplier names for all products that make up OrderIDs 10254 and 10260. [5 pts]

Answer 1

assuming northwind is the database name and suppliers is the table where the list of suppliers and their details are present

* A listing of Northwind’s suppliers. [1 pt.]

>use Northwind;

>SELECT * FROM suppliers;

*A listing of Northwind’s suppliers based in Norway and Sweden. [2 pts]

>SELECT * FROM suppliers WHERE country !== Sweden,Norway; (here country may be a column)

*A listing of Northwind’s product categories and the description of each product category

>use Northwind;

>SELECT DISTINCT[categories,price,description] FROM product;

* A clearer picture of the geographical footprint of their customers and suppliers - they want separate listings of the countries in which their customers and suppliers are located.

>SELECT customers.column_name, suppliers.column_name, customers.country, suppliers.country * FROM customers,suppliers;

{here you can give all the column names as you wish with the following syntax SELECT table_name.column_name * FROM table_name}

*The average, sum, maximum, and minimum per unit cost across all products in their product line. They would also like to know the names of Northwind’s products having per unit cost between 30 and 50 (both inclusive). [2 pts]

>SELECT sum(product_cost) as sum, MIN(product_cost) as minimum, MAX(product_cost) as maximum FROM product_table;

*A count and a listing of the cities and corresponding countries in North America (USA, Canada, Mexico) where they have a customer base. [3 pts]

>SELECT customer_base * FROM customer WHERE countries=NorthAmerica, USA, Canada, Mexico

*A listing of the product names and its corresponding category for products from suppliers based in Germany and France. [4 pts]

>SELECT product_names, product_id, price FROM product where country== Germany, France;

*A list of countries outside of North America (i.e. USA, Canada, Mexico) where they have less than 3 suppliers. [4 pts]

>SELECT countries *FROM suppliers WHERE count>3

*For OrderIDs 10258, 10259, and 10260, a listing of the corresponding customer, employee who accepted the order, and the shipper. The listed must be sorted alphabetically by customer name. [5 pts]

>SELECT customer_id, employee_id FROM supplier, customer WHERE order.employee=10258,10259,10260 && order.customer== 10258,10259,10260;

* A listing of the product names and supplier names for all products that make up OrderIDs 10254 and 10260.

>SELECT customer_id, employee_id, customer_name, supplier_name FROM supplier, customer WHERE order.employee=10254,10260 && order.customer==10254,10260;

NOTE: here I assumed the table names and column names and gave answer accordingly if you have different names in your database then you need to change the names and use the syntax as it is.