-- 1. Find out the selling average cost for Packages developed in PASCAL.

SELECT AVG(SCOST) FROM SOFTWARE WHERE DEVELOPIN = 'PASCAL';

-- 2. Display the names and ages of all programmers.

SELECT PNAME, TRUNC(MONTHS_BETWEEN(SYSDATE, DOB)/12) 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 births of all programmers born in January.
SELECT PNAME, DOB FROM PROGRAMMER WHERE TO_CHAR(DOB, 'MON') = 'JAN';

-- 5. What is the highest number of copies sold by a package.

SELECT MAX(SOLD) FROM SOFTWARE;

-- 6. Display the lowest course fee.

SELECT MIN(COURSEFEE) FROM STUDIES;

-- 7. How many programmers done the PGDCA course.

SELECT COUNT(*) FROM STUDIES WHERE COURSE = 'PGDCA';

-- 8. How much revenue has been earned thru sales of packages developed in C.
SELECT SUM(SCOST * SOLD) 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(*) FROM STUDIES WHERE INSTITUTE = 'SABHARI';

-- 11. Display details of packages whose sales crossed the 2000 mark.
-- Assuming "sales" refers to total sales value (SCOST * SOLD).
SELECT * FROM SOFTWARE WHERE (SCOST * SOLD) > 2000;

-- 12. Find out the number of copies which should be sold in order to recover the
development cost of each package.
SELECT TITLE, CEIL(DCOST / SCOST) AS BREAK_EVEN_COPIES FROM SOFTWARE;

-- 13. Display the details of packages for which developments cost have been
recovered.
SELECT * FROM SOFTWARE WHERE (SCOST * SOLD) >= DCOST;

-- 14. What is the cost of the costliest software developed in BASIC.

SELECT MAX(DCOST) FROM SOFTWARE WHERE DEVELOPIN = 'BASIC';

-- 15. How many packages developed in DBASE.

SELECT COUNT(*) FROM SOFTWARE WHERE DEVELOPIN = 'DBASE';

-- 16. How many programmers studied in Pragathi.

SELECT COUNT(*) FROM STUDIES WHERE INSTITUTE = 'PRAGATHI';

-- 17. How many programmers paid 5000 to 10000 for their studies.
SELECT COUNT(*) FROM STUDIES WHERE COURSEFEE BETWEEN 5000 AND 10000;

-- 18. What is average course fee.

SELECT AVG(COURSEFEE) FROM STUDIES;

-- 19. Display the details of the programmers knowing C.

SELECT * FROM PROGRAMMER WHERE PROF1 = 'C' OR PROF2 = 'C';
-- 20. How many programmers know either COBOL or PASCAL.
SELECT COUNT(*) FROM PROGRAMMER
WHERE PROF1 IN ('COBOL', 'PASCAL') OR PROF2 IN ('COBOL', 'PASCAL');

-- 21. How many programmers don’t know PASCAL and C.

SELECT COUNT(*) FROM PROGRAMMER
WHERE PROF1 NOT IN ('PASCAL', 'C') AND PROF2 NOT IN ('PASCAL', 'C');

-- 22. How old is the oldest male programmers.

SELECT MAX(TRUNC(MONTHS_BETWEEN(SYSDATE, DOB)/12)) FROM PROGRAMMER WHERE SEX = 'M';

-- 23. Calculate the experience in years for each programmers and display along
with the names in descending order.
SELECT PNAME, TRUNC(MONTHS_BETWEEN(SYSDATE, DOJ)/12) AS EXPERIENCE
FROM PROGRAMMER ORDER BY PNAME DESC;

-- 24. Who are the programmers who celebrate their birthdays during the current
month.
SELECT PNAME FROM PROGRAMMER WHERE TO_CHAR(DOB, 'MM') = TO_CHAR(SYSDATE, 'MM');

-- 25. How many female programmers are there.

SELECT COUNT(*) FROM PROGRAMMER WHERE SEX = 'F';

-- 26. What are the languages known by male programmers.

