数据库sql语句

guhusf

-- 学生信息表（学号，姓名，性别，年龄，所在系号，班级）Student（Sno, Sname, Ssex, Sage, Sdept, Class）
-- 教师信息表（教师号，姓名，性别，所在系号）Teacher（Tno, Tname, Tsex, Tdept）
-- 系信息表（系号，系名，系主任教师号）Dept(Dno, Dname, Tno)
-- 课程信息表（课号，课名，学分，前序课程号）Course(Cno, Cname, Credit, PreCno)
-- 教学任务表（课程号，教师号，学期，年度）T_Task（Cno, Tno, Term, Year）
-- 学生选课表（学期，年度，学号，课程号，教师号，是否必修, 平时成绩，期末成绩）SC（Term, Year，Sno, Cno, Tno, isCC, UGrade, Grade）


-- （1）查询年龄在18岁到22岁之间的学生所在的班级名，查询结果中不包括重复班级名；
SELECT DISTINCT Class FROM student WHERE Sage BETWEEN 18 AND 22;

-- （2）查询所有女生的学号、姓名和出生年份；
SELECT Sno,Sname,2022-Sage birthday FROM student WHERE Ssex = "女";

-- （3）查询“计算机”系学生的最小年龄和最大年龄；
SELECT MAX(Sage),MIN(Sage) FROM student;

-- （4）查询姓名为四个字的学生的全部学生信息表中信息；
SELECT * FROM student HAVING CHAR_LENGTH(Sname) = 4;

-- （5）查询“计算机”系学生中姓“刘”的学生的学号、姓名和班级；
SELECT Sno,Sname,Class from student WHERE Sdep = "计算机" AND Sname LIKE "刘%";

-- （6）查询前序课程为“离散数学”的课程名称及学分；
SELECT FIRST.Cname,SECOND.Credit from course FIRST, course SECOND WHERE  FIRST.PreCno = SECOND.Cno and SECOND.Cname = "离散数学";
SELECT FIRST.Cname,SECOND.Credit from course FIRST, course.SECOND WHERE  FIRST.PreCno = SECOND.Cno and SECOND.Cname='lisanshuxue';
-- （7）查询有平时成绩但是没有期末成绩的学生的学号和姓名；
SELECT Sno, Sname FROM student WHERE Sno IN (SELECT Sno FROM sc WHERE sc.Ugrade IS NOT NULL AND sc.Grade IS NULL);

-- （8）查询“计算2201”班学生“数据库原理”课程的平均成绩；
SELECT AVG(Grade) FROM sc WHERE Sno IN (SELECT Sno FROM student WHERE Class = "计算2201") AND Cno IN (SELECT Cno FROM course WHERE Cname = "数据库原理");

-- （9）查询“数据库原理”课程的学生的学号、姓名、平时成绩和期末成绩，按照期末成绩由多到少排序，期末成绩相同的依据平时成绩由少到多排序；
SELECT student.Sno, student.Sname, sub.Ugrade, sub.Grade
FROM student, (SELECT * FROM sc WHERE Cno IN (SELECT Cno FROM course WHERE Cname = "数据库原理")) AS sub
WHERE sub.Sno = student.Sno
ORDER BY sub.Grade DESC, sub.Ugrade ASC;

-- （10）查询“数据库原理”课程期末成绩低于平均成绩的学生的学号、姓名和所在系名；
SELECT Sno, Sname, Sdep
from
(SELECT student.*, sub.Grade
FROM student, (SELECT * FROM sc WHERE Cno IN (SELECT Cno FROM course WHERE Cname = "数据库原理")) AS sub
WHERE sub.Sno = student.Sno) as sub
WHERE sub.Grade < (SELECT AVG(sc.Grade) FROM sc WHERE sc.Grade IN (SELECT Grade FROM sc WHERE Cno IN (SELECT Cno FROM course WHERE Cname = "数据库原理"))
);

-- （11）查询选修了本系老师开设课程的学生学号、姓名；
SELECT student.Sno, student.Sname, teacher.Tno FROM student, teacher WHERE (student.Sno, teacher.Tno) IN (SELECT sc.Sno, sc.Tno FROM sc) AND student.Sdep = teacher.Tdep;

-- （12）查询必修课人数超过3人的课程编号；
SELECT sc.Cno FROM sc WHERE sc.isCC="是" GROUP BY sc.Cno HAVING COUNT(Cno) > 2;

-- （13）查询“计算机”系所有不及格的学生姓名和课程名；
SELECT student.Sname, course.Cname from student, course WHERE student.Sdep="计算机"
AND (student.Sno, course.Cno) IN (SELECT sc.Sno, sc.Cno from sc WHERE sc.Grade < 60);

-- （14）在“课程信息表”上给“课名”建立一个唯一索引，索引名自定；
CREATE UNIQUE INDEX courcn ON course(Cname);

