Consider the database with the following schema: STUDENT(sid, name, address, isI
ID: 3931870 • Letter: C
Question
Consider the database with the following schema:
STUDENT(sid, name, address, isInternational)
COURSE(crsid, name, dept)
SECTION(secid, crsid, semester)
TOOK(secid, sid, score)
ATHLETE(sid, sport)
where
1. sid uniquely identifies students
2. isInternational is a Boolean indicating whether the student is international or not (0 stands for false, 1 stands for true)
3. crsid uniquely identifies courses
4. secid uniquely identifies sections
Given:
PRAGMA foreign_keys = true;
CREATE TABLE STUDENT (
sid INTEGER,
name VARCHAR(20),
address VARCHAR(50),
isInternational BOOLEAN,
PRIMARY KEY (sid)
);
CREATE TABLE COURSE (
crsid VARCHAR(7),
cname VARCHAR(30),
dept VARCHAR(10),
PRIMARY KEY (crsid)
);
CREATE TABLE SECTION (
secid INTEGER,
crsid VARCHAR(7),
semester CHAR(5),
PRIMARY KEY (secid),
FOREIGN KEY (crsid) REFERENCES COURSE(crsid)
);
CREATE TABLE TOOK (
secid INTEGER,
sid INTEGER,
score INTEGER,
PRIMARY KEY (secid,sid),
FOREIGN KEY (secid) REFERENCES SECTION(secid),
FOREIGN KEY (sid) REFERENCES STUDENT(sid)
);
CREATE TABLE ATHLETE (
sid INTEGER,
sport VARCHAR(20),
PRIMARY KEY (sid,sport),
FOREIGN KEY (sid) REFERENCES STUDENT(sid)
);
INSERT INTO STUDENT VALUES (1,'Alice','123 Bluebird Lane', 0);
INSERT INTO STUDENT VALUES (2,'Bob','123 Bluebird Lane', 0);
INSERT INTO STUDENT VALUES (3,'Carol','123 Bluebird Lane', 1);
INSERT INTO STUDENT VALUES (4,'Dave','123 Bluebird Lane', 0);
INSERT INTO STUDENT VALUES (5,'Eve','123 Bluebird Lane', 0);
INSERT INTO STUDENT VALUES (6,'Frank', '123 Bluebird Lane', 0);
INSERT INTO STUDENT VALUES (7,'Gina', '123 Bluebird Lane', 1);
INSERT INTO STUDENT VALUES (8,'Henry', '123 Bluebird Lane', 0);
INSERT INTO STUDENT VALUES (9,'Isaac', '123 Bluebird Lane', 1);
INSERT INTO COURSE VALUES('CS101','Intro to CS','CompSci');
INSERT INTO COURSE VALUES('CS102','Data Structures','CompSci');
INSERT INTO COURSE VALUES('CS201','Databases','CompSci');
INSERT INTO COURSE VALUES('MA102','Calculus','Math');
INSERT INTO COURSE VALUES('MA201','Discrete Mathematics','Math');
INSERT INTO COURSE VALUES('PHY101','Physics I','Physics');
INSERT INTO SECTION VALUES(10,'CS101','F2008');
INSERT INTO SECTION VALUES(20,'CS101','F2009');
INSERT INTO SECTION VALUES(30,'CS101','F2010');
INSERT INTO SECTION VALUES(40,'CS102','F2008');
INSERT INTO SECTION VALUES(50,'CS102','F2009');
INSERT INTO SECTION VALUES(60,'CS201','F2008');
INSERT INTO SECTION VALUES(70,'CS201','F2010');
INSERT INTO SECTION VALUES(80,'MA102','F2008');
INSERT INTO SECTION VALUES(90,'MA102','F2009');
INSERT INTO SECTION VALUES(100,'MA201','F2008');
INSERT INTO SECTION VALUES(110,'MA201','F2009');
INSERT INTO SECTION VALUES(120,'PHY101','F2007');
INSERT INTO SECTION VALUES(130,'PHY101','F2008');
INSERT INTO SECTION VALUES(140,'PHY101','F2009');
INSERT INTO SECTION VALUES(150,'PHY101','F2010');
INSERT INTO TOOK VALUES(10,1,85);
INSERT INTO TOOK VALUES(10,2,80);
INSERT INTO TOOK VALUES(10,3,65);
INSERT INTO TOOK VALUES(10,4,45);
INSERT INTO TOOK VALUES(20,4,55);
INSERT INTO TOOK VALUES(20,5,100);
INSERT INTO TOOK VALUES(30,7,90);
INSERT INTO TOOK VALUES(30,8,95);
INSERT INTO TOOK VALUES(30,1,45);
INSERT INTO TOOK VALUES(40,2,55);
INSERT INTO TOOK VALUES(40,3,65);
INSERT INTO TOOK VALUES(40,4,75);
INSERT INTO TOOK VALUES(50,5,25);
INSERT INTO TOOK VALUES(50,6,90);
INSERT INTO TOOK VALUES(50,7,90);
INSERT INTO TOOK VALUES(60,1,55);
INSERT INTO TOOK VALUES(60,2,65);
INSERT INTO TOOK VALUES(70,3,45);
INSERT INTO TOOK VALUES(70,4,75);
INSERT INTO TOOK VALUES(70,5,95);
INSERT INTO TOOK VALUES(70,2,45);
INSERT INTO TOOK VALUES(80,6,95);
INSERT INTO TOOK VALUES(80,7,95);
INSERT INTO TOOK VALUES(80,8,35);
INSERT INTO TOOK VALUES(90,1,85);
INSERT INTO TOOK VALUES(90,2,95);
INSERT INTO TOOK VALUES(90,3,75);
INSERT INTO TOOK VALUES(100,2,90);
INSERT INTO TOOK VALUES(100,4,95);
INSERT INTO TOOK VALUES(100,5,55);
INSERT INTO TOOK VALUES(100,6,85);
INSERT INTO TOOK VALUES(110,7,95);
INSERT INTO TOOK VALUES(110,8,25);
INSERT INTO TOOK VALUES(110,1,55);
INSERT INTO TOOK VALUES(120,2,75);
INSERT INTO TOOK VALUES(120,3,95);
INSERT INTO TOOK VALUES(120,4,45);
INSERT INTO TOOK VALUES(130,2,85);
INSERT INTO TOOK VALUES(130,5,95);
INSERT INTO TOOK VALUES(130,6,85);
INSERT INTO TOOK VALUES(130,7,95);
INSERT INTO TOOK VALUES(140,8,85);
INSERT INTO TOOK VALUES(140,1,45);
INSERT INTO TOOK VALUES(140,2,95);
INSERT INTO TOOK VALUES(150,3,85);
INSERT INTO TOOK VALUES(150,4,100);
INSERT INTO TOOK VALUES(150,5,5);
INSERT INTO ATHLETE VALUES(1,'Football');
INSERT INTO ATHLETE VALUES(1,'Figure Skating');
INSERT INTO ATHLETE VALUES(3,'Baseball');
INSERT INTO ATHLETE VALUES(6,'Volleyball');
Question: Create a view V3 that displays all sections with at least 2 students with a score of 80 or higher. Print the secid and number of students with a score of 80 or higher. (V3 is simply the name of the view that is being created.)
Explanation / Answer
VIEW CREATION
CREATE VIEW V3 AS
SELECT SEC.SECID,
S.NAME,
C.CNAME,
C.DEPT,
SEC.SEMESTER,
T.SCORE
FROM STUDENT S,
COURSE C,
SECTION SEC,
TOOK T
WHERE T.SCORE>=80
AND sec.crsid = c.crsid
AND t.secid = sec.secid
AND t.sid = s.sid
GROUP BY SEC.SECID,
S.NAME,
C.CNAME,
C.DEPT,
SEC.SEMESTER,
T.SCORE
ORDER BY SEC.SECID;
VIEW CREATED
TO Print the secid and number of students with a score of 80 or higher
SELECT V.SECID,COUNT(1) FROM V3 V WHERE SCORE>=80 GROUP BY V.SECID HAVING COUNT(1)>=2 ORDER BY V.SECID;
OUTPUT
SECID COUNT(1)
---------------------- ----------------------
10 2
30 2
50 2
80 2
90 2
100 3
130 4
140 2
150 2
9 rows selected