SQL2008实验2～实验4.doc

/*实验24*/*删除YGGL*/USE master GODROP DATABASE YGGLDROP DATABASE YGGL1/*创建YGGL*/CREATE DATABASE YGGLON(NAME=YGGL_Data,FILENAME=c:YGGLYGGL.mdf,SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=5%)LOG ON(NAME=YGGL_Log,FILENAME=c:YGGLYGGL_Log.ldf,SIZE=2MB,MAXSIZE=5MB,FILEGROWTH=1MB)GO/*创建表*/USE YGGLGOCREATE TABLE Employees(EmployeeID char(6) NOT NULL PRIMARY KEY,Namechar(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)GOUSE YGGL GOCREATE TABLE Departments(DepartmentID char(3) NOT NULL PRIMARY KEY,DepartmentName char(20) NOT NULL,Note varchar(100) NULL)GOUSE YGGL GOCREATE TABLE Salary(EmployeeID char(6) NOT NULL PRIMARY KEY,InCome float NOT NULL,OutCome float NOT NULL)GO/*创建YGGL1*/CREATE DATABASE YGGL1ON(NAME=YGGL1_Data,FILENAME=c:YGGLYGGL1.mdf,SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=5%)LOG ON(NAME=YGGL1_Log,FILENAME=c:YGGLYGGL1_Log.ldf,SIZE=2MB,MAXSIZE=5MB,FILEGROWTH=1MB)GO/*修改YGGL1数据库逻辑文件的初始大小*/ALTER DATABASE YGGL1MODIFY FILE(NAME=YGGL1_Data,SIZE=16MB)GO/*在YGGL1中创建表Salary1，多一列ActIncome，由InCome-OutCome*/USE YGGL1 GOCREATE 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 GOCREATE 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)GOALTER TABLE Employees1DROP COLUMN AddressGO/*输入数据*/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)GOUSE YGGLGOINSERT INTO Departments VALUES(1,财务部,NULL), (2,人力资源部,NULL), (3,经理办公室,NULL), (4,研发部,NULL), (5,市场部,NULL)GOUSE YGGL GOINSERT 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)GOUSE YGGLGOCREATE 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 YGGLGOINSERT INTO Employees2 SELECT * FROM EmployeesGOUPDATE SallarySET InCome=2890WHERE EmployeeID=000001UPDATE SalarySET InCome=InCome+100;DELETE FROM EmployeesWHERE EmployeeID=000001DELETE FROM EmployeesWHERE Sex=0TRUNCATE TABLE Salary /*清空表内容*/USE YGGLGOCREATE 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)GOMERGE INTO Employees3USING Employees ON Employees3.EmployeeID=Employees.EmployeeIDWHEN MATCHEDTHEN 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.DepartmentIDWHEN NOT MATCHEDTHEN INSERT VALUES(Employees.EmployeeID,Employees.Name,Employees.Education,Employees.Birthday,Employees.Sex,Employees.WorkYear,Employees.Address,Employees.PhoneNumber,Employees.DepartmentID)WHEN NOT MATCHED BY SOURCETHEN DELETE; /*用SELECT语句查询Departments,Salary*/USE YGGL GOSELECT *FROM Employees GO USE YGGL GOSELECT *FROM DepartmentsUSE YGGLGO SELECT *FROM Salary/*查询Employees表中每个雇员的地址和电话*/USE YGGL GOSELECT 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 YGGLGO SELECT EmployeeID FROM Salary WHERE InCome 2000/*查询1970年以后出生的员工的姓名和住址*/USE YGGL GO SELECT EmployeeID,Name FROM Employees WHERE Birthday 1970-1-1/*查询所有财务部员工的号码和姓名*/USE YGGLGO SELECT EmployeeID ,Name FROM Employees WHERE DepartmentID =(SELECT DepartmentID FROM Departments WHERE DepartmentName =财务部)/*查询Employees表中男员工的姓名和出生日期，要求将各列标题用中文表示*/USE YGGL GOSELECT Name AS 姓名,Birthday AS 出生日期FROM Employees WHERE Sex=1/*查询Employees员工的姓名，住址和收入水平，2000元以下显示为低收入， 20003000元显示为中等收入， 3000元以上为高收入*/USE YGGL GO SELECT Name AS 姓名,Address AS 地址,CASEWHEN 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 GOSELECT 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 =1GOUSE 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 YGGLGOSELECT NAMEFROM Employees WHERE Birthday IN(SELECT BirthdayFROM 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 YGGLGO 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 YGGLGOSELECT Employees .Name AS 姓名,Salary .InCome AS 收入FROM Employees LEFT OUTER JOIN Salary ON Employees .EmployeeID =Salary .EmployeeID /*查询研发部在1976年以前出生的雇员姓名及其薪水详情*/USE YGGLGOSELECT Name ,InCome FROM (Employees JOIN Salary ON Employees.EmployeeID =Salary .EmployeeID) JOIN Departments ON Employees .DepartmentID =Departments .DepartmentID WHERE DepartmentName =研发部AND Birthday 2500)/*按部门列出在该部门工作的员工的人数*/USE YGGLGOSELECT DepartmentID AS 部门号,COUNT(Employees .EmployeeID) AS 人数FROM EmployeesGROUP BY DepartmentID /*按员工的学历分组，排列出本科，大专和硕士的人数*/USE YGGLGOSELECT Education