SQL Assignment – Module 4
SQL Assignment – Module 4 SQL Assignment – Module 4
Questions :
1. What is the cost of the costliest software development in Basic?
2. Display details of Packages whose sales crossed the 2000 Mark.
3. Who are the Programmers who celebrate their Birthdays during the Current Month?
4. Display the Cost of Package Developed By each Programmer.
5. Display the sales values of the Packages Developed by each Programmer.
6. Display the Number of Packages sold by Each Programmer.
7. Display each programmer’s name, costliest and cheapest Packages Developed by him or her.
8. Display each institute name with the number of Courses, Average Cost per Course.
9. Display each institute Name with Number of Students.
10. List the programmers (form the software table) and the institutes they studied.
11. How many packages were developed by students, who studied in institute that charge the lowest course fee?
12. What is the AVG salary for those whose software sales is more than 50,000/-.
13. Which language listed in prof1, prof2 has not been used to develop any package.
14. Display the total sales value of the software, institute wise.
15. Display the details of the Software Developed in C By female programmers of Pragathi.
16. Display the details of the packages developed in Pascal by the Female Programmers.
17. Which language has been stated as the proficiency by most of the Programmers?
18. Who is the Author of the Costliest Package?
19. Which package has the Highest Development cost?
20. Who is the Highest Paid Female COBOL Programmer?
21. Display the Name of Programmers and Their Packages.
22. Display the Number of Packages in Each Language Except C and C++.
23. Display AVG Difference between SCOST, DCOST for Each Package.
24. Display the total SCOST, DCOST and amount to Be Recovered for each Programmer for Those Whose Cost has not yet been Recovered.
25. Who is the Highest Paid C Programmers?
26. Who is the Highest Paid Female COBOL Programmer?
Answer :
---ARNAB BOSE----
SELECT * FROM PROGRAMMER
SELECT * FROM SOFTWARE
SELECT * FROM STUDIES
---Q1---
SELECT MAX(DCOST) [MAX BASIC PRICE]
FROM SOFTWARE
WHERE DEVELOPIN = 'Basic'
---Q2---
SELECT *
FROM SOFTWARE
WHERE (SCOST * SOLD) > 2000
---Q4---
SELECT PNAME, DOB
FROM PROGRAMMER
WHERE MONTH(DOB) = MONTH(GETDATE())
---Q5---
SELECT P.PNAME Programmer_Name,
S.TITLE Package_Title,
S.DCOST Dev_Package_Cost
FROM PROGRAMMER P
INNER JOIN SOFTWARE S ON P.PNAME = S.PNAME
---Q6---
SELECT P.PNAME AS ProgrammerName, COUNT(*) AS NumberOfPackagesSold
FROM PROGRAMMER P
INNER JOIN SOFTWARE S ON P.PNAME = S.PNAME
WHERE S.SOLD > 0
GROUP BY P.PNAME
---Q7---
SELECT PNAME [ Programmer Name],
MAX(SCOST) [ CostliestPackage ] ,
MIN(SCOST) [ CheapestPackage ]
FROM SOFTWARE
GROUP BY PNAME
---OR---
SELECT P.PNAME [ Programmer Name],
MAX(S.SCOST) [ CostliestPackage ] ,
MIN(S.SCOST) [ CheapestPackage ]
FROM PROGRAMMER P
INNER JOIN SOFTWARE S ON P.PNAME = S.PNAME
INNER JOIN STUDIES ST ON ST.PNAME = S.PNAME
--FULL JOIN SOFTWARE S ON P.PNAME = S.PNAME
--FULL JOIN STUDIES ST ON ST.PNAME = S.PNAME
GROUP BY P.PNAME
---Q8---
SELECT INSTITUTE,
COUNT(*) [ Num Courses ],
AVG(COURSE_FEE) [ AvgCostPerCourse]
FROM STUDIES
GROUP BY INSTITUTE
---ARNAB BOSE----
---Q9---
SELECT INSTITUTE,
COUNT(*) AS [ Num Students ]
FROM STUDIES
GROUP BY INSTITUTE
ORDER BY [ Num Students ] DESC
---Q10---
SELECT ST.PNAME, S.INSTITUTE
FROM SOFTWARE ST
INNER JOIN STUDIES S ON ST.PNAME = S.PNAME
---OR---
SELECT DISTINCT( ST.PNAME) , S.INSTITUTE
FROM SOFTWARE ST
INNER JOIN STUDIES S ON ST.PNAME = S.PNAME
---Q11---
SELECT COUNT(*) [ TOTAL ]
FROM SOFTWARE S
JOIN STUDIES ST
ON S.PNAME=ST.PNAME
WHERE ST.COURSE_FEE=(SELECT MIN(COURSE_FEE) FROM STUDIES)
---Q12---
SELECT AVG(SALARY) [ Avg Salary ]
FROM PROGRAMMER
WHERE PNAME IN
(SELECT PNAME FROM SOFTWARE WHERE (SCOST * SOLD) > 50000)
---Q13---
SELECT PROF1 Languages
FROM PROGRAMMER
WHERE PROF1 NOT IN
(SELECT DISTINCT (DEVELOPIN) FROM SOFTWARE)
UNION
SELECT PROF2 Languages
FROM PROGRAMMER
WHERE PROF2 NOT IN
(SELECT DISTINCT (DEVELOPIN) FROM SOFTWARE)
---Q14---
SELECT ST.INSTITUTE,
SUM(S.SOLD*S.SCOST) [ TOTAL SALES ]
--,COUNT(*) [NUMBERS OF STUDENTS ]
FROM SOFTWARE S
INNER JOIN STUDIES ST
ON S.PNAME=ST.PNAME
GROUP BY ST.INSTITUTE
---Q15---
SELECT S.*
FROM SOFTWARE S
INNER JOIN PROGRAMMER P ON S.PNAME = P.PNAME
INNER JOIN STUDIES ST ON S.PNAME = ST.PNAME
WHERE S.DEVELOPIN = 'C' AND P.GENDER = 'F' AND ST.INSTITUTE = 'Pragathi'
---Q16---
SELECT *
FROM SOFTWARE
WHERE DEVELOPIN = 'Pascal' AND
PNAME IN (SELECT PNAME FROM PROGRAMMER WHERE GENDER = 'F')
---ARNAB BOSE----
---Q17---
SELECT TOP 1 proficiency, COUNT(*) AS proficiency_count
FROM (
SELECT prof1 proficiency FROM PROGRAMMER
UNION ALL
SELECT prof2 proficiency FROM PROGRAMMER
) AS combined_proficiencies
GROUP BY proficiency
ORDER BY proficiency_count DESC
---Q18---
SELECT *
FROM SOFTWARE
WHERE SCOST = (SELECT MAX(SCOST) FROM SOFTWARE)
---Q19---
SELECT *
FROM SOFTWARE
WHERE DCOST = (SELECT MAX(DCOST) FROM SOFTWARE)
---Q20---
SELECT *
FROM PROGRAMMER P
JOIN SOFTWARE S
ON P.PNAME=S.PNAME
WHERE P.GENDER='F'
AND S.DEVELOPIN='COBOL'
AND P.SALARY=(SELECT MAX(SALARY) FROM PROGRAMMER P
JOIN SOFTWARE S
ON P.PNAME=S.PNAME
WHERE P.GENDER='F' AND S.DEVELOPIN='COBOL' )
---ARNAB BOSE----
---Q21---
SELECT S.PNAME, P.SALARY Packages
FROM SOFTWARE S
INNER JOIN PROGRAMMER P
ON P.PNAME=S.PNAME
---Q22---
SELECT DEVELOPIN, COUNT(*) NumPackages
FROM SOFTWARE
WHERE DEVELOPIN NOT IN ('C', 'CPP')
GROUP BY DEVELOPIN
---Q23---
SELECT DEVELOPIN, AVG(SCOST - DCOST) AvgDifference
FROM SOFTWARE
GROUP BY DEVELOPIN
---Q24---
SELECT PNAME,
SCOST Total_SCOST,
DCOST Total_DCOST,
((SCOST*SOLD) - DCOST ) [ Amount NOT Recovered ]
FROM SOFTWARE
WHERE ((SCOST*SOLD) - DCOST ) <0
---Q25---
SELECT *
FROM PROGRAMMER
WHERE
SALARY =(SELECT MAX(SALARY) FROM PROGRAMMER WHERE (PROF1 LIKE 'C' OR PROF2 LIKE 'C') AND GENDER='F')
---OR---
SELECT TOP 1 PNAME, SALARY
FROM PROGRAMMER
WHERE (PROF1 = 'C' OR PROF2 = 'C') AND GENDER ='F'
ORDER BY SALARY DESC
---Q26---
SELECT TOP 1 PNAME, SALARY
FROM PROGRAMMER
WHERE GENDER = 'F' AND (PROF1 = 'COBOL' OR PROF2 = 'COBOL')
ORDER BY SALARY DESC
---ARNAB BOSE----
Comments
Post a Comment