Rewrite the following query with two subqueries into multicolumn subquery. The subquery should use a filteredcross join between the film and actor tables.SELECTfa.actor idfa.film_idfilm_actor fafa.actor_id IN(SELECT actor id FROM actor WHERE last nameFROMWHERE'MONROE')ANDfa.film_id IN(SELECT film id FROM film WHERE rating = 'PG');The foregoing query returns the following result set, and so should the new query:| actor_id| film_id |12063120144120414120590120715120894178164178194178273178 |178 |311 |983 |11 rows in set (0.09 sec)

Question a). Rewrite the following query with two subqueries into the multicolumn subquery. The…

Rewrite the following query with two subqueries into multicolumn subquery. The subquery should use a filtered cross join between the film and actor tables. SELECT fa.actor_id fa.film_id film_actor fa fa.actor_id IN (SELECT actor id FROM actor WHERE last name = fa.film_id IN (SELECT film_id FROM film WHERE rating = 'PG'); FROM WHERE ' MONROE') AND The foregoing query returns the following result set, and so should the new query: actor_id | film_id | 120 63 120 144 120 414 120 590 120 715 120 894 178 164 | 178 194 273 | 178 | 178 | 178 | 311 983 11 rows in set (0.09 sec)

Rewrite the following query with two subqueries into multicolumn subquery. The subquery should use a filtered cross join between the film and actor tables. SELECT fa.actor_id fa.film_id film_actor fa fa.actor_id IN (SELECT actor id FROM actor WHERE last name = fa.film_id IN (SELECT film_id FROM film WHERE rating = 'PG'); FROM WHERE ' MONROE') AND The foregoing query returns the following result set, and so should the new query: actor_id | film_id | 120 63 120 144 120 414 120 590 120 715 120 894 178 164 | 178 194 273 | 178 | 178 | 178 | 311 983 11 rows in set (0.09 sec)

Computer Networking: A Top-Down Approach (7th Edition)

7th Edition

ISBN:9780133594140

Author:James Kurose, Keith Ross

Publisher:James Kurose, Keith Ross

Chapter1: Computer Networks And The Internet

Section: Chapter Questions

Problem R1RQ: What is the difference between a host and an end system? List several different types of end...

See similar textbooks

Related questions

Q: Question #8. From invoices table, write a query to retrieve everything (all columns), plus, add an…

A: The query is given in the below step

Q: Build two SQL queries that combine film and category information. List the film’s title, film’s…

