Assignment #3-Relational Data Retrieval-SQL Read chapter 4 carefully and do the
ID: 3708514 • Letter: A
Question
Assignment #3-Relational Data Retrieval-SQL Read chapter 4 carefully and do the following tasks. 1. The Henry Books Store. Ray Henry is the owner of a bookstore chain named Henry Books. Ray has decided to store his data in a database. He wants to ensure that his data is current and accurate. He also needs to create forms to interact with the data and to produce reports from that data. In addition, he wants to be able to ask questions concerning the data and to obtain answers to these questions easily and quickly Consider the following relational database that Henry uses to keep track of information about his bookstore chain including branches, publishers, authors, and books. Branch number (BRANCH NUM), publisher code (PUBLISHER CODE), author number (AUTHOR_NUM), and book code (BOOK CODE) are all unique fields. In addition, books are categorized by using the following types. (ART - Art, CMP-Computer Science, FIC-Fiction, HIS History, HOR - Horror, MYS-Mystery, PHI Philosophy, POE Poetry, PSY - Psychology, SCI-Science, SFI- Science Fiction, TRA Travel) a. Branch Table NUM-EMPLOYEES 10 15 BRANCH-NUM BRANCH-NAME BRANCH-LOCAT?O Henry Downtown 16 Riverview Henry On The Hill 1289 Bedford Henry Brentwood Brentwood Mall Henry Eastshore Eastshore Mall b. Publisher Table PUBLISHER NAME Arkham House Arcade PublishingNew York Basic Books Berkley Publishing Boston Back Bay Books Course Technology Boston Faweett Books Farrar Straus and Giroux New York HarperCollins Publishers New York Jove Publications Jeremy P. Tarcher Lb Books McPherson and Co.Kingston Penguin USA AH Sauk City W Boulder CO New York BP BY FA New Yorik HC New York Los Angeles New York New YorkExplanation / Answer
Create Statements:-
a. create table Branch(BRANCH_NUM INTEGER PRIMARY KEY,BRANCH_NAME VARCHAR(10),BRANCH_LOCATED VARCHAR(40),NUM_EMPLOYEES INTEGER);
b. create table Publisher(PUBLISHER_CODE CHAR(2) PRIMARY KEY,PUBLISHER_NAME VARCHAR(10),CITY VARCHAR(15));
C. create table Author(AUTHOR_NUM INTEGER PRIMARY KEY,AUTHOR_LAST VARCHAR(15),AUTHOR_FIRST VARCHAR(10));
D. create table Book(BOOK_CODE INTEGER PRIMARY KEY,TITLE VARCHAR(10),PUBLISHER_CODE INTEGER,TYPE VARCHAR(5),PRICE DECIMAL(4,2),PAPERBACK CHAR(1),FOREIGN KEY(PUBLISHER_CODE) REFERENCES Publisher(PUBLISHER_CODE));
Insert Statements:-
Below are the sample insert statements which inserts 2 rows in each table. Change the values and execute the statement to insert new rows in the respected table.
a. insert into Branch values (1,'Henry','Downtown 16 Riverview',10);
insert into Branch values (2,'Henry','On the Hill 1289 BedFord',6);
b. insert into Publisher values ('AH','Arkham House','Sauk City WI');
insert into Publisher values ('AP','Arcade Publishing','New York');
c. insert into Author values (1,'Morrison','Toni');
insert into Author values (2,'Solotarolf','Paul');
d. 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');
Queries:-
a. select BRANCH_NAME,BRANCH_LOCATION,NUM_EMPLOYEES from Branch group by BRANCH_NAME;
b. select PUBLISHER_CODE,PUBLISHER_NAME from Publisher where CITY='New York';
c. select BOOK_CODE,TITLE,PRICE,TYPE FROM Book b,Publisher p where b.PUBLISHRE_CODE=p.PUBLISHER_CODE and p.PUBLISHER_NAME='Penguine USA';
d. select AUTHOR_NUM,AUTHOUR_FIRST from Author where AUTHOR_LAST like '%s';
As per the chegg policy I am allowed to answer this number of questions only. Please post the remaining as new post.