You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
81 lines
5.5 KiB
81 lines
5.5 KiB
SET FEEDBACK OFF
|
|
|
|
DROP TABLE STUDENT CASCADE CONSTRAINTS;
|
|
DROP TABLE EXAM CASCADE CONSTRAINTS;
|
|
|
|
CREATE TABLE STUDENT (
|
|
STUDENT_ID INT PRIMARY KEY,
|
|
LASTNAME VARCHAR(40) NOT NULL,
|
|
FIRSTNAME VARCHAR(40) NOT NULL,
|
|
GENDER VARCHAR(1) NOT NULL,
|
|
CONSTRAINT CHK_STUDENT_ID
|
|
CHECK (STUDENT_ID>=100000 AND STUDENT_ID<=999999),
|
|
CONSTRAINT CHK_GENDER
|
|
CHECK (GENDER='m' OR GENDER='f')
|
|
);
|
|
|
|
CREATE TABLE EXAM (
|
|
STUDENT_ID INT,
|
|
COURSE VARCHAR(40),
|
|
GRADE DECIMAL(2,1),
|
|
ATTEMPT INT,
|
|
CONSTRAINT PK PRIMARY KEY(STUDENT_ID, ATTEMPT, COURSE),
|
|
CONSTRAINT CHK_ATTEMPT CHECK (ATTEMPT IN (1,2,3)),
|
|
CONSTRAINT CHK_GRADE CHECK (
|
|
GRADE IN (1.0,1.3,1.7,2.0,2.3,2.7,3.0,3.3,3.7,4.0,5.0)
|
|
),
|
|
CONSTRAINT FK_STUDENT_ID FOREIGN KEY (STUDENT_ID)
|
|
REFERENCES STUDENT(STUDENT_ID) ON DELETE CASCADE
|
|
);
|
|
|
|
INSERT INTO STUDENT (STUDENT_ID, FIRSTNAME, LASTNAME, GENDER) VALUES (100000, 'Joffrey', 'Baratheon', 'm');
|
|
INSERT INTO STUDENT (STUDENT_ID, FIRSTNAME, LASTNAME, GENDER) VALUES (100001, 'Jorah', 'Mormont', 'm');
|
|
INSERT INTO STUDENT (STUDENT_ID, FIRSTNAME, LASTNAME, GENDER) VALUES (100002, 'Daenerys', 'Targaryen', 'f');
|
|
INSERT INTO STUDENT (STUDENT_ID, FIRSTNAME, LASTNAME, GENDER) VALUES (100003, 'Theon', 'Graufreud', 'm');
|
|
INSERT INTO STUDENT (STUDENT_ID, FIRSTNAME, LASTNAME, GENDER) VALUES (100004, 'Tyrion', 'Lennister', 'm');
|
|
INSERT INTO STUDENT (STUDENT_ID, FIRSTNAME, LASTNAME, GENDER) VALUES (100005, 'Jon', 'Snow', 'm');
|
|
INSERT INTO STUDENT (STUDENT_ID, FIRSTNAME, LASTNAME, GENDER) VALUES (100006, 'Arya', 'Stark', 'f');
|
|
INSERT INTO STUDENT (STUDENT_ID, FIRSTNAME, LASTNAME, GENDER) VALUES (100007, 'Jaime', 'Lennister', 'm');
|
|
INSERT INTO STUDENT (STUDENT_ID, FIRSTNAME, LASTNAME, GENDER) VALUES (100008, 'Brienne', 'of Tarth', 'f');
|
|
INSERT INTO STUDENT (STUDENT_ID, FIRSTNAME, LASTNAME, GENDER) VALUES (100009, 'Sansa', 'Stark', 'f');
|
|
INSERT INTO STUDENT (STUDENT_ID, FIRSTNAME, LASTNAME, GENDER) VALUES (100010, 'Petyr', 'Baelish', 'm');
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100000, 'Database Management Systems', 1, 5.0);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100001, 'Database Management Systems', 1, 5);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100001, 'Database Management Systems', 2, 3.3);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100001, 'Programming I', 1, 2.3);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100002, 'Database Management Systems', 1, 5);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100002, 'Database Management Systems', 2, 1.7);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100002, 'Programming I', 1, 4);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100003, 'Database Management Systems', 1, 2.3);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100003, 'Programming I', 1, 5);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100003, 'Programming I', 2, 3.3);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100004, 'Database Management Systems', 1, 1.0);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100004, 'Programming I', 1, 1.3);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100005, 'Database Management Systems', 1, 5);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100005, 'Database Management Systems', 2, 1.3);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100005, 'Programming I', 1, 5);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100005, 'Programming I', 2, 2);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100006, 'Database Management Systems', 1, 1);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100006, 'Programming I', 1, 1);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100007, 'Database Management Systems', 1, 2.3);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100007, 'Programming I', 1, 5);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100007, 'Programming I', 2, 1.7);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100008, 'Database Management Systems', 1, 5);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100008, 'Database Management Systems', 2, 1.3);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100008, 'Programming I', 1, 5);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100008, 'Programming I', 2, 5);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100008, 'Programming I', 3, 3.3);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100009, 'Database Management Systems', 1, 2.3);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100009, 'Programming I', 1, 1.3);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100010, 'Database Management Systems', 1, 5);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100010, 'Database Management Systems', 2, 2.3);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100010, 'Programming I', 1, 5);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100010, 'Programming I', 2, 5);
|
|
INSERT INTO EXAM (STUDENT_ID, COURSE, ATTEMPT, GRADE) VALUES (100010, 'Programming I', 3, 5);
|
|
|
|
SET FEEDBACK ON
|
|
|
|
-- c.)
|
|
SELECT S.STUDENT_ID, S.LASTNAME, E.COURSE FROM STUDENT S, EXAM E WHERE S.STUDENT_ID=E.STUDENT_ID AND ATTEMPT=3 AND GRADE=5;
|
|
-- d.)
|
|
SELECT S.GENDER, AVG(ATTEMPT) FROM STUDENT S, EXAM E WHERE S.STUDENT_ID=E.STUDENT_ID AND NOT GRADE=5 GROUP BY S.GENDER;
|