Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --DDL
- -- (1) DROPS
- DROP VIEW IF EXISTS liban_artifacts;
- DROP VIEW IF EXISTS student_staff;
- DROP TABLE IF EXISTS book_loan;
- DROP TABLE IF EXISTS slide_loan;
- DROP TABLE IF EXISTS slides;
- DROP TABLE IF EXISTS books;
- DROP TABLE IF EXISTS book_shelf;
- DROP TABLE IF EXISTS slide_shelf;
- DROP TABLE IF EXISTS artifact_card;
- DROP TABLE IF EXISTS location_segment;
- DROP TABLE IF EXISTS dig;
- DROP TABLE IF EXISTS artifact;
- DROP TABLE IF EXISTS staff;
- -- (2) CREATE TABLES
- CREATE TABLE staff(
- PRIMARY KEY(staff_ID),
- staff_ID VARCHAR(8),
- staff_role VARCHAR(20) NOT NULL,
- staff_name VARCHAR(30) NOT NULL,
- date_registered VARCHAR(10) NOT NULL,
- );
- CREATE TABLE artifact(
- PRIMARY KEY(number, grid, depth),
- number INT,
- grid INT,
- depth INT,
- art_owner VARCHAR(8),
- art_date INT,
- FOREIGN KEY (art_owner)
- REFERENCES staff(staff_ID)
- );
- CREATE TABLE dig(
- dig_name VARCHAR(50) NOT NULL,
- dig_no INT,
- dig_date VARCHAR(10),
- worker VARCHAR(8),
- PRIMARY KEY(dig_name, dig_no),
- FOREIGN KEY (worker)
- REFERENCES staff(staff_ID)
- );
- CREATE TABLE location_segment(
- location_ID VARCHAR(8),
- PRIMARY KEY(location_ID)
- );
- CREATE TABLE artifact_card(
- Notes VARCHAR(140),
- card_ID INT,
- current_pos VARCHAR(8),
- dig_origin_no INT,
- dig_origin_name VARCHAR(50),
- dig_grid INT,
- dig_depth INT,
- FOREIGN KEY (card_ID, dig_grid, dig_depth)
- REFERENCES artifact(number, grid, depth),
- FOREIGN KEY (current_pos)
- REFERENCES location_segment(location_ID),
- FOREIGN KEY (dig_origin_name, dig_origin_no)
- REFERENCES dig(dig_name, dig_no),
- );
- CREATE TABLE slide_shelf(
- s_shelf_number VARCHAR(5),
- PRIMARY KEY (s_shelf_number)
- );
- CREATE TABLE book_shelf(
- b_shelf_number VARCHAR(5),
- PRIMARY KEY (b_shelf_number)
- );
- CREATE TABLE books(
- book_number VARCHAR(6),
- title VARCHAR(30) NOT NULL,
- author VARCHAR(30) NOT NULL,
- home_shelf VARCHAR(5),
- PRIMARY KEY(book_number),
- FOREIGN KEY (home_shelf)
- REFERENCES book_shelf(b_shelf_number)
- );
- CREATE TABLE slides(
- slide_number VARCHAR(6),
- title VARCHAR(30) NOT NULL,
- author VARCHAR(30),
- slide_category VARCHAR(30),
- home_shelf VARCHAR(5),
- PRIMARY KEY(slide_number),
- FOREIGN KEY (home_shelf)
- REFERENCES slide_shelf(s_shelf_number)
- );
- CREATE TABLE slide_loan(
- start_date_loan_s INT,
- end_date_loan_s INT,
- slide_borrower VARCHAR(8),
- slide_ID VARCHAR(6),
- PRIMARY KEY(start_date_loan_s),
- FOREIGN KEY (slide_borrower)
- REFERENCES staff(staff_ID),
- FOREIGN KEY (slide_ID)
- REFERENCES slides(slide_number),
- );
- CREATE TABLE book_loan(
- start_date_loan INT,
- end_date_loan INT,
- book_borrower VARCHAR(8),
- book_ID VARCHAR(6),
- PRIMARY KEY(start_date_loan),
- FOREIGN KEY (book_borrower)
- REFERENCES staff(staff_ID),
- FOREIGN KEY (book_ID)
- REFERENCES books(book_number)
- );
- -- (3) INSERT VALUES
- INSERT INTO slide_shelf(s_shelf_number)
- VALUES('O351'),
- ('C123'),
- ('K362'),
- ('I994'),
- ('Q122'),
- ('P125'),
- ('Y156');
- INSERT INTO book_shelf(b_shelf_number)
- VALUES ('W126'),
- ('J563'),
- ('U898'),
- ('H759'),
- ('O340'),
- ('L998'),
- ('E127'),
- ('H784');
- INSERT INTO staff(staff_ID, staff_role, staff_name, date_registered)
- VALUES('libra180','Proffessor', 'Liban Rask', 19450505),
- ('vserf495', 'Excavator', 'Vsera Rfoman', 20080905),
- ('lesar427', 'Proffessor', 'Lesman Armondol', 19951202),
- ('babol666', 'Excavator', 'Barbro Olev', 19970706),
- ('Jndio123', 'Excavator', 'Jndiana Iones', 19420713),
- ('johdo321', 'Student', 'John Doe', 19650621),
- ('namna146', 'Student', 'Name Nameson', 19990214),
- ('bombe534', 'Professor', 'Bom Betty', 1986042);
- INSERT INTO slides(slide_number, title, author, slide_category, home_shelf)
- VALUES('23-001', '15-century Swords', 'Barlos Cardos', 'Science', 'O351'),
- ('33-001', '18-century Dagger', 'Brevor Tartly', 'Art', 'C123'),
- ('23-002', 'Old rock formation', 'Jarl Carl', 'Religion', 'K362'),
- ('33-002', 'legacy grimstroke', 'Timon Simon', 'History', 'P125');
- INSERT INTO books(book_number, title, author)
- VALUES('JO5678', 'How to train your dragon', 'Carlos Bardos'),
- ('BO1337', 'The good life', 'Trevor Bartly'),
- ('PB7583', 'Burning birdges', 'Carl Jarl'),
- ('AS7548', 'Relation list', 'Simon Timon'),
- ('BO1234', 'The Lord Of The Rings', 'J.R.R Tolkien'),
- ('GS1536', 'Lord of the Flies', 'William Golding'),
- ('PO6845', 'SQL For Dummies', 'Robinson Crusoe');
- INSERT INTO book_loan(start_date_loan, end_date_loan, book_borrower, book_ID)
- VALUES (20181203, NULL, 'libra180', 'JO5678'),
- (20111107, 20181224, 'libra180', 'BO1337'),
- (20140514, 20180713, 'vserf495', 'PB7583' ),
- (20050315, 20180305, 'babol666', 'AS7548'),
- (20130325, 20181203, 'lesar427', 'JO5678');
- INSERT INTO slide_loan(start_date_loan_s, end_date_loan_s, slide_borrower, slide_ID)
- VALUES (20180302 , NULL, 'libra180', '23-001'),
- (20161207, 20181224, 'vserf495', '33-001'),
- (20180514, NULL, 'babol666', '23-002'),
- (20060315, 20180305, 'lesar427', '33-002');
- INSERT INTO artifact(number, grid, depth, art_owner, art_date)
- VALUES (25, 45.46, 256, 'libra180', 19450505),
- (45, 23.27, 322, 'vserf495', 20080905),
- (38, 10.12, 800, 'lesar427', 19951202),
- (10, 32.30, 300, 'babol666', 19970706),
- (13, 55.76, 450, 'johdo321', 20090507),
- (43, 32.56, 145, 'namna146', 20070928),
- (66, 45.87, 201, 'jndio123', 20120128),
- (23, 13.67, 384, 'bombe534', 20070717),
- (43, 54.98, 158, 'babol666', 20010101),
- (24, 43.11, 168, 'libra180', 19770325),
- (67, 85.96, 631, 'vserf495', 20060725),
- (85, 99.94, 351, 'lesar427', 20020523),
- (54, 24.62, 842, 'babol666', 20100505),
- (70, 17.63, 471, 'johdo321', 20130704),
- (20, 52.73, 279, 'namna146', 19940808),
- (36, 66.27, 417, 'jndio123', 20170313),
- (17, 81.47, 749, 'bombe534', 20140224),
- (63, 53.33, 826, 'babol666', 20021224);
- INSERT INTO dig(dig_name, dig_no, dig_date, worker)
- VALUES ('Tomb of Qin Shi Huangdi', 23, 19970904, 'vserf495'),
- ('Ziggurat of Ur', 33, 18859394, 'babol666'),
- ('Moche huaca', 55, 19330506, 'lesar427'),
- ('Pyramids of Giza', 11, 20050304, 'libra180'),
- ('the Great Pyramid of Khufu', 77, 20130207, 'libra180'),
- ('Teotihuacan', 88, 20010709, 'babol666'),
- ('Pyramid of the Sun at Teotihuacan', 84, 20020305, 'lesar427'),
- ('Sunken temple of Caral', 67, 20070707, 'vserf495'),
- ('Karazhan', 51, 20031203, 'vserf495'),
- ('Razorfen downs', 09, 20010112, 'libra180'),
- ('Uldaman', 99, 20060112, 'libra180'),
- ('Uldaman', 77, 20000203, 'libra180');
- INSERT INTO location_segment(location_ID)
- VALUES ('SH01'),
- ('SH02'),
- ('SH03'),
- ('SH04'),
- ('MU01'),
- ('MU02'),
- ('MU03'),
- ('MU04'),
- ('ST01'),
- ('ST02'),
- ('ST03'),
- ('ST04');
- INSERT INTO artifact_card(Notes, dig_origin_no, current_pos, card_ID, dig_grid, dig_depth, dig_origin_name)
- VALUES ('epic instance!' , 77, 'SH01', 25, 45.46, 256, 'Uldaman'),
- ('great big dig', 99, 'ST01', 45, 23.27, 322, 'Uldaman'),
- ('Amazing place!', 55, 'MU01', 85, 99.94, 351, 'Moche huaca'),
- ('impressive artwork', 67, 'ST04', 10, 32.30, 300, 'Sunken temple of Caral');
- -- (4) CREATE VIEWS
- /*Påvisa databasens funktionalitet med SQL. a.Skapa tre vyer (views) som sammanställer data från era tabeller.
- b.Skapa fyra frågor (queries) för att demonstrera era tabeller i användning
- i.Alla frågor ska använda sig av ORDER BY eller GROUP BY
- ii.Max en simpelfråga (Fråga utan join, aggregatfunktion och villkor)
- iii.Minst två frågor som ska använda Villkor (WHERE)
- iv.En fråga som använder aggregatfunktioner (MAX, SUM, AVG, etc.). c.Tänk på att koppla ihop tabeller med INNER JOIN */
- GO
- CREATE VIEW student_staff AS
- SELECT staff_name, staff_role
- FROM staff
- WHERE staff_role = 'Student'
- GO
- SELECT * FROM student_staff
- GO
- CREATE VIEW liban_artifacts AS
- SELECT number, art_owner
- FROM artifact
- WHERE art_owner = 'libra180'
- GO
- SELECT * FROM liban_artifacts
- SELECT slide_number
- FROM slides
- ORDER BY slide_number ASC;
- SELECT AVG(depth)
- FROM artifact
- SELECT book_borrower, book_ID
- FROM staff
- INNER JOIN book_loan ON book_loan.book_borrower = staff.staff_ID
- -- (5) CREATE TRIGGERS/PROCEDURES
- -- foreign keys ska släppas först?
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement