November 12, 2015 at 2:47 am
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.
November 12, 2015 at 2:55 am
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
November 12, 2015 at 2:59 am
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
November 12, 2015 at 3:00 am
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
November 12, 2015 at 3:04 am
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
November 12, 2015 at 3:16 am
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
November 12, 2015 at 3:18 am
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!
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
November 12, 2015 at 5:01 am
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.
November 12, 2015 at 5:23 am
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.
November 12, 2015 at 5:44 am
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
November 12, 2015 at 5:47 am
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.
November 12, 2015 at 10:17 am
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.
November 12, 2015 at 10:32 am
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. ...
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]
November 12, 2015 at 1:08 pm
I kinda hope he has to explain his answers....
November 12, 2015 at 2:57 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply