SQL平时上机题汇总

[TOC]

第一次

建表

CREATE TABLE DEPT (
DEPTNO      INT,
DNAME       VARCHAR(14),
LOC         VARCHAR(13),
-- 名为 PK_DEPT的主键
CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO)
);

CREATE TABLE BONUS (
ENAME       VARCHAR(10),
JOB         VARCHAR(9),
SAL         INT,
COMM        INT
);

CREATE TABLE SALGRADE (
GRADE       INT,
LOSAL       INT,
HISAL       INT
);

CREATE TABLE EMP (
EMPNO       INT,
ENAME       VARCHAR(10),
JOB         VARCHAR(9),
MGR         INT,
HIREDATE    DATETIME,
SAL         FLOAT,
COMM        FLOAT,
DEPTNO      INT,
-- 名为 PK_EMP 的主键，名为FK_DEPTNO指向表DEPT的DEPTNO的外键
CONSTRAINT PK_EMP PRIMARY KEY (EMPNO),
CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)
);

题目

将所有员工的工资上浮10%.然后查询员工姓名、薪水、补助。

（emp.sal为工资，emp.comm为补助）

1 2	UPDATE TABLE emp SET sal=sal*0.1; SELECT ename, sal, comm FROM emp;

查看EMP表中部门号为10的员工的姓名，职位，参加工作时间，工资。
SELECT ename, job, hiredate, sal FROM emp WHERE DEPTNO = 10;
查所有已有的职位，要求去除重复项。
SELECT DISTINCT job FROM emp;
计算每个员工的年薪，并取列名为Salary of Year（emp.sal为员工的月薪），要求输出员工姓名，年薪。

SELECT e.ename, e.sal*12 AS 'Salary of Year' FROM emp as e;

查询每个员工每个月拿到的总金额（emp.sal为工资，emp.comm为补助）。（提示：isnull（ex1,ex2）表示如果ex1为空则返回ex2）

– MySQL这里是 IFNULL(expr1,expr2)
SELECT e.sal + IFNULL(e.comm,0) AS ALL_Sal FROM emp AS e;

显示职位是主管（manager）的员工的姓名，工资。

SELECT ename, sal FROM emp WHERE JOB='manager';

显示第3个字符为大写O的所有员工的姓名及工资。

SELECT ename, sal from emp WHERE ENAME LIKE '__O%';

显示职位为销售员（SALESMAN）或主管（MANAGER）的员工的姓名，工资，职位。

SELECT ename, sal, job from emp WHERE job in ('salesman','manager');

显示所有没有佣金的员工的姓名。

SELECT ename from emp WHERE COMM IS NULL;

显示有佣金的员工的姓名，工资，补助。

SELECT ename, sal ,comm FROM emp WHERE comm is NOT NULL;

排序显示所有员工的姓名，工资（按工资降序方式）。

SELECT ename, sal FROM emp ORDER BY sal DESC;

显示员工的最高工资和最低工资。

SELECT MAX(sal) max_sal, MIN(sal) min_sal FROM emp;

显示所有员工的平均工资和总计工资

SELECT AVG(sal), SUM(sal) FROM emp;

显示佣金在员工中的发放比例、即有多少比例的员工有佣金。（此题需注意两个问题：1.select语句中进行除法如何保留小数点后数据。2.count函数如何处理null型数据。

– 如何保留小数点后位数？
– ROUND(num，小数点位数n) 会自动四舍五入
– TRUNCATE(num,n)，不会四舍五入
– n为负数的话，会截取整数部位

SELECT round(count(comm)/COUNT(*),8) from emp;

SELECT ROUND(123.1235,3);
# 123.124
SELECT TRUNCATE(123.1235,-2);
# 123.123

– 聚合查询

显示每种职业的平均工资。

SELECT job, avg(sal) FROM emp GROUP BY job;

显示每个部门每种岗位的平均工资和最高工资。

SELECT deptno ,job, avg(sal), max(sal) FROM emp GROUP BY DEPTNO,job;

显示平均工资低于2500的部门号，平均工资及最高工资

1
2
3

SELECT deptno, avg(sal), max(sal) FROM emp 
GROUP BY DEPTNO
HAVING avg(sal);

上一条语句以平均工资升序排序

