Oracle Database SQL practice for Beginners.

Tables: Studies, Software, Programmer

CREATE TABLE STUDIES(
STNAME VARCHAR2(20) PRIMARY KEY,
SPLACE VARCHAR2(20) NOT NULL,
COURSE VARCHAR2(20) NOT NULL,
COST NUMBER(7,2) NOT NULL);


CREATE TABLE SOFTWARE(
SNAME VARCHAR2(20) REFERENCES STUDIES(STNAME),
TITTLE VARCHAR2(20) NOT NULL,
DEV_IN VARCHAR2(10) NOT NULL,
SCOST NUMBER(7,2),
DCOST NUMBER(7,2),
SOLD NUMBER(4));


CREATE TABLE PROGRAMMER(
PNAME VARCHAR2(20) REFERENCES STUDIES(STNAME),
DOB DATE NOT NULL,
DOJ DATE NOT NULL,
SEX CHAR(1) CHECK (SEX IN ('M','F')),
PROF1 VARCHAR2(15) NOT NULL,
PROF2 VARCHAR2(15),
SALARY NUMBER(7,2) CHECK (SALARY>0));


INSERT INTO STUDIES VALUES('ANAND','SABHARI','PGDCA',45000);
INSERT INTO STUDIES VALUES('ALTAF','COIT','DCA',7200);
INSERT INTO STUDIES VALUES('JULIANA','BITS','MCA',22000);
INSERT INTO STUDIES VALUES('KAMALA','PRAGATHI','DCP',5000);
INSERT INTO STUDIES VALUES('MARY','SABHARI','PGDCA',4600);
INSERT INTO STUDIES VALUES('NELSON','PRAGATHI','DAP',6200);
INSERT INTO STUDIES VALUES('PATRICK','SABHARI','DCA',5200);
INSERT INTO STUDIES VALUES('QADIR','APPLE','HDCP',14000);
INSERT INTO STUDIES VALUES('RAMESH','SABHARI','PGDCA',4500);
INSERT INTO STUDIES VALUES('REBECCA','BPILLANI','DCA',11000);
INSERT INTO STUDIES VALUES('REMITHA','BDPS','DCS',6000);
INSERT INTO STUDIES VALUES('REVATHI','SABHARI','DAP',5000);
INSERT INTO STUDIES VALUES('VIJAYA','BDPS','DCA',48000);


INSERT INTO SOFTWARE VALUES('ANAND','PARACHUTES','BASIC',399,6000,43);
INSERT INTO SOFTWARE VALUES('ANAND','VIDEO TITLING PACK','PASCAL',7500,16000,9);
INSERT INTO SOFTWARE VALUES('JULIANA','INVENTORY CONTROL','COBOL',3000,3500,0);
INSERT INTO SOFTWARE VALUES('KAMALA','PAYROLL PACKAGE','DBASE',9000,20000,7);
INSERT INTO SOFTWARE VALUES('MARY','FINANCIAL ACC.S/W','ORACLE',18000,85000,4);
INSERT INTO SOFTWARE VALUES('PATRICK','CODE GENERATION','COBOL',4500,20000,23);
INSERT INTO SOFTWARE VALUES('QADIR','READ ME','C++',300,1200,84);
INSERT INTO SOFTWARE VALUES('QADIR','BOMBS AWAY','ASSEMBLY',750,5000,11);
INSERT INTO SOFTWARE VALUES('QADIR','VACCINES','C',1900,3400,21);
INSERT INTO SOFTWARE VALUES('RAMESH','HOTEL MANAGEMENT','DBASE',12000,3500,4);
INSERT INTO SOFTWARE VALUES('RAMESH','DEAD LEE','PASCAL',599,4500,73);
INSERT INTO SOFTWARE VALUES('REMITHA','PC UTILITIES','C',725,5000,51);
INSERT INTO SOFTWARE VALUES('REMITHA','TSR HELP PACKAGE','ASSEMBLY',2500,6000,6);
INSERT INTO SOFTWARE VALUES('REVATHI','HOSPITAL MANAGEMENT','PASCAL',1100,75000,2);
INSERT INTO SOFTWARE VALUES('REVATHI','QUIZ MASTER','BASIC',3200,2100,15);
INSERT INTO SOFTWARE VALUES('VIJAYA','ISR EDITION','C',900,700,6);


