Schema of the database provided below: bold are primary keys student( sid , snam

Question

Schema of the database provided below: bold are primary keys

student(sid, sname, sex, age, year, gpa)

dept(dname, numphds)

prof(pname, dname)

course(cno, cname, dname)

major(dname, sid)

section(dname, cno, sectno, pname)

enroll(sid, grade, dname, cno, sectno)

Please write one sql query for the following 5 question:

Print the name and department of each professor who has taught a course not from his/her department at least once Print the sid and name of each student who has earned a 3.5 or higher grade in at least two different courses. Print the name and age of the student (s) with the highest GPA in their exact age group (i.e., the name and age of the student (s) with the highest GPA among all 15 year olds, the name and age of the student(s) with the highest GPA among all 16 year olds, and so on), for ages less than or equal to 18 For each department that both (a) has the substring "Engineering" in its name (e.g., "Electrical Engineering") and (b) has at least 2 students majoring in the department, print the name of the department and the average GPA of the students who major in the department Some courses are popular among students just because students enrolled in those courses usually get good grades. Print the department name, course number and course enrollment of each course C such that the following two conditions hold: (1) course C's enrollment is at least 3% larger than the average enrollment of the courses offered by C's department, and (2) course C's average grade is at least 3% larger than the average grade obtained by students in the courses offered by C's department. Assume that the enrollment of a course is the sum of the enrollment of all its sections. You can completely ignore any course that has no students enrolled in it

Explanation / Answer

Student:
sid       sname   sex       age       year   gpa
001       sam       m       17       2010   4
002       janani      f       18       2009   3.6
003       ram       m       16       2013   3
004       anna       f       17       2010   3.4
005       honey   f       16       2013   6

Dept:
dname       numphds
CSE           10
ECE           15
EEE           8
IT           10
Mech       5

Prof:
pname       dname
vinal       CSE
jam           IT
Rose       EEE
Jase       ECE
ami           CSE
srafs       Mech

Course:
cno           cname                   dname
1001   Information tech           IT
1002   Computer Engineering       CSE
1003   Electronic Engineering       EEE
1004   Communication Engineering   ECE
1005   Mechanical                   Mech
1006   Civi Engineering           civil
1007   Automobile Engineering   automobiles
1008   Mechanical                   Mech

Major:
sid       dname
001       CSEngineering
002       IT
003       EEEngineering
004       ECE
005       CSE

Section:
sectno       cno       dname   pname
S001       1001   IT       jam
S002       1002   CSE       vinal
S003       1003   EEE       Rose
S004       1004   ECE       Jase
S005       1005   Mech   srafs

Enroll:
sid       grade   cno       sectno       dname
001       B       1002   S002       CSE
002       C       1004   S004       ECE
003       D       1001   S001       IT
004       C       1003   S003       EEE
005       O       1005   S005       Mech

1.Print the name and depat of each professor who has taught a course not from his/her department at least one.
SELECT * FROM prof p
INNER JOIN course c
ON c.dname = p.dname;

Output:
pname       dname
vinal       CSE
jam           IT
Rose       EEE
Jase       ECE
ami           CSE
srafs       Mech

2.Print the sid and name of each student who has earned 3.5 or higher grade in at least 2 different couses.

SELECT sid, name FROM student s
INNER JOIN enroll e ON e.sid = s.sid
WHERE s.gpa >=3.5 or e.grade = 'O';

Output:
sid       name
001       sam     //4-gpa, B-grade
002       janani   //3.6-gpa, C-grade
005       honey   //6-gpa, O-grade

3. Print name and age of student with highest GPA in their exact age group for ages less than or eqal to 18.
SELECT name, age
FROM student
WHERE age <= 18
GROUP BY age
HAVING gpa > 3

output:
sname   age
sam       17   //4-gpa
janani   18   //3.6
anna   17 //3.4-gpa
honey   16   //6-gpa

4.print depart name and gpa for Each dept has substring "engineering" and has 2 student major in the dept.
SELECT m.dname, AVG(s.gpa) as average_gpa
FROM major m
INNER JOIN student s
ON m.sid = s.sid
WHERE m.dname like '%engineering%';

sample output:
dname            average_gpa
CSEngineering   4
EEEngineering   3

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.