Creating and Managing Tables in SQL: A Step-by-Step Guide

Watch here 

A schema is essentially a blueprint or an organizational framework for a database. It defines how data is organized and how the relations among them are associated. It involves the layout of tables, the fields in each table, and the relationships between fields and tables.

For example, in the Northwind database, the schema would include the structure of tables like Customers, Orders, Products, etc. It would define fields in each of these tables (like CustomerID, OrderDate, ProductName) and relationships between them (such as Orders being linked to Customers through CustomerID). This schema helps in understanding how the data is organized and interconnected within the Northwind database, providing a clear map of its structure and how to query it.

PRIMARY KEY

A primary key in a database is a unique identifier for each record in a table. It ensures that no two rows in the table can have the same value in this column, thus maintaining the uniqueness of each record.

In the Northwind database, an example of a primary key is the OrderID field in the Orders table. Each order placed in the Northwind company is given a unique OrderID, which is used to uniquely identify and reference that specific order within the database. This primary key prevents the confusion of orders and ensures data integrity.

FOREIGN KEY

A foreign key in a database is a field (or collection of fields) in one table that uniquely identifies a row of another table. The foreign key establishes a relationship between two tables.

In the Northwind database, an example of a foreign key can be found in the Orders table. This table might have a column named CustomerID, which serves as a foreign key. It refers to the CustomerID in the Customers table. This relationship allows the database to understand that each order is associated with a specific customer. By using this foreign key, the database can link each order to the correct customer record, maintaining the relational integrity of the database.

How to add a Foreign Key

ALTER TABLE Orders
ADD CustomerID int,
ADD FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

• CustomerID is added to the Orders table.

• The FOREIGN KEY constraint is then used to indicate that CustomerID in Orders references CustomerID in Customers.

This establishes a relational link between the two tables, ensuring that each order in the Orders table is associated with a valid customer in the Customers table.

COMPOSITE KEY

A composite key in a database is a key consisting of two or more columns in a table that, together, uniquely identify a record in that table. The individual columns may not be unique by themselves, but their combination is unique.

For example, in the Northwind database, suppose there's a OrderDetails table that stores each item of an order. This table could have a composite key consisting of OrderID and ProductID. Neither OrderID nor ProductID uniquely identifies an entry in OrderDetails by themselves, as there might be multiple products per order and the same product might appear in multiple orders. However, the combination of OrderID and ProductID is unique for each row, ensuring that each product within each order is uniquely identified.

MODIFYING EXISTING DATA IN DATABASE

ADDING A COLUMN

This adds a new column named DiscountRate to the Orders table.

ALTER TABLE Orders
ADD discount_rate float;

MODIFYING A COLUMN

This changes the data type of the DiscountRate column in the Orders table to an integer.

ALTER TABLE Orders
ALTER COLUMN discount_rate int;

UPDATING DATA

This command updates the ShipName in the Orders table for the order with OrderID 12345.

UPDATE Orders
SET Ship_name = 'New Ship Name'
WHERE order_id = 10248;

JOINS IN SQL

INNER JOINS

Retrieves records that have matching values in both tables.

SELECT Orders.order_id, Customers.customer_name
FROM Orders
INNER JOIN Customers ON Orders.customer_id = Customers.customer_id ;

LEFT OUTER JOIN

Retrieves all records from the left table, and the matched records from the right table. If there's no match, NULL values are returned for the right table.

SELECT Customers.contact_name, Orders.order_id
FROM Customers
LEFT JOIN Orders ON Orders.customer_id = Customers.customer_id ;

RIGHT OUTER JOIN

Retrieves all records from the right table, and the matched records from the left table. If there's no match, NULL values are returned for the left table.

SELECT Orders.order_id, Employees.last_name
FROM Orders
RIGHT JOIN Employees ON Orders.employee_id = Employees.employee_id;

FULL(OUTER) JOINS

Combines the results of both left and right outer joins. It returns all records when there is a match in either left or right table.

SELECT Customers.contact_name, Orders.order_id
FROM Customers
FULL OUTER JOIN Orders ON Customers.customer_id = Orders.customer_id;

CROSS JOIN

Produces a Cartesian product of the two tables - returns all records where each row from the first table is combined with each row from the second table.

SELECT Customers.contact_name, Products.product_name
FROM Customers
CROSS JOIN Products;

Download the pdf for future reference.

This newsletter provides understanding SQL and its various aspects, such as schemas, keys, table operations, and joins, is crucial for effective database management and data analysis.

By exploring these concepts with practical examples from the Northwind database, we gain a comprehensive view of how to structure, relate, and manipulate data efficiently.

As we navigate through the intricacies of SQL, from establishing relationships with foreign keys to combining data with different types of joins, we equip ourselves with the tools necessary to unlock the full potential of relational databases.

This knowledge forms the backbone of effective data handling and paves the way for more advanced database and data science endeavors.. Keep an eye out for more deep dives into each topic in our upcoming issues! 🚀