INSERT INTO PROGRAMMER VALUES('ANAND','21-APR-66','21-APR-92','M','PASCAL','BASIC',3200);
INSERT INTO PROGRAMMER VALUES('ALTAF','02-JUL-64','13-NOV-90','M','CLIPPER','COBOL',2800);
INSERT INTO PROGRAMMER VALUES('JULIANA','31-JAN-68','21-APR-90','F','COBOL','DBASE',3000);
INSERT INTO PROGRAMMER VALUES('KAMALA','30-OCT-68','02-JAN-92','F','C','DBASE',2900);
INSERT INTO PROGRAMMER VALUES('MARY','24-JUN-70','01-FEB-91','F','C++','ORACLE',4500);
INSERT INTO PROGRAMMER VALUES('NELSON','11-SEP-85','11-OCT-89','M','COBOL','DBASE',2500);
INSERT INTO PROGRAMMER VALUES('PATRICK','10-NOV-65','21-APR-90','M','PASCAL','CLIPPER',2800);
INSERT INTO PROGRAMMER VALUES('QADIR','31-AUG-65','21-APR-91','M','ASSEMBLY','C',3000);
INSERT INTO PROGRAMMER VALUES('RAMESH','03-MAY-67','28-FEB-91','M','PASCAL','DBASE',3200);
INSERT INTO PROGRAMMER VALUES('REBECCA','01-JAN-67','01-DEC-90','F','BASIC','COBOL',2500);
INSERT INTO PROGRAMMER VALUES('REMITHA','19-APR-70','20-APR-93','F','C','ASSEMBLY',3600);
INSERT INTO PROGRAMMER VALUES('REVATHI','02-DEC-69','02-JAN-92','F','PASCAL','BASIC',3700);
INSERT INTO PROGRAMMER VALUES('VIJAYA','14-DEC-65','02-MAY-92','F','FOXPRO','C',3500);

Question Set - I

1) Display the details of THOSE WHO are drawing the same salary.

select name, salary from programmer where salary = any(select salary from programmer p group by salary having salary=p.salary and count(*)>1);

2) Display the details of software developed by male programmers earing MORE than 3000.

select software.* from programmer p,software s where p.name=s.name and salary>3000 and sex='m';

3) Display details of packages developed in PASCAL by female programmers.

select s.* from programmer p,software s where p.name=s.name and sex='f' and dev_in='pascal';

4) Display the details of these programmer WHO joined BEFORE 1990.

      select * from programmer where to_char(doj,'yy')<90;

5)Display details of software developed in C by female programmers of PRAGATHI.

select s.* from software s,studies st,programmer p where s.name=st.name and p.name=s.name and sex='f' and splace='pragathi';

6) Display NUMBER of packages NUMBER of copies sold and sales value of EACH 

   programmer Institute-wise.

Select studies.splace, count(software.dev_in), count(software.sold), sum(software.sold*software.scost) from software,studies where software.name=studies.name group by studies.splace;

7) Display details of software developed in DBASE by male programmers WHO belong to  

   the institute on which MOST NUMBER OF programmers studies.

select software.* from programmer,software,studies where programmer.name=software.name and software.name=studies.name and programmer.name=studies.name and sex='m' and dev_in='dbase' and splace= (select splace from studies group by splace having count(splace) =(select max(count(splace))from studies group by splace));

8) Display the details of the software that was developed by male programmers born BEFORE 1965 and female programmers born AFTER 1975.

select software.* from programmer p,software s where s.name=p.name and sex='m' and to_char(dob,'yy')<64 or sex='f' and To_char(dob,'yy')>75);

9) Display the details of the software that was developed in the language that is NOT the programmers first proficiency.

      select distinct x.* from software x, programmer y where y.prof1 <> x.dev_in and x.name = y.name;

10) Display details of software that was developed in the language which is NITHER first NOR second proficiency of the programmer.

select s.* from programmer p,software s where s.name=p.name and (dev_in <> prof1 and dev_in <> prof2);

11) Display details of software developed by male students of SABHARI.

select s.* from programmer p,software s,studies st where p.name=s.name and s.name=st.name and sex='m' and splace='sabhari';

12) Display the names of programmers WHO HAVE NOT developed any package.

      select name from programmer where name not in(select name from software);

13) What is the total cost of the software developed by the programmers by APPLE?

select sum(scost) from software s,studies st where s.name=st.name and splace='apple';

14) Who are the programmers WHO JOINED in the same day?

select a.name,a.doj from programmer a,programmer b where a.doj=b.doj and a.name <> b.name;

15) Who are the programmers WHO HAVE THE SAME PROF2?

select name from programmer where prof2 = any(select prof2 from programmer group by prof2 having count(*) >1);

16) Display the total sales values of software, institutes-wise.

select studies.splace,sum(software.sold*software.scost) from software,studies where studies.name=software.name group by studies.splace;

17) In which institutes did the person who developed the COSTLIEST package study?

select splace from software st,studies s where s.name=st.name group by splace,dcost having max(dcost)=(select max(dcost) from software);

18) Which language listed in prof1 and prof2 HAS NOT BEEN used to develop any package?

select prof1 from programmer where prof1 not in(select dev_in from software) union select prof2 from programmer where prof2 not in(select dev_in from software);

19) How much does the person WHO developed the HIGHEST selling package earn and WHAT

course did he/she undergo?

select p1.salary,s2.course from programmer p1,software s1,studies s2 where p1.name=s1.name and s1.name=s2.name and scost=(select max(scost) from software);

20) How many months will it take for each programmer to recover the cost of the course underwent?

select p.name,ceil(ccost/salary) from programmer p,studies s where s.name=p.name;

21) Which is the COSTLIEST package developed by a person with under 3 years expenence?

select x.title from software x, programmer y where (months_between(sysdate, y.doj)/12) > 3 and x.name=y.name;

22) What is the AVERAGE salary for those WHOSE software's sales value is more than 50,000?

select avg(salary) from programmer p,software s where p .name=s.name and sold*scost>50000;

