课堂演示总

-- 课堂演示总.sql
-- 作者：IT专家
-- 时间：2021-07-01

-- 创建学生表
CREATE TABLE student (
    id INT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    age INT NOT NULL,
    gender CHAR(1) NOT NULL
);

-- 创建课程表
CREATE TABLE course (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    teacher VARCHAR(20) NOT NULL,
    credits INT NOT NULL
);

-- 创建选课表
CREATE TABLE sc (
    sid INT NOT NULL,
    cid INT NOT NULL,
    score INT NOT NULL,
    PRIMARY KEY(sid, cid)
);

-- 查询选课表中成绩大于60且学生年龄大于18的记录
SELECT s.name, c.name, sc.score
FROM student s, course c, sc
WHERE s.id=sc.sid AND c.id=sc.cid AND sc.score>60 AND s.age>18;