SQL & MySQL Subqueries: Understanding the weird parts

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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 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.

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 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.

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.

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.

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.

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 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.

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 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.

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.

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.

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.