SELECT deptno, avg(sal), max(sal) FROM emp 
GROUP BY DEPTNO
HAVING avg(sal)
ORDER BY avg(sal);

– 多表查询

显示工资高于2500或岗位为MANAGER的所有员工的姓名，工资，职位，和部门号

SELECT ename, sal, job, deptno FROM emp WHERE sal>2500 OR job='manager';

排序显示所有员工的姓名，部门号，工资（以部门号升序，工资降序，雇用日期升序显示

SELECT ename, deptno, sal FROM emp ORDER BY deptno, sal DESC, hiredate ;

采用自然连接原理显示部门名以及相应的员工姓名。（Sql server不支持NATURAL JOIN语法

SELECT d.dname, e.ename FROM dept AS d NATURAL JOIN emp AS e;

查询SCOTT的上级领导的姓名。最直接能想到的就是标量子查询，当然也可以用连接的方式
1
2
3
SELECT m.ename
FROM emp AS m JOIN emp AS e ON 1=1
WHERE e.mgr = m.empno AND e.ename='scott';
显示部门的部门名称，员工名即使部门没有员工也显示部门名称

SELECT d.dname, e.ename FROM dept AS d LEFT JOIN emp AS e ON d.DEPTNO=e.DEPTNO;

– 子查询

显示所有员工的名称、工资以及工资级别

1 2	SELECT e.ename, e.sal, g.grade FROM emp e, salgrade g WHERE e.sal< (SELECT HISAL FROM salgrade) AND e.sal> (SELECT LOSAL FROM salgrade);

第二次

建表

create table classroom
    (building       varchar(15),
     room_number        varchar(7),
     capacity       numeric(4,0),
     primary key (building, room_number)
    );

create table department
    (dept_name      varchar(20), 
     building       varchar(15), 
     budget             numeric(12,2) check (budget > 0),
     primary key (dept_name)
    );

-- [Err] 1215 - Cannot add foreign key constraint
-- 添加外键约束错误
-- 解决：要保证两张表对应键数据类型相同，references后面不仅要有父表，还要有对应列信息，如 REFERENCES department(dept_name)

create table course
    (course_id      varchar(8), 
     title          varchar(50), 
     dept_name      varchar(20),
     credits        numeric(2,0) check (credits > 0),
     primary key (course_id),
     foreign key (dept_name) references department(dept_name)
        on delete set null
    );

-- 教师表

create table instructor
    (ID         varchar(5), 
     name           varchar(20) not null, 
     dept_name      varchar(20), 
     salary         numeric(8,2), -- check (salary > 29000)
     primary key (ID),
     foreign key (dept_name) references department(dept_name)
        on delete set null
    );

-- section部门 semester学期、

create table section
    (course_id      varchar(8), 
         sec_id         varchar(8),
     semester       varchar(6)
        check (semester in ('Fall', 'Winter', 'Spring', 'Summer')), 
     year           numeric(4,0) check (year > 1701 and year < 2100), 
     building       varchar(15),
     room_number        varchar(7),
     time_slot_id       varchar(4),
     primary key (course_id, sec_id, semester, year),
     foreign key (course_id) references course(course_id)
        on delete cascade,
     foreign key (building, room_number) references classroom(building, room_number)
        on delete set null
    );

create table teaches
    (ID         varchar(5), 
     course_id      varchar(8),
     sec_id         varchar(8), 
     semester       varchar(6),
     year           numeric(4,0),
     primary key (ID, course_id, sec_id, semester, year),
     foreign key (course_id,sec_id, semester, year) references section(course_id,sec_id, semester, year)
        on delete cascade,
     foreign key (ID) references instructor(ID)
        on delete cascade
    );

create table student
    (ID         varchar(5), 
     name           varchar(20) not null, 
     dept_name      varchar(20), 
     tot_cred       numeric(3,0) check (tot_cred >= 0), -- 总学分
     primary key (ID),
     foreign key (dept_name) references department(dept_name)
        on delete set null
    );

create table takes
    (ID         varchar(5), 
     course_id      varchar(8),
     sec_id         varchar(8), 
     semester       varchar(6),
     year           numeric(4,0),
     grade              varchar(2),
     primary key (ID, course_id, sec_id, semester, year),
     foreign key (course_id,sec_id, semester, year) references section(course_id,sec_id, semester, year)
        on delete cascade,
     foreign key (ID) references student(ID)
        on delete cascade
    );

