用SQL语句建立第2章习题6中的4个表,针对建立的4个表用SQL语句完成以下查询
(1)求供应工程J1零件的供应商号码SNO
(2)求供应工程J1零件P1的供应商号码SNO
(3)找出所有供应商的姓名和所在城市
(4)找出所有零件的名称、颜色和重量
(5)找出使用供应商S1所供应零件的工程号码
(1)先建数据库,建表和导入数据:
CREATE DATABASE SPJ
on primary
(
name=SPJ_data,
filename='E:\Data2\SPJ_data.mdf',
size=10MB,
filegrowth=10%,
maxsize=UNLIMITED
)
log on
(
name=SPJ_log,
filename='E:\Data2\SPJ_log.ldf',
maxsize=10MB,
filegrowth=1MB
)
USE SPJ;
CREATE TABLE S(
SNO CHAR(3) PRIMARY KEY,
SNAME CHAR(10),
STATUS CHAR(2),
CITY CHAR(10));
CREATE TABLE P(
PNO CHAR(3),
PNAME CHAR(10),
COLOR CHAR(4),
WEIGHT INT,
PRIMARY KEY(PNO));
CREATE TABLE J(
JNO CHAR(3),
JNAME CHAR(10),
CITY CHAR(10),
PRIMARY KEY(JNO));
CREATE TABLE SPJ(
SNO CHAR(3),
PNO CHAR(3),
JNO CHAR(3),
QTY INT,
PRIMARY KEY(SNO,PNO,JNO),
FOREIGN KEY(SNO) REFERENCES S(SNO),
FOREIGN KEY(PNO) REFERENCES P(PNO),
FOREIGN KEY(JNO) REFERENCES J(JNO)
);
INSERT INTO S(SNO,SNAME,STATUS,CITY) VALUES
('S1','精益','20','天津'),
('S2','盛锡','10','北京'),
('S3','东方红','30','北京'),
('S4','丰泰盛','20','天津'),
('S5','为民','30','上海');
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT) VALUES
('P1','螺母','红',12),
('P2','螺栓','绿',17),
('P3','螺丝刀','蓝',14),
('P4','螺丝刀','红',14),
('P5','凸轮','蓝',40),
('P6','齿轮','红',30);
INSERT INTO J VALUES
('J1','三建','北京'),
('J2','一汽','长春'),
('J3','弹簧厂','天津'),
('J4','造船厂','天津'),
('J5','机车厂','唐山'),
('J6','无限电厂','常州'),
('J7','半导体厂','南京');
INSERT INTO SPJ VALUES
('S1','P1','J1',200),
('S1','P1','J4',700),
('S1','P2','J2',100),
('S2','P3','J1',400),
('S2','P3','J2',200),
('S2','P3','J4',500),
('S2','P3','J5',400),
('S2','P5','J1',400),
('S3','P1','J1',200),
('S3','P3','J1',200),
('S4','P5','J1',100),
('S4','P6','J3',300),
('S4','P6','J4',200),
('S5','P2','J4',100),
('S5','P3','J1',200),
('S5','P6','J2',200),
('S5','P6','J4',50);
(2)开始用SQL语句查询
(1)求供应工程J1零件的供应商号码SNO
select distinct SNO from SPJ where JNO='J1';
(2)求供应工程J1零件P1的供应商号码SNO
select distinct SNO from SPJ where JNO='J1' and PNO='p1';
(3)找出所有供应商的姓名和所在城市
select SNAME,CITY from S ;
(4)找出所有零件的名称、颜色和重量
select PNAME,COLOR,WEIGHT FROM P;
(5)找出使用供应商S1所供应零件的工程号码
select distinct PNO from SPJ where SNO='S1';
额 ,先写这么多,后面再总结下常见的在MSSQL上的SQL语句查询,今天不想写了😶