101.school
CoursesAbout
Search...⌘K
Generate a course with AI...

    How Databases work

    Receive aemail containing the next unit.
    • Introduction to Databases
      • 1.1What is a Database?
      • 1.2Importance of Databases
      • 1.3Types of Databases
    • Database Models
      • 2.1Hierarchical Model
      • 2.2Network Model
      • 2.3Relational Model
      • 2.4Object-oriented Model
    • Relational Databases
      • 3.1Introduction to Relational Databases
      • 3.2Tables, Records, and Fields
      • 3.3Keys and Indexes
    • SQL Basics
      • 4.1Introduction to SQL
      • 4.2Basic SQL Commands
      • 4.3Creating and Modifying Tables
    • Advanced SQL
      • 5.1Joins
      • 5.2Subqueries
      • 5.3Stored Procedures
    • Database Design
      • 6.1Normalization
      • 6.2Entity-Relationship Diagrams
      • 6.3Data Integrity
    • Transaction Management
      • 7.1ACID Properties
      • 7.2Concurrency Control
      • 7.3Recovery Techniques
    • Database Security
      • 8.1Security Threats
      • 8.2Access Control
      • 8.3Encryption and Authentication
    • NoSQL Databases
      • 9.1Introduction to NoSQL
      • 9.2Types of NoSQL Databases
      • 9.3Use Cases for NoSQL
    • Big Data and Databases
      • 10.1Introduction to Big Data
      • 10.2Big Data Technologies
      • 10.3Big Data and Databases
    • Cloud Databases
      • 11.1Introduction to Cloud Databases
      • 11.2Benefits and Challenges
      • 11.3Popular Cloud Database Providers
    • Database Administration
      • 12.1Roles and Responsibilities of a Database Administrator
      • 12.2Database Maintenance
      • 12.3Performance Tuning
    • Future Trends in Databases
      • 13.1In-memory Databases
      • 13.2Autonomous Databases
      • 13.3Blockchain and Databases

    Advanced SQL

    Understanding and Implementing SQL Subqueries

    organized collection of data in computing

    Organized collection of data in computing.

    SQL subqueries, also known as inner queries or nested queries, are a powerful tool in SQL that allow you to retrieve data using multiple queries simultaneously. They are used to manipulate the data in such a way that the result of one query is used in another query.

    What are Subqueries?

    A subquery is a query that is embedded within another SQL query. It can be used in various parts of a query, including the SELECT, FROM, WHERE, and HAVING clauses. The result of a subquery can be used as an expression or a value for comparison in the main query.

    Why Use Subqueries?

    Subqueries are used when the result of one query depends on the result of another query. They can simplify complex queries by breaking them down into more manageable parts. Subqueries can also be used to perform operations that cannot be performed in a single SQL statement.

    Types of Subqueries

    There are three main types of subqueries:

    1. Single-row subquery: Returns only one row from the inner SELECT statement.
    2. Multiple-row subquery: Returns multiple rows from the inner SELECT statement.
    3. Correlated subquery: A subquery that is executed once for each row processed by the outer query.

    Nested Subqueries

    Nested subqueries are subqueries that are nested inside another subquery. A subquery can be nested inside the WHERE or HAVING clause of an outer SQL statement. An inner subquery can be used in the WHERE clause of an outer SQL statement to further filter the results.

    Practical Examples

    Let's look at some examples of how to use subqueries in SQL.

    1. Single-row subquery:
    SELECT employee_name, salary FROM employees WHERE salary > (SELECT salary FROM employees WHERE employee_name = 'John');

    This query will return the names and salaries of employees who earn more than John.

    1. Multiple-row subquery:
    SELECT employee_name, department_id FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

    This query will return the names of employees who work in departments located in New York.

    1. Correlated subquery:
    SELECT e1.employee_name, e1.department_id FROM employees e1 WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id);

    This query will return the names of employees who earn more than the average salary in their department.

    By understanding and implementing SQL subqueries, you can write more complex and powerful SQL statements to retrieve and manipulate data in your database.

    Test me
    Practical exercise
    Further reading

    Buenos dias, any questions for me?

    Sign in to chat
    Next up: Stored Procedures