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
Question
Chapter 11, Problem 27P
Program Plan Intro
Factor that are to be considered in writing the conditional expression:
- It is recommended to use simple column or literal as operands in a conditional expression.
- Numeric comparisons are made faster when compared with the characters, date and null characters.
- Equality expression will be processed in a faster rate when compared with inequality expressions.
- Use literals in place of the conditional expressions.
- Specify False condition first when multiple AND condition is used.
- Specify True condition first when multiple OR condition is used.
- Usage of NOT logical operation is avoided.
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
Below is the answer that I have but it is incorrect can anyone help?
select PROJ_NUM, ROUND(SUM(ASSIGN_HOURS),2) as 'TOTAL_HOURS', ROUND(SUM(ASSIGN_CHARGE),2) as 'TOTAL_CHARGE'
from ASSIGNMENT -- from ASSIGNMENT table
group by PROJ_NUM; -- group by to aggregate per PROJ_NUM
Below is the answer I got but it is incorrect
select PROJ_NUM, ROUND(SUM(ASSIGN_HOURS),2) as 'TOTAL_HOURS', ROUND(SUM(ASSIGN_CHARGE),2) as 'TOTAL_CHARGE'
from ASSIGNMENT -- from ASSIGNMENT table
group by PROJ_NUM; -- group by to aggregate per PROJ_NUM
Consider the following database table
named water_schemes:
water- schemes
scheme_no district_name capacity
Ajmer
1
20
1
Bikaner
10
2
Bikaner
10
3
Bikaner
20
1
Churu
10
Churu
20
1
Dungargarh
10
The number of tuples returned by the
following SQL query is
with total(name, capacity) as
select district_name,
sum(capacity)
from water_schemes
group by district_name
with total_avg(capacity) as
select avg(capacity)
from total
select name
from total, total_avg
where total.capacity 2
total_avg.capacity
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
Similar questions
- 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?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_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_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; How would you rewrite the query to ensure that the index you created in Problem 31 is used?arrow_forwardProblems 1721 are based on the following query: SELECT V_CODE, V_NAME, V_CONTACT, V_STATE FROM VENDOR WHERE V_STATE = 'TN' ORDER BY V_NAME; Assume that you have 10,000 different products stored in the PRODUCT table and that you are writing a web-based interface to list all products with a quantity on hand (P_QOH) that is less than or equal to the minimum quantity, P_MIN. What optimizer hint would you use to ensure that your query returns the result set to the web interface in the least time possible? Write the SQL code.arrow_forwardComputer Science At UFL, each class is a specific offering of a course (subject). Each row in the Courses table contains information on a single course or subject taught a UFL and each row in the Classes table contains information on a single offering of a course during a specific term. Investigate the two tables, and then write a query that returns the CourseTitle, ClassID, Term, SectionID and FacultyID for classes taught by FacultyID 5000009. SOLUTION:arrow_forward
- Closely examine the following tables and use it to answer the questions that follow: department course student_type student_category Student registration registration_type Suppose that the following are true: - all id column are primary keys in whichever table they are - the dept_id in course table is foreign key that is referencing the id column in the department table - the foreign keys stud_type_id, stud_cat_id in the student table are referencing the id columns in the student_type and student_category tables respectively. - the coursecode and stud_id columns in the registration table are foreign keys in the course and student tables respectively. 1. Design an Entity Relationship Diagram for the system above. 2. Write query that would return the names and contacts of all Local students. 3. Write a query that would return names, contact and locations of all departments that has a location text with Ave 3 as part of it. [hint: use the LIKE clause with “_” or “%”] 4. What would…arrow_forwardIn the process of query optimization, query cost refers to: A) How long it takes to execute a query. B) How much electricity is used to execute a query. C) How many query hints were used to execute a query. D) All of the above.arrow_forwardWrite sql statements for implementing ALTER,UPDATE and DELETE Write the queries to implement any three types of joinsarrow_forward
- Write an SQL query that uses a multiple-column subquery in a FROM clause. Explain what the query is intended to do. Write an SQL query that is based on multiple tables and uses a subquery with the GROUP BY statement and HAVING clause. Explain what the query is intended to do. Write an SQL query that is based on multiple tables and uses a multiple-row subquery in a WHERE clause. The subquery will include the GROUP BY statement and another multiple-row subquery in a HAVING clause. Explain what the query is intended to do. Write an SQL query that joins three tables and uses any type of a subquery. Explain what the query is intended to do. Write an SQL query that is based on multiple tables and uses the DECODE function. Explain what the query is intended to do.arrow_forwardDescribe a situation that could be solved by using EXPLAIN QUERY PLAN. How would this work? How EXPLAIN QUERY PLAN help to solve the problem?arrow_forwardTake the following extract from SUPPLIES: SUPNR PRODNR PURCHASE_PRICE DELIV_PERIOD 37 0185 32.99 84 0185 33.00 94 0185 32.99 130 1001 10.00 We type the following query: SELECT SUPNR, MIN(DELIV_PERIOD) FROM SUPPLIES WHERE PRODNR = '0185' What is returned? Supnr: 84; Deliv_Period: 5 3 5 2 1 Supnr: 94; Deliv_Period: 2 Supnr: 94; Purchase_Price: $32.99 \n Supnr: 37; Purchase_price: $32.99 Supnr:130, Delv_Period: 1arrow_forward
arrow_back_ios
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
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