當前位置:首頁 » 編程語言 » sql問題大全
擴展閱讀
怎麼清除預覽圖片的緩存 2022-11-30 14:15:11
c語言創建有序鏈表 2022-11-30 14:08:06

sql問題大全

發布時間: 2022-11-23 07:23:51

sql問題 (50)

sql="select * from user,class "
這裡面的user和class都是表嗎?如果是,後面用addnew新增記錄肯定不行了.
應該單個表做新增.
否則寫SQL語句也可以,用insert into語句.

② SQL的一些問題

1.不太確定
2.if exists(select * from sysobjects where name='ST')
drop table st
go
create table st
(
SName char(10) not null,
Sno char(8),
SAge varchar(3),
SClass char(6),
Stelephone char(12),
SEmail char(20)
)
go

1)insert into st values('張三','01','20','','')
insert into st values ('李四','02','','計算機01','')

2)update st set asge=21 where sname='張三'
delete from st where sname='李四'
3)select sname as 姓名,sno as 學號 from st where sname='張三'

3.if exists(select * from sysobjects where name='tbiemployees')
drop table tbiemployees
go
create table tbiemployees
(
ieno char(4) unquire not null,
ieidentity char(18) unquire ,
iename varchar(20) ,
iepay money
)
go
if exists(select * from sysobjects where name='tbidepartments')
drop table tbidepartments
go
create table tbidepartments
(
idno char(2) unquire not null,
idname varchar(30)
)

go

if exists(select * from sysobjects where name='tbiwork')
drop table tbiwork
go
create table tbiwork
(
iwemployees char(4)
iwork varchar(20),
iwtime datetime,
iwdescrition varchar(30)
)
go
alter table tbiemployees
add constraint ck_pay check(iepay >0)
go

alter table tbiwork
add constraint fk_ieno foreign key (ieno) references tniwork(iwemployees )
go

③ 問幾個SQL的問題

1.
select
SNO
FROM
SC
A
WHERE
EXISTS
(SELECT
*
FROM
SC
B
WHERE
B.SNO=A.SNO
AND
B.CNO=1)
AND
EXISTS
(SELECT
*
FROM
SC
B
WHERE
B.SNO=A.SNO
AND
B.CNO=2)
AND
NOT
EXISTS
(SELECT
*
FROM
SC
B
WHERE
B.SNO=A.SNO
AND
B.CNO!=1
AND
B.CNO!=2)
2.
select
SNO
FROM
SC
A
WHERE
EXISTS
(SELECT
*
FROM
SC
B
WHERE
B.SNO=A.SNO
AND
B.CNO=1)
AND
EXISTS
(SELECT
*
FROM
SC
B
WHERE
B.SNO=A.SNO
AND
B.CNO=2)
ORDER
BY
SNO
DESC
3.
SELECT
CPNO
FROM
COURSE
GROUP
BY
CPNO
HAVING
COUNT(1)>=3
4.
SELECT
SNO,SNAME,SDEPT
FROM
STUDENT
WHERE
ISNULL((SELECT
SUM(CCREDIT)
FROM
COURSE,SC
WHERE
SC.SNO=STUDENT.SNO
AND
SC.CNO=COURSE.CNO),0)>6
5.
SELECT
SNO,CNAME
FROM
COURSE,SC
WHERE
COURSE.CNO=SC.CNO
AND
EXISTS
(SELECT
SNO,AVG(GRADE)
FROM
SC
A
WHERE
A.SNO=SC.SNO
HAVING
AVG(GRADE)<80)
======================================================================================
測試後的答案(哈哈,請不要抄龔,把錯誤也抄去了):
1.
select
distinct
SNO
FROM
SC
A
WHERE
EXISTS
(SELECT
*
FROM
SC
B
WHERE
B.SNO=A.SNO
AND
B.CNO=1)
AND
EXISTS
(SELECT
*
FROM
SC
B
WHERE
B.SNO=A.SNO
AND
B.CNO=2)
AND
NOT
EXISTS
(SELECT
*
FROM
SC
B
WHERE
B.SNO=A.SNO
AND
B.CNO!=1
AND
B.CNO!=2)
2.
select
distinct
SNO
FROM
SC
A
WHERE
EXISTS
(SELECT
*
FROM
SC
B
WHERE
B.SNO=A.SNO
AND
B.CNO=1)
AND
EXISTS
(SELECT
*
FROM
SC
B
WHERE
B.SNO=A.SNO
AND
B.CNO=2)
ORDER
BY
SNO
DESC
3.
SELECT
CPNO
FROM
COURSE
GROUP
BY
CPNO
HAVING
COUNT(1)>=3
and
cpno
!=''
and
cpno
is
not
null
4.
SELECT
SNO,SNAME,SDEPT
FROM
STUDENT
WHERE
ISNULL((SELECT
SUM(CCREDIT)
FROM
COURSE,SC
WHERE
SC.SNO=STUDENT.SNO
AND
SC.CNO=COURSE.CNO),0)>6
5.
SELECT
SNO,CNAME
FROM
COURSE,SC
WHERE
COURSE.CNO=SC.CNO
AND
EXISTS
(SELECT
SNO,AVG(GRADE)
FROM
SC
A
WHERE
a.SNO=SC.SNO
group
by
a.sno
HAVING
AVG(GRADE)<80)
====忙了半宿,答了五個問題,還測試了,最少要加200分啊!

④ SQL資料庫問題

  1. create table TableX(Code varchar(10), Name varchar(50), Age varchar(10));

  2. d

  3. select * from TableX where Name like '張%' order by Age asc;

  4. select * from TableX, TableY where xCode=yCode and Class='計算機' and score<60;

  5. select TableX.Name,TableY.Class,TableY.Score from TableX,TableY where xCode=yCode;

  6. insert into TableX (xCode,Name,Age) values(『97005』,『趙六』,『20』);

  7. update TableX set Age=21 where xCode='97004';

  8. delete from TableX where xCode not in(select xCode from TableY);