SQL Explained: Learn the Language of Databases from Scratch

Introduction

Structured Query Language (SQL) is the standard language for managing and manipulating relational databases. SQL is crucial for anyone working with databases, whether as a beginner or a pro. In this guide, we will cover all SQL commands, including DDL (Data Definition Language), DML (Data Manipulation Language), DQL (Data Query Language), and TCL (Transaction Control Language). Each section comes with simple explanations and examples to help you understand and use SQL effectively.

---

🌐 What is SQL?

SQL is a standard language used to interact with relational databases. It allows users to create, read, update, and delete data stored in a database. SQL consists of several categories of commands:

1. DDL (Data Definition Language) - Defines and modifies database structures.
2. DML (Data Manipulation Language) - Manipulates the data in the tables.
3. DQL (Data Query Language) - Retrieves data from the database.
4. TCL (Transaction Control Language) - Manages transactions within the database.

---

🔧 SQL Commands Overview

1. DDL - Data Definition Language

DDL commands define the database schema. These include commands like CREATE, ALTER, DROP, and TRUNCATE.

Examples:

• CREATE: Creates a new table.

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

• ALTER: Modifies an existing table.

  ALTER TABLE employees ADD COLUMN salary INT;
  

• DROP: Deletes a table or a database.

  DROP TABLE employees;
  

• TRUNCATE: Removes all rows from a table but keeps the structure.

  TRUNCATE TABLE employees;
  

2. DML - Data Manipulation Language

DML commands allow for data manipulation within the tables. Common DML commands include INSERT, UPDATE, and DELETE.

Examples:

• INSERT: Adds new records to a table.

  INSERT INTO employees (id, name, position, salary)
  VALUES (1, 'John Doe', 'Manager', 50000);
  

• UPDATE: Modifies existing records.

  UPDATE employees
  SET salary = 55000
  WHERE id = 1;
  

• DELETE: Deletes records from a table.

  DELETE FROM employees WHERE id = 1;
  

3. DQL - Data Query Language

DQL consists of the SELECT statement, which retrieves data from the database.

Example:

• SELECT: Retrieves data from one or more tables.

  SELECT name, position FROM employees WHERE salary > 30000;
  

4. TCL - Transaction Control Language

TCL commands manage database transactions and include COMMIT, ROLLBACK, and SAVEPOINT.

Examples:

• COMMIT: Saves all changes made in the transaction.

  COMMIT;
  

• ROLLBACK: Undoes the changes made in the current transaction.

  ROLLBACK;
  

• SAVEPOINT: Creates a point within a transaction to which you can later roll back.

  SAVEPOINT save1;
  

---

🗂️ SQL Data Types

SQL supports several data types, which can be grouped into the following categories:

1. Numeric Types: Stores numerical values.
   • INT: Integer values.
   • FLOAT: Floating-point numbers.

   Example:

   CREATE TABLE products (
     product_id INT,
     price FLOAT
   );
   

2. String Types: Stores text.
   • VARCHAR(n): Variable-length strings.
   • CHAR(n): Fixed-length strings.

   Example:

   CREATE TABLE customers (
     customer_id INT,
     name VARCHAR(50)
   );
   

3. Date and Time Types: Stores date and time values.
   • DATE: For dates in the format YYYY-MM-DD.
   • TIMESTAMP: For date and time values.

   Example:

   CREATE TABLE orders (
     order_id INT,
     order_date DATE
   );
   

---

📊 SQL Constraints

SQL constraints are used to define rules for the data in a table. These constraints include:

1. PRIMARY KEY: Ensures each record in a table is unique.

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

2. FOREIGN KEY: Ensures referential integrity between two tables.

   CREATE TABLE orders (
     order_id INT PRIMARY KEY,
     customer_id INT,
     FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
   );
   

3. NOT NULL: Ensures that a column cannot have a NULL value.

   CREATE TABLE products (
     product_id INT,
     product_name VARCHAR(50) NOT NULL
   );
   

4. UNIQUE: Ensures all values in a column are unique.

   CREATE TABLE employees (
     id INT UNIQUE,
     email VARCHAR(100) UNIQUE
   );
   

5. CHECK: Ensures the values in a column meet a specific condition.

   CREATE TABLE employees (
     id INT,
     salary INT,
     CHECK (salary > 0)
   );
   

---

🛠️ Advanced SQL Topics

Once you’re comfortable with the basics, here are a few advanced SQL concepts to explore:

1. JOINS: Combine rows from two or more tables based on a related column.
   • INNER JOIN: Returns records with matching values in both tables.
   • LEFT JOIN: Returns all records from the left table and matched records from the right.

   Example:

   SELECT orders.order_id, customers.name
   FROM orders
   INNER JOIN customers ON orders.customer_id = customers.customer_id;
   

2. INDEXING: Improves query performance by creating an index on columns.

   CREATE INDEX idx_customer_name ON customers (name);
   

3. VIEWS: Creates virtual tables.

   CREATE VIEW employee_view AS
   SELECT name, position FROM employees WHERE salary > 30000;
   

4. STORED PROCEDURES: Encapsulates a set of SQL statements for reuse.

   CREATE PROCEDURE GetEmployeeDetails()
   BEGIN
     SELECT * FROM employees;
   END;
   

---

🚀 Conclusion

SQL is an essential skill for database management and data manipulation. Whether you’re working with simple queries or managing complex transactions, mastering SQL will empower you to work efficiently with databases. Start with basic queries, practice manipulating data, and explore advanced topics like joins, views, and stored procedures as you progress.

Happy coding!

---