SELECT DISTINCT PROF1 FROM PROGRAMMER WHERE SEX = 'M'
UNION
SELECT DISTINCT PROF2 FROM PROGRAMMER WHERE SEX = 'M';

-- 27. What is the average salary.

SELECT AVG(SALARY) FROM PROGRAMMER;

-- 28. How many people draw salary 2000 to 4000.

SELECT COUNT(*) 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 NOT IN ('CLIPPER', 'COBOL', 'PASCAL')
AND PROF2 NOT IN ('CLIPPER', 'COBOL', 'PASCAL');

-- 30. Display the cost of package developed by each programmer.

SELECT PNAME, SUM(DCOST) AS TOTAL_DEV_COST FROM SOFTWARE GROUP BY PNAME;

-- 31. Display the sales values of the packages developed by the each programmer.
SELECT PNAME, SUM(SCOST * SOLD) AS SALES_VALUE FROM SOFTWARE GROUP BY PNAME;

-- 32. Display the number of packages sold by each programmer.

SELECT PNAME, COUNT(*) AS PACKAGES_COUNT FROM SOFTWARE GROUP BY PNAME;

-- 33. Display the sales cost of the packages developed by each programmer.
-- Assuming "sales cost" refers to selling price (SCOST) per copy total.
SELECT PNAME, SUM(SCOST) FROM SOFTWARE GROUP BY PNAME;

-- 34. Display the sales cost of the packages developed by each programmer language
wise.
SELECT PNAME, DEVELOPIN, SUM(SCOST) FROM SOFTWARE GROUP BY PNAME, DEVELOPIN;

-- 35. Display each language name with Average development cost, average selling
cost and average price per copy.
-- SCOST is the price per copy.
SELECT DEVELOPIN, AVG(DCOST), AVG(SCOST) FROM SOFTWARE GROUP BY DEVELOPIN;

-- 36. Display each programmers name, costliest and cheapest packages developed by
him or her.
SELECT PNAME, MAX(DCOST) AS COSTLIEST, MIN(DCOST) AS CHEAPEST
FROM SOFTWARE GROUP BY PNAME;

-- 37. Display each institute name with number of courses, average cost per course.
SELECT INSTITUTE, COUNT(DISTINCT COURSE), AVG(COURSEFEE)
FROM STUDIES GROUP BY INSTITUTE;

-- 38. Display each institute name with number of students.

SELECT INSTITUTE, COUNT(*) FROM STUDIES GROUP BY INSTITUTE;

-- 39. Display names of male and female programmers name and sex also.
SELECT PNAME, SEX FROM PROGRAMMER ORDER BY SEX;

-- 40. Display the name of programmers and their packages.

SELECT PNAME, TITLE FROM SOFTWARE;

-- 41. Display the number of packages in each languages except C and C++.
-- Note: 'CPP' is usually used for C++ in legacy tables, checking raw data provided
'CPP'.
SELECT DEVELOPIN, COUNT(*) FROM SOFTWARE
WHERE DEVELOPIN NOT IN ('C', 'CPP') GROUP BY DEVELOPIN;

-- 42. Display the number of packages in each language for which development cost
is less than 1000.
SELECT DEVELOPIN, COUNT(*) FROM SOFTWARE WHERE DCOST < 1000 GROUP BY DEVELOPIN;

-- 43. Display the average difference between SCOST and DCOST for each package.
-- Since Title is unique per package, average is just the difference.
SELECT TITLE, AVG(DCOST - SCOST) FROM SOFTWARE GROUP BY TITLE;

-- 44. Display the total SCOST, DCOST and amount to be recovered for each
programmer for those whose DCOST has not yet been recovered.
SELECT PNAME, SUM(SCOST), SUM(DCOST), SUM(DCOST - (SCOST * SOLD)) AS
AMOUNT_TO_RECOVER
FROM SOFTWARE
WHERE DCOST > (SCOST * SOLD)
GROUP BY PNAME;

-- 45. Display the highest, lowest and average salaries for those earning more than
2000.
SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY)
FROM PROGRAMMER WHERE SALARY > 2000;

