anSQL 基本语句练习 ( 红色题目表示本人第一次没有写出来的)
表1 Employee表
编号 | 姓名 | 地址 | 邮编 | 电话 | 电子邮件 | 部门 | 出生 日期 | 性别 |
Num | Name | Addr | Zip | Tel | | Depno | Birth | Sex |
001 | 王林 | 武汉大学 | 430074 | 87598405 | Null | 2 | 1985-2-1 | 男 |
002 | 王芳 | 华中科大 | 430073 | 62534231 | Null | 1 | 1966-3-28 | 男 |
003 | 张晓 | 武汉理工大 | 430072 | 87596985 | Null | 1 | 1972-12-9 | 男 |
004 | 王小燕 | 武汉交大 | 430071 | 85743261 | 1 | 1950-7-30 | 女 | |
005 | 李华 | 华中农大 | 430070 | 87569865 | Null | 5 | 1962-10-18 | 男 |
006 | 李明 | 华中师大 | 430075 | 85362143 | zhujun@sina.com | 5 | 1955-09-28 | 男 |
007 | 田丽 | 中南财大 | 430076 | 85693265 | 3 | 1968-08-10 | 女 | |
008 | 吴天 | 武汉电力 | 430077 | 36985612 | 5 | 1964-10-01 | 男 | |
009 | 刘备 | 武汉邮科院 | 430078 | 69865231 | Null | 3 | 1967-04-02 | 男 |
010 | 张飞 | 武汉软通 | 430079 | 69865632 | 4 | 1958-09-20 | 男 | |
011 | 赵云 | 学府家园 | 430071 | 68592312 | Null | 4 | 1968-11-18 | 男 |
012 | 貂禅 | 湖北工大 | 430074 | 65987654 | null | 4 | 1959-09-03 | 女 |
表2 Department
编号 | 部门名称 | 备注 |
Depno | DepName | Remark |
1 | 财务部 | Null |
2 | 人力资源部 | Null |
3 | 经理办公室 | Null |
4 | 研发部 | Null |
5 | 市场部 | Null |
表3 salary
编号 | 收入 | 支出 |
Num | InCome | OutCome |
001 | 2100.8 | 123.09 |
002 | 1582.62 | 88.03 |
003 | 2569.88 | 185.65 |
004 | 1987.01 | 79.58 |
005 | 2066.15 | 108.0 |
006 | 2980.7 | 210.2 |
007 | 3259.98 | 281.52 |
008 | 2860.0 | 198 |
009 | 2347.68 | 180 |
010 | 2531.98 | 199.08 |
011 | 2240.0 | 121.0 |
012 | 1980.0 | 100.0 |
练习1:SELECT语句的基本使用
(1) 查询每个雇员的所有记录;
(2) 查询前5个会员的所有记录;
(3) 查询每个雇员的地址和电话;
(4) 查询num为001的雇员地址和电话;
(5) 查询表Employee表中女雇员的地址和电话,使用AS子句将结果列中各列的标题分别指定为地址、电话;
(6) 计算每个雇员的实际收入;
(7) 找出所有姓王的雇员的部门号(部门号不能重复显示);
(8) 找出所有收入在2000-3000元之间的雇员编号
练习2:
(1) 查找在财务部工作的雇员情况;
(2) 查找在财务部且年龄不低于研发部任一个雇员年龄的雇员的姓名;
(3) 查找比所有财务部雇员收入都高的雇员的姓名;
练习3:
(1) 查找每个雇员的情况及薪水情况;
(2) 查找财务部收入在2200元以上的雇员姓名及其薪水详细情况;
练习4:数据汇总
(1) 求财务部雇员的平均实际收入;
(2) 求财务部雇员的总人数;
练习5:GROUP BY 、ORDER BY 子句的使用
(1) 求各部门的雇员数(要求显示,部门号、部门名称和部门雇员数);
(2) 求部门的平均薪水大于2500的部门信息(要求显示,部门号、部门名称和平均工资)
本人写的SQL 脚本:
if DB_id('itcast') is null
CREATE DATABASE itcast
go
USE itcast
go
-- ----------- DROP TABLE----------------------------------------------
if Object_id('Employee') is not null
DROP TABLE Employee
go
if Object_id('Department') is not null
DROP TABLE Department
go
if Object_id('salary') is not null
DROP TABLE salary
go
-- --------------- CREATE TABLE: Department --------------------------
CREATE TABLE Department
(
Depno int identity(1,1),
DepName varchar(20),
Remark varchar(100)
)
go
ALTER TABLE Department
Add Constraint PK_Department Primary Key CLUSTERED (Depno)
WITH FILLFACTOR =80
ON [DEFAULT]
-- ----------- CREATE TABLE : Employee --------------
CREATE TABLE Employee
(
Num int identity(1,1) not null,
Name varchar(20),
Addr varchar(20),
Zip char(6),
Tel char(8),
Email varchar(20),
Depno int,
Birth datetime,
Sex char(2)
)
go
ALTER TABLE Employee
ADD Constraint PK_Employee Primary Key CLUSTERED (Num)
WITH FILLFACTOR =80
ON [DEFAULT]
go
-- ----------- CREATE TABLE : Salary---------------
CREATE TABLE salary
(
Num int identity(1,1),
InCome float,
OutCome float
)
go
ALTER TABLE salary
ADD Constraint PK_salary Primary Key CLUSTERED (Num)
WITH FILLFACTOR = 80
ON [DEFAULT]
go
-- ----------- INSERT TABLE : Employee---------------
INSERT INTO Employee values('王林', '武汉大学', '430074', '87598405', Null, 2, '1985-2-1', '男')
INSERT INTO Employee values('王芳', '华中科大', '430073', '62534231', Null, 1, '1966-3-28','男')
INSERT INTO Employee values('张晓', '武汉理工大','430072', '87596985', Null, 1, '1972-12-9','男')
INSERT INTO Employee values('王小燕','武汉交大' ,'430071', '85743261', 'lili@sina.com',1,'1950-7-30','女')
INSERT INTO Employee values('李华', '华中农大', '430070', '87569865', Null, 5,'1962-10-18','男')
INSERT INTO Employee values('李明', '华中师大', '430075', '85362143', 'zhujun@sina.com', 5, '1955-09-28' ,'男')
INSERT INTO Employee values('田丽', '中南财大', '430076', '85693265', 'zgming@sohu.com',3,'1968-08-10', '女')
INSERT INTO Employee values('吴天', '武汉电力', '430077', '36985612', 'zjamg@china.com',5,'1964-10-01', '男')
INSERT INTO Employee values('刘备', '武汉邮科院', '430078', '69865231', Null, 3, '1967-04-02','男')
INSERT INTO Employee values('张飞', '武汉软通', '430079', '69865632', 'liyu@tom.com',4,'1958-09-20', '男')
INSERT INTO Employee values('赵云', '学府家园', '430071', '68592312', Null, 4, '1968-11-18','男')
INSERT INTO Employee values('貂禅', '湖北工大', '430074', '65987654', null, 4, '1959-09-03','女')
go
-- --------- INSERT TABLE: Department ------------------
INSERT INTO Department values('财务部',null)
INSERT INTO Department values('人力资源部',Null)
INSERT INTO Department values('经理办公室',Null)
INSERT INTO Department values('研发部',Null)
INSERT INTO Department values('市场部',Null)
go
-- --------------- INSERT TABLE: salary ---------------------------
INSERT INTO salary values(2100.8, 123.09)
INSERT INTO salary values(1582.62, 88.03)
INSERT INTO salary values(2569.88, 185.65)
INSERT INTO salary values(1987.01, 79.58)
INSERT INTO salary values(2066.15, 108.0)
INSERT INTO salary values(2980.7, 210.2)
INSERT INTO salary values(3259.98, 281.52)
INSERT INTO salary values(2860.0, 198)
INSERT INTO salary values(2347.68, 180)
INSERT INTO salary values(2531.98, 199.08)
INSERT INTO salary values(2240.0, 121.0)
INSERT INTO salary values(1980.0, 100.0)
go
-- ----------------1. 查询每个雇员的所有记录 ----------------
-- SELECT * FROM Employee e,Department d, salary s WHERE e.Depno=d.Depno and e.Num=s.Num
-- ----------------2. 查询前5个会员的所有记录 ----------------
-- SELECT top(5)* FROM Employee e, Department d, salary s WHERE e.Depno=d.Depno and e.Num=s.Num
-- ----------------3. 查询每个雇员的地址和电话 ----------------
-- SELECT Addr , Tel from Employee
-- ----------------4. 查询num为001的雇员地址和电话 ----------------
-- SELECT Addr , Tel FROM Employee WHERE Num=001
-- 5.查询表Employee表中女雇员的地址和电话,使用AS子句将结果列中各列的标题分别指定为地址、电话
-- SELECT Addr , Tel AS '地址' ,'电话' FROM Employee WHERE SEX='女'
-- ---------------- 6. 计算每个雇员的实际收入 ----------------
-- SELECT e.Name AS '姓名',(s.Income -s.OutCome) AS '实际收入' FROM Employee e, salary s WHERE e.Num=s.Num
-- ---- 7.找出所有姓王的雇员的部门号(部门号不能重复显示) ---------------
-- SELECT DISTINCT Depno FROM Employee WHERE Name like '王%'
-- ------------- 8.找出所有收入在2000-3000元之间的雇员编号 -----------
-- SELECT e.Num ,e.Name from Employee e, salary s WHERE e.Num=s.Num and s.InCome between 2000 and 3000
-- ----------- 9. 查找在财务处工作的雇员的情况 ----------
-- SELECT * FROM Employee e, Department d, salary s WHERE e.Depno=d.Depno and e.Num=s.Num and d.DepName='财务部'
-- ------ 10. 查找在财务部且年龄不低于研发部任一个雇员年龄的雇员的姓名
--SELECT Name from Employee WHERE Depno=(SELECT Depno from Department WHERE DepName='财务部')
--and birth<=(SELECT min(birth) FROM Employee WHERE Depno=(SELECT Depno from Department WHERE DepName='研发部'))
-- --------- 11 查找比所有财务部雇员收入都高的雇员的姓名 --------
--select name from employee where num in(select num from salary where income>(select max(income) from salary where num in
--(select num from employee where depno=(select depno from department where depname='财务部') )))
-- --------------- 12. 查找每个雇员的情况及薪水情况 ----------
--SELECT Employee.*, InCome , OutCome FROM Employee , salary WHERE Employee.Num=salary.Num
-- ----------- 13. 查找财务部收入在2200元以上的雇员姓名及其薪水详细情况 ---
--SELECT e.Name,s.InCome, s.OutCome FROM Employee e, salary s
--WHERE e.Num=s.Num and s.InCome>2200 and Depno=(SELECT Depno FROM Department WHERE DepName='财务部')
-- ----------- 14. 求财务部雇员的平均实际收入 --------
--SELECT avg(InCome-OutCome) 财务部平均工资 from Department d, salary s WHERE d.DepName='财务部'
-- ------------------- 15. 求财务部雇员的总人数 --------------------------
--SELECT count(*) 财务部总人数 FROM Employee WHERE Depno=(SELECT Depno FROM Department WHERE depname='财务部')
-- ------- 16. 求各部门的雇员数(要求显示,部门号、部门名称和部门雇员数) ----
--SELECT d.Depno ,d.DepName ,COUNT(*) 部门总人数 FROM Employee e, Department d
--WHERE d.Depno=e.Depno GROUP BY d.Depno, d.DepName -- 必须同时GROUP BY Depno , DepName
-- 16. 求部门的平均薪水大于2500的部门信息(要求显示部门号、部门名称和平均工资)
SELECT d.Depno,DepName,avg(InCome) 部门平均薪水 FROM Employee e,Department d,salary s
WHERE e.depno=d.Depno and e.Num=s.Num group by d.Depno,DepName HAVING avg(InCome)>2500