[rnty]

My second post in quick succession.Trying to solve some database questions that I downloaded from the internet.

SQL> select * from aircraft;

       AID ANAME                    CRANGE
---------- -------------------- ----------
         1 Boeing                     1000
         2 Indigo                     2000
         3 Spice                      3000

SQL> select * from certified;

       AID        EID
---------- ----------
         1          1
         1          2
         2          3
         2          4
         3          5

SQL> select * from employee;

       EID ENAME          SALARY
---------- ---------- ----------
         1 Ram             10000
         2 SRam            10400
         3 DRam            15400
         4 SDRam           15400
         5 DDRam           19900

Query:For all aircrafts with crange>1000 find the NAME OF THE AIRCRAFT and the AVERAGE SALARY of pilots certified to operate the aircraft.

SQL> select avg(salary) from employee group by (eid) having eid in(select aid fr
om certified group by aid having aid in(select aid from aircraft where crange>10
00));

AVG(SALARY)
-----------
      10400
      15400

Questions:
I think I have written a part of the query correctly but shouldn't the answer be 15400 and 19900?
How do I display the names of the aircrafts.I have tried using aircraft.aname but an "invalid identifier error is thrown".Please help.
Thank you in advance

[boerkees]

rnty, on 14 August 2012 - 11:47 PM, said:

Dear rnty,

I assume that AID is a primary key in aircraft en EID in employee and that certified is a connecting table.
Then i would use an inner join on aircraft and certified with AID as connector and an inner join of employee and certified with EID as connector
Then all you have to do is asking for the name of the plane and the average of the salary with a group by on the name and a having on the crange being > 1000

I got this as result:

--------------+---------------
aircraft name | average salary
--------------+---------------
Indigo	      | 15400
Spice	      | 19900		 
--------------+---------------

You can change the column heading by specifying an 'AS'-clause like in:

SELECT ANAME AS `aircraft name` FROM ...

Depending on the database vendor you use the back-tick (`) or square brackets ([, ]) or so to delimit the column names.

With regards,

Kees de Boer
The Netherlands

My second post in quick succession.Trying to solve some database questions that I downloaded from the internet.

SQL> select * from aircraft;

       AID ANAME                    CRANGE
---------- -------------------- ----------
         1 Boeing                     1000
         2 Indigo                     2000
         3 Spice                      3000

SQL> select * from certified;

       AID        EID
---------- ----------
         1          1
         1          2
         2          3
         2          4
         3          5

SQL> select * from employee;

       EID ENAME          SALARY
---------- ---------- ----------
         1 Ram             10000
         2 SRam            10400
         3 DRam            15400
         4 SDRam           15400
         5 DDRam           19900

Query:For all aircrafts with crange>1000 find the NAME OF THE AIRCRAFT and the AVERAGE SALARY of pilots certified to operate the aircraft.

SQL> select avg(salary) from employee group by (eid) having eid in(select aid fr
om certified group by aid having aid in(select aid from aircraft where crange>10
00));

AVG(SALARY)
-----------
      10400
      15400

Questions:
I think I have written a part of the query correctly but shouldn't the answer be 15400 and 19900?
How do I display the names of the aircrafts.I have tried using aircraft.aname but an "invalid identifier error is thrown".Please help.
Thank you in advance

[baavgai]

First, I just want to indent your query...

select avg(salary) 
	from employee 
	group by (eid) 
	having eid in (
		select aid
			from certified
			group by aid 
			having aid in(
				select aid 
					from aircraft 
					where crange>1000
				)
		);

So, your inner most IN is select aid from aircraft where crange>1000, which makes sense. Try to avoid IN, though.

You next IN is a typo. having eid in ( select aid. Shouldn't those both be eid? That should actually work, but damn it's messy. ( Though, you don't need group by (eid). )

Let's start over:

select a.aid 
	from aircraft a
	where a.crange>1000

Now, I know I want all the eid for that, right?

select c.eid
	from aircraft a
		inner join certified c
			on a.aid=c.aid
	where a.crange>1000

Now, if I could get an employee list:

select e.*
	from aircraft a
		inner join certified c
			on a.aid=c.aid
		inner join employee e
			on c.eid=e.eid
	where a.crange>1000

Hmm... we don't even need a group by now:

select avg(e.salary)
	from aircraft a
		inner join certified c
			on a.aid=c.aid
		inner join employee e
			on c.eid=e.eid
	where a.crange>1000

Always, always, reach for a JOIN first. It's what databases do best. You never need to use IN. It can seem easy, but it will usually get you thinking in the wrong direction and it's very slow.

Hope this helps.

This post has been edited by baavgai: 15 August 2012 - 04:50 AM

[rnty]

Thank you.