shjuku.sql

该数据库包括三个表：Person、salary

  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','中级','0001')

INSERT
INTO person (P_no,P_name,sex,BirthDate,Prof,Deptno)
VALUES('000002','谢志文','男','1975-2-14','中级','0001')

INSERT
INTO person (P_no,P_name,Sex,BirthDate,Prof,Deptno)
VALUES ('000003','李浩然','男','1970-8-25','高级','0002')

INSERT
INTO person (P_no,P_name,Sex,BirthDate,Prof,Deptno)
VALUES ('000004','廖小玲','女','1979-8-6','初级','0002')

INSERT
INTO person (P_no,P_name,Sex,BirthDate,Prof,Deptno)
VALUES ('000005','梁玉琼','女','1970-8-25','中级','0003')

INSERT
INTO person (P_no,P_name,Sex,BirthDate,Prof,Deptno)
VALUES ('000006','罗向东','男','1979-5-11','初级','0003')

INSERT
INTO person (P_no,P_name,Sex,BirthDate,Prof,Deptno)
VALUES ('000007','肖家庆','男','1963-7-14','高级','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','财务部')
INSERT
INTO    dept(Deptno,Dname)
VALUES ('0003','市场部')



UPDATE salary
SET base=1800,bonus=160
WHERE P_no='000006'



DELETE FROM person WHERE P_no='000010'



UPDATE PersonView
SET  Prof='高级'
WHERE P_name='王云'



INSERT INTO PersonView
VALUES('000008','刘美萍','女','中级','0002')




DROP VIEW PersonView



SELECT * FROM person




SELECT DISTINCT Prof  FROM person
SELECT * FROM person WHERE Prof='中级'
SELECT P_name 姓名,Prof 职称,birthdate 出生日期
FROM person
WHERE sex='男' AND Prof='高级'
SELECT * FROM person
WHERE P_name IN ('王云','谢志文','罗向东')




SELECT * FROM salary
WHERE P_no BETWEEN '000003' AND '000008'
ORDER BY Fact ASC



SELECT P_no 工号 ,2*base+1.5*bonus 实际收入
FROM salary
WHERE P_no='000002'




SELECT deptno 部门,SUM(Fact) 实发总数
FROM salary A,person B
WHERE A.P_no=B.P_no
GROUP BY deptno



SELECT deptno 部门,AVG(bonus)平均奖金
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='中级'




SELECT P_name 员工姓名,base 工资,bonus 奖金
FROM person AS A LEFT OUTER JOIN salary AS B 
ON A.P_no=B.P_no




SELECT P_name 姓名,Fact 实发
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 工号,Fact 实发 FROM salary Main
WHERE Fact>(SELECT AVG(Fact) From salary
            WHERE P_no=Main.P_no)




SELECT P_name 姓名,Prof 职称
FROM person
WHERE Prof='高级'
UNION
SELECT P_name 姓名,Prof 职称
FROM person
WHERE Prof='中级'

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))+'人'