ASSIGNMENT SHEET NO: 1
ASSIGNMENT SHEET on RELATIONAL ALGEBRA AND SQL
Instructions to the students:
Answer the following 110 queries in SQL using the sample database tables given below.
For each query, you have to specify the question (as mentioned in the assignment sheet), the corresponding query representation in SQL and then followed by the answer returned by it.
Also you have to specify the DDL description instructions used in created the database has to be submitted in a separate file. ( SQL scripts)
Answer the questions in relational algebra wherever it is possible. This query has to be included along with a comment “ ALGEBRAIC QUERY for Question No. “ just before SQL query for the question. If there is no equivalent query exists, just give a comment “ NO Equivalent Relational algebraic query exists for the question No. “
The Deadline for submission of this Assignment: June 30th 2002.
During the laboratory sessions in the 1st week of July 2002, every student will be called for showing the execution of five queries randomly from this list.
Every student has to explain how the evaluation takes place and should give explanation for the query asked /posed.
TABLE NAME : STUDIES
-
PNAME
INSTITUTE
COURSE
COURSEFEE
ANAND
SABHARI
PGDCA
4500
ALTAF
COIT
DCA
7200
JULIANA
BITS
MCA
22000
KAMALA
PRAGATHI
DCP
5000
MARY
SABHARI
PGDCA
4500
NELSON
PRAGATHI
DAP
6200
PATRICK
PRAGATHI
DCAP
5200
QADIR
APPLE
HDCP
14000
RAMESH
SABHARI
PGDCA
4500
RESECCA
BRILLIANT
DCAP
11000
REMITHA
BDPS
DCS
6000
VIJAYA
BDPS
DCA
48000
TABLE NAME : SOFTWARE
PNAME |
TITLE |
DEVELOP ING |
SCOST |
DCOST |
SOLD |
|
MARY |
README |
CPP |
100.00 |
1200 |
84 |
|
ANAND |
PARACHUTES |
BASIC |
399.95 |
6000 |
43 |
|
ANAND |
VIDEOTITLING |
PASCAL |
7500.00 |
16000 |
9 |
|
JULIANA |
INVENTORY |
COBOL |
3000.00 |
3500 |
0 |
|
KAMALA |
PAYROLLPRG |
DBASE |
9000.00 |
20000 |
7 |
|
MARY |
FINANCIALACC |
ORACLE |
18000.00 |
85000 |
4 |
|
MARY |
CODEGENRRATOP |
C |
4500.00 |
20000 |
23 |
|
PATTRICK |
README |
CPP |
300.00 |
1200 |
84 |
|
QADIR |
BOMBSAWAY |
ASSEMBLY |
750.00 |
5000 |
11 |
|
QADIR |
VACCINES |
C |
1900.00 |
3400 |
21 |
|
RAMESH |
HOTLIMGMT |
DBASE |
12000.00 |
35000 |
4 |
|
RAMESH |
DEADLEE |
PASCAL |
599.95 |
4500 |
73 |
|
REMITHA |
PCUTILITIES |
C |
725.00 |
5000 |
51 |
|
REMITHA |
TSRHELPPKG |
ASSEMBLY |
2500.00 |
6000 |
7 |
|
REVATHI |
HOSPITALMGMT |
PASCAL |
1100.00 |
75000 |
2 |
|
VIJAYA |
TSREDITOR |
C |
900.00 |
700 |
6 |
TABLE NAME : PROGRAMMER
PNAME |
DOB |
DOJ |
SEX |
PROF1 |
PROF2 |
SALARY |
|
ANAND |
12-APR-66 |
21-APR-92 |
M |
PASCAL |
BASIC |
3200 |
|
ALTAF |
02-JUL-64 |
13-NOV-90 |
M |
CLIPPER |
COBOL |
2800 |
|
JULIANA |
31-JAN-60 |
21-APR-90 |
F |
COBOL |
DBASE |
3000 |
|
KAMALA |
30-OCT-68 |
02-JAN-92 |
F |
C |
DBASE |
2900 |
|
MARY |
24-JUN-70 |
01-FEB-91 |
F |
CPP |
ORACLE |
4500 |
|
NELSON |
11-SEP-85 |
11-MAR-89 |
M |
COBOL |
DBASE |
2500 |
|
PATTRICK |
10-NOV-65 |
21-APR-90 |
M |
PASCAL |
|
2800 |
|
QADIR |
31-AUG-65 |
21-APR-90 |
M |
ASSEMBLY |
C |
3000 |
|
RAMESH |
03-MAY-67 |
26-FEB-91 |
M |
PASCAL |
DBASE |
3200 |
|
REBECCA |
01-JAN-67 |
01-DEC-90 |
F |
BASIC |
COBOL |
2500 |
|
REMITHA |
19-APR-70 |
20-APR-93 |
F |
C |
ASSEMBLY |
3000 |
|
REVATHI |
02-DEC-69 |
02-JAN-92 |
F |
PASCAL |
BASIC |
3200 |
|
VIJAYA |
14-DEC-65 |
02-MAY-92 |
F |
FOXPRO |
C |
4500 |
Find out the selling average cost for Packages developed in PASCAL.
Display the names ages of all programmers
Display the names of those who have done the DAP course.
Display the names and date of births of all programmers born in January.
What is the highest number of copies sold by a package
Display the lowest course fee
How many programmers done the PGDCA course
How much revenue has been earned thru sales of packages developed in c.
Display the details of the software developed by Ramesh.
How may programmers studied at Sabhari
Display details of packages whose sales crossed the 2000 mark
Find out the number of copies which should be sold in order to
Display the details of packages for which developments cost have been recovered.
What is the cost of the costliest software developed in basic
How many packages developed in dbase
How many programmers studies in Pragathi
How many programmers paid 5000 to 10000 for their studies.
What is avg course fee
Display the detail the programmers knowing c
How many programmers know either Cobol or Pascal
How many programmers don’t know Pascal and c
How old is the oldest male programmers
Calculate the experience in years for each programmers and. Display along with the names in descending order.
Who are the programmers who celebrate their birthdays during the current month.
How many female programmers are there
What are the languages by male programmers
What is the average salary
How many people draw salary 2000 to 4000
Display the details of those who don’t know clipper Cobol or Pascal
Display the cost of package developed by each programmer
Display the sales values of the packages developed by the each programmer
Display the number of packages sold by each programmer.
Display the sales cost of the packages developed by each programmer
Display the sales cost of the packages developed by each programmer language wise
Display each language name with Average development cost, average selling cost and average price per copy.
Display each programmers name costliest and cheapest packages developed by him or her .
Display each institute name with number of courses average cost per course.
Display each institute name with number of students.
Display names of male and female programmers name and sex also
Display the name of programmers and their packages
Display the number of packages in each languages except c and c++.
Display the number of packages in each language for which development cost is less than 1000.
Display the average difference between SCOST and DCOST for each package.
Display the total SCOST, DCOST and amount to be recovered for each programmer for those whose DCOST has not yet been recovered.
Display the highest, lowest and average salaries for those earning more than 2000.
Who is the highest paid in ’C’ programmers.
Who is the highest paid female Cobol programmer
Display the names of the highest paid programmer for each language
Who is the least experienced programmer
Who is the most experienced male programmer knowing Pascal
Which language is known by only one programmer.
Who is the above programmer referred in 51.
Who is the youngest programmer knowing DBASE
Which female programmer earning more than 3000 does not know c. C++ oracle or DBASE.
Which institute has most number of students
What is the costliest course
Which course has been done by the most of the students
Which course has been done by the most of the student
Which institute conducts costliest course
Display the name of the institute and course which has below average course fee
Display the names of the courses whose fees are within 1000 (+or) of the average fee.
Which package has the highest development cost
Which course has below average number of students
Which package has the lowest selling cost.
Who developed the package that has sold the least number of copies
Which language has used to develop the package which has the highest sales amount
How many copies of package that has the least difference between development and selling cost were sold
Which is the costliest package developed in Pascal
Which language was used to develop the most number of packages
Which programmer has developed the highest number of packages
Display the names of the package which have sold less than the average number of copies
Who are the authors of the packages which have recovered more than double the development cost
Display the programmer names and the cheapest packages developed by them in each language
Display the language used by each programmer to develop the highest selling and lowest selling package
Who is the youngest male programmer born in 1965
Who is the oldest female programmer who joined in 1992.
In which year were the most number of programmers born.
In which month did most number of programmers join
In which language are most of the programmers proficient
Who are the male programmers earning below the average salary of female programmers.
Who are the female programmers earning more than the highest paid male programmer
Which language has been stated as the PROF by most of the programmers
Display the details of those who are drawing the same salary.
Display the details of the software developed by the male programmers earning more than 3000/-
Display the details of the packages developed in Pascal by the female programmers
Display the details of the programmers who joined before 1990
Display the details of the software developed in c by female programmers of Pragathi
Display the number of packages, number of copies sold and sales value of each programmer institute wise.
Display the details of the software developed in dbase by male programmers who belong to the institute in which most number of programmers studied
Display the details of the software developed by the male programmers born before 1965 and female programmers born after 1975
Display the details of the software that has developed in the language which is neither the first nor the second proficiency of the programmer
Display the details of the software developed by the male students of Sabhari
Display the names of the programmers who have not developed any packages
What is the total cost of the software developed by the programmers of apple
Display the names of the programmers who have not developed any packages’
Who are the programmers who have the same PROF2.
Display the total sales value of the software institute wise.
In which institute does the person who developed the costliest package studied.
Which language listed in PROF1 and PROF2 has not been used to develop any package
How much does the person who developed the highest selling package earn and what course did he / she undergo
How many months will it take for each programmer to recover the cost of the course under went
Which is the costliest package by a person with under 3 years experience
What is the average salary for those whose software sales is more than 50,000/-
How many packages were developed by students who studied in institute that charge the lowest courser fee
How many packages were developed by the person who developed the cheapest package, where did he / she studied.
How many packages were developed by the female programmers earning more than the highest paid male programmer
How many packages are developed by the most experienced programmers from BDPS.
List the programmers from the software table and the institutes they studied including those who did not develop and package
List each PROF with the number of programmers having that PROF and the number of the packages in that PROF.
List the programmer names from the programmer table and the no of packages each has developed.
