SQL Queries

I. SCHEMAS

Table 1 : STUDIES

PNAME (VARCHAR), SPLACE (VARCHAR), COURSE (VARCHAR), CCOST (NUMBER)

Table 2 : SOFTWARE

PNAME (VARCHAR), TITLE (VARCHAR), DEVIN (VARCHAR), SCOST (NUMBER), DCOST (NUMBER), SOLD (NUMBER)

Table 3 : PROGRAMMER

PNAME (VARCHAR), DOB (DATE), DOJ (DATE), SEX (CHAR), PROF1 (VARCHAR), PROF2 (VARCHAR), SAL (NUMBER)

LEGEND :

PNAME – Programmer Name, SPLACE – Study Place, CCOST – Course Cost, DEVIN – Developed in, SCOST – Software Cost, DCOST – Development Cost, PROF1 – Proficiency 1

QUERIES :

1. Find out the selling cost average for packages developed in Oracle.

SELECT AVG(SCOST) FROM SOFTWARE WHERE DEVIN = 'ORACLE';

2. Display the names, ages and experience of all programmers.

SELECT PNAME,TRUNC(MONTHS_BETWEEN(SYSDATE,DOB)/12) "AGE", TRUNC(MONTHS_BETWEEN(SYSDATE,DOJ)/12) "EXPERIENCE" FROM PROGRAMMER;

3. Display the names of those who have done the PGDCA course.

SELECT PNAME FROM STUDIES WHERE COURSE = 'PGDCA';

4. What is the highest number of copies sold by a package?

SELECT MAX(SOLD) FROM SOFTWARE;

5. Display the names and date of birth of all programmers born in April.

SELECT PNAME, DOB FROM PROGRAMMER WHERE DOB LIKE '%APR%'

6. Display the lowest course fee.

SELECT MIN(CCOST) FROM STUDIES;

7. How many programmers have done the DCA course.

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

8. How much revenue has been earned through the sale of packages developed in C.

SELECT SUM(SCOST*SOLD-DCOST) FROM SOFTWARE GROUP BY DEVIN HAVING DEVIN = 'C';

9. Display the details of software developed by Rakesh.

SELECT * FROM SOFTWARE WHERE PNAME = 'RAKESH';

10. How many programmers studied at Pentafour.

SELECT * FROM STUDIES WHERE SPLACE = 'PENTAFOUR';

11. Display the details of packages whose sales crossed the 5000 mark.

SELECT * FROM SOFTWARE WHERE SCOST*SOLD-DCOST > 5000;

12. Find out the number of copies which should be sold in order to recover the development cost of each package.

SELECT CEIL(DCOST/SCOST) FROM SOFTWARE;

13. Display the details of packages for which the development cost has been recovered.

SELECT * FROM SOFTWARE WHERE SCOST*SOLD >= DCOST;

14. What is the price of costliest software developed in VB?

SELECT MAX(SCOST) FROM SOFTWARE GROUP BY DEVIN HAVING DEVIN = 'VB';

15. How many packages were developed in Oracle ?

SELECT COUNT(*) FROM SOFTWARE WHERE DEVIN = 'ORACLE';

16. How many programmers studied at PRAGATHI?

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

17. How many programmers paid 10000 to 15000 for the course?

SELECT COUNT(*) FROM STUDIES WHERE CCOST BETWEEN 10000 AND 15000;

18. What is the average course fee?

SELECT AVG(CCOST) FROM STUDIES;

19. Display the details of programmers knowing C.

SELECT * FROM PROGRAMMER WHERE PROF1 = 'C' OR PROF2 = 'C';

20. How many programmers know either C or Pascal?

SELECT * FROM PROGRAMMER WHERE PROF1 IN ('C','PASCAL') OR PROF2 IN ('C','PASCAL');

21. How many programmers don’t know C and C++?

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

22. How old is the oldest male programmer?

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

23. What is the average age of female programmers?

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

24. Calculate the experience in years for each programmer and display along with their names in descending order.

SELECT PNAME, TRUNC(MONTHS_BETWEEN(SYSDATE,DOJ)/12) FROM PROGRAMMER ORDER BY PNAME DESC;

25. Who are the programmers who celebrate their birthdays during the current month?

SELECT PNAME FROM PROGRAMMER WHERE TO_CHAR(DOB,'MON') = TO_CHAR(SYSDATE,'MON');

26. How many female programmers are there?

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

27. What are the languages known by the male programmers?

SELECT DISTINCT(PROF1) FROM PROGRAMMER WHERE SEX = 'M';

28. What is the average salary?

SELECT AVG(SAL) FROM PROGRAMMER;

29. How many people draw 5000 to 7500?

SELECT COUNT(*) FROM PROGRAMMER WHERE SAL BETWEEN 5000 AND 7500;

30. Display the details of those who don’t know C, C++ or Pascal.

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

31. Display the costliest package developed by each programmer.

SELECT PNAME,TITLE,SCOST FROM SOFTWARE WHERE SCOST IN (SELECT MAX(SCOST) FROM SOFTWARE GROUP BY PNAME);

32. Produce the following output for all the male programmers

Programmer

Mr. Arvind – has 15 years of experience

SELECT 'Mr.' || PNAME || ' - has ' || TRUNC(MONTHS_BETWEEN(SYSDATE,DOJ)/12) || ' years of experience' “Programmer” FROM PROGRAMMER WHERE SEX = 'M' UNION SELECT 'Ms.' || PNAME || ' - has ' || TRUNC (MONTHS_BETWEEN (SYSDATE,DOJ)/12) || ' years of experience' “Programmer” FROM PROGRAMMER WHERE SEX = 'F';