23) How many packages were developed by the students WHO studied in the institute that Charge the LOWEST course fee?

select count(s.name) from software s,studies st where s.name=st.name group by s.name,ccost having min(ccost)=(select min(ccost) from studies);

24) How many packages were developed by the person WHO developed the CHEAPEST

package. Where did he\she study?

select count(*) from programmer p,software s where s .name=p.name group by dev_in having min(dcost)=(select min(dcost) from software);

25) How many packages were developed by female programmers earning MORE than the

HIGHEST paid male programmer?

select count(dev_in) from programmer p,software s where s.name=p.name and sex='f' and salary>(select max(salary) from programmer p,software s where s.name=p.name and sex='m');

26) How many packages were developed by the MOST experienced programmers from BDPS.

select count(x.name) from software x, programmer y, studies x where months_between(sysdate, y.doj)/12) = (select max(months_between(sysdate,y.doj)/12) from programmer y, studies = where x.splace = 'BDPS' and y.name = z.name) and x.name=y.name andz.splace='BDPS';

27) List the programmers (from software table) and institutes they studied, including those WHO DIDN'T develop any package.

      select name,splace from studies where name not in(select name from software);

28) List each profit with the number of programmers having that prof1 and the number of packages developed in that prof1.

select count(*),sum(scost*sold-dcost) "PROFIT" from software where dev_in in (select prof1 from programmer) group by dev_in;

29) List programmer names (from programmer table) and number of packages EACH developed.

select s.name,count(dev_in) from programmer p1,software s where p1.name=s.name group by s.name;

30) List all the details of programmers who has done a course at S.S.I.L.

select programmer.* from programmer,studies where splace='SSIL' and programmer.name=software.name and programmer.name=studies.name and studies.splace='s.s.i.l.';

Question Set 2

1) Who is the highest paid C programmer?

SELECT * FROM PROGRAMMER WHERE SALARY=(SELECT MAX(SALARY) FROM PROGRAMMER WHERE PROF1 LIKE 'C' OR PROF2 LIKE 'C');

2) Who is the highest paid female cobol programmer?

SELECT * FROM PROGRAMMER WHERE SALARY=(SELECT MAX(SALARY) FROM PROGRAMMER WHERE (PROF1 LIKE 'COBOL' OR PROF2 LIKE 'COBOL')) AND SEX LIKE 'F';

3) Display the name of the HIGEST paid programmer for EACH language (prof1)

SELECT DISTINCT NAME, SALARY, PROF1 FROM PROGRAMMER WHERE (SALARY,PROF1) IN (SELECT MAX(SALARY),PROF1 FROM PROGRAMMER GROUP BY PROF1);

4) Who is the LEAST experienced programmer?

SELECT FLOOR((SYSDATE-DOJ)/365) EXP,NAME FROM PROGRAMMER WHERE FLOOR((SYSDATE-DOJ)/365) = (SELECT MIN(FLOOR((SYSDATE-DOJ)/365)) FROM PROGRAMMER);

5) Who is the MOST experienced programmer?

SELECT FLOOR((SYSDATE-DOJ)/365) EXP,NAME,PROF1,PROF2 FROM PROGRAMMER WHERE FLOOR((SYSDATE-DOJ)/365) = (SELECT MAX(FLOOR((SYSDATE-DOJ)/365)) FROM PROGRAMMER) AND (PROF1 LIKE 'COBOL' OR PROF2 LIKE 'COBOL');

6) 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;

7) Who is the YONGEST programmer knowing DBASE?

SELECT FLOOR((SYSDATE-DOB)/365) AGE, NAME, PROF1, PROF2 FROM PROGRAMMER WHERE FLOOR((SYSDATE-DOB)/365) = (SELECT MIN(FLOOR((SYSDATE-DOB)/365)) FROM PROGRAMMER WHERE PROF1 LIKE 'DBASE' OR PROF2 LIKE 'DBASE');

8) Which institute has MOST NUMBER of students?

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

9) Which female programmer earns MORE than 3000/- but DOES NOT know C, C++, Oracle or Dbase?

     

SELECT * FROM PROGRAMMER WHERE SEX = 'F' AND SALARY >3000 AND (PROF1 NOT IN('C','C++','ORACLE','DBASE') OR PROF2 NOT IN('C','C++','ORACLE','DBASE'));

10) Which is the COSTLIEST course?

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

11) Which course has been done by MOST of the students?

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

12) Display name of the institute and course Which has below AVERAGE course fee?

SELECT SPLACE,COURSE FROM STUDIES WHERE CCOST < (SELECT AVG(CCOST) FROM STUDIES);

13) Which institute conducts COSTLIEST course?

      SELECT SPLACE FROM STUDIES WHERE CCOST = (SELECT MAX(CCOST) FROM STUDIES);

14) Which course has below AVERAGE number of students?

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

15) Which institute conducts the above course?

SELECT SPLACE FROM STUDIES WHERE COURSE IN (SELECT COURSE FROM STUDIES GROUP BY COURSE HAVING COUNT(NAME) < (SELECT AVG(COUNT(NAME)) FROM STUDIES GROUP BY COURSE));

16) Display names of the course WHOSE fees are within 1000(+ or -) of the AVERAGE fee.

