Collection of SQL queries with Answer and Output Set 3

Here is a collection or a list of 38 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) Who is the highest paid C programmer?

Table Structure

Write your answer hereAnswer

SELECT * FROM PROGRAMMER
WHERE SALARY=(SELECT MAX(SALARY)
FROM PROGRAMMER
WHERE PROF1 LIKE C OR PROF2 LIKE C)

2) Who is the highest paid female cobol programmer?

Table Structure

Write your answer hereAnswer

SELECT * FROM PROGRAMMER
WHERE SALARY=(SELECT MAX(SALARY)
FROM PROGRAMMER
WHERE (PROF1 LIKE COBOL OR PROF2 LIKE COBOL))
AND SEX LIKE F

3) Display the name of the HIGEST paid programmer for EACH language (prof1)

Table Structure

Write your answer hereAnswer

SELECT DISTINCT NAME, SALARY, PROF1
FROM PROGRAMMER
WHERE (SALARY,PROF1) IN (SELECT MAX(SALARY),PROF1
FROM PROGRAMMER
GROUP BY PROF1)

4) Who is the LEAST experienced programmer?

Table Structure

Write your answer hereAnswer

SELECT FLOOR((SYSDATE-DOJ)/365) EXP,NAME
FROM PROGRAMMER
WHERE FLOOR((SYSDATE-DOJ)/365) = (SELECT MIN(FLOOR((SYSDATE-DOJ)/365))
FROM PROGRAMMER)

5) Who is the MOST experienced programmer?

Table Structure

Write your answer hereAnswer

SELECT FLOOR((SYSDATE-DOJ)/365) EXP,NAME,PROF1,PROF2
FROM PROGRAMMER
WHERE FLOOR((SYSDATE-DOJ)/365) = (SELECT MAX(FLOOR((SYSDATE-DOJ)/365))
FROM PROGRAMMER)
AND (PROF1 LIKE COBOL OR PROF2 LIKE COBOL)

6) Which language is known by ONLY ONE programmer?

Table Structure

Write your answer hereAnswer

SELECT PROF1
FROM PROGRAMMER
GROUP BY PROF1
HAVING PROF1 NOT IN
(SELECT PROF2 FROM PROGRAMMER)
AND COUNT(PROF1)=1
UNION
SELECT PROF2
FROM PROGRAMMER
GROUP BY PROF2
HAVING PROF2 NOT IN
(SELECT PROF1 FROM PROGRAMMER)
AND COUNT(PROF2)=1;

7) Who is the YONGEST programmer knowing DBASE?

Table Structure

Write your answer hereAnswer

SELECT FLOOR((SYSDATE-DOB)/365) AGE, NAME, PROF1, PROF2
FROM PROGRAMMER
WHERE FLOOR((SYSDATE-DOB)/365) = (SELECT MIN(FLOOR((SYSDATE-DOB)/365))
FROM PROGRAMMER
WHERE PROF1 LIKE DBASE OR PROF2 LIKE DBASE)

8) Which institute has MOST NUMBER of students?

Table Structure

Write your answer hereAnswer

SELECT SPLACE
FROM STUDIES
GROUP BY SPLACE
HAVING COUNT(SPLACE)= (SELECT MAX(COUNT(SPLACE))
FROM STUDIES GROUP BY SPLACE)

9) Who is the above programmer?

Table Structure

Write your answer hereAnswer

SELECT NAME
FROM PROGRAMMER
WHERE PROF1 IN (SELECT PROF1
FROM PROGRAMMER
GROUP BY PROF1
HAVING PROF1 NOT IN (SELECT PROF2 FROM PROGRAMMER)
AND COUNT(PROF1)=1
UNION
SELECT PROF2
FROM PROGRAMMER
GROUP BY PROF2
HAVING PROF2 NOT IN (SELECT PROF1 FROM PROGRAMMER)
AND COUNT(PROF2)=1))
UNION
SELECT NAME
FROM PROGRAMMER
WHERE PROF2 IN (SELECT PROF1
FROM PROGRAMMER
GROUP BY PROF1
HAVING PROF1 NOT IN (SELECT PROF2 FROM PROGRAMMER)
AND COUNT(PROF1)=1
UNION
SELECT PROF2
FROM PROGRAMMER
GROUP BY PROF2
HAVING PROF2 NOT IN (SELECT PROF1 FROM PROGRAMMER)
AND COUNT(PROF2)=1))

10) Which female programmer earns MORE than 3000/- but DOES NOT know C, C++, Oracle or Dbase?

Table Structure

Write your answer hereAnswer