-- 导师
create table advisor
    (s_ID           varchar(5),
     i_ID           varchar(5),
     primary key (s_ID),
     foreign key (i_ID) references instructor (ID)
        on delete set null,
     foreign key (s_ID) references student (ID)
        on delete cascade
    );

-- time_slot时间段

create table time_slot
    (time_slot_id       varchar(4),
     day            varchar(1),
     start_hr       numeric(2) check (start_hr >= 0 and start_hr < 24),
     start_min      numeric(2) check (start_min >= 0 and start_min < 60),
     end_hr         numeric(2) check (end_hr >= 0 and end_hr < 24),
     end_min        numeric(2) check (end_min >= 0 and end_min < 60),
     primary key (time_slot_id, day, start_hr, start_min)
    );

create table prereq
    (course_id      varchar(8), 
     prereq_id      varchar(8),
     primary key (course_id, prereq_id),
     foreign key (course_id) references course(course_id)
        on delete cascade,
     foreign key (prereq_id) references course(prereq_id)
    );

题目

查询学生至少两次选择的课程的课程名，学生名，选的次数

-- SELECT s.name, c.title, 
-- FROM student s, course c
-- WHERE (s.ID, c.course_id) in (
-- SELECT id, course_id
-- FROM takes
-- GROUP BY id,course_id
-- HAVING COUNT(course_id)>=2);

SELECT
    student. NAME,
    COUNT(takes.course_id) AS Counts,
    (
        SELECT
            title
        FROM
            course
        WHERE
            course.course_id = takes.course_id
    ) AS title
FROM
    student
JOIN takes ON student.ID = takes.ID
GROUP BY
    takes.ID,
    takes.course_id
HAVING
    COUNT(takes.course_id) >= 2;

老师的答案

>   select S.name, C.title, count(S.name) cnt
    from (student S join takes T on S.ID=T.ID) 
            join course C on T.course_id=C.course_id
    group by S.name, C.title
    having count(S.name) >= 2;
>

查询从没注册过课程的学生ID、name

SELECT
    student.ID,
    NAME
FROM
    student
LEFT JOIN takes ON student.ID = takes.ID
WHERE
    takes.ID IS NULL;

老师的

>   select S.ID, S.name
    from student S
    where S.ID not in ( 
        select distinct T.ID
        from takes T) 
>

查找已注册的但学分不明的课程名，及对应section的ID、年份、学期

SELECT
    ID,
    (
        SELECT
            title
        FROM
            course
        WHERE
            course.course_id = takes.course_id
    ) AS title,
    sec_id,
    semester,
    YEAR
FROM
    takes
WHERE
    grade IS NULL;

老师的

>   select C.title, T.ID, T.year, T.semester
    from course C join takes T on C.course_id = T.course_id
    where T.grade is null
>

查询注册至少有4名教师的部门的学生id、name，部门name。
– 提供两种方法

SELECT
    s.id,
    s. NAME,
    s.dept_name
FROM
    student s
WHERE
    s.dept_name IN (
        SELECT
            dept_name
        FROM
            instructor
        GROUP BY
            dept_name
        HAVING
            COUNT(NAME) >= 4
    );

-- 二
SELECT
    s.id,
    s. NAME,
    s.dept_name
FROM
    student s
JOIN (
    SELECT
        dept_name
    FROM
        instructor
    GROUP BY
        dept_name
    HAVING
        COUNT(NAME) >= 4
) AS i ON s.dept_name = i.dept_name;

-- 三
SELECT
    s.id,
    s. NAME,
    s.dept_name
FROM
    student s
JOIN instructor i ON s.dept_name = i.dept_name
GROUP BY
    s.id,
    s. NAME,
    s.dept_name
HAVING
    COUNT(i. NAME) >= 4;

>   4-1.
    select S.ID, S.name, S.dept_name
    from student S
    where S.dept_name in (
                select I.dept_name
                from instructor I
                group by I.dept_name
                having count(I.dept_name) >= 4)
    4-2.
    select S.ID, S.name, S.dept_name
    from student S join instructor I on S.dept_name=I.dept_name
    group by S.ID, S.name, S.dept_name
    having count(S.dept_name) >= 4
