Database Systems: Design, Implementation, & Management
12th Edition
ISBN: 9781305627482
Author: Carlos Coronel, Steven Morris
Publisher: Cengage Learning
expand_more
expand_more
format_list_bulleted
Concept explainers
Videos
Textbook Question
Chapter 11, Problem 14P
Problems 11–14 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.
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solution
Students have asked these similar questions
WEEK SQL ASSIGNMENT
Complete the following exercise:
Alter your student table adding a new column called enroll_date using the datetime data type.
Populate the new column (enroll_date) with the current date and time.
Using your student table return the student first and last names concatenated with a space and then the enroll_date minus one month. Here is an example of the output:
Name
Date
Jane Smith
2019-05-03 13:14:12
Tom Jones
2020-08-07 05:23:56
Using your student table return the month name for the enroll_date column. For example, if the enroll_date was 11-6-2019 then the month name would be June.
select * 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.
(A) Write a query which shows the employee IDs that are unique to the employee table. Order the employee IDs in descending order. An employee ID Is the same in both tables if the integer value of the ID matches.
(B) We want to add a new column to the employee table. We want to provide a new column with a more complete phone number. Right now the PHONENO column only shows the last 4 digits. We want a new column which is called PHONE and consists of ###-###-####. The last 4-digits are already in the PHONENO column. The first three digits should be 416 and the next three should be 123. To improve clarity in the table, we also want to rename the PHONENO column to PHONEEXT. Show all the commands used to accomplish this, then, select all data for employees who have the last name of 'smith' (case insensitive).
(C) Show a list of employee id, names, department, years and job of any employee in the staff table who makes a total amount more than their manager or has more years of service than…
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 EMP_LNAME, EMP_FNAME, EMP_AREACODE, EMP_SEX...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 P_CODE, P_DESCRIPT, P_PRICE, P.V_CODE,...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
- How do you add data from an existing table to another table?arrow_forwardWhich of the following characters can be used in a table name? a. b. ( c. % d. !arrow_forwardThe OR_CASE table has one bro per surgical case. The OR_LOG table has one row per surgical log. It's LOG_ID column identifies each log. If a case has an associated log, OR_CASE.LOG_ID will store the logs ID. Otherwise, it will be null. A query uses the following join: FROM OR_CASE INNER JOIN OR_LOG ON OR_CASE.LOG_ID=OR_LOG.LOG_ID which of the following would be the results? A. A log with no associated case.B. A log with an associated case. C. A case with no associated log.D. Rows with no case or log.arrow_forward
- Refer to the film and inventory tables of the Sakila database. The tables in this lab have the same columns and data types but fewer rows. Write a query that lists the titles of films with the fewest rows in the inventory table. This query requires a subquery that computes the minimum of counts by film_id: SELECT MIN(count_film_id) FROM ( SELECT COUNT(film_id) AS count_film_id FROM inventory GROUP BY film_id ) AS temp_table; This subquery is provided in the template.arrow_forwardWEEK 3 ASSIGNMENT Complete the following exercise: Using the student table you created in week two insert four rows into your table. Update the first name to “Luke” and last name to “Plew” for one of the rows you inserted above. Delete the row you updated above. Explain how you insert data in one table from another table. Your answer should be a minimum of three sentences in length.arrow_forwardTrue or False? An action query is accomplished by putting AS and an alternative name after the table name in the FROM clause.arrow_forward
- Question 15 Suppose that you have the following table created as: CREATE TABLE TAB1 (NAME VARCHAR2 (100)); INSERT INTO TAB1 VALUES ('Ahmed Raoof Hasan'); Use one SELECT statement to decompose the NAME into first, second, and last names. e.g. Name: Ahmed Raoof Hasan 1. first: Ahmed 2. second: Raoof 3. last: Hasan Hint: use the following syntax if needed: INSTR (string, substring, start_position, occurrences) SUBSTR (string, start_pos, length) Use the editor to format your answerarrow_forwardModify the Job_class column of the EMPLOYEES table so that it allows storing a maximum width of two characters.arrow_forwardYou have been told that you need to store the zip code for employees. Add a column to the EMPLOYEE table which will be used to store each employee’s zip code Update two rows in the EMPLOYEE table and assign a zip code (must have a WHERE clause). (you MAY use 1 or 2 statements) Write a query that includes each employee's last name, department name (not ID), and salary for each employee whose salary is less than the average for their departmentarrow_forward
- What is the result of the query below: SELECT V_CODE, COUNT (P_CODE) FROM PRODUCT HAVING V_CODE != 21344; A) A list of V_CODE, and the number of products for each V_CODE that is equal to 21344. B) An error message. C) A list of V_CODE, and the number of products for each V_CODE that is not equal to 21344. D) The number of counts for each P_CODE that has a V_CODE that is not equal to 21344.arrow_forwardA Write a query to create a report that displays the count of the number of tables in the SQ library. Use DICTIONARY.tables as input.arrow_forward. Rename the EMPLOYEES table as JL_EMPSarrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
Programming with Microsoft Visual Basic 2017Computer ScienceISBN:9781337102124Author:Diane ZakPublisher:Cengage Learning
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
Np Ms Office 365/Excel 2016 I NtermedComputer ScienceISBN:9781337508841Author:CareyPublisher:Cengage
A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr
Programming with Microsoft Visual Basic 2017
Computer Science
ISBN:9781337102124
Author:Diane Zak
Publisher:Cengage Learning
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
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
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