SQL2008实验2～实验4

实验2 4 删除YGGL USE master GO DROP DATABASE YGGL DROP DATABASE YGGL1 创建YGGL CREATE DATABASE YGGL ON NAME YGGL Data FILENAME c YGGL YGGL mdf SIZE 10MB MAXSIZE 50MB FILEGROWTH 5 LOG ON NAME YGGL Log FILENAME c YGGL YGGL Log ldf SIZE 2MB MAXSIZE 5MB FILEGROWTH 1MB GO 创建表 USE YGGL GO CREATE TABLE Employees EmployeeID char 6 NOT NULL PRIMARY KEY Name char 10 NOT NULL Education char 4 NOT NULL Birthday date NOT NULL Sex bit NOT NULL DEFAULT 1 WorkYear tinyint NULL Address varchar 40 NULL PhoneNumber char 12 NULL DepartmentID char 3 NOT NULL GO USE YGGL GO CREATE TABLE Departments DepartmentID char 3 NOT NULL PRIMARY KEY DepartmentName char 20 NOT NULL Note varchar 100 NULL GO USE YGGL GO CREATE TABLE Salary EmployeeID char 6 NOT NULL PRIMARY KEY InCome float NOT NULL OutCome float NOT NULL GO 创建YGGL1 CREATE DATABASE YGGL1 ON NAME YGGL1 Data FILENAME c YGGL YGGL1 mdf SIZE 10MB MAXSIZE 50MB FILEGROWTH 5 LOG ON NAME YGGL1 Log FILENAME c YGGL YGGL1 Log ldf SIZE 2MB MAXSIZE 5MB FILEGROWTH 1MB GO 修改YGGL1数据库逻辑文件的初始大小 ALTER DATABASE YGGL1 MODIFY FILE NAME YGGL1 Data SIZE 16MB GO 在YGGL1中创建表Salary1 多一列ActIncome 由InCome OutCome USE YGGL1 GO CREATE TABLE Salary1 EmployeeID char 6 NOT NULL PRIMARY KEY InCome float NOT NULL OutCome float NOT NULL ActIncome AS Income OutCome PERSISTED GO 在YGGL1中创建表Employees1 将Address删除 将Sex的默认值修改为0 USE YGGL1 GO CREATE TABLE Employees1 EmployeeID char 6 NOT NULL PRIMARY KEY Name char 10 NOT NULL Education char 4 NOT NULL Birthday date NOT NULL Sex bit NOT NULL DEFAULT 1 WorkYear tinyint NULL Address varchar 40 NULL PhoneNumber char 12 NULL DepartmentID char 3 NOT NULL GO ALTER TABLE Employees1 DROP COLUMN Address GO 输入数据 USE YGGL GO INSERT INTO Employees VALUES 000001 王林 大专 1966 01 23 1 8 中山路32 1 508 83355668 2 010008 伍容华 本科 1976 03 28 1 3 北京东路100 2 83321321 1 020010 王向容 硕士 1982 12 09 1 2 四牌楼10 0 108 83792361 1 020018 李丽 大专 1960 07 30 0 6 中山东路102 2 83413301 1 102201 刘明 本科 1972 10 18 1 3 虎距路100 2 83606608 5 102208 朱俊 硕士 1965 09 28 1 2 牌楼巷5 3 106 84708817 5 108991 钟敏 硕士 1979 08 10 0 4 中山路10 3 105 83346722 3 111006 张石兵 本科 1974 10 01 1 1 解放路34 1 203 84563418 5 210678 林涛 大专 1977 04 02 1 2 中山北路24 35 83467336 3 302566 李玉珉 本科 1968 09 20 1 3 热和路209 3 58765991 4 308759 叶凡 本科 1978 11 18 1 2 北京西路3 7 52 83308901 4 504209 陈林琳 大专 1969 09 03 0 5 汉中路120 4 12 84468158 4 GO USE YGGL GO INSERT INTO Departments VALUES 1 财务部 NULL 2 人力资源部 NULL 3 经理办公室 NULL 4 研发部 NULL 5 市场部 NULL GO USE YGGL GO INSERT INTO Salary VALUES 000001 2100 8 123 09 010008 1582 62 88 03 102201 2569 88 185 65 111006 1987 01 79 58 504209 2066 15 108 0 302566 2980 7 210 2 108991 3259 98 281 52 020010 2860 0 198 0 020018 2347 68 180 0 308759 2531 98 199 08 210678 2240 0 121 0 102208 1980 0 100 0 GO USE YGGL GO CREATE TABLE Employees2 EmployeeID char 6 NOT NULL PRIMARY KEY Name char 10 NOT NULL Education char 4 NOT NULL Birthday date NOT NULL Sex bit NOT NULL DEFAULT 1 WorkYear tinyint NULL Address varchar 40 NULL PhoneNumber char 12 NULL DepartmentID char 3 NOT NULL GO 这一行有错误 USE YGGL GO INSERT INTO Employees2 SELECT FROM Employees GO UPDATE Sallary SET InCome 2890 WHERE EmployeeID 000001 UPDATE Salary SET InCome InCome 100 DELETE FROM Employees WHERE EmployeeID 000001 DELETE FROM Employees WHERE Sex 0 TRUNCATE TABLE Salary 清空表内容 USE YGGL GO CREATE TABLE Employees3 EmployeeID char 6 NOT NULL PRIMARY KEY Name char 10 NOT NULL Education char 4 NOT NULL Birthday date NOT NULL Sex bit NOT NULL DEFAULT 1 WorkYear tinyint NULL Address varchar 40 NULL PhoneNumber char 12 NULL DepartmentID char 3 NOT NULL GO MERGE INTO Employees3 USING Employees ON Employees3 EmployeeID Employees EmployeeID WHEN MATCHED THEN UPDATE SET Employees3 Name Employees Name Employees3 Education Employees Education Employees3 Birthday Employees Birthday Employees3 Sex Employees Sex Employees3 WorkYear Employees WorkYear Employees3 Address Employees Address Employees3 PhoneNumber Employees PhoneNumber Employees3 DepartmentID Employees DepartmentID WHEN NOT MATCHED THEN INSERT VALUES Employees EmployeeID Employees Name Employees Education Employees Birthday Employees Sex Employees WorkYear Employees Address Employees PhoneNumber Employees DepartmentID WHEN NOT MATCHED BY SOURCE THEN DELETE 用SELECT语句查询Departments Salary USE YGGL GO SELECT FROM Employees GO USE YGGL GO SELECT FROM Departments USE YGGL GO SELECT FROM Salary 查询Employees表中每个雇员的地址和电话 USE YGGL GO SELECT Name Address PhoneNumber FROM Employees 查询Salary中的OutCome USE YGGL GO SELECT OutCome FROM Salary 查询Employees中的部门号和性别 要求使用DISTINCT消除重复行 USE YGGL GO SELECT DISTINCT DepartmentID Sex FROM Employees 查询月收入高于2000元的员工号码 USE YGGL GO SELECT EmployeeID FROM Salary WHERE InCome 2000 查询1970年以后出生的员工的姓名和住址 USE YGGL GO SELECT EmployeeID Name FROM Employees WHERE Birthday 1970 1 1 查询所有财务部员工的号码和姓名 USE YGGL GO SELECT EmployeeID Name FROM Employees WHERE DepartmentID SELECT DepartmentID FROM Departments WHERE DepartmentName 财务部 查询Employees表中男员工的姓名和出生日期 要求将各列标题用中文表示 USE YGGL GO SELECT Name AS 姓名 Birthday AS 出生日期 FROM Employees WHERE Sex 1 查询Employees员工的姓名 住址和收入水平 2000元以下显示为低收入 2000 3000元显示为中等收入 3000元以上为高收入 USE YGGL GO SELECT Name AS 姓名 Address AS 地址 CASE WHEN InCome 3000 THEN 高收入 END AS 收入水平 FROM Employees Salary 使用SELECT语句进行简单的计算 USE YGGL GO SELECT EmployeeID 总收入 InCome OutCome FROM Salary 计算Salary表中员工月收入的平均数 USE YGGL GO SELECT 员工月收入平均 AVG InCome FROM Salary 获得Employees表中最大的员工号码 USE YGGL GO SELECT 最大员工号码 MAX EmployeeID FROM Employees 计算Salary表中所有员工的总支出 USE YGGL GO SELECT SUM OutCome AS 所有员工总支出 FROM Salary GO 查询财务部雇员的最高和最低实际收入 USE YGGL GO SELECT MAX InCome AS 最高收入 MIN InCome AS 最低收入 FROM Employees Salary WHERE DepartmentID SELECT DepartmentID FROM Departments WHERE DepartmentName 财务部 GO 找出所有其地址中含有 中山 的雇员的号码及部门号 USE YGGL GO SELECT EmployeeID AS 号码 DepartmentID AS 部门号 FROM Employees WHERE Address LIKE 中山 GO 查找员工号码中倒数第二个数字为0的员工的姓名 地址和学历 USE YGGL GO SELECT Name Address Education FROM Employees WHERE EmployeeID LIKE 0 GO 找出所有在部门 1 或 2 工作的雇员的号码 USE YGGL GO SELECT EmployeeID FROM Employees WHERE DepartmentID BETWEEN 1 AND 2 WHERE DepartmentID 1 OR DepartmentID 2 GO 使用INTO子句 由表Employees创建 男员工 表 包括编号和姓名 USE YGGL GO SELECT EmployeeID Name INTO 男员工 FROM Employees WHERE Sex 1 GO USE YGGL GO SELECT FROM 男员工 GO 用子查询的方法查找所有收入在2500元以下的雇员的情况 USE YGGL GO SELECT FROM Employees WHERE EmployeeID IN 不能用等于 否则出错 SELECT EmployeeID FROM Salary WHERE InCome ALL SELECT InCome FROM Salary WHERE EmployeeID IN SELECT EmployeeID FROM Employees WHERE DepartmentID IN SELECT DepartmentID FROM Departments WHERE DepartmentName 财务部 用子查询的方法查找所有年龄比研发部雇员年龄都大的雇员的姓名 USE YGGL GO SELECT NAME FROM Employees WHERE Birthday IN SELECT Birthday FROM Employees WHERE DepartmentID IN SELECT DepartmentID FROM Departments WHERE DepartmentName 研发部 AND Birthday SELECT MIN Birthday FROM Employees WHERE DepartmentID IN SELECT DepartmentID FROM Departments WHERE DepartmentName 研发部 查询每个雇员的情况及其工作部门的情况 USE YGGL GO SELECT Employees Departments FROM Employees Departments WHERE Employees DepartmentID Departments DepartmentID 使用内连接方法查找出不在财务部工作的所有员工信息 USE YGGL GO SELECT Employees FROM Employees INNER JOIN Departments ON Employees DepartmentID Departments DepartmentID WHERE Departments DepartmentName 财务部 使用外连接方法查找出所有员工的月收入 USE YGGL GO SELECT Employees Name AS 姓名 Salary InCome AS 收入 FROM Employees LEFT OUTER JOIN Salary ON Employees EmployeeID Salary EmployeeID 查询研发部在1976年以前出生的雇员姓名及其薪水详情 USE YGGL GO SELECT Name InCome FROM Employees JOIN Salary ON Employees EmployeeID Salary EmployeeID JOIN Departments ON Employees DepartmentID Departments DepartmentID WHERE DepartmentName 研发部 AND Birthday 2500 按部门列出在该部门工作的员工的人数 USE YGGL GO SELECT DepartmentID AS 部门号 COUNT Employees EmployeeID AS 人数 FROM Employees GROUP BY DepartmentID 按员工的学历分组 排列出本科 大专和硕士的人数 USE YGGL G