SQL Work Sample.sql

--Drop statements
If Object_ID ('dbo.prj_transaction_part') is not null
	drop table dbo.prj_transaction_part;
If Object_ID ('dbo.prj_part') is not null
	drop table dbo.prj_part;
If Object_ID ('dbo.prj_transaction') is not null
	drop table dbo.prj_transaction;
If Object_ID ('dbo.prj_transaction_type') is not null
	drop table dbo.prj_transaction_type;





Create table prj_transaction_type (
	trans_type_id int Identity NOT NULL PRIMARY KEY,
	trans_type_text VARCHAR(30) Unique NOT NULL)

INSERT INTO prj_transaction_type (trans_type_text) VALUES
    ('Purchase')
    ,('Used')

Select * From prj_transaction_type

	Create table prj_part (
	part_id INT Identity PRIMARY KEY,
	part_name VARCHAR(30) Unique NOT NULL,
	part_cost money NOT NULL)


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)

Select * From prj_part

Create Table prj_transaction ( 
	transaction_date DATETIME NOT NULL,
	transaction_id INT IDENTITY NOT NULL PRIMARY KEY,
	transaction_type_id INT NOT NULL ) 

	Alter Table prj_transaction 
	ADD FOREIGN KEY (transaction_type_id) references prj_transaction_type(trans_type_id)


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)

Select * from prj_transaction


Create table prj_transaction_part (
	transaction_part_id INT identity PRIMARY KEY,
	tp_transaction_id INT NOT NULL,
	tp_part_id INT NOT NULL,
	tp_quantity INT NOT NULL)

ALTER table prj_transaction_part
ADD FOREIGN KEY (tp_transaction_id) references prj_transaction(transaction_id)

Alter table prj_transaction_part
ADD FOREIGN KEY (tp_part_id) references prj_part(part_id)