SELECT COURSE FROM STUDIES WHERE CCOST < (SELECT AVG(CCOST)+1000 FROM STUDIES) AND CCOST > (SELECT AVG(CCOST)-1000 FROM STUDIES);

17) Which package has the HIGEST development cost?

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

18) Which package has the LOWEST selling cost?

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

19) Who developed the package, which has sold the LEAST number of copies?

SELECT NAME,SOLD FROM SOFTWARE WHERE SOLD = (SELECT MIN(SOLD) FROM SOFTWARE);

20) Which language was used to develop the package WHICH has the HIGEST sales amount?

SELECT DEV_IN,SCOST FROM SOFTWARE WHERE SCOST = (SELECT MAX(SCOST) FROM SOFTWARE);

21) How many copies of the package that has the LEAST DIFFRENCE between development and selling cost were sold?

SELECT SOLD,TITLE FROM SOFTWARE WHERE TITLE = (SELECT TITLE FROM SOFTWARE WHERE (DCOST-SCOST)=(SELECT MIN(DCOST-SCOST) FROM SOFTWARE);

22) Which is the COSTLIEAST package developed in PASCAL?

SELECT TITLE FROM SOFTWARE WHERE DCOST = (SELECT MAX(DCOST) FROM SOFTWARE WHERE DEV_IN LIKE 'PASCAL');

23) Which language was used to develop the MOST NUMBER of package?

SELECT DEV_IN FROM SOFTWARE GROUP BY DEV_IN HAVING MAX(DEV_IN) = (SELECT MAX(DEV_IN) FROM SOFTWARE);

24) Which programmer has developed the HIGEST NUMBER of package?

SELECT NAME FROM SOFTWARE GROUP BY NAME HAVING MAX(NAME) = (SELECT MAX(NAME) FROM SOFTWARE);

25) Who is the author of the COSTLIEST package?

      SELECT NAME,DCOST FROM SOFTWARE WHERE DCOST = (SELECT MAX(DCOST) FROM   SOFTWARE);

26) Display names of packages WHICH have been sold LESS THAN the AVERAGE number of copies?

SELECT TITLE FROM SOFTWARE WHERE SOLD < (SELECT AVG(SOLD) FROM SOFTWARE);

27) Who are the female programmers earning MORE than the HIGEST paid male programmers?

SELECT NAME FROM PROGRAMMER WHERE SEX = 'F' AND SALARY > (SELECT(MAX(SALARY)) FROM PROGRAMMER WHERE SEX = 'M');

28) Which language has been stated as prof1 by MOST of the programmers?

SELECT PROF1 FROM PROGRAMMER GROUP BY PROF1 HAVING PROF1 = (SELECT MAX(PROF1) FROM PROGRAMMER);

29) Who are the authors of packages, WHICH have recovered MORE THAN double the development cost?

      SELECT NAME FROM SOFTWARE WHERE SOLD*SCOST > 2*DCOST;

30) Display programmer names and CHEAPEST package developed by them in EACH

language?

SELECT NAME,TITLE FROM SOFTWARE WHERE DCOST IN (SELECT MIN(DCOST) FROM SOFTWARE GROUP BY DEV_IN);

31) Who is the YOUNGEST male programmer born in 1965?

SELECT NAME FROM PROGRAMMER WHERE DOB=(SELECT (MAX(DOB)) FROM PROGRAMMER WHERE TO_CHAR(DOB,'YYYY') LIKE '1965');

32) Display language used by EACH programmer to develop the HIGEST selling and LOWEST selling package.

SELECT NAME, DEV_IN FROM SOFTWARE WHERE SOLD IN (SELECT MAX(SOLD) FROM SOFTWARE GROUP BY NAME) UNION SELECT NAME, DEV_IN FROM SOFTWARE WHERE SOLD IN (SELECT MIN(SOLD) FROM SOFTWARE GROUP BY NAME);

33) Who is the OLDEST female programmer WHO joined in 1992?

SELECT NAME FROM PROGRAMMER WHERE DOJ=(SELECT (MIN(DOJ)) FROM PROGRAMMER WHERE TO_CHAR(DOJ,'YYYY') LIKE '1992');

34) In WHICH year where the MOST NUMBER of programmer born?

SELECT DISTINCT TO_CHAR(DOB,'YYYY') FROM PROGRAMMER WHERE TO_CHAR(DOJ,'YYYY') = (SELECT MIN(TO_CHAR(DOJ,'YYYY')) FROM PROGRAMMER);

35) In WHICH month did MOST NUMBRER of programmer join?

SELECT DISTINCT TO_CHAR(DOJ,'MONTH') FROM PROGRAMMER WHERE TO_CHAR(DOJ,'MON') = (SELECT MIN(TO_CHAR(DOJ,'MON')) FROM PROGRAMMER);

36) In WHICH language are MOST of the programmer's proficient?

SELECT PROF1 FROM PROGRAMMER GROUP BY PROF1 HAVING COUNT(PROF1)=(SELECT  MAX(COUNT(PROF1)) FROM PROGRAMMER GROUP BY PROF1) OR COUNT(PROF2)=(SELECT MAX(COUNT(PROF2)) FROM PROGRAMMER GROUP BY PROF2)

