实训
1. SELECT m_name FROM movie WHERE m_star='吴京';
基本语法结构(2分) 条件(2分)
2. SELECT m_name,m_type FROM filmSession JOIN movie ON filmSession.movie_id=movie.movie_id WHERE session_id=3;
基本语法结构(2分) 连接(2分)
3. SELECT m_name,startTime FROM member JOIN ticket ON member.`user_id`=ticket.`user_id` JOIN filmSession ON ticket.`session_id`=filmSession.`session_id` JOIN movie ON filmSession.`movie_id`=movie.`movie_id` WHERE u_name='李勇' ORDER BY startTime;
基本语法结构(2分) 连接和排序(2分)
4. SELECT m_name,m_type,m_company FROM filmSession JOIN movie ON filmSession.movie_id=movie.movie_id WHERE startTime BETWEEN '2022-11-08' AND '2022-11-10' order by m_name;
基本语法结构(2分) between...and....(2分)
5. SELECT u_name,u_tel FROM member JOIN ticket ON member.`user_id`=ticket.`user_id` GROUP BY ticket.`user_id` HAVING COUNT(*)>=3;
基本语法结构(2分) 分组与条件(2分)
6. SELECT session_id,startTime FROM filmSession WHERE NOT EXISTS(
SELECT * FROM member WHERE NOT EXISTS (
SELECT * FROM ticket WHERE ticket.`session_id`=filmSession.`session_id`
AND ticket.`user_id`=member.`user_id`
)
);
基本语法结构(2分) 存在谓词(2分)
7. CREATE VIEW user_film
AS
SELECT member.user_id,u_name,m_name,startTime FROM member JOIN ticket ON member.`user_id`=ticket.`user_id` JOIN filmSession ON ticket.`session_id`=filmSession.`session_id`
JOIN movie ON filmSession.`movie_id`=movie.`movie_id`;
基本语法结构(2分) 视图逻辑(2分)
8. CREATE USER 'u1'@'localhost' IDENTIFIED BY '123456';
GRANT SELECT ON movie TO 'u1'@'localhost';
创建用户(2分) 授权(2分)
9. CREATE TABLE filmReview(
user_id INT,
movie_id INT,
review CHAR(50),
PRIMARY KEY(user_id,movie_id),
FOREIGN KEY (user_id) REFERENCES member(user_id),
FOREIGN KEY (movie_id) REFERENCES movie(movie_id)
);
基本语法结构(2分) 主外键(2分)
10. ALTER TABLE filmReview ADD COLUMN words INT;
基本语法结构(2分) 字段(2分)
11. INSERT INTO filmReview VALUES(3,2,'非常热血,我爱我国',8);
基本语法结构(2分) 元组正确(2分)
12. update filmReview set words=9 where user_id=3 and movie_id=2;
基本语法结构(2分) 元组正确(2分)
13. DELIMITER $$
CREATE PROCEDURE pro_select(IN username CHAR(10))
READS SQL DATA
BEGIN
SELECT * FROM member WHERE u_name LIKE CONCAT('%',username,'%');
END
$$
DELIMITER ;
CALL pro_select('立');
基本语法结构(5分) 功能正确(2分) 调用(2分)
14. DELIMITER $$
CREATE FUNCTION fun_sum(username CHAR(10)) RETURNS INT
READS SQL DATA
BEGIN
DECLARE sum_pri INT;
SELECT SUM(price) INTO sum_pri FROM member JOIN ticket ON member.user_id=ticket.user_id JOIN filmSession ON ticket.session_id=filmSession.session_id
WHERE member.u_name=username;
RETURN sum_pri;
END
$$
DELIMITER ;
SELECT fun_sum('李勇');
基本语法结构(5分) 功能正确(2分) 调用(2分)
15. DELIMITER $$
CREATE TRIGGER tri_remain AFTER INSERT ON ticket FOR EACH ROW
BEGIN
UPDATE filmSession SET remain=remain-1 WHERE session_id=new.session_id;
END
$$
DELIMITER ;
基本语法结构(5分) 功能正确(4分)
简答
1.∏m_star (σm_type=‘喜剧片’(movie))
(2分) (2分)
2.∏price (σstartTime=‘2022-11-8’Ʌm_type=‘喜剧片’(movie⋈filmSession))
(2分) (2分)
3.∏u_name(∏user_id,session_id(ticket) ÷ ∏session_id (filmSession)⋈member)
(2分) (2分)
5.评价表的关系模型为:评价(用户ID,电影ID,评价内容,字数);候选码为:(用户ID,电影ID),
非主属性为:评价内容、字数。非主属性和候选码之间不存在部分依赖,属于2NF。(用户ID,电影ID)
可推出评价内容,评价内容可推出字数,非主属性和候选码之间存在传递依赖,所以不是3NF。