Write SQL queries with the following information: The schema of an enrollment da

Question

Write SQL queries with the following information:

The schema of an enrollment database for college students contains the following:

STUDENT(ssn, lname, fname, dob)

Primary key: {ssn}

COURSE(cid, year, semester, department, cnum)

Primary key: {cid, year, semester}

Enrolled(ssn, year, sem, date_enrolled)

Primary key: {ssn, cid, year, sem}

Foreign key 1: {ssn} refernces primary key of STUDENT

Foreign key 2: {cid, year, sem} refernces primary key of COURSE

Write the following queries:

1) Find full names of all students enrolled in CIS3400 (CIS is the department name, 3400 is the cnum)

2) Find full names of all students who are enrolled in more than one course this semester (Summer 2017, summer is the semester, 2017 is the year)

3) Interpret the following SQL statement and write concise sentence in English to describe the output of the stated query.

select lname, fname

from student

where dob in (

select top 1 dob

from (

select dob, count(dob) as someNum

from student

group by dob

order by count(dob) desc));

Explanation / Answer

Given schema is
STUDENT(ssn, lname, fname, dob)
Primary key: {ssn}

COURSE(cid, year, semester, department, cnum)
Primary key: {cid, year, semester}

Enrolled(ssn, year, sem, date_enrolled)
Primary key: {ssn, cid, year, sem}
Foreign key 1: {ssn} refernces primary key of STUDENT
Foreign key 2: {cid, year, sem} refernces primary key of COURSE

1) Find full names of all students enrolled in CIS3400 ?

SELECT S. lname, S.fname from Stundent S,Enrolled E where S.SSN=E.SSN and E.SSN='CIS3400 ';

2)Find full names of all students who are enrolled in more than one course this semester

SELECT S.lname,S.fname from Stundent S,Enrolled E,COURSE where S.SSN=E.SSN and C.cid=E.cid GROUP BY S.ssn HAVING COUNT(DISTINCT cid) > 2

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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