Exam_2_Final, Sql Server

Exam_2_Final

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

CREATE TABLE users (
id INT IDENTITY PRIMARY KEY,
date_visited datetime    
);

CREATE TABLE shop (
id INT IDENTITY PRIMARY KEY,
user_id INT FOREIGN KEY REFERENCES users(id),    
shopped BIT DEFAULT 1
);

CREATE TABLE checkout (
id INT IDENTITY PRIMARY KEY,
user_id INT FOREIGN KEY REFERENCES users(id),    
checkout BIT DEFAULT 1
);
go

INSERT INTO users (date_visited) VALUES ('01-10-2020 01:39:00'),('01-10-2020 03:20:40'),('01-10-2020 05:20:20');
INSERT INTO shop(user_id) VALUES (1), (2);
INSERT INTO checkout(user_id) VALUES (2);
SELECT * FROM users;
SELECT * FROM shop;
SELECT * FROM checkout;

SELECT * FROM users LEFT JOIN shop ON shop.id = users.id INTERSECT SELECT * FROM users RIGHT JOIN shop ON shop.id = users.id

go
CREATE PROCEDURE graphical_query
AS
SELECT u.id,s.shopped, c.checkout
FROM users u FULL JOIN shop s ON s.user_id = u.id
FULL JOIN checkout c ON s.user_id=c.user_id
--WHERE s.user_id=@id;
go
EXEC graphical_query;
go


run \| edit \| history \| help	0