-- 46. Who is the highest paid in C programmers.

SELECT PNAME FROM PROGRAMMER
WHERE SALARY = (SELECT MAX(SALARY) FROM PROGRAMMER WHERE PROF1='C' OR PROF2='C')
AND (PROF1='C' OR PROF2='C');

-- 47. Who is the highest paid female COBOL programmer.

SELECT PNAME FROM PROGRAMMER
WHERE SEX = 'F' AND (PROF1='COBOL' OR PROF2='COBOL')
AND SALARY = (SELECT MAX(SALARY) FROM PROGRAMMER WHERE SEX='F' AND (PROF1='COBOL'
OR PROF2='COBOL'));
-- 48. Display the names of the highest paid programmer for each language.
SELECT [Link], P.PROF1
FROM PROGRAMMER P
WHERE [Link] = (SELECT MAX(SALARY) FROM PROGRAMMER WHERE PROF1 = P.PROF1 OR PROF2
= P.PROF1)
UNION
SELECT [Link], P.PROF2
FROM PROGRAMMER P
WHERE [Link] = (SELECT MAX(SALARY) FROM PROGRAMMER WHERE PROF1 = P.PROF2 OR PROF2
= P.PROF2);

-- 49. Who is the least experienced programmer.

-- Least experienced = Date of Joining (DOJ) is Max (most recent).
SELECT PNAME FROM PROGRAMMER WHERE DOJ = (SELECT MAX(DOJ) FROM PROGRAMMER);

-- 50. Who is the most experienced male programmer knowing PASCAL.

SELECT PNAME FROM PROGRAMMER
WHERE SEX = 'M' AND (PROF1='PASCAL' OR PROF2='PASCAL')
AND DOJ = (SELECT MIN(DOJ) FROM PROGRAMMER WHERE SEX='M' AND (PROF1='PASCAL' OR
PROF2='PASCAL'));

-- 51. Which language is known by only one programmer.

SELECT LANG FROM
(SELECT PROF1 AS LANG FROM PROGRAMMER UNION ALL SELECT PROF2 AS LANG FROM
PROGRAMMER)
GROUP BY LANG HAVING COUNT(*) = 1;

-- 52. Who is the above programmer referred in 51.

SELECT PNAME FROM PROGRAMMER
WHERE PROF1 IN (SELECT LANG FROM (SELECT PROF1 AS LANG FROM PROGRAMMER UNION ALL
SELECT PROF2 FROM PROGRAMMER) GROUP BY LANG HAVING COUNT(*) = 1)
OR PROF2 IN (SELECT LANG FROM (SELECT PROF1 AS LANG FROM PROGRAMMER UNION ALL
SELECT PROF2 FROM PROGRAMMER) GROUP BY LANG HAVING COUNT(*) = 1);

-- 53. Who is the youngest programmer knowing DBASE.

SELECT PNAME FROM PROGRAMMER
WHERE (PROF1='DBASE' OR PROF2='DBASE')
AND DOB = (SELECT MAX(DOB) FROM PROGRAMMER WHERE PROF1='DBASE' OR PROF2='DBASE');

-- 54. Which female programmer earning more than 3000 does not know C, C++, ORACLE
or DBASE.
SELECT PNAME FROM PROGRAMMER
WHERE SEX = 'F' AND SALARY > 3000
AND PROF1 NOT IN ('C','CPP','ORACLE','DBASE')
AND PROF2 NOT IN ('C','CPP','ORACLE','DBASE');

-- 55. Which institute has most number of students.

SELECT INSTITUTE FROM STUDIES
GROUP BY INSTITUTE
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM STUDIES GROUP BY INSTITUTE);

-- 56. What is the costliest course.

SELECT COURSE FROM STUDIES
WHERE COURSEFEE = (SELECT MAX(COURSEFEE) FROM STUDIES);

-- 57. Which course has been done by the most of the students.
SELECT COURSE FROM STUDIES
GROUP BY COURSE
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM STUDIES GROUP BY COURSE);
-- 58. Which course has been done by the most of the student.
-- (Duplicate of 57)
SELECT COURSE FROM STUDIES
GROUP BY COURSE
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM STUDIES GROUP BY COURSE);

