?? shjuku.sql
字號(hào):
CREATE TABLE person
( P_no Char(6) PRIMARY KEY,
P_name Varchar(10) Not Null,
Sex Char(2) Not Null,
BirthDate Datetime Null,
Prof Varchar(10) Null,
Deptno Char(4) Not Null,
)
CREATE TABLE salary
(P_no char(6) PRIMARY KEY,
Base dec(5,1) Null,
Bonus dec(5,1) Null CHECK(Bonus>=50),
Fact As Base+Bonus,
Month int Not Null,
CONSTRAINT P_no_key FOREIGN KEY(P_no)
REFERENCES person(P_no)
)
CREATE TABLE dept
(Deptno Char(4) PRIMARY KEY,
Dname Varchar(10) Not Null
)
CREATE VIEW PersonView
AS
SELECT P_no,P_name,sex,prof,deptno
FROM person
CREATE INDEX name_sort ON person(P_name)
CREATE INDEX birth_name ON person(birthdate,P_name)
CREATE UNIQUE INDEX u_name_sort ON person(P_name)
CREATE CLUSTERED INDEX fact_idx ON salary(Fact)
DROP INDEX salary.fact_idx
INSERT
INTO person (P_no,P_name,Sex,BirthDate,Prof,Deptno)
VALUES ('000001','王云','男','1973-4-7','中級(jí)','0001')
INSERT
INTO person (P_no,P_name,sex,BirthDate,Prof,Deptno)
VALUES('000002','謝志文','男','1975-2-14','中級(jí)','0001')
INSERT
INTO person (P_no,P_name,Sex,BirthDate,Prof,Deptno)
VALUES ('000003','李浩然','男','1970-8-25','高級(jí)','0002')
INSERT
INTO person (P_no,P_name,Sex,BirthDate,Prof,Deptno)
VALUES ('000004','廖小玲','女','1979-8-6','初級(jí)','0002')
INSERT
INTO person (P_no,P_name,Sex,BirthDate,Prof,Deptno)
VALUES ('000005','梁玉瓊','女','1970-8-25','中級(jí)','0003')
INSERT
INTO person (P_no,P_name,Sex,BirthDate,Prof,Deptno)
VALUES ('000006','羅向東','男','1979-5-11','初級(jí)','0003')
INSERT
INTO person (P_no,P_name,Sex,BirthDate,Prof,Deptno)
VALUES ('000007','肖家慶','男','1963-7-14','高級(jí)','0003')
INSERT
INTO salary(P_no,Base,Bonus,Month)
VALUES ('000001','2100','300','1')
INSERT
INTO salary(P_no,Base,Bonus,Month)
VALUES ('000002','1800','300','1')
INSERT
INTO salary(P_no,Base,Bonus,Month)
VALUES ('000003','2800','280','1')
INSERT
INTO salary(P_no,Base,Bonus,Month)
VALUES ('000004','2500','250','1')
INSERT
INTO salary(P_no,Base,Bonus,Month)
VALUES ('000005','2300','275','1')
INSERT
INTO salary(P_no,Base,Bonus,Month)
VALUES ('000006','1750','130','1')
INSERT
INTO salary(P_no,Base,Bonus,Month)
VALUES ('000007','2400','210','1')
INSERT
INTO dept(Deptno,Dname)
VALUES ('0001','人事部')
INSERT
INTO dept(Deptno,Dname)
VALUES ('0002','財(cái)務(wù)部')
INSERT
INTO dept(Deptno,Dname)
VALUES ('0003','市場(chǎng)部')
UPDATE salary
SET base=1800,bonus=160
WHERE P_no='000006'
DELETE FROM person WHERE P_no='000010'
UPDATE PersonView
SET Prof='高級(jí)'
WHERE P_name='王云'
INSERT INTO PersonView
VALUES('000008','劉美萍','女','中級(jí)','0002')
DROP VIEW PersonView
SELECT * FROM person
SELECT DISTINCT Prof FROM person
SELECT * FROM person WHERE Prof='中級(jí)'
SELECT P_name 姓名,Prof 職稱(chēng),birthdate 出生日期
FROM person
WHERE sex='男' AND Prof='高級(jí)'
SELECT * FROM person
WHERE P_name IN ('王云','謝志文','羅向東')
SELECT * FROM salary
WHERE P_no BETWEEN '000003' AND '000008'
ORDER BY Fact ASC
SELECT P_no 工號(hào) ,2*base+1.5*bonus 實(shí)際收入
FROM salary
WHERE P_no='000002'
SELECT deptno 部門(mén),SUM(Fact) 實(shí)發(fā)總數(shù)
FROM salary A,person B
WHERE A.P_no=B.P_no
GROUP BY deptno
SELECT deptno 部門(mén),AVG(bonus)平均獎(jiǎng)金
FROM salary A,person B
WHERE A.P_no=B.P_no
GROUP BY deptno
HAVING AVG(bonus)>200
ORDER BY AVG(bonus) ASC
SELECT * FROM person A,dept B
WHERE (A.deptno=B.deptno) AND dname='人事部'
SELECT DISTINCT A.P_name,B.P_name
FROM person A,person B
WHERE A.P_no<B.P_no AND A.prof=B.prof AND A.prof='中級(jí)'
SELECT P_name 員工姓名,base 工資,bonus 獎(jiǎng)金
FROM person AS A LEFT OUTER JOIN salary AS B
ON A.P_no=B.P_no
SELECT P_name 姓名,Fact 實(shí)發(fā)
FROM person A,salary B
WHERE A.P_no=B.P_no AND
B.Fact>(SELECT Fact FROM salary
WHERE P_no='000005')
SELECT A.P_name ,B.Fact
FROM person A,salary B
WHERE (A.P_no=B.P_no) AND
B.Fact>ALL(SELECT Fact FROM salary
WHERE deptno='0003')
SELECT P_no 工號(hào),Fact 實(shí)發(fā) FROM salary Main
WHERE Fact>(SELECT AVG(Fact) From salary
WHERE P_no=Main.P_no)
SELECT P_name 姓名,Prof 職稱(chēng)
FROM person
WHERE Prof='高級(jí)'
UNION
SELECT P_name 姓名,Prof 職稱(chēng)
FROM person
WHERE Prof='中級(jí)'
sp_addtype d_no,'char(2)','NOT NULL'
go
create table dept
( deptno d_no not null,
dname varchar(10) not null)
create function check_pno(@pno char(6))
returns integer as
begin
declare @num int
if exists (select p_no from person
where @pno=p_no)
select @num=0
else
select @num=-1
return @num
end
declare @num int
select @num=dbo.check_pno('000008')
if @num=0
insert salary values('000008',2200,280,'1')
create proc proc_age @code char(6),@age int output
as
declare @birth varchar(4),@today varchar(4)
select @birth=datename(year,birthday)
from person
where p_no=@code
select @today=datename(year,getdate())
select @age=convert(int,@today)-convert(int,@birth)
proc_age'000001'
declare @row_count int ,@male_count int,@female_count int
select @male_count=0,@female_count=0
select @row_count=count(*) from person
while(@row_count>0)
begin
begin transaction
if(person.sex='男') set @male_count=@male_count+1
else set @female_count=@female_count+1
set @row_count=@row_count-1
commit transaction
end
print '男職工共有'+cast(@male_count as char(6))+'人'
print '女職工共有'+cast(@female_count as char(6))+'人'?? 快捷鍵說(shuō)明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號(hào)
Ctrl + =
減小字號(hào)
Ctrl + -