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 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
Database Systems: Design, Implementation, & Management
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_forwardPlease writee the SQL queriees: Wrrite a query to display all departments no, which have more-than 2 instructors without using Join or Sub Queries. Wrrite a query to display instructors who have taught course which course id starts except "CS-4". Wrrite a query using Joins to display 2nd lowest salary of instructor using Join. Wrrite a query using Joins to display first name, age and department name of youngest instructor's from each department. Using Joinarrow_forward
- Question 6 List classroom ID, building name, class ID and class description. Use a LEFT JOIN to list all the classrooms in the BSU_Classrooms table and show matching Class ID and class description from the BSU_Class table. Sort results in descending order by classroom ID. Paste the SQL code here: Paste the resulting table here: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_forwardWhich of the following tables is followed by the outer join operator (+) in an OUTER JOIN query?arrow_forward
- Database based questionarrow_forwardStudent: studentNO, firstName, lastName, studentProgram Professor: professorID, professorProgram, professorName Student_professor: studentNO, professorID, mentor Complete the following requirements: Using the UNION operator, do a full outer join to display the student last name and student program as well as the professor name and professor program. No JOIN operator is to be used in this requirement. Create a query/subquery to display student first name, last name and program. The student program must be same as that in the professor table and the professor program name starts with a ‘C’. This query will use a subquery and not a join. Create a query to display the professor ID, professor name and mentor column including records where the professor ID is not on the student_professor table. Note, this will be a left join. Make sure to eliminate duplicates from the output. Display the average age of students in each student program. Display the number of students in the Computer…arrow_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_forward
- Animal animal_id name adopter_id Adopter adopter_id name Given the above data for an adoption agency, what does the result set for the following query represent? SELECT adopter.name, animal.name FROM Animal CROSS JOIN Adopter; It represents every single Animal in the animal table regardless of whether they have been adopted or not. O It represents every single animal matches with every single adopter. It represents each animal, with the name of their adopter if that has been specified via a Foreign Key. QI 77°F Sunny Imml Warrow_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_forwardHello, 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_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