UNION

SELECT PROF2 FROM PROGRAMMER GROUP BY PROF2 HAVING COUNT(PROF1)=(SELECT MAX(COUNT(PROF1)) FROM PROGRAMMER   GROUP BY PROF1) OR COUNT(PROF2)=(SELECT MAX(COUNT(PROF2)) FROM PROGRAMMERGROUP BY PROF2);

37) Who are the male programmers earning BELOW the AVERAGE salary of female

programmers?

SELECT NAME FROM PROGRAMMER WHERE SEX = 'M' AND SALARY < (SELECT(AVG(SALARY))FROM PROGRAMMER WHERE SEX = 'F');

Question Set 3

1) Display THE NUMBER OF packages developed in EACH language.

      SELECT DEV_IN LANGUAGE,COUNT(TITLE) NO_of_PACK FROM SOFTWARE GROUP BY DEV_IN;

2) Display THE NUMBER OF packages developed by EACH person.

     

      SELECT NAME,COUNT(TITLE) No_of_Pack  FROM SOFTWARE GROUP BY NAME;

3) Display THE NUMBER OF male and female programmer.

      SELECT SEX,COUNT(NAME) FROM PROGRAMMER GROUP BY SEX;

4) Display THE COSTLIEST packages and HIGEST selling developed in EACH language.

      SELECT DEV_IN,MAX(SCOST),MAX(SOLD) FROM SOFTWARE GROUP BY DEV_IN;

5) Display THE NUMBER OF people BORN in EACH YEAR.

SELECT TO_CHAR(DOB,'YY') AS YEAR,COUNT(NAME) FROM PROGRAMMER GROUP BY TO_CHAR(DOB,'YY');

6) Display THE NUMBER OF people JOINED in EACH YEAR.

SELECT TO_CHAR(DOJ,'YY') AS YEAR,COUNT(NAME) FROM PROGRAMMER GROUP BY TO_CHAR(DOJ,'YY');

7) Display THE NUMBER OF people BORN in EACH MONTH.

      SELECT SUBSTR(DOB,4,3),COUNT(NAME) FROM PROGRAMMER GROUP BY SUBSTR(DOB,4,3);

8) Display THE NUMBER OF people JOINED in EACH MONTH.

      SELECT SUBSTR(DOJ,4,3),COUNT(NAME) FROM PROGRAMMER GROUP BY SUBSTR(DOJ,4,3);

9) Display the language wise COUNTS of prof1.

      SELECT PROF1, COUNT(PROF1) FROM PROGRAMMER GROUP BY PROF1;

10) Display the language wise COUNTS of prof2.

      SELECT PROF2, COUNT(PROF2) FROM PROGRAMMER GROUP BY PROF2;

11) Display THE NUMBER OF people in EACH salary group.

      SELECT SALARY,COUNT(NAME) FROM PROGRAMMER GROUP BY SALARY;

12) Display THE NUMBER OF people who studied in EACH institute.

      SELECT SPLACE,COUNT(NAME) FROM STUDIES GROUP BY SPLACE;

13) Display THE NUMBER OF people who studied in EACH course.

      SELECT COURSE ,COUNT(NAME) FROM STUDIES GROUP BY COURSE;

14) Display the TOTAL development COST of the packages developed in EACH language.

      SELECT DEV_IN LANG,SUM(DCOST) TOTAL_COST FROM SOFTWARE GROUP BY DEV_IN;

15) Display the selling cost of the package developed in EACH language.

      SELECT DEV_IN ,SUM(SCOST) FROM SOFTWAREGROUP BY DEV_IN;

16) Display the cost of the package developed by EACH programmer.

      SELECT NAME,SUM(DCOST) AS TOTAL_COST FROM SOFTWARE GROUP BY NAME;

17) Display the sales values of the package developed in EACH programmer.

      SELECT NAME, SUM(SCOST*SOLD) FROM SOFTWARE GROUP BY NAME;

18) Display the NUMBER of packages developed by EACH programmer.

      SELECT NAME,COUNT(TITLE) AS TOTAL_PACK FROM SOFTWARE GROUP BY NAME;

19) Display the sales COST of packages developed by EACH programmer language wise.

      SELECT DEV_IN, SUM(SCOST) FROM SOFTWARE GROUP BY DEV_IN;

20) Display EACH programmers name,costliest package,cheapest packages developed by His/Her?

      SELECT NAME,MIN(DCOST),MAX(DCOST) FROM SOFTWARE GROUP BY NAME;

21) Display EACH language name with AVERAGE development cost, AVERAGE cost, selling

cost and AVERAGE price per copy.

      SELECT DEV_IN,AVG(DCOST),AVG(SCOST),AVG(SCOST) FROM SOFTWARE GROUP BY DEV_IN;

22) Display EACH institute name with NUMBER of courses, AVERAGE cost per course.

      SELECT SPLACE,COUNT(COURSE), AVG(CCOST) FROM STUDIES GROUP BY SPLACE;

23) Display EACH institute name with NUMBER of students.

      SELECT SPLACE,COUNT(NAME) FROM STUDIES GROUP BY SPLACE;

