Software Testing & Quality Assurance Slides - Week 7
Date Published: 05/11/2019
These slides will explain the Primary and Foreign keys concepts with examples. Later, we will learn about different SQL Joins e.g. Inner, Outer, Full Outer, etc. to load data from two or more tables joined by Primary and Foreign keys relationship.

Agenda
  • Basic Database Operators:
    • Primary and Foreign key
    • SQL Joins (Inner and Outer)

      Primary key Constraint
      • The PRIMARY KEY constraint uniquely identifies each record in a database table.
      • Primary keys must contain UNIQUE values.
      • A primary key column cannot contain NULL values.
      • Most tables have a primary key.
      • Each table can have only ONE primary key
      • In the example on the next page, ProductID is the primary key of the Products table. 

          Primary key

              Foreign Key Constraint
              • A PRIMARY KEY in one table becomes a FOREIGN KEY in another table.
              • To create a relationship among tables, some information is kept common.
              • This information sharing is done via PRIMARY KEY, FOREIGN KEY relationship.
              • See example on next slide.

                  Foreign Key Contd...

                      Foreign Key Contd...
                      • In the previous example, the CategoryID column is the PRIMARY KEY in the Categories table 
                      • ProductID column is a PRIMARY KEY in Products table 
                      • However, CategoryID also exists in Products table as Foreign Key
                      • CategoryID column in Products table can contain only those which CategoryID column in Categories table already contains.

                          SQL Joins
                          • SQL joins are used to combine rows from two or more tables.
                          • Tables are joined based on a common field between them (Primary Key & Foreign Key)

                              SQL Joins Contd...
                              • INNER JOIN: Returns all rows when there is at least one match in BOTH tables
                              • LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
                              • RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
                              • FULL JOIN: Return all rows when there is a match in ONE of the tables

                                  INNER Join
                                  • The most common type of join is an INNER JOIN. INNER JOIN returns all rows from multiple tables where the join condition is met.
                                  • Example

                                  SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate

                                  FROM Orders INNER JOIN Customers

                                  ON Orders.CustomerID=Customers.CustomerID;

                                      LEFT Join
                                      • The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL on the right side when there is no match.
                                      • In some databases LEFT JOIN is called LEFT OUTER JOIN
                                      • Syntax

                                      SELECT column_name(s)

                                      FROM table1

                                      LEFT JOIN table2

                                      ON table1.column_name=table2.column_name;

                                          RIGHT Join
                                          • The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL on the left side when there is no match.
                                          • In some databases RIGHT JOIN is called RIGHT OUTER JOIN.
                                          • Syntax

                                          SELECT column_name(s)

                                          FROM table1

                                          RIGHT JOIN table2

                                          ON table1.column_name=table2.column_name;

                                              FULL OUTER Join
                                              • The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2).
                                              • The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.

                                              SELECT column_name(s)

                                              FROM table1

                                              FULL OUTER JOIN table2

                                              ON table1.column_name=table2.column_name;

                                                  UNION Operator
                                                  • The SQL UNION operator combines the result of two or more SELECT statements.
                                                  • Each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

                                                  SELECT column_name(s) FROM table1

                                                  UNION

                                                  SELECT column_name(s) FROM table2

                                                      UNION ALL Operator
                                                      • The UNION operator selects only distinct values by default. To allow duplicate values, use the ALL keyword with UNION.

                                                      SELECT column_name(s) FROM table1

                                                      UNION ALL

                                                      SELECT column_name(s) FROM table2;


                                                          Keywords: sql primary and foreign keys, sql joins, sql inner join, sql outer join, sql full outer join, sql joins tutorial