SQL & MySQL Subqueries: Understanding the weird parts

Learn to use a nested subquery with like to fetch customer company names from cities starting with l, using in to compare against cities returned by a subquery.

Identify the names and titles of employees who manage others by matching employee IDs to their manager IDs in the employees table using a nested subquery.

Master nested subqueries to retrieve territory descriptions for a specific employee by linking territories and employee territories tables and filtering on the employee with ID five.

Identify categories whose products have a unit price greater than 20 by using a nested subquery that links categories to products via category ID to fetch category names.

Identify supplier names that supply products priced above 100 using a nested subquery linking the supply and product tables via supplier_id.

Explore a nested subquery to identify product names that have never been ordered by checking product IDs against the order details table.

Identify employees who have not received any orders using a nested subquery that compares employee_id against the orders table.

Master nested subqueries by retrieving employee names who share the same region as a given employee, using the employees table and region comparisons.

Identify territories not assigned to any employee by using a nested subquery with not in against the employee territories table, returning territory names and descriptions.

Identify employees who have at least one other with freight greater than 50 by joining employees with orders and filtering by freight > 50 to return names.

Use nested subqueries across suppliers, products, and categories to return supplier names that supply products in the seafood category.

Use a subquery to find the city of the customer with ID five and list all customers in that city, showing how city links same-table records.

Use a nested subquery to find employees who share the same manager as the employee with id five, and return their first and last names.

Learn how a nested subquery finds product names with reorder level zero in the same category using category_id, and why results may be empty.

Identify employees who are managed by others, and ensure their managers, identified by mg_id, also have managers, using nested subqueries on the employees table.

Apply a nested subquery to list supplier names in the same country as supplier with id five, by selecting the country from the suppliers table and filtering on it.

The lecture demonstrates a nested subquery to find territory names assigned to employees who have the same title as the employee with id five, using the territories and employees tables.

Explore nested subqueries to identify product names supplied by the same company as their supplier, linking products to suppliers via supplier_id and filtering by company_name.

Identify customers who placed orders for products supplied by suppliers in the same city as supplier with id 5, using nested subqueries through customers, orders, order details, products, and suppliers.

Identify product names and unit prices for items that have never been ordered by any customer using a nested subquery that checks product IDs against the order details table.

Identify territories that have never been assigned to any employee using a nested subquery, by comparing territory IDs against the employee_territories table and returning the territory descriptions.

Identify customers who never placed any order using a nested subquery, returning their company name as customer name and their contact title.

Explore how nested subqueries connect products to orders through order details, filtering by the order date year 2008 to list product names ordered by customers that year.

Identify employees who share the same territory as employee id five by using a nested subquery on the employee territories table to match territory ids.

Explore nested subqueries to list product names ordered by customers, filtering for orders with freight costs greater than 50 by traversing from products to order details to orders.

Use a nested subquery to list employees who are managed by a Foxx, connecting employee_id and manager_id to identify each supervisor.

Identify suppliers who supplied products to a single customer using a nested subquery across suppliers, products, orders, and customers tables. Extract supplier names (company name) and related IDs.

Identify customers who placed orders for products with unit price greater than 30 by navigating from customers to orders to order details to products with a nested subquery.

Use a nested subquery to find employees who have territories in the western region by joining territory and region on region_id and filtering region_description. The example returns Rochelle and Paul.

Show the company names of customers who ordered products in the beverages category using a nested subquery across customers, orders, order details, products, and categories.

Learn how to use a nested subquery to list suppliers whose products were ordered by customers in London, using a step-by-step sequence from suppliers to customers.

Practice using a subquery to identify products never ordered by retail customers, listing their names and unit prices. Start from the product table and inspect the database schema.

Explore how to identify products that have never been ordered by retail customers using a subquery pathway from products through order details to orders, customers, and customer demographics.

Identify category names that have at least one product ordered by a customer in Germany, and walk through categories, products, and orders.

Link categories to products via category id and connect to order details, orders, and customers. Filter by country Germany to return the categories of products ordered by German customers.

Explore how to list region names that have at least one territory assigned to an employee with the title sales representative, as the instructor sets up the upcoming solution.

