help with sql class 2 questions 1. Report the names of all authors and the title_name of the books (if any) they have written. 2. For each author, report the author name and the number of books that he/she wrote, if any.
help with sql class 2 questions 1. Report the names of all authors and the title_name of the books (if any) they have written. 2. For each author, report the author name and the number of books that he/she wrote, if any.
Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
Related questions
Question
help with sql class
2 questions
1. Report the names of all authors and the title_name of the books (if any) they have written.
2. For each author, report the author name and the number of books that he/she wrote, if any.
Transcribed Image Text:);
INSERT INTO Authors VALUES ('A01', 'Sarah', 'Buchman', '718-496-7223',
¹75 West 205 St', 'Bronx', 'NY', '10468');
INSERT INTO Authors VALUES ('A02', 'Wendy', 'Heydemark', '303-986-7020',
'2922 Baseline Rd', 'Boulder', 'CO', '80303');
INSERT INTO Authors VALUES ('A03', 'Hallie', 'Hull', '415-549-4278',
'3800 Waldo Ave, #14F', 'San Francisco', 'CA', '94123');
INSERT INTO Authors VALUES ('A04', 'Klee', 'Hull', '415-549-4278',
'3800 Waldo Ave, #14F', 'San Francisco', 'CA', '94123');
INSERT INTO Authors VALUES ('A05', 'Christian', 'Kells', '212-771-4680',
'114 Horatio St', 'New York', 'NY', '10014');
INSERT INTO Authors VALUES ('A06', '' , 'Kellsey', '650-836-7128',
'390 Serra Mall', 'Palo Alto', 'CA', '94305');
INSERT INTO Authors VALUES ('A07', 'Paddy', '0''Furniture', '941-925-0752',
'1442 Main St', 'Sarasota', 'FL', '34236');
INSERT INTO Publishers VALUES ('P01', 'Abatis Publishers', 'New York', 'NY', 'USA');
INSERT INTO Publishers VALUES ('P02', 'Core Dump Books', 'San Francisco', 'CA', 'USA');
INSERT INTO Publishers VALUES ('P03', 'Schadenfreude
Press', 'Hamburg', NULL, 'Germany');
INSERT INTO Publishers VALUES ('P04', 'Tenterhooks Press', 'Berkeley', 'CA', 'USA');
INSERT INTO Publishers VALUES ('P05', 'PTR Press', 'Los Angeles', 'CA', 'USA');
INSERT INTO Titles VALUES ('T01', '1977!', 'history', 'P01',
107,21.99,566, '2000-08-01',1);
INSERT INTO Titles VALUES ('T02', '200 Years of German Humor', 'history', 'P03',
14,19.95, 9566, '1998-04-01',1);
INSERT INTO Titles VALUES ('T03', 'Ask Your System Administrator', 'computer', 'P02',
1226, 39.95, 25667, '2000-09-01',1);
INSERT INTO Titles VALUES ('T04', 'But I Did It Unconsciously', 'psychology', 'P04',
510, 12.99, 13001, '1999-05-31',1);
INSERT INTO Titles VALUES ('T05', 'Exchange of Platitudes', 'psychology', 'P04',
201, 6.95, 201440, '2001-01-01',1);
INSERT INTO Titles VALUES ('T06', 'How About Never?', 'biography', 'P01',
473, 19.95, 11320, '2000-07-31',1);
INSERT INTO Titles VALUES ('T07', 'I Blame My Mother', 'biography', 'P03',
333, 23.95, 1500200, '1999-10-01',1);
INSERT INTO Titles VALUES ('T08', 'Just Wait Until After School', 'children', 'P04',
86, 10.00, 4095, '2001-06-01',1);
INSERT INTO Titles VALUES ('T09', 'Kiss My Boo-Boo', 'children', 'P04',
22,13.95, 5000, '2002-05-31',1);
INSERT INTO Titles VALUES ('T10', 'Not Without My Faberge Egg', 'biography', 'P01',
NULL, NULL, NULL, NULL, 0);
INSERT INTO Titles VALUES ('T11', 'Perhaps It''s a Glandular
Problem', 'psychology', 'P04',
826, 7.99, 94123, '2000-11-30',1);
INSERT INTO Titles VALUES('T12', 'Spontaneous, Not Annoying', 'biography', 'P01',
507, 12.99, 100001, '2000-08-31',1);
INSERT INTO Titles VALUES ('T13', 'What Are The Civilian
Applications?', 'history', 'P03',
802, 29.99, 10467, '1999-05-31',1);
INSERT INTO Title Authors VALUES ('T01', 'A01', 1, 1.0);
INSERT INTO Title Authors VALUES ('T02', 'A01', 1, 1.0);
INSERT INTO Title Authors VALUES ('T03', 'A05', 1, 1.0);
INSERT INTO Title Authors VALUES ('T04', 'A03', 1,0.6);
Transcribed Image Text:CREATE TABLE Authors
(
au_id
CHAR (3)
NOT NULL,
au_fname VARCHAR(15)
NOT NULL,
au_lname VARCHAR(15) NOT NULL,
VARCHAR(12)
VARCHAR (20)
VARCHAR(15)
city
state
CHAR (2)
zip
CHAR (5)
CONSTRAINT pk_Authors PRIMARY KEY (au_id)
);
CREATE TABLE Publishers
(
phone
address
pub_id
pub_name
city
state
);
CREATE TABLE Titles
(
title_id CHAR(3)
title_name
type
pub_id
pages
price
sales
country VARCHAR(15) NOT NULL,
CONSTRAINT pk_Publishers PRIMARY KEY (pub_id)
title_id
au_id
au_order
CHAR(3)
NOT NULL,
VARCHAR(20) NOT NULL,
VARCHAR(15) NOT NULL,
CHAR(2)
VARCHAR(40)
VARCHAR(10)
title_id
advance
CHAR(3)
INTEGER
I
DECIMAL (5,2)
INTEGER
DATE
I
I
/
CHAR (3)
CHAR(3)
SMALLINT
"
pubdate
contract SMALLINT
NOT NULL,
CONSTRAINT pk_Titles PRIMARY KEY (title_id)
);
CREATE TABLE Title Authors
NOT NULL,
NOT NULL,
NOT NULL,
royalty_share DECIMAL (5,2) NOT NULL,
CONSTRAINT pk_Title Authors PRIMARY KEY (title_id, au_id)
);
CREATE TABLE Royalties
(
NOT NULL,
NOT NULL,
NOT NULL,
CHAR (3)
DECIMAL (9, 2)
royalty_rate DECIMAL (5,2)
CONSTRAINT pk_Royalties PRIMARY KEÝ (title_id)
NOT NULL,
I
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 3 steps
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Recommended textbooks for you
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education