-- 59. Which institute conducts costliest course.

SELECT INSTITUTE FROM STUDIES
WHERE COURSEFEE = (SELECT MAX(COURSEFEE) FROM STUDIES);

-- 60. Display the name of the institute and course which has below average course
fee.
SELECT INSTITUTE, COURSE FROM STUDIES
WHERE COURSEFEE < (SELECT AVG(COURSEFEE) FROM STUDIES);

-- 61. Display the names of the courses whose fees are within 1000 (+or-) of the
average fee.
SELECT COURSE FROM STUDIES
WHERE COURSEFEE BETWEEN (SELECT AVG(COURSEFEE)-1000 FROM STUDIES)
AND (SELECT AVG(COURSEFEE)+1000 FROM STUDIES);

-- 62. Which package has the highest development cost.

SELECT TITLE FROM SOFTWARE WHERE DCOST = (SELECT MAX(DCOST) FROM SOFTWARE);

-- 63. Which course has below average number of students.

SELECT COURSE FROM STUDIES
GROUP BY COURSE
HAVING COUNT(*) < (SELECT AVG(COUNT(*)) FROM STUDIES GROUP BY COURSE);

-- 64. Which package has the lowest selling cost.

SELECT TITLE FROM SOFTWARE WHERE SCOST = (SELECT MIN(SCOST) FROM SOFTWARE);

-- 65. Who developed the package that has sold the least number of copies.
SELECT PNAME FROM SOFTWARE WHERE SOLD = (SELECT MIN(SOLD) FROM SOFTWARE);

-- 66. Which language has used to develop the package which has the highest sales
amount.
SELECT DEVELOPIN FROM SOFTWARE
WHERE (SCOST * SOLD) = (SELECT MAX(SCOST * SOLD) FROM SOFTWARE);

-- 67. How many copies of package that has the least difference between development
and selling cost were sold.
SELECT SOLD FROM SOFTWARE
WHERE (DCOST - SCOST) = (SELECT MIN(DCOST - SCOST) FROM SOFTWARE);

-- 68. Which is the costliest package developed in PASCAL.

SELECT TITLE FROM SOFTWARE
WHERE DEVELOPIN = 'PASCAL' AND DCOST = (SELECT MAX(DCOST) FROM SOFTWARE WHERE
DEVELOPIN = 'PASCAL');

-- 69. Which language was used to develop the most number of packages.
SELECT DEVELOPIN FROM SOFTWARE
GROUP BY DEVELOPIN
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM SOFTWARE GROUP BY DEVELOPIN);

-- 70. Which programmer has developed the highest number of packages.

SELECT PNAME FROM SOFTWARE
GROUP BY PNAME
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM SOFTWARE GROUP BY PNAME);

-- 71. Display the names of the package which have sold less than the average
number of copies.
SELECT TITLE FROM SOFTWARE WHERE SOLD < (SELECT AVG(SOLD) FROM SOFTWARE);

-- 72. Who are the authors of the packages which have recovered more than double
the development cost.
SELECT PNAME FROM SOFTWARE WHERE (SCOST * SOLD) > (2 * DCOST);

-- 73. Display the programmer names and the cheapest packages developed by them in
each language.
SELECT PNAME, DEVELOPIN, TITLE, DCOST FROM SOFTWARE S1
WHERE DCOST = (SELECT MIN(DCOST) FROM SOFTWARE S2 WHERE [Link] = [Link] AND
[Link] = [Link]);

-- 74. Display the language used by each programmer to develop the highest selling
and lowest selling package.
SELECT PNAME, DEVELOPIN, 'Highest Selling' AS TYPE FROM SOFTWARE S1
WHERE SOLD = (SELECT MAX(SOLD) FROM SOFTWARE S2 WHERE [Link] = [Link])
UNION
SELECT PNAME, DEVELOPIN, 'Lowest Selling' AS TYPE FROM SOFTWARE S3
WHERE SOLD = (SELECT MIN(SOLD) FROM SOFTWARE S4 WHERE [Link] = [Link]);

