NEED HELP WITH QUERIES PLEASE!

Question

I am trying to create the following queries: 1) Display the order number, customer number, order date, and items ordered for order number 1

2) Total the cose of order number 1

3) Find any customers who have not placed orders

4) Produce a list of all hte products (i.e., product description) and the number of times each product has been ordered

5) List the names and number of employees supervised (label this value HeadCount) for all the supervisors who supervise more than two employees

6) List in alphabetical order the names of all employees (managers) who are managing people with skill ID BS12; list each such manager's name only once, even if htat manger manages serveral people with this kill

7) Show the customer ID and name for all the cutomers who have ordered both products with IDs 3 and 4 on the same order

Answer 1

0

This is homework right ? Or at least Baylor is "A top Texas Christian University" I suggest you re-read your course notes or ask your tutor for further explanation of any points you are unclear of.

answered Nov 11 '09 at 05:47 AM

dave ballantyne
918 ● 1 ● 1 ● 4

I agree with the sentiment, Dave, but I don't get the reference?

Dec 07 '09 at 06:48 PM RBarryYoung

Apologies for the delay, thats where the table script is hosted ....

May 12 '10 at 09:54 AM dave ballantyne

(comments are locked)

Answer 2

I think these queries are basic and something you should learn by yourself to get the understanding of t-sql language, but today I am kind and give you the answers anyway.

I can't find order number or the customer number, but i think you mean order_id and customer_id

Question 1

select o.Order_Date, o.Order_Id, o.Customer_Id, p.Product_Id, p.Product_Description, ol.Ordered_Quantity 
from order_tx o
    inner join ORDER_LINE_TX ol
    	on o.Order_Id = ol.Order_Id
    inner join PRODUCT_TX p 
    	on ol.Product_Id = p.Product_Id
WHERE o.Order_id=1

Question 2 I do not understand what you mean by "cose", so you have to solve it by yourself.

Question 3

SELECT c.* FROM CUSTOMER_TX c
    LEFT JOIN ORDER_TX o
    	ON c.Customer_Id=o.Customer_Id
WHERE o.Order_Id IS NULL

Question 4

SELECT p.Product_Description, COUNT(Order_Id) FROM PRODUCT_TX p
    LEFT JOIN ORDER_LINE_TX ol
    	ON p.Product_Id=ol.Product_Id
GROUP BY p.Product_Description

Question 5

SELECT sv.Employee_Name, COUNT(e.Employee_ID) AS HeadCount FROM EMPLOYEE_TX as e
    INNER JOIN EMPLOYEE_TX AS sv
    	ON e.Employee_Supervisor=sv.Employee_Id
GROUP BY sv.Employee_Name
HAVING COUNT(e.Employee_ID)>1

Qustion 6

SELECT sv.Employee_Name 
FROM EMPLOYEE_TX as e
    INNER JOIN EMPLOYEE_TX AS sv
    	ON e.Employee_Supervisor=sv.Employee_Id
    INNER JOIN EMPLOYEE_SKILLS_TX s
    	ON e.Employee_Id=s.Employee_Id
WHERE s.Skill_Id='BS12'
GROUP BY
    sv.Employee_Name

Question 7 (this code work for SQL 2005)

SELECT  c.Customer_Id
FROM order_tx o
    INNER JOIN CUSTOMER_TX c 
    	ON o.Customer_Id=c.Customer_Id
    INNER JOIN (
    		SELECT Order_Id 
    		FROM ORDER_LINE_TX ol
    		WHERE ol.Product_Id =4
    		INTERSECT		
    		SELECT Order_Id 
    		FROM ORDER_LINE_TX ol
    		WHERE ol.Product_Id =3
    	) AS ol
    	ON o.Order_Id = ol.Order_Id

NEED HELP WITH QUERIES PLEASE!

Follow this question

Related Questions