 /* 3-9 */
 
 /* multiple-table view: can't get updated */
-CREATE VIEW `SANJIAN_SPJ`(`SNO`, `PNO`, `QTY`)
+CREATE VIEW [SANJIAN_SPJ]([SNO], [PNO], [QTY])
 AS
-SELECT `SNO`, `PNO`, `QTY`
-FROM `J`, `SPJ`
-WHERE `J`.`JNO` = `SPJ`.`JNO`
-	AND `JNAME` = '三建'
+SELECT [SNO], [PNO], [QTY]
+FROM [SPJ414800].[J], [SPJ414800].[SPJ]
+WHERE [SPJ414800].[J].[JNO] = [SPJ414800].[SPJ].[JNO]
+	AND [JNAME] = ''
 WITH CHECK OPTION;
-
-SELECT `PNO`, `QTY` FROM `SANJIAN_SPJ`;
-SELECT * FROM `SANJIAN_SPJ` WHERE `SNO` = 'S1';
-
-CREATE VIEW `TIANJING_S`
+GO
+
+SELECT [PNO], [QTY] FROM [SANJIAN_SPJ];
+SELECT * FROM [SANJIAN_SPJ] WHERE [SNO] = 'S1';
+GO
+
+CREATE VIEW [TIANJING_S]
 AS
-SELECT `SNO`, `SNAME`, `STATUS`
-FROM `S`
-WHERE `CITY` = '天津';
+SELECT [SNO], [SNAME], [STATUS]
+FROM [SPJ414800].[S]
+WHERE [CITY] = '';
+-- WHERE [CITY] = ''
 -- WITH CHECK OPTION;
-
-INSERT INTO `TIANJING_S` VALUES('S23', '华科重工', 30);
-SELECT * FROM `S`;
+GO
+
+INSERT INTO [TIANJING_S] VALUES('S23', 'ع', 30);
+SELECT * FROM [SPJ414800].[S];
 
 /* 3-9 */
 
 
 /* 3-5 part2 */
 
-UPDATE `P`
-SET `COLOR` = '蓝'
-WHERE `COLOR` = '红';
-SELECT * FROM `P`;
-
-UPDATE `SPJ`
-SET `SNO` = 'S3'
-WHERE `SNO` = 'S5'
-	AND `PNO` = 'P6'
-	AND `JNO` = 'J4';
-SELECT * FROM `SPJ`;
-
-INSERT INTO `SPJ` VALUES('S2', 'P4', 'J6', 200);
-SELECT * FROM `SPJ`;
-
-DELETE FROM `S` WHERE `SNO` = 'S2';		/* cascade */
-SELECT * FROM `S`;
-SELECT * FROM `SPJ`;
-
-/* 3-5 part2 */
-
-/* DELIMITER $$
-
-DROP TRIGGER IF EXISTS `SPJ_CHECK1`$$
-CREATE TRIGGER `SPJ_CHECK1`
-    BEFORE UPDATE ON `SPJ`
-    FOR EACH ROW
-    BEGIN
-        -- condition to check
-        IF NEW.QTY < 1 OR NEW.QTY > 10000 THEN
-            -- hack to solve absence of SIGNAL/prepared statements in triggers
-            UPDATE `Error: invalid QTY value` SET x = 1;
-        END IF;
-    END$$
-
-DROP TRIGGER IF EXISTS `SPJ_CHECK2`$$
-CREATE TRIGGER `SPJ_CHECK2`
-    BEFORE INSERT ON `SPJ`
-    FOR EACH ROW
-    BEGIN
-        -- condition to check
-        IF NEW.QTY < 1 OR NEW.QTY > 10000 THEN
-            -- hack to solve absence of SIGNAL/prepared statements in triggers
-            UPDATE `Error: invalid QTY value` SET x = 1;
-        END IF;
-    END$$
-
-DELIMITER ; */
+UPDATE [SPJ414800].[P]
+SET [COLOR] = ''
+WHERE [COLOR] = '';
+SELECT * FROM [SPJ414800].[P];
+
+UPDATE [SPJ414800].[SPJ]
+SET [SNO] = 'S3'
+WHERE [SNO] = 'S5'
+	AND [PNO] = 'P6'
+	AND [JNO] = 'J4';
+SELECT * FROM [SPJ414800].[SPJ];
+
+INSERT INTO [SPJ414800].[SPJ] VALUES('S2', 'P4', 'J6', 200);
+SELECT * FROM [SPJ414800].[SPJ];
+
+DELETE FROM [SPJ414800].[S] WHERE [SNO] = 'S2';		/* cascade */
+SELECT * FROM [SPJ414800].[S];
+SELECT * FROM [SPJ414800].[SPJ];
+
+/* 3-5 part2 */
USE [master];
IF EXISTS(SELECT * from sys.databases where name = 'DBU201414800')
DROP DATABASE [DBU201414800];
CREATE DATABASE [DBU201414800];
USE [DBU201414800];