Use subqueries to list customers who ordered beverages, extracting their names and contact titles from orders and product categories.

Explore SQL and MySQL subqueries to identify customers who ordered beverages by linking customers, orders, order details, products, and categories to extract contact names and titles.

Identify employees who are in charge of territories that include Seattle, using SQL subqueries in MySQL.

Join the employee, employee territory, and territories tables to retrieve employees by territory description, using equals or like on territory description to identify C2 and its variations.

List the names of products ordered by customers with a contact title of owner, as explored in this question and its forthcoming solution.

Apply a subquery to identify products ordered by customers whose contact title equals owner, linking products, order details, orders, and customers to show owner-related purchases.

Apply a subquery to list product names ordered by customers from Germany with unit prices above 20, and prepare to discuss the solution in the next video.

Explore subqueries to find products ordered by customers from Germany and validate unit prices above 20 by joining products, order details, orders, and customers.

Identify the names of suppliers that have supplied products ordered by customers in the WA region, illustrating how SQL subqueries address this question.

Identify supplier names who supplied products ordered by customers in the wa region by tracing from suppliers to products through order details and orders, using a subquery.

Identify suppliers who supplied products ordered by customers in the WA region with a total cost above 4000. Explore how subqueries address this condition.

Explore how to find suppliers who supplied to WA region customers with orders above 4000 by joining suppliers, orders, order details, and customers and computing line totals.

Explore how to identify customers who ordered products from suppliers based in the United Kingdom using subqueries, with a practical solution discussion.

Retrieve the contact names of customers whose orders involve products supplied by UK suppliers, using customers, orders, order details, products, and suppliers tables.

Explore how joins connect data across one or more tables, from employees and orders to tables that are far apart, and prepare to use joins inside subqueries with practical examples.

Master inner joins by querying the customers and orders tables on the customer_id to retrieve customer names and order dates, even when column names differ.

You can interchange parts of a query and still get the same result, so rearranging them does not affect the outcome.

Learn how to indicate the source of columns when joining customers and suppliers by qualifying each column with its table or alias, to avoid ambiguity and ensure correct results.

Learn to alias columns and tables in SQL queries, using or omitting as, with examples like pc name and C to clarify results in joins.

Learn how to simplify sql joins by using common column names, and switch to on for mismatched names, clarifying primary and foreign key relationships.

Use an inner join between the product table and the supplier table to retrieve the product name, unit price, and supplier name using the supplier ID.

Explore joining the products and categories tables with an inner join on category_id to retrieve product and category names, then simplify using aliases like p and c.

Execute an inner join across the products, suppliers, and categories tables in the Northwind database to display product name, supplier name, and category name.

Use inner join across suppliers, products, and order details to compute the total quantity per product, showing product and supplier names. Group by product and supplier, order by total quantity.

Apply having to filter results after grouping, using aggregation such as count by country, to include only countries with more than ten customers and order by count.

Explore how to use having to filter aggregated results by count of orders per customer, ensuring only customers with at least two orders appear.

Explore how to retrieve company names, order IDs, and employee IDs by joining the customers and orders tables in the Northwind database, clarifying which IDs come from which table.

Identify employees who registered more than 100 orders by counting order IDs, joining the employees and orders tables, and grouping by first and last name, then sorting by count.

Learn to retrieve product names, supplier company names, and the total quantity ordered by joining products, suppliers, and order details in the Northwind database.

Compute the total quantity of products sold by each category by joining categories to products and using the order details table, then sort results by total quantity sold descending.

Join categories, products, and order details to compute each category's total quantity sold, then group by category name and order by total quantity descending.

retrieve customer names and their purchased product names with unit prices greater than 100 by joining the customers, orderdetails, and products tables.

Learn to join customers, orders, order details, and products, select distinct customers, and filter by unit price greater than 100 to show customer names and purchased product details.

Discover how to identify customers who ordered from a specific supplier by traversing the customers, orders, order details, product, and suppliers tables with joins on key ids.

Identify customers who ordered products from a specific supplier by querying customers, orders, details, products, and suppliers, filtering by supplier name, and checking order IDs, product names, and order dates.

Explore how to report each product sold in the orders table by its total quantity and total price, including the company name and the supplier name.

