Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

This problem uses a of the database is provided below. Keys are underlined, field types are omitted; assume that appropriate foreign key constraints (for example,

image text in transcribed

This problem uses a of the database is provided below. Keys are underlined, field types are omitted; assume that appropriate foreign key constraints (for example, from prof into dept) are present in the full version of the schema: QL database with data about a university. The schema 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) We ask that you write exactly one SQL query for each question below We do not care if your answer contains duplicate rows or not. The answer to each question is worth 1 point. For each question, you should submit the SQL query (a) Print the name and department of each professor who has taught a course not from his/her department at least once. b) Print the sid and name of each student who has earned a 3.5 or higher grade in at least two different courses. (c) 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 d) 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 (e) 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

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

MFDBS 91 3rd Symposium On Mathematical Fundamentals Of Database And Knowledge Base Systems Rostock Germany May 6 9 1991

Authors: Bernhard Thalheim ,Janos Demetrovics ,Hans-Detlef Gerhardt

1991st Edition

3540540091, 978-3540540090

More Books

Students also viewed these Databases questions

Question

Define and describe skimming, pretexting, phishing, and pharming.

Answered: 1 week ago