Database Systems: Design, Implementation, & Management
11th Edition
ISBN: 9781285196145
Author: Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher: Cengage Learning
expand_more
expand_more
format_list_bulleted
Concept explainers
Videos
Textbook Question
Chapter 11, Problem 26P
Problems 25 and 26 are based on the following query:
| SELECT | P_CODE, P_DESCRIPT, P_QOH, P_PRICE, V_CODE |
| FROM | PRODUCT |
| WHERE | V_CODE = '21344' |
| ORDER BY | P_CODE; |
How should you rewrite the query to ensure that it uses the index you created in your solution to Problem 25?
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solution
Students have asked these similar questions
Task 5:
The Driver Relationship team requested to ensure that there will be no duplicates in the active drivers tables in terms of first name, last name and driving license ID. You need to create a constraint in the ACTIVE_DRIVERS table, called DuplicateCheck, to ensure the first name, last name, and the driving license ID are unique.
ANSWER IN MYSQL PLEASE
Task 2:
The Driver Relationship team wants to ensure that the all driving license IDs in the active drivers table have the length of 7. Alter the ACTIVE_DRIVERS table created in Chapter 8, Activity 1 to check the length of the DRIVER_DRIVING_LICENSE_ID.
Add a CHECK to the ACTIVE_DRIVERS table to enforce a driver license length of 7.
Test Query:
SELECT DISTINCT(constraint_name)
FROM information_schema.check_constraints
WHERE constraint_schema = 'InstantRide'
AND constraint_name = 'ACTIVE_DRIVERS_chk_1'
Expected Results
CONSTRAINT_NAME
ACTIVE_DRIVERS_chk_1
write it by the keyboard
CREATE TABLE sales (
"year SMALLINT NOT NULL,
"quarter' TINYINT NOT NULL,
region VARCHAR (50) NOT NULL,
vehicle VARCHAR (50) NOT NULL,
quantity SMALLINT NULL,
CONSTRAINT pri_sales PRIMARY KEY (year, quarter, region, vehicle));
e) Continuing with the preceding scenario, suggest a new non-clustered index to
stop the query optimizers having to resort to a table scan
Chapter 11 Solutions
Database Systems: Design, Implementation, & Management
Ch. 11 - Prob. 1RQCh. 11 - What index should you create? Write the required...Ch. 11 - What is the focus of most performance-tuning...Ch. 11 - What are database statistics, and why are they...Ch. 11 - How are database statistics obtained?Ch. 11 - What database statistics measurements are typical...Ch. 11 - How is the processing of SQL DDL statements (such...Ch. 11 - In simple terms, the DBMS processes a query in...Ch. 11 - If indexes are so important, why not index every...Ch. 11 - What is the difference between a rule-based...
Ch. 11 - Prob. 11RQCh. 11 - What are some general guidelines for creating and...Ch. 11 - Prob. 13RQCh. 11 - Prob. 14RQCh. 11 - Prob. 15RQCh. 11 - SELECT SELECT EMP_LNAME, EMP_FNAME, EMP_AREACODE,...Ch. 11 - Problem 1 and 2 are based on the following query:...Ch. 11 - Using Table 11.4 as an example, create two...Ch. 11 - Problems 46 are based on the following query:...Ch. 11 - Problems 46 are based on the following query:...Ch. 11 - Prob. 6PCh. 11 - Problems 732 are based on the ER model shown in...Ch. 11 - Problems 732 are based on the ER model shown in...Ch. 11 - Problems 732 are based on the ER model shown in...Ch. 11 - Problems 732 are based on the ER model shown in...Ch. 11 - Problems 1114 are based on the following query:...Ch. 11 - Problems 1114 are based on the following query:...Ch. 11 - Problems 1114 are based on the following query:...Ch. 11 - Problems 1114 are based on the following query:...Ch. 11 - Problems 15 and 16 are based on the following...Ch. 11 - Problems 15 and 16 are based on the following...Ch. 11 - Problems 1721 are based on the following query:...Ch. 11 - Problems 1721 are based on the following query:...Ch. 11 - Prob. 19PCh. 11 - Prob. 20PCh. 11 - Problems 1721 are based on the following query:...Ch. 11 - SELECT SELECT P_CODE, P_DESCRIPT, P_PRICE,...Ch. 11 - Problems 2224 are based on the following query:...Ch. 11 - Problems 2224 are based on the following query:...Ch. 11 - Problems 25 and 26 are based on the following...Ch. 11 - Problems 25 and 26 are based on the following...Ch. 11 - Prob. 27PCh. 11 - Problems 27 and 28 are based on the following...Ch. 11 - Problems 2932 are based on the following query:...Ch. 11 - Problems 2932 are based on the following query:...Ch. 11 - Problems 2932 are based on the following query:...Ch. 11 - Problems 2932 are based on the following query:...
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.Similar questions
- Problems 2932 are based on the following query: SELECT CUS_CODE, MAX(LINE_UNITSLINE_PRICE) FROM CUSTOMER NATURAL JOIN INVOICE NATURAL JOIN LINE WHERE CUS_AREACODE = '615' GROUP BY CUS_CODE; How would you rewrite the query to ensure that the index you created in Problem 31 is used?arrow_forwardProblems 27 and 28 are based on the following query: SELECT P_CODE, P_DESCRIPT, P_QOH, P_PRICE, V_CODE FROM PRODUCT WHERE P_QOH P_MIN AND P_MIN = P_REORDER AND P_REORDER = 50 ORDER BY P_QOH; What indexes would you recommend? Write the commands to create those indexes.arrow_forwardDelete the OWNER_INDEX 3 index from the OWNER table.arrow_forward
- Problems 2224 are based on the following query: SELECT P_CODE, P_DESCRIPT, P_PRICE, P.V_CODE, V_STATE FROM PRODUCT P, VENDOR V WHERE P.V_CODE = V.V_CODE AND V_STATE = 'NY' AND V_AREACODE = '212' ORDER BY P_PRICE; Write the commands required to create the indexes you recommended in Problem 22.arrow_forwardProblems 2224 are based on the following query: SELECT P_CODE, P_DESCRIPT, P_PRICE, P.V_CODE, V_STATE FROM PRODUCT P, VENDOR V WHERE P.V_CODE = V.V_CODE AND V_STATE = 'NY' AND V_AREACODE = '212' ORDER BY P_PRICE; Write the command(s) used to generate the statistics for the PRODUCT and VENDOR tables.arrow_forwardEnsure that the only legal values for the BDRMS column in the CONDO_UNIT table are 1, 2, or 3.arrow_forward
- How is adding a NOT NULL constraint to an existing table different from adding other types of constraints?arrow_forwardChange the constraint created in Assignment #6 so that associated rows of the BOOK_STORES table are deleted automatically if a row in the STORE_REPS table is deleted.arrow_forwardProblems 1114 are based on the following query: SELECT P_CODE, SUM(LINE_UNITS) FROM LINE GROUP BY P_CODE HAVING SUM(LINE_UNITS) (SELECT MAX(LINE_UNITS) FROM LINE); Write the command to create statistics for this table.arrow_forward
- Problems 2932 are based on the following query: SELECT CUS_CODE, MAX(LINE_UNITSLINE_PRICE) FROM CUSTOMER NATURAL JOIN INVOICE NATURAL JOIN LINE WHERE CUS_AREACODE = '615' GROUP BY CUS_CODE; Assuming that you follow the recommendations you gave in Problem 29, how would you rewrite the query?arrow_forwardIn the initial creation of a table, if a UNIQUE constraint is included for a composite column that requires the combination of entries in the specified columns to be unique, which of the following statements is correct? a. The constraint can be created only with the ALTER TABLE command. b. The constraint can be created only with the table-level approach. c. The constraint can be created only with the column-level approach. d. The constraint can be created only with the ALTER TABLE MODIFY command.arrow_forwardselect * from cast where cast_id=162652763; Use explain to display the query plan without creating any index. Now create an index to speed up the query. Use explain and timer to show the improvement.arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781285196145Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos CoronelPublisher:Cengage Learning
Programming with Microsoft Visual Basic 2017Computer ScienceISBN:9781337102124Author:Diane ZakPublisher:Cengage Learning
A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Programming with Microsoft Visual Basic 2017
Computer Science
ISBN:9781337102124
Author:Diane Zak
Publisher:Cengage Learning
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
SQL Basics for Beginners | Learn SQL | SQL Tutorial for Beginners | Edureka; Author: edureka;https://www.youtube.com/watch?v=zbMHLJ0dY4w;License: Standard YouTube License, CC-BY