Aggregate total quantity and total price by joining supplier, products, and order details, using supplier name and product name, and group by to summarize by supplier and product.

Compute the average unit price of products purchased by each customer, and display each customer's company name and country.

Demonstrate spotting an unnecessary join to the products table in a customer, orders, and order details query, and explain why removing it preserves correct results.

Identify customers who ordered products from a Germany-based supplier and list the product names using subqueries, navigating the customers, products, and supplier schema.

Learn to retrieve customer and product names for Germany by traversing from customers to orders to order details to products to suppliers, filtering on the supplier country.

Calculate the total quantity of each product and product category ordered by customers who share the same country as their suppliers, using subqueries.

Compute the total quantity of products ordered for customers and suppliers in the same country by joining category, product, order details, orders, customer, and supplier tables where country matches.

Identify how to list customers with the products they ordered, plus the suppliers and categories, by joining the relevant tables in SQL and MySQL subqueries.

Explore joining customers, orders, order details, products, suppliers, and categories to fetch names and relationships, and learn why join order and table measurement matter to avoid errors.

Identify employees who registered orders for products supplied by a specific supplier by traversing the employees, purchases, supplies, products, and suppliers tables.

Demonstrate querying employees and their order IDs through the chain from employee to orders to order details to product to supplier, filtering by a specific supplier name.

Identify employees who managed more than ten orders using subqueries, and return customer names with the total order cost calculated from unit price times quantity in order details.

Learn how to compute a customer's total sales by multiplying unit price by quantity, applying discounts, and filtering those with more than ten orders using group by and having.

Explore computing the average unit price of products from each supplier who has at least one order from a customer in France, using subqueries in SQL and MySQL.

Learn to return supplier names with their average unit price by joining suppliers, products, orders, and customers from France, using group by and having, with optional descending sort.

Compute total quantity of each product sold by each supplier. Include those with more than 1200 units, show supplier company name, product name, and total quantity, ordered by descending totals.

Explore the solution for totaling product quantities and identifying suppliers who sold more than 1200 units using SQL subqueries, with run-and-compare steps to confirm results.

Explore join question in sql subqueries: compute average price of products sold per employee, filter > 200, and display first name, last name, title, product name, ordered by average price.

Present the final join solution, using group by for the remaining columns and a unit price greater than $200, showing the output of the query.

Build mastery of join and subqueries by combining what you learned, then dive deeper into subqueries in the next session to apply advanced techniques.

Explore how join and subquery techniques retrieve product names and category names from the Northwind database, comparing inner joins with a category name subquery to produce identical results.

Explore retrieving employee names and order dates using joins and subqueries. Learn how inner joins and subqueries determine which staff placed which orders by matching employee IDs.

Fetch product names supplied by a specific company by joining the products and suppliers tables on supplier ID, and practice replacing the join with a subquery to reference supplier ID.

Explore counting total products per supplier by replacing a join with a subquery in SQL and MySQL, grouping by supplier name to return each supplier's product total.

Learn to retrieve the product name and supplier name by joining the products and suppliers tables, and use a subquery to glue external and internal selects.

Explore how joins and subqueries fetch product names and supplier names from the Northwind database, demonstrating an inner join and a subquery approach to select the needed fields.

Learn to retrieve product names and their categories supplied by a specific supplier using joins across categories, products, and suppliers, with a subquery replacing the inner join.

See how to fetch product name, unit price, category name, and supplier company name by joining products with categories and suppliers. Then use a subquery to replace the company name.

Learn to compute the average total quantity sold per product and the number of orders, by joining products, order details, orders, and suppliers to display each supplier's company name.

Explore how to use joins and subqueries to find customers who ordered seafood products shipped to france and supplied by usa-based suppliers, with practical query examples.

Explore single row subqueries that return one row of data and can be used for calculated values or filters in the outer query, including averages, lookups by identifier, and percentages.

Identify the oldest employee by using a single-row subquery to get the minimum hire date, then filter to return the matching first and last name.

Find the company name of the customer with the most recent order using order by date desc, limit 1, and a single-row subquery to retrieve the customer ID.

