A schema is the structure of a database, defining tables, columns, relationships, and other elements. It organizes data logically.
CREATE SCHEMA Company;
CREATE TABLE Company.Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
DepartmentID INT,
FOREIGN KEY (DepartmentID) REFERENCES Company.Departments(DepartmentID)
);
Normalization is the process of organizing data to reduce redundancy and improve integrity. It involves splitting data into related tables.
-- Example of splitting data into two tables for normalization
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
Name VARCHAR(100),
Email VARCHAR(100)
);
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
Data types define the kind of data that can be stored in a column. Common types include INT, VARCHAR, DATE, and BOOLEAN.
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
Price DECIMAL(10, 2),
Available BOOLEAN DEFAULT TRUE
);
Constraints ensure data integrity. Examples include PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT, NOT NULL, and UNIQUE.
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Age INT CHECK (Age >= 18),
Email VARCHAR(100) UNIQUE
);
Use SQL commands to modify the structure of an existing table, such as adding, renaming, or removing columns.
-- Add a new column
ALTER TABLE Employees ADD COLUMN PhoneNumber VARCHAR(15);
-- Rename a column
ALTER TABLE Employees RENAME COLUMN PhoneNumber TO ContactNumber;
-- Drop a column
ALTER TABLE Employees DROP COLUMN ContactNumber;