-- （15）创建一个新表CCAG (SNO, CCAVGGRADE)，用来记录每个学生必修课的平均成绩，其中SNO是主码，并且也是外码，参照Student表的Sno; SNO 为字符型，长度为9，CCAVGGRADE为整型；
CREATE TABLE CCAG
(Sno CHAR(9),
CCAVGGRADE INT,
PRIMARY KEY (Sno),
FOREIGN KEY (Sno) REFERENCES student(Sno)
);

-- （16）将（15）中创建的CCAG中的CCAVGGRADE修改为FLOAT类型；
ALTER TABLE ccag MODIFY COLUMN CCAVGGRADE FLOAT;

-- （17）计算每个学生必修课的平均成绩，插入到CCAG中；
INSERT INTO ccag(Sno, CCAVGGRADE) SELECT sc.Sno, AVG(sc.Grade) FROM sc GROUP BY sc.Sno ORDER BY sc.Sno;

-- （18）将CCAG中平均成绩为0的CCAVGGRADE改为NULL；
UPDATE ccag
SET CCAVGGRADE=NULL
WHERE CCAVGGRADE=0;

-- （19）删除CCAG中平均成绩为NULL的记录；
DELETE FROM ccag
WHERE CCAVGGRADE IS NULL;

-- （20）利用Student和CCAG，查询每个学生必修课的平均成绩，输出列中包括学号、姓名、必修课平均成绩，要求查询结果中包含所有学生，即使该学生没有必修课的平均成绩；
SELECT student.Sno, student.Sname, ccag.CCAVGGRADE FROM student LEFT OUTER JOIN ccag ON (student.Sno=ccag.Sno);

-- （21）创建视图，查询CCAG中，每个系有多少名学生的平均成绩小于70分，查询结果包括系名，人数，系主任姓名。
CREATE VIEW less_ccag AS
SELECT student.Sdep, ct.COUNT, dept.Tno from student, dept left OUTER JOIN (SELECT student.Sdep,COUNT(student.Sdep) COUNT from student GROUP BY student.Sdep) AS ct on (dept.Dname=ct.Sdep)
WHERE student.Sno IN (SELECT ccag.Sno from ccag WHERE ccag.CCAVGGRADE < 70) GROUP BY student.Sdep;

-- （22）为 Task表添加用户自定义约束条件；需要添加的约束条件有：年度（year）数据必须形如“2022-2023”、“2023-2024”，学期（term）数据必须为“春季学期”或“秋季学期”；
ALTER TABLE task ADD CONSTRAINT ch_yt CHECK (Year LIKE "%-%" AND term IN ("春季学期", "秋季学期"));

-- （23） 写一条SQL语句，向Task表中，插入一条不违背该表所有约束条件的记录；
INSERT INTO task(Cno, Tno, Term, Year) VALUES ("6", "电路", "秋季学期", "2022-2023");

-- （24） 创建一个新表，称为年度学期课程统计表（课程号，学期，年度，选修人数）YTC（Cno,Term, Year, CountStu），并根据学生选修情况和开课情况，填写该表，记录每学年每学期每门课的选课学生总人数。
CREATE TABLE YTC
(
    Cno CHAR(9) PRIMARY KEY,
    term CHAR(9),
    Year CHAR(9),
    CountStu INT
);
INSERT INTO ytc(Cno,Term, Year, CountStu)
SELECT task.Cno, task.Term, task.Year, COUNT(sc.Cno) FROM task, sc WHERE sc.Cno IN (SELECT task.Cno) GROUP BY sc.Cno;

-- （25） 基于数据库中的基本表，创建一个视图，视图中，能显示一个学生的学号，姓名，所属系，选修课程和该课程的期末成绩；
CREATE VIEW info AS
SELECT student.Sno, student.Sname, student.Sdep, sc.Cno, sc.Grade FROM student, sc WHERE student.Sno=sc.Sno ORDER BY student.Sno;

-- （26） 创建一个触发器，当某个学生选修、退选、改选某门课程后，在YTC表中，这门课的学生人数对应发生变化；
CREATE TRIGGER trig
AFTER UPDATE ON sc
FOR EACH ROW
    INSERT INTO ytc
    SELECT task.Cno, task.Term, task.Year, COUNT(sc.Cno) FROM task, sc WHERE sc.Cno IN (SELECT task.Cno) GROUP BY sc;
   
DROP TRIGGER trig ON sc;

-- （27） 创建一个触发器，能实现，SC插入某条记录时，根据系统时间自动填写年度和学期信息；每学年学期以当年9.1日和3.1日为学期第一天。


-- （28） 请查询统计每个学生学分总数；输出学号，姓名，总学分；注：（平时成绩+期末成绩）>=60即获得该门课的学分，否则得0学分），按学号升序排列。


-- （29） 统计每个老师在每学年讲授的课程，输出列表数据格式为：年度，教师号，教师姓名，课程号，课程名。
SELECT ytc.`Year`, teacher.Tno, teacher.Tname, sc.Cno, course.Cname FROM teacher, sc, course, ytc
WHERE ytc.Cno=sc.Cno AND teacher.Tno=sc.Tno
GROUP BY teacher.Tno
ORDER BY teacher.Tno;