>

查找聘请教师姓名中含有”世”的部门名、教学楼、聘请教师数

SELECT
    d.dept_name,
    d.building,
    (
        SELECT
            COUNT(*)
        FROM
            instructor
        WHERE
            dept_name = d.dept_name
    ) AS counts
FROM
    department d
     WHERE
    d.dept_name IN (
        SELECT
            dept_name
        FROM
            instructor
        WHERE
            NAME LIKE "%世%"
    );

Teacher:

>   select D.dept_name, D.building, count(*) cnt
    from department D join instructor I on D.dept_name=I.dept_name
    group by D.dept_name, D.building
    having D.dept_name in( 
            select I.dept_name
            from instructor I
            where I.name like '%世%')
>

第三次

查询不包含成绩为F的课程（没人注册的课程也包含在内），课程名，统计与该课程相关联的section数

– 法一

SELECT DISTINCT c.course_id,c.title,(SELECT COUNT(course_id) 
FROM section 
WHERE section.course_id =c.course_id)AS 'count(section)'
FROM takes t RIGHT JOIN course c ON t.course_id = c.course_id
WHERE c.course_id NOT in (SELECT course_id FROM takes WHERE grade='F');

– 法二

SELECT DISTINCT c.course_id,c.title,( SELECT COUNT(*) 
        FROM section 
        WHERE section.course_id=c.course_id
        ) AS 'count(section)'
FROM takes t RIGHT JOIN course c ON t.course_id = c.course_id
WHERE NOT EXISTS(SELECT * FROM takes WHERE takes.grade = 'F' AND takes.course_id=t.course_id);


SELECT c.title, count(sec_id)
FROM 
(
        SELECT DISTINCT section.course_id, section.sec_id 
        FROM takes RIGHT JOIN section ON takes.course_id = section.course_id
        WHERE section.course_id NOT in (SELECT course_id FROM takes WHERE grade='F')
) AS s left JOIN course c ON s.course_id = c.course_id
GROUP BY c.title;

– 法三

WITH takes_course(course_id) AS(
    SELECT DISTINCT course_id 
    FROM takes
    WHERE takes.course_id not in (SELECT course_id FROM takes WHERE grade='F')
)
SELECT c.course_id, c.title,(SELECT COUNT(*) FROM section WHERE section.course_id=c.course_id)AS 'count(section)'
FROM takes_course t RIGHT JOIN course c ON t.course_id = c.course_id

– 法四

with course_(course_id,title)as(
    SELECT DISTINCT course.course_id ,course.title
    FROM takes RIGHT JOIN course on takes.course_id = course.course_id
    WHERE takes.course_id not in (SELECT course_id FROM takes WHERE grade='F')
)
SELECT c.course_id, c.title, COUNT(section.course_id) AS 'count(section)'
FROM course_  c LEFT JOIN section ON c.course_id=section.course_id
GROUP BY c.course_id, c.title;

– 法五

SELECT course.title, count(section.course_id)AS 'count(sec)'
FROM course LEFT JOIN section ON course.course_id = section.course_id
WHERE 1>(SELECT COUNT(course_id)
                    FROM takes
                    WHERE grade = 'F' AND course.course_id = takes.course_id
)
GROUP BY course.title;

Teacher’s Anwsers

>  with cur1 as (
    select distinct course.course_id, title
    from course left join takes on course.course_id = takes.course_id
    where course.course_id not in(
        select distinct course_id
        from takes
        where grade = 'F'
        )
    ),
    cur2 as (
    select distinct course.course_id, title
    from course left join takes on course.course_id = takes.course_id
    where course.course_id != all(
        select distinct course_id
        from takes
        where grade = 'F'
        )
    ),
    cur3 as (
    select distinct course.course_id, title
    from course left join takes on course.course_id = takes.course_id
    where not exists (
        select distinct course_id
        from takes
        where grade = 'F'
            and course.course_id=takes.course_id
        )
    ),
    cur4 as (
        select distinct course.course_id, title
        from course join takes on course.course_id = takes.course_id
        where not exists (
            select distinct course_id
            from takes
            where grade = 'F'
                and course.course_id=takes.course_id
            )
        union
        select distinct course.course_id, title
        from course 
        where course_id not in (
            select course_id
            from takes
        )
    ),
    cur as (
        select course_id, title
        from course
        where course_id in (
            select course_id
            from course 
            except
            select course_id
            from takes 
            where grade = 'F'
        )
    )
    select cur.course_id, title, count(section.course_id) cnt
    from cur left join section 
        on cur.course_id=section.course_id
    group by cur.course_id, title