IF (EXISTS (SELECT * FROM sys.schemas WHERE name = 'SPJ414800'))
DROP SCHEMA [SPJ414800];
EXEC ('CREATE SCHEMA [SPJ414800]');
ALTER USER [dbo] WITH DEFAULT_SCHEMA [SPJ414800];

CREATE TABLE `S` (
	`SNO` VARCHAR(5) PRIMARY KEY,
    `SNAME` VARCHAR(30) NOT NULL,
    `STATUS` INT,
    `CITY` VARCHAR(30)
);

CREATE TABLE `P` (
	`PNO` VARCHAR(5) PRIMARY KEY,
    `PNAME` VARCHAR(30) NOT NULL,
    `COLOR` VARCHAR(10),
    `WEIGHT` INT DEFAULT 10
);

CREATE TABLE `J` (
	`JNO` VARCHAR(5) PRIMARY KEY,
    `JNAME` VARCHAR(30) NOT NULL,
    `CITY` VARCHAR(30)
);

CREATE TABLE `SPJ` (
	`SNO` VARCHAR(5),
    `PNO` VARCHAR(5),
    `JNO` VARCHAR(5),
    `QTY` INT CHECK(`QTY` >= 1 AND `QTY` <= 10000),
    PRIMARY KEY(`SNO`, `PNO`, `JNO`),
    FOREIGN KEY (`SNO`)
		REFERENCES `S`(`SNO`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (`PNO`)
		REFERENCES `P`(`PNO`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
	FOREIGN KEY (`JNO`)
		REFERENCES `J`(`JNO`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE `BIG_SPJ` LIKE `SPJ`;

ALTER TABLE `P` ADD COLUMN `PRICE` FLOAT;
ALTER TABLE `J` ADD COLUMN `JSTART` DATE NOT NULL;
ALTER TABLE `J` ADD COLUMN `JEND` DATE;
ALTER TABLE `J` ADD CHECK(`JEND` > `JSTART`);

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, '上海');

INSERT INTO `P` VALUES('P1', '螺母', '红', 12, FLOOR(2 + (RAND() * 3)));
INSERT INTO `P` VALUES('P2', '螺栓', '绿', 17, FLOOR(3 + (RAND() * 3)));
INSERT INTO `P` VALUES('P3', '螺丝刀', '蓝', 14, FLOOR(5 + (RAND() * 3)));
INSERT INTO `P` VALUES('P4', '螺丝刀', '红', 14, FLOOR(6 + (RAND() * 3)));
INSERT INTO `P` VALUES('P5', '凸轮', '蓝', 40, FLOOR(7 + (RAND() * 3)));
INSERT INTO `P` VALUES('P6', '齿轮', '红', 30, FLOOR(8 + (RAND() * 3)));

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

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);

INSERT INTO `BIG_SPJ`
SELECT *
FROM `SPJ`
WHERE `SPJ`.`QTY` > 400;

/* integrity violation insertion */
INSERT INTO `S` VALUES('S1', '华科重工', 30, '武汉');	/* error: primary key */
INSERT INTO `S` VALUES(NULL, '华科重工', 30, '武汉');	/* error: entity integrity */
INSERT INTO `SPJ` VALUES('S233', 'P233', 'J233', 233);	/* error: referential integrity */
INSERT INTO `S` VALUES('S6', NULL, 30, '武汉');			/* error: user-defined integrity */
-- INSERT INTO `SPJ` VALUES('S1', 'P1', 'J7', 23333);		/* error: user-defined integrity */
-- INSERT INTO `J` VALUES('J8', '阿姆斯特朗对撞超级计算机中心', '武汉', '2017-07-01', '2016-12-25');	/* error: user-defined integrity */

/*
SELECT * FROM `S`;
SELECT * FROM `P`;
SELECT * FROM `J`;
SELECT * FROM `SPJ`;
SELECT * FROM `BIG_SPJ`;
*/

/* 2-6 */

SELECT DISTINCT `SNO`
FROM `SPJ`
WHERE `JNO` = 'J1';

SELECT DISTINCT `SNO`
FROM `SPJ`
WHERE `PNO` = 'P1' AND `JNO` = 'J1';

SELECT DISTINCT `SNO`
FROM `P`, `SPJ`
WHERE `P`.`PNO` = `SPJ`.`PNO`
	AND `JNO` = 'J1'
    AND `COLOR` = '红';

SELECT  DISTINCT `JNO`
FROM `SPJ`
WHERE `JNO` NOT IN (
	SELECT `JNO`
    FROM `S`, `P`, `SPJ`
    WHERE `S`.`SNO` = `SPJ`.`SNO`
		AND `P`.`PNO` = `SPJ`.`PNO`
        AND `CITY` ='天津'
        AND `COLOR` = '红'
);

SELECT DISTINCT `JNO`
FROM `SPJ` `SPJX`
WHERE NOT EXISTS (
	SELECT *
    FROM `SPJ` `SPJY`
    WHERE `SPJY`.`SNO` = 'S1'
		AND NOT EXISTS (
			SELECT *
			FROM `SPJ` `SPJZ`
			WHERE `SPJX`.`JNO` = `SPJZ`.`JNO`
				AND `SPJY`.`PNO` = `SPJZ`.`PNO`
		)
);



/* 3-5 part1 */

SELECT [SNAME], [CITY]
FROM [SPJ414800].[S];

SELECT [PNAME], [COLOR], [WEIGHT]
FROM [SPJ414800].[P];

SELECT DISTINCT [JNO]
FROM [SPJ414800].[SPJ]
WHERE [SNO] = 'S1';

SELECT [PNAME], [QTY]
FROM [SPJ414800].[P], [SPJ414800].[SPJ]
WHERE [SPJ414800].[P].[PNO] = [SPJ414800].[SPJ].[PNO]
	AND [JNO] = 'J2';

SELECT DISTINCT [PNO]
FROM [SPJ414800].[S], [SPJ414800].[SPJ]
WHERE [SPJ414800].[S].[SNO] = [SPJ414800].[SPJ].[SNO]
	AND [CITY] = 'Ϻ';

SELECT DISTINCT [JNAME]
FROM [SPJ414800].[S], [SPJ414800].[J], [SPJ414800].[SPJ]
WHERE [SPJ414800].[S].[SNO] = [SPJ414800].[SPJ].[SNO]
	AND [SPJ414800].[J].[JNO] = [SPJ414800].[SPJ].[JNO]
    AND [SPJ414800].[S].[CITY] = 'Ϻ';

SELECT DISTINCT [JNO]
FROM [SPJ414800].[SPJ]
WHERE [JNO] NOT IN (
	SELECT DISTINCT [JNO]
    FROM [SPJ414800].[S], [SPJ414800].[SPJ]
    WHERE [SPJ414800].[S].[SNO] = [SPJ414800].[SPJ].[SNO]
		AND [CITY]= ''
);

/* 3-5 part1 */

/* additional SQL */

SELECT DISTINCT [SPJ414800].[P].[PNO], [PNAME], [JNAME]
FROM [SPJ414800].[P], [SPJ414800].[J], [SPJ414800].[SPJ]
WHERE [SPJ414800].[P].[PNO] = [SPJ414800].[SPJ].[PNO]
	AND [SPJ414800].[J].[JNO] = [SPJ414800].[SPJ].[JNO]
    AND [JNAME] LIKE '%'
ORDER BY [JNAME], [PNO], [PNAME];

SELECT DISTINCT [JNO], SUM([PRICE]) AS [JPRICE]
FROM [SPJ414800].[P], [SPJ414800].[SPJ]
WHERE [SPJ414800].[P].[PNO] = [SPJ414800].[SPJ].[PNO]
GROUP BY [JNO]
ORDER BY [JPRICE] DESC;

SELECT DISTINCT [SNO], SUM([QTY]) AS [SQTY]
FROM [SPJ414800].[SPJ]
GROUP BY [SNO];

SELECT DISTINCT [JNO], DATEDIFF(DAY, [JSTART], [JEND]) AS [JDAY]
FROM [SPJ414800].[J]
WHERE DATEDIFF(DAY, [JSTART], [JEND]) <= 365;

SELECT DISTINCT [JNO]
FROM [SPJ414800].[SPJ]
WHERE [JNO] NOT IN (
	SELECT [SPJ414800].[J].[JNO]
    FROM [SPJ414800].[S], [SPJ414800].[J], [SPJ414800].[SPJ]
    WHERE [SPJ414800].[S].[SNO] = [SPJ414800].[SPJ].[SNO]
		AND [SPJ414800].[J].[JNO] = [SPJ414800].[SPJ].[JNO]
        AND [SPJ414800].[S].[CITY] = [SPJ414800].[J].[CITY]
);
GO

/* additional SQL */