Identify the product with the highest unit price using a single row subquery to fetch the maximum price from the products table and its name.

Identify the order with the highest freight value using a single-row subquery that compares each order's freight to the maximum freight from the orders table.

Identify the top customer by order count using a single-line subquery, then count that customer's orders from the orders table and join to show the customer name.

Identify the most expensive product per category by joining the products and categories tables on category ids, using a single-row subquery to compare unit prices within each category.

Discover common table expressions (CTEs) and the with keyword to create named temporary results, simplify complex queries, enable multiple references, and reduce redundant processing in SQL statements.

Explore a common table expression by defining with x as a subquery. Select last name from x and observe how order by works only with columns visible in x.

Learn how to retrieve each customer's total order amount by computing quantity times unit price, using a common table expression (cte), and ordering results by total sales descending.

Compute total sales by multiplying quantity by unit price for each product by category, using joins across categories, products, and order details, and group by category and product name.

Explore using a common table expression to compute top selling products by quantity from the product sales table, summing quantities, ordering by descending, and understanding the temporary table concept.

Compute the average unit price for each product in each category using a common table expression, joining categories and products, and grouping by category and product.

Use a CTE to compute total sales by category, then a derived table to count orders and calculate the average sales per order.

Learn to use two cte definitions to produce last name, first name, total sales, and region sales by joining employees, orders, order details, and territories, then group by city.

Identify the top five customers by total spending, calculated as quantity times unit price, by joining customers with spending data, group by customer, order by spending, and limit to five.

Identify the top five discontinued products by total sales, calculated from unit price times quantity, filtering where discontinued equals one and ordering by total sales.

Extracts average order value by aggregating quantity times unit price for each customer, using a CTE to combine order and order details.

Learn to compute total sales as quantity multiplied by unit price, join products with suppliers, order details, and categories, and return the five products with their supplier and category names.

Explore inline view subqueries by using a select inside the from clause to create a derived, on-the-fly temporary table that can be joined or used in further calculations.

Use an inline view subquery to compute a product's discount amount by joining products and orderdetails tables, selecting product name, unit price, and discount, and calculating unit price times discount.

Explore computing the average order value per customer with an inline view that sums quantity times unit price per order, then averages per customer via a join.

Use an inline view to count each customer's orders by grouping on customer_id, then join customers to orders on customer_id and order by total descending to show the top five.

Use an inline view and subquery to count unique customers per product, join to get product names, and sort by the largest customer counts.

Identify the top ten products by total quantity sold using an inline view to aggregate per order, then join to products and apply group by, order by, and limit.

Explore scalar subqueries, which return a single value and can be used in select, where, and order by clauses, including nesting with other queries for data analysis in SQL databases.

Use a scalar subquery to compute sales per employee by summing unit price times quantity from orders and other details, then list employees whose sales exceed 100,000.

Identify the orders shipped by the most recently hired employee using a scalar subquery, selecting the single matching order id and shipment date from the orders table.

Use a scalar subquery to return categories whose products have an average unit price over 30, joined on category id, listing category id and name.

Explore scalar subqueries to find the employee with the fewest shipped orders by counting rows in the orders table, deriving a single value, and joining to fetch the employee name.

Explore the multiple row subquery, a nested subquery returning more than one row. Use it with in or any, and in the where clause or from clause.

Identify products ordered more than 50 times using a multi-row subquery, where the materials subquery returns multiple IDs and the outer query uses in to filter, then reveal product names.

demonstrate a multi-row subquery that returns distinct customer ids from orders for 2008, and then retrieve the corresponding customer names with an outer query.

Apply a multiple-rows subquery to list employee IDs that have at least one sale and return those employees' names.

Identify categories with at least ten products by grouping by category_id and having counts over ten, using a multi-row subquery, and fetch category names via an outer query.

Identify suppliers with products priced over $100 by querying the products table for distinct supplier IDs with unit_price > 100 and retrieving their names from suppliers, all without a join.

Learn how to use a multi-row subquery to find products whose unit price exceeds the average price in their category, using category-level aggregation and an external comparison.

Explore how a multi-row subquery finds other customers who ordered the same products as a given customer, using order details and product IDs to identify matches and exclude the reference.