SET 1
1) Display THE NUMBER OF packages developed in EACH language.
select dev_in,count(*) from software group by dev_in;
2) Display THE NUMBER OF packages developed by EACH person.
select name,count(*) from software group by dev_in;
3) Display THE NUMBER OF male and female programmer.
select sex,count(*) from programmer group by sex;
4) Display THE COSTLIEST packages and HIGEST selling developed in EACH
language.
select dev_in,max(scost),max(ccost) from software group by dev_in;
5) Display THE NUMBER OF people BORN in EACH YEAR.
select year(dob),count(*) from programmer group by year(dob);
6) Display THE NUMBER OF people JOINED in EACH YEAR.
select year(doj),count(*) from programmer group by year(doj);
7) Display THE NUMBER OF people BORN in EACH MONTH.
select month(dob),count(*) from programmer group by month(dob);
8) Display THE NUMBER OF people JOINED in EACH MONTH.
select month(doj),count(*) from programmer group by month(doj);
9) Display the language wise COUNTS of prof1.
select prof1,count(*) from programmer group by prof1;
10) Display the language wise COUNTS of prof2.
select prof2,count(*) from programmer group by prof2;
11) Display THE NUMBER OF people in EACH salary group.
select salary,count(*) from programmer group by salary;
12) Display THE NUMBER OF people who studied in EACH institute.
select splace,count(*) from studies group by splace;
13) Display THE NUMBER OF people who studied in EACH course.
select course,count(*) from studies group by course;
14) Display the TOTAL development COST of the packages developed in EACH
language.
select dev_in,sum(ccost) from software group by dev_in;
15) Display the selling cost of the package developed in EACH language.
select dev_in,sum(scost) from software group by dev_in;
16) Display the cost of the package developed by EACH programmer.
select name,sum(ccost) 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(*) from software group by name;
19) Display the sales COST of packages developed by EACH programmer language
wise.
select name,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,max(ccost),min(ccost) 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(ccost), avg(scost),((scost*sold)/ccost) 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;
25) Display the programmer's name and their packages.
select name,title from software;
26) Display the NUMBER of packages in EACH language.
select dev_in,count(title) from software group by dev_in;
27) Display the NUMBER of packages in EACH language for which development cost
is less than
1000.
select dev_in,count(title) from software where ccost<1000 group by
dev_in;
28) Display the AVERAGE difference BETWEEN scost and dcost for EACH language.
select dev_in,avg(ccost-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(ccost),sum(ccost-(scost*sold)) from software where
ccost<scost group by name;
30) Display highest, lowest and average salaries for THOSE earning MORE than
2000.
select name,max(salary),min(salary),avg(salary) from programmer where
salary>2000 group by name;
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');
SET 3/4
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=[Link] 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 [Link]=[Link] 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 [Link]=[Link] 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 [Link]=[Link] and
[Link]=[Link] and sex='f' and splace='pragathi';
6) Display NUMBER of packages NUMBER of copies sold and sales value of EACH
programmer Institute-wise.
Select [Link], count(software.dev_in), count([Link]),
sum([Link]*[Link]) from software,studies where
[Link]=[Link] group by [Link];
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
[Link]=[Link] and [Link]=[Link] and
[Link]=[Link] 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 [Link]=[Link] 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 [Link] = [Link];
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 [Link]=[Link] 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 [Link]=[Link] and
[Link]=[Link] 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 [Link]=[Link] and
splace='apple';
14) Who are the programmers WHO JOINED in the same day?
select [Link],[Link] from programmer a,programmer b where [Link]=[Link] and
[Link] <> [Link];
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 [Link],sum([Link]*[Link]) from software,studies
where [Link]=[Link] group by [Link];
17) In which institutes did the person who developed the COSTLIEST package
study?
select splace from software st,studies s where [Link]=[Link] 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 [Link],[Link] from programmer p1,software s1,studies s2 where
[Link]=[Link] and [Link]=[Link] 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 [Link],ceil(ccost/salary) from programmer p,studies s where
[Link]=[Link];
21) Which is the COSTLIEST package developed by a person with under 3 years
expenence?
select [Link] from software x, programmer y where (months_between(sysdate,
[Link])/12) > 3 and [Link]=[Link];
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=[Link] 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([Link]) from software s,studies st where [Link]=[Link] group by
[Link],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=[Link] 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 [Link]=[Link] and
sex='f' and salary>(select max(salary) from programmer p,software s where
[Link]=[Link] and sex='m');
26) How many packages were developed by the MOST experienced programmers from
BDPS.
select count([Link]) from software x, programmer y, studies x where months_between(sysdate, [Link])/12) =
(select max(months_between(sysdate,[Link])/12) from programmer y, studies = where [Link] = 'BDPS' and
[Link] = [Link]) and [Link]=[Link] [Link]='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 [Link],count(dev_in) from programmer p1,software s where [Link]=[Link]
group by [Link];
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
[Link]=[Link] and [Link]=[Link] and
[Link]='s.s.i.l.';
set 4
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 '% %';