SIMPLE QUERY USING PUBS.

Last Post 26 Apr 2008 06:39 AM by SQLUSA. 6 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
salnasi
New Member
New Member

--
25 Apr 2008 06:44 PM
Using the pubs database how would I write a query to
Return the totals sales dollars of books purchased in the state of CA and where the publisher of the book was not located in the state of CA.

Thanks
No Rush.
SQLUSA
New Member
New Member

--
26 Apr 2008 12:54 AM
Try this:

select TotalNonCASales=sum(s.qty * t.price)
from Sales s
join Titles t
on s.title_id = t.title_id
join Publishers p
on p.pub_id = t.pub_id
where p.state != 'CA'

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
salnasi
New Member
New Member

--
26 Apr 2008 04:37 AM
Thanks SQLUSA.

TSQL is not my strong point just wanted to see how I did on this short quiz.

I have a few more.

1)Return the full name and unique employee identifier of the people employed with the job description
Sales Representative.

2)Return a list of titles published withing the last 3 months.

3)Return the total quantity of all books in each state.

Thanks again.
SQLUSA
New Member
New Member

--
26 Apr 2008 06:39 AM
How about if you give it a first try, and I will check it/ correct it?

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/order2005repo...ngservices
salnasi
New Member
New Member

--
26 Apr 2008 07:31 AM
Sounds good.

Here is what i have.

1)SELECT A.FNAME, A.LNAME, A.EMP_ID
FROM EMPLOYEE A
INNER JOIN JOBS B
ON A.JOB_ID = B.JOB_ID
WHERE B.JOB_DESC = 'SALES REPRESENTATIVE'


2)SELECT TITLE
FROM TITLES
WHERE PUBDATE > GETDATE() - 90

3)SELECT SUM(A.QTY), B.STATE
FROM SALES A
INNER JOIN STORES B
ON A.STOR_ID = B.STOR_ID
GROUP BY STATE

Do you know of any good sites to get some good tsql quizes?

Thanks in advance.

SQLUSA
New Member
New Member

--
26 Apr 2008 08:25 AM
Here they are:
SELECT A.FNAME, A.LNAME, A.EMP_ID
FROM EMPLOYEE A
INNER JOIN JOBS B
ON A.JOB_ID = B.JOB_ID
WHERE B.JOB_DESC = 'SALES REPRESENTATIVE'
GO

-- no return, pubdate is years into past
SELECT TITLE
FROM TITLES
WHERE datediff(mm, PUBDATE, GETDATE()) <= 3
GO



SELECT TotalSales=SUM(A.QTY), B.STATE
FROM SALES A
INNER JOIN STORES B
ON A.STOR_ID = B.STOR_ID
GROUP BY STATE
ORDER BY TotalSales desc
GO


Sites to learn SQL:

http://www.sqlusa.com/contact/registrationquiz/
http://www.sqlusa.com/bestpractices/
http://www.sqlusa.com/bestpractices2005/
http://www.sqlusa.com/bestpractices2008/



salnasi
New Member
New Member

--
26 Apr 2008 09:11 AM
Thanks SQLUSA for your feedback.



Acceptable Use Policy
---