① 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資料庫問題
create table TableX(Code varchar(10), Name varchar(50), Age varchar（10）);
d
select * from TableX where Name like '張%' order by Age asc;
select * from TableX, TableY where xCode=yCode and Class='計算機' and score<60;
select TableX.Name,TableY.Class,TableY.Score from TableX,TableY where xCode=yCode;
insert into TableX (xCode,Name,Age) values(『97005』，『趙六』，『20』);
update TableX set Age=21 where xCode='97004';
delete from TableX where xCode not in(select xCode from TableY);