数据库的知识忘干净了
时间：2021-07-01 10:21:17
帮助过：2人阅读
----------------------------------第一题--------------------------
DROP TABLE TB
DROP TABLE TA
create table TA
(
IDKey int primary key,
[Name] varchar(
20),
[Type] varchar(
20)
)
create table TB
(
DKey int primary key,
IDKey int references TA(IDKey),
[count] int,
)
insert into TA
values(
1,
‘aaa‘,
‘类型一‘)
insert into TA
values(
2,
‘bbb‘,
‘类型二‘)
insert into TA
values(
3,
‘ccc‘,
‘类型三‘)
insert into TB
values(
1,
1,
300)
insert into TB
values(
2,
1,
421)
insert into TB
values(
3,
2,
124)
insert into TB
values(
4,
1,
415)
DELETE TB
WHERE IDKey
IN (
SELECT IDKey
FROM TA
WHERE [Type]=‘类型一‘)
------------------------------------第二题--------------------------
create table TC
(
Guest int ,
[name] varchar(
20),
je int,
)
insert into TC
values(
1,
‘张三‘,
1000)
insert into TC
values(
2,
‘李四‘,
4000)
insert into TC
values(
3,
‘李四‘,
3000)
insert into TC
values(
4,
‘王武‘,
1000)
insert into TC
values(
5,
‘王武‘,
2000)
insert into TC
values(
6,
‘李四‘,
3000)
insert into TC
values(
7,
‘王武‘,
2000)
select [name] as 姓名,
count(
[name])
as 消费次数,
sum(je)
as 消费金额
from
TC group by [name] having count(
[name])
>2 order by sum(je)
desc
------------------------------------第三题--------------------------
drop table SC
drop table S
drop table C
create table S
(
s# INT PRIMARY KEY IDENTITY,
sn varchar(
20),
sd varchar(
20),
sa int
)
insert into S
values(
‘张三‘,
‘武软‘,
20)
insert into S
values(
‘李四‘,
‘武职‘,
10)
insert into S
values(
‘王武‘,
‘武软‘,
18)
insert into S
values(
‘赵六‘,
‘武软‘,
18)
create table C
(
c# VARCHAR(
20)
PRIMARY KEY,
cn varchar(
20),
)
insert into C
values(
‘C1‘,
‘税收基础‘)
insert into C
values(
‘C2‘,
‘JAVA‘)
insert into C
values(
‘C3‘,
‘SQL SERVER‘)
insert into C
values(
‘C4‘,
‘语文‘)
insert into C
values(
‘C5‘,
‘数学‘)
create table SC
(
S# INT REFERENCES S(s#),
C# VARCHAR(
20)
REFERENCES C(c#),
G INT
)
insert into SC
values(
1,
‘C1‘,
90)
insert into SC
values(
1,
‘C2‘,
100)
insert into SC
values(
1,
‘C3‘,
90)
insert into SC
values(
2,
‘C2‘,
100)
insert into SC
values(
3,
‘C3‘,
90)
--1
select s# 学号,sn 姓名
from s
where S#
in (
select S#
from SC
where C#
in (
select c#
from C
where cn
=‘税收基础‘))
--2
select sn 姓名,sd 单位
from S
where s#
in (
select s#
from SC
where C#
=‘C2‘)
--3
select sn 姓名,sd 单位
from S
where s#
in (
select s#
from SC
where C#
<>‘C5‘)
--4
select ‘选修了课程的学员人数为‘=COUNT(
*)
from S
WHERE S#
IN (
select count(S#)
from SC
group by S#)
--5
select sn 姓名,sd 单位
from S
where s#
in (
select S#
from SC
group by S#
having S#
>5)
--6
-----------------------------------------------------------------------------------------
select sn 姓名,sd 单位
from S
where s#
in (
select s#
from SC
where C#
in (
‘C1‘,
‘C2‘,
‘C3‘))
------------------------------------第四题--------------------------
create table TD
(
sfish char(
6)
primary key,
je money,
jsfs varchar(
20),
xj money
)
insert into TD
values (
‘NO001‘,
150,
‘现金‘,
150)
insert into TD
values (
‘NO002‘,
150,
‘刷卡‘,
0)
insert into TD
values (
‘NO003‘,
150,
‘刷卡‘,
150)
insert into TD
values (
‘NO004‘,
150,
‘支票‘,
0)
select sum(je)
from TD
--总金额
select ‘现金‘= SUM(xj),
‘刷卡‘=sum(je)
- SUM(xj)
-(
select sum(je)
from TD
group by jsfs
having jsfs
=‘支票‘),
‘支票‘=(
select sum(je)
from TD
group by jsfs
having jsfs
=‘支票‘),
‘总金额‘=sum(je)
from TD
数据库的知识忘干净了
标签：
