Question

can someone solve these quick please and thank you!

© Employees 8 Employeeld LastName Categories 9 CategoryID CategoryName Description Picture Suppliers Supplier CompanyName ContactName Contact Title Address FirstName Title TitleOfCourtesy Birth Date City Region PostalCode HireDate Address Orders 8 Order D Customer Employeeld Order Date Required Date Shipped Date ShipVia Freight ShipName ShipAddress ShipCity Ship Region ShipPostalCode Ship Country City Order Details OrderID ProductID UnitPrice Quantity Discount Country Phone Fax HomePage Region Postal Code Country HomePhone Extension Photo Notes Reports To PhotoPath Shippers ShipperID Company Name Phone Products 8 ProductID ProductName SupplierlD CategoryID QuantityPerUnit Unit Price UnitsInStock Units OnOrder Reorderlevel Discontinued Region 8 RegionID RegionDescription Customers Y Customer D CompanyName ContactName ContactTitle Address City Region PostalCode Country Phone Fax Employee Territories 8 Employeeld TerritoryID Territories TerritoryID TerritoryDescription RegionID

sql chapter 4

Query #1: List the company name, contact name, contact title and the phone number for customers who HAVE NOT put in an order. Use an outer join.

Query #2: Create a listing displaying the employee first name, last name and the full name (First name space Last Name) of the person they report to (Supervisor). This is a self-join. If an employee does not report to anyone then they should not appear in the result set.

Query #3: This query will need 6 tables. Display the following: Customer ID, customer company name, employee last name who wrote the order, order date, order product name, order product quantity, and the company name of the supplier who provides the product. This should only contain orders that Buchanan (employee last name) created for customer id SAVEA. It should only be for orders written (OrderDate) for the month of July regardless of year – use the Month(date field) function.

Query #4: List the product name and the supplier name distinctly for those products that meet the following criteria: there had to be at least a quantity of 100 placed on an order, the products should be within the following categories: Beverages, Meat/Poultry, Dairy Products, Seafood. Order the list by product name.

Query #5: Create a union that combines the following result sets: The first interim result set should display the literal ‘Discounted’, product ID, name, unit price, quantity, product sale amount which is a calculated field. (UnitPrice * Quantity) * (1 - Discount) AS ProductSale. This should be for discounted products (located in the OrderDetails table where discount>0) only where a quantity of 100 or more were placed for an order. The second interim result set should display the literal ‘No Discount’, product ID, name, unit price, quantity, product sale which should be setup as follows: (UnitPrice * Quantity) AS ProductSale. This should be for non discount products (discount=0) where a quantity of 100 or more were placed for an order.

Query #6: Create a query that will display an employee first name and last name uniquely for those employees that have sold at least one of the following products: Boston Crab Meat, Chai, and Sir Rodney's Scones. Make sure you bring in the correct number of tables to perform this query, a Cross Join SHOULD NOT BE USED.

Query #7: List the cities for an employee that is common to a customer city location. YOU MUST use the Intersect operator.

Query #8: This query will involve 7 tables. Make sure a CROSS JOIN is NOT used. Display the employee first name, last name, Territory description, unit price at the time of the order, quantity ordered, product name, region description for order quantities within the range of 10 and 25, territory of Atlanta, within the Southern region, unitprice of 10 dollars or less, and the product of Filo Mix.

Answer 1

1)

SELECT c.CompanyName, c.ContactName, , c.ContactTitle, c.Phone FROM Customers c LEFT OUTER JOIN Orders o ON c.CustomerID = o.CustomerID;

2)

SELECT e.FirstName, e.LastName, concat(s.FirstName, ' ', s.LastName) AS SupervisorName FROM Employees e JOIN Employees s ON e.ReportsTo = s.EmployeeID;

3)

SELECT c.CustomerID, c.CompanyName, e.LastName, o.OrderDate, p.ProductName, od.Quantity, s.CompanyName FROM Customers c, Employees e, Orders o, OrderDetails od, Products p, Suppliers s WHERE c.CustomerID = o.CustomerID and e.EmployeeID = o.EmployeeID and o.OrderID = od.OrderID AND od.ProductID = p.ProductID AND p.SupplierID = s.SupplierID AND e.LastName = 'Buchanan' AND c.CustomerID = 'SAVEA' AND month(o.OrderDate) = 7;

NOTE: As per HOMEWORKLIB POLICY I am allowed to answer specific number of questions (including sub-parts) on a single post. Kindly post the remaining questions separately and I will try to answer them. Sorry for the inconvenience caused.