student(sid, sname, sex, age, year, gpa) dept(dname, numphds) prof(pname, dname)

Question

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)

Q4. SQL: The School DB [21 points] The schema of the database is provided below (keys are underlined, field types are omitted): 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) In this assignment, you will only deal with querying part of SQL. You are NOT allowed to tamper with (change the contents of) the database, i.e., CREATE, INSERT, DELETE, ALTER, UPDATE etc Write SQL queries that answer the questions below (one query per question). The query answers must not contain duplicates, but you should use the SQL keyword distinct only when necessary. For this question, creation of temporary tables is NOT allowed, i.e., for each question you have to write exactly one SQL statement (possible using nested SQL). (2 points) Find the names and gpas of the students who are enrolled in 312. Q4.1. Q4.2. (2 points) Find the name of the oldest student. Q4.3. (3 points) Find the names and majors of students who are taking one of the Artificial Intelligence courses (i.e. courses containing the name Artificial Q4.4. (4 points) Find the names of students who are enrolled in a course from both Q4.5. (5 points) How many students have more than one major? (Hint: requires a Q4.6. (5 points) Find the name(s) of the oldest first year student year 1 (Hint: Intelligence) the "Computer Sciences" and "Chemical Engineering" departments nested query) requires a nested query)

Explanation / Answer

1. select sname, gpa from student, enroll where student.sid=enroll.sid and cno=312;

2. select name from student where age = (select max(age) from student);

3. select student.sname, course.cname from student, enroll, course where student.sid = enroll.sid and enroll.cno = couse.cno and cname like '%Artificial Intelligent%';

4. (select name from student, enroll, course where student.sid = enroll.sid and enroll.cno = course.cno and course.dname = "Computer Sciences") intersect (select name from student, enroll, course where student.sid = enroll.sid and enroll.cno = course.cno and course.dname = "Chemical Engineering");

5. select count(*) from stduent, major where sid in (select count(*), sid from student, major where student.sid = majo.sid);

6. select max(age), sname from student where sid in (select * from student where year = 1)

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

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