① 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);