Refer to the sample tables and sample data below.Tables:adult (id(pk), name)child (id(pk), name)parent_child (parent (pk, fk), child (pk, fk))foreign key (parent) references adult(id)- foreign key (child) references child(id)mysql> SELECT * FROM adult;|id|name|1 | Homer Simpson.| 2 | Marge Simpson||||||3 | Fred Flintstone4 | Wilma Flintstone5 | George Jetson6 | Jane Jetson7 | Patty Bouvier8 | Selma Bouviermysql> SELECT * FROM child;|id|name| 11 | Bart| 12 | Lisa| 13 | Maggie| 14 |Pebbles| 15 | Judy| 16 | Elroy| 17 | Lingmysql> SELECT * FROM parent_child;| parent child |1 |2 |1 |21 |213 |45 |in 10 in65 |10001111121213 |131414 |815556615 |6 | 161716 | (a) How many rows will be returned by the following command? [Select]SELECT DISTINCT a.name FROM adult aLEFT JOIN parent_child pc ON a.id = pc.parentLEFT JOIN child c ON pc.child = c.id;(b) How many rows will be returned by the following command? [Select]SELECT DISTINCT a.name FROM adult aRIGHT JOIN parent_child pc ON a.id = pc.parentRIGHT JOIN child c ON pc.child = c.id;

Please find the answer in the following steps.

(a) How many rows will be returned by the following command? [Select] SELECT DISTINCT a.name FROM adult a LEFT JOIN parent_child pc ON a.id = pc.parent LEFT JOIN child c ON pc.child = c.id; (b) How many rows will be returned by the following command? [Select] SELECT DISTINCT a.name FROM adult a RIGHT JOIN parent_child pc ON a.id = pc.parent RIGHT JOIN child c ON pc.child = c.id; <

(a) How many rows will be returned by the following command? [Select] SELECT DISTINCT a.name FROM adult a LEFT JOIN parent_child pc ON a.id = pc.parent LEFT JOIN child c ON pc.child = c.id; (b) How many rows will be returned by the following command? [Select] SELECT DISTINCT a.name FROM adult a RIGHT JOIN parent_child pc ON a.id = pc.parent RIGHT JOIN child c ON pc.child = c.id; <

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

