MySQL

CREATE SCHEMA TEST1;
USE TEST1;
CREATE TABLE Student(
Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20)UNIQUE,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);

CREATE TABLE Course(
Cno CHAR(4)PRIMARY KEY,
Cname CHAR(40)NOT NULL,
Cpno CHAR(4),
Ccredit SMALLINT,
FOREIGN KEY(Cpno)REFERENCES Course(Cno)
);

CREATE TABLE SC(

Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY (Sno,Cno),
FOREIGN KEY(Sno)REFERENCES Student(Sno),
FOREIGN KEY(Cno)REFERENCES Course(Cno));

INSERT INTO Student VALUES
('202300001','蔡徐坤','男',20,'CS'),
('202300002','谷爱凌','女',11,'CS'),
('202300003','丁真' ,'男',23,'MA'),
('202300004','何同学','男',19,'IS');

INSERT INTO Course VALUES
('1','高等数学',NULL,4),
('2','C语言',NULL,2),
('3','数字电路',NULL,4),
('4','模拟电路',NULL,3),
('5','STM32',NULL,1),
('6','汇编语言',NULL,5),
('7','数据结构',NULL,3);

INSERT INTO sc VALUES
('202300001','1',92),
('202300002','2',85),
('202300003','3',88),
('202300004','2',90),
('202300005','3',80);



ALTER TABLE Student MODIFY COLUMN Sage INT;
ALTER TABLE Course ADD UNIQUE(Cname);

UPDATE Course SET Cpno='5'WHERE Cno='1';
UPDATE Course SET Cpno='1'WHERE Cno='3';
UPDATE Course SET Cpno='6'WHERE Cno='4';
UPDATE Course SET Cpno='7'WHERE Cno='5';

ALTER TABLE Student ADD S_enterance DATE;


CREATE UNIQUE INDEX stusno ON student(sno);
SHOW INDEX FROM student;

CREATE UNIQUE INDEX coucno ON course (cno);
SHOW INDEX FROM course;

CREATE UNIQUE INDEX scno ON sc(sno ASC,cno DESC);
SHOW INDEX FROM sc;

SELECT Sno,Sname FROM student;--3.16
SELECT Sname,Sno,Sdept FROM Student;--3.17
SELECT *FROM student;--3.18
SELECT  Sno,Sname,Ssex,Sage,Sdept FROM Student;--3.18
SELECT Sname,2014-Sage FROM student;--3.19
SELECT Sname,'Year of Birth:',2014-Sage,LOWER(Sdept)FROM student;--3.20
SELECT DISTINCT Sno FROM sc;--3.21
SELECT Sname FROM Student WHERE Sdept='CS';--3.22
SELECT Sname,Sage FROM Student WHERE Sage<20;--3.23
SELECT DISTINCT Sno FROM SC WHERE Grade<60;--3.24
SELECT Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23;--3.25
SELECT Sname,Sdept,Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23;--3.26
SELECT Sname,Sage FROM student WHERE Sdept IN('CS','MA','IS');--3.27
SELECT Sname,Ssex FROM Student WHERE Sdept NOT IN('CS','MA','IS');--3.28
SELECT *FROM Student WHERE Sno LIKE 202300001;--3.29
SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE'谷%'; --3.30
SELECT Sno,Grade FROM SC WHERE Cno='3'ORDER BY Grade DESC; --3.39
SELECT COUNT(*)FROM Student;--3.41
SELECT AVG (Grade) FROM SC WHERE Cno='1';--3.43
SELECT MAX(Grade)FROM SC WHERE Cno='1';--3.44
SELECT Sno,AVG(Grade)FROM SC GROUP BY Sno HAVING AVG(Grade)>=90;--3.48