For big solutions

CodePudding user response:

Topic 1, I write the SQL statement, algebraic expressions, write their own oh
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 ` & gt; 5 

Topic 2:
1)

 CREATE TABLE Course (
Cno CHAR (3) NOT NULL, PRIMARY KEY, 
Cname VARCHAR (20), 
Total_perior INT, 
Credit INT, 
CHECK (Credit & gt; 0 AND Credit & lt;=6), 
CHECK (Total_perior & gt; 0) 
); 

2)

 SELECT Sno FROM sc 
WHERE Cno='002' AND Grade & gt; 80 
The ORDER BY Sno. 

3)

 SELECT * FROM student AS s 
WHERE s.S name LIKE '_ %' red 

4)

 SELECT t.S no, t.S name, Todd Harper ome_addr FROM student AS t 
INNER JOIN student AS st ON tc lassno=st. Classno 
WHERE st. Sname='zhang hong 

5)

 DELETE FROM sc WHERE sno='20110103' 

6)

 CREATE VIEW scview AS 
The SELECT s.S no, s.S name, SUM (sc) Grade) FROM the AS Grade student AS s 
INNER JOIN sc AS sc ON s.S no=sc. Sno 
GROUP BY Sno. 

7)

 DELIMITER//
CREATE PROCEDURE stu_pr (classnumber IN CHAR (3), IN studentname VARCHAR (8)) 
The BEGIN 
SELECT Sno, Sname, YEAR (NOW ()) - YEAR (Birth) FROM student WHERE Sname=studentname AND Classno=classnumber; 
END//