1. 使用含有关键字exists查找未分配具体部门的员工的所有信息。
题目描述
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
输出格式:
emp_no | birth_date | first_name | last_name | gender | hire_date |
---|---|---|---|---|---|
10011 | 1953-11-07 | Mary | Sluis | F | 1990-01-22 |
这里主要就是设计了exists的语法格式(从网上找了一下这个的格式,如下):
EXISTS语句不关心子查询的具体内容,因此用“SELECT *”,“Exists + 子查询”用来判断该子查询是否返回记录。
Exists:若子查询的结果集非空时,返回“True”;若子查询的结果集为空时,返回“False” 。
NOT EXISTS :若子查询结果为空,返回“TRUE”值;若子查询的结果集非空时,返回 “FALSE。
查询没有选C1课程的学生的学号、姓名
SELECT 姓名
FROM 学生表
WHERE NOT EXISTS
(
SELECT *
FROM 选课表
WHERE 学生表.学号 = 选课表.学号 AND 课程号 = 'C1'
);
本题中:
用 EXISTS 关键字的方法如下:意为在 employees 中挑选出令(SELECT emp_no FROM dept_emp WHERE emp_no = employees.emp_no)不成立的记录,即当 employees.emp_no=10011时。反之,把NOT去掉,则输出 employees.emp_no=10001~10010时的记录。
select * from employees em where not exists (select emp_no from dept_emp where emp_no=em.emp_no)
2. 创建一个actor_name表,将actor表中的所有first_name以及last_name导入改表
题目描述
actor_id | first_name | last_name | last_update |
---|---|---|---|
1 | PENELOPE | GUINESS | 2006-02-15 12:34:33 |
2 | NICK | WAHLBERG | 2006-02-15 12:34:33 |
创建一个actor_name表,将actor表中的所有first_name以及last_name导入改表。 actor_name表结构如下:
列表 | 类型 | 是否为NULL | 含义 |
---|---|---|---|
first_name | varchar(45) | not null | 名字 |
last_name | varchar(45) | not null | 姓氏 |
1.用create table 语句建立actor_name 表
2.用inset into actor select插入子查询的结果集(不需要用values(),()这种形式。这种形式是手工插入单条数据或多条数据时用圆括号分割。插入结果集是不需要)
create table actor_name(
first_name varchar(45) not null,
last_name varchar(45) not null
);
insert into actor_name select first_name,last_name from actor
3. 批量插入数据
题目描述
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))
actor_id | first_name | last_name | last_update |
---|---|---|---|
1 | PENELOPE | GUINESS | 2006-02-15 12:34:33 |
2 | NICK | WAHLBERG | 2006-02-15 12:34:33 |
本题考查批量插入数据库的操作(当然去掉(actor_id,first_name,last_name,last_update) 也是可以的):
insert into actor (actor_id,first_name,last_name,last_update) values
(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
(2,'NICK' ,'WAHLBERG','2006-02-15 12:34:33')
下面这个参考牛客网网友:
SQL UNION 操作符
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。
SQL UNION 语法
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
注释:默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL。
SQL UNION ALL 语法
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
另外,UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
INSERT INTO actor
SELECT 1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'
UNION SELECT 2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33'
4. 针对上面的salaries表emp_no字段创建索引idx_emp_no,查询emp_no为10005,
题目描述
针对salaries表emp_no字段创建索引idx_emp_no,查询emp_no为10005, 使用强制索引。
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
create index idx_emp_no on salaries(emp_no);
SQLite中,使用 INDEXED BY 语句进行强制索引查询:
select * from salaries indexed by idx_emp_no where emp_no=10005
MySQL中,使用 FORCE INDEX 语句进行强制索引查询:
SELECT * FROM salaries FORCE INDEX idx_emp_no WHERE emp_no = 10005
5. 构造一个触发器audit_log,在向employees表中插入一条数据的时候,触发插入相关的数据到audit中
题目描述
构造一个触发器audit_log,在向employees表中插入一条数据的时候,触发插入相关的数据到audit中。
CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
CREATE TABLE audit(
EMP_no INT NOT NULL,
NAME TEXT NOT NULL
本题中构造触发器时注意以下几点:
1、用 CREATE TRIGGER 语句构造触发器,用 BEFORE或AFTER 来指定在执行后面的SQL语句之前或之后来触发TRIGGER
2、触发器执行的内容写出 BEGIN与END 之间
3、可以使用 NEW与OLD 关键字访问触发后或触发前的employees_test表单记录
创建 触发器(Trigger) 的基本语法如下:
CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name
ON table_name
BEGIN
-- Trigger logic goes here....
END;
[BEFORE|AFTER] event_name
ON table_name
BEGIN
-- Trigger logic goes here....
END;
6. 使用join查询方式找出没有分类的电影id以及名称
题目描述
film表
字段 | 说明 |
film_id | 电影id |
title | 电影名称 |
description | 电影描述信息 |
CREATE TABLE IF NOT EXISTS film (
film_id smallint(5) NOT NULL DEFAULT '0',
title varchar(255) NOT NULL,
description text,
PRIMARY KEY (film_id));
category表
字段 | 说明 |
category_id | 电影分类id |
name | 电影分类名称 |
last_update | 电影分类最后更新时间 |
CREATE TABLE category (
category_id tinyint(3) NOT NULL ,
name varchar(25) NOT NULL, `last_update` timestamp,
PRIMARY KEY ( category_id ));
film_category表
字段 | 说明 |
film_id | 电影id |
category_id | 电影分类id |
last_update | 电影id和分类id对应关系的最后更新时间 |
CREATE TABLE film_category (
film_id smallint(5) NOT NULL,
category_id tinyint(3) NOT NULL, `last_update` timestamp);
使用join查询方式找出没有分类的电影id以及名称
SQL LEFT JOIN 关键字
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行,即使在右表 (table_name2) 中没有匹配的行。
LEFT JOIN 关键字语法
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
注释:在某些数据库中, LEFT JOIN 称为 LEFT OUTER JOIN。
由此sql1:
SELECT f.film_id,f.title from film f left join film_category fc
on f.film_id=fc.film_id where fc.category_id is null
sql2:inner join
先用film_id相等条件连接两个表,然后再在film表中找出film_id不在连接的表中的部分
SELECT f.film_id,f.title from film f where f.film_id not in(select f.film_id from film f inner join film_category
fc where f.film_id=fc.film_id)
sql3:
select f.film_id,f.title
from film as f left join film_category as fm
on f.film_id = fm.film_id
where f.film_id not in (select film_id from film_category);
sql4:
select bb.film_id,bb.title
from (film left join film_category on film.film_id = film_category.film_id) as bb
where bb.category_id is null
7. 统计salary的累计和running_total
题目描述
按照salary的累计和running_total,其中running_total为前两个员工的salary累计和,其他以此类推。 具体结果如下Demo展示。。
CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
输出格式:
emp_no | salary | running_total |
---|---|---|
10001 | 88958 | 88958 |
10002 | 72527 | 161485 |
10003 | 43311 | 204796 |
10004 | 74057 | 278853 |
10005 | 94692 | 373545 |
10006 | 43311 | 416856 |
10007 | 88070 | 504926 |
10009 | 95409 | 600335 |
10010 | 94409 | 694744 |
10011 | 25828 | 720572 |
本题的思路为复用 salaries表进行子查询,最后输出求和结果。
1、输出的第三个字段,是由一个 SELECT 子查询构成。将子查询内复用的 salaries 表记为 s2,主查询的 salaries 表记为 s1,当主查询的 s1.emp_no 确定时,对子查询中不大于 s1.emp_no 的 s2.emp_no 所对应的薪水求和
2、注意是对员工当前的薪水求和,所以在主查询和子查询内都要加限定条件 to_date = '9999-01-01'
select s1.emp_no,s1.salary,
(
SELECT SUM(s2.salary) from salaries as s2 where s1.emp_no>=s2.emp_no and s2.to_date='9999-01-01'
)as running_total
from salaries as s1 where s1.to_date='9999-01-01'
8.获取有奖金的员工相关信息。
题目描述
获取有奖金的员工相关信息。
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
create table emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`from_date`));
给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%,btype为2其奖金为薪水的20%,其他类型均为薪水的30%。 当前薪水表示to_date='9999-01-01'
输出格式:
emp_no | first_name | last_name | btype | salary | bonus |
---|---|---|---|---|---|
10001 | Georgi | Facello | 1 | 88958 | 8895.8 |
10002 | Bezalel | Simmel | 2 | 72527 | 14505.4 |
10003 | Parto | Bamford | 3 | 43311 | 12993.3 |
10004 | Chirstian | Koblick | 1 | 74057 | 7405.7 |
本题主要考查 SQLite 中 CASE 表达式的用法。即当 btype = 1 时,得到 salary * 0.1;当 btype = 2 时,得到 salary * 0.2;其他情况得到 salary * 0.3。
条件表达式:
该表达式的语法规则如下:
1). CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END
2). CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END
select eb.emp_no, first_name, last_name, btype, salary,
(case eb.btype
when 1 then salary*0.1
when 2 then salary*0.2
else salary*0.3
end) as bonus
from emp_bonus eb,employees e ,salaries s where
eb.emp_no = e.emp_no and e.emp_no=s.emp_no and s.to_date='9999-01-01' ;
sql2:
SELECT e.emp_no, e.first_name, e.last_name, b.btype, s.salary,
(CASE b.btype
WHEN 1 THEN s.salary * 0.1
WHEN 2 THEN s.salary * 0.2
ELSE s.salary * 0.3 END) AS bonus
FROM employees AS e INNER JOIN emp_bonus AS b ON e.emp_no = b.emp_no
INNER JOIN salaries AS s ON e.emp_no = s.emp_no AND s.to_date = '9999-01-01'
sql3:
其实观察测试数据会发现 btype 只有1,2,3三种情况,即使不会 CASE 表达式,也能运用四则运算解出:(注意要除以10.0间接转换为double类型,如果除以10的话,得到的是int类型。结果的小数位会被舍去)
SELECT e.emp_no, e.first_name, e.last_name, b.btype, s.salary,
(s.salary * b.btype / 10.0) AS bonus
FROM employees AS e INNER JOIN emp_bonus AS b ON e.emp_no = b.emp_no
INNER JOIN salaries AS s ON e.emp_no = s.emp_no AND s.to_date = '9999-01-01'
在这里,event_name 可以是在所提到的表 table_name 上的 INSERT、DELETE 和 UPDATE 数据库操作。您可以在表名后选择指定 FOR EACH ROW。
以下是在 UPDATE 操作上在表的一个或多个指定列上创建触发器(Trigger)的语法:
CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
BEGIN
-- Trigger logic goes here....
END;
[BEFORE|AFTER] UPDATE OF column_name
ON table_name
BEGIN
-- Trigger logic goes here....
END;
CREATE TRIGGER audit_log AFTER INSERT
ON employees_test
BEGIN
INSERT INTO audit VALUES(NEW.ID,NEW.NAME);
END;
9. 查找所有已经分配部门的员工的last_name和first_name
题目描述
查找所有已经分配部门的员工的last_name和first_name
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
在SQL中:
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行
SQL INNER JOIN 关键字
在表中存在至少一个匹配时,INNER JOIN 关键字返回行。
INNER JOIN 关键字语法
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
注释:INNER JOIN 与 JOIN 是相同的。
SELECT employees.last_name, employees.first_name, dept_emp.dept_no from employees inner join
dept_emp on employees.emp_no=dept_emp.emp_no;
自然连接:natural join,自动对两个表,按照同名的列进行内连接,是一种特殊的等值连接:
SELECT employees.last_name,employees.first_name,dept_emp.dept_no from employees natural join dept_emp