Introduction to SQL

SQL (Structured Query Language) is a standardized programming language designed for managing and manipulating relational databases. It allows users to perform various operations such as querying data, updating records, and managing database structures. Here’s a detailed introduction to SQL:

What is SQL?

SQL is used for:

• Defining database schemas (DDL).
• Manipulating data within those schemas (DML).
• Controlling access to the data (DCL).
• Ensuring data integrity and managing transactions (TCL).

 

History of SQL

• 1970: Dr. Edgar F. Codd introduced the relational model for databases.
• 1974: SQL was developed by IBM researchers Raymond Boyce and Donald Chamberlin.
• 1979: Oracle released the first commercial SQL-based RDBMS.
• 1986: SQL was standardized by ANSI and ISO.

 

SQL Syntax

SQL syntax is relatively straightforward. Here’s an example of the basic SQL structure:

SELECT column1, column2

FROM table_name

WHERE condition

ORDER BY column1, column2;

 

 

Key SQL Commands

Data Definition Language (DDL)

CREATE: Used to create databases and tables.

CREATE TABLE employees (

    id INT PRIMARY KEY,

    name VARCHAR(100),

    department VARCHAR(100),

    salary DECIMAL(10, 2)

);

 

ALTER: Used to modify an existing database object.

ALTER TABLE employees

ADD COLUMN hire_date DATE;

 

DROP: Used to delete a table or database.

DROP TABLE employee;

 

 

Data Manipulation Language (DML)

SELECT: Used to retrieve data from a database.

SELECT name, department FROM employees WHERE salary > 50000;

 

INSERT: Used to add new records to a table.

INSERT INTO employees (id, name, department, salary)

VALUES (1, 'John Doe', 'Engineering', 75000);

 

UPDATE: Used to modify existing records.

UPDATE employees SET salary = 80000 WHERE id = 1;

 

DELETE: Used to remove records from a table. 

DELETE FROM employees WHERE id = 1;

 

Data Control Language (DCL)

GRANT: Used to provide user access privileges.

GRANT SELECT, INSERT ON employees TO user1;

REVOKE: Used to remove user access privileges.

REVOKE SELECT, INSERT ON employees FROM user1;

 

Transaction Control Language (TCL)

COMMIT: Used to save all transactions to the database.

COMMIT;

ROLLBACK: Used to undo transactions that have not yet been saved.

ROLLBACK;

SAVEPOINT: Sets a savepoint within a transaction.

SAVEPOINT savepoint1;

 

Basic SQL Concepts

Data Types

Different databases support different data types. Common SQL data types include:

INTEGER: Whole numbers.

VARCHAR(size): Variable length string.

CHAR(size): Fixed length string.

DATE: Date values.

DECIMAL(p, s): Exact numeric values.

 

Primary Key

A primary key uniquely identifies each record in a table. It must contain unique values and cannot contain NULL values.

CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(100) );

 

Foreign Key

A foreign key is a field (or collection of fields) in one table that uniquely identifies a row in another table.

CREATE TABLE orders ( order_id INT PRIMARY KEY, employee_id INT, FOREIGN KEY (employee_id) REFERENCES employees(id) );

 

Indexes

Indexes are used to retrieve data from the database more quickly.

CREATE INDEX idx_name ON employees(name);

 

Conclusion

SQL is a powerful tool for managing and manipulating relational databases. Its standardized syntax and broad support across various database systems make it an essential skill for database administrators, developers, and data analysts. This introduction provides a foundation for further exploration and mastery of SQL.