Collection of SQL queries with Answer and Output Set 2

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 NUMBER OF packages developed in EACH language.

Table Structure

Write your answer hereAnswer

SELECT DEV_IN AS LANGUAGE,COUNT(TITLE) AS NOOFPACK
FROM SOFTWARE
GROUP BY DEV_IN

2) Display THE NUMBER OF packages developed by EACH person.

Table Structure

Write your answer hereAnswer

SELECT NAME AS PRNAME,COUNT(TITLE)AS NOOFPACK
FROM SOFTWARE
GROUP BY NAME

3) Display THE NUMBER OF male and female programmer.

Table Structure

Write your answer hereAnswer

SELECT SEX,COUNT(NAME) AS NAME
FROM PROGRAMMER
GROUP BY SEX

4) Display THE COSTLIEST packages and HIGEST selling developed in EACH language.

Table Structure

Write your answer hereAnswer

SELECT DEV_IN AS LANGAUGE,MAX(SCOST) AS COSTPACK,MAX(SOLD) AS HIGHPACK
SFROM SOFTWARE
GROUP BY DEV_IN

5) Display THE NUMBER OF people BORN in EACH YEAR.

SELECT TO_CHAR(DOB,YY) AS YEAR,COUNT(NAME) AS PRNO
FROM PROGRAMMER
GROUP BY TO_CHAR(DOB,YY)

Table Structure

Write your answer hereAnswer

6) Display THE NUMBER OF people JOINED in EACH YEAR.

Table Structure

Write your answer hereAnswer

SELECT TO_CHAR(DOJ,YY) AS YEAR,COUNT(NAME) AS PRNO
FROM PROGRAMMER
GROUP BY TO_CHAR(DOJ,YY)

7) Display THE NUMBER OF people BORN in EACH MONTH.

Table Structure

Write your answer hereAnswer

SELECT SUBSTR(DOB,4,3) AS MONTHOFBIRTH,COUNT(NAME) AS PRNO FROM PROGRAMMER
GROUP BY SUBSTR(DOB,4,3)

8) Display THE NUMBER OF people JOINED in EACH MONTH.

Table Structure

Write your answer hereAnswer

SELECT SUBSTR(DOJ,4,3) AS MONTHOFJOIN,COUNT(NAME) AS PRNO
FROM PROGRAMMER
GROUP BY SUBSTR(DOJ,4,3)

9) Display the language wise COUNTS of prof1.

Table Structure

Write your answer hereAnswer

SELECT PROF1 AS LANGUAGE, COUNT(PROF1) AS PROF1COUNT
FROM PROGRAMMER
GROUP BY PROF1

10) Display the language wise COUNTS of prof2.

Table Structure

Write your answer hereAnswer

SELECT PROF2 AS LANGUAGE, COUNT(PROF2) AS PROF2COUNT
FROM PROGRAMMER
GROUP BY PROF2

11) Display THE NUMBER OF people in EACH salary group.

Table Structure

Write your answer hereAnswer

SELECT SALARY,COUNT(NAME) AS PEOPLE
FROM PROGRAMMER
GROUP BY SALARY

12) Display THE NUMBER OF people who studied in EACH institute.

Table Structure

Write your answer hereAnswer

SELECT SPLACE AS INSTITUTE,COUNT(NAME) AS PEOPLE
FROM STUDIES
GROUP BY SPLACE

13) Display THE NUMBER OF people who studied in EACH course.

Table Structure

Write your answer hereAnswer

SELECT COURSE AS STUDY,COUNT(NAME) AS PEOPLE
FROM STUDIES GROUP BY COURSE

14) Display the TOTAL development COST of the packages developed in EACH language.

Table Structure

Write your answer hereAnswer

SELECT DEV_IN AS LANGUAGE,SUM(DCOST) AS TOTCOST
FROM SOFTWARE
GROUP BY DEV_IN

15) Display the selling cost of the package developed in EACH language.

Table Structure

Write your answer hereAnswer

SELECT DEV_IN AS LANGUAGE,SUM(SCOST) AS SELLCOST
FROM SOFTWARE
GROUP BY DEV_IN

16) Display the cost of the package developed by EACH programmer.

Table Structure

