SQL Assignment – Module 3

                        SQL Assignment – Module 3 SQL Assignment – Module 3

Questions :

1. How many Programmers Don’t know PASCAL and C 

2. Display the details of those who don’t know Clipper, COBOL or PASCAL. 

3. Display each language name with AVG Development Cost, AVG Selling Cost and AVG Price per Copy.

 4. List the programmer names (from the programmer table) and No. Of Packages each has developed.

 5. List each PROFIT with the number of Programmers having that PROF and the number of the packages in that PROF. 

6. How many packages are developed by the most experienced programmer form BDPS. 

7. How many packages were developed by the female programmers earning more than the highest paid male programmer? 

8. How much does the person who developed the highest selling package earn and what course did HE/SHE undergo. 

9. In which institute did the person who developed the costliest package study? 

10. Display the names of the programmers who have not developed any packages. 

11. Display the details of the software that has developed in the language which is neither the first nor the second proficiency 

12. Display the details of the software Developed by the male programmers Born before 1965 and female programmers born after 1975 

13. Display the number of packages, No. of Copies Sold and sales value of each programmer institute wise. 

14. Display the details of the Software Developed by the Male Programmers Earning More than 3000/

 15. Who are the Female Programmers earning more than the Highest Paid male? 

16. Who are the male programmers earning below the AVG salary of Female Programmers?

 17. Display the language used by each programmer to develop the Highest Selling and Lowest-selling package. 

18. Display the names of the packages, which have sold less than the AVG number of copies. 

19. Which is the costliest package developed in PASCAL. 

20. How many copies of the package that has the least difference between development and selling cost were sold. 

21. Which language has been used to develop the package, which has the highest sales amount? 

22. Who Developed the Package that has sold the least number of copies? 

23. Display the names of the courses whose fees are within 1000 (+ or -) of the Average Fee 

24. Display the name of the Institute and Course, which has below AVG course fee. 

25. Which Institute conducts costliest course. 

26. What is the Costliest course?

Answer :

---ARNAB BOSE---

SELECT * FROM PROGRAMMER

SELECT * FROM SOFTWARE

SELECT * FROM STUDIES

---Q1---

SELECT COUNT(*) [NUMBER OF PROG.]

FROM  PROGRAMMER

WHERE PROF1 NOT IN ('PASCAL' ,'C') 

AND

PROF2 NOT IN ('PASCAL' ,'C') 

---Q2---

SELECT *

FROM PROGRAMMER

WHERE PROF1 NOT IN ('Clipper', 'COBOL', 'PASCAL') 

AND

PROF2 NOT IN ('Clipper', 'COBOL', 'PASCAL')

---Q3---

SELECT DEVELOPIN,

AVG (SCOST) [ AVG Selling Cost ],

AVG (DCOST) [AVG Development Cost],

AVG(SCOST*SOLD )[ AVG Price per Copy],

SUM(SCOST*SOLD ) [ TOTAL PRICE],

COUNT(*) [TOTAL  Member ],

AVG(SOLD) [AVG SOLD]

FROM SOFTWARE

WHERE SOLD >0

GROUP BY DEVELOPIN

---Q4---

SELECT P.PNAME,COUNT(*) TOTAL_NUM

FROM PROGRAMMER P

INNER JOIN SOFTWARE S

ON P.PNAME= S.PNAME

GROUP BY P.PNAME

---Q5---

SELECT DEVELOPIN, COUNT(*)  NumPackages, SUM((SCOST * SOLD) - DCOST) PROFIT

FROM SOFTWARE

WHERE DEVELOPIN IN (SELECT PROF1 FROM PROGRAMMER)

AND 

((SCOST * SOLD) - DCOST)>0

GROUP BY DEVELOPIN

---Q6---

SELECT COUNT(*) [DEVELOPIN BY MOSE EXP PERSON]

FROM

(SELECT  TOP 1 ST.PNAME NAME , 

DATEDIFF(YEAR,DOJ,GETDATE()) EXP_YEARS ,

DEVELOPIN

FROM STUDIES ST

INNER JOIN SOFTWARE SW ON ST.PNAME=SW.PNAME

INNER JOIN PROGRAMMER P ON P.PNAME=SW.PNAME

WHERE INSTITUTE='BDPS'

ORDER BY EXP_YEARS DESC) EXPYEAR

GROUP BY DEVELOPIN

---Q7---

SELECT COUNT(DEVELOPIN)[No Of Packages Developed] ,

S.PNAME NAME

FROM SOFTWARE S

JOIN

PROGRAMMER P

ON P.PNAME=S.PNAME 

WHERE GENDER='F' 

AND 

SALARY>

(SELECT MAX(SALARY) 

FROM PROGRAMMER 

WHERE GENDER='M')

GROUP BY S.PNAME

---Q8---

SELECT TOP 1 P.PNAME NAME,P.SALARY,S.DEVELOPIN

FROM SOFTWARE S

JOIN

PROGRAMMER P

ON P.PNAME=S.PNAME 

WHERE P.PNAME=( SELECT TOP 1 PNAME FROM SOFTWARE ORDER BY (SOLD*SCOST) DESC)

---OR---

SELECT P.PNAME, P.SALARY, P.PROF1

FROM PROGRAMMER P

INNER JOIN (

    SELECT TOP 1 PNAME

    FROM SOFTWARE

    ORDER BY SCOST DESC

) S ON P.PNAME = S.PNAME

---ARNAB BOSE---

---Q9---

SELECT SW.PNAME,S.INSTITUTE,SW.DCOST 

FROM SOFTWARE SW

INNER JOIN STUDIES S 

