Create the following queries for the “books” database. For each query make sure to only include those tables that are necessary to answer the question. Your answers for each of these questions should consist of the SQL query that will produce these lists, NOT the list itself.

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
icon
Related questions
Question
100%

Create the following queries for the “books” database. For each query make sure to only include those tables that are necessary to answer the question. Your answers for each of these questions should consist of the SQL query that will produce these lists, NOT the list itself.

  1. For each type of book (e.g. biography, children, etc), show the number of those books that are 450 pages or longer. The column heading should be '# of long books'.

  2. Modify the answer to the previous question so that only those types for which there are at least 2 long books are displayed

  3. For each publisher, list the name of the publisher and total number of pages that the publisher has published in all their books (ie. add up all the pages in all books for each publisher). Show the publishers who have published the most pages at the top of the result list.

  4. Modify the previous query so that only publishers who have published at least 1250 pages will show up.

  5. List each publisher's name and the numbers of pages in their longest and shortest books. Sort the results by the publisher's name.

  6. List the authors first and last names, the author's state, the title name, the publisher name and the publishers state for all books.

  7. List the title, number of pages, authors first and last names and author's state for all books that are longer than 150 pages and whose author lives in NY or CA.

  8. List the authors first and last names, the author's state, the title name, the publisher name and the publishers state only for those books where the author and publisher are from the same state. (HINT: specify authors.state [note: there is a period between author and state] in the criteria for the publisher's state).

