SQL
1. Overview of SQL
Structured Query Language (SQL) is the standard programming language for managing relational databases. It allows users to create, read, update, and delete data. SQL provides a uniform interface for interacting with different database management systems such as PostgreSQL and MySQL.
Data Definition Language (DDL)
DDL commands define the structure of the database. These commands modify the schema rather than the data records.
i. CREATE: This command generates a new table.
Example: CREATE TABLE Students (ID INT PRIMARY KEY, Name VARCHAR(50));
ii. ALTER: This command modifies an existing table structure.
iii. DROP: This command deletes a table and all its data.
Data Manipulation Language (DML)
DML commands manage the data within the tables. These are the most frequent operations in daily database management.
i. INSERT: Adds new rows to a table.
Example: INSERT INTO Students (ID, Name) VALUES (1, 'John Smith');
ii. UPDATE: Modifies existing data in a table.
iii. DELETE: Removes specific rows from a table.
Data Query Language (DQL)
DQL consists of the SELECT statement. It retrieves specific data from one or more tables.
Syntax: SELECT column_name FROM table_name WHERE condition;
Usage: A bank uses DQL to display an account balance.
Example: SELECT Balance FROM Accounts WHERE AccountNumber = 101;
Practical Implementation Steps
To manage a database effectively, follow a standard sequence of operations.
- Design: Identify the necessary entities and their attributes.
- Creation: Use DDL to build the tables and define primary keys.
- Population: Use DML to import or insert initial data sets.
- Maintenance: Run updates and backups to ensure data integrity and protection.
Constraints in SQL
SQL enforces the integrity rules mentioned in Week 1 through specific keywords.
PRIMARY KEY: Ensures each row is unique.
NOT NULL: Prevents a column from being empty.
FOREIGN KEY: Links a record in one table to a record in another, maintaining referential integrity.