ON SW.PNAME = S.PNAME

WHERE DCOST = (

    SELECT MAX(DCOST)

    FROM SOFTWARE

)

---Q10---

SELECT PNAME

FROM PROGRAMMER

WHERE PNAME NOT IN (

    SELECT PNAME

    FROM SOFTWARE

)

---OR---

SELECT P.PNAME,S.DEVELOPIN

FROM PROGRAMMER P

LEFT JOIN SOFTWARE S

ON P.PNAME=S.PNAME

WHERE S.DEVELOPIN IS NULL

---Q11---

SELECT PROF1 [NOT IN DEV. ]

FROM PROGRAMMER

WHERE PROF1 NOT IN (SELECT DEVELOPIN  FROM SOFTWARE)

UNION

SELECT PROF2 [NOT IN DEV. ]

FROM PROGRAMMER

WHERE PROF2 NOT IN (SELECT DEVELOPIN  FROM SOFTWARE)

---Q12---

SELECT * FROM PROGRAMMER P

JOIN SOFTWARE S

ON P.PNAME=S.PNAME

WHERE ((P.GENDER='M' AND  YEAR(DOB)<1965)

OR 

(P.GENDER='F' AND  YEAR(DOB)>1975))

SELECT *,YEAR(DOB) FROM PROGRAMMER P

WHERE YEAR(DOB)>1975

--JOIN SOFTWARE S

--ON P.PNAME=S.PNAME

---Q13---

SELECT ST.INSTITUTE,ST.PNAME,

       COUNT(*) AS [No. Of Packages],

       SUM(S.SOLD) AS [No. Of Copies Sold],

       SUM(S.SCOST * S.SOLD) AS [Sales Value]

FROM STUDIES ST

INNER JOIN SOFTWARE S ON ST.PNAME = S.PNAME

GROUP BY ST.INSTITUTE,ST.PNAME

---Q14---

SELECT *

FROM PROGRAMMER P

INNER JOIN SOFTWARE S 

ON

P.PNAME = S.PNAME

WHERE GENDER = 'M' AND SALARY > 3000

---Q15--

SELECT P.PNAME, P.SALARY,P.GENDER

FROM PROGRAMMER P

WHERE P.GENDER = 'F'

AND P.SALARY > (SELECT MAX(SALARY) FROM PROGRAMMER WHERE GENDER = 'M');

---OR---

SELECT P.PNAME, P.SALARY,P.GENDER, NEW.HIGHEST_SALARY [HIGHEST SALARY]

FROM PROGRAMMER P

INNER JOIN (

    SELECT MAX(SALARY) AS [HIGHEST_SALARY]

    FROM PROGRAMMER

    WHERE GENDER = 'M'

) NEW ON P.SALARY > NEW.HIGHEST_SALARY

WHERE GENDER = 'F'

---ARNAB BOSE---

---Q16---

SELECT * 

FROM PROGRAMMER

WHERE GENDER = 'M'

AND 

SALARY<(SELECT AVG(SALARY) FROM PROGRAMMER WHERE GENDER='F')

---Q17---

SELECT S.DEVELOPIN,

       MAX(S.SCOST) AS "Highest Selling Package",

       MIN(S.SCOST) AS "Lowest-selling Package"

FROM SOFTWARE S

GROUP BY DEVELOPIN

---Q18---

SELECT *

FROM SOFTWARE 

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

---Q19---

SELECT TOP 1 *

FROM SOFTWARE

WHERE DEVELOPIN = 'PASCAL'

ORDER BY SCOST DESC

---OR---

select * from software 

where scost = 

(select max(scost) from software 

group by developin 

having DEVELOPIN ='pascal')

---Q20---

SELECT * ,(Dcost - SCOST) DIFF_COST

FROM software

WHERE (DCOST - SCOST) = ((

    SELECT MIN(Dcost - Scost) AS COST

    FROM software

--WHERE (Dcost - Scost) >0

)

)

---OR---

SELECT SOLD

FROM software

WHERE (DCOST - SCOST) = ((

    SELECT MIN(Dcost - Scost) AS COST

    FROM software

--WHERE (Dcost - Scost) >0

)

)

---Q21---

SELECT TOP 1 *,(SCOST * SOLD) [MAX PRICE]

FROM SOFTWARE

ORDER BY SCOST * SOLD DESC

---OR---

SELECT * 

FROM SOFTWARE

WHERE (SOLD*SCOST)=(SELECT MAX(SOLD*SCOST) FROM SOFTWARE)

---Q22---

SELECT PNAME,DEVELOPIN,SOLD [MIN SOLD]

FROM SOFTWARE

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

---OR---

SELECT TOP 1 PNAME,DEVELOPIN,SOLD [MIN SOLD]

FROM SOFTWARE

ORDER BY  SOLD 

---Q23---

SELECT COURSE

FROM STUDIES

WHERE COURSE_FEE BETWEEN

(SELECT AVG(COURSE_FEE) + 1000 FROM STUDIES) 

AND

(SELECT AVG(COURSE_FEE) - 1000 FROM STUDIES)

---Q24---

SELECT *

FROM STUDIES

WHERE COURSE_FEE < (SELECT AVG(COURSE_FEE) FROM STUDIES)

---Q25---

SELECT INSTITUTE

FROM STUDIES

WHERE COURSE_FEE = (SELECT MAX(COURSE_FEE) FROM STUDIES)

---Q26---

SELECT COURSE

FROM STUDIES

WHERE COURSE_FEE = (SELECT MAX(COURSE_FEE) FROM STUDIES)

---ARNAB BOSE---