求大佬解答
uj5u.com熱心網友回復：
題目1，我把SQL 陳述句寫出來 ，代數運算式，自己寫哦1)
SELECT s.`Sno`, s.`Sname`, s.`Classno` FROM Student AS s
WHERE s.`Sno` NOT IN (
SELECT Sno FROM sc AS sc WHERE sc.`Cno` = '001'
)
2)
SELECT DISTINCT(sc.`Sno`) FROM sc AS sc
INNER JOIN course AS c ON sc.`Cno` = c.`Cno`
WHERE c.`Credit` > 5
題目2：
1)
CREATE TABLE Course(
Cno CHAR(3) NOT NULL PRIMARY KEY,
Cname VARCHAR(20),
Total_perior INT,
Credit INT ,
CHECK(Credit > 0 AND Credit <= 6),
CHECK(Total_perior > 0)
);
2)
SELECT Sno FROM sc
WHERE Cno = '002' AND Grade > 80
ORDER BY Sno;
3)
SELECT * FROM student AS s
WHERE s.Sname LIKE '_紅%'
4)
SELECT t.Sno, t.Sname, t.Home_addr FROM student AS t
INNER JOIN student AS st ON t.Classno = st.Classno
WHERE st.Sname = '張紅'
5)
DELETE FROM sc WHERE sno = '20110103'
6)
CREATE VIEW scview AS
SELECT s.Sno,s.Sname, SUM(sc.Grade) AS 總成績 FROM student AS s
INNER JOIN sc AS sc ON s.Sno = sc.Sno
GROUP BY Sno;
7)
DELIMITER //
CREATE PROCEDURE stu_pr(IN classnumber CHAR(3), IN studentname VARCHAR(8))
BEGIN
SELECT Sno, Sname, YEAR(NOW()) - YEAR(Birth) FROM student WHERE Sname = studentname AND Classno = classnumber;
END //
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/16707.html
標籤：MySQL