Tuesday, October 25, 2016

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

Answer
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

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



3) Display THE NUMBER OF male and female programmer.

Table Structure

Answer
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

Answer
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

Answer


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

Table Structure

Answer
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

Answer
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

Answer
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

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





10) Display the language wise COUNTS of prof2.

Table Structure

Answer
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

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




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

Table Structure

Answer
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

Answer
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

Answer
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

Answer
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

Answer
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

Answer
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

Answer
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

Answer
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

Answer
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

Answer
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

Answer
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

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




24) Display names of male and female programmers.

Table Structure

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





25) Display the programmers name and their packages.

Table Structure

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




26) Display the NUMBER of packages in EACH language.

Table Structure

Answer
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

Answer
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

Answer
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

Answer
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

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


SQL Query collection: Set1 Set2 Set3 Set 4

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

Post a Comment

 

Computer Info Copyright © 2016 -- Powered by Blogger