数据库(SQL Server )经典例题(一):对S表、P表、J表、SPJ表的操作——数据库的创建

例题介绍:

供应商表 S 由供应商代码(SNO)、供应商姓名(SNAME)、供应商状态(STATUS)、供应商所在城市(CITY)组成。

零件表P由零件代码(PNO)、零件名(PNAME)、颜色(COLOR)、重量(WEIGHT)组成。

工程项目表J由工程项目代码(JNO)、工程项目名(JNAME)、工程项目所在城市(CITY)组成。

供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成,表示某供应商供应某种零件给某工程项目的数量为QTY。

    S (SNO,SNAME,STATUS,CITY) ;
    P (PNO,PNAME,COLOR,WEIGHT) ;
    J (JNO,JNAME,CITY) ;
    SPJ (SNO,PNO,JINO,QTY) ;

1.创建,S,P,J,SPJ四个表

  CREATE TABLE S
  (SNO CHAR(2) PRIMARY KEY,
  SNAME CHAR(6),
  STATUS SMALLINT,
  CITY CHAR(4),
  );
  
  CREATE TABLE P
  (PNO CHAR(2) PRIMARY KEY,
  PNAME CHAR(10),
  COLOR CHAR(2),
  WEIGHT smallINT check(WEIGHT BETWEEN 1 AND 50)
  );
  
  CREATE TABLE J
  (JNO CHAR(2) PRIMARY KEY,
  JNAME CHAR(10) UNIQUE NOT NULL,
  CITY  CHAR(4),
  );
  
  CREATE TABLE SPJ
  (SNO CHAR(2),
  PNO CHAR(2),
  JNO CHAR(2),
  QTY INT,
  PRIMARY KEY(SN0,PNO,JNO),
  FOREIGN KEY(SNO)REFERENCES S(SNO)
  FOREIGN KEY(PNO)REFERENCES P(PNO)
  FOREING KEY(JNO)REFERENCES 
J(JNO)

插入数据 S表

  INSERT INTO S
  VALUES('S1','精益',20,'天津');
  INSERT INTO S
  VALUES('S2','盛锡',10,'北京');
  INSERT INTO S
  VALUES('S3','东方红',30,'北京');
  INSERT INTO S
  VALUES('S4','丰盛泰',20,'天津');
  INSERT INTO S
  VALUES('S5','为民',30,'上海');

插入数据 P表

  INSERT INTO P
  VALUES('P1','螺母','红',12);
  INSERT INTO P
  VALUES('P2','螺栓','绿',17);
  INSERT INTO P
  VALUES('P3','螺丝刀','蓝',14);
  INSERT INTO P 
  VALUES('P4','螺丝刀','红',14);
  INSERT INTO P
  VALUES('P5','凸轮','蓝',40);
  INSERT INTO P
  VALUES('P6','齿轮','红',30);

插入数据 J表

  INSERT INTO J
  VALUES('J1','三建','北京');
  INSERT INTO J
  VALUES('J2','一汽','长春');
  INSERT INTO J
  VALUES('J3','弹簧厂','天津');
  INSERT INTO J
  VALUES('J4','造船厂','天津');
  INSERT INTO J
  VALUES('J5','机车厂','唐山');
  INSERT INTO J
  VALUES('J6','无线电厂','常州');
  INSERT INTO J
  VALUES('J7','半导体厂','南京');

插入数据 SPJ表

  INSERT INTO SPJ 
  VALUES('S1','P1','J1',200);
  INSERT INTO SPJ 
  VALUES('S1','P1','J3',100);
  INSERT INTO SPJ 
  VALUES('S1','P1','J4',700);
  INSERT INTO SPJ 
  VALUES('S1','P2','J2',100);
  INSERT INTO SPJ 
  VALUES('S2','P3','J1',400);
  INSERT INTO SPJ 
  VALUES('S2','P3','J2',200);
  INSERT INTO SPJ 
  VALUES('S2','P3','J4',500);
  INSERT INTO SPJ 
  VALUES('S2','P3','J5',400);
  INSERT INTO SPJ 
  VALUES('S2','P5','J1',400);
  INSERT INTO SPJ 
  VALUES('S2','P5','J2',100);
  INSERT INTO SPJ 
  VALUES('S3','P1','J1',200);
  INSERT INTO SPJ 
  VALUES('S3','P3','J1',200);
  INSERT INTO SPJ
  VALUES('S4','P5','J1',100);
  INSERT INTO SPJ
  VALUES('S4','P6','J3',300);
  INSERT INTO SPJ
  VALUES('S4','P6','J4',200);
  INSERT INTO SPJ
  VALUES('S5','P2','J4',100);
  INSERT INTO SPJ
  VALUES('S5','P3','J1',200);
  INSERT INTO SPJ
  VALUES('S5','P6','J2',200);
  INSERT INTO SPJ
  VALUES('S5','P6','J4',500);

2.修改表的结构

用SQL语句完成以下操作:

(1)给S表增加Sphone和Semail两个属性列,分别用来存放供应商的联系电话和电子信箱。

use gongcheng
ALTER TABLE S ADD Sphone int;
use gongcheng
ALTER TABLE S ADD Semail char(20);

(2)删除Jname属性列取值唯一的约束。

use gongcheng
ALTER TABLE J ADD CONSTRAINT uinqueJName UNIQUE(JName);//增加唯一性约束
use gongcheng
ALTER TABLE J DROP CONSTRAINT uinqueJName;//删除唯一性约束

(3)将QTY属性列的数据类型修改为Smallint型。

USE gongcheng
ALTER TABLE SPJ ALTER COLUMN QTY SMALLINT;

(4)删除S表中的属性列Semail

use gongcheng
ALTER TABLE S
DROP COLUMN Semail;

3.创建、删除索引

通过SQL语句分别在表S、P、J表中的Sno,Pno,Jno属性列上建立唯一索引(升序)

use gongcheng
CREATE UNIQUE INDEX SSno ON S(SNo);
CREATE UNIQUE INDEX PPno ON P(PNo);
CREATE UNIQUE INDEX JJno ON J(JNO);

4.在“gongcheng”数据库中完成以下查询

(1)查询所有供应商所在的城市。

USE gongcheng
SELECT City
FROM S;

(2)查询零件重量在10-20之间(包括10和20)的零件名和颜色。

USE gongcheng
SELECT PNo,Color
FROM P
WHERE Weight BETWEEN 10 AND 20;

(3)查询工程项目的总个数。

USE gongcheng
SELECT COUNT(JNO)
FROM J;

(4)查询所有零件的平均重量。

USE gongcheng
SELECT AVG(Weight)
FROM P;

(5)查询供应商S3供应的零件号。

USE gongcheng
SELECT PNo
FROM SPJ
WHERE  SNo='S3';

(6)查询各个供应商号及其供应了多少类零件。

use gongcheng
SELECT SNO,COUNT( DISTINCT PNo)
FROM SPJ
GROUP BY SNO;

(7)查询供应了2类以上零件的供应商号。

use gongcheng
SELECT SNO
FROM SPJ
GROUP BY SNO
HAVING COUNT(DISTINCT PNO)>2; 

(8)查询零件名以“螺”字开头的零件信息。

use gongcheng
SELECT*
FROM P
WHERE PName LIKE '螺%';

(9)查询工程项目名中最后一个字为“厂”字的工程项目所在的城市。

use gongcheng
SELECT  DISTINCT City
FROM J
WHERE JNAME LIKE '%厂';

(10)查询给每个工程供应零件的供应商的个数。

use gongcheng
SELECT JNO,COUNT( DISTINCT SNO)
FROM SPJ
GROUP BY JNO;

(12)完成课本P127页第5题的前七个小题。

1.找出所有的供应商的姓名和所在城市。

use gongcheng
SELECT SName,City
FROM S;

2.找出所有零件的名称,颜色,重量。

use gongcheng
SELECT PName,Color,Weight
FROM p;

3.找出使用供应商S1所供应零件的工程号码

use gongcheng
SELECT JNO
FROM SPJ
WHERE SNO='S1';

4.找出工程项目J2使用的各种零件的名称及其数量

USE gongcheng
SELECT P.PName,SPJ.QTY
FROM P,SPJ
WHERE P.PNo=SPJ.PNO AND SPJ.JNO='J2';

5.找出上海厂商供应的所有零件号码

USE gongcheng
SELECT  DISTINCT SPJ.PNO
FROM S,SPJ
WHERE S.SNO=SPJ.SNO AND City='上海';
//嵌套查询
USE gongcheng
SELECT  DISTINCT SPJ.PNO
FROM SPJ
WHERE SNO IN
(SELECT SNO
FROM S
WHERE City='上海'
);

6.找出使用上海产的零件的工程名称。

USE gongcheng
SELECT JName
FROM J
WHERE JNO IN
(
SELECT  DISTINCT SPJ.JNO
FROM SPJ
WHERE SNO IN
(SELECT SNO
FROM S
WHERE City='上海'
)
);
//不使用嵌套
USE gongcheng
SELECT DISTINCT JName
from J,SPJ,S
WHERE J.JNO=SPJ.JNO
AND  SPJ.SNO=S.SNO
AND  S.City='上海';

7.找出没有使用天津产的零件的工程号码。

//方法一
USE gongcheng
SELECT JNO
FROM J
WHERE NOT EXISTS
(
SELECT *
FROM SPJ
WHERE SPJ.JNO=J.JNO
AND SNO IN
(
SELECT SNO  
FROM S
WHERE City='天津'
)
)
//方法二
USE gongcheng
SELECT JNO
FROM J
WHERE NOT EXISTS
(
SELECT *
FROM SPJ,S
WHERE SPJ.JNO=J.JNO
AND SPJ.SNO=S.SNO
AND City='天津'
);

(13)现有一供应商,代码为S9、姓名为英特尔、所在城市西安,供应情况如下:供应零件P5给工程J7数量为600,供应零件P4给工程J4数量为500,请将此供应商的信息和供应信息插入数据库。

USE gongcheng
INSERT
INTO S
VALUES ('S9','英特尔',NULL,'西安',NULL);
INSERT
INTO SPJ
VALUES('S9','P5','J7','600');
INSERT
INTO SPJ
VALUES('S9','P4','J4','500');

(14)零件P3已经停产,请将P3的相关信息从数据库中删除。

USE gongcheng
DELETE 
FROM P
WHERE PNO='P3';
DELETE 
FROM SPJ
WHERE PNO='P3';

15)创建零件名为螺丝刀的供应情况的视图P_ls,包括供应商名(Sname),零件名(Pname),零件重量(Weight),工程项目代码(Jno),供应数量(QTY).