SELECT * FROM PROGRAMMER
WHERE SEX LIKE F
AND SALARY >3000
AND (PROF1 NOT IN(C,C++,ORACLE,DBASE)
OR PROF2 NOT IN(C,C++,ORACLE,DBASE))

11) Which is the COSTLIEST course?

Table Structure

Write your answer hereAnswer

SELECT COURSE
FROM STUDIES
WHERE CCOST = (SELECT MAX(CCOST) FROM STUDIES)

12) Which course has been done by MOST of the students?

Table Structure

Write your answer hereAnswer

SELECT COURSE
FROM STUDIES
GROUP BY COURSE
HAVING COUNT(COURSE)= (SELECT MAX(COUNT(COURSE))
FROM STUDIES
GROUP BY COURSE)

13) Display name of the institute and course Which has below AVERAGE course fee?

Table Structure

Write your answer hereAnswer

SELECT SPLACE,COURSE
FROM STUDIES
WHERE CCOST < (SELECT AVG(CCOST) FROM STUDIES)

14) Which institute conducts COSTLIEST course?

Table Structure

Write your answer hereAnswer

SELECT SPLACE
FROM STUDIES
WHERE CCOST = (SELECT MAX(CCOST) FROM STUDIES)

15) Which course has below AVERAGE number of students?

Table Structure

Write your answer hereAnswer

SELECT COURSE
FROM STUDIES
HAVING COUNT(NAME)<(SELECT AVG(COUNT(NAME))
FROM STUDIES
GROUP BY COURSE)
GROUP BY COURSE;

16) Which institute conducts the above course?

Table Structure

Write your answer hereAnswer

SELECT SPLACE
FROM STUDIES
WHERE COURSE IN (SELECT COURSE
FROM STUDIES
HAVING COUNT(NAME) < (SELECT AVG(COUNT(NAME))
FROM STUDIES
GROUP BY COURSE)
GROUP BY COURSE);

17) Display names of the course WHOSE fees are within 1000(+ or -) of the AVERAGE fee.

Table Structure

Write your answer hereAnswer

SELECT COURSE
FROM STUDIES
WHERE CCOST < (SELECT AVG(CCOST)+1000 FROM STUDIES)
AND CCOST > (SELECT AVG(CCOST)-1000 FROM STUDIES)

18) Which package has the HIGEST development cost?

Table Structure

Write your answer hereAnswer

SELECT TITLE,DCOST
FROM SOFTWARE
WHERE DCOST = (SELECT MAX(DCOST) FROM SOFTWARE)

19) Which package has the LOWEST selling cost?

Table Structure

Write your answer hereAnswer

SELECT TITLE,SCOST
FROM SOFTWARE
WHERE SCOST = (SELECT MIN(SCOST) FROM SOFTWARE)

20) Who developed the package, which has sold the LEAST number of copies?

Table Structure

Write your answer hereAnswer

SELECT NAME,SOLD
FROM SOFTWARE
WHERE SOLD = (SELECT MIN(SOLD) FROM SOFTWARE)

21) Which language was used to develop the package WHICH has the HIGEST sales amount?

Table Structure

Write your answer hereAnswer

SELECT DEV_IN,SCOST
FROM SOFTWARE
WHERE SCOST = (SELECT MAX(SCOST) FROM SOFTWARE)

22) How many copies of the package that has the LEAST DIFFRENCE between development and selling cost were sold?

Table Structure

Write your answer hereAnswer

SELECT SOLD,TITLE
FROM SOFTWARE
WHERE TITLE = (SELECT TITLE
FROM SOFTWARE
WHERE (DCOST-SCOST)=(SELECT MIN(DCOST-SCOST) FROM SOFTWARE))

23) Which is the COSTLIEAST package developed in PASCAL?

Table Structure

Write your answer hereAnswer

SELECT TITLE
FROM SOFTWARE
WHERE DCOST = (SELECT MAX(DCOST)
FROM SOFTWARE
WHERE DEV_IN LIKE PASCAL)

24) Which language was used to develop the MOST NUMBER of package?

Table Structure

Write your answer hereAnswer

SELECT DEV_IN FROM SOFTWARE
GROUP BY DEV_IN
HAVING MAX(DEV_IN) = (SELECT MAX(DEV_IN) FROM SOFTWARE)

25) Which programmer has developed the HIGEST NUMBER of package?

Table Structure

Write your answer hereAnswer

SELECT NAME FROM SOFTWARE
GROUP BY NAME
HAVING MAX(NAME) = (SELECT MAX(NAME) FROM SOFTWARE)

26) Who is the author of the COSTLIEST package?

Table Structure

Write your answer hereAnswer

SELECT NAME,DCOST
FROM SOFTWARE
WHERE DCOST = (SELECT MAX(DCOST) FROM SOFTWARE)

