5.12

数据库实验报告二

SELECT  * FROM s ORDER BY city ASC,sname DESC

SELECT * FROM p ORDER BY pname ASC,weight DESC

SELECT * FROM j WHERE jname LIKE '%厂%'

SELECT  * FROM s WHERE sname LIKE '_方%'

SELECT MAX(weight) AS 最大重量

, MIN(weight) AS 最小重量

,AVG(weight) AS 平均重量 FROM P

SELECT COUNT(DISTINCT pno) AS 种类数 , AVG(weight) AS 平均质量 FROM p WHERE pname ='螺丝刀'

SELECT pno,qty from spj where sno='s1'

SELECT pno,qty from spj where jno='j1'

SELECT DISTINCT jname FROM j WHERE jno NOT IN (SELECT jno FROM spj INNER JOIN p ON spj.pno = p.pno WHERE p.pname = '螺丝刀' AND p.color = '红');

SELECT DISTINCT sname FROM s WHERE sno NOT IN (SELECT sno FROM spj INNER JOIN p ON spj.pno = p.pno WHERE p.pname = '螺丝刀' AND p.color = '红');

SELECT jno FROM spj GROUP BY jno HAVING SUM(qty) > 500;

SELECT j.jname FROM j INNER JOIN spj ON j.jno = spj.jno GROUP BY j.jname HAVING COUNT(DISTINCT spj.pno) > 3;

SELECT j.jname FROM j WHERE NOT EXISTS (SELECT pno FROM p WHERE pno NOT IN (SELECT pno FROM spj WHERE j.jno = spj.jno));

SELECT j.jname FROM j WHERE NOT EXISTS ( SELECT * FROM p WHERE NOT EXISTS ( SELECT * FROM spj WHERE spj.jno = j.jno AND spj.pno = p.pno ) )

SELECT s.sname FROM s WHERE NOT EXISTS (SELECT pno FROM j INNER JOIN spj ON j.jno = spj.jno WHERE j.jno = 'j1' AND pno NOT IN (SELECT pno FROM spj WHERE sno = s.sno));

SELECT s.sname, p.pname, j.jname, spj.qty FROM s INNER JOIN spj ON s.sno = spj.sno INNER JOIN p ON spj.pno = p.pno INNER JOIN j ON spj.jno = j.jno;

SELECT p.pname, j.jname, spj.qty FROM s INNER JOIN spj ON s.sno = spj.sno INNER JOIN p ON spj.pno = p.pno INNER JOIN j ON spj.jno = j.jno WHERE s.sname = '东方红';

GO

CREATE VIEW supply_view AS

SELECT sno, pno, qty

FROM spj

WHERE jno = 'j1';

GO

SELECT pno, SUM(qty) AS total_quantity

FROM supply_view

GROUP BY pno;

GO

SELECT pno, qty

FROM supply_view

WHERE sno = 's2';

在 SQL Server Management Studio (SSMS) 中，可以进行各种数据库管理的直接操作，包括但不限于：

1. 创建数据库：在 SSMS 中，可以通过图形界面或者 SQL 脚本来创建新的数据库。在 Object Explorer 中右键单击“Databases”，选择“New Database”并填写相关信息即可创建数据库。
2. 创建表：在创建数据库后，可以通过图形界面的“Tables”节点或者使用 SQL 脚本来创建新的表。在 Object Explorer 中选择目标数据库，右键单击“Tables”节点，选择“New Table”来创建新表。
3. 插入数据：可以通过 SQL 查询或者图形界面的“Edit Top 200 Rows”功能来插入数据到表中。
4. 查询数据：通过编写 SQL 查询语句或者使用图形界面的“New Query”功能来查询数据库中的数据。
5. 修改表结构：可以通过 ALTER TABLE 语句或者图形界面的“Design”功能来修改表结构，如添加列、删除列等操作。
6. 删除数据：可以通过 DELETE 语句或者使用图形界面的“Delete”功能来删除表中的数据。
7. 删除表：可以通过 DROP TABLE 语句或者在图形界面中右键单击目标表，选择“Delete”来删除表。
8. 删除数据库：可以通过 DROP DATABASE 语句或者在 Object Explorer 中右键单击目标数据库，选择“Delete”来删除数据库。