Create only those tables which are related to your query.

Table Name: Programmer

Description: Used to store information about programmers.

Name Null? Type Remark

NAME Not Null Varchar2 (8) Name
DOB Not Null Date Date of Birth
DOJ Not Null Date Date of Joining
SEX Not Null Varchar2 (1) Male/Female
PROF1 Varchar2 (8) Known Language 1
PROF2 Varchar2 (8) Known Language 2
SALARY Not Null Number (8) Salary

Programmer

NAME DOB DOJ SEX PROF1 PROF2 SALARY

-------------------- --------- --------- -------- ---------- ---------- ---------
anand 21-APR-92 21-APR-2008 m pascal basic 3200
altaf 02-JUL-95 13-NOV-2005 m clipper cobol 2800
jagadish 05-OCT-93 04-OCT-2006 m oracle java 4100
juliana 31-JAN-93 04-APR-2006 f cobol dbase 3000
kamala 30-OCT-78 02-JUN-1999 f c dbase 2900
mary 24-JUN-94 01-FEB-2004 f c oracle 4500
nelson 11-SEP-69 11-OCT-89 m cobol dbase 2500
patrick 16-NOV-65 21-APR-90 m pascal clipper 2800
qadir 31-AUG-65 21-APR-93 m assembly c 3000
ramesh 03-MAY-67 28-FEB-98 m pascal dbase 3200
rebacca 01-JAN-67 01-DEC-90 f basic cobol 2500
remitha 19-APR-70 20-APR-90 f c assembly 3600
revathi 02-DEC-69 02-JAN-92 f pascal basic 3700
vijaya 11-DEC-65 02-MAY-92 f foxpro c 3500
Table Name: Software

Name Null? Type Remark

NAME Not Null Varchar2(8) Name
TITILE Not Null Varchar2(20) Developed Project Name
DEV_IN Not Null Varchar2(8) Language Developed
SCOST Number(7,2) Software Cost
DCOST Number(5) Development Cost
SOLD Number(3) Number of Software Sold

Software

NAME TITLE DEVIN SCOST DCOST SOLD

-------------------- -------------------- -------------------- --------- --------- ---------
anand parachutes basic 399.95 6000 43
anand video titling pack pascal 7500 16000 9
jagadesh serial link utility java 800 7500 10
jagadesh shares management oracle 3000 12000 14
juliana inventory control cobol 3000 3500 0
kamala payroll package dbase 9000 20000 7
mary financila acc. s/w oracle 18000 85000 4
mary code generator c 4500 20000 23
mary read me c++ 300 1200 84
patrick graphic editor pascal 750 5000 11
qadir bombsaway assembly 499 530 114
qadir vaccines c 1900 3400 21
ramesh hotel management dbase 12000 35000 4
remitha pc utilities c 725 5000 51
Table Name: Studies
Description: Used to store information about programmer studies.

Name Null? Type Remark

NAME Not Null Varchar2(8) Name
SPALCE Not Null Varchar2(9) Studied Place
COURSE Not Null Varchar2(5) Course Studied
CCOST Not Null Number(5) Course Cost

Studies

NAME SPLACE COURSE CCOST

-------------------- -------------------- ---------- ---------
anand sabhari pgdca 4500
jagadesh ccit dca 7200
juliana bits dca 22000
jagadesh ssil dca 3500
kamala pragathi dcp 5000
mary sabhari pgdca 4500
nelson pragathi dap 6200
patrick pragathi dcap 5200
qadir apple hdcp 14000
ramesh sabhari pgdca 4500
rebacca brilliant dca 11000
remitha bdps dcs 6000
Perform the Queries as per instruction given to you in examination:

1. Find out SELLING COST AVERAGE for package developed in PASCAL.

2. Display the names and ages of all the PROGRAMMERS.
3. Display the names of those who have done the DAP course.
4. What is the highest number of copies sold by a package.
5. Display the name and date of birth of all the programmers born in JANUARY.
6. Display the LOWEST course fee.
7. How many programmers have done the PGDCA course.
8. How much revenue has been earned through sale of packages in C.
9. Display the details of SOFTWAWRE developed by Ramesh.
10. How many programmers have studied at SABHARI.
11. Display the details of PACKAGE who’s sales CROSSED the 20000 marks.
12. Find out the number of copies which should be sold in order to recover the
DEVELOPMENT COST of each package.
13. Display the details of packages for which DEVELOPMENT COST has been recovered.
14. What is the price of the costliest software developed in BASIC?
15. How many packages were developed in DBASE?
16. How many programmers have studied at PRAGATHI?
17. How many programmers paid 5000 to 10000 for their COURSE?
18. what is the AVERAGE COURSE FEE?
19. Display the details of programmers knowing C.
20. How many programmers know either COBOL or PASCAL?
21. How many programmers don’t know PASCAL. & C.
22. How old is the oldest mail programmer.
23. What is the average age of female programmers?
24. Calculate the experience in years for each programmer & display along with the names in
descending order.
25. Who are the programmers who celebrate their birthdays during the current month?
26. How many female programmers are there?
27. What are the languages known by the male programmers?
28. What are the average salaries?
29. How many people draw 2000 to 4000?
30. Display the details of those who don’t know CLIPPER, COBOL & PASCAL.
31. How many female programmers knowing C are above 24 years of age?
32. Who are the programmers who will be celebrating their birthdays within a week?
33. Display the details of those with less than one year’s experience.
34. Display the details of those who will be completing two years of service this year.
35. Calculate the amount to be recovered for those packages whose development cost has not yet
been recovered.
36. List the packages, which have not been sold so far.
37. Find out the Cost of the software developed by MARY.
38. Display the institute names from the STUDIES table without duplicates.
39. How many different courses are mentioned in the STUDIES table.
40. Display the name of the programmers who’s name contains two occurrences of letter ‘A’.
41. Display the name of the programmers who’s name contains unto 5 characters.
42. How many female programmers knowing COBOL have more than two years experience.
43. What is the length of the shortest name in the PROGRAMMER table.
44. What is the average DEVELOPMENT COST of a package developed in COBOL.