-- 75. Who is the youngest male programmer born in 1965.

SELECT PNAME FROM PROGRAMMER
WHERE SEX = 'M' AND TO_CHAR(DOB, 'YYYY') = '1965'
AND DOB = (SELECT MAX(DOB) FROM PROGRAMMER WHERE SEX = 'M' AND TO_CHAR(DOB, 'YYYY')
= '1965');

-- 76. Who is the oldest female programmer who joined in 1992.

SELECT PNAME FROM PROGRAMMER
WHERE SEX = 'F' AND TO_CHAR(DOJ, 'YYYY') = '1992'
AND DOB = (SELECT MIN(DOB) FROM PROGRAMMER WHERE SEX = 'F' AND TO_CHAR(DOJ, 'YYYY')
= '1992');

-- 77. In which year were the most number of programmers born.

SELECT TO_CHAR(DOB, 'YYYY') FROM PROGRAMMER
GROUP BY TO_CHAR(DOB, 'YYYY')
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM PROGRAMMER GROUP BY TO_CHAR(DOB,
'YYYY'));

-- 78. In which month did most number of programmers join.

SELECT TO_CHAR(DOJ, 'MON') FROM PROGRAMMER
GROUP BY TO_CHAR(DOJ, 'MON')
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM PROGRAMMER GROUP BY TO_CHAR(DOJ,
'MON'));

-- 79. In which language are most of the programmers proficient.

-- This requires combining PROF1 and PROF2.
SELECT LANG FROM
(SELECT PROF1 AS LANG FROM PROGRAMMER UNION ALL SELECT PROF2 FROM PROGRAMMER)
GROUP BY LANG HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM
(SELECT PROF1 AS LANG FROM PROGRAMMER UNION ALL SELECT PROF2 FROM PROGRAMMER) GROUP
BY LANG);

-- 80. Who are the male programmers earning below the average salary of female
programmers.
SELECT PNAME FROM PROGRAMMER
WHERE SEX = 'M' AND SALARY < (SELECT AVG(SALARY) FROM PROGRAMMER WHERE SEX = 'F');

-- 81. Who are the female programmers earning more than the highest paid male
programmer.
SELECT PNAME FROM PROGRAMMER
WHERE SEX = 'F' AND SALARY > (SELECT MAX(SALARY) FROM PROGRAMMER WHERE SEX = 'M');

-- 82. Which language has been stated as the PROF by most of the programmers.
-- (Similar to 79)
SELECT LANG FROM
(SELECT PROF1 AS LANG FROM PROGRAMMER UNION ALL SELECT PROF2 FROM PROGRAMMER)
GROUP BY LANG HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM
(SELECT PROF1 AS LANG FROM PROGRAMMER UNION ALL SELECT PROF2 FROM PROGRAMMER) GROUP
BY LANG);

-- 83. Display the details of those who are drawing the same salary.
SELECT * FROM PROGRAMMER P1
WHERE EXISTS (SELECT 1 FROM PROGRAMMER P2 WHERE [Link] = [Link] AND [Link]
<> [Link]);

-- 84. 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 SEX = 'M' AND SALARY > 3000);

-- 85. Display the details of the packages developed in PASCAL by the female
programmers.
SELECT * FROM SOFTWARE
WHERE DEVELOPIN = 'PASCAL' AND PNAME IN (SELECT PNAME FROM PROGRAMMER WHERE SEX =
'F');

-- 86. Display the details of the programmers who joined before 1990.
SELECT * FROM PROGRAMMER WHERE TO_CHAR(DOJ, 'YYYY') < '1990';

-- 87. Display the details of the software developed in C by female programmers of

Pragathi.
SELECT * FROM SOFTWARE
WHERE DEVELOPIN = 'C'
AND PNAME IN (SELECT [Link] FROM STUDIES S, PROGRAMMER P
WHERE [Link] = [Link] AND [Link] = 'PRAGATHI' AND [Link] =
'F');