>

查询每个部门的成绩分布

SELECT course_id,sec_id,semester,year,sum(CASE when grade LIKE 'A%' THEN 1 ELSE 0 END) A,
                SUM(CASE when grade LIKE 'B%' THEN 1 ELSE 0 END) B,
                SUM(CASE when grade LIKE 'C%' THEN 1 ELSE 0 END) C,
                SUM(CASE when grade LIKE 'D%' THEN 1 ELSE 0 END) D,
                SUM(CASE when grade LIKE 'E%' THEN 1 ELSE 0 END) E,
                SUM(CASE when grade LIKE 'F%' THEN 1 ELSE 0 END) F,SUM(CASE WHEN grade IS NULL THEN 1 ELSE 0 END) NO_grade
FROM takes
GROUP BY course_id,sec_id,semester,`year`;

Teacher:

>   with tempt as(
    select course_id, year, semester, sec_id, 
        case when grade like 'A%' then 1 end A,
        case when grade like 'B%' then 1 end B,
        case when grade like 'C%' then 1 end C,
        case when grade like 'D%' then 1 end D,
        case when grade='F' then 1 end F,
        case when grade is null then 1 end nograde
    from takes
    )
    select course_id, year, semester, sec_id,
        isnull(sum(A), 0) A,
        isnull(sum(B), 0) B,
        isnull(sum(C), 0) C,
        isnull(sum(D), 0) D,
        isnull(sum(F), 0) F,
        isnull(sum(nograde), 0) nograde
    from tempt
    group by course_id, year, semester, sec_id
>

查询每门课每周的学时（一学时为50分钟），且每学期该门课有相关联的部门
查询该门课的名称，年份，学期，统计同年同学期开设该门课的部门数，每周学时

SELECT (SELECT title from course WHERE course_id=section.course_id) AS course_title,`year`,semester,
(
                SELECT SUM((end_hr-start_hr)*60/50+(end_min-start_min)/50) 
                FROM time_slot
                WHERE time_slot_id=section.time_slot_id
                GROUP BY time_slot_id
)AS 'credit hour/week', 
count_s.count_section
FROM section LEFT JOIN (SELECT COUNT(*) AS count_section,course_id FROM section GROUP BY course_id)AS count_s ON section.course_id = count_s.course_id
;

– 感觉这种是对的

SELECT title,`year`,semester, temp.cred_hr, COUNT(*)
FROM
(select time_slot_id, sum(((end_hr-start_hr)*60+(end_min-start_min))/50) as cred_hr
from time_slot 
group by time_slot_id
)AS temp JOIN section ON temp.time_slot_id = section.time_slot_id JOIN course ON section.course_id = course.course_id
GROUP BY title, `year`, semester, temp.cred_hr;

Teacher’s Awq


with timeslot_credithour as(
    select time_slot_id, count(*) * ((end_hr-start_hr)*60+(end_min-start_min))/50 credit_hr
    from time_slot
    group by time_slot_id, start_hr, end_hr, start_min, end_min);

select course_id, year, semester,count(*) sec_cnt, sum(credit_hr) cred_hr
from section join timeslot_credithour 
    on section.time_slot_id=timeslot_credithour.time_slot_id
group by course_id, year, semester;

//

with A as(select section.course_id,section.semester,year,section.sec_id,section.time_slot_id as slotid,start_hr,start_min,end_hr,end_min
from section join time_slot on section.time_slot_id=time_slot.time_slot_id)

select course_id,year,semester,sum(((end_hr-start_hr)*60+end_min-start_min)/50) as totalhours
from A
group by course_id,year,semester

查询学生和老师对，要求同一部门中该学生选了该老师的课，同时该老师也是学生的导师
列出学生名，老师名

Teacher’s AWQ

