SQL Introduction

What is SQL?

SQL stands for Structured Query Language. SQL is the standard language for managing relational databases. It is used to create, read, update, and delete data in databases.

SQL was first developed in the 1970s and has since become the standard language for relational database management systems (RDBMS) like MySQL, PostgreSQL, Oracle, SQL Server, and SQLite.

What Can SQL Do?

SQL can execute queries against a database, retrieve data, insert records, update records, delete records, create new databases, create new tables, create views, set permissions, and much more.

SQL Components

SQL consists of several components:

1. Data Query Language (DQL)

Used to query data from databases. The main command is SELECT.

SELECT * FROM Customers;

2. Data Manipulation Language (DML)

Used to manipulate data in the database. Commands include:

• INSERT - Insert new records
• UPDATE - Update existing records
• DELETE - Delete records

INSERT INTO Customers (name, email) 
VALUES ('John Doe', 'john@example.com');

UPDATE Customers 
SET email = 'newemail@example.com' 
WHERE id = 1;

DELETE FROM Customers WHERE id = 1;

3. Data Definition Language (DDL)

Used to define and modify database structure. Commands include:

• CREATE - Create databases, tables, indexes, views
• ALTER - Modify existing database objects
• DROP - Delete database objects

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

ALTER TABLE Customers ADD phone VARCHAR(20);

DROP TABLE Customers;

4. Data Control Language (DCL)

Used to control access to data. Commands include:

• GRANT - Grant permissions
• REVOKE - Revoke permissions

5. Transaction Control Language (TCL)

Used to manage transactions. Commands include:

• COMMIT - Save changes
• ROLLBACK - Undo changes
• SAVEPOINT - Create savepoint

Database Systems

SQL works with relational database management systems (RDBMS). Some popular SQL database systems include:

• MySQL: Open-source, widely used in web applications
• PostgreSQL: Advanced open-source database with extensive features
• SQL Server: Microsoft's relational database management system
• Oracle: Enterprise-level database system
• SQLite: Lightweight, embedded database
• MariaDB: MySQL fork with additional features

Why Learn SQL?

SQL is essential for:

• Database Administration: Managing and maintaining databases
• Data Analysis: Querying and analyzing data
• Web Development: Working with database-driven applications
• Data Science: Extracting and manipulating data
• Business Intelligence: Generating reports and insights

SQL Syntax Basics

SQL statements are composed of keywords and follow a specific syntax:

SELECT column1, column2 
FROM table_name 
WHERE condition;

Key Points:

• SQL keywords are case-insensitive (SELECT = select = Select)
• SQL statements typically end with a semicolon (;)
• SQL ignores whitespace between keywords
• Strings in SQL are enclosed in single quotes ('text')

SQL in Action

Here's a simple example of SQL working with a database:

-- Create a table
CREATE TABLE Customers (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

-- Insert data
INSERT INTO Customers (id, name, email) 
VALUES (1, 'John Doe', 'john@example.com');

-- Query data
SELECT * FROM Customers;

-- Update data
UPDATE Customers 
SET email = 'newemail@example.com' 
WHERE id = 1;

-- Delete data
DELETE FROM Customers WHERE id = 1;

Next Steps

Now that you understand what SQL is, you're ready to learn about SQL Syntax and start writing your first SQL queries with the SELECT statement.