November 12, 2015 at 3:14 pm
I hope he's patient... osmosis takes a LONG time.
November 13, 2015 at 4:55 am
ScottPletcher (11/12/2015)
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.
Why did he delete his own posts, I wonder?
November 13, 2015 at 7:26 am
Brandie Tarvin (11/13/2015)
ScottPletcher (11/12/2015)
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.
Why did he delete his own posts, I wonder?
Why? My guess, destroying the evidence.
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 13, 2015 at 8:11 am
nexus2vijay (11/12/2015)
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.
This is actually pretty sickening. Why do you even bother going to school if you're not going to even try. You should drop the course if this is the way you intend to pass your courses.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 13, 2015 at 11:29 am
I will say this, though: I think that's a pretty good basic test. Covers the fundamentals of creating and populating tables, relationships, and some moderately complex querying. We didn't do anything remotely this cool when I took an Oracle class a very long time ago, and any other database class that I've taken was more about learning the system commands than learning good database theory.
In fact, I think I'm going to work it out myself for my own satisfaction: I've almost always worked in government which never required business-like operations like this.
Pity the OP didn't put too much effort behind trying to work it out.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
November 13, 2015 at 12:46 pm
Jeff Moden (11/13/2015)
This is actually pretty sickening. Why do you even bother going to school if you're not going to even try. You should drop the course if this is the way you intend to pass your courses.
Amen. The world already has enough 9-to-5-ers who always do the same thing the same way and never even realize they're in a box. 😉
Then again, education has become more about getting a piece of paper than about learning. It's a sad situation where people pay such huge sums of money for a piece of paper and don't take advantage of the opportunity to learn something.
November 13, 2015 at 12:59 pm
I've been looking at the data (i.e., the column names) and I just realized this is Adventure works. So I looked again at the problems, actually reading them this time without looking at the OPs code.
Even the most complicated version of the problem is like my SQL 102 class. Basic JOIN / subquery stuff.
I wonder if we should start putting this kind of stuff in our ad-hoc "here's a PC, let's see how you do" interviews. Assuming they pass the "get the current date and time" question.
November 13, 2015 at 1:02 pm
Wayne West (11/13/2015)
I will say this, though: I think that's a pretty good basic test. Covers the fundamentals of creating and populating tables, relationships, and some moderately complex querying. We didn't do anything remotely this cool when I took an Oracle class a very long time ago, and any other database class that I've taken was more about learning the system commands than learning good database theory.In fact, I think I'm going to work it out myself for my own satisfaction: I've almost always worked in government which never required business-like operations like this.
Pity the OP didn't put too much effort behind trying to work it out.
Having actually studied engineering (before there were personal computers), I never took a theory class. I have often thought about taking one, but never get around to it.
November 13, 2015 at 1:11 pm
Brandie Tarvin (11/13/2015)
I've been looking at the data (i.e., the column names) and I just realized this is Adventure works. So I looked again at the problems, actually reading them this time without looking at the OPs code.Even the most complicated version of the problem is like my SQL 102 class. Basic JOIN / subquery stuff.
I wonder if we should start putting this kind of stuff in our ad-hoc "here's a PC, let's see how you do" interviews. Assuming they pass the "get the current date and time" question.
To be fair, I believe that #4 -- the update of the price of any product that had not been ordered in two years but only if the updated price was still greater than the product cost -- requires a sophisticated query. That's why I wanted to walk thru that specific one with the poster: if he can learn that one, the rest should be easy :-D.
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".
November 13, 2015 at 1:46 pm
djj (11/13/2015)Having actually studied engineering (before there were personal computers), I never took a theory class. I have often thought about taking one, but never get around to it.
I really enjoyed programming theory classes, unfortunately now at the school that I went to they no longer teach it as its own class: they teach a language and whatever theory is appropriate for it, which means students sometimes have problems applying what they learned to new programming environments.
For me, I started taking community college classes while in high school. I was the first class series after they stopped using punch cards and switched to VDTs and Decwriters, along with some KSR-33 teletypes with dial-up connections. I took every computer class that I could, but in an unstructured way so I never got a degree. The database classes that I took were heavy on relational design and normalization, the first microcomputer DB class that I took used R:Base 4000, which had really good relational integrity enforcement as I recall. Then it was on to dBase/Foxbase, Wang Pace and finally SQL Server 4.
Unfortunately our local college only teaches Access as of the last time that I looked, and the university wants people to be Java coders, which I think is ridiculous. I've thought about offering to teach SQL Sever locally as I've conducted several classes before, but I don't have the time with working full-time. So I remain self-taught and I do pretty good for myself and my employers.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
November 13, 2015 at 2:18 pm
Alright, nexus2vijay... Putting the teaching hat back on, let's work on the first problem...
Here's the problem as you listed it in the original post on this thread...
1. List the top 10 customers for the current year by total sales.
Column List: CustomerName, [Total Sales]
Sorting Order: [Total Sales] (desc)
... and here's the answer that you posted...
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
[font="Arial Black"]Observation #1: Current Year[/font]
Now... look back at the question. What is it asking? The first thing to notice is that it's asking for some type of rows for the "CURRENT YEAR". If you run your solution next year, will it return the rows for the "CURRENT YEAR"? NO. It'll return rows for 2015 but that's not the "CURRENT YEAR".
How to fix that problem? Not like a lot of people might think. Most would think that you could simply get the year of the current date using something like the following formula (or any of the functions that return the current date and time) and, because of "Observation #2" below, they'd be only partially correct.
YEAR(CURRENT_TIMESTAMP)
[font="Arial Black"]Observation #2: Non-SARGable Predicate[/font]
"SARG" stands for "Search ARGument". A "SARGable Predicate" is a search condition where an Index SEEK can be used if an index on the proper columns is available. A "NON-SARGable" Predicate is a search condition where an index SEEK is impossible no matter what kind of index you use.
Your WHERE clause is totally NON_SARGable. The thing that makes it so is the fact that you've wrapped the only column in the whole WHERE clause (soh.orderdate) in a formula. What that means is that the entire table (or index, if limited to that in a "Covering Index") must be scanned from top to bottom to run the column through the formula to see if the FORMULA produces results matching the criteria instead of checking to see if the COLUMN has rows that match the criteria that could result in an index SEEK.
You said this was a test (and I don't care if it's a school test or an employer's test) so I'm not going to give you the answers to how to solve the two observations I made with your code. But, now you have enough information to know what I'd be looking for in code whether I was an instructor or an employer. Do some research, science it out, and good luck.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 13, 2015 at 2:36 pm
Jeff Moden (11/13/2015)
Alright, nexus2vijay... Putting the teaching hat back on, let's work on the first problem...Here's the problem as you listed it in the original post on this thread...
1. List the top 10 customers for the current year by total sales.
Column List: CustomerName, [Total Sales]
Sorting Order: [Total Sales] (desc)
... and here's the answer that you posted...
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
[font="Arial Black"]Observation #1: Current Year[/font]
Now... look back at the question. What is it asking? The first thing to notice is that it's asking for some type of rows for the "CURRENT YEAR". If you run your solution next year, will it return the rows for the "CURRENT YEAR"? NO. It'll return rows for 2015 but that's not the "CURRENT YEAR".
How to fix that problem? Not like a lot of people might think. Most would think that you could simply get the year of the current date using something like the following formula (or any of the functions that return the current date and time) and, because of "Observation #2" below, they'd be only partially correct.
YEAR(CURRENT_TIMESTAMP)
[font="Arial Black"]Observation #2: Non-SARGable Predicate[/font]
"SARG" stands for "Search ARGument". A "SARGable Predicate" is a search condition where an Index SEEK can be used if an index on the proper columns is available. A "NON-SARGable" Prediccate is a search condition where an index SEEK is impossible no matter what kind of index you use.
Your WHERE clause is totally NON_SARGable. The thing that makes it so is the fact that you've wrapped a column (soh.orderdate) in a formula. What that means is that the entire table (or index, if limited to that in a "Covering Index") must be scanned from top to bottom to run the column through the formula to see if the FORMULA produces results matching the criteria instead of checking to see if the COLUMN has rows that match the criteria that could result in an index SEEK.
You said this was a test (and I don't care if it's a school or an employer's test) so I'm not going to give you the answers to how to solve the two observations I made with your code. But, now you have enough information to know what I'd be looking for in code whether I was an instructor or an employer. Do some research, science it out, and good luck.
[font="Arial Black"]Observation #3: Missing Table in JOIN[/font]
Adding to Jeff's observations, you are also referencing the column of a table that is not included in your query at all, specifically Salesorderheader. Also, the join you do have is wrong.
November 13, 2015 at 4:13 pm
Ed Wagner (11/13/2015)
Jeff Moden (11/13/2015)
This is actually pretty sickening. Why do you even bother going to school if you're not going to even try. You should drop the course if this is the way you intend to pass your courses.Amen. The world already has enough 9-to-5-ers who always do the same thing the same way and never even realize they're in a box. 😉
Then again, education has become more about getting a piece of paper than about learning. It's a sad situation where people pay such huge sums of money for a piece of paper and don't take advantage of the opportunity to learn something.
It's sad, indeed. What's really sad is that I just realized that I've been reduced to producing Celko-like guttural utterances of no particular use on questions like this one because I'm so incredibly angry about people not only not being able to solve such problems nor do they even read the problems with any fore thought as to how the code will operate in the future, but that (at least the, ummm... "candidates" {yeah... that's a nice word for them} that I've been interviewing) these are the same people that are demanding a salary of between 90K and 115K USD and bloody head-hunters that also don't know better are helping them get it at companies that also don't know better. What really get's my goat is when the head hunters get mad at me when I tell them that the candidates they're sending are NOT senior level candidates no matter what their resume says.
Heh... and don't get me started on 9-to-5'ers that make no investment in themselves unless the "company pays for it" (haven't they already paid enough by not getting what they paid for by hiring them?). Of course, if you look through Books Online and most MS books, you won't find any mention of how to correctly do even problem #1 on the original post so even if folks were to get training, it wouldn't actually give them enough information to solve either the current year problem or the SARGability problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply