SQL Assignment – Module 2
SQL Assignment – Module 2 SQL Assignment – Module 2
Questions :
1) What is the Highest Number of copies sold by a Package?
2) Display lowest course Fee.
3) How old is the Oldest Male Programmer.
4) What is the AVG age of Female Programmers?
5) Calculate the Experience in Years for each Programmer and Display with their names in Descending order.
6) How many programmers have done the PGDCA Course?
7) How much revenue has been earned thru sales of Packages Developed in C.
8) How many Programmers Studied at Sabhari?
9) How many Packages Developed in DBASE?
10) How many programmers studied in Pragathi?
11) How many Programmers Paid 5000 to 10000 for their course?
12) How many Programmers know either COBOL or PASCAL?
13) How many Female Programmers are there?
14) What is the AVG Salary?
15) How many people draw salary 2000 to 4000?
16) Display the sales cost of the packages Developed by each Programmer Language wise
17) Display the details of the software developed by the male students of Sabhari.
18) Who is the oldest Female Programmer who joined in 1992?
19) Who is the youngest male Programmer born in 1965?
20) Which Package has the lowest selling cost?
21) Which Female Programmer earning more than 3000 does not know C, C++, ORACLE or DBASE?
22) Who is the Youngest Programmer knowing DBASE?
23) Which Language is known by only one Programmer?
24) Who is the most experienced male programmer knowing PASCAL?
25) Who is the least experienced Programmer?
26) Display the Number of Packages in Each Language for which Development Cost is less than 1000.
27) Display Highest, Lowest and Average Salaries for those earning more than 2000.
Answer :
---ARNAB BOSE---
Select * from SOFTWARE
Select * from PROGRAMMER
Select * from STUDIES
---Q1---
SELECT
MAX(SOLD) MAX_SOLD
FROM SOFTWARE
---Q2---
SELECT MIN(STUDIES.COURSE_FEE)
FROM STUDIES
---Q3---
SELECT MAX(Age) MaxAge
FROM (
SELECT DATEDIFF(YEAR, DOB, GETDATE()) Age
FROM PROGRAMMER
WHERE GENDER = 'M'
) MAX_AGE
---Q4---
SELECT Avg(Age) AVGAge
FROM (
SELECT DATEDIFF(YEAR, DOB, GETDATE()) Age
FROM PROGRAMMER
WHERE GENDER = 'F'
) avg_AGE
--or-- both-ok---
SELECT AVG(DATEDIFF(YEAR, CONVERT(DATE, DOB, 103), GETDATE())) Avg_Age
FROM PROGRAMMER
WHERE GENDER = 'F';
---Q5---
SELECT PROGRAMMER.PNAME Name,
DATEDIFF(YEAR,DOJ,GETDATE()) [Total YearS Of Experience ]
FROM PROGRAMMER
ORDER BY [Total YearS Of Experience ] DESC
---Q6---
SELECT COUNT(*) TOTAL_PGDCA
FROM STUDIES
WHERE COURSE='PGDCA'
---Q7---
SELECT SUM(SCOST * SOLD) [TOTAL VALUE OF C]
FROM SOFTWARE
WHERE DEVELOPIN='C'
---ARNAB BOSE---
---Q8---
SELECT COUNT(*) [HOW MANY Sabhari]
FROM STUDIES
WHERE STUDIES.INSTITUTE='Sabhari'
---Q9---
SELECT COUNT(*) TOTAL
FROM SOFTWARE
WHERE DEVELOPIN='DBASE'
---OR---
SELECT COUNT(*) TOTAL
FROM PROGRAMMER
WHERE PROF1='DBASE' OR PROF2='DBASE'
---Q10---
SELECT COUNT(*) [HOW MANY Pragathi]
FROM STUDIES
WHERE STUDIES.INSTITUTE='Pragathi'
---Q11---
SELECT COUNT(*) AS count_number
FROM STUDIES
WHERE COURSE_FEE BETWEEN 5000 AND 10000
---Q12---
SELECT COUNT(*) TOTAL
FROM PROGRAMMER
WHERE PROF1 IN ('COBOL', 'PASCAL') OR PROF2 IN ('COBOL', 'PASCAL')
---OR---
SELECT COUNT(*) TOTAL
FROM PROGRAMMER
WHERE PROF1='COBOL' OR PROF1= 'PASCAL'
OR
PROF2='COBOL' OR PROF2='PASCAL'
---Q13---
SELECT COUNT(*) TOTAL_FEMALE
FROM PROGRAMMER
WHERE GENDER='F'
---Q14---
SELECT AVG(SALARY) Avg_Salary
FROM PROGRAMMER
---Q15---
SELECT COUNT(*) TOTAL
FROM PROGRAMMER
WHERE SALARY BETWEEN 2000 AND 4000
---Q16---
SELECT PNAME NAME,
DEVELOPIN,
SCOST,SOLD,
(SCOST*SOLD) TOTAL
FROM SOFTWARE
ORDER BY DEVELOPIN
--- IF You need language I mean programming language baSed Sold CASE then GROUP BY---
SELECT
DEVELOPIN [programming language],
SUM(SCOST*SOLD) TOTAL_COST
FROM SOFTWARE
GROUP BY DEVELOPIN
ORDER BY DEVELOPIN
---ARNAB BOSE---
---Q17---
SELECT S.PNAME, S.TITLE,
S.DEVELOPIN,
S.SCOST, S.DCOST,
S.SOLD,ST.INSTITUTE,
GENDER
FROM SOFTWARE S
JOIN PROGRAMMER p ON S.PNAME = p.PNAME
JOIN STUDIES ST ON ST.PNAME = p.PNAME
WHERE p.GENDER = 'M' AND ST.INSTITUTE = 'SABHARI'
---Q18---
---using month BCOZ YEAR ARE DIFF GET ONLY IN MONTH IF MONTH THEN DATE
--- HERE ALL SAME
SELECT TOP 1 PNAME NAME,DOJ,
DATEDIFF(MONTH, DOJ, GETDATE()) DATE_OF_JONNING
FROM PROGRAMMER
WHERE GENDER = 'F' AND DOJ LIKE '%92%'
ORDER BY DATEDIFF(MONTH, DOJ, GETDATE())
SELECT TOP 1 PNAME NAME
FROM PROGRAMMER
WHERE GENDER = 'F' AND DOJ LIKE '%92%'
ORDER BY DATEDIFF(YEAR, DOJ, GETDATE())
---Q19---
---using month BCOZ YEAR ARE DIFF GET ONLY IN MONTH
SELECT TOP 1 PNAME NAME,DOB,
DATEDIFF(MONTH, DOB, GETDATE()) [ TOTAL MONTH]
FROM PROGRAMMER
WHERE GENDER = 'M' AND DOB LIKE '%65%'
ORDER BY DATEDIFF(MONTH, DOB, GETDATE())
--- USING YEAR ---
SELECT TOP 1 PNAME NAME,DOB,
DATEDIFF(YEAR, DOB, GETDATE()) [ TOTAL YEAR]
FROM PROGRAMMER
WHERE GENDER = 'M' AND DOB LIKE '%65%'
ORDER BY DATEDIFF(YEAR, DOB, GETDATE())
SELECT PNAME NAME,DOB,DATEDIFF(MONTH, DOB, GETDATE())
FROM PROGRAMMER
WHERE GENDER = 'M' AND DOB LIKE '%65%'
ORDER BY DATEDIFF(MONTH, DOB, GETDATE()) DESC
---Q20---
SELECT TOP 1 TITLE
FROM SOFTWARE
WHERE SCOST =(SELECT MIN(SCOST) MIN_COST FROM SOFTWARE)
---AND ---
SELECT TOP 1 TITLE
FROM SOFTWARE
ORDER BY SCOST
---Q21---
SELECT *
FROM PROGRAMMER
WHERE GENDER = 'F' AND SALARY > 3000 AND
PROF1 NOT IN ('C', 'CPP', 'ORACLE', 'DBASE') AND
PROF2 NOT IN ('C', 'CPP', 'ORACLE', 'DBASE')
---Q22---
SELECT TOP 1 PNAME,DATEDIFF(YEAR,DOB,GETDATE()) AGE
FROM PROGRAMMER
WHERE PROF1 = 'DBASE' OR PROF2 = 'DBASE'
ORDER BY DATEDIFF(YEAR,DOB,GETDATE()) ASC
---Q23---
SELECT Language
FROM (
SELECT PROF1 Language FROM PROGRAMMER
UNION ALL
SELECT PROF2 Language FROM PROGRAMMER
) AS Languages
GROUP BY Language
HAVING COUNT(*) = 1
---Q24---
SELECT TOP 1 PNAME, DATEDIFF(YEAR, DOJ, GETDATE()) EXP_YEARS
FROM PROGRAMMER
WHERE GENDER = 'M' AND (PROF1 = 'PASCAL' OR PROF2 = 'PASCAL')
ORDER BY DATEDIFF(YEAR, DOJ, GETDATE()) DESC
---Q25---
SELECT TOP 1 PNAME, DATEDIFF(YEAR, DOJ, GETDATE()) EXP_YEARS
FROM PROGRAMMER
ORDER BY DATEDIFF(YEAR, DOJ, GETDATE())
---Q26---
SELECT SOFTWARE.DEVELOPIN Language ,
COUNT(*) TOTAL_NUMBERS
FROM SOFTWARE
WHERE DCOST<1000
GROUP BY SOFTWARE.DEVELOPIN
---Q27---
SELECT MAX(SALARY) HighestSalary,
MIN(SALARY) LowestSalary,
AVG(SALARY) AverageSalary
FROM PROGRAMMER
WHERE SALARY > 2000
---ARNAB BOSE---
Comments
Post a Comment