Here's a detailed guide on some basic SQL queries with examples, explanations, and expected
output:
---
## 1. **CREATE TABLE**
- **Purpose**: To create a new table in the database.
- **Syntax**:
```sql
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Age INT
);
```
- **Explanation**: A `Students` table is created with four columns: `StudentID`, `FirstName`,
`LastName`, and `Age`.
- **Output**: Table `Students` is created successfully.
---
## 2. **ALTER TABLE**
- **Purpose**: To modify an existing table structure.
- **Syntax**:
```sql
ALTER TABLE Students ADD Email VARCHAR(100);
```
- **Explanation**: Adds a new column `Email` to the `Students` table.
- **Output**: Table `Students` is modified successfully.
---
## 3. **DROP TABLE**
- **Purpose**: To delete an entire table.
- **Syntax**:
```sql
DROP TABLE Students;
```
- **Explanation**: Deletes the `Students` table.
- **Output**: Table `Students` is dropped successfully.
---
## 4. **INSERT INTO**
- **Purpose**: To insert data into a table.
- **Syntax**:
```sql
INSERT INTO Students (StudentID, FirstName, LastName, Age) VALUES (1, 'John', 'Doe', 20);
```
- **Explanation**: Inserts a new record into the `Students` table.
- **Output**: One row is inserted into the `Students` table.
---
## 5. **UPDATE**
- **Purpose**: To update existing records in a table.
- **Syntax**:
```sql
UPDATE Students SET Age = 21 WHERE StudentID = 1;
```
- **Explanation**: Updates the `Age` of the student with `StudentID = 1` to 21.
- **Output**: One row is updated.
---
## 6. **DELETE**
- **Purpose**: To delete records from a table.
- **Syntax**:
```sql
DELETE FROM Students WHERE StudentID = 1;
```
- **Explanation**: Deletes the record of the student with `StudentID = 1`.
- **Output**: One row is deleted.
---
## 7. **SELECT**
- **Purpose**: To select data from a table.
- **Syntax**:
```sql
SELECT * FROM Students;
```
- **Explanation**: Selects all columns from the `Students` table.
- **Output**: All rows and columns from the `Students` table are displayed.
---
## 8. **WHERE**
- **Purpose**: To filter records.
- **Syntax**:
```sql
SELECT * FROM Students WHERE Age > 18;
```
- **Explanation**: Selects records of students older than 18.
- **Output**: Only students with `Age > 18` are displayed.
---
## 9. **DISTINCT**
- **Purpose**: To select unique values.
- **Syntax**:
```sql
SELECT DISTINCT Age FROM Students;
```
- **Explanation**: Returns unique ages from the `Students` table.
- **Output**: Unique `Age` values are displayed.
---
## 10. **GRANT**
- **Purpose**: To give permissions to a user.
- **Syntax**:
```sql
GRANT SELECT ON Students TO user_name;
```
- **Explanation**: Grants `SELECT` permission on the `Students` table to `user_name`.
- **Output**: Permission granted successfully.
---
## 11. **REVOKE**
- **Purpose**: To take back permissions from a user.
- **Syntax**:
```sql
REVOKE SELECT ON Students FROM user_name;
```
- **Explanation**: Revokes `SELECT` permission from `user_name`.
- **Output**: Permission revoked successfully.
---
## 12. **COMMIT**
- **Purpose**: To save all changes made during a transaction.
- **Syntax**:
```sql
COMMIT;
```
- **Explanation**: Saves all changes to the database.
- **Output**: Changes are committed.
---
## 13. **ROLLBACK**
- **Purpose**: To undo changes made during a transaction.
- **Syntax**:
```sql
ROLLBACK;
```
- **Explanation**: Reverts the database to the last committed state.
- **Output**: Changes are rolled back.
---
## 14. **SAVEPOINT**
- **Purpose**: To set a savepoint within a transaction.
- **Syntax**:
```sql
SAVEPOINT sp1;
```
- **Explanation**: Creates a savepoint named `sp1`.
- **Output**: Savepoint created successfully.
---
## 15. **SQL Operators**
### a) **ALL**
- **Syntax**:
```sql
SELECT * FROM Students WHERE Age > ALL (SELECT Age FROM Students WHERE Age < 18);
```
- **Explanation**: Selects students older than all students younger than 18.
- **Output**: Rows satisfying the condition.
### b) **AND**
- **Syntax**:
```sql
SELECT * FROM Students WHERE Age > 18 AND FirstName = 'John';
```
- **Explanation**: Selects students older than 18 with `FirstName = 'John'`.
- **Output**: Rows satisfying both conditions.
### c) **ANY**
- **Syntax**:
```sql
SELECT * FROM Students WHERE Age < ANY (SELECT Age FROM Students WHERE Age > 20);
```
- **Explanation**: Selects students younger than any student older than 20.
- **Output**: Rows satisfying the condition.
### d) **BETWEEN**
- **Syntax**:
```sql
SELECT * FROM Students WHERE Age BETWEEN 18 AND 25;
```
- **Explanation**: Selects students aged between 18 and 25.
- **Output**: Rows within the specified age range.
---
## 16. **SET Operations**
### a) **UNION**
- **Syntax**:
```sql
SELECT FirstName FROM Students UNION SELECT FirstName FROM Teachers;
```
- **Explanation**: Combines unique first names from `Students` and `Teachers`.
- **Output**: Union of names.
### b) **INTERSECT**
- **Syntax**:
```sql
SELECT FirstName FROM Students INTERSECT SELECT FirstName FROM Teachers;
```
- **Explanation**: Returns common first names from both tables.
- **Output**: Intersection of names.
### c) **MINUS**
- **Syntax**:
```sql
SELECT FirstName FROM Students MINUS SELECT FirstName FROM Teachers;
```
- **Explanation**: Returns names in `Students` not in `Teachers`.
- **Output**: Difference of names.
---
## 17. **JOIN Operations**
### a) **CROSS JOIN**
- **Syntax**:
```sql
SELECT * FROM Students CROSS JOIN Courses;
```
- **Explanation**: Returns the Cartesian product of `Students` and `Courses`.
- **Output**: Every combination of rows from both tables.
### b) **INNER JOIN**
- **Syntax**:
```sql
SELECT * FROM Students INNER JOIN Courses ON Students.StudentID = Courses.StudentID;
```
- **Explanation**: Returns matching rows from both tables.
- **Output**: Matched rows based on `StudentID`.
### c) **NATURAL JOIN**
- **Syntax**:
```sql
SELECT * FROM Students NATURAL JOIN Courses;
```
- **Explanation**: Joins tables based on columns with the same name.
- **Output**: Matched rows based on common columns.
### d) **OUTER JOIN (LEFT/RIGHT/FULL)**
- **LEFT JOIN**:
```sql
SELECT * FROM Students LEFT JOIN Courses ON Students.StudentID = Courses.StudentID;
```
- **Explanation**: Returns all rows from `Students` and matched rows from `Courses`.
- **Output**: All students with matching course details or `NULL` values.
- **RIGHT JOIN**:
```sql
SELECT * FROM Students RIGHT JOIN Courses ON Students.StudentID = Courses.StudentID;
```
- **Explanation**: Returns all rows from `Courses` and matched rows from `Students`.
- **Output**: All courses with matching student details or `NULL` values.
- **FULL JOIN**:
```sql
SELECT * FROM Students FULL JOIN Courses ON Students.StudentID = Courses.StudentID;
```
- **Explanation**: Returns all rows from both tables, matched or not.
- **Output**: Combined data from both tables.
### e) **SELF JOIN**
- **Syntax**:
```sql
SELECT A.StudentID, B.FirstName FROM Students A, Students B WHERE A.StudentID != B.StudentID;
```
- **Explanation**: Joins a table with itself.
- **Output**: Pairs of different students.
---
These examples should cover basic SQL queries and commands, along with expected outputs and
functionality.
