SQL Introduction

SQL (Structured Query Language) is a standardized programming language used for managing relational databases and performing various operations on the data within them. SQL is used to query, insert, update, and delete database records. It can also be used to create and modify the structure of database systems and control access to the data.

Basic SQL Concepts

1. Database

A database is a collection of organized data that can be easily accessed, managed, and updated. Databases are managed using a Database Management System (DBMS).

2. Table

A table is a collection of related data entries and consists of columns and rows. Each column in a table represents a different attribute (or field) of the data, and each row represents a single record.

3. SQL Commands

SQL commands can be categorized into several types:

Data Definition Language (DDL)

• CREATE: To create databases and database objects (tables, indexes, views).

  CREATE TABLE table_name (
      column1 datatype,
      column2 datatype,
      ...
  );
  

• ALTER: To modify existing database objects.

  ALTER TABLE table_name
  ADD column_name datatype;
  

• DROP: To delete databases and database objects.

  DROP TABLE table_name;
  

Data Manipulation Language (DML)

• SELECT: To retrieve data from the database.

  SELECT column1, column2
  FROM table_name;
  

• INSERT: To insert new data into the database.

  INSERT INTO table_name (column1, column2, ...)
  VALUES (value1, value2, ...);
  

• UPDATE: To update existing data within the database.

  UPDATE table_name
  SET column1 = value1, column2 = value2
  WHERE condition;
  

• DELETE: To delete data from the database.

  DELETE FROM table_name
  WHERE condition;
  

Data Control Language (DCL)

• GRANT: To give user access privileges to the database.

  GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';
  

• REVOKE: To remove user access privileges.

  REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'host';
  

Basic SQL Examples

1. Creating a Table

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

2. Inserting Data into a Table

INSERT INTO employees (id, first_name, last_name, email)
VALUES (1, 'John', 'Doe', 'john.doe@example.com');

3. Querying Data from a Table

SELECT first_name, last_name
FROM employees
WHERE id = 1;

4. Updating Data in a Table

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

5. Deleting Data from a Table

DELETE FROM employees
WHERE id = 1;

Keys in SQL

• Primary Key: A field (or combination of fields) that uniquely identifies each record in a table.
• Foreign Key: A field (or combination of fields) that establishes a link between data in two tables, enforcing referential integrity.

SQL Joins

• INNER JOIN: Returns records with matching values in both tables.

  SELECT columns
  FROM table1
  INNER JOIN table2
  ON table1.common_field = table2.common_field;
  

• LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table, and the matched records from the right table.

  SELECT columns
  FROM table1
  LEFT JOIN table2
  ON table1.common_field = table2.common_field;
  

• RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table, and the matched records from the left table.

  SELECT columns
  FROM table1
  RIGHT JOIN table2
  ON table1.common_field = table2.common_field;
  

• FULL JOIN (or FULL OUTER JOIN): Returns all records when there is a match in either left or right table.

  SELECT columns
  FROM table1
  FULL JOIN table2
  ON table1.common_field = table2.common_field;
  

SQL is a powerful tool for interacting with relational databases, and mastering it is essential for anyone working in data management, analysis, or related fields.