question

Heather avatar image
Heather asked

NEED HELP WITH QUERIES PLEASE!

Table Script

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

sql-server-2005queryhomework
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
A homework tag might be useful for this question. Do you have some queries already started that you need help fine-tuning, or are you wondering where to start?
1 Like 1 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
Added homework tag...
0 Likes 0 ·
RBarryYoung avatar image RBarryYoung commented ·
Heather: If this is homework, then you need to be careful about posting these kinds of questions. Many schools will fail or even expel students for doing this. You should check your school's policy on what kind of help you can receive on assignments.
0 Likes 0 ·
dave ballantyne avatar image
dave ballantyne answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered

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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image Jeff Moden commented ·
I know this is a wicked old post but, Wow. While I strongly believe in helping others, I don't believe in doing homework for other people especially when they haven't tried on their own. The OP showed nothing of a decent try or any effort (didn't even take the time to spell "cost" correctly). My biggest resentment to such a thing is two-fold... the OP learned little because he's already demonstrated a bit of laziness and won't go back to your examples to figure things out and that will make him stupid when it comes to SQL Server. The other resentment is that I might have to work with him some day and he won't be able to pull his weight.
0 Likes 0 ·
liz_bailey48 avatar image
liz_bailey48 answered
Hi I have a homework question for PRG140. The queries are already created in this database. The question ask the following: Find the name of each publisher containing the word “and”. (HINT: Be sure that your query selects only those publishers that contain the word “and” and not those that contain the letters “and” in the middle of a word. For example, your query should select the publisher named “Farrar Straus and Giroux,” but should not select the publisher named “Random House”).
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DaveC avatar image DaveC ♦♦ commented ·
You should post this as a new question so that it's visible on the site homepage.
0 Likes 0 ·
Jeff Moden avatar image Jeff Moden commented ·
Heh... and show that you've actually tried.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.