Write your answer hereAnswer

SELECT NAME AS PRNAME,SUM(DCOST) AS TOTCOST
FROM SOFTWARE
GROUP BY NAME

17) Display the sales values of the package developed in EACH programmer.

Table Structure

Write your answer hereAnswer

SELECT NAME AS PRNAME, SUM(SCOST*SOLD) AS SALESVAL
FROM SOFTWARE
GROUP BY NAME

18) Display the NUMBER of packages developed by EACH programmer.

Table Structure

Write your answer hereAnswer

SELECT NAME AS PRNAME,COUNT(TITLE) AS TOTPACK
FROM SOFTWARE
GROUP BY NAME

19) Display the sales COST of packages developed by EACH programmer language wise.

Table Structure

Write your answer hereAnswer

SELECT SUM(SCOST) AS SELLCOST
FROM SOFTWARE
GROUP BY DEV_IN

20) Display EACH programmers name, costliest package and cheapest packages developed by Him/Her.

Table Structure

Write your answer hereAnswer

SELECT NAME PRNAME,MIN(DCOST) CHEAPEST,MAX(DCOST) COSTLIEST
FROM SOFTWARE
GROUP BY NAME

21) Display EACH language name with AVERAGE development cost, AVERAGE cost, selling cost and AVERAGE price per copy.

Table Structure

Write your answer hereAnswer

SELECT DEV_IN AS LANGUAGE,AVG(DCOST) AS AVGDEVCOST,AVG(SCOST) AS AVGSELLCOST,AVG(SCOST) AS PRICEPERCPY
FROM SOFTWARE
GROUP BY DEV_IN

22) Display EACH institute name with NUMBER of courses, AVERAGE cost per course.

Table Structure

Write your answer hereAnswer

SELECT SPLACE AS INSTITUTE,COUNT(COURSE) AS NOOFCOURS,AVG(CCOST) AS AVGCOSTPERCOUR
FROM STUDIES
GROUP BY SPLACE

23) Display EACH institute name with NUMBER of students.

Table Structure

Write your answer hereAnswer

SELECT SPLACE AS INSTITUTE,COUNT(NAME) AS NOOFSTUD
FROM STUDIES
GROUP BY SPLACE

24) Display names of male and female programmers.

Table Structure

Write your answer hereAnswer

SELECT NAME AS PRNAME,SEX AS SEX
FROM PROGRAMMER
ORDER BY SEX

25) Display the programmers name and their packages.

Table Structure

Write your answer hereAnswer

SELECT NAME AS PRNAME,TITLE AS PACKAGE
FROM SOFTWARE
ORDER BY NAME

26) Display the NUMBER of packages in EACH language.

Table Structure

Write your answer hereAnswer

SELECT COUNT(TITLE) AS NOOFPACK,DEV_IN AS LANGUAGE
FROM SOFTWARE
GROUP BY DEV_IN

27) Display the NUMBER of packages in EACH language for which development cost is less than 1000.

Table Structure

Write your answer hereAnswer

SELECT COUNT(TITLE) AS NOOFPACK,DEV_IN AS LANGUAGE
FROM SOFTWARE
WHERE DCOST<1000 GROUP BY DEV_IN

28) Display the AVERAGE difference BETWEEN scost and dcost for EACH language.

Table Structure

Write your answer hereAnswer

SELECT DEV_IN AS LANGUAGE,AVG(DCOST - SCOST) AS DIFF
FROM SOFTWARE
GROUP BY DEV_IN

29) Display the TOTAL scost, dcsot and amount TOBE recovered for EACH programmer for whose dcost HAS NOT YET BEEN recovered.

Table Structure

Write your answer hereAnswer

SELECT SUM(SCOST), SUM(DCOST), SUM(DCOST-(SOLD*SCOST))
FROM SOFTWARE
GROUP BY NAME
HAVING SUM(DCOST)>SUM(SOLD*SCOST)

30) Display highest, lowest and average salaries for THOSE earning MORE than 2000.

Table Structure

Write your answer hereAnswer

SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY)
FROM PROGRAMMER
WHERE SALARY > 2000

SQL Query collection: Set1 Set2 Set3 Set 4

Read More..

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..