24) Display names of male and female programmers.

      SELECT NAME,SEX FROM PROGRAMMER ORDER BY SEX;

25) Display the programmer's name and their packages.

      SELECT NAME,TITLE FROM SOFTWARE ORDER BY NAME;

26) Display the NUMBER of packages in EACH language.

      SELECT COUNT(TITLE),DEV_IN FROM SOFTWARE GROUP BY DEV_IN;

27) Display the NUMBER of packages in EACH language for which development cost is less than

1000.

      SELECT COUNT(TITLE),DEV_IN FROM SOFTWARE WHERE DCOST<1000 GROUP BY DEV_IN;

28) Display the AVERAGE difference BETWEEN scost and dcost for EACH language.

      SELECT DEV_IN,AVG(DCOST - SCOST) FROM SOFTWARE GROUP BY DEV_IN;

29) Display the TOTAL scost, dcsot and amount TOBE recovered for EACH programmer for

whose dcost HAS NOT YET BEEN recovered.

      SELECT SUM(SCOST), SUM(DCOST), SUM(DCOST-(SOLD*SCOST)) FROM SOFTWARE GROUP BY NAME

HAVING SUM(DCOST)>SUM(SOLD*SCOST);

30) Display highest, lowest and average salaries for THOSE earning MORE than 2000.

      SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY) FROM PROGRAMMER WHERE SALARY > 2000;

Question Set 4

1) Display THE NUMBER OF packages developed in EACH language.

      SELECT DEV_IN LANGUAGE,COUNT(TITLE) NO_of_PACK FROM SOFTWARE GROUP BY DEV_IN;

2) Display THE NUMBER OF packages developed by EACH person.

     

      SELECT NAME,COUNT(TITLE) No_of_Pack  FROM SOFTWARE GROUP BY NAME;

3) Display THE NUMBER OF male and female programmer.

      SELECT SEX,COUNT(NAME) FROM PROGRAMMER GROUP BY SEX;

4) Display THE COSTLIEST packages and HIGEST selling developed in EACH language.

      SELECT DEV_IN,MAX(SCOST),MAX(SOLD) FROM SOFTWARE GROUP BY DEV_IN;

5) Display THE NUMBER OF people BORN in EACH YEAR.

SELECT TO_CHAR(DOB,'YY') AS YEAR,COUNT(NAME) FROM PROGRAMMER GROUP BY TO_CHAR(DOB,'YY');

6) Display THE NUMBER OF people JOINED in EACH YEAR.

SELECT TO_CHAR(DOJ,'YY') AS YEAR,COUNT(NAME) FROM PROGRAMMER GROUP BY TO_CHAR(DOJ,'YY');

7) Display THE NUMBER OF people BORN in EACH MONTH.

      SELECT SUBSTR(DOB,4,3),COUNT(NAME) FROM PROGRAMMER GROUP BY SUBSTR(DOB,4,3);

8) Display THE NUMBER OF people JOINED in EACH MONTH.

      SELECT SUBSTR(DOJ,4,3),COUNT(NAME) FROM PROGRAMMER GROUP BY SUBSTR(DOJ,4,3);

9) Display the language wise COUNTS of prof1.

      SELECT PROF1, COUNT(PROF1) FROM PROGRAMMER GROUP BY PROF1;

10) Display the language wise COUNTS of prof2.

      SELECT PROF2, COUNT(PROF2) FROM PROGRAMMER GROUP BY PROF2;

11) Display THE NUMBER OF people in EACH salary group.

      SELECT SALARY,COUNT(NAME) FROM PROGRAMMER GROUP BY SALARY;

12) Display THE NUMBER OF people who studied in EACH institute.

      SELECT SPLACE,COUNT(NAME) FROM STUDIES GROUP BY SPLACE;

13) Display THE NUMBER OF people who studied in EACH course.

      SELECT COURSE ,COUNT(NAME) FROM STUDIES GROUP BY COURSE;

14) Display the TOTAL development COST of the packages developed in EACH language.

      SELECT DEV_IN LANG,SUM(DCOST) TOTAL_COST FROM SOFTWARE GROUP BY DEV_IN;

15) Display the selling cost of the package developed in EACH language.

      SELECT DEV_IN ,SUM(SCOST) FROM SOFTWAREGROUP BY DEV_IN;

16) Display the cost of the package developed by EACH programmer.

      SELECT NAME,SUM(DCOST) AS TOTAL_COST FROM SOFTWARE GROUP BY NAME;

17) Display the sales values of the package developed in EACH programmer.

      SELECT NAME, SUM(SCOST*SOLD) FROM SOFTWARE GROUP BY NAME;

18) Display the NUMBER of packages developed by EACH programmer.

      SELECT NAME,COUNT(TITLE) AS TOTAL_PACK FROM SOFTWARE GROUP BY NAME;

19) Display the sales COST of packages developed by EACH programmer language wise.

      SELECT DEV_IN, SUM(SCOST) FROM SOFTWARE GROUP BY DEV_IN;

20) Display EACH programmers name,costliest package,cheapest packages developed by His/Her?

      SELECT NAME,MIN(DCOST),MAX(DCOST) FROM SOFTWARE GROUP BY NAME;

