CREATE DATABASE ASSIGNMENT;
USE ASSIGNMENT;
CREATE TABLE Studies (PNAME VARCHAR(25) NOT NULL, INSTITUTE VARCHAR(25) NOT NULL,
COURSE VARCHAR(15) NOT NULL, COURSE_FEE INT NOT NULL);
CREATE TABLE Software (PNAME VARCHAR(25) NOT NULL, TITLE VARCHAR(25) NOT NULL,
DEVELOPIN VARCHAR(15) NOT NULL, SCOST FLOAT NOT NULL, DCOST FLOAT NOT NULL, SOLD INT
NOT NULL);
CREATE TABLE Programmer (PNAME VARCHAR(25) NOT NULL, DOB DATE NOT NULL, DOJ DATE NOT
NULL, GENDER CHAR(1) NOT NULL, PROF1 VARCHAR(10) NOT NULL, PROF2 VARCHAR(10) NOT NULL,
SALARY INT NOT NULL);
SELECT * FROM Studies;
SELECT * FROM Software;
SELECT * FROM Programmer;
INSERT INTO Studies (PNAME, INSTITUTE, COURSE, COURSE_FEE) VALUES
('ANAND', 'SABHARI', 'PGDCA', 4500),
('ALTAF', 'COIT', 'DCA', 7200),
('JULIANA', 'BDPS', 'MCA', 22000),
('KAMALA', 'PRAGATHI', 'DCA', 5000),
('MARY', 'SABHARI', 'PGDCA', 4500),
('NELSON', 'PRAGATHI', 'DAP', 6200),
('PATRICK', 'PRAGATHI', 'DCAP', 5200),
('QADIR', 'APPLE', 'HDCA', 14000),
('RAMESH', 'SABHARI', 'PGDCA', 4500),
('REBECCA', 'BRILLIANT', 'DCAP', 11000),
('REMITHA', 'BDPS', 'DCS', 6000),
('REVATHI', 'SABHARI', 'DAP', 5000),
('VIJAYA', 'BDPS', 'DCA', 48000);
INSERT INTO Software (PNAME, TITLE, DEVELOPIN, SCOST, DCOST, SOLD) VALUES
('MARY', 'README', 'CPP', 300, 1200, 84),
('ANAND', 'PARACHUTES', 'BASIC', 399.95, 6000, 43),
('ANAND', 'VIDEO TITLING', 'PASCAL', 7500, 16000, 9),
('JULIANA', 'INVENTORY', 'COBOL', 3000, 3500, 0),
('KAMALA', 'PAYROLL PKG.', 'DBASE', 9000, 20000, 7),
('MARY', 'FINANCIAL ACCT.', 'ORACLE', 18000, 85000, 4),
('MARY', 'CODE GENERATOR', 'C', 4500, 20000, 23),
('PATTRICK', 'README', 'CPP', 300, 1200, 84),
('QADIR', 'BOMBS AWAY', 'ASSEMBLY', 750, 3000, 11),
('QADIR', 'VACCINES', 'C', 1900, 3100, 21),
('RAMESH', 'HOTEL MGMT.', 'DBASE', 13000, 35000, 4),
('RAMESH', 'DEAD LEE', 'PASCAL', 599.95, 4500, 73),
('REMITHA', 'PC UTILITIES', 'C', 725, 5000, 51),
('REMITHA', 'TSR HELP PKG.', 'ASSEMBLY', 2500, 6000, 7),
('REVATHI', 'HOSPITAL MGMT.', 'PASCAL', 1100, 75000, 2),
('VIJAYA', 'TSR EDITOR', 'C', 900, 700, 6);
INSERT INTO Programmer (PNAME, DOB, DOJ, GENDER, PROF1, PROF2, SALARY) VALUES
('ANAND', '12-Apr-66', '21-Apr-92', 'M', 'PASCAL', 'BASIC', 3200),
('ALTAF', '02-Jul-64', '13-Nov-90', 'M', 'CLIPPER', 'COBOL', 2800),
('JULIANA', '31-Jan-60', '21-Apr-90', 'F', 'COBOL', 'DBASE', 3000),
('KAMALA', '30-Oct-68', '02-Jan-92', 'F', 'C', 'DBASE', 2900),
('MARY', '24-Jun-70', '01-Feb-91', 'F', 'CPP', 'ORACLE', 4500),
('NELSON', '11-Sep-85', '11-Oct-89', 'M', 'COBOL', 'DBASE', 2500),
('PATTRICK', '10-Nov-65', '21-Apr-90', 'M', 'PASCAL', 'CLIPPER', 2800),
('QADIR', '31-Aug-65', '21-Apr-91', 'M', 'ASSEMBLY', 'C', 3000),
('RAMESH', '03-May-67', '28-Feb-91', 'M', 'PASCAL', 'DBASE', 3200),
('REBECCA', '01-Jan-67', '01-Dec-90', 'F', 'BASIC', 'COBOL', 2500),
('REMITHA', '19-Apr-70', '20-Apr-93', 'F', 'C', 'ASSEMBLY', 3600),
('REVATHI', '02-Dec-69', '02-Jan-92', 'F', 'PASCAL', 'BASIC', 3700),
('VIJAYA', '14-Dec-65', '02-May-92', 'F', 'FOXPRO', 'C', 3500);
-- 1. Find out the selling cost average for packages developed in Pascal
SELECT AVG(SCOST) AS Pascal_Selling_Cost_Avg
FROM Software
WHERE DEVELOPIN= 'PASCAL'
-- 2. Display the names and ages of all programmers.
SELECT PNAME, DATEDIFF(YEAR,DOB, GETDATE()) AS AGE
FROM Programmer;
-- 3. Display the names of those who have done the DAP Course.
SELECT PNAME FROM Studies
WHERE Course= 'DAP';
-- 4. Display the names and date of birth of all programmers born in January
SELECT PNAME, DOB FROM Programmer
WHERE DOB LIKE '%01%';
-- 5. What is the highest number of copies sold by a package?
SELECT MAX(SOLD) AS Highest_Copies_Sold
FROM Software;
--- SELECT TITLE, MAX(SOLD) AS Highest_Copies_Sold
--- FROM Software
--- GROUP BY TITLE
--- ORDER BY Highest_Copies_Sold DESC
-- 6. Display lowest course fee.
SELECT MIN(COURSE_FEE) AS Lowest_Course_Fee
FROM Studies;
-- 7. How many programmers have done the PGDCA Course?
SELECT COUNT(PNAME) AS Ct
FROM Studies
WHERE COURSE= 'PGDCA';
-- 8. How much revenue has been earned through sales of packages developed in C?
SELECT SUM(SCOST) AS Sales_through_C
FROM Software
WHERE DEVELOPIN= 'C';
-- 9. Display the details of the software developed by Ramesh.
SELECT *
FROM Software
WHERE PNAME= 'Ramesh';
-- 10. How many programmers studied at Sabhari
SELECT COUNT(PNAME) AS Ct
FROM Studies
WHERE Institute= 'Sabhari';
-- 11. Display details of packages whose sales crossed the 2000 mark.
Select *
FROM Software
WHERE SCOST>2000;
-- 12. Display the details of packages for which development costs have been recovered.
SELECT *
FROM Software
WHERE DCOST>=SCOST;
-- 13. What is the cost of the costliest software development in Basic?
SELECT MAX(DCOST) AS Costliest_Software
FROM SOFTWARE
WHERE DEVELOPIN = 'BASIC'
-- 14. How many packages have been developed in dBase?
SELECT COUNT(DEVELOPIN) AS Ct_of_dBase
FROM SOFTWARE
WHERE DEVELOPIN = 'DBASE'
-- 15. How many programmers studied in Pragathi?
SELECT COUNT(PNAME) AS Ct FROM STUDIES WHERE INSTITUTE = 'Pragathi'
-- 16. How many programmers paid 5000 to 10000 for their course?
SELECT COUNT(PNAME) AS Ct FROM Studies WHERE Course_Fee BETWEEN 5000 AND 10000
-- 17. What is the average course fee?
SELECT AVG(Course_Fee) AS AVG_Course_Fee FROM STUDIES
-- 18. Display the details of the programmers knowing C.
SELECT * FROM Programmer
WHERE PROF1 = 'C' OR Prof2= 'C'
-- 19. How many programmers know either COBOL or Pascal?
SELECT COUNT(PNAME) AS Ct_Cobol_and_Pascal FROM Programmer
WHERE PROF1 = 'COBOL' OR Prof1= 'PASCAL' OR PROF2 = 'COBOL' OR PROF2= 'PASCAL'
-- 20. How many programmers don’t know Pascal and C?
SELECT COUNT(PNAME) AS Ct FROM Programmer
WHERE NOT PROF1= 'C' AND NOT PROF1= 'Pascal' AND NOT PROF2= 'C' AND NOT PROF2= 'Pascal'
-- 21. How old is the oldest male programmer?
SELECT MAX(DATEDIFF(YEAR, DOB, GETDATE())) AS Oldest_Male_Programmer
FROM PROGRAMMER
WHERE GENDER= 'M'
-- 22. What is the average age of female programmers?
SELECT AVG(DATEDIFF(YEAR, DOB, GETDATE())) AS AVG_Female_Age
FROM PROGRAMMER
WHERE GENDER = 'F'
-- 23. Calculate the experience in years for each programmer and display with their names in
descending order.
SELECT PNAME, DATEDIFF(YEAR, DOJ, GETDATE()) AS Experience
FROM PROGRAMMER
ORDER BY Experience DESC
-- 24. Who are the programmers who celebrate their birthdays during the current month?
SELECT PNAME
FROM PROGRAMMER
WHERE DOB LIKE '%8%'
-- TAKING CURRENT MONTH AS AUGUST FOR NOW
-- 25. How many female programmers are there?
SELECT COUNT(PNAME) Female_Ct FROM PROGRAMMER
WHERE GENDER = 'F'
-- 26. What are the languages studied by male programmers?
SELECT P.PNAME, GENDER, PROF1, PROF2
FROM PROGRAMMER AS P
FULL OUTER JOIN
STUDIES AS S
ON P.PNAME = S.PNAME
WHERE P.GENDER = 'M'
-- Considering PROF1 and PROF2 as the name of language known by respective programmers.
-- 27. What is the average salary?
SELECT AVG(SALARY) avg_salary FROM PROGRAMMER
-- 28. How many people draw a salary between 2000 to 4000?
SELECT COUNT(PNAME) AS ct
FROM PROGRAMMER
WHERE SALARY BETWEEN 2000 AND 4000
-- 29. Display the details of those who don’t know Clipper, COBOL or Pascal.
SELECT * FROM PROGRAMMER
WHERE PROF1 != 'Clipper' AND PROF1 != 'COBOL' AND PROF1 != 'Pascal' AND PROF2 != 'Clipper' AND
PROF2 != 'COBOL' AND PROF2 != 'Pascal'
-- 30. Display the cost of packages developed by each programmer
SELECT PNAME, SUM(DCOST) AS Developed_Package_Cost FROM SOFTWARE
GROUP BY PNAME
-- 31. Display the sales value of the packages developed by each programmer.
SELECT PNAME, SUM(SCOST) AS Sales_Package_Cost FROM SOFTWARE
GROUP BY PNAME
-- 32. Display the number of packages sold by each programmer.
SELECT PNAME, SUM(SOLD) AS Sold_Package FROM SOFTWARE
GROUP BY PNAME
-- 33. Display the sales cost of the packages developed by each programmer language wise.
SELECT PNAME, DEVELOPIN, SUM(SCOST) AS sales_cost_of_packages
FROM Software
GROUP BY PNAME, DEVELOPIN
-- 34. Display each language name with the average development cost, average selling cost and
average price per copy.
SELECT DEVELOPIN, AVG(SCOST), AVG(DCOST), IIF(SUM(SOLD) <> 0, SUM(SCOST)/SUM(SOLD), 0)
FROM Software
GROUP BY DEVELOPIN
SELECT DEVELOPIN, AVG(DCOST), AVG(SCOST) ,AVG(SCOST/SOLD)
FROM Software
GROUP BY DEVELOPIN
-- 35. Display each programmer’s name and the costliest and cheapest packages developed by him
or her.
SELECT PNAME, MAX(DCOST) AS COSTLIEST , MIN(DCOST) AS CHEAPEST
FROM Software
GROUP BY PNAME
-- 36. Display each institute’s name with the number of courses and the average cost per course.
SELECT INSTITUTE, COUNT(COURSE) Course_Ct, AVG(COURSE_FEE) AS AVG_Cost FROM Studies
GROUP BY INSTITUTE
-- 37. Display each institute’s name with the number of students.
SELECT INSTITUTE, COUNT(PNAME) AS number_of_students FROM Studies
GROUP BY INSTITUTE
-- 38. Display names of male and female programmers along with their gender.
SELECT PNAME, GENDER FROM PROGRAMMER
-- GROUP BY PNAME, GENDER
WHERE GENDER = 'M'
UNION
SELECT PNAME, GENDER FROM PROGRAMMER
WHERE GENDER = 'F'
-- 39. Display the name of programmers and their packages.
SELECT PNAME, DEVELOPIN AS Package FROM Software
-- 40. Display the number of packages in each language except C and C++.
SELECT COUNT(TITLE) AS No_of_Package FROM Software
WHERE DEVELOPIN != 'C' AND DEVELOPIN != 'CPP'
-- 41. Display the number of packages in each language for which development cost is less than 1000
(Dicuss)
SELECT DEVELOPIN, COUNT(Title) AS Package_Ct FROM Software
WHERE DCOST < 1000
GROUP BY DEVELOPIN
--42. Display the average difference between SCOST and DCOST for each package.
SELECT TITLE AS Package, AVG(DCOST - SCOST) AS Avg_Difference FROM Software
GROUP BY TITLE
SELECT TITLE, AVG(DCOST) - AVG(SCOST) FROM Software
GROUP BY TITLE
-- 43. Display the total SCOST, DCOST and the amount to be recovered for each programmer whose
cost has not yet been recovered.
SELECT PNAME, SUM(SCOST), SUM(DCOST), SUM(SCOST*SOLD) - SUM(DCOST) AS
Amount_to_be_Recovered
FROM SOFTWARE
GROUP BY PNAME
HAVING SUM(SCOST*SOLD) < SUM(DCOST)
-- 44. Display the highest, lowest and average salaries for those earning more than 2000.
SELECT MAX(SALARY) AS Highest_Salary, MIN(SALARY) AS Lowest_Salary, AVG(SALARY) AS
Average_Salary FROM Programmer
WHERE SALARY > 2000;
-- 45. Who is the highest paid C programmer?
SELECT PNAME AS Highest_Paid_Programmer_in_C, Salary FROM Programmer
WHERE SALARY= (SELECT MAX(SALARY) AS Salary
FROM Programmer
WHERE PROF1 = 'C' OR PROF2 = 'C')
-- 46. Who is the highest paid female COBOL programmer?
SELECT PNAME AS Highest_Paid_Programmer, GENDER, Salary FROM Programmer
WHERE SALARY = (SELECT MAX(SALARY) AS Salary FROM Programmer
WHERE GENDER = 'F' AND (PROF1 = 'COBOL' OR PROF2 = 'COBOL'))
AND GENDER = 'F'
-- 47. Display the names of the highest paid programmers for each language.
select pname from programmer where SALARY in
(select max(SALARY) from Programmer)
select * from Programmer
select p.PNAME,prof_max_sal.prof,prof_max_sal.max_sal from
(select prof,max(sal) as max_sal from
(select PROF1 as prof,max(SALARY) as sal from Programmer group by PROF1
union all
select PROF2, max(SALARY) as sal from Programmer group by PROF2) as prof_sal
group by prof) as prof_max_sal inner join Programmer p
on prof_max_sal.prof = p.PROF1 or prof_max_sal.prof = p.PROF2
where prof_max_sal.max_sal = p.SALARY
with prof_sal as
( select PROF1 as prof,max(SALARY) as sal from Programmer group by PROF1
union all
select PROF2, max(SALARY) as sal from Programmer group by PROF2
),
prof_max_sal as
( select prof,max(sal) as max_sal from prof_sal group by prof
)
select p.PNAME,prof_max_sal.prof,prof_max_sal.max_sal from prof_max_sal inner join Programmer
p
on prof_max_sal.prof = p.PROF1 or prof_max_sal.prof = p.PROF2
where prof_max_sal.max_sal = p.SALARY
WITH MAIN_CTE AS
(WITH CTE AS
(SELECT Prof1, SALARY From Programmer
UNION
SELECT Prof2, SALARY From Programmer)
SELECT PROF1, MAX(SALARY) AS Maximum_Salary_Each_Program FROM CTE
GROUP BY PROF1)
SELECT PNAME FROM PROGRAMMER AS P
INNER JOIN
MAIN_CTE AS M
ON P.Prof1 = M.Prof1 OR P.Prof2 = M.Prof1 AND P.Salary = M.Maximum_Salary_Each_Program
WITH CTE AS
SELECT Prof1, Max(SALARY)Salary From Programmer group by Prof1
UNION
SELECT Prof2, Max(SALARY)Salary From Programmer group by Prof2
SELECT PNAME, C.SALARY FROM PROGRAMMER AS P
INNER JOIN
CTE AS C
ON P.Prof1 = C.Prof1 OR P.Prof2 = C.Prof1 AND P.Salary = C.Salary
GROUP BY PNAME, PROF1, PROF2
SELECT * FROM PROGRAMMER
SELECT * FROM Software
-- 48. Who is the least experienced programmer
SELECT PNAME FROM Programmer
WHERE DATEDIFF(YEAR, DOJ, GETDATE()) = (SELECT MIN(DATEDIFF(YEAR, DOJ, GETDATE())) FROM
Programmer)
-- 49. Who is the most experienced male programmer knowing PASCAL?
SELECT PNAME, DOJ FROM Programmer
WHERE DOJ= (SELECT MIN(DOJ) FROM Programmer
WHERE PROF1 ='Pascal' OR PROF2 = 'Pascal')
-- 50. Which language is known by only one programmer?
SELECT PROF1 FROM PROGRAMMER
GROUP BY PROF1
HAVING PROF1 NOT IN
(SELECT PROF2 FROM PROGRAMMER)
AND COUNT(PROF1)=1
UNION
SELECT PROF2 FROM PROGRAMMER
GROUP BY PROF2
HAVING PROF2 NOT IN
(SELECT PROF1 FROM PROGRAMMER)
AND COUNT(PROF2)=1;
-- 51. Who is the above programmer referred in 50?
WITH NAME_OF_PROGRAMMER AS
( SELECT PROF1 FROM PROGRAMMER
GROUP BY PROF1
HAVING PROF1 NOT IN
(SELECT PROF2 FROM PROGRAMMER)
AND COUNT(PROF1)=1
UNION
SELECT PROF2 FROM PROGRAMMER
GROUP BY PROF2
HAVING PROF2 NOT IN
(SELECT PROF1 FROM PROGRAMMER)
AND COUNT(PROF2)=1)
SELECT PNAME FROM NAME_OF_PROGRAMMER AS NP
INNER JOIN
PROGRAMMER P
ON NP.PROF1 = P.PROF1 OR NP.PROF1 = P.PROF2
-- 52. Who is the youngest programmer knowing dBase?
SELECT PNAME FROM Programmer
WHERE DOB = (SELECT MAX(DOB) FROM Programmer
WHERE PROF1 = 'DBASE' OR PROF2 = 'DBASE')
-- 53. Which female programmer earning more than 3000 does not know C, C++, Oracle or dBase?
SELECT PNAME FROM Programmer
WHERE GENDER = 'F' AND SALARY >= 3000 AND
(PROF1 != 'C' AND PROF1 != 'CPP' AND PROF1 != 'Oracle' AND PROF1 != 'DBASE') AND
(PROF2 != 'C' AND PROF2 != 'CPP' AND PROF2 != 'Oracle' AND PROF2 != 'DBASE')
-- 54. Which institute has the most number of students?
WITH Max_Student AS
(SELECT institute, COUNT(PNAME) AS Ct FROM Studies
GROUP BY INSTITUTE)
SELECT S.INSTITUTE, MAX(Ct) CT FROM Max_Student AS MS
INNER JOIN
Studies AS S
ON MS.INSTITUTE = S.INSTITUTE
GROUP BY S.INSTITUTE, MS.Ct
HAVING Ct= (SELECT MAX(Ct) FROM Max_Student)
-- 55. What is the costliest course?
SELECT * FROM Studies
WHERE COURSE_FEE = (SELECT MAX(COURSE_FEE) FROM Studies)
-- 56. Which course has been done by the most number of students?
WITH Max_Course AS
(SELECT COURSE, COUNT(PNAME) AS Ct FROM Studies
GROUP BY COURSE)
SELECT MC.COURSE, MC.Ct FROM Max_Course AS MC
JOIN
Studies AS S
ON MC.Course = S.COURSE
GROUP BY MC.COURSE, MC.CT
HAVING Ct = (SELECT MAX(Ct) FROM Max_Course)
--57. Which institute conducts the costliest course?
SELECT INSTITUTE FROM Studies
WHERE COURSE_FEE = (SELECT MAX(COURSE_FEE) FROM Studies)
-- 58. Display the name of the institute and the course which has below average course fee.
SELECT INSTITUTE, COURSE FROM Studies
WHERE COURSE_FEE <= (SELECT AVG(COURSE_FEE) FROM Studies)
-- 59. Display the names of the courses whose fees are within 1000 (+ or -) of the average fee.
SELECT COURSE FROM Studies
WHERE COURSE_FEE =
(SELECT (AVG(COURSE_FEE) + 1000) AS Higher_Avg_FEE FROM Studies) BETWEEN (SELECT
(AVG(COURSE_FEE) - 1000) AS Lower_Avg_FEE FROM Studies)
WITH AVG_PRICE AS
(SELECT COURSE, (AVG(COURSE_FEE) + 1000) AS Higher_Avg_FEE, (AVG(COURSE_FEE) - 1000) AS
Lower_Avg_FEE FROM Studies
GROUP BY COURSE)
SELECT S.COURSE, S.COURSE_FEE FROM AVG_PRICE AS AP
FULL OUTER JOIN
Studies AS S
ON S.COURSE = AP.COURSE
HAVING COURSE_FEE BETWEEN (SELECT (AVG(COURSE_FEE) + 1000) AS Higher_Avg_FEE FROM
Studies) AND (SELECT (AVG(COURSE_FEE) - 1000) AS Lower_Avg_FEE FROM Studies)
-- 60. Which package has the highest development cost
SELECT TITLE AS Package from Software
WHERE DCOST = ( SELECT MAX(DCOST) FROM Software)
-- 61. Which course has below average number of students?
CREATE TABLE #AVGCNT (Course VARCHAR(20), Counts INT)
SELECT * FROM #AVGCNT
INSERT INTO #AVGCNT (Course, Counts) SELECT COURSE, COUNT(PNAME) FROM Studies GROUP BY
COURSE
SELECT Course, Counts FROM #AVGCNT WHERE Counts < = (SELECT AVG(Counts) FROM #AVGCNT)
SELECT COURSE, COUNT(*) AS num_students
FROM studies
GROUP BY COURSE
HAVING num_students < (SELECT AVG(num_students) FROM (SELECT COURSE, COUNT(*) AS
num_students FROM studies GROUP BY COURSE) AS course_counts);
WITH Count_Students AS
(SELECT COURSE, COUNT(PNAME) AS Ct FROM Studies
GROUP BY COURSE)
SELECT S.Course, AVG(Ct) AS Avg_Ct FROM Count_Students AS CS
FULL OUTER JOIN
Studies AS S
ON S.COURSE = CS.COURSE
GROUP BY S.COURSE
HAVING Ct < (SELECT AVG(Ct) FROM Count_Students
-- 62. Which package has the lowest selling cost?
SELECT TITLE AS Package FROM Software
WHERE SCOST = (SELECT MIN(SCOST) FROM Software)
-- 63. Who developed the package that has sold the least number of copies?
SELECT PNAME FROM Software
WHERE SOLD = (SELECT MIN(SOLD) FROM Software)
-- 64. Which language has used to develop the package, which has the highest sales amount?
SELECT DEVELOPIN FROM SOFTWARE
WHERE SCOST = (SELECT MAX(SCOST) FROM Software)
-- 65. How many copies of package that has the least difference between development and selling
cost were sold.
SELECT COUNT(TITLE) AS Package_Ct FROM Software
WHERE DCOST-SCOST = (SELECT MIN(DCOST - SCOST) FROM Software)
-- 66. Which is the costliest package developed in Pascal?
SELECT TITLE AS Package FROM Software
WHERE DCOST = (SELECT MAX(DCOST) FROM Software WHERE DEVELOPIN = 'PASCAL')
-- 67. Which language was used to develop the most number of packages?
CREATE TABLE #LANGCNT (Packages VARCHAR(20), Developin VARCHAR(20), Ct INT)
INSERT INTO #LANGCNT SELECT TITLE AS Packages, DEVELOPIN, COUNT(DEVELOPIN) AS Ct FROM
Software
GROUP BY TITLE, DEVELOPIN
SELECT * FROM #LANGCNT
SELECT DEVELOPIN, MAX(Ct) AS Ct FROM #LANGCNT
GROUP BY DEVELOPIN
ORDER BY Ct DESC
SELECT DEVELOPIN FROM SOFTWARE GROUP BY DEVELOPIN HAVING DEVELOPIN = (SELECT
MAX(DEVELOPIN) FROM SOFTWARE);
-----------------------------
-- SELECT TITLE AS Packages, DEVELOPIN, COUNT(DEVELOPIN) AS Ct FROM Software
-- GROUP BY TITLE, DEVELOPIN
-- HAVING Ct = (SELECT MAX(Ct) FROM Software)
SELECT DEVELOPIN AS Language, COUNT(*) AS NumberOfPackages
FROM Software
GROUP BY DEVELOPIN
ORDER BY NumberOfPackages DESC
LIMIT 1;
-----------------------
-- 68. Which programmer has developed the highest number of packages?
SELECT PNAME FROM SOFTWARE GROUP BY PNAME HAVING PNAME = (SELECT MAX(PNAME)
FROM SOFTWARE);
WITH Ct_Packages AS
(SELECT PNAME, COUNT(TITLE) AS Ct FROM Software
GROUP BY PNAME)
SELECT CP.PNAME, Ct FROM Software AS S
FULL OUTER JOIN
Ct_Packages AS CP
ON S.PNAME = CP.PNAME
GROUP BY CP.PNAME
-- 69. Who is the author of the costliest package?
SELECT PNAME FROM Software
WHERE DCOST = (SELECT MAX(DCOST) FROM SOFTWARE)
-- 70. Display the names of the packages which have sold less than the average number of copies.
SELECT TITLE AS Packages, SOLD FROM Software
WHERE SOLD < (SELECT AVG(SOLD) AS Avg_Sold FROM Software)
-- 71. Who are the authors of the packages which have recovered more than double the
development cost?
SELECT PNAME FROM Software
WHERE SOLD*SCOST > = 2*DCOST
-- 72. Display the programmer names and the cheapest packages developed by them in each
language.
SELECT PNAME, TITLE AS Packages FROM Software
WHERE DCOST in (SELECT MIN(DCOST) AS Min_Cost FROM Software
GROUP BY DEVELOPIN)
-- 73. Display the language used by each programmer to develop the highest selling and lowest
selling package.
SELECT PNAME, DEVELOPIN FROM Software
WHERE SOLD IN (SELECT MAX(SOLD) AS Maximum FROM Software GROUP BY PNAME)
UNION
SELECT PNAME, DEVELOPIN FROM Software
WHERE SOLD IN (SELECT MIN(SOLD) AS Maximum FROM Software GROUP BY PNAME)
-- 74. Who is the youngest male programmer born in 1965?
SELECT PNAME, DOB FROM programmer WHERE DATEDIFF(DD,DOB,GETDATE())=
(SELECT MIN(DATEDIFF(DD,DOB,GETDATE())) FROM Programmer WHERE year(DOB)=1965 AND
GENDER='M')
-- QUERY 75. Who is the oldest Female Programmer who joined in 1992?
SELECT PNAME,DOB,YEAR(DOJ) FROM Programmer WHERE DATEDIFF(DD,DOB,GETDATE())=
(SELECT MAX(DATEDIFF(DD,DOB,GETDATE())) FROM Programmer WHERE YEAR(DOJ) =1992);
--QUERY 76. In which year was the most number of Programmers born.
CREATE TABLE #TEMP (YOB INT, CNT INT)
INSERT INTO #TEMP
SELECT YEAR(DOB),COUNT(YEAR(DOB)) FROM programmer GROUP BY YEAR(DOB)
SELECT YOB, CNT FROM #TEMP WHERE CNT= (SELECT MAX(CNT) FROM #TEMP)
--QUERY 77. In which month did most number of programmers join?
CREATE TABLE #MOJ (MOJ INT, CNT INT)
INSERT INTO #MOJ
SELECT MONTH(DOJ),COUNT(MONTH(DOJ)) FROM programmer GROUP BY MONTH(DOJ)
SELECT MOJ, CNT FROM #MOJ WHERE CNT= (SELECT MAX(CNT) FROM #MOJ)
--QUERY 78. In which language are most of the programmer’s proficient.
CREATE TABLE #PCNT (PR VARCHAR(20), CNT INT)
CREATE TABLE #PsCNT (PRs VARCHAR(20), sCNT INT)
INSERT INTO #PCNT
SELECT PROF1, COUNT(PROF1) FROM programmer GROUP BY PROF1 UNION ALL
SELECT PROF2, COUNT(PROF2) FROM programmer GROUP BY PROF2
INSERT INTO #PsCNT
SELECT PR, SUM(CNT) FROM #PCNT GROUP BY PR
SELECT PRs, sCNT FROM #PsCNT WHERE sCNT =
(SELECT MAX(sCNT) FROM #PsCNT)
--QUERY 79. Who are the male programmers earning below the AVG salary of Female Programmers?
SELECT PNAME, SALARY FROM Programmer WHERE GENDER = 'M' AND SALARY <
(SELECT(AVG(SALARY))FROM Programmer WHERE GENDER = 'F')
--QUERY 80. Who are the Female Programmers earning more than the Highest Paid male?
SELECT PNAME, SALARY FROM Programmer WHERE GENDER='F' AND
SALARY > (SELECT MAX (SALARY) FROM Programmer WHERE GENDER ='M')
--QUERY 81. Which language has been stated as the proficiency by most of the Programmers?
CREATE TABLE #PCNT (PR VARCHAR(20), CNT INT)
CREATE TABLE #PsCNT (PRs VARCHAR(20), sCNT INT)
INSERT INTO #PCNT
SELECT PROF1, COUNT(PROF1) FROM programmer GROUP BY PROF1 UNION ALL
SELECT PROF2, COUNT(PROF2) FROM programmer GROUP BY PROF2
INSERT INTO #PsCNT
SELECT PR, SUM(CNT) FROM #PCNT GROUP BY PR
SELECT PRs, sCNT FROM #PsCNT WHERE sCNT =
(SELECT MAX(sCNT) FROM #PsCNT)
--QUERY 82. Display the details of those who are drawing the same salary.
SELECT A.PNAME, A.SALARY FROM Programmer A
INNER JOIN
PROGRAMMER B ON A.SALARY = B.SALARY AND A.PNAME != B.PNAME
ORDER BY A.SALARY
--QUERY 83. Display the details of the Software Developed by the Male Programmers Earning More
than 3000/-.
SELECT * FROM software WHERE PNAME IN
(SELECT PNAME FROM programmer WHERE SALARY > 3000 AND GENDER='M')
--QUERY 84. Display the details of the packages developed in Pascal by the Female Programmers.
SELECT * FROM Programmer P, Software S
WHERE P.PNAME=S.PNAME AND GENDER='F' AND DEVELOPIN= 'PASCAL'
--QUERY 85. Display the details of the Programmers who joined before 1990.
SELECT * FROM Programmer WHERE YEAR(DOJ) < 1990
--QUERY 86. Display the details of the Software Developed in C By female programmers of Pragathi.
SELECT SW.*,P.GENDER,ST.INSTITUTE FROM studies AS ST, software AS SW, programmer AS P
WHERE P.PNAME=SW.PNAME AND ST.PNAME=SW.PNAME AND
GENDER= 'F' AND INSTITUTE= 'PRAGATHI' AND DEVELOPIN='C';
--QUERY 87. Display the number of packages, No. of Copies Sold and sales value of each programmer
institute wise.
SELECT INSTITUTE, COUNT(SW.TITLE) AS PKGCNT, COUNT(SW.SOLD) AS SALESCNT,
SUM(SCOST*SOLD)
AS SALVAL
FROM software AS SW, studies AS ST
WHERE SW.PNAME=ST.PNAME
GROUP BY INSTITUTE
--QUERY 88. Display the details of the software developed in DBASE by Male Programmers, who
belong to the institute
-- in which most number of Programmers studied.
CREATE TABLE #INST (INS VARCHAR(20), CNT INT)
INSERT INTO #INST
SELECT INSTITUTE, COUNT(PNAME) FROM studies GROUP BY INSTITUTE
SELECT DISTINCT SW.* FROM software AS SW, programmer AS PG, studies AS ST, #INST
WHERE DEVELOPIN= 'DBASE' AND GENDER= 'M' AND SW.PNAME = PG.PNAME
AND INSTITUTE = INS AND CNT= (SELECT MAX(CNT) FROM #INST)
--QUERY 89. Display the details of the software Developed by the male programmers Born before
1965 and female
-- programmers born after 1975.
SELECT S.*, YEAR(DOB),GENDER FROM Programmer p,Software s
WHERE s.PNAME=p.PNAME AND
((GENDER ='M' AND YEAR(DOB)< 1965) OR (GENDER ='F' AND YEAR(DOB)> 1975))
--QUERY 90. Display the details of the software that has developed in the language which is neither
the
-- first nor the second proficiency of the programmers.
SELECT SW.*,PG.PROF1,PG.PROF2 from Software AS SW, programmer AS PG
WHERE PG.PROF1 != SW.DEVELOPIN AND PG.PROF2 != SW.DEVELOPIN AND PG.PNAME =
SW.PNAME
--QUERY 91. Display the details of the software developed by the male students of Sabhari.
SELECT s.*, INSTITUTE FROM Programmer p,Software s,Studies st
WHERE p.PNAME=s.PNAME and s.PNAME=st.PNAME AND GENDER='M' and INSTITUTE='SABHARI'
--QUERY 92. Display the names of the programmers who have not developed any packages.
SELECT PNAME FROM Programmer
WHERE PNAME NOT IN (SELECT PNAME from Software);
--QUERY 93. What is the total cost of the Software developed by the programmers of Apple?
SELECT SUM(SCOST) FROM Software s,Studies st
WHERE s.PNAME=st.PNAME and INSTITUTE='APPLE'
--QUERY 94. Who are the programmers who joined on the same day?
SELECT A.PNAME, A.DOJ FROM programmer AS A
INNER JOIN
programmer AS B
ON A.DOJ=B.DOJ WHERE A.PNAME <> B.PNAME
ORDER BY A.DOJ
--QUERY 95. Who are the programmers who have the same Prof2?
SELECT DISTINCT A.PNAME, A.PROF2 FROM programmer AS A
INNER JOIN
programmer AS B
ON A.PROF2=B.PROF2 WHERE A.PNAME != B.PNAME
ORDER BY A.PROF2
--QUERY 96. Display the total sales value of the software, institute wise.
SELECT Studies.INSTITUTE,sum(Software.SOLD*Software.SCOST) from Software,
Studies WHERE Studies.PNAME=Software.PNAME group by Studies.INSTITUTE
--QUERY 97. In which institute did the person who developed the costliest package study?
SELECT INSTITUTE, SW.PNAME, DCOST FROM studies AS ST, software AS SW
WHERE ST.PNAME = SW.PNAME AND DCOST= (SELECT MAX(DCOST) FROM software)
--QUERY 98. Which language listed in prof1, prof2 has not been used to develop any package.
SELECT DISTINCT PROF1 FROM programmer WHERE PROF1 NOT IN (SELECT DEVELOPIN FROM
software)
UNION
SELECT DISTINCT PROF2 FROM programmer WHERE PROF2 NOT IN (SELECT DEVELOPIN FROM
software)
--QUERY 99. How much does the person who developed the highest selling package earn and what
-- course did HE/SHE undergo.
SELECT SW.PNAME, COURSE, SALARY FROM studies AS ST,software AS SW, programmer AS PG
WHERE ST.PNAME = SW.PNAME AND ST.PNAME=PG.PNAME
AND SOLD= (SELECT MAX(SOLD) FROM software)
--QUERY 100. What is the AVG salary for those whose software sales is more than 50,000/-.
select avg(Salary) from Programmer p,Software s
where p .PNAME=s.PNAME and SOLD*SCOST >50000
