Problems are based on the ER model shown in Figure P11.7 and on the query shown after the figure. SELECT P_CODE, P_PRICE FROM PRODUCT WHERE P_PRICE >= (SELECT AVG(P_PRICE) FROM PRODUCT); What type of database I/O operations will likely be used by the query? (See Table 11.3.)

Problems are based on the ER model shown in Figure P11.7 and on the query shown after the figure. SELECT P_CODE, P_PRICE FROM PRODUCT WHERE P_PRICE >= (SELECT AVG(P_PRICE) FROM PRODUCT); What type of database I/O operations will likely be used by the query? (See Table 11.3.)

Database Systems: Design, Implementation, & Management

12th Edition

ISBN:9781305627482

Author:Carlos Coronel, Steven Morris

Publisher:Carlos Coronel, Steven Morris

Chapter11: Database Performance Tuning And Query Optimization

Section: Chapter Questions

Problem 19P

See similar textbooks

Similar questions

Suppose you are a manufacturer of product ABC, which is composed of parts A, B, and C When a new product is created, the involved parts in table PART must be reduced. As such, when product ABC is created, PROD_QTY is increased by one, and the quantity of parts A, B, and C in table PART (i.e. PART_QTY) is reduced by one respectively. The database content is shown in the following tables. How many database requests can you identify when a product ABC is created? List all.
Subject - Fundamentals to Database Question 2 Based on the Table 5 and Table 6, write the SQL statement for the following cases in (5a), (5b), (5c), (5d) and (5e). Table 5: Employee Employee_Id First_name Last_name Salary Joining_Date Department 1 John Abraham 1000000 01-Jan-13 12.00.00AM Banking 2 Micheal Clarke 800000 01-Jan-13 12.00.00AM Insurance 3 Roy Thomas 700000 01-Feb-13 12.00.00AM Banking 4 Tom Jose 600000 01-Feb-13 12.00.00AM Insurance 5 Jerry Pinto 650000 01-Feb-13 12.00.00AM Insurance 6 Philip Mahtew 750000 01-Jan-13 12.00.00AM Services 7 TestName1 123 650000 01-Jan-13 12.00.00AM Services 8 TestName2 Lname% 600000 01-Feb-13 12.00.00AM Insurance Table 6: Incetives Employee_ref_id Incentive_date Incentive_amount 1 01-Feb-13 5000 3 01-Feb-13 3000 4 01-Feb-13 4000 5 01-Jan-13 4500 6 01-Jan-13 3500 (a) Write the SQL command that remove the employees from the employee table WHERE the joining date is before 1-Jan-13. (b) Write the SQL…
Subject - Fundamentals to Database Question 5 Based on the Table 5 and Table 6, write the SQL statement for the following cases in (5a), (5b), (5c), (5d) and (5e). Table 5: Employee Employee_Id First_name Last_name Salary Joining_Date Department 1 John Abraham 1000000 01-Jan-13 12.00.00AM Banking 2 Micheal Clarke 800000 01-Jan-13 12.00.00AM Insurance 3 Roy Thomas 700000 01-Feb-13 12.00.00AM Banking 4 Tom Jose 600000 01-Feb-13 12.00.00AM Insurance 5 Jerry Pinto 650000 01-Feb-13 12.00.00AM Insurance 6 Philip Mahtew 750000 01-Jan-13 12.00.00AM Services 7 TestName1 123 650000 01-Jan-13 12.00.00AM Services 8 TestName2 Lname% 600000 01-Feb-13 12.00.00AM Insurance Table 6: Incetives Employee_ref_id Incentive_date Incentive_amount 1 01-Feb-13 5000 3 01-Feb-13 3000 4 01-Feb-13 4000 5 01-Jan-13 4500 6 01-Jan-13 3500 (d) Write the SQL code to get employee details from employee table whose first name ends with ‘n’ and name contain 4 letters. (e) Write…
QUESTION 14 Consider the database schema below for cities in countries. A country have many cities, and the same city name can be found in different countries. Country (ID: integer, Name: String (unique), SN: serialNumber (int)(unique)) City (serialNo: integer (unique, not null), CountryID: Integer, CityName: String, Population: integer) The same country (ID , name) can be added twice in Country relation with different SN? No, violates entity Integrity constraint No, violates referential integrity constraint. No, violates key constraint. Yes. No, violates domain constraints.
Consider the relational database below, where the primary keys are underlined.employee (person-name, street, city)works (person-name, company-name, salary)company (company-name, city)manages (person-name, manager-name)Give a query for each of the following:a Find all employees who live in the same city as the company for which they work islocated.b Find all employees who live in the same city and on the same street as theirmanagers.c Find all employees in the database who do not work for the First Bank Corporation. d Find all employees who earn more than the average salary of all employees of theircompany.e Find the company that has the most employees.f Find the company that has the smallest payroll.g Modify the database so that Jones now lives in Newtown.h Give all employees of the First Bank Corporation a 10 percent raise.i Give all managers in database a 10 percent raise.j Give managers in the database a 10 percent raise, unless the salary would be greaterthan $100,000. In such…
QUESTION 5 Consider the database schema below: Company (ID: integer, Name: String (unique), Address) Department (No: integer (not null), CompanyID: Integer, DepartmentName: String) Is it valid for Department to have unknown No? No, violates key constraint. No, violates referential integrity constraint. Yes. No, violates entity Integrity constraint No, violates domain constraints.
QUESTION 7 Consider the database schema below: Company (ID: integer, Name: String (unique), Address) Department (No: integer, CompanyID: Integer, DepartmentName: String) Is it valid to have the same Company name to be repeated with two different IDs in Company relation? Yes. No, violates key constraint. No, violates entity Integrity constraint No, violates referential integrity constraint. No, violates domain constraints.
Specify the following queries on the COMPANY relational database schema shown in Figure 3.5, using the relational operators discussed in this chapter. Also show the result of each query as it would apply to the database state of Figure 3.6. - List the managers who are controlling more than 2 projects. - List the names of employees all of whose dependents were born before 1990. - Retrieve the average salary of employees who work totally no less than 30 hours on projects.
Consider the following database design: Also consider the virtual table ASSIGNMENTS, which doesn't meet the third normal form but is created by: CREATE VIEW ASSIGNMENT AS SELECT ASSIGN_NUM, ASSIGN_DATE, PROJ_NUM, EMPLOYEE.EMP_NUM, JOB.JOB_CODE AS ASSIGN_JOB, JOB_CHG_HOUR AS ASSIGN_CHG_HR, ASSIGN_HOURS, JOB_CHG_HOUR *ASSIGN_HOURS AS ASSIGN_CHARGE FROM ASSIGN, EMPLOYEE, JOB WHERE ASSIGN.EMP_NUM = EMPLOYEE.EMP_NUM AND EMPLOYEE.JOB_CODE = JOB.JOB_CODE; In SQL design the following queries for information search: Create and run a query to list employees who were hired before the year 1995. Create and run a query to list job code, job description, and employee last name, first name sorted by job description and employee last name. Create and run a query to list the name of employees who were assigned to the Rolling Tide project (which is one of the entries in the column PROJ_NAME). Create and run a query to list the project name, the number of employees worked for the project, and the…
QUESTION 17 Consider the database schema below for cities in countries. A country have many cities, and the same city name can be found in different countries. Country (ID: integer, Name: String (unique)) City (serialNo: integer (unique, not null), CountryID: Integer, CityName: String, Population: integer) The same country name can be repeated with two different IDs in Country relation? No, violates key constraint. No, violates entity Integrity constraint No, violates domain constraints. No, violates referential integrity constraint. Yes.
Consider the following relations: Students(snum: string, sname:string, major:string, level:string, age:int); Enrolled(snum: string, cname: string) And Consider the following query SELCT s.sname, s.age FROM Student s, Enrolled e WHERE s.snum = e.snum and s.level = 'JR' and e.cname = 'Database Management'; Given the following info: T(student) = 10000 records t(Enrolled) = 20000 record D(Student) = 300 pages B(enrolled) = 200 pages V(Student, level) = 5 // (FR, SP, SR, GRAD) V(Enrolled, cname) = 500 //there are 500 different courses) a. please draw a query plan for the above query: join two tables first, then apply selection finaally apply projection. b. Please modify the query plan in (a) to apply push selection rule.
Consider the transaction database in the table below: TID items_bought 1 {a, b, c, d} 2 {b, c, e, f} 3 {a, d, e, f} 4 {a, e, f} 5 {b, d, f} Represent the database in vertical format.

SEE MORE QUESTIONS

Recommended textbooks for you

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

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

SEE MORE TEXTBOOKS