# Problems 29–32 are based on the following query: SELECT CUS_CODE, MAX(LINE_UNITS*LINE_PRICE) FROM CUSTOMER NATURAL JOIN INVOICE NATURAL JOIN LINE WHERE CUS_AREACODE = '615' GROUP BY CUS_CODE; What indexes would you recommend for the query you wrote in Problem 30, and what SQL commands would you use?

### Database Systems: Design, Implemen...

13th Edition
Carlos Coronel + 1 other
Publisher: Cengage Learning
ISBN: 9781337627900

### Database Systems: Design, Implemen...

13th Edition
Carlos Coronel + 1 other
Publisher: Cengage Learning
ISBN: 9781337627900

#### Solutions

Chapter
Section
Chapter 11, Problem 31P
Textbook Problem

## Problems 29–32 are based on the following query: SELECT CUS_CODE, MAX(LINE_UNITS*LINE_PRICE) FROM CUSTOMER NATURAL JOIN INVOICE NATURAL JOIN LINE WHERE CUS_AREACODE = '615' GROUP BY CUS_CODE; What indexes would you recommend for the query you wrote in Problem 30, and what SQL commands would you use?

Expert Solution
Program Plan Intro

Indexes:

• Indexing is a technique that is used in SQL for optimizing the performance.
• Indexes are used when a small set of rows needs to be selected from the table that is large in size.
• The selection of rows is made by specifying conditions.

Rules for using indexes:

The below are the rules how the indexes can be used

• When an indexed column appears within itself in the search criteria of “WHERE” or “HAVING” clause.
• When an indexed column appears within itself in “GROUP BY” or “ORDER BY” clause.
• When the indexed column is applied with functions MAX and MIN.
• When the indexed column’s data sparsity is high.

Syntax for creating INDEX:

The below mentioned is the syntax for creating the index:

CREATE [UNIQUE]INDEX indexname ON tablename(column1 [, column2])

### Explanation of Solution

The query of problem 29:

SELECT CUS_CODE, MAX(LINE_TOTAL)

FROM CUSTOMER NATURAL JOIN INVOICE NATURAL JOIN LINE

WHERE CUS_AREACODE = ‘615’

GROUP BY CUS_CODE;

Index that is recommended for the query:

• Creating an index on “CUS_AREACODE” and an index on “CUS_CODE” the query will be greatly benefited.
• Considering the column “CUS_CODE”, which is foreign key on invoice hence it seems to be an index that already exists...

### Want to see the full answer?

Check out a sample textbook solution.See solution

### Want to see this answer and more?

Bartleby provides explanations to thousands of textbook problems written by our experts, many with advanced degrees!

See solution

Find more solutions based on key concepts
Show solutions
What is connectivity? (Use a Crows Foot ERD to illustrate connectivity.)

Database Systems: Design, Implementation, & Management

Direct hardening can be performed on steel containing at least________ carbon.

Precision Machining Technology (MindTap Course List)

What do we mean by a physical law and what are they based on?

Engineering Fundamentals: An Introduction to Engineering (MindTap Course List)

The magnitude of the moment of the force P about the axis CD is 50lbin. Find the magnitude of P.

International Edition---engineering Mechanics: Statics, 4th Edition

If your motherboard supports ECC DDR3 memory, can you substitute non-ECC DDR3 memory?

A+ Guide to Hardware (Standalone Book) (MindTap Course List)

What is a cybersquatter? (64) What is the goal of the Anticybersquatting Consumer Protection Act (ACPA)? (64)

Enhanced Discovering Computers 2017 (Shelly Cashman Series) (MindTap Course List)