Solution
Deepti answered on
Dec 05 2021
48506-SQLServe
DDL-DML-SQLServer.sql
*DDL Statements:*
CREATE TABLE prj_part (
part_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
part_name VARCHAR(30) NOT NULL UNIQUE,
part_cost FLOAT NOT NULL
);
CREATE Table prj_transaction_type(
transaction_type_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
trans_type_text VARCHAR(30) NOT NULL UNIQUE
);
CREATE TABLE prj_transaction(
transaction_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
transaction_date DATETIME NOT NULL DEFAULT(GETDATE()),
transaction_type_id INT NOT NULL,
FOREIGN KEY (transaction_type_id) REFERENCES prj_transaction_type(transaction_type_id)
);
CREATE TABLE prj_transaction_part(
tp_transaction_part_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
tp_transaction_id INT NOT NULL,
tp_part_id INT NOT NULL,
tp_quantity INT NOT NULL,
CONSTRAINT [UQ_transaction_part] UNIQUE NONCLUSTERED
(
tp_transaction_id, tp_part_id
),
FOREIGN KEY (tp_transaction_id) REFERENCES prj_transaction(transaction_id),
FOREIGN KEY (tp_part_id) REFERENCES prj_part(part_id)
);
*DML statements:*
INSERT INTO prj_transaction_type (trans_type_text) VALUES
('Purchase')
,('Used');
INSERT INTO prj_part (part_name, part_cost) VALUES
('Cognition amplifier', 956)
, ('Combat suit', 163)
, ('Dark matter', 968)
, ('Alien Containment Box', 552)
, ('Dream inceptor', 909)
, ('Freeze ray', 143)
, ('Ionic Defibulizer', 905)
, ('Love Antidote', 891)
, ('Love Potion', 244)
, ('Meeseeks Box', 832)
, ('Microverse battery', 262)
, ('Operation Phoenix', 287)
, ('Particle Beam Wrist Watch', 412)
, ('Portal gun', 370)
, ('Shrink ray', 277)
, ('Space cruiser', 959);
INSERT INTO prj_transaction (transaction_date, transaction_type_id) VALUES
('1/1/2018', 1)
, ('1/1/2018', 2)
, ('2/1/2018', 1)
, ('2/1/2018', 2)
, ('3/1/2018', 1)
, ('3/1/2018', 2)
, ('4/1/2018', 1)
, ('4/1/2018', 2)
, ('5/1/2018', 1)
, ('5/1/2018', 2)
, ('6/1/2018', 1)
, ('6/1/2018', 2)
, ('7/1/2018', 1)
, ('7/1/2018', 2)
, ('8/1/2018', 1)
, ('8/1/2018', 2)
, ('9/1/2018', 1)
, ('9/1/2018', 2)
, ('10/1/2018', 1)
, ('10/1/2018', 2)
, ('11/1/2018', 1)
, ('11/1/2018', 2)
, ('12/1/2018', 1)
, ('12/1/2018', 2)
, ('1/1/2019', 1)
, ('1/1/2019', 2)
, ('2/1/2019', 1)
, ('2/1/2019', 2)
, ('3/1/2019', 1)
, ('3/1/2019', 2)
, ('4/1/2019', 1)
, ('4/1/2019', 2)
, ('5/1/2019', 1)
, ('5/1/2019', 2)
, ('6/1/2019', 1)
, ('6/1/2019', 2)
, ('7/1/2019', 1)
, ('7/1/2019', 2)
, ('8/1/2019', 1)
, ('8/1/2019', 2)
, ('9/1/2019', 1)
, ('9/1/2019', 2)
, ('10/1/2019', 1)
, ('10/1/2019', 2);
INSERT INTO prj_transaction_part (tp_transaction_id, tp_part_id, tp_quantity) VALUES
(1, 1, 10)
, (1, 2, 10)
, (1, 3, 10)
, (1, 4, 10)
, (1, 5, 10)
, (1, 6, 10)
, (1, 7, 10)
, (1, 8, 10)
, (1, 9, 10)
, (1, 10, 10)
, (1, 11, 10)
, (1, 12, 10)
, (1, 13, 10)
, (1, 14, 10)
, (1, 15, 10)
, (1, 16, 10)
, (2, 1, -2)
, (2, 2, -10)
, (2, 3, 0)
, (2, 4, -4)
, (2, 5, -1)
, (2, 6, -6)
, (2, 7, -5)
, (2, 8, -3)
, (2, 9, -1)
, (2, 10, -2)
, (2, 11, -7)
, (2, 12, -3)
, (2, 13, -8)
, (2, 14, -8)
, (2, 15, -3)
, (2, 16, -10)
, (3, 1, 5)
, (3, 2, 10)
, (3, 3, 6)
, (3, 4, 3)
, (3, 5, 3)
, (3, 6, 4)
, (3, 7, 5)
, (3, 8, 4)
, (3, 9, 8)
, (3, 10, 0)
, (3, 11, 0)
, (3, 12, 6)
, (3, 13, 0)
, (3, 14, 2)
, (3, 15, 7)
, (3, 16, 10)
, (4, 1, -8)
, (4, 2, 0)
, (4, 3, -13)
, (4, 4, -3)
, (4, 5, -6)
, (4, 6, -1)
, (4, 7, -5)
, (4, 8, -10)
, (4, 9, -4)
, (4, 10, -4)
, (4, 11, -1)
, (4, 12, -2)
, (4, 13, -2)
, (4, 14, 0)
, (4, 15, -13)
, (4, 16, -8)
, (5, 1, 0)
, (5, 2, 2)
, (5, 3, 3)
, (5, 4, 1)
, (5, 5, 4)
, (5, 6, 0)
, (5, 7, 0)
, (5, 8, 9)
, (5, 9, 7)
, (5, 10, 4)
, (5, 11, 0)
, (5, 12, 6)
, (5, 13, 10)
, (5, 14, 0)
, (5, 15, 9)
, (5, 16, 2)
, (6, 1, -3)
, (6, 2, -8)
, (6, 3, 0)
, (6, 4, -7)
, (6, 5, -3)
, (6, 6, -4)
, (6, 7, 0)
, (6, 8, -10)
, (6, 9, -18)
, (6, 10, -6)
, (6, 11, -2)
, (6, 12, -9)
, (6, 13, -10)
, (6, 14, -2)
, (6, 15, -3)
, (6, 16, -4)
, (7, 1, 1)
, (7, 2, 0)
, (7, 3, 3)
, (7, 4, 10)
, (7, 5, 3)
, (7, 6, 1)
, (7, 7, 3)
, (7, 8, 10)
, (7, 9, 0)
, (7, 10, 2)
, (7, 11, 10)
, (7, 12, 2)
, (7, 13, 10)
, (7, 14, 1)
, (7, 15, 1)
, (7, 16, 10)
, (8, 1, -2)
, (8, 2, -4)
, (8, 3, -2)
, (8, 4, 0)
, (8, 5, -5)
, (8, 6, -1)
, (8, 7, -2)
, (8, 8, -2)
, (8, 9, -2)
, (8, 10, 0)
, (8, 11, -4)
, (8, 12, -3)
, (8, 13, -2)
, (8, 14, -3)
, (8, 15, -6)
, (8, 16, -3)
, (9, 1, 9)
, (9, 2, 10)
, (9, 3, 0)
, (9, 4, 8)
, (9, 5, 0)
, (9, 6, 3)
, (9, 7, 0)
, (9, 8, 2)
, (9, 9, 10)
, (9, 10, 2)
, (9, 11, 3)
, (9, 12, 6)
, (9, 13, 1)
, (9, 14, 10)
, (9, 15, 2)
, (9, 16, 6)
, (10, 1, 0)
, (10, 2, -3)
, (10, 3, 0)
, (10, 4, -3)
, (10, 5, 0)
, (10, 6, -5)
, (10, 7, -5)
, (10, 8, -2)
, (10, 9, -7)
, (10, 10, -2)
, (10, 11, -7)
, (10, 12, -8)
, (10, 13, -2)
, (10, 14, -10)
, (10, 15, -4)
, (10, 16, -9)
, (11, 1, 1)
, (11, 2, 7)
, (11, 3, 4)
, (11, 4, 9)
, (11, 5, 0)
, (11, 6, 9)
, (11, 7, 9)
, (11, 8, 2)
, (11, 9, 3)
, (11, 10, 0)
, (11, 11, 0)
, (11, 12, 2)
, (11, 13, 1)
, (11, 14, 10)
, (11, 15, 10)
, (11, 16, 0)
, (12, 1, -4)
, (12, 2, -13)
, (12, 3, -4)
, (12, 4, -19)
, (12, 5, -5)
, (12, 6, -4)
, (12, 7, -2)
, (12, 8, -8)
, (12, 9, -5)
, (12, 10, 0)
, (12, 11, -2)
, (12, 12, -6)
, (12, 13, -5)
, (12, 14, -3)
, (12, 15, -10)
, (12, 16, -4)
, (13, 1, 2)
, (13, 2, 9)
, (13, 3, 1)
, (13, 4, 0)
, (13, 5, 10)
, (13, 6, 5)
, (13, 7, 0)
, (13, 8, 2)
, (13, 9, 9)
, (13, 10, 3)
,...