CREATE VIEW P_ls
AS
SELECT SName,PName,Weight,JNO,QTY
FROM S,P,SPJ
WHERE SPJ.SNO=S.SNO
AND P.PNo=SPJ.PNO
AND PName='螺丝刀';

(16)在视图P_ls中查询供应数量为500的供应商姓名。

USE gongcheng
SELECT SName
FROM P_ls
where QTY=500;

到这里,整个实验就做完了,实在是花了太多的时间,一下午加一个晚上,现在(晚上九点半),好累,当然肯定还有不对之处,欢迎指正!

评论

  1. 小木白
    博主
    Windows
    9月前
    2022-5-07 21:56:34

    太麻烦了,我敲 {{daku}} 还有不会写的

    • 小木白
      博主
      小木白
      Windows
      9月前
      2022-5-10 8:30:24

      在某人帮助下修改完成了 {{dianzan}}

发送评论 编辑评论


				
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
(╯‵□′)╯︵┴─┴
 ̄﹃ ̄
(/ω\)
∠( ᐛ 」∠)_
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ`)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ( ̄∇ ̄o)
ヾ(´・ ・`。)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò。)
Σ(っ °Д °;)っ
( ,,´・ω・)ノ"(´っω・`。)
╮(╯▽╰)╭
o(*////▽////*)q
>﹏<
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
颜文字
Emoji
小恐龙
花!
上一篇
下一篇