Thursday, February 11, 2016

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

Answer
SELECT AVG(SCOST)
FROM SOFTWARE
WHERE DEV_IN LIKE PASCAL

2) Display the names and ages of all programmers.

Table Structure

Answer
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

Answer
SELECT MAX(SOLD) FROM SOFTWARE







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

Table Structure

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




6) Display lowest course fee.

Table Structure

Answer
SELECT MIN(CCOST) FROM STUDIES



7) How many programmer has done PGDCA course.

Table Structure

Answer
SELECT COUNT(NAME)
FROM STUDIES
WHERE COURSE LIKE PGDCA



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

Table Structure

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


9) Display the details of software developed by Ramesh?

Table Structure

Answer
SELECT *
FROM SOFTWARE
WHERE NAME=RAMESH




10) How many programmers studied at SABHARI.

Table Structure

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



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

Table Structure

Answer
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

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


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

Table Structure

Answer
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

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



15) How many packages were developed in dbase?

Table Structure

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




16) How many programmers studies at paragathi?

Table Structure

Answer
SELECT COUNT(NAME)
FROM STUDIES
WHERE SPLACE=PRAGATHI


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

Table Structure

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




18) What is the average course fee?

Table Structure

Answer
SELECT AVG(CCOST) AS AVERAGECOST
FROM STUDIES




19) Display the details of programmers knowing c?

Table Structure

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




20) How many programmers know either Cobol or Pascal?

Table Structure

Answer
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

Answer
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

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




23) What is the average age of female programmers?

Table Structure

Answer
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

Answer
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

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



26) How many female programmers are there?

Table Structure

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




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

Table Structure

Answer
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

Answer
SELECT AVG(SALARY) AS AVGSAL
FROM PROGRAMMER



29) How many people draw 2000 to 4000?

Table Structure

Answer
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

Answer
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

Answer
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

Answer
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

Answer
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

Answer
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

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


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

Table Structure

Answer
SELECT TITLE AS SOFTNAME
FROM SOFTWARE
WHERE SOLD=0



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

Table Structure

Answer
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

Answer
SELECT DISTINCT SPLACE
FROM STUDIES




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

Table Structure

Answer
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

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



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

Table Structure

Answer
SELECT NAME
FROM PROGRAMMER
WHERE LENGTH(NAME)=5



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

Table Structure

Answer
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

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




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

Table Structure

Answer
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

Answer
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

Answer
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

Answer
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

Answer
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

Answer
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

Answer
SELECT TITLE AS PACKAGES
FROM SOFTWARE
WHERE TITLE LIKE % %


SQL Query collection: Set1 Set2 Set3 Set 4

0 comments to “Collection of SQL queries with Answer and Output”

Post a Comment

 

Computer Info Copyright © 2016 -- Powered by Blogger