SQL queries. Will someone assist me in the following queries?

Question

I need help with the following queries for a sample database. I appreciate anyone who can offer their assistance,

The sample database has the following tables:

Department (DeptNo, DeptName, Office, Phone)
Employee (EmpNo, FirstName, LastName, JobTitle, HireDate, Salary, MgrNo, Deptno)
Customer (CustNo, CompanyName, Street, City, State, Zip, Phone, CreditLimit, AcctRepNo)
CustomerOrder (OrderNo, OrderDate, ShipDate, CustNo)
OrderLine (OrderNo, ProductNo, Quantity, ActualPrice)
Product (ProductNo, Description, QtyOnHand, MinStockLevel)
Price (ProductNo, StartDate, EndDate, StdPrice, MinPrice)



5. List the product number, product description, and order date for each time a tennis-related product was ordered by a customer in the state of Florida in 2006. (Assume that tennis-related products all have the word Tennis somewhere in their product description.) Order the list by product number, with a secondary sort by order date, from oldest order to newest order. Use the default column headings.

6. For all customers, list the company name and the total number of orders placed by the customer. Include all customers, even those who have never placed an order. Order the customers in the list by the number of orders placed, from most to fewest. For any customers with the same number of orders, arrange them within the list alphabetically by company name. Use Customer Name and Order Count as the column headings.

7. For all orders with a total price either over $7,000 or under $1,000, list the company name of the customer placing the order, the last name of the salesperson handling the customer’s account, the order number, and the total price for the order. Sort the orders from highest total price to lowest total price. For column headings use: Customer, Salesperson, Order, and Order Total.

8. For all customers that have never placed an order, list the customer’s company name, telephone number, account representative last name, and the last name of the manager of the account repre-sentative. Order the list by company name. For column headings, use: Company, Telephone, Salesperson, and Manager.

Answer 1

Is this MsSql or MySql or Oracle or PL/Sql or something else?



5.)

SELECT

OrderLine.ProductNo,

Product.Description,

CustomerOrder.OrderDate

FROM

CustomerOrder,

Customer,

OrderLine,

Product

WHERE

Product.Description LIKE '%tennis%' AND

Customer.State = 'FL' AND

YEAR(CustomerOrder.OrderDate) = 2006 AND

OrderLine.OrderNo = CustomerOrder.OrderNo AND

Customer.CustNo = CustomerOrder.CustNo AND

Product.ProductNo = OrderLine.ProductNo

ORDER BY

Product.ProductNo DESC,

CustomerOrder.OrderDate DESC



6.)

SELECT

a.CompanyName AS 'Customer Name',

SUM(c.Quantity) AS 'Order Count'

FROM

Customer AS a

INNER JOIN

CustomerOrder AS b

ON b.CustNo = a.CustNo

LEFT JOIN

OrderLine AS c

ON c.OrderNo = b.OrderNo

ORDER BY

a.CompanyName ASC



I hope this gave you an idea on how to go about #7 and #8...

Do your homework so that you'll learn it!

Good luck!

Answer 2

It's always easiest to break complex queries down stepwise and build them up from the inside out. I'll go thru how I would address #6, which should help you figure out the others:



First, get a list of all customer numbers:



SELECT CustNo FROM Customer



Next, get a count of their orders...since we want to include those who have never ordered, we need to do an outer join instead of inner:



SELECT c.CustNo, COUNT(*) AS OrderCount FROM Customer c

LEFT OUTER JOIN CustomerOrder o ON c.CustNo = o.CustNo

GROUP BY c.CustNo



(Note: This SHOULD return a 0 in OrderCount if the customer never made an order...in the event it returns a null with your DBMS, try COALESCE(COUNT(*), 0) instead.)



Now all we have to do is get the company name...we could join the result table above with Customer for the company name needed, but we can simply return it as a column function result instead and order it appropriately:



SELECT MAX(CompanyName) AS "Customer Name", COUNT(*) AS "Order Count"

FROM Customer c LEFT OUTER JOIN CustomerOrder o ON c.CustNo = o.CustNo

GROUP BY c.CustNo

ORDER BY 2 DESC, 1



A few hints on 7 and 8: for 7, you'll do a GROUP BY and SUM to figure out the total price and add a HAVING criteria to limit the results. For 8, you could either GROUP BY HAVING COUNT(*) = 0 and make all your select columns the result of column functions as I did with CompanyName above or you could do a WHERE CustNo NOT IN (subselect)

Answer 3

Ugh, a sprint rusty on my sq., and it would help if i will actually try the question, yet once you could try some thing like this: decide on su.sname, count selection(*) from shipments sh, providers su, areas pa the place sh.snumber = su.snumber and sh.pnumber = pa.pnumber and count selection(*) > 2 team by skill of pa.pnumber possibly somebody can advance my answer. stable success!