EBK DATABASE SYSTEMS: DESIGN, IMPLEMENT
12th Edition
ISBN: 8220101450739
Author: Morris
Publisher: YUZU
expand_more
expand_more
format_list_bulleted
Concept explainers
Videos
Textbook Question
Chapter 8, Problem 3RQ
Using tables named T1 and T2, write a query example for each of the three join types you described in Question 2. Assume that T1 and T2 share a common column named Cl.
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
The 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.
Consider the Table: Employee
Write a Query First to Alter Table to add a new Column managername to the employee table
Write an Update query using Self-Join and Case When/Then to update the managername for the respective employees in the managername column. Where there is no manager present, then the CASE When/Then should update the text ‘No Manager’ in the managername column. [Hint: Jennifer has no Manager, everyone else has a manager]
JOIN TABLES
One row in the PAT_ENC table represents one patient encounter. One row in the ORDER MED table
represents one medication order.
One patient encounter can have many medication orders but one medication order can only have one
patient encounter. In other words, the cardinality of this PAT_ENC to ORDER_MED relationship is one-to-
many.
You start a query with PAT_ENC. You then add ORDER_MED using an inner join. What is true about the
granularity of the result set before and after adding the ORDER_MED table?
SELECT ONE OF THE FOLLOWING
A. The granularity stays at one row per patient encounter.
B. The granularity stays at one row per medication order.
C. The granularity changes from one row per patient encounter to one row per medication order on an
encounter.
D. The granularity changes from one row per medication order to one row per patient encounter.
Chapter 8 Solutions
EBK DATABASE SYSTEMS: DESIGN, IMPLEMENT
Ch. 8 - What is a cross join? Give an example of its...Ch. 8 - What three join types are included in the outer...Ch. 8 - Using tables named T1 and T2, write a query...Ch. 8 - Prob. 4RQCh. 8 - Prob. 5RQCh. 8 - Prob. 6RQCh. 8 - Prob. 7RQCh. 8 - What does it mean to say that SQL operators are...Ch. 8 - Prob. 9RQCh. 8 - Prob. 10RQ
Ch. 8 - Prob. 11RQCh. 8 - Prob. 12RQCh. 8 - Given the employee information in Question 11,...Ch. 8 - Prob. 14RQCh. 8 - Prob. 15RQCh. 8 - Prob. 16RQCh. 8 - What Oracle function should you use to calculate...Ch. 8 - Prob. 18RQCh. 8 - What string function should you use to list the...Ch. 8 - Prob. 20RQCh. 8 - Prob. 21RQCh. 8 - Prob. 22RQCh. 8 - Prob. 23RQCh. 8 - Prob. 24RQCh. 8 - Prob. 1PCh. 8 - Insert the data into the tables you created in...Ch. 8 - Prob. 3PCh. 8 - Prob. 4PCh. 8 - Prob. 5PCh. 8 - Prob. 6PCh. 8 - Prob. 7PCh. 8 - Prob. 8PCh. 8 - Prob. 9PCh. 8 - Modify the CUSTOMER table to include two new...Ch. 8 - Assuming that you completed Problem 10, write the...Ch. 8 - Assuming that the CUSTOMER table contains a...Ch. 8 - Write the query that lists the average age of your...Ch. 8 - Write the trigger to update the CUST_BALANCE in...Ch. 8 - Prob. 15PCh. 8 - Prob. 16PCh. 8 - Write a trigger to update the customer balance...Ch. 8 - Write a procedure to delete an invoice, giving the...Ch. 8 - Prob. 19PCh. 8 - Prob. 20PCh. 8 - Prob. 21PCh. 8 - Prob. 22PCh. 8 - Write a single SQL command to increase all price...Ch. 8 - Prob. 24PCh. 8 - Prob. 25PCh. 8 - Prob. 26PCh. 8 - Prob. 27PCh. 8 - Create a trigger named trg_line_total to write the...Ch. 8 - Create a trigger named trg_line_prod that...Ch. 8 - Create a stored procedure named prc_inv_amounts to...Ch. 8 - Create a procedure named prc_cus_balance_update...Ch. 8 - Modify the MODEL table to add the attribute and...Ch. 8 - Prob. 33PCh. 8 - Modify the CHARTER table to add the attributes...Ch. 8 - Write the sequence of commands required to update...Ch. 8 - Write the sequence of commands required to update...Ch. 8 - Write the command required to update the...Ch. 8 - Write the command required to update the...Ch. 8 - Write the command required to update the...Ch. 8 - Prob. 40PCh. 8 - Create a trigger named trg_char_hours that...Ch. 8 - Create a trigger named trg_pic_hours that...Ch. 8 - Create a trigger named trg_cust_balance that...Ch. 8 - Prob. 44PCh. 8 - Prob. 45PCh. 8 - Prob. 46PCh. 8 - Prob. 47PCh. 8 - Prob. 48PCh. 8 - Prob. 49PCh. 8 - Prob. 50PCh. 8 - Prob. 51PCh. 8 - Prob. 52PCh. 8 - Prob. 53PCh. 8 - Prob. 54PCh. 8 - Prob. 55PCh. 8 - Prob. 56PCh. 8 - Prob. 57PCh. 8 - Prob. 58PCh. 8 - Prob. 59PCh. 8 - Prob. 61PCh. 8 - Prob. 62PCh. 8 - Prob. 63PCh. 8 - Prob. 64PCh. 8 - Prob. 65PCh. 8 - Prob. 66PCh. 8 - Prob. 67PCh. 8 - Alter the DETAILRENTAL table to include a derived...Ch. 8 - Update the DETAILRENTAL table to set the values in...Ch. 8 - Alter the VIDEO table to include an attribute...Ch. 8 - Update the VID_STATUS attribute of the VIDEO table...Ch. 8 - Alter the PRICE table to include an attribute...Ch. 8 - Prob. 73CCh. 8 - Prob. 76C
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
- Delete the OWNER_INDEX 3 index from the OWNER table.arrow_forwardAssuming that the CUSTOMER table contains a CUST_AGE attribute, write the query to update the values in that attribute. (Hint: Use the results of the previous query.)arrow_forwardThe OR_LOG table has one row per surgical log. The OR_CASE table has one row per surgical case. Its CASE_ID column idenifies each case.If a log has an associated case, OR_LOG.CASE_ID will store the case's ID. Otherwise, it will be NULL. A query uses the following join: FROM OR_LOG INNER JOIN OR_CASEON OR_LOG.CASE = OR_CASE.CASE_ID Which of the following would be in the results? A. A log with no associated caseB. A log associated with a caseC. A case with no associated logD. Rows with no case or logarrow_forward
- Database based questionarrow_forwardSQL SUM Problem -SELECT * FROM [Sale Order] INNER JOIN Product ON [Sale Order] productID = Product.productID here i've joined two tables , now what i need to do is sum the productPrice of the rows with the same customerID .. order id and product id are the primary keyarrow_forwarda ) Write a query that uses NATURAL JOIN TO connect the EMPLOYEE and EMPPROJACT table. Include the Employee number , First and Last name, Salary, Salary increased by 3% and Project number b) Use INNER JOIN OR JOIN with the same query with USING statement c) Use INNER JOIN OR JOIN with the same query with joined columns (hint a = a )arrow_forward
- Hello, can you please assit me with this. I am struggling to get the code to run correctly without errors: 1. Write a query using a join that displays Department names (not Department_IDs) Employees’ IDs, last & first names Sort your results by department name and employee last and first name. NOTE: INNER JOIN is OK here. NOTE: I suggest you use table aliases.*/ /*2. Write a query using a join that displays Department names (no DEPARTMENT_IDs). Job titles (not Job_IDs) Employees’ IDs, last & first names, Sort your results by department name, job title, and employee last and first name. NOTE: INNER JOIN is OK here. NOTE: I suggest you use table aliases.*/ /*3. Write a query using a self-join to display Employees’ IDs and names along with Their manager’s ID and name. Use column aliases so that it is clear which columns are for the Manager and which columns are for the Employee.…arrow_forwardWrite a query which set the following constraints in Customers table (in MySQL). First Name and city must be entered. Last name must be different of each customer.arrow_forwardMy SQL WORKBENCH **Please note that you may actually use as many tables as you need. You are not limited to use only twotables.Example:SELECT *FROM DEPARTMENTS;SELECT *FROM EMPLOYEES;Now join departments and employees tables.SELECT FIRST_NAME, DEPARTMENT_NAMEFROM EMPLOYEES, DEPARTMENTSWHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID;NOTE: If you omit the join condition, the result will be the Cartesian Product of the two tables. A CartesianProduct joins all rows of the first table with all rows of the second table.That is if the first table has n rows and the second table has m rows, the output will have n*m rows.The following query will produce a Cartesian Product:SELECT FIRST_NAME, DEPARTMENT_NAMEFROM EMPLOYEES, DEPARTMENTS;**When table names are long, qualifying column names might be very time consuming. Usually peopleprefer to use table aliases for this purpose.Example: List the name of all employees who work for 'Purchasing' departmentSELECT E.FIRST_NAME "EMPLOYEE…arrow_forward
- My SQL WORKBENCH **Please note that you may actually use as many tables as you need. You are not limited to use only twotables.Example:SELECT *FROM DEPARTMENTS;SELECT *FROM EMPLOYEES;Now join departments and employees tables.SELECT FIRST_NAME, DEPARTMENT_NAMEFROM EMPLOYEES, DEPARTMENTSWHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID;NOTE: If you omit the join condition, the result will be the Cartesian Product of the two tables. A CartesianProduct joins all rows of the first table with all rows of the second table.That is if the first table has n rows and the second table has m rows, the output will have n*m rows.The following query will produce a Cartesian Product:SELECT FIRST_NAME, DEPARTMENT_NAMEFROM EMPLOYEES, DEPARTMENTS;**When table names are long, qualifying column names might be very time consuming. Usually peopleprefer to use table aliases for this purpose.Example: List the name of all employees who work for 'Purchasing' departmentSELECT E.FIRST_NAME "EMPLOYEE…arrow_forwardYou have the following tables: APARTMENTS (ADDRESS, CITY, STATE, RENTER_ID, RENTER_LAST_NAME) RENTERS (RENTER_ID, FIRST_NAME, LAST_NAME) What is the primary key for the APARTMENTS table? (It may be a composite key involving 2 or more fields) What are the foreign keys, if any? What is the primary key for the RENTERS table? What are the foreign keys, if any? What field in RENTERS can be eliminated so that the RENTERS table is normalized?arrow_forwardCreate a query for displaying e1.first_name with e1.last_name of employee under column alias “Employee Full Name” by using concatenation from employees table with a table alias e1 and e2.first_name with e2.last_name of employee under column alias “Manager Full Name” self joining with employees table with a table alias e2 on the basis of equality of manager_id from e1 with employee_id from e2. Use order by clause to display list according to first name of manager e2.first_name in ascending order.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