Select * from prj_transaction_part

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)
, (13, 11, 10)
, (13, 12, 9)
, (13, 13, 1)
, (13, 14, 7)
, (13, 15, 10)
, (13, 16, 10)
, (14, 1, -3)
, (14, 2, -4)
, (14, 3, -5)
, (14, 4, -1)
, (14, 5, -4)
, (14, 6, -2)
, (14, 7, -6)
, (14, 8, 0)
, (14, 9, -6)
, (14, 10, -1)
, (14, 11, -6)
, (14, 12, -5)
, (14, 13, 0)
, (14, 14, -4)
, (14, 15, -4)
, (14, 16, -3)
, (15, 1, 5)
, (15, 2, 4)
, (15, 3, 2)
, (15, 4, 3)
, (15, 5, 2)
, (15, 6, 9)
, (15, 7, 0)
, (15, 8, 2)
, (15, 9, 4)
, (15, 10, 4)
, (15, 11, 1)
, (15, 12, 5)
, (15, 13, 3)
, (15, 14, 8)
, (15, 15, 6)
, (15, 16, 1)
, (16, 1, -11)
, (16, 2, -2)
, (16, 3, 0)
, (16, 4, -1)
, (16, 5, -1)
, (16, 6, -8)
, (16, 7, 0)
, (16, 8, 0)
, (16, 9, 0)
, (16, 10, -3)
, (16, 11, -3)
, (16, 12, -3)
, (16, 13, -7)
, (16, 14, -13)
, (16, 15, -3)
, (16, 16, -3)
, (17, 1, 10)
, (17, 2, 0)
, (17, 3, 2)
, (17, 4, 5)
, (17, 5, 7)
, (17, 6, 6)
, (17, 7, 1)
, (17, 8, 1)
, (17, 9, 6)
, (17, 10, 4)
, (17, 11, 1)
, (17, 12, 3)
, (17, 13, 10)
, (17, 14, 5)
, (17, 15, 6)
, (17, 16, 2)
, (18, 1, 0)
, (18, 2, -5)
, (18, 3, -6)
, (18, 4, -4)
, (18, 5, -13)
, (18, 6, -5)
, (18, 7, -3)
, (18, 8, -2)
, (18, 9, -10)
, (18, 10, -6)
, (18, 11, -1)
, (18, 12, -2)
, (18, 13, -10)
, (18, 14, -2)
, (18, 15, -3)
, (18, 16, -6)
, (19, 1, 8)
, (19, 2, 1)
, (19, 3, 9)
, (19, 4, 3)
, (19, 5, 9)
, (19, 6, 0)
, (19, 7, 10)
, (19, 8, 3)
, (19, 9, 4)
, (19, 10, 5)
, (19, 11, 0)
, (19, 12, 6)
, (19, 13, 10)
, (19, 14, 7)
, (19, 15, 6)
, (19, 16, 9)
, (20, 1, -7)
, (20, 2, -3)
, (20, 3, -2)
, (20, 4, 0)
, (20, 5, -5)
, (20, 6, -4)
, (20, 7, -6)
, (20, 8, -2)
, (20, 9, -5)
, (20, 10, 0)
, (20, 11, -2)
, (20, 12, -3)
, (20, 13, -6)
, (20, 14, -9)
, (20, 15, -7)
, (20, 16, -3)
, (21, 1, 2)
, (21, 2, 9)
, (21, 3, 8)
, (21, 4, 3)
, (21, 5, 0)
, (21, 6, 7)
, (21, 7, 0)
, (21, 8, 6)
, (21, 9, 2)
, (21, 10, 3)
, (21, 11, 10)
, (21, 12, 10)
, (21, 13, 3)
, (21, 14, 4)
, (21, 15, 7)
, (21, 16, 1)
, (22, 1, -12)
, (22, 2, -7)
, (22, 3, -14)
, (22, 4, -10)
, (22, 5, -3)
, (22, 6, -13)
, (22, 7, -2)
, (22, 8, -1)
, (22, 9, -3)
, (22, 10, -5)
, (22, 11, -4)
, (22, 12, -19)
, (22, 13, -7)
, (22, 14, -5)
, (22, 15, -13)
, (22, 16, -6)
, (23, 1, 9)
, (23, 2, 2)
, (23, 3, 0)
, (23, 4, 3)
, (23, 5, 0)
, (23, 6, 9)
, (23, 7, 0)
, (23, 8, 0)
, (23, 9, 1)
, (23, 10, 5)
, (23, 11, 4)
, (23, 12, 1)
, (23, 13, 10)
, (23, 14, 2)
, (23, 15, 1)
, (23, 16, 0)
, (24, 1, -4)
, (24, 2, -2)
, (24, 3, 0)
, (24, 4, -3)
, (24, 5, -1)
, (24, 6, -7)
, (24, 7, 0)
, (24, 8, -10)
, (24, 9, -2)
, (24, 10, -10)
, (24, 11, -10)
, (24, 12, 0)
, (24, 13, -8)
, (24, 14, -5)
, (24, 15, -6)
, (24, 16, -2)
, (25, 1, 1)
, (25, 2, 2)
, (25, 3, 1)
, (25, 4, 3)
, (25, 5, 9)
, (25, 6, 0)
, (25, 7, 2)
, (25, 8, 9)
, (25, 9, 9)
, (25, 10, 2)
, (25, 11, 10)
, (25, 12, 2)
, (25, 13, 0)
, (25, 14, 1)
, (25, 15, 10)
, (25, 16, 10)
, (26, 1, -7)
, (26, 2, 0)
, (26, 3, -3)
, (26, 4, -2)
, (26, 5, -1)
, (26, 6, -1)
, (26, 7, 0)
, (26, 8, 0)
, (26, 9, -10)
, (26, 10, -2)
, (26, 11, 0)
, (26, 12, 0)
, (26, 13, 0)
, (26, 14, 0)
, (26, 15, -2)
, (26, 16, -6)
, (27, 1, 10)
, (27, 2, 2)
, (27, 3, 10)
, (27, 4, 3)
, (27, 5, 4)
, (27, 6, 1)
, (27, 7, 2)
, (27, 8, 8)
, (27, 9, 10)
, (27, 10, 1)
, (27, 11, 7)
, (27, 12, 1)
, (27, 13, 2)
, (27, 14, 2)
, (27, 15, 3)
, (27, 16, 3)
, (28, 1, -6)
, (28, 2, -2)
, (28, 3, -7)
, (28, 4, -1)
, (28, 5, -1)
, (28, 6, -1)
, (28, 7, -2)
, (28, 8, -14)
, (28, 9, -7)
, (28, 10, -1)
, (28, 11, -13)
, (28, 12, -4)
, (28, 13, -3)
, (28, 14, -5)
, (28, 15, -8)
, (28, 16, 0)
, (29, 1, 4)
, (29, 2, 4)
, (29, 3, 2)
, (29, 4, 1)
, (29, 5, 7)
, (29, 6, 0)
, (29, 7, 2)
, (29, 8, 2)
, (29, 9, 0)
, (29, 10, 1)
, (29, 11, 1)
, (29, 12, 2)
, (29, 13, 9)
, (29, 14, 10)
, (29, 15, 1)
, (29, 16, 7)
, (30, 1, -7)
, (30, 2, -7)
, (30, 3, -2)
, (30, 4, -2)
, (30, 5, 0)
, (30, 6, -2)
, (30, 7, -2)
, (30, 8, -3)
, (30, 9, -1)
, (30, 10, -2)
, (30, 11, -3)
, (30, 12, -1)
, (30, 13, -8)
, (30, 14, -7)
, (30, 15, 0)
, (30, 16, -10)
, (31, 1, 9)
, (31, 2, 1)
, (31, 3, 3)
, (31, 4, 4)
, (31, 5, 16)
, (31, 6, 10)
, (31, 7, 2)
, (31, 8, 3)
, (31, 9, 0)
, (31, 10, 2)
, (31, 11, 2)
, (31, 12, 1)
, (31, 13, 2)
, (31, 14, 2)
, (31, 15, 4)
, (31, 16, 2)
, (32, 1, -2)
, (32, 2, -2)
, (32, 3, -1)
, (32, 4, -7)
, (32, 5, -29)
, (32, 6, -5)
, (32, 7, -4)
, (32, 8, -3)
, (32, 9, -2)
, (32, 10, 0)
, (32, 11, -1)
, (32, 12, -1)
, (32, 13, -4)
, (32, 14, 0)
, (32, 15, -1)
, (32, 16, -3)
, (33, 1, 1)
, (33, 2, 9)
, (33, 3, 4)
, (33, 4, 1)
, (33, 5, 6)
, (33, 6, 0)
, (33, 7, 1)
, (33, 8, 1)
, (33, 9, 10)
, (33, 10, 2)
, (33, 11, 2)
, (33, 12, 0)
, (33, 13, 10)
, (33, 14, 2)
, (33, 15, 1)
, (33, 16, 1)
, (34, 1, -1)
, (34, 2, -8)
, (34, 3, -2)
, (34, 4, 0)
, (34, 5, -12)
, (34, 6, -3)
, (34, 7, -2)
, (34, 8, -3)
, (34, 9, -4)
, (34, 10, -6)
, (34, 11, -4)
, (34, 12, -2)
, (34, 13, -4)
, (34, 14, -5)
, (34, 15, -1)
, (34, 16, 0)
, (35, 1, 8)
, (35, 2, 1)
, (35, 3, 5)
, (35, 4, 0)
, (35, 5, 10)
, (35, 6, 2)
, (35, 7, 9)
, (35, 8, 9)
, (35, 9, 6)
, (35, 10, 10)
, (35, 11, 9)
, (35, 12, 9)
, (35, 13, 1)
, (35, 14, 2)
, (35, 15, 6)
, (35, 16, 1)
, (36, 1, -9)
, (36, 2, -1)
, (36, 3, -10)
, (36, 4, -3)
, (36, 5, -1)
, (36, 6, 0)
, (36, 7, -5)
, (36, 8, -3)
, (36, 9, -12)
, (36, 10, -8)
, (36, 11, -5)
, (36, 12, -4)
, (36, 13, 0)
, (36, 14, -2)
, (36, 15, -1)
, (36, 16, -1)
, (37, 1, 6)
, (37, 2, 1)
, (37, 3, 0)
, (37, 4, 10)
, (37, 5, 6)
, (37, 6, 0)
, (37, 7, 4)
, (37, 8, 5)
, (37, 9, 10)
, (37, 10, 0)
, (37, 11, 5)
, (37, 12, 6)
, (37, 13, 3)
, (37, 14, 1)
, (37, 15, 10)
, (37, 16, 3)
, (38, 1, -9)
, (38, 2, 0)
, (38, 3, -1)
, (38, 4, -9)
, (38, 5, -15)
, (38, 6, 0)
, (38, 7, -3)
, (38, 8, -11)
, (38, 9, -4)
, (38, 10, -2)
, (38, 11, 0)
, (38, 12, -4)
, (38, 13, -2)
, (38, 14, -3)
, (38, 15, -11)
, (38, 16, -6)
, (39, 1, 3)
, (39, 2, 0)
, (39, 3, 9)
, (39, 4, 9)
, (39, 5, 10)
, (39, 6, 2)
, (39, 7, 0)
, (39, 8, 9)
, (39, 9, 0)
, (39, 10, 10)
, (39, 11, 7)
, (39, 12, 8)
, (39, 13, 2)
, (39, 14, 10)
, (39, 15, 0)
, (39, 16, 9)
, (40, 1, 0)
, (40, 2, 0)
, (40, 3, -4)
, (40, 4, -6)
, (40, 5, -6)
, (40, 6, -3)
, (40, 7, -5)
, (40, 8, -10)
, (40, 9, -6)
, (40, 10, -4)
, (40, 11, -1)
, (40, 12, -4)
, (40, 13, -8)
, (40, 14, -6)
, (40, 15, -1)
, (40, 16, -5)
, (41, 1, 4)
, (41, 2, 1)
, (41, 3, 2)
, (41, 4, 2)
, (41, 5, 1)
, (41, 6, 2)
, (41, 7, 9)
, (41, 8, 10)
, (41, 9, 10)
, (41, 10, 1)
, (41, 11, 12)
, (41, 12, 4)
, (41, 13, 2)
, (41, 14, 2)
, (41, 15, 1)
, (41, 16, 2)
, (42, 1, -4)
, (42, 2, -1)
, (42, 3, -2)
, (42, 4, -6)
, (42, 5, 0)
, (42, 6, -2)
, (42, 7, -10)
, (42, 8, -6)
, (42, 9, -6)
, (42, 10, -6)
, (42, 11, -7)
, (42, 12, -13)
, (42, 13, -2)
, (42, 14, -3)
, (42, 15, -10)
, (42, 16, -6)
, (43, 1, 5)
, (43, 2, 3)
, (43, 3, 2)
, (43, 4, 10)
, (43, 5, 2)
, (43, 6, 2)
, (43, 7, 10)
, (43, 8, 4)
, (43, 9, 4)
, (43, 10, 9)
, (43, 11, 10)
, (43, 12, 1)
, (43, 13, 2)
, (43, 14, 0)
, (43, 15, 9)
, (43, 16, 9)
, (44, 1, -3)
, (44, 2, -1)
, (44, 3, -4)
, (44, 4, -1)
, (44, 5, 0)
, (44, 6, -2)
, (44, 7, -9)
, (44, 8, -3)
, (44, 9, -4)
, (44, 10, -2)
, (44, 11, -19)
, (44, 12, -3)
, (44, 13, 0)
, (44, 14, 0)
, (44, 15, -2)
, (44, 16, -1)


