Collection of SQL queries with Answer and Output

Here is a collection or a list of 50 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) Find out the SELLING COST AVERAGE for the packages developed in PASCAL?

Table Structure

Write your answer hereAnswer

SELECT AVG(SCOST)
FROM SOFTWARE
WHERE DEV_IN LIKE PASCAL

2) Display the names and ages of all programmers.

Table Structure

Write your answer hereAnswer

SELECT NAME AS NAME,FLOOR((SYSDATE-DOB)/365) AS AGE
FROM PROGRAMMER

OR

SELECT 100 + MONTHS_BETWEEN(SYSDATE,DOB)/12 FROM PROGRAMMER;

3)---------------


4) What is the highest numbers of copies sold by a package?

Table Structure

Write your answer hereAnswer

SELECT MAX(SOLD) FROM SOFTWARE

5) Display the names and date of birth of all the programmer born in JANUARY.

Table Structure

Write your answer hereAnswer

SELECT DOB,NAME
FROM PROGRAMMER
WHERE TO_CHAR(DOB,MON) LIKE JAN

6) Display lowest course fee.

Table Structure

Write your answer hereAnswer

SELECT MIN(CCOST) FROM STUDIES

7) How many programmer has done PGDCA course.

Table Structure

Write your answer hereAnswer

SELECT COUNT(NAME)
FROM STUDIES
WHERE COURSE LIKE PGDCA

8) How much revenue has been earned through sales of packages in C.

Table Structure

Write your answer hereAnswer

SELECT SUM(SOLD*SCOST)
FROM SOFTWARE
WHERE DEV_IN LIKE C

9) Display the details of software developed by Ramesh?

Table Structure

Write your answer hereAnswer

SELECT *
FROM SOFTWARE
WHERE NAME=RAMESH

10) How many programmers studied at SABHARI.

Table Structure

Write your answer hereAnswer

SELECT COUNT(NAME) AS NOPROGRAMMERS
FROM STUDIES
WHERE SPLACE=SABHARI

11) Display the details of PACKAGES whose sales crossed the 20000 mark.

Table Structure

Write your answer hereAnswer

SELECT *
FROM SOFTWARE
WHERE (SOLD*SCOST)>20000

12) Find out the number of copies which should be sold in order to recover the development cost of each package.

Table Structure

Write your answer hereAnswer

SELECT ROUND(DCOST/SCOST)
FROM SOFTWARE
WHERE SCOST*SOLD

13) What is the price of the costliest software developed in BASIC?

Table Structure

Write your answer hereAnswer

SELECT MAX(SCOST)
FROM SOFTWARE
WHERE DEV_IN LIKE BASIC

14) Display the details of packages for which development cost has been recovered.

Table Structure

Write your answer hereAnswer

SELECT *
FROM SOFTWARE
WHERE (SOLD*SCOST)>DCOST

15) How many packages were developed in dbase?

Table Structure

Write your answer hereAnswer

SELECT COUNT(TITLE) AS TOTAL
FROM SOFTWARE
WHERE DEV_IN=DBASE

16) How many programmers studies at paragathi?

Table Structure

Write your answer hereAnswer

SELECT COUNT(NAME)
FROM STUDIES
WHERE SPLACE=PRAGATHI

17) How many programmers paid 5000 to 10000 for their course?

Table Structure

Write your answer hereAnswer

SELECT COUNT(NAME) AS NOOFPROGRAMMERS
FROM STUDIES
WHERE CCOST>=5000
AND CCOST<=10000

18) What is the average course fee?

Table Structure

Write your answer hereAnswer

SELECT AVG(CCOST) AS AVERAGECOST
FROM STUDIES

19) Display the details of programmers knowing c?

Table Structure

Write your answer hereAnswer

SELECT *
FROM PROGRAMMER
WHERE PROF1=C OR PROF2=C

20) How many programmers know either Cobol or Pascal?

Table Structure

Write your answer hereAnswer

SELECT COUNT(NAME) AS PROGRAMMERS
FROM PROGRAMMER
WHERE PROF1=COBOL
OR PROF1=PASCAL
OR PROF2=COBOL
OR PROF2=PASCAL

