Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Query Expand / Collapse
Author
Message
Posted Tuesday, May 13, 2008 9:26 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 5:36 PM
Points: 1,339, Visits: 596
Comments posted to this topic are about the item Query

--www.sqlvillage.com
Post #500155
Posted Wednesday, May 14, 2008 12:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 12:06 AM
Points: 217, Visits: 172
Interesting point on filters and sub selects, but I thought the question was a bit misleading in terms of the possible answers provided:
- From a syntax point of view, I agree 'B' would the correct answer
- From a results/holistic view, 'C' would be the answer as the query (using the attached example) yields incorrect results as there is no filter on the join and only Customer 1 is on pricing plan 'X', but results say both customers are on pricing plan 'X'?
Post #500197
Posted Wednesday, May 14, 2008 1:45 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 19, 2010 6:47 AM
Points: 100, Visits: 130
For the sake of precision, I think the correct answer should have been formulated as

Query will succeed if the customerid and pricingplan columns are available in any table used in the first From Clause.
Post #500214
Posted Wednesday, May 14, 2008 2:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 4:21 AM
Points: 216, Visits: 194
Yup I'm with Marius, the results would not be what is intended and therefore the query is just plain wrong, which is different to a syntax error as offered in option 1. So in fact it should have been a select all that apply because indeed results are returned assuming suitable columns exist but they are the wrong results.

K.
Post #500235
Posted Wednesday, May 14, 2008 5:20 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 18, 2010 5:25 AM
Points: 162, Visits: 694
Syntactically correct, but it doesn't produce meaningful results. I agree with the previous posts, it compiles and executes, but what is it trying to provide?


Honor Super Omnia-
Jason Miller
Post #500327
Posted Wednesday, May 14, 2008 6:50 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 17, 2014 4:33 AM
Points: 221, Visits: 110
Please avoid posting these sort of misleading questions.


select CategoryID, Quantity, Price
from Customer c, SalesOrderDetail sod
where sod.clientid in (select customerid where pricingplan='X')

works even without customerid in any of the table but not the intended result.

If any table has customerid. eg: customer table has customerid then alias name should be there otherwise gives ambigous column

select CategoryID, Quantity, Price
from Customer c, SalesOrderDetail sod
where sod.clientid in (select c.customerid where pricingplan='X')
Post #500412
Posted Wednesday, May 14, 2008 6:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, December 5, 2014 10:31 AM
Points: 13,872, Visits: 9,599
I think it's a valid question. Was clear enough to me anyway.

It brings up the caveat of: Compiles and Runs does NOT mean Correct

That's an important lesson for any developer to learn.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #500414
Posted Wednesday, May 14, 2008 7:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:31 PM
Points: 2,428, Visits: 2,861
I got the question wrong, which is OK, but I think choices such as "Query is wrong" sound too vague. Perhaps "Query throws an error" or the specific error or something like that. It's not a big difference, but I think it's a meaningful one.

-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #500421
Posted Wednesday, May 14, 2008 7:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 10,380, Visits: 13,434
I got it wrong as well, which, unfortunately, seems to be happening a lot lately. My only comment is that I would prefer that database context be given something like;

Given the tables Customer (customerid, pricingplan) and SalesOrderId(clientid, category) what is the result of the following query.

I assumed the context was AdventureWorks and the query is wrong in that context.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #500436
Posted Wednesday, May 14, 2008 7:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 16, 2008 1:20 PM
Points: 102, Visits: 25
OK, just plain and simple, I am going to give my 2 cents worth here. This question is pure BS. I mean has anyone here ever seen a query written like this? In my house(place of business) the person who tried to get this through me, would be ridiculed beyond belief. I mean really, did I get the question wrong, yep. Did I also think that the question was misleading, yep. Do I know a developer that would try to get this past me, nope. Lets get some real questions in here. First off I do not even let anyone write in ANSI 89 SQL. Plain and simple it is my way or the highway.

DEX
:D
The more you help the business, the more they will help you...well sometimes anyway.
Post #500446
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse