The Board of Directors of Northwind have appointed Doug Hanus at their new CEO a
ID: 3880408 • Letter: T
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/23/2017.
Please type your SQL statements without the database file.
1. A listing of Northwind’s customers.
2. A listing of Northwind’s customers based in UK and France.
3. A listing of Northwind’s product categories and the description of each product category.
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.
5. With the intent of expanding operations in Germany, a count of the cities where Northwind has customers in Germany and a listing of those cities.
6. A count and a listing of the cities and corresponding countries in North America (USA, Canada, Mexico) where they have a customer base.
7. 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). [3 pts] 8. A listing of the product names and its corresponding category for products from suppliers based in Germany and France.
9. For orders having OrderID between 10250 and 10265, a listing of the corresponding customer, employee who accepted the order, and the shipper. The listed must be sorted alphabetically by customer name.
10. A listing of the product names and supplier names for all products that make up OrderIDs 10254 and 10260.
Explanation / Answer
Hey,
The DB schema is needed to run and test the SQL statements. But as you have mentioned that it has to be done without the database file, I am just using generic terms for relations(tables) and fields(columns).
1. Select * from Customers;
2. Select * from Customers where Country="UK" or Country="France";
3. Select category, Description from Product_Categories;
4a. Select distinct(Country) from Customers ;
4b. Select distinct(Country) from Suppliers;
5. Select City, count(*) from Customers where Country="Germany" group by City;
6. Select City, count(*) from Customers where Country in ("USA","Canada","Mexico") group by City;
7a. Select Sum(Cost), AVG(Cost), Min(Cost), Max(Cost) from Products;
7b.Select Name from Products where Cost between 30 and 50;
8. Select Products.name, Products.category from Products, Suppliers where Products.supplierId=Suppliers.supplierID and Suppliers.Country in ("Germany","France");
9. Select Customers.name, Employee.name, Suppliers.name from Orders, Employee, Customers,Suppliers where Orders.OrderId between 10250 and 10265 and Orders.customerId=Customers.Id and Orders.EmployeeId=Employee.Id and Orders.supplierId=Suppliers.supplierId;
10. Select Products.name, Suppliers.name from Products,Suppliers,Orders where Orders.OrderId in (10254,10260) and Orders.supplierId=Suppliers.supplierId and Orders.ProductId=Products.Id ;
Kindly let me know if you have any doubts in the above SQL statements!
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.