Article directory
-
- database display
- Create table statement and related data
- Answers to textbook exercises
Database display
Create table statement and related data
- List J
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for j
-- ----------------------------
DROP TABLE IF EXISTS `j`;
CREATE TABLE `j` (
`JNO` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`JNAME` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`CITY` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of j
-- ----------------------------
INSERT INTO `j` VALUES ('J1', 'Sanjian', 'Beijing');
INSERT INTO `j` VALUES ('J2', 'FAW', 'Changchun');
INSERT INTO `j` VALUES ('J3', 'Spring Factory', 'Tianjin');
INSERT INTO `j` VALUES ('J4', 'Shipyard', 'Tianjin');
INSERT INTO `j` VALUES ('J5', 'Locomotive Factory', 'Tangshan');
INSERT INTO `j` VALUES ('J6', 'Radio Factory', 'Changzhou');
INSERT INTO `j` VALUES ('J7', 'Semiconductor Factory', 'Nanjing');
SET FOREIGN_KEY_CHECKS = 1;
- P list
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for p
-- ----------------------------
DROP TABLE IF EXISTS `p`;
CREATE TABLE `p` (
`PNO` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`PNAME` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`COLOR` char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`WEIGHT` int NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of p
-- ----------------------------
INSERT INTO `p` VALUES ('P1', 'Nut', 'Red', 12);
INSERT INTO `p` VALUES ('P2', 'Bolt', 'Green', 17);
INSERT INTO `p` VALUES ('P3', 'screwdriver', 'blue', 14);
INSERT INTO `p` VALUES ('P4', 'Screwdriver', 'Red', 14);
INSERT INTO `p` VALUES ('P5', 'Cam', 'Blue', 40);
INSERT INTO `p` VALUES ('P6', 'Gear', 'Red', 30);
SET FOREIGN_KEY_CHECKS = 1;
- S table
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for s
-- ----------------------------
DROP TABLE IF EXISTS `s`;
CREATE TABLE `s` (
`SNO` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`SNAME` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`STATUS` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`CITY` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of s
-- ----------------------------
INSERT INTO `s` VALUES ('S1', 'Lean', '20', 'Tianjin');
INSERT INTO `s` VALUES ('S2', 'Shengxi', '10', 'Beijing');
INSERT INTO `s` VALUES ('S3', 'Dongfanghong', '30', 'Beijing');
INSERT INTO `s` VALUES ('S4', 'Feng Tai Sheng', '20', 'Tianjin');
INSERT INTO `s` VALUES ('S5', 'for the people', '30', 'Shanghai');
SET FOREIGN_KEY_CHECKS = 1;
- SPJ table
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for spj
-- ----------------------------
DROP TABLE IF EXISTS `spj`;
CREATE TABLE `spj` (
`SNO` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`PNO` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`JNO` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`QTY` int NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of 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);
SET FOREIGN_KEY_CHECKS = 1;
Textbook exercise answers
-- Question 4
-- (1) Ask for the supplier number SNO of the J1 parts of the supply project.
SELECT SNO FROM SPJ
WHERE JNO = 'J1';
-- (2) Ask for the supplier number SNO of the part P1 of the supply engineering J1.
SELECT SNO FROM SPJ
WHERE JNO = 'J1' AND PNO = 'P1';
-- (3) Ask for the supplier number SNO of the supplier whose J1 parts are in red.
SELECT SNO FROM SPJ
WHERE JNO = 'J1' AND PNO IN (
SELECT PNO FROM P
WHERE COLOR = 'red'
);
-- (4) Ask for the project number JNO that does not use the red parts produced by Tianjin suppliers.
SELECT JNO FROM J
WHERE NOT EXISTS (
SELECT *
FROM SPJ, S, P
WHERE SPJ.JNO = J.JNO AND SPJ.SNO = S.SNO
AND SPJ.PNO = P.PNO AND S.CITY = 'Tianjin'
AND P.COLOR = 'red'
);
-- (5) Find the project number JNO that uses at least all parts supplied by supplier S1.
SELECT DISTINCT JNO
FROM SPJ AS SPJZ
WHERE NOT EXISTS (
SELECT *
FROM SPJ AS SPJX
WHERE SNO = 'S1'
AND NOT EXISTS (
SELECT *
FROM SPJ AS SPJY
WHERE SPJY.PNO = SPJX.PNO
AND SPJY.JNO = SPJZ.JNO
)
);
-- Question 5
-- (1) Find the names and cities of all suppliers.
SELECT SNAME, CITY FROM S;
-- (2) Find out the name, color, weight of all parts.
SELECT PNAME, COLOR, WEIGHT FROM P;
-- (3) Find out the project number using the part supplied by supplier S1.
SELECT PNO FROM SPJ
WHERE SNO = 'S1';
-- (4) Find out the names and quantities of various parts used in project J2.
SELECT P.PNAME, SPJ.QTY
FROM P, SPJ WHERE P.PNO = SPJ.PNO
AND SPJ.JNO = 'J2';
-- (5) Find out all part numbers supplied by Shanghai manufacturers.
SELECT DISTINCT SPJ.PNO
FROM S, SPJ WHERE S.SNO = SPJ.SNO
AND S.CITY = 'Shanghai';
-- (6) Find out the name of the project using the parts produced in Shanghai.
SELECT J.JNAME
FROM SPJ, S, J
WHERE SPJ.SNO = S.SNO
AND SPJ.JNO = J.JNO
AND S.CITY = 'Shanghai';
\t
-- (7) Find out the name of the project that does not use parts from Tianjin.
SELECT JNO FROM J
WHERE NOT EXISTS (
SELECT *
FROM SPJ
WHERE SPJ.JNO = J.JNO
AND SNO IN (
SELECT SNO
FROM S
WHERE CITY = 'Tianjin'
)
);
-- (8) Change the color of all red parts to blue.
UPDATE P SET COLOR = 'Blue'
WHERE COLOR = 'red';
-- (9) Change the part P6 supplied by S5 to J4 to be supplied by S3, please make necessary modifications.
UPDATE SPJ SET SNO = 'S3'
WHERE SNO = 'S5' AND JNO = 'J4' AND PNO = 'P6';
\t
-- (10) Delete the record of S2 from the supplier relationship, and delete the related record from the supply situation relationship.
DELETE FROM SPJ WHERE SNO = 'S2'; -- delete the related S2 supplier record from SPJ
DELETE FROM S WHERE SNO = 'S2'; -- delete supplier S2
-- (11) Please insert (S2, J6, P4, 200) into the availability relationship.
INSERT INTO SPJ VALUES('S2', 'J6', 'P4', 200);