21) Display EACH language name with AVERAGE development cost, AVERAGE cost, selling

cost and AVERAGE price per copy.

      SELECT DEV_IN,AVG(DCOST),AVG(SCOST),AVG(SCOST) FROM SOFTWARE GROUP BY DEV_IN;

22) Display EACH institute name with NUMBER of courses, AVERAGE cost per course.

      SELECT SPLACE,COUNT(COURSE), AVG(CCOST) FROM STUDIES GROUP BY SPLACE;

23) Display EACH institute name with NUMBER of students.

      SELECT SPLACE,COUNT(NAME) FROM STUDIES GROUP BY SPLACE;

24) Display names of male and female programmers.

      SELECT NAME,SEX FROM PROGRAMMER ORDER BY SEX;

25) Display the programmer's name and their packages.

      SELECT NAME,TITLE FROM SOFTWARE ORDER BY NAME;

26) Display the NUMBER of packages in EACH language.

      SELECT COUNT(TITLE),DEV_IN FROM SOFTWARE GROUP BY DEV_IN;

27) Display the NUMBER of packages in EACH language for which development cost is less than

1000.

      SELECT COUNT(TITLE),DEV_IN FROM SOFTWARE WHERE DCOST<1000 GROUP BY DEV_IN;

28) Display the AVERAGE difference BETWEEN scost and dcost for EACH language.

      SELECT DEV_IN,AVG(DCOST - SCOST) FROM SOFTWARE GROUP BY DEV_IN;

29) Display the TOTAL scost, dcsot and amount TOBE recovered for EACH programmer for

whose dcost HAS NOT YET BEEN recovered.

      SELECT SUM(SCOST), SUM(DCOST), SUM(DCOST-(SOLD*SCOST)) FROM SOFTWARE GROUP BY NAME

HAVING SUM(DCOST)>SUM(SOLD*SCOST);

30) Display highest, lowest and average salaries for THOSE earning MORE than 2000.

      SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY) FROM PROGRAMMER WHERE SALARY > 2000;

Question Set 5

1) Find out the SELLING COST AVERAGE for the packages developed in PASCAL?

SELECT AVG (SCOST) FROM SOFTWARE WHERE DEV_IN LIKE 'PASCAL';

2) Display the names and ages of all programmers.

SELECT NAME AS NAME, FLOOR((SYSDATE-DOB)/365) AS AGE FROM PROGRAMMER;

3) Display all the programmers whose name end with H?

      SELECT NAME FROM PROGRAMMER WHERE NAME LIKE ‘%H’;

4) What is the highest numbers of copies sold by a package?

      SELECT MAX(SOLD) FROM SOFTWARE;

5) Display the names and date of birth of all the programmer born in JANUARY.

SELECT DOB, NAME FROM PROGRAMMER WHERE TO_CHAR(DOB,'MON') LIKE 'JAN';

     

6) Display lowest course fee.

SELECT MIN (CCOST) FROM STUDIES;

     

7) How many programmer has done PGDCA course.

      SELECT COUNT (NAME) FROM STUDIES WHERE COURSE LIKE 'PGDCA';

8) How much revenue has been earned through sales of packages in C.

      SELECT SUM (SOLD*SCOST) FROM SOFTWARE WHERE DEV_IN LIKE 'C';

9) Display the details of software developed by Ramesh?

      SELECT * FROM SOFTWARE WHERE NAME='RAMESH';

10) How many programmers studied at SABHARI.

      SELECT COUNT (NAME) AS NOPROGRAMMERS FROM STUDIES WHERE SPLACE='SABHARI';

11) Display the details of PACKAGES whose sales crossed the 20000 mark.

      SELECT * FROM SOFTWARE WHERE (SOLD*SCOST)>20000;

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

      SELECT ROUND (DCOST/SCOST) FROM SOFTWARE WHERE SCOST*SOLD<DCOST;

13) What is the price of the costliest software developed in BASIC?

SELECT MAX (SCOST) FROM SOFTWARE WHERE DEV_IN LIKE 'BASIC';

     

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

      SELECT * FROM SOFTWARE WHERE (SOLD*SCOST)>DCOST;

15) How many packages were developed in dbase?

      SELECT COUNT (TITLE) AS TOTAL FROM SOFTWARE WHERE DEV_IN='DBASE';

16) How many programmers studies at paragathi?

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

17) How many programmers paid 5000 to 10000 for their course?

SELECT COUNT (NAME) AS NO_OF_PROGRAMMERS FROM STUDIES WHERE CCOST>=5000 AND CCOST<=10000;

18) What is the average course fee?

      SELECT AVG (CCOST) AS AVERAGECOST 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 Cobol or Pascal?

SELECT COUNT (NAME) AS PROGRAMMERS FROM PROGRAMMER WHERE PROF1 IN ('COBOL' ,'PASCAL' ) OR PROF2 IN ('COBOL' ,'PASCAL' );

21) How many programmers don't know Pascal & C?

      SELECT COUNT (NAME) AS PROGRAMMERS FROM PROGRAMMER WHERE PROF1 NOT IN ('C','PASCAL')

AND PROF2 NOT IN ('C','PASCAL');

