[Solved] Northwind Database Schema Region Regionid Pk Regiondescription Regionid Orders Orderid Pk Q37235427
Northwind database schema: * Region(regionID: PK, regionDescription) regionID * Orders(orderID: PK, customerID, employeeID, orderDate, requiredDate, shippedDate, * shipVia, freight, shipName, shipAddress, shipCity, shipRegion, shipPostalCode, shipCountry) * Order_Details(orderID: PK, productID: PK, unitPrice, quantity, discount) * Categories(categoryID: PK, categoryName, description, picture) * Employees(employeeID: PK, lastName, firstName, title, titleOfCourtesy, birthDate, hireDate, * address, city, region, postalCode, country, homepage, extension, photo, notes, reportsTo, photoPath) * EmployeeTerritories(employeeID: PK, territoryID: PK) * Territory(territoryID: PK, territoryDescription, regionID) * Customers(customerID: PK, companyName, contactName, contactTitle, address, * city, region, postalCode, country, phone, fax) * CustomerCustomerDemo(customerID: PK, customerTypeID: PK) * CustomerDemographics(customerTypeID: PK, customerDesc) * Shippers(shipperID: PK, companyName, phone) * USStates(stateID: PK, stateName, stateAbbr, stateRegion) * Products(productId: PK, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued) * */– Simple Queries/* Query 1:Find all customers who are located in ‘USA’8//* Query 2:Find all customers who are located in ‘USA’, and for each customerdisplay the company name, and region.*//* Query 3:Find all products which are discontinued and with a unit price less than 50,and for each, display the following: productname, unitprice, and unitsinstock,under the column names: “Discontinued Products”, “Unit Price”, and “Units in Stock”, respectively.(Hint: use “Discontinued Products” as an alias)*//* Query 4:For each employee display: the employee full name, andthe employee age when he/she was hired (age in days can be computed by: hiredate – birthdate)*//* Query 5:Find all product names that consist of three words or more(Hint: the pattern ‘% %’ matches strings with at least one blank, for example ‘Ipoh Coffee’)*/– Complex Queries/* Query 6:For each product, display the product name and, its category name(find the answer using cross product)*//* Query 7:Repeat query #6, but using natural join. Sort the tuples by category name.*//* Query 8:Find all product pairs that come from the same supplier (same supplierid).Make sure not to list the same pair twice.(hint: consider using self-join on products, and tuple variables)*//* Query 9:Find all products that were shipped either to Poland or Norway (or both)*//* Query 10:Repeat query#3, but using set intersection. Display only the product name, and price.Sort the tuples by product name into descending order.(sort after taking the intersection)*//* Query 11:Find all products with a unitprice above the average unitprice. For each, display only the product name, and the unitprice.Sort by productname. Use a subquery in the WHERE clause to find the average unit price.*//* Query 12:Find all products with a unitprice greater than the unitprice for all seafood-products,and for each display the product name and unitprice, sorted by productname.First collect all the seafood-product unitprices using a subquery,then in the parent query compare product unit prices to prices in the child subquery using the operator (> ALL).*//* Query 13:For each category display the category name, minimum product unitprice, and maximum product unitprice, for products in the category.Sort the results by categoryname.*//* Query 14 (optional: attempt for bonus):Find the suppliers who supplied products that were shipped to Brazil in 1996. For each supplier displaythe supplier (company) name, and the product name. Use a subquery in the FROM clause to collectthe product names, and the supplier IDs for those products shipped to Brazil in 1996.Then perform a natural left join between suppliers and the constructed relation, and display the required output.Suppliers with no products shipped to Brazil in 1996, will appear in the results as well.To extract year from dates, use the function date_part(‘year’, orderdate).*/
Expert Answer
Answer to Northwind database schema: * Region(regionID: PK, regionDescription) regionID * Orders(orderID: PK, customerID, employe… . . .
OR
