I need help writing a SQL query for the following: List the names of all books which have fewer pages than the average number of pages in all books published by Lions Publishing.   List the names of all books which have fewer pages than the number of pages in every book published by Lions Publishing.   List the names of all books which have fewer pages than the number of pages in at least one book published by Lions Publishing.   List all customer information for the customer or customers who paid the least for any book in the store.

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

I need help writing a SQL query for the following:

  • List the names of all books which have fewer pages than the average number of pages in all books published by Lions Publishing.

 

  • List the names of all books which have fewer pages than the number of pages in every book published by Lions Publishing.

 

  • List the names of all books which have fewer pages than the number of pages in at least one book published by Lions Publishing.

 

  • List all customer information for the customer or customers who paid the least for any book in the store.
### Entity-Relationship Diagram (ERD) for a Bookstore Database

#### Book Table
- **BOOKNUM**: Unique identifier for each book.
- **BOOKNAME**: Name of the book.
- **PUBYEAR**: Year the book was published.
- **PAGES**: Number of pages in the book.
- **PUBNAME**: Name of the publisher.

#### Customer Table
- **CUSTNUM**: Unique identifier for each customer.
- **CUSTNAME**: Name of the customer.
- **ADDRESS**: Customer's address.
- **PHONE**: Customer's phone number.

#### Sales Table
- **BOOKNUM**: Links to a unique book.
- **CUSTNUM**: Links to a unique customer.
- **DATE**: Date the sale was made.
- **PRICE**: Price at which the book was sold.

#### Publisher Table
- **PUBNAME**: Name of the publisher.
- **ADDRESS**: Publisher's address.
- **PHONE**: Publisher's phone number.

### Description
The diagram illustrates the relationships between books, customers, sales, and publishers. Lines between the tables indicate how data is related across different entities:
- A **Book** is linked to a **Publisher** through the **PUBNAME** field.
- A **Sales** record connects a **Book** to a **Customer** using the **BOOKNUM** and **CUSTNUM** fields.

### SQL Queries
To retrieve information:
1. Identify relationships between the tables using the foreign keys.
2. Use JOIN operations in SQL to fetch related data across multiple tables.

These queries can be documented in Word or another preferred format for reference.
Transcribed Image Text:### Entity-Relationship Diagram (ERD) for a Bookstore Database #### Book Table - **BOOKNUM**: Unique identifier for each book. - **BOOKNAME**: Name of the book. - **PUBYEAR**: Year the book was published. - **PAGES**: Number of pages in the book. - **PUBNAME**: Name of the publisher. #### Customer Table - **CUSTNUM**: Unique identifier for each customer. - **CUSTNAME**: Name of the customer. - **ADDRESS**: Customer's address. - **PHONE**: Customer's phone number. #### Sales Table - **BOOKNUM**: Links to a unique book. - **CUSTNUM**: Links to a unique customer. - **DATE**: Date the sale was made. - **PRICE**: Price at which the book was sold. #### Publisher Table - **PUBNAME**: Name of the publisher. - **ADDRESS**: Publisher's address. - **PHONE**: Publisher's phone number. ### Description The diagram illustrates the relationships between books, customers, sales, and publishers. Lines between the tables indicate how data is related across different entities: - A **Book** is linked to a **Publisher** through the **PUBNAME** field. - A **Sales** record connects a **Book** to a **Customer** using the **BOOKNUM** and **CUSTNUM** fields. ### SQL Queries To retrieve information: 1. Identify relationships between the tables using the foreign keys. 2. Use JOIN operations in SQL to fetch related data across multiple tables. These queries can be documented in Word or another preferred format for reference.
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 5 steps with 5 images

Blurred answer
Similar 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