A: Creating Film table: CREATE table FILM ( filmID NUMBER(3) NOT NULL, filmTitle VARCHAR2(50)…

Q: Your company needs to contact the 1st author of each textbook and to discuss reprint options. Pull…

A: Microsoft AccessMicrosoft Access is a relational database management system that allows you to…

Q: Using the software.subscription table, pull all columns for subscriptions that have been cancelled.…

A: For the given database, if the user wants to select all the columns by giving the condition that the…

Q: Go ahead and run the below query into your "users" table. You just need to copy and paste it. 2-…

A: Output after inserting data.

Q: Create a new table, named Senior Teaching Staff, using the existing teacher table as your source.…

A: Structured query language is a programming language for storing and processing information in a…

Q: Write a Query First to Alter Table to add a new Column managername to the employee table…

A: Q: Write a Query First to Alter Table to add a new Column managername to the employee table…

Q: You are given an array of integers and you have to swap the first and last element of the array…

A: Complete the function in C language to swap the first and the last element of the array of size n…

Q: what is wrong with this query: Select Title,count(Rental_ID) as…

A: The query seems to be missing a join condition to link the Rental table with the Film and Inventory…

Q: this is the code i run in sql developer CREATE TABLE MARKETING_CAMPAIGN( MACampID int,

A: CREATE TABLE MARKETING_CAMPAIGN(MACampID int,MACampName varchar(30) not null,MACampDescr…

Q: Animal animal_id name adopter_id Adopter adopter_id name Given the above data for an adoption…

A: Cross join : Each row from the first table is combined with each row from the second table using a…

Q: reate a query in query design view based on both tables to answer the question, What is the concert…

A: Query: Create query: CREATE TABLE `sponsor`( `concert_id` INT(50), `sponsor_name` VARCHAR(50),…

Q: Create a query for displaying minimum salary in column alias “Minimum Salary” along with job_id…

A: Format of query :- SELECT colounname , MIN( coloumname) AS aliasname FROM tablename; According…

Q: Write a query that will display all movies with a return date of March 1, 2018, March 5, 2018, March…

A: Sql query to display all the movies with a return date of March 1, 2018, March 5, 2018, March 15,…

Q: Create a query that uses any THREE of the tables (SCHOOL, DEPARTMENT, PROFESSOR, TECHNOLOGY &…

A: Creating all the three tables required for the question-For Department-create table…

Q: Create a copy of the Total Balances By Client query. Name the query Total Balances By State. Open…

A: To create a copy of the Total Balances By Client query and modify it to show the total balances by…

Q: Using the Winners table from Figure 12-2, write a parameter query that selects theActor and Actress…

A: A query that has a parameter marker instead of a value of criteria is called as parameter query. The…

Question

Rewrite the following query with two subqueries into multicolumn subquery. The subquery should use a filtered
cross join between the film and actor tables.
SELECT
fa.actor id
fa.film_id
film_actor fa
fa.actor_id IN
(SELECT actor id FROM actor WHERE last name
FROM
WHERE
'MONROE')
AND
fa.film_id IN
(SELECT film id FROM film WHERE rating = 'PG');
The foregoing query returns the following result set, and so should the new query:
| actor_id| film_id |
120
63
120
144
120
414
120
590
120
715
120
894
178
164
178
194
178
273
178 |
178 |
311 |
983 |
11 rows in set (0.09 sec)

Expert Solution

This question has been solved!

Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.

This is a popular solution!

SEE SOLUTION Check out a sample Q&A here

Step 1 of 2

VIEW

Step 2 of 2

VIEW

Trending now

This is a popular solution!

Step by step

Solved in 2 steps with 1 images

SEE SOLUTION Check out a sample Q&A here

Similar questions

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:
Task 2: The Driver Relationship team wants to ensure that the all driving license IDs in the active drivers table have the length of 7. Alter the ACTIVE_DRIVERS table created in Chapter 8, Activity 1 to check the length of the DRIVER_DRIVING_LICENSE_ID. Add a CHECK to the ACTIVE_DRIVERS table to enforce a driver license length of 7. Test Query: SELECT DISTINCT(constraint_name) FROM information_schema.check_constraints WHERE constraint_schema = 'InstantRide' AND constraint_name = 'ACTIVE_DRIVERS_chk_1' Expected Results CONSTRAINT_NAME ACTIVE_DRIVERS_chk_1
Write a query that uses INNER JOIN TO connect the EMPLOYEE, EMPPROJACT, PROJACT and PROJECT tables. Include the Project number , Department number, Project start and end date and AC STAFF WHERE They belong to department D11 , Salary is more than or equal to 65 percent of $15,000 AND Salary is less than or equal to 130 percent of $40,000
Write an SQL query that will select OrderID and OrderDate from theOrders table and OrderDetailID from the OrderDetails table. The queryshould join on the Orders table using the OrderID attribute.
Which type of join is demonasted by the folloing query? Select from make. model where make modelID to mode ID a) Non-Equlion b) Self Join c) Cross Join d) Equijoin
Create a View to show the city name of any location that has a department. Only show the city name once in the results. You must use a non-correlated subquery.Call this view: Department_LocationsInclude a second query to display from the View after it is created. Refer to the attached for the table.
The BLOOD_ADMIN_INFO are ORDER ID and LINE. The MAR_ADMIN_INFO MAR_BLOOD_INFO_LN table has one row per blood unit associated with an order. Its primary key columns table has one row per medication administration. The columns ORDER_MED_ID and form the foreign key to the BLOOD_ADMIN_INFO table. Use the followin*query to answer the question below it- SELECT mar.ORDER_MED_ID, mar.SCHEDULED_TIME, mar.TAKEN TIME, bld.BLOOD_ADMIN_TYPE FROM MAR_ADMIN_INFO as mar INNER JOIN BLOOD_ADMIN_INFO as bld What join condition is appropriate in ? AS [Blood Type] Select on of the following: A. mar.ORDER_MED_ID = bld.ORDER_ID B. mar.MAR_BLOOD_INFO_LN = bld.LINE C. mar.ORDER_MED_ID = bld.ORDER_ID AND mar.MAR_BLOOD_INFO_LN = bld.LINE D. It is not possible to join these tables
Write the following as MySQL query: Find the employee ID, first and last name, and salary for each employee whose salary ismore than $20,000 or who works in the shipping department. Use the UNION set operator. List the country ID, country name, city, and state/province. Be sure all country names areincluded in the result, regardless of whether there is no matching city and state/province. You have to use an outer join.
i need to create a query that joins these 3 tables, without the duplicate column which would be the shop_id. i cant get them to join without that column duplicating. help would be appreciated thanks! CREATE TABLE COFFEE_SHOP ( shop_id int, shop_name VARCHAR(50), city VARCHAR(50),state CHAR(2), PRIMARY KEY (shop_id)); CREATE TABLE EMPLOYEE ( employee_id int, first_name VARCHAR(30), last_name VARCHAR(30),hire_date Date,job_title VARCHAR(30),shop_id int, PRIMARY KEY (employee_id),FOREIGN KEY (shop_id) REFERENCES COFFEE_SHOP(shop_id)); CREATE TABLE COFFEE ( coffee_id int, shop_id int, supplier_id int,coffee_name VARCHAR(30),price_per_pound NUMERIC(5,2), PRIMARY KEY (coffee_id),FOREIGN KEY (shop_id) REFERENCES COFFEE_SHOP(shop_id),FOREIGN KEY (supplier_id) REFERENCES SUPPLIER(supplier_id));
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.
Write a query that returns the Name of ALL Customers (based on CUS_FNAME and CUS_LNAME in CUSTOMER table, call this CUSTOMER_NAME), Phone number of ALL Customers (based on CUS_AREACODE and CUS_PHONE in CUSTOMER table, call this CUSTOMER_PHONE), for those haven’t flied before (based on the record in CHARTER table).
The given SQL creates a Song table and inserts some songs. The first SELECT statement selects songs released after 1992. The second SELECT statement selects the release year for song with ID 800. Create a third query that combines the two existing queries. The first SELECT should be the outer query, and the second SELECT should be the subquery. The ORDER BY clause should appear after the subquery. Run your solution and verify the new query returns a result table with five rows, all with release years after 1992. CREATE TABLE Song (ID INT,Title VARCHAR(60),Artist VARCHAR(60),ReleaseYear INT,Genre VARCHAR(20),PRIMARY KEY (ID)); INSERT INTO Song VALUES(100, 'Hey Jude', 'Beatles', 1968, 'pop rock'),(200, 'You Belong With Me', 'Taylor Swift', 2008, 'country pop'),(300, 'You\'re Still the One', 'Shania Twain', 1998, 'country pop'),(400, 'Need You Now', 'Lady Antebellum', 2011, 'country pop'),(500, 'You\'ve Lost That Lovin\' Feeling', 'The Righteous Brothers', 1964, 'R&B'),(600, 'That\'s…