Podcast
Questions and Answers
What is a subquery?
What is a subquery?
A subquery is a SELECT statement that is coded within another SQL statement.
Which of the following clauses can be used within a query that allows a subquery to be introduced?
Which of the following clauses can be used within a query that allows a subquery to be introduced?
- WHERE (correct)
- HAVING (correct)
- FROM (correct)
- SELECT (correct)
List the names of all the customers who are in the same city as Freddi using a subquery.
List the names of all the customers who are in the same city as Freddi using a subquery.
SELECT customer_id, first_name, state FROM customer WHERE state = (SELECT state FROM customer WHERE first_name = 'Freddi')
List the product with the highest unit price.
List the product with the highest unit price.
List name of customers that have orders with more than 3 quantity, using a nested subquery.
List name of customers that have orders with more than 3 quantity, using a nested subquery.
Using a subquery, write SQL query to insert a new record into order_item where the order_id is Naseby.
Using a subquery, write SQL query to insert a new record into order_item where the order_id is Naseby.
Use a subquery to write a SQL query that increases the unit price of products by 10% if they have more than 75 items in stock.
Use a subquery to write a SQL query that increases the unit price of products by 10% if they have more than 75 items in stock.
Write a SQL query to delete any products that have not been ordered.
Write a SQL query to delete any products that have not been ordered.
Using a subquery in the WHERE clause will include the results in the final result set:
Using a subquery in the WHERE clause will include the results in the final result set:
When using a subquery, the first SELECT statement determines the fields that will be displayed in the final result.
When using a subquery, the first SELECT statement determines the fields that will be displayed in the final result.
Flashcards
Multi-table select
Multi-table select
Joining more than two tables in a query. This involves multiple JOIN and ON clauses.
Joining multiple tables
Joining multiple tables
Combining data from various tables using SQL JOIN clauses. Connects tables based on related columns.
SQL JOIN clause
SQL JOIN clause
Connects tables to retrieve data from multiple related sources using matching columns as defined through ON clauses
ON clause
ON clause
Signup and view all the flashcards
JOIN types (inner join)
JOIN types (inner join)
Signup and view all the flashcards
JOIN types (left join)
JOIN types (left join)
Signup and view all the flashcards
JOIN types (right join)
JOIN types (right join)
Signup and view all the flashcards
Customer orders
Customer orders
Signup and view all the flashcards
Order items
Order items
Signup and view all the flashcards
Products table
Products table
Signup and view all the flashcards
Subqueries
Subqueries
Signup and view all the flashcards
Parent table
Parent table
Signup and view all the flashcards
Intermediary table
Intermediary table
Signup and view all the flashcards
Mix of joins
Mix of joins
Signup and view all the flashcards
Study Notes
Lecture 11: Multi-Table Selects & Intro to Subqueries
- Lecture is about multi-table selects and subqueries in SQL.
- The outline includes multi-table selects, subqueries, joins vs subqueries, and additional uses of subqueries.
Multi Table Select
- Joining more than 2 tables requires multiple JOIN and ON clauses.
- Example: Getting customer orders with associated products requires identifying tables (e.g., Customer Orders, Order Items, Product), determining join types, starting with the parent table, joining with an intermediary table (Order Items), and finally joining to the Product table.
- Direct joins between Product and Customer Orders tables are not sufficient for the complete information. An intermediary table called Order Items is needed.
Subqueries
- A subquery is a SELECT statement inside another SQL statement.
- It can return a single value, a list of values (a single column or a table of values).
- Subqueries can be used in a variety of places, including the WHERE, HAVING, FROM, and SELECT clauses.
- Examples: finding customers in the same city as another customer, getting the product with the highest unit price, listing customers with orders having more than 3 items.
- Nested subqueries can be used for complex queries, where a subquery is used within another subquery in the WHERE clause. For example, finding customers having orders with more than 3 items requires using an inner query within the outer query.
- The order of the parenthesis matters when using nested subqueries.
- The field in the inner query must correspond to the field inside the outer query.
- Table names in subqueries can differ from those in the main query.
Joins vs Subqueries
- Subqueries can frequently be rewritten as joins; and vice versa.
- Using the most readable technique is generally preferred when there is no particular reason to favor one over the other.
- Joins can be more intuitive as they use existing relationships; however, subqueries can be used to obtain aggregate results from one table.
- Joins can be memory intensive. In a WHERE clause, the results of a subquery might not be included in the final result.
Uses of Subqueries
- Uses of subqueries include inserting, updating, and deleting records in a table.
- Example: Inserting a new record into an order_item with given parameters such as order ID, product ID, quantity, and price.
- Example: Updating the unit price of products that have more than 75 items in stock by 10%.
- Example: Deleting products that are not in order_item table.
Zoo Database (Example)
- Finding animals of a specific species.
- Finding events with prices above or equal to average price.
- Finding animals fed at 9 AM.
Studying That Suits You
Use AI to generate personalized quizzes and flashcards to suit your learning preferences.
Related Documents
Description
This quiz covers multi-table selects and the introduction to subqueries in SQL. You'll learn about the use of joins, intermediary tables, and how subqueries can enhance data retrieval. Test your understanding of these fundamental concepts in relational databases.
