4 Queries Required Very Urgent

Question

4 Queries Required Very Urgent

nexus2vijay

SSC-Addicted

Points: 422
More actions
November 12, 2015 at 2:47 am

#313008

Using the attached schema diagram below, develop TSQL script for each question.
Use JOIN wherever applicable (do not use EXISTS!).
Do NOT use variable or temporary table.
4 Tables are below and are related as 1-->2-->3-->4
1)
Customer
Customer ID
CustomerName
Street
City
State
CountryCode
2)
Salesorderheader
SalesOrderID
CustomerID
OrderDate
DueDate
ShipDate
Status
3)
Salesorderdetail
SalesOrderDetailID
SalesOrderID
ProductID
OrderQTY
UnitPrice
LineTotal
4)
Product
ProductID
ProductName
ProductNumber
StandardCost
ListPrice
1. List the top 10 customers for the current year by total sales.
Column List:CustomerName, [Total Sales]
Sorting Order:[Total Sales] (desc)
2. List the customers with the total sales, who placed an order last year but not this year.
Column List:CustomerName, [Total Sales]
Sorting Order:[Total Sales] (desc)
3. List last year’s 10 best-selling products and 10 least-selling products with the total sales.
Column List:ProductName, ProductNumber, [Total Sales]
Sorting Order:[Total Sales] (desc)
4. Reduce Product’s ListPrice by 20% if it did not receive any order for the last two years and the reduced price would be still above StandardCost.
Update ListPrice in Product.
My Answers are below : Kindly correct me where ever required.
For Query 1:
selec t Top 10 c.customername, sum( sod.linetotal) totalsales from c.customer innerjoin s.salesorderdetail on c.customerid = soh.customerid
where datepart( 'year',soh.orderdate) = '2015'
group by c.customername order by totalsales desc
For Query 2:
select c.customername,sum(sod.linetotal) totalsales from c.customer innerjoin s.salesorderdetail on c.customerid = soh.customerid
where datepart('year',soh.orderdate ) = '2014'
groupby c.customername order by total sales desc
For Query3:
select * from
(
select ProductID,ProductName,SUM(OrderQty) TotalQty from salesorderdetail sod join productp
group by produuctname
orderby sum(orderquanity) desc, product name asc) A where rownumber < = 10;
For Query4:
Update Product Set listprice = ( Listprice + 20% ) where datepart ( 'year', orderdate ) between '2014' and '2015.

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Do you get extra points for the readability of your code? If you do (and in fact even if you don't), I'd suggest putting a few line returns and maybe a bit of white space in those queries. And make sure you at least parse the queries - at least one of them has a syntax error.

John

nexus2vijay SSC-Addicted Points: 422 More actions · Answer 2

Hi John,

Thanks for your revert. I'm aware that there are some syntax errors.

Appreciate your turn if you could provide me the actual queries than mentioned John.

Thanks,

Vijay

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

homework I assume?

have you actually set up some sample tables and data and run your answers to see if they are correct?

or are you looking for any easy way out?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

nexus2vijay SSC-Addicted Points: 422 More actions · Answer 4

Hi,

It's a test actually.

Nope infact i don't have setup to test the same. I have just given the logical approach.

Appreciate if you could help me with that sir.

Vijay

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 5

nexus2vijay (11/12/2015)
Hi,
It's a test actually.
Nope infact i don't have setup to test the same. I have just given the logical approach.
Appreciate if you could help me with that sir.
Vijay

A "test" and its "Very Urgent" ......hmmmm?

care to explain a little more so that we understand your urgency?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

J Livingston SQL (11/12/2015)
homework I assume?
have you actually set up some sample tables and data and run your answers to see if they are correct?
or are you looking for any easy way out?

I'm reposting this because it's very important. You MUST create those tables and populate them with sensible sample data. It's a) part of your homework assignment, b) next to impossible to avoid syntax errors without running your queries against data and c) a vital part of the development process.

Take the time to set up the sample data scripts and have a play with your queries. Folks here will be happy to assist you with correcting your queries and perhaps helping you to script alternatives. On the other hand, if you ask others to do your homework for you, how are you going to learn? These exercises are trivial compared with real-life scenarios - which are harder than you can possibly imagine right now. Get cracking!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 7

Vijay,

If you're aware there are syntax errors, why didn't you fix them before posting the code?

I'm curious what kind of class you're in where you don't have the resources to test your code. Any school worth its salt gives its students test databases and query tools for the duration of class time.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 8

While we're happy to help you, if we do the simple stuff for you instead, you won't learn the basics. Unless you have a good foundation, you won't be able to learn anything more advanced. Chris is right, this is pretty simple stuff. Having us simply do the work for you would get you a good grade on the test, but it will be doing you a tremendous disservice. If you're going to be doing this for a living one day, you need to understand how they work.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Lowell SSC Guru Points: 323518 More actions · Answer 9

if you go to http://sqlfiddle.com/ you can create and test a suite of commands, without having SQL installed; it's basically a syntax checker and pass through page that gets you access to a SQL instance.

you can test your code directly there.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Luis Cazares SSC Guru Points: 183706 More actions · Answer 10

The syntax errors would be the least of your problems if you're not expected to run your code against a database. You're missing half of the logic for your queries. Logic errors are worse than syntax errors because they'll allow the code to run and show wrong results. You're also including columns which are not available in the tables used on the queries.

I'll highlight the logic that you're missing:

1. List the top 10 customers for the current year by total sales.
Column List:CustomerName, [Total Sales]
Sorting Order:[Total Sales] (desc)
2. List the customers with the total sales, who placed an order last year but not this year.
Column List:CustomerName, [Total Sales]
Sorting Order:[Total Sales] (desc)
3. List last year’s 10 best-selling products and 10 least-selling products with the total sales.
Column List:ProductName, ProductNumber, [Total Sales]
Sorting Order:[Total Sales] (desc)
4. Reduce Product’s ListPrice by 20% if it did not receive any order for the last two years and the reduced price would be still above StandardCost.
Update ListPrice in Product.

If you're trying to get a job which involves SQL, please read about SARGable queries.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

nexus2vijay (11/12/2015)
Hi,
It's a test actually.
Nope infact i don't have setup to test the same. I have just given the logical approach.
Appreciate if you could help me with that sir.
Vijay

Is this a test for a class or job?

If a class, you are way off. If for a job, you aren't ready for it and need to learn more.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 12

Lynn Pettis (11/12/2015)
nexus2vijay (11/12/2015)
Hi,
It's a test actually.
Nope infact i don't have setup to test the same. I have just given the logical approach.
Appreciate if you could help me with that sir.
Vijay
Is this a test for a class or job?
If a class, you are way off. If for a job, you aren't ready for it and need to learn more.

It's a test and very urgent? hmmm

... Some things are better left unsaid. ...

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

pietlinden SSC Guru Points: 63503 More actions · Answer 13

pietlinden

SSC Guru

Points: 63503

November 12, 2015 at 1:08 pm

#1839494

I kinda hope he has to explain his answers....

ScottPletcher SSC Guru Points: 101103 More actions · Answer 14

He wants to do nothing himself. I tried to walk him thru the logic of how to code #4 in his original q, here:

http://forums.sqlteam.com/t/need-help-case-statement-should-work/4070

but he refused, instead basically just "demanding" the fully-coded answer.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".