Home » SQL & PL/SQL » SQL & PL/SQL » SQL solution needed - urgent!
SQL solution needed - urgent! [message #610215] Tue, 18 March 2014 09:33 Go to next message
Annie Agarwal
Messages: 8
Registered: February 2012
Junior Member

Hi all,
I am student and tried solving this SQL query problem , but I am getting incorrect output.
Here are the details:

Bowlers: table 1
BowlerID Firstname Lastname
1 George Jefferson
2 Thomas Jones


Balls: table 2
BallID BowlerID BallNumber BallWeight
1 1 1 10
2 1 2 11
3 2 1 12
4 2 2 15
5 1 3 14


Bowlers may not have any balls, so they wouldn't have a record in the balls table. Bowlers can have up to 3 balls; they would be in the balls tables as BallNumber=1, BallNumber= 2, BallNumber=3.

Q: Select a single record first and last name only of all bowlers that have record(s) in the balls table.

my initial query:
SELECT b.first_name, b.last_name
FROM balls AS ba
INNER JOIN bowler AS b
ON ba.ball_id = b.bowler_id;

Thanks
Annie
Re: SQL solution needed - urgent! [message #610216 is a reply to message #610215] Tue, 18 March 2014 09:39 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Please read and follow How to use [code] tags and make your code easier to read?

Assuming your using oracle you'll only get an error from that, you can't us AS in the from clause.
If you join a record in one table to two records in another table your resulting output will be two records.
You need to either:
a) use an IN/EXISTS sub-query to check the balls table
b) use DISTINCT to eliminate the duplicates.

(a) is generally considered to be the better practice.
Re: SQL solution needed - urgent! [message #610217 is a reply to message #610216] Tue, 18 March 2014 09:44 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

explain why it is urgent for us to solve this problem for you.
Re: SQL solution needed - urgent! [message #610218 is a reply to message #610217] Tue, 18 March 2014 09:47 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Also, since balls contains a column called bowler_id, don't you think you should be joining on that?
Re: SQL solution needed - urgent! [message #610243 is a reply to message #610216] Tue, 18 March 2014 12:37 Go to previous messageGo to next message
Annie Agarwal
Messages: 8
Registered: February 2012
Junior Member

Thanks cookiemonster. will correct the query and send it again for review.

Answer to BlackSwan's question.... i am preparing for an interview (march 21st) for a beginner level QA tester's position. So, these are some tutorials for SQL queries. Unfortunately, the SQL developer tool is only in my college lab. So at home, i am having to guess the queries.

Thanks
Annie
Re: SQL solution needed - urgent! [message #610247 is a reply to message #610243] Tue, 18 March 2014 12:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can download SQL Developer and Oracle XE (both free) at http://www.oracle.com/technetwork/indexes/downloads/index.html

Re: SQL solution needed - urgent! [message #610251 is a reply to message #610243] Tue, 18 March 2014 14:21 Go to previous messageGo to next message
Annie Agarwal
Messages: 8
Registered: February 2012
Junior Member

for Q1 - i have 3 possibilities...can anyone let me know if this will work ?

a )
Select b.first_name, b.last_name
From bowlers b
Where EXISTS (Select *
From balls ba
where ba.bowler_id = b.bowler_id);

b)
Select b.first_name, b.last_name
From bowlers b
Where b.bowler_id IN (Select Distinct ba.bowler_id
From balls ba
where ba.bowler_id = b.bowler_id);

c)
Select b.first_name, b.last_name
From bowlers b Join balls ba
on b.bowler_id = ba.bowler_id
Where ba.bowler_id
(Select *
From balls ba
Where ball_number <=3);

Thanks
Annie
Re: SQL solution needed - urgent! [message #610252 is a reply to message #610251] Tue, 18 March 2014 14:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why, in the last query?

Quote:
Where ba.bowler_id
(Select *
From balls ba
Where ball_number <=3);


In addition, DISTINCT in the second query is useless.
The 3 queries are equivalent (if you remove the WHERE part of the last one).

Re: SQL solution needed - urgent! [message #610254 is a reply to message #610252] Tue, 18 March 2014 14:44 Go to previous messageGo to next message
Annie Agarwal
Messages: 8
Registered: February 2012
Junior Member

Thanks Michel
Re: SQL solution needed - urgent! [message #610266 is a reply to message #610254] Tue, 18 March 2014 22:49 Go to previous messageGo to next message
Annie Agarwal
Messages: 8
Registered: February 2012
Junior Member

Hi Michel,
I have downloaded SQL developer on my local machine.
How do I create a new connection to create a new database?

Thanks
Annie
Re: SQL solution needed - urgent! [message #610269 is a reply to message #610266] Wed, 19 March 2014 00:12 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Annie Agarwal wrote on Tue, 18 March 2014 20:49
Hi Michel,
I have downloaded SQL developer on my local machine.
How do I create a new connection to create a new database?

Thanks
Annie

to create a new database you should use the DBCA utility; NOT sqldeveloper
Re: SQL solution needed - urgent! [message #610275 is a reply to message #610266] Wed, 19 March 2014 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
How do I create a new connection to create a new database?


You must also download Oracle XE; its installer will create a database.

Re: SQL solution needed - urgent! [message #610296 is a reply to message #610275] Wed, 19 March 2014 04:14 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
The last of the three queries will give too many rows as you've joined to balls in the main query.
Re: SQL solution needed - urgent! [message #610300 is a reply to message #610296] Wed, 19 March 2014 04:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, how did I not see that! /forum/fa/1606/0/

Re: SQL solution needed - urgent! [message #610301 is a reply to message #610300] Wed, 19 March 2014 04:36 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Actually the last one is just invalid syntax. Looks like the IN keyword is missing but adding it won't work since sub-query selects *.
Previous Topic: timestamp shows 0 for all the records
Next Topic: how to find data using date as a parameter in financial year
Goto Forum:
  


Current Time: Thu Mar 28 06:10:25 CDT 2024