select student.name, instructor.name
from student, instructor, (
    select distinct TK.ID s_id, TC.ID t_id
    from takes TK join teaches TC on
        TK.course_id=TC.course_id and TK.year=TC.year
        and TK.sec_id=TC.sec_id and TK.semester=TC.semester
    where exists (
        select *
        from advisor
        where TK.ID=advisor.s_ID 
            and TC.ID=advisor.i_ID
    ) 
) t_s_i
where student.ID=t_s_i.s_id and instructor.ID=t_s_i.t_id;

第四次

更新所有学生的总学分tot_cred（如果该生无成绩为F）

复制一张新表student_1

CREATE TABLE student_1 LIKE student;
INSERT INTO student_1 SELECT * FROM student;

UPDATE student_1
SET tot_cred = (SELECT sum(credits)
FROM takes, course
WHERE takes.course_id = course.course_id AND takes.grade != 'F' AND takes.grade NOT NULL AND takes.ID = student_1.ID
GROUP BY ID )

Teacher:

>   with passed_course_credit as (
        select ID, c_id, credits
        from course join (
            select distinct ID, course_id c_id
            from takes
            where grade != 'F') passed_course
            on course_id = passed_course.c_id
    )
    update student
    set tot_cred = (
        select sum(credits)
        from passed_course_credit
        where student.ID=passed_course_credit.ID
        group by passed_course_credit.ID
    )
>

查找学生，该生通过了其所属的部门开设的所有课程，列出stu_name，dept_name

SELECT DISTINCT `name`, student.dept_name
FROM student JOIN course ON student.dept_name = course.dept_name 
WHERE EXISTS ( 
        SELECT * FROM takes WHERE grade != 'F' AND ID = student.ID AND course_id = course.course_id
) 
GROUP BY `name`,student.dept_name
HAVING COUNT(DISTINCT course_id)= (
            SELECT COUNT(course_id) 
            FROM course 
            WHERE student.dept_name=course.dept_name 
            GROUP BY course.dept_name);

Teacher:

>   select name, dept_name
    from student
    where not exists (
        select course_id
        from course
        where student.dept_name=course.dept_name
        except
        select course_id
        from takes
        where student.ID = takes.ID 
            and grade != 'F'
    )
>

学生学号升序排列ascending，输出第六到第十个学生的name，id（提供两种方法）

– 法一

SELECT name,id 
FROM (
SELECT name, id , @curRank := @curRank +1 AS rank
FROM student,(SELECT @curRank :=0) p
ORDER BY id ASC) temp
WHERE temp.rank BETWEEN 6 AND 10;

– 法二

SELECT top 10 ID ,name
FROM student
ORDER BY ID
except
SELECT top 5 ID ,name 
FROM student
ORDER BY ID ;

SELECT top 10 ID , name 
FROM student
WHERE ID NOT in (SELECT top 5 ID FROM student ORDER BY ID)
ORDER BY ID;

Teacher:

>   with rk_stu1 as ( 
    select row_number() over(order by ID ) rk, ID, name
    from student
    )
    , rk_stu2 as (  
    select rank() over(order by ID ) rk, ID, name
    from student
    )
    , rk_stu as (   
    select count(*) rk, s1.ID, s1.name
    from student s1 , student s2
    where s1.ID >= s2.ID
    group by s1.ID, s1.name
    )
    select ID, name
    from rk_stu
    where rk in (6,10)
>

找出挂过科并且重修也没过的学生，id，name和现阶段重修没过科目数目

SELECT s.name, s.ID, COUNT(*)
FROM student s JOIN (
SELECT ID,course_id
FROM takes
WHERE grade='F' AND (ID,course_id) not in (SELECT DISTINCT id,course_id FROM takes WHERE grade !='F')
) t
ON s.ID=t.ID
GROUP BY s.ID,s.name;

– 就是成绩为F的集合减去不为F的集合（仍旧未通过）

select student.ID,student.`name`,COUNT(*)
from takes t JOIN student ON t.ID=student.ID
where t.grade='F'and t.grade is not null AND (t.id,t.course_id) NOT in (select id,course_id
from takes 
where grade!='F'and grade is not null
)
GROUP BY student.ID,student.`name`;

Teacher:

