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

    SQL

    Receive aemail containing the next unit.
    • Introduction to SQL
      • 1.1Introduction to Database & SQL
      • 1.2SQL Environment Setup
      • 1.3Basic SQL Commands
    • Intermediate SQL
      • 2.1SQL Constraints and Joins
      • 2.2SQL Functions
      • 2.3SQL Views and Indexes
    • Advanced SQL
      • 3.1SQL Triggers and Sequences
      • 3.2Managing Data with SQL
      • 3.3SQL Stored Procedures
    • Real-World SQL Applications
      • 4.1SQL for Data Analysis
      • 4.2SQL and Big Data
      • 4.3SQL for Web Applications

    Intermediate SQL

    Understanding SQL Constraints and Joins

    SQL clause

    SQL clause.

    SQL Constraints

    SQL constraints are rules enforced on data columns in a table. These are used to limit the type of data that can go into a table, ensuring the accuracy and reliability of the data.

    Primary Key

    A primary key is a field in a table which uniquely identifies each record in a database table. Primary keys must contain unique values, and cannot contain null values. A table can have only one primary key, which may consist of single or multiple fields.

    Example of a primary key:

    CREATE TABLE Customers ( ID int NOT NULL, Name varchar(255) NOT NULL, Age int, PRIMARY KEY (ID) );

    Foreign Key

    A foreign key is a field (or collection of fields) in one table that refers to the primary key in another table. The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.

    Example of a foreign key:

    CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, CustomerID int, PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customers(ID) );

    SQL Joins

    SQL Joins are used to combine rows from two or more tables, based on a related column between them.

    Inner Join

    The INNER JOIN keyword selects records that have matching values in both tables.

    Example of an inner join:

    SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

    Outer Join

    The OUTER JOIN keyword returns records when there is a match in either the left (LEFT OUTER JOIN) or the right (RIGHT OUTER JOIN) table records. A FULL OUTER JOIN returns records when there is a match in either left (table1) or the right (table2) table records.

    Example of a left outer join:

    SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

    By understanding and implementing SQL constraints and joins, you can ensure data integrity and effectively link data across multiple tables.

    Test me
    Practical exercise
    Further reading

    Hi, any questions for me?

    Sign in to chat
    Next up: SQL Functions