Linking Multiple Tables Flashcards
What is a relational database?
A relational database is a system where data is stored in tables. These tables are structured in a way that makes it easy to access and combine the data in various ways without needing to change the tables themselves.
How are the data in the tables related to each other?
The tables in a relational database are “related” to one
another through their primary and foreign keys
What is a one-to-one relationship in a relational database?
In a one-to-one relationship, each row in one table is linked to one and only one row in another table. For example, an Employee ID belongs to only one name (or one record in another table).
What is a one-to-many relationship in a relational database?
In a one-to-many relationship, a single row in one table can be linked to multiple rows in another table, but a child
record can only have one parent record. For example a customer in the Customers table can have multiple orders in the Orders table. An artist can have multiple albums.
What’s a many-to-many relationship in a relational database?
Multiple rows in one table can be linked to multiple rows in another table. This relationship requires a third table, known as a junction or linking table, to connect the two tables.
What’s an Entity Relationship Diagram?
An Entity-Relationship Diagram (ERD) visually represents how tables in a database are related to one another via primary and foreign keys.
If you want to join tables, what clause must you use to join them from?
If you want to pull data from multiple tables then they must all be included in the FROM clause
How is the JOIN keyword used?
The JOIN keyword is used to define relationships between tables.
What is the purpose of the ON Keyword in a JOIN clause?
The ON keyword determines the criteria for matching rows between the tables. Without the ON clause, the database would not know which columns to use for joining the tables.
What does an INNER JOIN do?
It returns values that have records in both tables you’re joining.
Explain this example:
SELECT Orders.OrderID, Customers.Name
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
SELECT Statement:
Specifies which columns to retrieve from the combined result set.
Orders.OrderID: Retrieves the OrderID column from the Orders table.
Customers.Name: Retrieves the Name column from the Customers table.
FROM Clause:
Specifies the primary table (Orders) from which data will be retrieved.
Indicates the starting point of the query.
INNER JOIN Operation:
Combines data from two tables (Orders and Customers) based on a common column (CustomerID).
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID:
This line specifies that you want to join the Orders table with the Customers table based on matching CustomerID columns.
It ensures that each order (Orders) is associated with the corresponding customer (Customers) based on their CustomerID.
What is a LEFT OUTER JOIN and RIGHT OUTER and when is it used?
A LEFT OUTER JOIN returns all records from the table on left side of the join, and only records with a match on the right side of the join
Imagine you have two tables: one containing information about customers and their orders (Customers table), and another detailing payments made for those orders (Payments table). When you perform a LEFT OUTER JOIN from Customers to Payments, you are instructing the database to retrieve all records from the Customers table regardless of whether there are matching records in the Payments table.
Here’s how it works: Each row from the Customers table will appear in the result set, even if there are no corresponding payment records in the Payments table. If a customer has made payments (and thus has matching records in the Payments table), those payment details will be included alongside the customer’s information. However, if a customer has not made any payments, the fields related to payments in the result set will typically be NULL, indicating the absence of matching data from the Payments table
A RIGHT OUTER JOIN
What’s a FULL OUTER JOIN?
A FULL OUTER JOIN returns all records from the table on left side of the join, and all records from the right side of the join
What’s a A CROSS JOIN?
A CROSS JOIN matches every row in table A with every row in table B. It is used when you need to generate all possible combinations between two sets of data. It’s particularly useful for scenarios like generating product combinations, creating test datasets, or exploring every possible pairing between items
What’s the result of using AND and OR keywords with a join clause?
In SQL, the AND and OR keywords can be used after the ON keyword in a JOIN clause allows you to add additional conditions that determine which rows from the joined tables should be included in the result set.
