x

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

more ▼

asked Nov 11, 2009 at 03:47 AM in Default

Heather gravatar image

Heather
1 1 1 1

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?
Nov 11, 2009 at 05:54 AM KenJ
Added homework tag...
Nov 11, 2009 at 06:14 AM Matt Whitfield ♦♦
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.
Dec 07, 2009 at 06:52 PM RBarryYoung
(comments are locked)
10|1200 characters needed characters left

3 answers: sort oldest

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.

more ▼

answered Nov 11, 2009 at 05:47 AM

dave ballantyne gravatar image

dave ballantyne
928 1 1 4

I agree with the sentiment, Dave, but I don't get the reference?
Dec 07, 2009 at 06:48 PM RBarryYoung
Apologies for the delay, thats where the table script is hosted ....
May 12, 2010 at 09:54 AM dave ballantyne
(comments are locked)
10|1200 characters needed characters left

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 
more ▼

answered Nov 11, 2009 at 06:05 AM

Håkan Winther gravatar image

Håkan Winther
15.9k 35 37 48

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.
Dec 02, 2013 at 05:38 AM Jeff Moden
(comments are locked)
10|1200 characters needed characters left
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”).
more ▼

answered Oct 18, 2013 at 05:32 AM

liz_bailey48 gravatar image

liz_bailey48
0

You should post this as a new question so that it's visible on the site homepage.
Oct 18, 2013 at 09:30 AM DaveC ♦♦
Heh... and show that you've actually tried.
Dec 02, 2013 at 05:38 AM Jeff Moden
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1951
x376
x77

asked: Nov 11, 2009 at 03:47 AM

Seen: 6073 times

Last Updated: Dec 02, 2013 at 05:38 AM