write the code for the following triggers in MySQL following the style shown in the text. A: When adding a customer, add the customer balance multiplied by the sales rep's commission rate to the commission for the corresponding sales rep. B: When updating a customer, add the difference between the new balance and the old balance multiplied by the sales rep's commission rate to the commission for the corresponding sales rep. C: When deleting a customer, subtract the balance multiplied by the sales rep's commission rate from the commission for the corresponding sales rep. CREATE TABLE REP(REP_NUM CHAR(2) PRIMARY KEY,LAST_NAME CHAR(15),FIRST_NAME CHAR(15),STREET CHAR(15),CITY CHAR(15),STATE CHAR(2),POSTAL_CODE CHAR(5),COMMISSION DECIMAL(7,2),RATE DECIMAL(3,2) );CREATE TABLE CUSTOMER(CUSTOMER_NUM CHAR(3) PRIMARY KEY,CUSTOMER_NAME CHAR(35) NOT NULL,STREET CHAR(20),CITY CHAR(15),STATE CHAR(2),POSTAL_CODE CHAR(5),BALANCE DECIMAL(8,2),CREDIT_LIMIT DECIMAL(8,2),REP_NUM CHAR(2) );INSERT INTO…
Which of the following is a correct statement?a. A commit is issued implicitly when a user exits SQL Developer or SQL*Plus.b. A commit is issued implicitly when a DDL command is executed.c. A commit is issued automatically when a DML command is executed.d. All of the above are correct.e. Both a and b are correct.f. Both a and c are correct
Task 2: The Car Maintenance team also wants to store the actual maintenance operations in the database. The team wants to start with a table to store CAR_ID (CHAR(5)), MAINTENANCE_TYPE_ID (CHAR(5)) and MAINTENANCE_DUE (DATE) date for the operation. Create a new table named MAINTENANCES. The PRIMARY_KEY should be the combination of the three fields. The CAR_ID and MAINTENANCE_TYPE_ID should be foreign keys to their original tables. Cascade update and cascade delete the foreign keys. Create a new table to store maintenance operations Test Query DESCRIBE MAINTENANCES Expected Results Field Type Null Key Default Extra CAR_ID char(5) NO PRI NULL MAINTENANCE_TYPE_ID char(5) NO PRI NULL MAINTENANCE_DUE date NO PRI NULL
Write a stored procedure named updateUnitsOnOrder(). The updateUnitsOnOrder ()procedure should update the UnitsOnOrder field of those products that are discontinued. It should set the UnitsOnOrder field to 99 for those discontinued products. Hint: There is no need to use cursor processing for this stored procedure.
What is the most efficient way to write a trigger that updates a large number of rows? Specify "for each statement" Specify "for each row" Specify "for each table" Specify "referencing new row"
Task 2: The Car Maintenance team also wants to store the actual maintenance operations in the database. The team wants to start with a table to store CAR_ID (CHAR(5)), MAINTENANCE_TYPE_ID (CHAR(5)) and MAINTENANCE_DUE (DATE) date for the operation. Create a new table named MAINTENANCES. The PRIMARY_KEY should be the combination of the three fields. The CAR_ID and MAINTENACNE_TYPE_ID should be foreign keys to their original tables. Cascade update and cascade delete the foreign keys. Answer in MYSQL please
Insert the following records into their corresponding tables, using the MySQL CLI. Take a screenshot of a Select * command being successfully executed on each table (6 total). Administrator employeeNumber SchoolNumber firstname lastName phoneNumber officeNumber 4 45 Albus Dumbledore 2072087222 201 60 45 Minerva McGonagall 2406994295 150 119 45 Percy Weasley 4324061884 302 138 345 Ken Jeong 5058091461 503 52 345 Jim Rash 4173119531 654 16 345 John Oliver 4255510706 321 25 93 Dennis Haskins 5056465505 987 146 93 Hayley Mills 5056443920 852 101 93 Joan Ryan 5056444527 963 172 119 Eric Taylor 4197627238 741 89 119 Tami Taylor 5056449431 159 76 119 Matt Saracen 5058813720 753 193 54 George Feeny 2136699130 456 77 54 Jonathan Turner 5056833823 789 24 54 Eli Williams…
5. Open the UnsoldComponents query and complete the following tasks: a. Delete the criteria row that contains criteria in the InstallationFee field. b. Add the ClientID field from the ClientSoftware table to the query grid in the fourth column. С. Set the query to select all records from the SoftwareComponent table. d. Select only those records from the SoftwareComponent table with no matching records in the ClientSoftware table. Save and close the UnsoldComponents query. The query should match Figure 2 when in Datasheet view.
Produce a script file with 15 queries: At least two using join At least three using where clause At least three using order by At least two using single-row functions At least two using group by and group functions at least one nested query At least 4 queries from chapter 1 - 6 (exclude Create Table, Insert queries) ------- TABLE 1 create TABLE ProCoaches(pro_co_ID INTEGER PRIMARY KEY,pro_coachName Varchar(50) NOT NULL,pro_coachLocation Varchar(20)); INSERT INTO ProCoaches VALUES (1, 'John', 'Missoula'); INSERT INTO ProCoaches VALUES (2, 'Joe', 'Bozeman');INSERT INTO ProCoaches VALUES (3, 'Tara','Butte'); select * FROM ProCoaches;select LENGTH('Missoula') AS LengthOfLocation;select pro_coachName FROM ProCoaches where pro_coachLocation='Missoula'; select COUNT(pro_co_ID), pro_coachLocation FROM ProCoaches GROUP BY pro_coachLocation; TABLE 2 create TABLE ProCyclist(pro_cy_ID VARCHAR(10) PRIMARY KEY,pro_cyclist_Name VARCHAR(50) NOT NULL,pro_cyclist_Gender Varchar(20),pro_cyclist_DOB…
Which of the following is a correct statement?a. If you attempt to add a record that violates a constraint for one of the table’s columns, only the valid columns for the row are added.b. A subquery nested in the VALUES clause of an INSERT INTO command can return only one value without generating an Oracle 12c error message.c. If you attempt to add a record that violates a NOT NULL constraint, a blank space is inserted automatically in the appropriate column so that Oracle 12c can complete the DML operation.d. None of the above statements is correct.
USER_ID USER_FIRST_NAME USER_LAST_NAME USER_EMAIL 3001 Jack Hill j.hill@xmail.com 3002 Ryan Collins r.collins@xmail.com 3003 Nursin Yilmaz n.atak@gmail.com 3004 Sarah Price s.price@xmail.com 3005 Bobby Griffin b.griffin@xmail.com 3006 Randy Clark r.clark@xmail.com 3007 Jose Thomas j.thomas@xmail.com 3008 Nursin Yilmaz n.yilmaz@xmail.com
Insert the following records into their corresponding tables, using the MySQL CLI. Take a screenshot of a Select * command being successfully executed on each table (6 total). Subject subjectNumber Name gradeLevel 62 Algebra 9 33 Biology 10 83 Legal Studies 11 35 Entomology 9 70 Norse Mythology 10 23 Geography 10 67 Literature 12 78 World History 11 13 US History 12 9 Astronomy 9 53 Self Defense 12 85 Introduction to Computer Science 11 43 Algebra II 10