中国石油大学华东数据库上机测试

只做出来下面这几道 我是废物

--1

select SNO
from SPJ
WHERE JNO='J1'
AND PNO IN(
SELECT PNO
FROM P
WHERE COLOR='红'
);

--2

CREATE TABLE SSC1 (
SNO INT ,
CNO INT ,
GRADE INT,
PRIMARY KEY(SNO,CNO),
FOREIGN KEY(SNO) REFERENCES SS(SNO),
FOREIGN KEY(CNO) REFERENCES SC(CNO)
)

--3
INSERT INTO GSPJ VALUES('S2','P4','J4',200)

--5

SELECT SNAME,DEPT,AGE 
FROM SS 
WHERE AGE BETWEEN 20 AND 23

--6

UPDATE blend
SET gdate='2015-7-1'
WHERE readno=(
select readno
from breader
where name='王平')

--8

select CNO, AVG(GRADE)
from SSC 
GROUP BY CNO
HAVING (AVG(GRADE)>=80)

--10

SELECT breader.readno,name,bookname,publish,ldate,gdate 
FROM breader,bbook,blend 
WHERE publish='机械工业出版社' AND bookname LIKE '%数据库%' AND bbook.bookno=blend.bookno AND breader.readno=blend.readno