>   with tempt as (
        select ID, course_id
        from takes
        where grade='F'
        except
        select ID, course_id
        from takes
        where grade!='F'
    )
    select tempt.ID, name, count(*) cnt 
    from student join tempt on student.ID=tempt.ID
    group by tempt.ID, name
>

找到上过某些课超过一次的学生，id，name，课程title，该生上课次数，以及这门课挂科记录次数

– 自己的

with a AS(
SELECT ID,course_id,count(*) AS 'count',count(CASE WHEN grade='F' THEN 1 END) AS 'count_F'
FROM takes
GROUP BY ID,course_id
HAVING COUNT(*)>1);

SELECT student.ID, student.`name`, course.title, a.count, a.count_F
FROM student,course,a
WHERE student.ID = a.id AND course.course_id = a.course_id

– 老师写的

select student.id,student.`name`,title,count(*),count(case grade when 'F' then 1  end )
from takes join course on course.course_id=takes.course_id,student
WHERE takes.ID=student.ID
group by id,title
having count(*)>1

Teacher:

    with course_grade as (
    select ID, course_id, 
        case when grade = 'F' then 1 else 0 end F
    from takes
    )
    select student.ID, name, course_id, count(F) cnt_stu, sum(F) cnt_fail_course
    from course_grade join student 
        on course_grade.ID = student.ID
    group by student.ID, name, course_id
    having count(*) > 1
>

第五次 E-R图，关系数据库设计

医院病房管理系统

需求：

一个科室有多个病房、多个医生和一个科室主任。
一个病房只属于一个科室、一个医生只能在一个科室工作。
一个医生可以同时做多个病人的主治医生。
一个病人只能有一个主治医生、也只能入住一个病房。
一个护士负责多个病房、一个病房由多个护士负责。
一个护士属于一个科室、一个科室拥有多个护士。

问题：

在给出的需求基础上，进一步进行分析，完善需求，并列出添加的需求（上面加粗的部分）

根据以上需求，抽象出实体和实体间关系，包括实体和关系的属性。

科室（科室编码、所在建筑、科室主任）
病房（病房号、所在建筑、病房床数）
医生（医生编码、年龄、性别）
病人（病人编码、年龄、性别）
护士（护士编码、年龄、性别）
属于（科室编码、病房编码）
医生工作（科室编码、医生编码）
护士工作（科室编码、医生编码）
入住（病人编码、病房号）
护理（护士编码、病房号）
治疗（病人编码、医生编码）

根据分析得到的实体和实体间关系，画出E-R图。

将设计得到的E-R图转换为等价的关系模式。

科室（科室编码、所在建筑、科室主任）
病房（病房号、科室编码、所在建筑、病房床数）
医生（医生编码、科室编码、年龄、性别）
病人（病人编码、医生编码、病房号、年龄、性别）
护士（护士编码、科室编码、年龄、性别）
护理（护士编码、病房号）

第六次关系代数和范式

关系代数练习题目（基于EMP-DEPT数据库）

关系规范化练习题目

设有关系模式 R（A，B，C，D），其上的函数依赖集为：
F={A->C, C->A, B->AC, D->AC}

（1）计算（AD）+

ACD

（2）求 R 的候选码

（3）把 R 分解为满足 BCNF 的关系模式集合。

(AC) 和 (ABD)

(AC) 和 (CBD)

(AC)、(BC) 和 (BD)

(AC)、(AB) 和 (BD)

(AC)、(CD) 和 (BD)

(AC)、(AD) 和 (BD)

设有关系模式 R<U,F>，其中：U={A,B,C,D,E,P}, F={A->B,C->P,E->A,CE->D}求R的所有候选码。

CE
设属性 X={BG}，函数依赖集 F 包含 9 个函数依赖：
{AC->PE, PG->A, B->CE, A->P, A->B, GC->A, PAB->G, AG->BG, ABCP->H}
求 X 关于 F 的的属性闭包 X+。

ABCEGPH

第一次

建表

题目

第二次

建表

题目

第三次

第四次

第五次 E-R图，关系数据库设计

医院病房管理系统

第六次 关系代数和范式

关系代数练习题目（基于EMP-DEPT数据库）

关系规范化练习题目

第六次关系代数和范式