project1part1.sql

DROP DATABASE IF EXISTS Scores;
CREATE DATABASE IF NOT EXISTS Scores;
SHOW DATABASES;

USE Scores;

CREATE Table IF NOT EXISTS Course(CNO INT Not Null, CNAME	VarChar(20)	Not Null, PRIMARY KEY(CNO));

CREATE Table IF NOT EXISTS Student(SNO INT Not Null, ADDRESS VarChar(20) Not Null, SNAME VARCHAR(100) NOT NULL,
                                    SEX CHAR, PRIMARY KEY (SNO));

CREATE Table IF NOT EXISTS Result(CNO INT Not Null, SNO INT Not Null, SCORE INT Not Null,
                                CONSTRAINT FOREIGN KEY (CNO) References COURSE (CNO), CONSTRAINT  FOREIGN KEY (SNO)
                                    References STUDENT (SNO), PRIMARY KEY(CNO, SNO)) ENGINE=InnoDB ;

INSERT INTO Course(CNO, CNAME)
Values (21, 'Computer Science'),
       (17, 'Chemistry'),
       (15, 'Physics'),
       (12, 'Mathematics I'),
       (13, 'Mathematics III'),
       (4, 'History'),
       (3, 'Geography'),
       (2, 'Biology');

SELECT * FROM Course;

INSERT INTO Student
Values
(31, 'L. Gatlin',	'Holcomb', 'F'),
(11, 'N. Park',	'Whitney', 'M'),
(22 ,'S. Conners',	'Holcomb', 'F'),
(24, 'D. Davisson',	'E. Quad', 'M'),
(20, 'I. Blake',	'Holcomb', 'M'),
(4, 'T. Smith',	'Cambridge',	'F'),
(1, 'N. J. Sloane',	'Whitney', 'F'),
(14, 'D. E. Knuth',	'Whitney', 'M'),
(6, 'J. Brown',	'Whitney', 'M'),
(2, 'M. Roberts',	'Cambridge', 'M'),
(33, 'U. Smith',	'Cambridge',	'F');

SELECT * FROM Student;

INSERT INTO Result
VALUES
(2,	20,	71),
(2,	31,	82),
(2,	14,	63),
(2,	2,	69),
(2,	24,	72),
(2,	33,	94),
(3,	20,	80),
(3,	14,	58),
(12, 24, 67),
(12, 4,	82),
(12, 14, 37),
(12, 2, 78),
(12, 33, 90),
(13,	33,	78),
(13,	4,	92),
(13,	24,	78),
(13,	6,	44),
(15,	33,	97),
(15,	4,	72),
(15,	11,	0),
(15,	14,	61),
(15,	6,	52),
(15,	24,	83);
INSERT INTO result VALUES(12, 6, 80);


SELECT * FROM Result;


DESCRIBE Result;

SELECT constraint_name, table_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'result';

SELECT constraint_name, table_name, column_name,
referenced_table_name ref_table, referenced_column_name ref_column
FROM information_schema.key_column_usage
WHERE table_name = 'result';

SELECT AVG(SCORE) FROM (SELECT R.CNO as CourseID, SNO as StudentID, CNAME as Course, SCORE
        FROM Result as R inner join Course as C where R.CNO=C.CNO) as RC where RC.Course='Physics';