The schema of the database is provided below (keys are underlined, field types a

Question

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).
Q4.1. (2 points) Find the names and gpas of the students who are enrolled in 312.
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
Intelligence).
Q4.4. (4 points) Find the names of students who are enrolled in a course from both
the "Computer Sciences" and "Chemical Engineering" departments.
Q4.5. (5 points) How many students have more than one major? (Hint: requires a
nested query)
Q4.6. (5 points) Find the name(s) of the oldest first year student {year = 1} (Hint:
requires a nested query)

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 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). answers must not contain duplicates, but you should use Q4.1. (2 points) Find the names and gpas of the students who are enrolled in 312. 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 the name Artificial Artificial Intelligence courses ( 1.e. courses c containing 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

Please Note: According to the Chegg Answering Guidelines, I have answered the first 4 questions here. For receiving answer to the rest of the Questions, please Re-post for the other Questions.

Answers)

Q4.1.

select sname from student where sid in (

select sid from enroll where cno = 312

);

Q4.2.

select sname form student where age= max(age);

Q4.3.

SELECT student.sname, major.dname

FROM (((student

INNER JOIN major ON student.sid = major.sid)

INNER JOIN enroll ON major.sid = enroll.sid)

INNER JOIN course ON enroll.cno = course.cno)

where course.cname='Artificial

Intelligence';

Q4.4.

select sname from student where sid in(

select sid from enroll where cno in(

select cno from course where dname in ('Computer Sciences','Chemical Engineering')

)

);

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

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