SQL實驗(實驗4至實驗7的答案)

1、SQL實驗 實驗 4 1. 用 select 語句查詢 departments 和 salary 表中的所有數(shù)據(jù)： select salary .*, departments .* from salary , departments 2、查詢 departments 中的 departmentid ： select departmentid from departments go 3 、查詢 salary 中的 income ， outcome ： select income , outcome from salary go 4、查詢 employees 表中的部門號，性別，要用 distin

2、ct 消除重復行： select distinct ( departmentid ), sex from employees 5、查詢月收入高于 2000 元的員工號碼： select employeeid from salary where income 2000 go 6、查詢 1970 年以后出生的員工的姓名和住址： select name , address from employees where birthday 1970 go 7、查詢所有財務部的員工的號碼和姓名： select employeeid , name from employees where where depar

3、tmentid in( select departmentid from departments departmentname = 財務部 ) go 20003000 8、查詢 employees 員工的姓名，住址和收入水平， 2000 元以下顯示為低收入， 元顯示為中等收入， 3000 元以上顯示為高收入： select name , address , case when income - outcome 3000 then 高收入 else 中等收入 end as 收入等級 from employees , salary where = go 9、計算 salary 表中員工月收入的評價

4、數(shù)： select avg ( income ) as 平均收入 from salary 10、查找 employees 表中最大的員工號碼： select max( employeeid ) as 最大員工號碼 from employees 11、計算 salary 表中的所有員工的總支出： select sum( outcome ) as 總支出 from salary 12、查詢財務部雇員的最高實際收入： select max( income-outcome ) from salary , employees , departments where go = and = and depa

5、rtmentname = 財務部 13、查詢財務部雇員的最低實際收入： select min ( income-outcome ) from salary , employees , departments where go = and = and departmentname = 財務部 14、找出所用地址中含有“中山”的雇員的號碼及部門號： select employeeid , departmentid from employees where address like % 中山 % go 15、查找員工號碼中倒數(shù)第二個數(shù)字為0 的員工的姓名，地址和學歷： select educatio

6、n , address , name from employees where employeeid like %0_ go 16、使用 into 字句，由表 employees 創(chuàng)建“男員工 1”表，包括編號和姓名： select employeeid , name into 男員工表 from employees where sex =1 go 17、用子查詢的方法查找收入在2500 元以下的雇員的情況： select * from employees where employeeid in ( select employeeid from salary where income ALL

7、( SELECT InCome FROM Salary WHEREEmployeeID IN ( SELECT EmployeeId FROMEmployees WHEREDepartmentID IN ( SELECT DepartmentID FROM Departments WHEREDepartmentName = 財務部 ) ) ) ) 19、用子查詢的方法查找所有年齡比研發(fā)部雇員都大的雇員的姓名： select name from employees where Birthday 2500 ) 26、按部門列出在該部門工作的員工的人數(shù)： select departmentid ,

8、count (*) as 人數(shù) from employees group by departmentid 27、按員工的學歷分組： select education , count (*) as 人數(shù) from employees group by education 28、按員工的工作年份分組，統(tǒng)計年份人數(shù)： select workyear , count (*) as 人數(shù) from employees group by workyear 29、按各雇員的情況收入由低到高排列：select employees .* , from employees , salary where = ord

9、er by income 30、將員工信息按出生時間從小到大排列： select * from employees order by birthday 31、在 order by 字句中使用子查詢，查詢員工姓名，性別和工齡信息，要求按實際收入從 大到小排列： select name , sex , workyear , income - outcome from salary , employees where = order by income - outcome desc 視圖部分 1、創(chuàng)建 view1 ： Create view view1 as select , name, depar

