Download and run Henry.sql in MySQL Workbench. It created 6 tables, please take
ID: 3838562 • Letter: D
Question
Download and run Henry.sql in MySQL Workbench. It created 6 tables, please take a look at the BOOK table.
Henry.sql:
SET SQL_SAFE_UPDATES = 0;
CREATE DATABASE Henry;
Use Henry;
CREATE TABLE AUTHOR
(AUTHOR_NUM DECIMAL(2,0) PRIMARY KEY,
AUTHOR_LAST CHAR(12),
AUTHOR_FIRST CHAR(10) );
CREATE TABLE BOOK
(BOOK_CODE CHAR(4) PRIMARY KEY,
TITLE CHAR(40),
PUBLISHER_CODE CHAR(3),
TYPE CHAR(3),
PRICE DECIMAL(4,2),
PAPERBACK CHAR(1) );
CREATE TABLE BRANCH
(BRANCH_NUM DECIMAL(2,0) PRIMARY KEY,
BRANCH_NAME CHAR(50),
BRANCH_LOCATION CHAR(50),
NUM_EMPLOYEES DECIMAL(2,0) );
CREATE TABLE INVENTORY
(BOOK_CODE CHAR(4),
BRANCH_NUM DECIMAL(2,0),
ON_HAND DECIMAL(2,0),
PRIMARY KEY (BOOK_CODE, BRANCH_NUM) );
CREATE TABLE PUBLISHER
(PUBLISHER_CODE CHAR(3) PRIMARY KEY,
PUBLISHER_NAME CHAR(25),
CITY CHAR(20) );
CREATE TABLE WROTE
(BOOK_CODE CHAR(4),
AUTHOR_NUM DECIMAL(2,0),
SEQUENCE DECIMAL(1,0),
PRIMARY KEY (BOOK_CODE, AUTHOR_NUM) );
INSERT INTO AUTHOR VALUES (1,'Morrison','Toni');
INSERT INTO AUTHOR VALUES (2,'Solotaroff','Paul');
INSERT INTO AUTHOR VALUES (3,'Vintage','Vernor');
INSERT INTO AUTHOR VALUES (4,'Francis','Dick');
INSERT INTO AUTHOR VALUES (5,'Straub','Peter');
INSERT INTO AUTHOR VALUES (6,'King','Stephen');
INSERT INTO AUTHOR VALUES (7,'Pratt','Philip');
INSERT INTO AUTHOR VALUES (8,'Chase','Truddi');
INSERT INTO AUTHOR VALUES (9,'Collins','Bradley');
INSERT INTO AUTHOR VALUES (10,'Heller','Joseph');
INSERT INTO AUTHOR VALUES (11,'Wills','Gary');
INSERT INTO AUTHOR VALUES (12,'Hofstadter','Douglas R.');
INSERT INTO AUTHOR VALUES (13,'Lee','Harper');
INSERT INTO AUTHOR VALUES (14,'Ambrose','Stephen E.');
INSERT INTO AUTHOR VALUES (15,'Rowling','J.K.');
INSERT INTO AUTHOR VALUES (16,'Salinger','J.D.');
INSERT INTO AUTHOR VALUES (17,'Heaney','Seamus');
INSERT INTO AUTHOR VALUES (18,'Camus','Albert');
INSERT INTO AUTHOR VALUES (19,'Collins, Jr.','Bradley');
INSERT INTO AUTHOR VALUES (20,'Steinbeck','John');
INSERT INTO AUTHOR VALUES (21,'Castelman','Riva');
INSERT INTO AUTHOR VALUES (22,'Owen','Barbara');
INSERT INTO AUTHOR VALUES (23,'O''Rourke','Randy');
INSERT INTO AUTHOR VALUES (24,'Kidder','Tracy');
INSERT INTO AUTHOR VALUES (25,'Schleining','Lon');
INSERT INTO BOOK VALUES ('0180','A Deepness in the Sky','TB','SFI',7.19,'Y');
INSERT INTO BOOK VALUES ('0189','Magic Terror','FA','HOR',7.99,'Y');
INSERT INTO BOOK VALUES ('0200','The Stranger','VB','FIC',8.00,'Y');
INSERT INTO BOOK VALUES ('0378','Venice','SS','ART',24.50,'N');
INSERT INTO BOOK VALUES ('079X','Second Wind','PU','MYS',24.95,'N');
INSERT INTO BOOK VALUES ('0808','The Edge','JP','MYS',6.99,'Y');
INSERT INTO BOOK VALUES ('1351','Dreamcatcher: A Novel','SC','HOR',19.60,'N');
INSERT INTO BOOK VALUES ('1382','Treasure Chests','TA','ART',24.46,'N');
INSERT INTO BOOK VALUES ('138X','Beloved','PL','FIC',12.95,'Y');
INSERT INTO BOOK VALUES ('2226','Harry Potter and the Prisoner of Azkaban','ST','SFI',13.96,'N');
INSERT INTO BOOK VALUES ('2281','Van Gogh and Gauguin','WP','ART',21.00,'N');
INSERT INTO BOOK VALUES ('2766','Of Mice and Men','PE','FIC',6.95,'Y');
INSERT INTO BOOK VALUES ('2908','Electric Light','FS','POE',14.00,'N');
INSERT INTO BOOK VALUES ('3350','Group: Six People in Search of a Life','BP','PSY',10.40,'Y');
INSERT INTO BOOK VALUES ('3743','Nine Stories','LB','FIC',5.99,'Y');
INSERT INTO BOOK VALUES ('3906','The Soul of a New Machine','BY','SCI',11.16,'Y');
INSERT INTO BOOK VALUES ('5163','Travels with Charley','PE','TRA',7.95,'Y');
INSERT INTO BOOK VALUES ('5790','Catch-22','SC','FIC',12.00,'Y');
INSERT INTO BOOK VALUES ('6128','Jazz','PL','FIC',12.95,'Y');
INSERT INTO BOOK VALUES ('6328','Band of Brothers','TO','HIS',9.60,'Y');
INSERT INTO BOOK VALUES ('669X','A Guide to SQL','CT','CMP',37.95,'Y');
INSERT INTO BOOK VALUES ('6908','Franny and Zooey','LB','FIC',5.99,'Y');
INSERT INTO BOOK VALUES ('7405','East of Eden','PE','FIC',12.95,'Y');
INSERT INTO BOOK VALUES ('7443','Harry Potter and the Goblet of Fire','ST','SFI',18.16,'N');
INSERT INTO BOOK VALUES ('7559','The Fall','VB','FIC',8.00,'Y');
INSERT INTO BOOK VALUES ('8092','Godel, Escher, Bach','BA','PHI',14.00,'Y');
INSERT INTO BOOK VALUES ('8720','When Rabbit Howls','JP','PSY',6.29,'Y');
INSERT INTO BOOK VALUES ('9611','Black House','RH','HOR',18.81,'N');
INSERT INTO BOOK VALUES ('9627','Song of Solomon','PL','FIC',14.00,'Y');
INSERT INTO BOOK VALUES ('9701','The Grapes of Wrath','PE','FIC',13.00,'Y');
INSERT INTO BOOK VALUES ('9882','Slay Ride','JP','MYS',6.99,'Y');
INSERT INTO BOOK VALUES ('9883','The Catcher in the Rye','LB','FIC',5.99,'Y');
INSERT INTO BOOK VALUES ('9931','To Kill a Mockingbird','HC','FIC',18.00,'N');
INSERT INTO BRANCH
VALUES
(1,'Henry Downtown','16 Riverview',10);
INSERT INTO BRANCH
VALUES
(2,'Henry On The Hill','1289 Bedford',6);
INSERT INTO BRANCH
VALUES
(3,'Henry Brentwood','Brentwood Mall',15);
INSERT INTO BRANCH
VALUES
(4,'Henry Eastshore','Eastshore Mall',9);
INSERT INTO INVENTORY
VALUES
('0180',1,2);
INSERT INTO INVENTORY
VALUES
('0189',2,2);
INSERT INTO INVENTORY
VALUES
('0200',1,1);
INSERT INTO INVENTORY
VALUES
('0200',2,3);
INSERT INTO INVENTORY
VALUES
('0378',3,2);
INSERT INTO INVENTORY
VALUES
('079X',2,1);
INSERT INTO INVENTORY
VALUES
('079X',3,2);
INSERT INTO INVENTORY
VALUES
('079X',4,3);
INSERT INTO INVENTORY
VALUES
('0808',2,1);
INSERT INTO INVENTORY
VALUES
('1351',2,4);
INSERT INTO INVENTORY
VALUES
('1351',3,2);
INSERT INTO INVENTORY
VALUES
('1382',2,1);
INSERT INTO INVENTORY
VALUES
('138X',2,3);
INSERT INTO INVENTORY
VALUES
('2226',1,3);
INSERT INTO INVENTORY
VALUES
('2226',3,2);
INSERT INTO INVENTORY
VALUES
('2226',4,1);
INSERT INTO INVENTORY
VALUES
('2281',4,3);
INSERT INTO INVENTORY
VALUES
('2766',3,2);
INSERT INTO INVENTORY
VALUES
('2908',1,3);
INSERT INTO INVENTORY
VALUES
('2908',4,1);
INSERT INTO INVENTORY
VALUES
('3350',1,2);
INSERT INTO INVENTORY
VALUES
('3743',2,1);
INSERT INTO INVENTORY
VALUES
('3906',2,1);
INSERT INTO INVENTORY
VALUES
('3906',3,2);
INSERT INTO INVENTORY
VALUES
('5163',1,1);
INSERT INTO INVENTORY
VALUES
('5790',4,2);
INSERT INTO INVENTORY
VALUES
('6128',2,4);
INSERT INTO INVENTORY
VALUES
('6128',3,3);
INSERT INTO INVENTORY
VALUES
('6328',2,2);
INSERT INTO INVENTORY
VALUES
('669X',1,1);
INSERT INTO INVENTORY
VALUES
('6908',2,2);
INSERT INTO INVENTORY
VALUES
('7405',3,2);
INSERT INTO INVENTORY
VALUES
('7443',4,1);
INSERT INTO INVENTORY
VALUES
('7559',2,2);
INSERT INTO INVENTORY
VALUES
('8092',3,1);
INSERT INTO INVENTORY
VALUES
('8720',1,3);
INSERT INTO INVENTORY
VALUES
('9611',1,2);
INSERT INTO INVENTORY
VALUES
('9627',3,5);
INSERT INTO INVENTORY
VALUES
('9627',4,2);
INSERT INTO INVENTORY
VALUES
('9701',1,2);
INSERT INTO INVENTORY
VALUES
('9701',2,1);
INSERT INTO INVENTORY
VALUES
('9701',3,3);
INSERT INTO INVENTORY
VALUES
('9701',4,2);
INSERT INTO INVENTORY
VALUES
('9882',3,3);
INSERT INTO INVENTORY
VALUES
('9883',2,3);
INSERT INTO INVENTORY
VALUES
('9883',4,2);
INSERT INTO INVENTORY
VALUES
('9931',1,2);
INSERT INTO PUBLISHER
VALUES
('AH','Arkham House','Sauk City WI');
INSERT INTO PUBLISHER
VALUES
('AP','Arcade Publishing','New York');
INSERT INTO PUBLISHER
VALUES
('BA','Basic Books','Boulder CO');
INSERT INTO PUBLISHER
VALUES
('BP','Berkley Publishing','Boston');
INSERT INTO PUBLISHER
VALUES
('BY','Back Bay Books','New York');
INSERT INTO PUBLISHER
VALUES
('CT','Course Technology','Boston');
INSERT INTO PUBLISHER
VALUES
('FA','Fawcett Books','New York');
INSERT INTO PUBLISHER
VALUES
('FS','Farrar Straus and Giroux','New York');
INSERT INTO PUBLISHER
VALUES
('HC','HarperCollins Publishers','New York');
INSERT INTO PUBLISHER
VALUES
('JP','Jove Publications','New York');
INSERT INTO PUBLISHER
VALUES
('JT','Jeremy P. Tarcher','Los Angeles');
INSERT INTO PUBLISHER
VALUES
('LB','Lb Books','New York');
INSERT INTO PUBLISHER
VALUES
('MP','McPherson and Co.','Kingston');
INSERT INTO PUBLISHER
VALUES
('PE','Penguin USA','New York');
INSERT INTO PUBLISHER
VALUES
('PL','Plume','New York');
INSERT INTO PUBLISHER
VALUES
('PU','Putnam Publishing Group','New York');
INSERT INTO PUBLISHER
VALUES
('RH','Random House','New York');
INSERT INTO PUBLISHER
VALUES
('SB','Schoken Books','New York');
INSERT INTO PUBLISHER
VALUES
('SC','Scribner','New York');
INSERT INTO PUBLISHER
VALUES
('SS','Simon and Schuster','New York');
INSERT INTO PUBLISHER
VALUES
('ST','Scholastic Trade','New York');
INSERT INTO PUBLISHER
VALUES
('TA','Taunton Press','Newtown CT');
INSERT INTO PUBLISHER
VALUES
('TB','Tor Books','New York');
INSERT INTO PUBLISHER
VALUES
('TH','Thames and Hudson','New York');
INSERT INTO PUBLISHER
VALUES
('TO','Touchstone Books','Westport CT');
INSERT INTO PUBLISHER
VALUES
('VB','Vintage Books','New York');
INSERT INTO PUBLISHER
VALUES
('WN','W.W. Norton','New York');
INSERT INTO PUBLISHER
VALUES
('WP','Westview Press','Boulder CO');
INSERT INTO WROTE
VALUES
('0180',3,1);
INSERT INTO WROTE
VALUES
('0189',5,1);
INSERT INTO WROTE
VALUES
('0200',18,1);
INSERT INTO WROTE
VALUES
('0378',11,1);
INSERT INTO WROTE
VALUES
('079X',4,1);
INSERT INTO WROTE
VALUES
('0808',4,1);
INSERT INTO WROTE
VALUES
('1351',6,1);
INSERT INTO WROTE
VALUES
('1382',23,2);
INSERT INTO WROTE
VALUES
('1382',25,1);
INSERT INTO WROTE
VALUES
('138X',1,1);
INSERT INTO WROTE
VALUES
('2226',15,1);
INSERT INTO WROTE
VALUES
('2281',9,2);
INSERT INTO WROTE
VALUES
('2281',19,1);
INSERT INTO WROTE
VALUES
('2766',20,1);
INSERT INTO WROTE
VALUES
('2908',17,1);
INSERT INTO WROTE
VALUES
('3350',2,1);
INSERT INTO WROTE
VALUES
('3743',16,1);
INSERT INTO WROTE
VALUES
('3906',24,1);
INSERT INTO WROTE
VALUES
('5163',20,1);
INSERT INTO WROTE
VALUES
('5790',10,1);
INSERT INTO WROTE
VALUES
('6128',1,1);
INSERT INTO WROTE
VALUES
('6328',14,1);
INSERT INTO WROTE
VALUES
('669X',7,1);
INSERT INTO WROTE
VALUES
('6908',16,1);
INSERT INTO WROTE
VALUES
('7405',20,1);
INSERT INTO WROTE
VALUES
('7443',15,1);
INSERT INTO WROTE
VALUES
('7559',18,1);
INSERT INTO WROTE
VALUES
('8092',12,1);
INSERT INTO WROTE
VALUES
('8720',8,1);
INSERT INTO WROTE
VALUES
('9611',5,2);
INSERT INTO WROTE
VALUES
('9611',6,1);
INSERT INTO WROTE
VALUES
('9627',1,1);
INSERT INTO WROTE
VALUES
('9701',20,1);
INSERT INTO WROTE
VALUES
('9882',4,1);
INSERT INTO WROTE
VALUES
('9883',16,1);
INSERT INTO WROTE
VALUES
('9931',13,1);
create a table level1_book using the following code:
finish the following operations on the table level1_book:
Note: all the following manipulations are on the table level1_book, not the BOOK table!
Get data from BOOK table for all books with price great than 15.
Change the book title to "Database Management" for the book with code "669X"
Change the book price to 90 for books with type 'CMP' and has paperback ('Y').
Delete all books with price less than 19
Change the structure of the table level1_book through adding a new column "english" with the type "char(1), and set the value of the column english to 'Y'.
Show the final result of the table level1_book through using MySQL script: select * from level1_book.
create table level 1 book book code char (4) primary key, title char (40) type char (3) price decimal (4,2), paperback char (1)Explanation / Answer
create table level1_book
(
book_code char(4) primary key,
title char(40),
type char(3),
price decimal(4,2),
paperback char(1)
);
INSERT INTO level1_book VALUES ('0180','A Deepness in the Sky','SFI',7.19,'Y');
INSERT INTO level1_book VALUES ('0189','Magic Terror','HOR',7.99,'Y');
INSERT INTO level1_book VALUES ('0200','The Stranger','FIC',8.00,'Y');
INSERT INTO level1_book VALUES ('0378','Venice','SS',24.50,'N');
INSERT INTO level1_book VALUES ('079X','Second Wind','MYS',24.95,'N');
INSERT INTO level1_book VALUES ('0808','The Edge','MYS',6.99,'Y');
INSERT INTO level1_book VALUES ('1351','Dreamcatcher: A Novel','HOR',19.60,'N');
INSERT INTO level1_book VALUES ('1382','Treasure Chests','ART',24.46,'N');
INSERT INTO level1_book VALUES ('138X','Beloved','FIC',12.95,'Y');
INSERT INTO level1_book VALUES ('2226','Harry Potter and the Prisoner of Azkaban','SFI',13.96,'N');
INSERT INTO level1_book VALUES ('2281','Van Gogh and Gauguin','ART',21.00,'N');
INSERT INTO level1_book VALUES ('2766','Of Mice and Men','FIC',6.95,'Y');
INSERT INTO level1_book VALUES ('2908','Electric Light','POE',14.00,'N');
INSERT INTO level1_book VALUES ('3350','Group: Six People in Search of a Life','PSY',10.40,'Y');
INSERT INTO level1_book VALUES ('3743','Nine Stories','FIC',5.99,'Y');
INSERT INTO level1_book VALUES ('3906','The Soul of a New Machine','SCI',11.16,'Y');
INSERT INTO level1_book VALUES ('5163','Travels with Charley','TRA',7.95,'Y');
INSERT INTO level1_book VALUES ('5790','Catch-22','FIC',12.00,'Y');
INSERT INTO level1_book VALUES ('6128','Jazz','FIC',12.95,'Y');
INSERT INTO level1_book VALUES ('6328','Band of Brothers','HIS',9.60,'Y');
INSERT INTO level1_book VALUES ('669X','A Guide to SQL','CMP',37.95,'Y');
INSERT INTO level1_book VALUES ('6908','Franny and Zooey','FIC',5.99,'Y');
INSERT INTO level1_book VALUES ('7405','East of Eden','FIC',12.95,'Y');
INSERT INTO level1_book VALUES ('7443','Harry Potter and the Goblet of Fire','SFI',18.16,'N');
INSERT INTO level1_book VALUES ('7559','The Fall','FIC',8.00,'Y');
INSERT INTO level1_book VALUES ('8092','Godel, Escher, Bach','PHI',14.00,'Y');
INSERT INTO level1_book VALUES ('8720','When Rabbit Howls','PSY',6.29,'Y');
INSERT INTO level1_book VALUES ('9611','Black House','HOR',18.81,'N');
INSERT INTO level1_book VALUES ('9627','Song of Solomon','FIC',14.00,'Y');
INSERT INTO level1_book VALUES ('9701','The Grapes of Wrath','FIC',13.00,'Y');
INSERT INTO level1_book VALUES ('9882','Slay Ride','MYS',6.99,'Y');
INSERT INTO level1_book VALUES ('9883','The Catcher in the Rye','FIC',5.99,'Y');
INSERT INTO level1_book VALUES ('9931','To Kill a Mockingbird','FIC',18.00,'N');
select * from dbo.level1_book where PRICE>15
update dbo.level1_book
set title='Database Management'
where book_code='669X'
update dbo.level1_book
set price=37.97
where TYPE='cmp' and paperback ='Y'
delete from dbo.level1_book where price<19
alter table level1_book
add english char(1)
update level1_book
set english='Y'
select * from level1_book
result is:
book_code title type price paperback english
0378 Venice SS 24.50 N Y
079X Second Wind MYS 24.95 N Y
1351 Dreamcatcher: A Novel HOR 19.60 N Y
1382 Treasure Chests ART 24.46 N Y
2281 Van Gogh and Gauguin ART 21.00 N Y
669X Database Management CMP 37.97 Y Y
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.