Collection of SQL queries with Answer and Output Set 4

Here is a collection or a list of 30 SQL Queries with Answers as well as output. You can write your answer at the text box below each query any time you can see the table structure by clicking on Table Structure. And check your Answer by clicking on Answer. You can test your Skill in SQL. You can also go for an online Quiz in SQL in one of my previous posts: Click here for Quiz. More queries will be added to this post within few days, visit again!!!

Happy learning!!!
Carry on....
You can also share your queries in this site. Use this Link to share your part with the visitors like you.

SQL Query collection: Set1 Set2 Set3 Set 4

Below is the Table Structure using which you have to form the queries:


1) Display the details of THOSE WHO are drawing the same salary.

Table Structure

Write your answer hereAnswer

select a.name,a.salary
from programmer a,programmer b
where a.salary=b.salary and a.name <> b.name
OR

select name, salary from programmer where
salary = any(select salary from programmer p group by salary having
salary=p.salary and count(*)>1)

2) Display the details of software developed by male programmers earing MORE than 3000.

Table Structure

Write your answer hereAnswer

select software.*
from programmer p,software s
where p.name=s.name and salary>3000 and sex=m;

3) Display details of packages developed in PASCAL by female programmers.

Table Structure

Write your answer hereAnswer

select s.*
from programmer p,software s
where p.name=s.name and sex=f and dev_in=pascal;

4) Display the details of these programmer WHO joined BEFORE 1990.

Table Structure

Write your answer hereAnswer

select *
from programmer
where to_char(doj,yy)<90;

5)Display details of software developed in C by female programmers of PRAGATHI.

Table Structure

Write your answer hereAnswer

select s.*
from software s,studies st,programmer p
where s.name=st.name and p.name=s.name and sex=f and splace=pragathi;

6) Display NUMBER of packages NUMBER of copies sold and sales value of EACH programmer Institute-wise.

Table Structure

Write your answer hereAnswer

Select studies.splace, count(software.dev_in), count(software.sold), sum(software.sold*software.scost)
from software,studies
where software.name=studies.name group by studies.splace;

7) Display details of software developed in DBASE by male programmers WHO belong to the institute on which MOST NUMBER OF programmers studies.

Table Structure

Write your answer hereAnswer

select software.*
from programmer,software,studies
where programmer.name=software.name and software.name=studies.name and programmer.name=studies.name and sex=m and dev_in=dbase and splace= (select splace
from studies group by splace having count(splace) =(select max(count(splace))
from studies group by splace));

8) Display the details of the software that was developed by male programmers born BEFORE 1965 and female programmers born AFTER 1975.

Table Structure

Write your answer hereAnswer

select software.*
from programmer p,software s
where s.name=p.name and sex=m and to_char(dob,yy)<64 or sex=f and To_char(dob,yy)>75);

9) Display the details of the software that was developed in the language that is NOT the programmers first proficiency.

Table Structure

Write your answer hereAnswer

select *
from software
where dev_in in(select unique(prof2)
from programmer
where prof2 not in(select prof1
from programmer));

or

select distinct x.* from software x, programmer y
where y.prof1 <> x.dev_in
and x.name = y.name

10) Display details of software that was developed in the language which is NITHER first NOR second proficiency of the programmer.

Table Structure

Write your answer hereAnswer

select s.*
from programmer p,software s
where s.name=p.name and (dev_in <> prof1 and dev_in <> prof2);

11) Display details of software developed by male students of SABHARI.

Table Structure

Write your answer hereAnswer

select s.*
from programmer p,software s,studies st
where p.name=s.name and s.name=st.name and sex=m and splace=sabhari;

12) Display the names of programmers WHO HAVE NOT developed any package.

Table Structure

Write your answer hereAnswer

select name
from programmer
where name not in(select name
from software);
or

select distinct name from programmer minus
select distinct name from software;

13) What is the total cost of the software developed by the programmers by APPLE?

Table Structure

Write your answer hereAnswer

select sum(scost)
from software s,studies st
where s.name=st.name and splace=apple;
or

select sum(x.scost) from software x, studies y where
x.name=y.name
group by y.splace
having
y.splace = APPLE

14) Who are the programmers WHO JOINED in the same day?

Table Structure

Write your answer hereAnswer

select a.name,a.doj
from programmer a,programmer b
where a.doj=b.doj and a.name <> b.name;
or

select name from programmer where to_char(doj,dd)=
any(select to_char(doj,dd) from programmer
group by
to_Char(doj,dd)
having
count(*)>1)

15) Who are the programmers WHO HAVE THE SAME PROF2?

Table Structure

Write your answer hereAnswer

select unique(a.name),a.prof2
from programmer a,programmer b
where a.prof2=b.prof2 and a.name <> b.name;

or

select name from programmer where prof2 = any(
select prof2 from programmer group by prof2 having count(*) >1);