27) Display names of packages WHICH have been sold LESS THAN the AVERAGE number of copies?

Table Structure

Write your answer hereAnswer

SELECT TITLE
FROM SOFTWARE
WHERE SOLD < (SELECT AVG(SOLD) FROM SOFTWARE)

28) Who are the female programmers earning MORE than the HIGEST paid male programmers?

Table Structure

Write your answer hereAnswer

SELECT NAME
FROM PROGRAMMER
WHERE SEX LIKE F
AND SALARY > (SELECT(MAX(SALARY))
FROM PROGRAMMER
WHERE SEX LIKE M)

29) Which language has been stated as prof1 by MOST of the programmers?

Table Structure

Write your answer hereAnswer

SELECT PROF1
FROM PROGRAMMER
GROUP BY PROF1
HAVING PROF1 = (SELECT MAX(PROF1)
FROM PROGRAMMER)

30) Who are the authors of packages, WHICH have recovered MORE THAN double the development cost?

Table Structure

Write your answer hereAnswer

SELECT NAME distinct
FROM SOFTWARE
WHERE SOLD*SCOST > 2*DCOST

31) Display programmer names and CHEAPEST package developed by them in EACH language?

Table Structure

Write your answer hereAnswer

SELECT NAME,TITLE
FROM SOFTWARE
WHERE DCOST IN (SELECT MIN(DCOST)
FROM SOFTWARE
GROUP BY DEV_IN)

32) Who is the YOUNGEST male programmer born in 1965?

Table Structure

Write your answer hereAnswer

SELECT NAME
FROM PROGRAMMER
WHERE DOB=(SELECT (MAX(DOB))
FROM PROGRAMMER
WHERE TO_CHAR(DOB,YYYY) LIKE 1965)

33) Display language used by EACH programmer to develop the HIGEST selling and LOWEST selling package.

Table Structure

Write your answer hereAnswer

SELECT NAME, DEV_IN
FROM SOFTWARE
WHERE SOLD IN (SELECT MAX(SOLD)
FROM SOFTWARE
GROUP BY NAME)
UNION
SELECT NAME, DEV_IN
FROM SOFTWARE
WHERE SOLD IN (SELECT MIN(SOLD)
FROM SOFTWARE
GROUP BY NAME)

34) Who is the OLDEST female programmer WHO joined in 1992

Table Structure

Write your answer hereAnswer

SELECT NAME
FROM PROGRAMMER
WHERE DOJ=(SELECT (MIN(DOJ))
FROM PROGRAMMER
WHERE TO_CHAR(DOJ,YYYY) LIKE 1992)

35) In WHICH year where the MOST NUMBER of programmer born?

Table Structure

Write your answer hereAnswer

SELECT DISTINCT TO_CHAR(DOB,YYYY)
FROM PROGRAMMER
WHERE TO_CHAR(DOJ,YYYY) = (SELECT MIN(TO_CHAR(DOJ,YYYY))
FROM PROGRAMMER)

36) In WHICH month did MOST NUMBRER of programmer join?

Table Structure

Write your answer hereAnswer

SELECT DISTINCT TO_CHAR(DOJ,MONTH)
FROM PROGRAMMER
WHERE TO_CHAR(DOJ,MON) = (SELECT MIN(TO_CHAR(DOJ,MON))
FROM PROGRAMMER)

37) In WHICH language are MOST of the programmers proficient?

Table Structure

Write your answer hereAnswer

SELECT PROF1
FROM PROGRAMMER
GROUP BY PROF1
HAVING COUNT(PROF1)=(SELECT MAX(COUNT(PROF1))
FROM PROGRAMMER
GROUP BY PROF1)
OR COUNT(PROF2)=(SELECT MAX(COUNT(PROF2))
FROM PROGRAMMER
GROUP BY PROF2)
UNION
SELECT PROF2
FROM PROGRAMMER
GROUP BY PROF2
HAVING COUNT(PROF1)=(SELECT MAX(COUNT(PROF1))
FROM PROGRAMMER
GROUP BY PROF1)
OR COUNT(PROF2)=(SELECT MAX(COUNT(PROF2))
FROM PROGRAMMER
GROUP BY PROF2)

38) Who are the male programmers earning BELOW the AVERAGE salary of female programmers?

Table Structure

Write your answer hereAnswer

SELECT NAME
FROM PROGRAMMER
WHERE SEX LIKE M
AND SALARY < (SELECT(AVG(SALARY))
FROM PROGRAMMER
WHERE SEX LIKE F)

SQL Query collection: Set1 Set2 Set3 Set 4

Read More..

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