select 
	part_name,
	Sum(part_cost * tp_quantity) as check_entry
from prj_transaction_part
	join prj_transaction on transaction_id = tp_transaction_id
	join prj_transaction_type on transaction_type_id=trans_type_id
	join prj_part on part_id=tp_part_id
group by part_name


--1
select * from prj_part

--2
select top 5 * from prj_part
order by part_cost desc

--3
Select trans_type_text, month(transaction_date) as Month, part_name,
part_cost, tp_quantity, (part_cost * tp_quantity) as total_cost
from prj_transaction_part
	join prj_transaction on transaction_id = tp_transaction_id
	join prj_transaction_type on transaction_type_id=trans_type_id
	join prj_part on part_id=tp_part_id
group by transaction_date,trans_type_text, part_name, part_cost, tp_quantity
order by month(transaction_date), trans_type_text, part_name

--4
Select part_name, sum(tp_quantity) as Remaining
from prj_transaction_part
	join prj_part on part_id=tp_part_id
where part_name ='Freeze Ray'
group by part_name

--5
select 
sum(tp_quantity * part_cost) as total_cost
from prj_part
	join prj_transaction_part on part_id = tp_part_id


--6
--start of statement
select trans_type_text , count(transaction_id) as transactions, 
CURRENT_USER as ME, GETDATE() as Today, DB_NAME() as My_Database from prj_transaction_type
	join prj_transaction on transaction_type_id = trans_type_id
group by trans_type_text
--end of statement