DROP DATABASE IF EXISTS bookstore;

CREATE DATABASE bookstore;

GRANT ALL PRIVILEGES ON bookstore.* to art@localhost IDENTIFIED BY 'lee';

USE bookstore;

CREATE TABLE Customer (
	custid INTEGER NOT NULL,
	name VARCHAR(256) NOT NULL,
	phone VARCHAR(64),
	PRIMARY KEY (custid)
);

CREATE TABLE Buy (
	custid INTEGER NOT NULL,	
	bookid INTEGER NOT NULL,
	quantity INTEGER NOT NULL DEFAULT 0,
	PRIMARY KEY (custid, bookid)
);

CREATE TABLE Book (
	bookid INTEGER NOT NULL,
	name VARCHAR(256) NOT NULL,
	author VARCHAR(256),
	price DOUBLE NOT NULL,
	PRIMARY KEY (bookid)
);


INSERT INTO Customer (custid, name, phone) VALUES (0, "Jack", "70410");
INSERT INTO Customer (custid, name, phone) VALUES (1, "Jill", "70411");

INSERT INTO Book (bookid, name, author, price) VALUES (0, "DBMS", "Rama", 140.97);

INSERT INTO Book (bookid, name, author, price) VALUES (1,"Open Life", "Campbell", 11.0);

INSERT INTO Book (bookid, name, author, price) VALUES (2,"What?", "Nagel", 7.95);

INSERT INTO Buy (custid, bookid, quantity) VALUES (0, 0, 3);
INSERT INTO Buy (custid, bookid, quantity) VALUES (0, 1, 5);
INSERT INTO Buy (custid, bookid, quantity) VALUES (0, 2, 7);

SELECT * FROM Customer, Book;

SELECT * FROM Customer;

SELECT * FROM Customer, Book WHERE custid = 0 or (bookid = 2 and price > 100);

SELECT * FROM Customer;

SELECT * FROM Customer, Book;

SELECT * FROM Customer, Book WHERE custid = 0 and bookid = 2;
/**/
