ASSIGNMENT SHEET on RELATIONAL ALGEBRA AND SQL Queries

                                   ASSIGNMENT SHEET NO: 1

ASSIGNMENT SHEET on RELATIONAL ALGEBRA AND SQL

Instructions to the students:

 

 

  1. Answer the following 110  queries in SQL using the sample database tables given below.
  1. 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.
  1. Also you have to specify the DDL description instructions used in creating the database has to be submitted in a separate file. ( SQL scripts)
  1. Answer the questions in relational algebra wherever 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,  just give a comment   NO Equivalent Relational algebraic query exists for the question No. ____.
  2. The evaluation mode for this question will be as follows:

i)                   on Feb 16th  and 17th, every student will be called for showing the execution of five queries randomly from this list.

ii)                 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

DEVELOPIN

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

  1. Find out the selling cost av….. Packages developed in pascal.
  2. Display the names ages of all programmers
  3. Display the names of those who have done the dap course.
  4. Display the names and date of births of all programmers born in january.
  5. What is the highest number of copies sold by a  package
  6. Display the lowest course fee
  7. How many programmers done the pgdca course
  8. How much revenue has been earned thru sales of packages developed in c.
  9. Display the details of the software developed by ramesh.
  10. How may programmers studied at sabhari
  11. Display details of packages whose sales crossed the 2000 mark
  12. Find out the number of copies which should be sold in order to
  13. Display the details of packages for which developments cost have been recovered.
  14. What is the cost of the costliest software developed in basic
  15. How many packages developed in dbase
  16. How many programmers studies in pragathi
  17. How many programmers paid 5000 to 10000 for their studies.
  18. What is avg course fee
  19. Display the detail the programmers knowing c
  20. How many programmers know either cobol or pascal
  21. How many programmers don’t know pascal and c
  22. How old is the oldest male programmers
  23. Calculate the experience in years for each programmers and. Display along with the names in descending order.
  24. Who are the programmers who celebrate their birthdays during the current month.
  25. How many female programmers are there
  26. What are the languages by male programmers
  27. What is the avg salary
  28. How many people draw salary 2000 to 4000
  29. Display the details of those who don’t know clipper cobol or pascal
  30. Display the cost of package developed by each programmer
  31. Display the sales values of the packages developed by the each programmer
  32. Display the number of packages sold by each programmer.
  33. Display the sales cost of the packages developed by each programmer
  34. Display the sales cost cf the packages developed by each programmer language wise
  35. Display each language name with avg development cost avg selling cost and avg price per copy.
  36. Display each programmers name costliest and cheapes packages developed by him or her .
  37. Display each institute name with number of courses average cost per course.
  38. Display each institute name with number of students.
  39. Display names of male and female programmers name and sex also
  40. Display the name of programmers and their packages
  41. Display the number of packages in each languages except c and c++.
  42. Display the number of packages in each language for which development cost is less than 1000.
  43. Display the avg difference between scost and dcost for each package.
  44. Display the total scost, dcost and amount to be recovered for each programmer for those whose dcost has not yet been recovered.
  45. Display highest losest and average salaries for those earning more than 2000.
  46. Who is the highest paid in c programmers.
  47. Who is the highest paid female cobol programmer
  48. Display the names of the highest paid programmer for each language
  49. Who is the least experienced programmer
  50. Who is the most experienced male programmer knowing pascal
  51. Which language is known by only one programmer.
  52. Who is the above programmer referred in 51.
  53. Who is the youngest programmer knowing dbase
  54. Which female programmer earning more than 3000 does not know c. C++ oracle or dbase.
  55. Which institute has most number of students
  56. What is the costliest course
  57. Which course has been done by the most of the  students
  58. Which course has been done by the most of the student
  59. Which institute conducts costiest course
  60. Display the name of the institute and course which has below avg course fee
  61. Display the names of the courses whose fees are within 1000 (+or) of the average fee.
  62. Which package has the highest development cost
  63. Which course has below avg number of students
  64. Which package has the lowest selling cost.
  65. Who developed the package that has sold the least number of copies
  66. Which language has used to develop the package which has the highest sales amount
  67. How many copies of package that has the least difference between development and selling cost were sold
  68. Which is the costliest package developed in pascal
  69. Which language was used to develop the most number of packages
  70. Which programmer has developed the highest number of packages
  71. Display the names of the package which have sold less than the avg number of copies
  72. Who are the authors of the packages which have recovered more than double the development cost
  73. Display the programmer names and the cheapest packages developed by them in each language
  74. Display the language used by each programmer to develop the highest selling and lowest selling package
  75. Who is the youngest male programmer born in 1965
  76. Who is the oldest female programmer who joined in 1992.
  77. In which year were the most number of  programmers born.
  78. In which month did most number of programmers join
  79. In which language are most of the programmers proficient
  80. Who are the male programmers earning below the avg salary of female programmers.
  81. Who are the female programmers earning more than the highest paid male programmer
  82. Which language has been stated as the prof  by most of the programmers
  83. Display the details of those who are drawing the same salary.
  84. Display the details of the  software developed by the male programmers earning more than 3000/-
  85. Display  the details of the packages developed in pascal by the female programmers
  86. Display the details of the programmers who joined before 1990
  87. Display the details of the software developed in c by female programmers of pragathi
  88. Display the number of packages no. Of copies sold and sales value of each programmer institute wise.
  89. Display the details of the software developed in dbase by male programmers who belong to the institute in which most number of programmers studied
  90. Display the details of the software developed by the male programmers  born before 1965 and female programmers born after 1975
  91. Display the details of the software that has developed in the language which is neither the first nor the second proficiency of the programmer
  92. Display the details of the software developed by the male students of sabhari
  93. Display the names of the programmers who have not developed any packages
  94. What is the total cost of the software developed by the programmers of apple
  95. Display the names of the programmers who have not developed any packages’
  96. Who are the programmers who have the same prof2.
  97. Display the total sales value of the software institute wise.
  98. In which institute does the person who developed the costliest package studied.
  99. Which language list  d in prof1, prof2 has not been used to develop any package
  100. How much does the person who developed the highest selling package earn and what course did he / she undergo
  101. How many months will it take for each programmer to recover the cost of the course under went
  102. Which is the costliest package by a person with under 3 years experience
  103. What is the avg salary for those whose software sales is more than 50,000/-
  104. How many packages were developed by students who studied in instt that charge the lowest courser fee
  105. How many packages were developed by the person who developed the cheapest package, where did he / she studied.
  106. How many packages were developed by the female programmers earning more than the highest paid male programmer
  107. How many packages are developed by the most experienced programmers from bdps.
  108. List the programmers from the software table and the institutes they studied including those who did not develop and package
  109. List each prof with the number of programmers having that prof and the number of the packages in that prof.
  110. List the programmer names from the programmer table and the no of packages each has developed.
Advertisements

One Response to ASSIGNMENT SHEET on RELATIONAL ALGEBRA AND SQL Queries

  1. Gyandeep Sahu says:

    where can we find the answers?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s