21) How many programmers dont know Pascal & C?

Table Structure

Write your answer hereAnswer

SELECT COUNT(NAME) AS PROGRAMMER
FROM PROGRAMMER
WHERE PROF1!=C
AND PROF1!=PASCAL
AND PROF2!=C
AND PROF2!=PASCAL

22) How old is the oldest male programmers?

Table Structure

Write your answer hereAnswer

SELECT MAX(FLOOR((SYSDATE - DOB)/365)) AS OLDESTMALEPROGRAMMERAGE
FROM PROGRAMMER

23) What is the average age of female programmers?

Table Structure

Write your answer hereAnswer

SELECT FLOOR(AVG(FLOOR((SYSDATE - DOB)/365))) AS AVERAGEFEMALEAGE
FROM PROGRAMMER

24) Calculate the experience in years for each programmers and display along with the names in descending order?

Table Structure

Write your answer hereAnswer

SELECT NAME AS PRNAME,FLOOR((SYSDATE - DOJ)/365) AS EXPERIENCE
FROM PROGRAMMER
ORDER BY NAME DESC

25) Who are the programmers who celebrate their birthday during the current month?

Table Structure

Write your answer hereAnswer

SELECT NAME
FROM PROGRAMMER
WHERE TO_CHAR(DOB,MM)=TO_CHAR(SYSDATE,MM)

26) How many female programmers are there?

Table Structure

Write your answer hereAnswer

SELECT COUNT(NAME) AS NOFPROG
FROM PROGRAMMER
WHERE SEX=F

27) What are the languages known by the male programmers?

Table Structure

Write your answer hereAnswer

SELECT DISTINCT PROF1 AS LANGUAGES
FROM PROGRAMMER
WHERE SEX=M
UNION
SELECT DISTINCT PROF2 AS LANGUAGES
FROM PROGRAMMER
WHERE SEX=M

28) What is the Average salary?

Table Structure

Write your answer hereAnswer

SELECT AVG(SALARY) AS AVGSAL
FROM PROGRAMMER

29) How many people draw 2000 to 4000?

Table Structure

Write your answer hereAnswer

SELECT NAME AS PRNAME
FROM PROGRAMMER
WHERE SALARY>= 2000
OR SALARY <=4000

30) Display the details of those who dont know Clipper, Cobol or Pascal?

Table Structure

Write your answer hereAnswer

SELECT *
FROM PROGRAMMER
WHERE PROF1 NOT IN (CLIPPER,COBOL,PASCAL)
AND PROF2 NOT IN (CLIPPER,COBOL,PASCAL)

31) How many Female programmers knowing C are above 24 years of age?

Table Structure

Write your answer hereAnswer

SELECT COUNT(NAME) AS NOOFFPRG
FROM PROGRAMMER
WHERE SEX=F
AND (PROF1=C OR PROF2=C)
AND ((SYSDATE-DOB)/365) > 24

32) Who are the programmers who will be celebrating their Birthday within a week?

Table Structure

Write your answer hereAnswer

SELECT NAME
FROM PROGRAMMER
WHERE TO_CHAR(DOB,WW) LIKE TO_CHAR(SYSDATE,WW)

OR

select * from programmer where
to_char(dob,dd) between to_char(sysdate,dd) and
to_char(next_day(sysdate,(to_char(sysdate,day))),dd) and
to_char(dob,mon) between to_char(sysdate,mon) and
to_char(next_day(sysdate,(to_char(sysdate,day))),mon)

33 Display the details of those with less than a years experience?

Table Structure

Write your answer hereAnswer

SELECT *
FROM PROGRAMMER
WHERE FLOOR((SYSDATE - DOJ)/365)<1

34 Display the details of those who will be completing 2 years of service this year?

Table Structure

Write your answer hereAnswer

SELECT NAME AS PRNAME
FROM PROGRAMMER WHERE FLOOR((SYSDATE-DOJ)/365)=2;

OR

select * from programmer where
(to_number(to_char(sysdate,yyyy))-to_number(to_char(doj,yyyy)))=2;

OR

SELECT * FROM PROGRAMMER WHERE
(100 + MONTHS_BETWEEN(31-DEC-00,DOJ)/12)>=2 AND
(100 + MONTHS_BETWEEN(SYSDATE,DOJ)/12) != 2;

35 Calculate the amount to be recovered for those packages whose development cost has not been recovered?

Table Structure

Write your answer hereAnswer

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

36)ist the packages which have not been sold so far?

Table Structure

Write your answer hereAnswer

SELECT TITLE AS SOFTNAME
FROM SOFTWARE
WHERE SOLD=0

37) Find out the cost of the software developed by Mary?

Table Structure

Write your answer hereAnswer

SELECT TITLE AS SOFTNAME,SCOST AS SOFTCOST
FROM SOFTWARE
WHERE NAME=MARY

38) Display the institutes names from the studies table without duplicates?

Table Structure

Write your answer hereAnswer

SELECT DISTINCT SPLACE
FROM STUDIES

39) How many different courses are mentioned in the studies table?

Table Structure

Write your answer hereAnswer

SELECT DISTINCT COURSE AS COURSES
FROM STUDIES

40) Display the names of the programmers whose names contain 2 occurrences of the letter A?

Table Structure

Write your answer hereAnswer

SELECT NAME
FROM PROGRAMMER
WHERE NAME LIKE %A%A%

41) Display the names of programmers whose names contain unto 5 characters?

Table Structure

Write your answer hereAnswer

SELECT NAME
FROM PROGRAMMER
WHERE LENGTH(NAME)=5

42) How many female programmers knowing COBOL have more than 2 years experience?

Table Structure

Write your answer hereAnswer

SELECT NAME AS PRNAME
FROM PROGRAMMER
WHERE FLOOR((SYSDATE-DOJ)/365)>2
AND SEX=F
AND (PROF1=COBOL OR PROF2=COBOL)

43) What is the length of the shortest name in the programmer table?

Table Structure

Write your answer hereAnswer

SELECT MIN(LENGTH(NAME)) AS SHORTNAME
FROM PROGRAMMER

44) What is the average development cost of a package developed in COBOL?

Table Structure

Write your answer hereAnswer

SELECT AVG(DCOST) AS AVGCOST
FROM SOFTWARE
WHERE DEV_IN=COBOL

45) Display the name,sex,dob(DD/MM/YY format), doj for all the programmers without using conversion function?

Table Structure

Write your answer hereAnswer

SELECT NAME AS PRNAME, SEX AS SEX, SUBSTR(DOB,1,2)||/||SUBSTR(DOB,4,3)||/||SUBSTR(DOB,8,2) AS DATEOFBIRTH, SUBSTR(DOJ,1,2)||/||SUBSTR(DOJ,4,3)||/||SUBSTR(DOJ,8,2) AS DATEOFJOIN FROM PROGRAMMER

46) Who are the programmers who were born on the last day of the month?

Table Structure

Write your answer hereAnswer

SELECT NAME AS PRNAME
FROM PROGRAMMER
WHERE LAST_DAY(DOB) LIKE DOB

47) What is the amount paid in salaries of the male programmers who do not know Cobol?

Table Structure

Write your answer hereAnswer

SELECT SALARY AS SALOFPR
FROM PROGRAMMER
WHERE SEX=M
AND (PROF1!=COBOL
OR PROF2!=COBOL)

48) Display the title, scost, dcost and difference between scost and dcost in descending order of difference?

Table Structure

Write your answer hereAnswer

SELECT TITLE AS SOFTNAME,SCOST AS SOFTCOST,DCOST AS DEVCOST,DCOST - SCOST DIFF FROM SOFTWARE
ORDER BY 4 DESC

49) Display the name, dob, doj of those month of birth and month of joining are same?

Table Structure

Write your answer hereAnswer

SELECT NAME AS PRNAME
FROM PROGRAMMER
WHERE TO_CHAR(DOB,MM)=TO_CHAR(DOJ,MM)

50) Display the names of the packages whose names contain more than 1 word?

Table Structure

Write your answer hereAnswer

SELECT TITLE AS PACKAGES
FROM SOFTWARE
WHERE TITLE LIKE % %

SQL Query collection: Set1 Set2 Set3 Set 4