Understand relational tables
Start with a small sales database containing customers, products, sales and sale items.
Rows and columns
A table stores one kind of entity. A row is one record; a column is one property. Good design avoids repeating the same fact in several places.
One customer can have many sales. One sale can have many sale items. Each sale item points to one product.
| Table | Purpose | Identifier |
|---|---|---|
| Customers | Buyer details | CustomerNo |
| Products | Items and prices | ProductNo |
| Sales | Receipts | ReceiptNo |
| Sales_Items | Products on each receipt | SerialNo |
Products, not repeated in every Sales_Items row.Read the embedded Tables notes
Choose the right keys
Keys identify records and preserve relationships between tables.
Core key types
- Super key: any field combination that uniquely identifies a row.
- Candidate key: a minimal field combination that could be the primary key.
- Primary key: the selected candidate key; unique and not null.
- Foreign key: a field referencing a key in another table.
Composite key
A key may contain multiple columns. In Sales_Items, the pair
(ReceiptNo, ProductNo) can prevent a product from appearing twice on the same receipt.
CREATE TABLE Sales_Items (
ReceiptNo INT,
ProductNo INT,
Quantity INT CHECK (Quantity > 0),
PRIMARY KEY (ReceiptNo, ProductNo),
FOREIGN KEY (ReceiptNo) REFERENCES Sales(ReceiptNo),
FOREIGN KEY (ProductNo) REFERENCES Products(ProductNo)
);Read the embedded Keys in a Database notes
Create tables and constraints
Use Data Definition Language (DDL) to create and change table structures.
CREATE TABLE Marks (
RollNo INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Phy INT NOT NULL CHECK (Phy BETWEEN 0 AND 100),
Chem INT NOT NULL CHECK (Chem BETWEEN 0 AND 100)
);
ALTER TABLE Marks ADD Maths INT;
ALTER TABLE Marks DROP COLUMN Maths;PRIMARY KEY identifies a row, NOT NULL requires a value, UNIQUE
prevents duplicates, CHECK validates a rule, and FOREIGN KEY protects a
relationship.
Products table with a positive price and
a unique product name. Insert one valid and one invalid record.Read the embedded Creating Tables in SQL notes
Combine data with joins
Joins connect related rows; set operators combine compatible query results.
| Operation | Result |
|---|---|
| INNER JOIN | Only matching rows |
| LEFT JOIN | Every left row plus matches |
| RIGHT JOIN | Every right row plus matches |
| FULL OUTER JOIN | All rows from both sides |
| UNION | Combined distinct rows |
| UNION ALL | Combined rows including duplicates |
| INTERSECT | Rows common to both results |
| MINUS | Rows in the first result but not the second (Oracle) |
SELECT s.ReceiptNo, c.CustomerName, s.DateOfSale
FROM Sales s
INNER JOIN Customers c
ON s.CustomerNo = c.CustomerNo;INNER JOIN becomes
LEFT JOIN and a sale has no matching customer?Read the embedded Joins and Set Operations notes
Summarise data with aggregate queries
Aggregate functions calculate one result from several rows.
Five essential functions
MAX(), MIN(), SUM(), AVG() and COUNT().
WHERE filters rows before grouping.
HAVING filters groups after aggregate values are calculated.SELECT Batsman,
MAX(Score) AS HighestScore,
AVG(Score) AS AverageScore
FROM Cricket_Scores
WHERE MatchType = 'Test'
GROUP BY Batsman
HAVING MAX(Score) >= 100;SUM(Quantity * Price) and GROUP BY ReceiptNo.Read the embedded Aggregate Queries in Oracle notes
Sort results with ORDER BY
Sorting is normally the final step in a query.
SELECT RollNo, Name, Phy, Chem, Maths,
(Phy + Chem + Maths) AS Total
FROM Result
ORDER BY Total DESC, Name ASC;ASC means ascending and is the default. DESC means descending. Multiple columns
create tie-break rules from left to right.
Logical query order
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Although SELECT is written
first, SQL logically builds and filters the result before sorting it.