SQL Introduction

SQL (Structured Query Language) is a programming language used for managing and manipulating relational databases. It is primarily utilized for Data Definition (DDL), Data Manipulation (DML), and Data Querying (DQL). It is widely applied in database systems such as MySQL, PostgreSQL, and Oracle, serving as a core tool for database administrators and developers.

Core SQL Concepts

The core functionalities of SQL include:

Data Definition Language (DDL): Used to create, modify, or delete database objects, such as CREATE, ALTER, and DROP.
Data Manipulation Language (DML): Used to insert, update, or delete data, such as INSERT, UPDATE, and DELETE.
Data Query Language (DQL): Used to retrieve data, such as the SELECT statement.

Core SQL Functions

Data Definition (DDL)
- CREATE: Used to create databases, tables, or indexes.
```
CREATE DATABASE school;
CREATE TABLE students (id INT, name VARCHAR(50));
```
- ALTER: Modifies table structure or database attributes.
```
ALTER TABLE students ADD COLUMN age INT;
```
- DROP: Deletes databases, tables, or indexes.
```
DROP TABLE students;
```

Data Manipulation (DML)

INSERT: Adds data to a table.

INSERT INTO students (id, name) VALUES (1, 'Alice');

UPDATE: Modifies existing data in a table.

UPDATE students SET name = 'Bob' WHERE id = 1;

DELETE: Removes specific data from a table.
```
DELETE FROM students WHERE id = 1;  
```

Data Query (DQL)
- SELECT: Retrieves data, supporting filtering, sorting, and limiting based on conditions.
```
SELECT * FROM students WHERE age > 20 ORDER BY name LIMIT 10;
```
- JOIN: Connects multiple tables to retrieve related data.
```
SELECT s.name, p.grade FROM students s JOIN grades p ON s.id = p.student_id;
```

SQL Syntax Structure

SQL statements typically consist of keywords, table names, column names, and values, for example:

SELECT: Used to query data.

SELECT column1, column2 FROM table WHERE condition;

INSERT: Adds new records.

INSERT INTO table (column1, column2) VALUES (value1, value2);

UPDATE: Modifies existing data.

UPDATE table SET column1 = value1 WHERE condition;

DELETE: Removes data.
```
DELETE FROM table WHERE condition;
```

Practical SQL Applications

SQL is extensively used in practical scenarios for:

Data Management: Creating and maintaining database structures.
Data Manipulation: Managing table data through INSERT/UPDATE/DELETE operations.
Data Querying: Retrieving specific information via SELECT statements, such as statistical analysis and conditional filtering.
Database Development: Interacting with database systems to implement Create, Read, Update, and Delete (CRUD) operations on data.

Common SQL Issues

Question 1: How do I create a table containing student information?

Answer: Define the table structure using the CREATE TABLE statement.

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

Question 2: How do I query the names of students older than 20?

Answer: Use a SELECT statement combined with a WHERE clause to filter data.

SELECT name FROM students WHERE age > 20;

Question 3: How do I insert a new record into a table?

Answer: Add data using the INSERT INTO statement.

INSERT INTO students (name, age) VALUES ('Alice', 22);

Question 4: How do I delete a record with a specified ID?

Answer: Remove data using the DELETE FROM statement.

DELETE FROM students WHERE id = 1;

Question 5: How do I modify the value of a specific column in a table?

Answer: Update data using the UPDATE statement.

UPDATE students SET name = 'Bob' WHERE id = 1;