SQL 新建查询

SQL1
create database test2015
go
use test2015
go
create table EMPLOYEE
(
EmpNo char(6) not null,
EmpName varchar(50) not null,
EmpSex char(2) null,
EmpAge int null,
constraint PK_EMPLOYEE primary key (EmpNo)

)
go

create table COMPANY
(
CmpNo char(6) not null,
CmpName varchar(50) not null,
constraint PK_COMPANY primary key (CmpNo)
)

go

create table WORKS
(
EmpNo char(6) not null,
CmpNo char(6) not null,
Salary Numeric(8,2) default (0),
constraint PK_WORKS primary key (EmpNo,CmpNo),
constraint PK_WORKS_EMPLOYEE foreign key (EmpNo) references EMPLOYEE (EmpNo),
constraint PK_WORKS_COMPANY foreign key (CmpNo) references COMPANY (CmpNo)
)

go

insert into EMPLOYEE values (‘E01’,‘张三’,‘女’,‘32’)
insert into EMPLOYEE values (‘E02’,‘李四’,‘男’,‘28’)
insert into EMPLOYEE values (‘E03’,‘王五’,‘女’,‘42’)
insert into EMPLOYEE values (‘E04’,‘赵六’,‘男’,‘37’)
insert into EMPLOYEE values (‘E05’,‘陈七’,‘男’,‘51’)

go

insert into COMPANY values (‘C01’,‘阳光科技’)
insert into COMPANY values (‘C02’,‘晨光科技’)
insert into COMPANY values (‘C03’,‘未来科技’)

go

insert into WORKS values (‘E01’,‘C01’,‘3000’)
insert into WORKS values (‘E01’,‘C02’,‘4000’)
insert into WORKS values (‘E02’,‘C02’,‘5000’)
insert into WORKS values (‘E02’,‘C03’,‘2500’)
insert into WORKS values (‘E03’,‘C01’,‘3500’)
insert into WORKS values (‘E04’,‘C02’,‘3000’)
insert into WORKS values (‘E05’,‘C03’,‘2000’)

go

SQL2
INSERT INTO [test2015].[dbo].[EMPLOYEE]
([EmpNo]
,[EmpName]
,[EmpSex]
,[EmpAge])
VALUES
(@EmpNo,@EmpName,@EmpSex,@EmpAge)
GO

UPDATE [test2015].[dbo].[EMPLOYEE]
SET [EmpName] = @EmpName
,[EmpSex] = @EmpSex
,[EmpAge] = @EmpAge
WHERE [EmpNo] = @EmpNo
GO

DELETE FROM [test2015].[dbo].[EMPLOYEE]
WHERE [EmpNo] = @EmpNo
GO

SELECT [EmpNo]
,[EmpName]
,[EmpSex]
,[EmpAge]
FROM [test2015].[dbo].[EMPLOYEE]
GO

select c.EmpName 员工姓名,b.CmpName 公司名,a.Salary 薪水
from WORKS a,COMPANY b,EMPLOYEE c
where a.CmpNo=b.CmpNo and c.EmpNo=a.EmpNo and c.EmpNo=@EmpNo

select b.EmpName,SUM(Salary) 薪水
from WORKS a,EMPLOYEE b
where a.EmpNo=b.EmpNo and b.EmpAge>=40
group by b.EmpNo ,b.EmpName
order by 薪水 desc

select c.EmpName 员工姓名,b.CmpName 公司名
from WORKS a,COMPANY b,EMPLOYEE c
where a.CmpNo=b.CmpNo and c.EmpNo=a.EmpNo and c.EmpNo in(select EmpNo
from WORKS
group by EmpNo
having COUNT(*)>1)