Author: Not specified Language: sql
Description: Not specified Timestamp: 2017-10-04 00:44:54 +0000
View raw paste Reply
CREATE DATABASE goodreads;
USE goodreads;
CREATE TABLE author(authorId int, name varchar(20) NOT NULL, PRIMARY KEY (authorId));
CREATE TABLE book (isbn varchar(255), title varchar(20), authorId int, numpages int NOT NULL, avgrating decimal(3,2), PRIMARY KEY(isbn), constraint fk1 FOREIGN KEY(authorId) REFERENCES author(authorId));
CREATE TABLE users(uid int, name varchar(20), age int, sex char(1), location varchar(20), birthday date, readCt int, toReadCt int, currentlyReadCt int, PRIMARY KEY(uid));
CREATE TABLE shelf(uid int, isbn varchar(255), name varchar(20), rating decimal(3,2), dateRead date, dateAdded date, PRIMARY KEY(uid, isbn), constraint fk2 FOREIGN KEY(uid) REFERENCES users(uid), constraint fk3 FOREIGN KEY(isbn) REFERENCES book(isbn));
CREATE TABLE friends(uid int, fid int, PRIMARY KEY(uid, fid), constraint fk4 FOREIGN KEY(uid) REFERENCES users(uid), constraint fk5 FOREIGN KEY(fid) REFERENCES users(uid));


INSERT INTO author VALUES (1,"Joe Sacco"),(2,"Tolkien"),(3,"George Martin");
INSERT INTO book VALUES ("9730618260320","ASOS",3,150,2.50),
                                                ("9770618260320","ACOK",3,150,3.47),
                                                ("9780618260300","The Hobbit",2,366,3.40),
                                                ("9780618260301","LOTR 1",2,350,3.40),
                                                ("9780618260320","LOTR 2",2,150,2.50),
                                                ("9781560974321","Palestine",1,288,4.20),
                                                ("9880618260320","AGOT",3,150,2.50);

INSERT INTO users VALUES (1,"User 1",21,"M","India","1992-01-14",10,5,1),
                                                 (2,"User 2",21,"M","USA","1992-02-14",8,10,2),
                                                 (3,"User 3",21,"M","London","1992-03-14",10,20,5),
                                                 (4,"user 4",20,"m","jampit","2000-01-01",10,3,12);

INSERT INTO shelf VALUES (1,"9770618260320","Read",5.00,"2000-01-01","2000-02-02"),
                                                 (1,"9780618260301","Read",5.00,"2000-01-01","2000-01-01"),
                                                 (1,"9780618260320","Read",5.00,"2000-01-01","2000-02-02"),
                                                 (1,"9781560974321","Read",5.00,"2000-01-01","2000-02-02"),
                                                 (2,"9770618260320","To-Read",2.70,NULL,"2000-02-02"),
                                                 (2,"9781560974321","To-Read",2.70,NULL,"2000-02-02"),
                                                 (3,"9770618260320","Read",5.00,"1992-12-11","2000-01-01"),
                                                 (4,"9730618260320","Currently-Reading",5.00,NULL,"2000-01-01"),
                                                 (4,"9780618260301","Currently-Reading",5.00,NULL,"2000-01-01");
INSERT INTO shelf VALUES (2,"9780618260320","Read",4.00,"2000-01-01","2000-02-02");


INSERT INTO friends VALUES (2,1),(3,1),(1,2),(1,3);
 
View raw paste Reply