-- 88. Display the number of packages, number of copies sold and sales value of
each programmer institute wise.
SELECT [Link], [Link], COUNT([Link]), SUM([Link]), SUM([Link] * [Link])
FROM SOFTWARE S, STUDIES ST
WHERE [Link] = [Link]
GROUP BY [Link], [Link];

-- 89. Display the details of the software developed in DBASE by male programmers
who belong to the institute in which most number of programmers studied.
SELECT * FROM SOFTWARE
WHERE DEVELOPIN = 'DBASE'
AND PNAME IN (SELECT PNAME FROM PROGRAMMER WHERE SEX='M')
AND PNAME IN (SELECT PNAME FROM STUDIES WHERE INSTITUTE =
(SELECT INSTITUTE FROM STUDIES GROUP BY INSTITUTE HAVING COUNT(*) = (SELECT
MAX(COUNT(*)) FROM STUDIES GROUP BY INSTITUTE)));

-- 90. Display the details of the software developed by the male programmers born
before 1965 and female programmers born after 1975.
SELECT * FROM SOFTWARE
WHERE PNAME IN
(SELECT PNAME FROM PROGRAMMER WHERE (SEX='M' AND TO_CHAR(DOB,'YYYY') < '1965')
OR (SEX='F' AND TO_CHAR(DOB,'YYYY') > '1975'));

-- 91. Display the details of the software that has developed in the language which
is neither the first nor the second proficiency of the programmer.
SELECT S.* FROM SOFTWARE S, PROGRAMMER P
WHERE [Link] = [Link] AND [Link] <> P.PROF1 AND [Link] <> P.PROF2;

-- 92. Display the details of the software developed by the male students of
Sabhari.
SELECT * FROM SOFTWARE
WHERE PNAME IN (SELECT [Link] FROM PROGRAMMER P, STUDIES S WHERE [Link] = [Link]
AND [Link]='M' AND [Link]='SABHARI');

-- 93. Display the names of the programmers who have not developed any packages.
SELECT PNAME FROM PROGRAMMER MINUS SELECT PNAME FROM SOFTWARE;

-- 94. What is the total cost of the software developed by the programmers of
Apple.
-- Assuming "Total Cost" is total DCOST.
SELECT SUM(DCOST) FROM SOFTWARE WHERE PNAME IN (SELECT PNAME FROM STUDIES WHERE
INSTITUTE = 'APPLE');

-- 95. Display the names of the programmers who have not developed any packages.
-- (Duplicate of 93)
SELECT PNAME FROM PROGRAMMER WHERE PNAME NOT IN (SELECT PNAME FROM SOFTWARE);

-- 96. Who are the programmers who have the same PROF2.
SELECT PNAME FROM PROGRAMMER P1
WHERE EXISTS (SELECT 1 FROM PROGRAMMER P2 WHERE P1.PROF2 = P2.PROF2 AND [Link] <>
[Link]);

-- 97. Display the total sales value of the software institute wise.
SELECT [Link], SUM([Link] * [Link])
FROM SOFTWARE S, STUDIES ST
WHERE [Link] = [Link]
GROUP BY [Link];

-- 98. In which institute does the person who developed the costliest package
studied.
SELECT INSTITUTE FROM STUDIES
WHERE PNAME = (SELECT PNAME FROM SOFTWARE WHERE DCOST = (SELECT MAX(DCOST) FROM
SOFTWARE));

-- 99. Which language listed in PROF1 and PROF2 has not been used to develop any
package.
SELECT PROF FROM
(SELECT DISTINCT PROF1 AS PROF FROM PROGRAMMER UNION SELECT DISTINCT PROF2 FROM
PROGRAMMER)
MINUS
SELECT DISTINCT DEVELOPIN FROM SOFTWARE;

-- 100. How much does the person who developed the highest selling package earn and
what course did he / she undergo.
SELECT [Link], [Link]
FROM PROGRAMMER P, STUDIES S, SOFTWARE SW
WHERE [Link] = [Link] AND [Link] = [Link]
AND [Link] = (SELECT MAX(SOLD) FROM SOFTWARE);

-- 101. How many months will it take for each programmer to recover the cost of the
course underwent.
-- Assuming recovery is via Salary. (Fee / Salary).
SELECT [Link], CEIL([Link] / [Link]) AS MONTHS_TO_RECOVER
FROM PROGRAMMER P, STUDIES S
WHERE [Link] = [Link];

-- 102. Which is the costliest package by a person with under 3 years experience.
SELECT TITLE FROM SOFTWARE
WHERE PNAME IN (SELECT PNAME FROM PROGRAMMER WHERE (SYSDATE - DOJ)/365 < 3)
ORDER BY DCOST DESC FETCH FIRST 1 ROWS ONLY;
-- Note: 'FETCH FIRST' is Oracle 12c+. For older versions use subquery with
MAX(DCOST).

-- 103. What is the average salary for those whose software sales is more than
50,000.
SELECT AVG([Link])
FROM PROGRAMMER P
WHERE [Link] IN (SELECT PNAME FROM SOFTWARE GROUP BY PNAME HAVING SUM(SCOST *
SOLD) > 50000);

-- 104. How many packages were developed by students who studied in institute that
charge the lowest course fee.
SELECT COUNT(*) FROM SOFTWARE
WHERE PNAME IN (SELECT PNAME FROM STUDIES WHERE COURSEFEE = (SELECT MIN(COURSEFEE)
FROM STUDIES));

-- 105. How many packages were developed by the person who developed the cheapest
package, where did he / she studied.
SELECT COUNT([Link]), [Link]
FROM SOFTWARE SW, STUDIES ST
WHERE [Link] = [Link]
AND [Link] = (SELECT PNAME FROM SOFTWARE WHERE DCOST = (SELECT MIN(DCOST) FROM
SOFTWARE))
GROUP BY [Link];

-- 106. How many packages were developed by the female programmers earning more
than the highest paid male programmer.
SELECT COUNT(*) FROM SOFTWARE
WHERE PNAME IN (SELECT PNAME FROM PROGRAMMER WHERE SEX = 'F' AND SALARY > (SELECT
MAX(SALARY) FROM PROGRAMMER WHERE SEX = 'M'));

-- 107. How many packages are developed by the most experienced programmers from
BDPS.
SELECT COUNT(*) FROM SOFTWARE
WHERE PNAME IN
(SELECT [Link] FROM PROGRAMMER P, STUDIES S
WHERE [Link] = [Link] AND [Link] = 'BDPS'
AND [Link] = (SELECT MIN(DOJ) FROM PROGRAMMER P2, STUDIES S2 WHERE [Link] =
[Link] AND [Link] = 'BDPS'));

-- 108. List the programmers from the software table and the institutes they
studied including those who did not develop and package.
-- Requires Outer Join between Programmer and Software, then join to Studies.
SELECT [Link], [Link], [Link]
FROM PROGRAMMER P
LEFT JOIN SOFTWARE SW ON [Link] = [Link]
JOIN STUDIES S ON [Link] = [Link];

-- 109. List each PROF with the number of programmers having that PROF and the
number of the packages in that PROF.
-- This requires correlating Proficiency count with Package count where Language
matches Proficiency.
SELECT [Link], T1.PROG_COUNT, T2.PKG_COUNT
FROM
(SELECT PROF, COUNT(*) AS PROG_COUNT FROM
(SELECT PROF1 AS PROF FROM PROGRAMMER UNION ALL SELECT PROF2 FROM PROGRAMMER)
GROUP BY PROF) T1,
(SELECT DEVELOPIN, COUNT(*) AS PKG_COUNT FROM SOFTWARE GROUP BY DEVELOPIN) T2
WHERE [Link] = [Link] (+);

-- 110. List the programmer names from the programmer table and the no of packages
each has developed.
SELECT [Link], COUNT([Link])
FROM PROGRAMMER P
LEFT JOIN SOFTWARE S ON [Link] = [Link]
GROUP BY [Link];