CATALOG STUDENTS CNO CSCI130 Programming 1 CSCI230 Programming 2 CSCI360 Databas

Question

CATALOG STUDENTS CNO CSCI130 Programming 1 CSCI230 Programming 2 CSCI360 Database Management MINIT SID 123456789 Tom Brady 333445555 Jerry Seinfeld A 999999999 Doug Heffernan C ENROLLS FNAMELNAME COURSES SEC NOCNO SID 1 1 Fall 2016 Spr 2017 Spr 2017 l CSC1130 90 80 70 60 123456789 Fall 2016 1 CSCI230 90 80 70 60 123456789 Spr 2017 2 CSCI360 90 80 70 60 333445555 Fall 2016 333445555 Spr 2017 333445555 Spr 2017 2 2. Write SQL queries for the following (Your queries should work for any GRADEBOOK database state (not just for the given/current data)) (10) a. Retrieve the names of students enrolled in the ‘Database Management' class during the 'Spr 2017, term b. Retrieve the total number of students in the GRADEBOOK database c. Retrieve the last names of all students whose last name contains the pattern "fe". d. Retrieve the Sid values of students who have/had enrolled in 'CSCI130' and 'CSCI230 e. Retrieve the names of students who have not enrolled in any class

Explanation / Answer

If you have any doubts, please give me comment...

-- a)

SELECT FNAME, LNAME

FROM STUDENTS S, ENROLLS E, COURSES C, CATALOG CT

WHERE S.SID = E.SID AND E.SEC_NO = C.SEC_NO AND C.CNO = CT.CNO AND CTITLE = 'Database Management' AND C.TERM = 'Spr 2017';

-- b)

SELECT COUNT(*) AS no_of_students

FROM STUDENTS;

-- c)

SELECT LNAME

FROM STUDENTS

WHERE LNAME LIKE '%fe%';

-- d)

SELECT SID

FROM ENROLLS E, COURSES C

WHERE E.SEC_NO = C.SEC_NO AND CNO = 'CSCI130'

UNION

SELECT SID

FROM ENROLLS E, COURSES C

WHERE E.SEC_NO = C.SEC_NO AND CNO = 'CSCI230';

-- e)

SELECT FNAME, LNAME

FROM STUDENTS

WHERE SID NOT IN(

SELECT SID

FROM ENROLLS

);

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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