22) How old is the oldest male programmers?

      SELECT MAX (FLOOR((SYSDATE - DOB)/365)) FROM PROGRAMMER WHERE SEX = ‘M’;

23) What is the average age of female programmers?

      SELECT AVG (FLOOR((SYSDATE - DOB)/365)) FROM PROGRAMMER WHERE SEX = ‘F’;

24) Calculate the experience in years for each programmers and display along with the names in descending order?

SELECT NAME, FLOOR ((SYSDATE - DOJ)/365) AS EXPERIENCE FROM PROGRAMMER ORDER BY NAME DESC;

25) Who are the programmers who celebrate their birthday during the current month?

      SELECT NAME FROM PROGRAMMER WHERE TO_CHAR (DOB,'MM')=TO_CHAR(SYSDATE,'MM');

26) How many female programmers are there?

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

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

      SELECT DISTINCT PROF1 LANGUAGES FROM PROGRAMMER WHERE SEX='M' UNION

SELECT DISTINCT PROF2 FROM PROGRAMMERWHERE SEX='M';

28) What is the Average salary?

      SELECT AVG (SALARY) AS AVGSAL FROM PROGRAMMER;

29) How many people draw 2000 to 4000?

      SELECT NAME FROM PROGRAMMER WHERE SALARY BETWEEN 2000 AND 4000;

30) 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');

31) How many Female programmers knowing C are above 24 years of age?

SELECT COUNT (NAME) FROM PROGRAMMER WHERE SEX='F' AND (PROF1='C' OR PROF2='C') AND ((SYSDATE-DOB)/365) > 24;

32) Who are the programmers who will be celebrating their Birthday within a week?

SELECT NAME FROM PROGRAMMER WHERE TO_CHAR(DOB,'WW') = TO_CHAR(SYSDATE,'WW');

     

33 Display the details of those with less than a year's experience?

      SELECT * FROM PROGRAMMER WHERE FLOOR((SYSDATE - DOJ)/365)<1;

34 Display the details of those who will be completing 2 years of service this year?

      SELECT NAME FROM PROGRAMMER WHERE FLOOR((SYSDATE-DOJ)/365)=2;

35 Calculate the amount to be recovered for those packages whose development cost has not been recovered?

      SELECT (DCOST-(SCOST*SOLD)), TITLE FROM SOFTWARE WHERE (SCOST*SOLD)<DCOST;

36) List the packages which have not been sold so far?

      SELECT TITLE SOFTWARE FROM SOFTWARE WHERE SOLD=0;

37) Find out the cost of the software developed by Mary?

      SELECT TITLE, SCOST AS SOFTCOST FROM SOFTWARE WHERE NAME='MARY';

38) Display the institutes names from the studies table without duplicates?

      SELECT DISTINCT SPLACE FROM STUDIES;

39) How many different courses are mentioned in the studies table?

      SELECT DISTINCT COURSE FROM STUDIES;

40) Display the names of the programmers whose names contain 2 occurrences of the letter A?

      SELECT NAME FROM PROGRAMMER WHERE NAME LIKE '%A%A%';

41) Display the names of programmers whose names contain upto 5 characters?

      SELECT NAME FROM PROGRAMMER WHERE LENGTH(NAME)=5;

     

42) How many female programmers knowing COBOL have more than 2 years experience?

SELECT NAME FROM PROGRAMMER WHERE FLOOR((SYSDATE-DOJ)/365)>2 AND SEX='F' AND (PROF1='COBOL' OR PROF2='COBOL');

43) What is the length of the shortest name in the programmer table?

      SELECT MIN(LENGTH(NAME)) FROM PROGRAMMER;

44) What is the average development cost of a package developed in COBOL?

      SELECT AVG(DCOST) FROM SOFTWARE WHERE DEV_IN='COBOL';

45) Display the name,sex,dob(DD/MM/YY format), doj for all the programmers without using

conversion function?

      SELECT NAME, SEX , SUBSTR(DOB,1,2)||'/'||SUBSTR(DOB,4,3)||'/'||SUBSTR(DOB,8,2) DOB, SUBSTR(DOJ,1,2)||'/'||SUBSTR(DOJ,4,3)||'/'||SUBSTR(DOJ,8,2) DOJ FROM PROGRAMMER;

46) Who are the programmers who were born on the last day of the month?

      SELECT NAME FROM PROGRAMMER WHERE LAST_DAY(DOB) = DOB;

47) What is the amount paid in salaries of the male programmers who do not know Cobol?

      SELECT SALARY FROM PROGRAMMER WHERE SEX='M' AND (PROF1!='COBOL' OR PROF2!='COBOL);

48) Display the title, scost, dcost and difference between scost and dcost in descending order of

difference?

SELECT TITLE, SCOST, DCOST, DCOST -SCOST DIFF FROM SOFTWARE ORDER BY 4 DESC;

     

49) Display the name, dob, doj of those month of birth and month of joining are same?

      SELECT NAME FROM PROGRAMMER WHERE TO_CHAR(DOB,'MM')=TO_CHAR(DOJ,'MM');

50) Display the names of the packages whose names contain more than 1 word?

      SELECT TITLE FROM SOFTWARE WHERE TITLE LIKE '% %';