10、tmentname ,( income - outcome ) as come from employees , departments , salary where = and = 2、查詢視圖 employeeid ： 3、向視圖 view1 中插入一行數(shù)據(jù)： 謊言 , 1 , 30000 ) insert into view1 values （ 111111 4、查看視圖（沒有影響）基本表： 實驗 5 1、 定義一個變量，用于描述 YGGL 數(shù)據(jù)庫的 查詢該變量： declare hy int set hy =( select income - outcome from salary w

11、here employeeid =000001 ) select hy 2、 使用運算符“ ”： select name salary 表中 000001 號員工的實際收入，然后 from employees where birthday 1974-10-10 3000 元，如果是則顯示“高收入” ，否則 3、判斷姓名為“王林”的員工實際收入是否高于 顯示“收入不高于 3000”： if ( select income from salary , employees where = and = 劉明 ) 3000 )select income as 高收入 from salary , empl

12、oyees where = and = 劉明 else select 收入不高于 4、使用循環(huán)輸出一個“ * ”三角形： declare i int declare j int set j =20 set i =1 while i 1 begin set j =j* i set i =i- 1 end return ( j) end declare h int exec h= 4 select h as jiecheng 7、/* 生成隨機數(shù) */ select rand () 8 、 /* 平方 */ select square ( 12) 9 、/* 求財務部收入最高的員工姓名*/ sel