16) Display the total sales values of software, institutes-wise.

Table Structure

Write your answer hereAnswer

select studies.splace,sum(software.sold*software.scost)
from software,studies
where studies.name=software.name group by studies.splace;

17) In which institutes did the person who developed the COSTLIEST package study?

Table Structure

Write your answer hereAnswer

select splace
from software st,studies s
where s.name=st.name group by splace,dcost having max(dcost)=(select max(dcost) from software);
or

select x.splace from studies x, software y where
y.scost = ( select max(y.scost) from software y) and
x.name=y.name;

18) Which language listed in prof1 and prof2 HAS NOT BEEN used to develop any package?

Table Structure

Write your answer hereAnswer

select prof1
from programmer
where prof1 not in(select dev_in
from software) union
select prof2
from programmer
where prof2 not in(select dev_in from software);
or

(select distinct prof1 from prgrammer union
select distinct prof2 from programmer) minus
select distinct dev_in from software;

19) How much does the person WHO developed the HIGHEST selling package earn and WHAT course did he/she undergo?

Table Structure

Write your answer hereAnswer

select p1.salary,s2.course
from programmer p1,software s1,studies s2
where p1.name=s1.name and s1.name=s2.name and scost=(select max(scost) from software);

20) How many months will it take for each programmer to recover the cost of the course underwent?

Table Structure

Write your answer hereAnswer

select p.name,ceil(ccost/salary)
from programmer p,studies s
where s.name=p.name;

21) Which is the COSTLIEST package developed by a person with under 3 years expenence?

Table Structure

Write your answer hereAnswer

select dev_in
from programmer p,software s
where p.name=s.name and dcost= (select max(software.dcost)
from programmer p, software s
where p.name=s.name and to_char(round(((sysdate- doj)/365)+100))<3);

or

select x.title from software x, programmer y where
(months_between(sysdate, y.doj)/12) > 3 and
x.name=y.name;

22) What is the AVERAGE salary for those WHOSE softwares sales value is more than 50,000?

Table Structure

Write your answer hereAnswer

select avg(salary)
from programmer p,software s
where p .name=s.name and sold*scost>50000;

23) How many packages were developed by the students WHO studied in the institute that Charge the LOWEST course fee?

Table Structure

Write your answer hereAnswer

select count(s.name)
from software s,studies st
where s.name=st.name group by s.name,ccost having min(ccost)=(select min(ccost) from studies);

24) How many packages were developed by the person WHO developed the CHEAPEST package. Where did heshe study?

Table Structure

Write your answer hereAnswer

select count(*)
from programmer p,software s
where s .name=p.name group by dev_in having min(dcost)=(select min(dcost) from software);

25) How many packages were developed by female programmers earning MORE than the HIGHEST paid male programmer?

Table Structure

Write your answer hereAnswer

select count(dev_in)
from programmer p,software s
where s.name=p.name and sex=f and salary>(select max(salary)
from programmer p,software s
where s.name=p.name and sex=m);

26) How many packages were developed by the MOST experienced programmers from BDPS.

Table Structure

Write your answer hereAnswer

select count(*)
from software s,programmer p
where p.name=s.name group by doj having max(doj)=(select max(doj)
from studies st,programmer p, software s
where p.name=s.name and st.name=p.name and (splace=bdps));

or

select count(x.name) from software x, programmer y, studies x where
months_between(sysdate, y.doj)/12) = (select max(months_between(sysdate,y.doj)/12)
from programmer y, studies = where
x.splace = BDPS and y.name = z.name) and
x.name=y.name and
z.splace=BDPS

27) List the programmers (from software table) and institutes they studied, including those WHO DIDNT develop any package.

Table Structure

Write your answer hereAnswer

select name,splace
from studies
where name not in(select name
from software);
or

(select distinct x.name, z.splace from programmer x, software y, studies z where
x.name not in (select y.name from software y) and
x.name = z.name) union
(select distinct y.name, z.splace from
software y, studies z where y.name=z.name);

28) List each profit with the number of programmers having that prof1 and the number of packages developed in that prof1.

Table Structure

Write your answer hereAnswer

select count(*),sum(scost*sold-dcost) "PROFIT"
from software
where dev_in in (select prof1
from programmer) group by dev_in;

29) List programmer names (from programmer table) and number of packages EACH developed.

Table Structure

Write your answer hereAnswer

select s.name,count(dev_in)
from programmer p1,software s
where p1.name=s.name group by s.name;
or

select programmer name, count(title) from programmer , software
where
programmer name = software.name(+)
group by programmer.name;

30) List all the details of programmers who has done a course at S.S.I.L.

Table Structure

Write your answer hereAnswer

select programmer.*
from programmer,studies
where splace=SSIL and programmer.name=software.name and programmer.name=studies.name and studies.splace=s.s.i.l.;

SQL Query collection: Set1 Set2 Set3 Set 4

Read More..