title_authors table: Each row in this table represents a relationship between an
author and a book that (s)he wrote. Ifa book has 3 authors, there will be 3 rows in
this table for that book. This table has a composite primary key (titie_id, au_id).
The "royalty_share" field represents the percent of the royalties that this author
receives. If there is only one author for a book then this will be 1. If there are two
authors for the book, the "royalty_share" could be the same for both or it could be
different (eg. 0.6 and 0.4).
Data Type
Fidld Name
Vlite id
au_d
au_order
royaly_shere
Oescription
Lnique tile identifie
Author identifier
Author name order on book cover
Author fractional royaty share
Text
Text
Number
Number
royalties table: This table the information about royalties that the publisher will
pay to the author(s) of a book. "advance" is the fixed amount that the publisher
pays the author when (s)he starts to write the book. "royalty_rate" is a decimal
value that contains the percent of each book's sale price that is paid to the
author(s). If there is more than one author then the royalties and advaces are split
between them
Deta Type
Text
Field Neme
le jd
advance
royaty rate
Desription
Tle dentiie
Upfrork payment to auchor(s)
Fraction of revenue pad author(s)
Mumber
Mumber
The data
Publishers:
pub_id pub_name
city
New York
Core Dump Books San Francisco CA
state country
PO1
Abatis Publishers
NY
USA
P02
USA
РОЗ
Schadenfreude Press Hamburg
Germany
P04
Tenterhooks Press
Berkeley
Berkeley
CA
USA
POS
AAA Publishing
CA
USA
Titles:
title_id title_name
pub_i pages price sales
P01 107 21.99 566
P03
pubdate
2000-08-01 00:00:00
1998-04-01 00:00:00
type
T01
|1977!
history
history
computer P02 1226 39.95 25667
psychology P01 510
psychology P01 201
biography P01
biography P03 333
children
T02
200 Years of German Humor
14
19.95 9566
T03
Ask Your System Administrator
But I Did It Unconsciously
Exchange of Platitudes
2000-09-01 00:00:00
T04
12.99 13001
1999-05-31 00:00:00
TO5
6.95 201440 2001-01-01 00:00:00
T06
How About Never?
473
19.95 11320
2000-07-31 00:00:00
I Blame My Mother
Just Wait Until After School
TO7
23.95 1500200 1999-10-01 00:00:00
TO8
P01
86
10.00 4095
2001-06-01 00:00:00
T09
Kiss My Boo-Boo
Not Without My Faberge Egg
Perhaps It's a Glandular Problem psychology P05 826 7.99 94123
Spontaneous, Not Annoying
What Are The Civilian Applications? history
children
PO1 22
13.95 5000
2002-05-31 00:00:00
T10
biography PO5
T11
2000-11-30 00:00:00
biography PO5 507 12.99 100001 2000-08-31 00:00:00
1999-05-31 00:00:00
T12
T13
P03 802
29.99 10467
Authors:
au_id au_fnam au_Iname phone
address
city
state zip
10468
Buchman 718-496-7223 75 West 205 St
A01 Sarah
A02 Wendy Heydemark 303-986-7020 2922 Baseline Rd
A03 Hallie
Bronx
NY
Boulder
CÓ
80303
Hull
415-549-4278 3800 Waldo Ave, #14F San Francisco CA
94123
Hull
415-549-4278 3300 Waldo Ave, #14F San Francisco CA
212-771-4680 114 Horatio St
650-836-7128 390 Serra Mall
A04 Klee
94123
A05 Christian Kells
New York
NY
10014
A06 Harvey Kellsey
A07 Paddy
Palo Alto
CA
94305
O'Furniture 941-925-0752 1442 Main St
Sarasota
FL
34236
Transcribed Image Text:title_authors table: Each row in this table represents a relationship between an author and a book that (s)he wrote. Ifa book has 3 authors, there will be 3 rows in this table for that book. This table has a composite primary key (titie_id, au_id). The "royalty_share" field represents the percent of the royalties that this author receives. If there is only one author for a book then this will be 1. If there are two authors for the book, the "royalty_share" could be the same for both or it could be different (eg. 0.6 and 0.4). Data Type Fidld Name Vlite id au_d au_order royaly_shere Oescription Lnique tile identifie Author identifier Author name order on book cover Author fractional royaty share Text Text Number Number royalties table: This table the information about royalties that the publisher will pay to the author(s) of a book. "advance" is the fixed amount that the publisher pays the author when (s)he starts to write the book. "royalty_rate" is a decimal value that contains the percent of each book's sale price that is paid to the author(s). If there is more than one author then the royalties and advaces are split between them Deta Type Text Field Neme le jd advance royaty rate Desription Tle dentiie Upfrork payment to auchor(s) Fraction of revenue pad author(s) Mumber Mumber The data Publishers: pub_id pub_name city New York Core Dump Books San Francisco CA state country PO1 Abatis Publishers NY USA P02 USA РОЗ Schadenfreude Press Hamburg Germany P04 Tenterhooks Press Berkeley Berkeley CA USA POS AAA Publishing CA USA Titles: title_id title_name pub_i pages price sales P01 107 21.99 566 P03 pubdate 2000-08-01 00:00:00 1998-04-01 00:00:00 type T01 |1977! history history computer P02 1226 39.95 25667 psychology P01 510 psychology P01 201 biography P01 biography P03 333 children T02 200 Years of German Humor 14 19.95 9566 T03 Ask Your System Administrator But I Did It Unconsciously Exchange of Platitudes 2000-09-01 00:00:00 T04 12.99 13001 1999-05-31 00:00:00 TO5 6.95 201440 2001-01-01 00:00:00 T06 How About Never? 473 19.95 11320 2000-07-31 00:00:00 I Blame My Mother Just Wait Until After School TO7 23.95 1500200 1999-10-01 00:00:00 TO8 P01 86 10.00 4095 2001-06-01 00:00:00 T09 Kiss My Boo-Boo Not Without My Faberge Egg Perhaps It's a Glandular Problem psychology P05 826 7.99 94123 Spontaneous, Not Annoying What Are The Civilian Applications? history children PO1 22 13.95 5000 2002-05-31 00:00:00 T10 biography PO5 T11 2000-11-30 00:00:00 biography PO5 507 12.99 100001 2000-08-31 00:00:00 1999-05-31 00:00:00 T12 T13 P03 802 29.99 10467 Authors: au_id au_fnam au_Iname phone address city state zip 10468 Buchman 718-496-7223 75 West 205 St A01 Sarah A02 Wendy Heydemark 303-986-7020 2922 Baseline Rd A03 Hallie Bronx NY Boulder CÓ 80303 Hull 415-549-4278 3800 Waldo Ave, #14F San Francisco CA 94123 Hull 415-549-4278 3300 Waldo Ave, #14F San Francisco CA 212-771-4680 114 Horatio St 650-836-7128 390 Serra Mall A04 Klee 94123 A05 Christian Kells New York NY 10014 A06 Harvey Kellsey A07 Paddy Palo Alto CA 94305 O'Furniture 941-925-0752 1442 Main St Sarasota FL 34236
Title_Authors:
title id au_id au_order royalty_share
T01
TO2
A01 1
1.00
A01 1
1.00
T03
A05
1.00
T04
A03
0.60
T04
A04
2
0.40
TO5
A04
1
1.00
T06
A02
1.00
T07
A02
1
0.50
T07
A04 2
0.50
T08
A06
1.00
T09
A06
1.00
T10
A02
1
1.00
T11
A03
2
0.30
T11
A04 3
0.30
T11
A06
0.40
T12
A02
1
1.00
T13
A01
1
1.00
Royalties:
title_id advance royalty_rate
T01
10000.00 0.05
T02
1000.00
0.06
T03
15000.00
0.07
T04
20000.00 0.08
T05
100000.00 0.09
T06
20000.00
0.08
T07
1000000.00 0.11
T08
0.00
0.04
T09
0.00
0.05
T10
T11
100000.00 0.07
T12
50000.00
0.09
T13
20000.00 0.06
Transcribed Image Text:Title_Authors: title id au_id au_order royalty_share T01 TO2 A01 1 1.00 A01 1 1.00 T03 A05 1.00 T04 A03 0.60 T04 A04 2 0.40 TO5 A04 1 1.00 T06 A02 1.00 T07 A02 1 0.50 T07 A04 2 0.50 T08 A06 1.00 T09 A06 1.00 T10 A02 1 1.00 T11 A03 2 0.30 T11 A04 3 0.30 T11 A06 0.40 T12 A02 1 1.00 T13 A01 1 1.00 Royalties: title_id advance royalty_rate T01 10000.00 0.05 T02 1000.00 0.06 T03 15000.00 0.07 T04 20000.00 0.08 T05 100000.00 0.09 T06 20000.00 0.08 T07 1000000.00 0.11 T08 0.00 0.04 T09 0.00 0.05 T10 T11 100000.00 0.07 T12 50000.00 0.09 T13 20000.00 0.06
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 3 steps

Blurred answer
Knowledge Booster
SQL Query
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.
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
Database System Concepts
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)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education