13、ect max( name) from employees where employeeid in ( select employeeid from salary where employeeid in ( select employeeid from employees where departmentid in ( select departmentid from departments where departmentname = 財務部 ) select avg ( income ) as 平均收入 from salary /* 聚合函數(shù)與 group by 一起使用 */ selec

14、t workyear, count (*) as 人數(shù) from employees group by workyear /* 將字符組成字符串 */ select char ( 123 ) /* 返回字符串左邊開始的個字符 */ selectleft( abcdef , 2) /* 返回指定日期時間的天數(shù) */ selectday ( birthday) from employees where employeeid =010000 /* 獲取當前時間 */ select getdate () 實驗 6 1、創(chuàng)建索引： create unique index huangyan on empl

15、oyees ( employeeid ) 2 、 /* 用 create index 語句創(chuàng)建主鍵 */ 3 、重建表 employees 中 employeeid 列上的索引 alter index huangyan on employees rebuild 4 、刪除索引： 5 、創(chuàng)建一個新表，使用一個復合列作為主鍵，作為表的約束，并為其命名： create table employees5 ( employeeid char ( 6) not null, name char ( 5) not null, sex tinyint , education char ( 4), constra

16、int yan primary key ( employeeid , name ) ) 為新表添加一列： alter table employees5 add address char ( 10 ) 6 、創(chuàng)建新表 student ，性別只能包含男或女： create table student ( 號碼 char ( 6) not null, 性別 char ( 2)not null check (性別 in (男,女) ) 7 、創(chuàng)建新表： create table employees7 ( 學號 char ( 10 ) not null, 出生日期 datetime not null c

17、heck ( 出生日期 1980-01-01) employees = EmpId ) 8 、創(chuàng)建一個規(guī)則： 9 ，創(chuàng)建 salary2 ： create table salary2 ( employeeid char ( 6) not null primary key income float not null, outcome float not null, foreign key ( employeeid ) references salary ( employeeid ) on update cascade on delete cascade ) 10 、添加一個外鍵， salary

18、與 employees 有相關記錄，則拒絕更新 alter table salary add constraint kc_for foreign key ( employeeid ) references employees ( employeeid ) on delete no action on update no action 實驗 7 1、工作年份大于 6時，跟換科室到經(jīng)理辦公室 ( 根據(jù)員工 )： Create PROC UpdateDeptByYear ( EmpId char ( 6) ) AS BEGIN DECLARE year int SELECT year = WorkY

19、ear From Employees WHEREEmployeeID IF ( year 6) UPDATE Employees SET DepartmentID =3 WHEREEmployeeID = EmpId END EXEC UpdateDeptByYear 020010 SELECT * FROM EmployeesWHEREEmployeeid =020010 2、根據(jù)每個員工的學歷將收入提高元： CREATE PROC UpdateInComeByEdu Employeeid char ( 6) AS BEGIN UPDATE Salary SET InCome = InCom

20、e +500 FROM Salary LEFT JOIN Employees ON = WHERE = Employeeid END EXEC UpdateInComeByEdu 020010 SELECT * FROM Salarywhere EmployeeID = 020010 3、游標： CREATE PROCEDURE Employees_bili AS BEGIN DECLARE i FLOAT DECLARE j FLOAT DECLARE Education CHAR( 10) DECLARE Employees_cursor CURSOR FOR SELECT Educati

21、onFROM Employees SET i =0 SET j =0 OPEN Employees_cursor FETCH Employees_cursor INTO Education WHILE ( FETCH_STATUS=0) BEGIN IF ( Education != 大專 ) SET i =i+1 SET j =j+1 FETCH Employees_cursor INTO Education END CLOSE Employees_cursor SELECT i AS 本科及以上員工所占員工數(shù) SELECT j AS 員工總數(shù) SELECT i / j AS 本科及以上員工

22、所占比例 CLOSE Employees_cursor END EXEC Employees_bili 4、使用命令的方式修改存儲過程的定義： 列應 5 、對于 YGGL 數(shù)據(jù)庫，表 Employees 的 EmployeeID 列與表 Salary 的 EmployeeID 該滿足參照的完整性規(guī)則，請用觸發(fā)器實現(xiàn)兩個表的參照完整性： CREATE TRIGGER Salary_insertON Salary FOR INSERT , UPDATE AS BEGIN IF ( SELECT EmployeeIDFROM INSERTED ) NOT IN( SELECT EmployeeID

23、FROM Employees ) ROLLBACK END ON Employees CREATE TRIGGER Employeesupdate FOR UPDATE AS BEGIN UPDATE Salary SET EmployeeID =( SELECT EmployeeID FROMINSERTED ) WHEREEmployeeID =( SELECT EmployeeID FROM DELETED ) END CREATE TRIGGER EmployeesdeleteON Employees FOR DELETE AS BEGIN DELETE FROM Salary WHE

24、REEmployeeID =( SELECT EmployeeIDFROM DELETED ) END INSERT INTO Salary VALUES ( 000005 , 2000 , 1000 ) UPDATE Employees SET EmployeeID =000000 WHEREEmployeeID = 990230 DELETE FROM Employees WHEREEmployeeID =000000 6. 當修改表 Employees 時，若將 Employees 表中員工的工作時間增加 1 年，則將收入增加 500 ，若增加 2 年則增加 1000 ，依次增加。若工作

25、時間減少則無變化： CREATE TRIGGER EM_WORKYEAR ON Employees AFTER UPDATE AS BEGIN FROM INSERTED ) FROM DELETED ) DECLARE i INT , j INT SET i =( SELECT WorkYear SET j =( SELECT WorkYear IF ( ij) UPDATE Salary SET InCome = InCome +( i- j)* 500 WHEREEmployeeID IN( SELECT EmployeeID FROM INSERTED ) END UPDATE Emp

26、loyees SET WorkYear =7 WHEREEmployeeid =990230 SELECT * FROM Employees WHEREEmployeeid =990230 7. 創(chuàng)建 UPDATE 觸發(fā)器 , 當 Salary 表中 Income 值增加 500 時， outcome 值增加 50 ： CREATE TRIGGER SA_INCOME ON Salary FOR UPDATE AS BEGIN IF ( SELECT InCome FROM INSERTED )-( SELECT InCome FROMDELETED )= 500 ) UPDATE Salary SET OutCome =OutCome +50 WHEREEmployeeID =( SELECT EmployeeID FROM INSERTED ) END SELECT INCOME , OUTCOME FROM SALARY WHEREEMPLOYEEID = 504209 UPDATE Salary SET InCome = InCome +500 WHEREEmployeeID =