SQL Reference

KEYS

KEYS in SQL are essential components of relational databases. They establish and enforce various types of integrity and help to create relationships between database tables. 1. Primary Key: A primary key uniquely identifies each record in a database table. It must contain unique values and cannot be null. 2. Foreign Key: A foreign key is a field in a table that matches a primary key in another table. It establishes a link between two tables, maintaining referential integrity. 3. Unique Key: A unique key is a set of one or more fields/columns of a table that uniquely identify a record in a table. It allows null values and can provide unique constraints for a column or set of columns. 4. Composite Key: A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in that table when the columns are combined. 5. Candidate Key: A candidate key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple candidate keys in one table. Each candidate can be a primary key. 6. Super Key: A super key is a set of one or more keys that can be used to identify a record uniquely in a table. A super key is a superset of a candidate key.

Example

CREATE TABLE Orders (
    OrderID int,
    CustomerID int,
    OrderNumber int,
    PRIMARY KEY (OrderID)
);

Output

Query OK, 0 rows affected (0.20 sec)

Explanation

The CREATE TABLE statement creates a table named ‘Orders’ with three columns: OrderID, CustomerID, and OrderNumber. The PRIMARY KEY keyword denotes that the OrderID is the primary key of the Orders table. A primary key is a unique identifier for records in the table. It cannot contain NULL values and must contain unique values.

Example

CREATE TABLE Employees (
    ID int NOT NULL,
    Name varchar(255) NOT NULL,
    BirthDate date,
    Position varchar(255),
    Salary decimal(10,2),
    PRIMARY KEY (ID)
);

INSERT INTO Employees
VALUES (1, 'John Doe', '1980-10-01', 'Manager', 45000.00),
       (2, 'Jane Doe', '1982-05-09', 'Developer', 35000.00);

SELECT * FROM Employees;

Output

| ID |  Name   | BirthDate  | Position  | Salary  |
|----|---------|------------|-----------|---------|
| 1  |John Doe | 1980-10-01 | Manager   | 45000.00|
| 2  |Jane Doe | 1982-05-09 | Developer | 35000.00|

Explanation

In the SQL Server example given, an ‘Employees’ table is created with five columns: ‘ID’ (integer), ‘Name’ (string), ‘BirthDate’ (date), ‘Position’ (string), and ‘Salary’ (decimal). A primary key is set on the ‘ID’ column, which means that each entry in the ‘Employees’ table must have a unique ‘ID’. Two records are then inserted into the ‘Employees’ table, and finally, a query selects all records from the ‘Employees’ table and returns them as output.

Example

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    NAME VARCHAR (20),
    AGE INT,
    SALARY DECIMAL (10, 2)
);

INSERT INTO Employees (ID, NAME, AGE, SALARY)
VALUES (1, "Mike", 45, 50000),
       (2, "Hannah", 35, 75000),
       (3, "Sam", 55, 97000);

Output

Query OK, 0 rows affected
Query OK, 3 rows affected

Explanation

The above SQL code is creating an Employees table with various columns: ID, NAME, AGE, and SALARY. The structure of the table includes a Primary Key ID to ensure there are no duplicate, null or conflicting entries. Then, three rows are inserted into this table. The Output demonstrates that the commands executed successfully without affecting any records (as this is a creation operation).