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

class="commenthidden" id="hideshowdemo019">
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