Structured Query Language (SQL)

Structured Query Language (SQL)

SQL (Structured Query Language) is a standardized programming language used for managing and manipulating relational databases. It allows users to query, insert, update, and delete data stored in a relational database system (RDBMS). SQL provides a powerful interface to interact with data, making it the core tool for database administration, querying, and data analysis.

Here’s a detailed explanation of SQL, its components, and key concepts:

---

1. Key Features of SQL

• Declarative Language: SQL is a declarative language, meaning users specify what they want to do with the data, rather than how to do it.
• Data Manipulation: SQL allows you to insert, update, delete, and retrieve data.
• Data Definition: SQL provides commands for defining, modifying, and managing database schema (tables, views, etc.).
• Data Control: SQL includes commands to manage access and permissions to the database.
• Standardized: SQL is standardized by ANSI (American National Standards Institute) and ISO (International Organization for Standardization), though different RDBMS systems may have slight variations (e.g., MySQL, PostgreSQL, Oracle, SQL Server).

---

2. Types of SQL Commands

SQL commands are categorized into several types based on their functionality:

a. Data Query Language (DQL)

• SELECT: The most common SQL command used to query the database and retrieve data from one or more tables.
• Syntax:

  SELECT column1, column2, ... FROM table_name WHERE condition;
  

• Example:

  SELECT Name, Age FROM Students WHERE Major = 'Computer Science';
  

  This query will retrieve the Name and Age of students who are majoring in Computer Science.

b. Data Definition Language (DDL)

• CREATE: Defines a new table, view, index, or database.
• ALTER: Modifies an existing database object, such as a table (e.g., adding or modifying columns).
• DROP: Deletes an existing database object like a table or a view.
• TRUNCATE: Removes all rows from a table but retains the structure for future use.
• Example:

  CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100),
    Age INT,
    Major VARCHAR(50)
  );
  

  This command creates a new table called Students with StudentID, Name, Age, and Major as columns.

c. Data Manipulation Language (DML)

• INSERT INTO: Adds new rows of data into a table.
• UPDATE: Modifies existing data in a table.
• DELETE: Removes data from a table.
• Example:
  • INSERT:

    INSERT INTO Students (StudentID, Name, Age, Major)
    VALUES (1, 'Alice', 20, 'Computer Science');
    

  • UPDATE:

    UPDATE Students
    SET Age = 21
    WHERE StudentID = 1;
    

  • DELETE:

    DELETE FROM Students WHERE StudentID = 1;
    

d. Data Control Language (DCL)

• GRANT: Provides user access privileges to the database (e.g., select, insert, update).
• REVOKE: Removes user access privileges from the database.
• Example:

  GRANT SELECT, INSERT ON Students TO user1;
  REVOKE INSERT ON Students FROM user1;
  

e. Transaction Control Language (TCL)

• COMMIT: Saves all changes made during the current transaction.
• ROLLBACK: Reverts any changes made during the current transaction, undoing them.
• SAVEPOINT: Sets a savepoint within a transaction, allowing a rollback to a specific point.
• Example:

  BEGIN TRANSACTION;
  UPDATE Students SET Age = 21 WHERE StudentID = 2;
  COMMIT;
  

---

3. SQL Clauses and Operators

SQL includes several clauses and operators used to filter, group, and sort data:

a. WHERE Clause

• Used to filter records based on specific conditions.
• Example:

  SELECT * FROM Students WHERE Age > 18;
  

b. ORDER BY Clause

• Sorts the result set in ascending or descending order.
• Example:

  SELECT * FROM Students ORDER BY Name ASC;
  

c. GROUP BY Clause

• Groups rows that have the same values in specified columns into summary rows, often used with aggregate functions (e.g., COUNT, SUM).
• Example:

  SELECT Major, COUNT(*) AS NumberOfStudents FROM Students GROUP BY Major;
  

d. HAVING Clause

• Used to filter records after grouping with GROUP BY.
• Example:

  SELECT Major, COUNT(*) AS NumberOfStudents
  FROM Students
  GROUP BY Major
  HAVING COUNT(*) > 5;
  

e. LIKE Operator

• Used for pattern matching with string values.
• Example:

  SELECT * FROM Students WHERE Name LIKE 'A%';
  

  This will return all students whose names start with the letter 'A'.

f. IN Operator

• Used to specify multiple values in a WHERE clause.
• Example:

  SELECT * FROM Students WHERE Major IN ('Computer Science', 'Physics');
  

g. BETWEEN Operator

• Used to filter the results within a range of values.
• Example:

  SELECT * FROM Students WHERE Age BETWEEN 18 AND 22;
  

h. IS NULL

• Used to check for NULL values.
• Example:

  SELECT * FROM Students WHERE Major IS NULL;
  

---

4. Aggregate Functions

SQL includes several aggregate functions to perform calculations on sets of data:

• COUNT(): Returns the number of rows.
  • Example: SELECT COUNT(*) FROM Students;
• SUM(): Returns the sum of a numeric column.
  • Example: SELECT SUM(Age) FROM Students;
• AVG(): Returns the average value of a numeric column.
  • Example: SELECT AVG(Age) FROM Students;
• MIN(): Returns the smallest value.
  • Example: SELECT MIN(Age) FROM Students;
• MAX(): Returns the largest value.
  • Example: SELECT MAX(Age) FROM Students;

---

5. SQL Joins

Joins are used to combine rows from two or more tables based on a related column.

a. INNER JOIN

• Returns only rows where there is a match in both tables.
• Example:

  SELECT Students.Name, Courses.CourseName
  FROM Students
  INNER JOIN Enrollment ON Students.StudentID = Enrollment.StudentID
  INNER JOIN Courses ON Enrollment.CourseID = Courses.CourseID;
  

b. LEFT JOIN (OUTER JOIN)

• Returns all rows from the left table, and matched rows from the right table. If no match, NULL values are returned for right table columns.
• Example:

  SELECT Students.Name, Courses.CourseName
  FROM Students
  LEFT JOIN Enrollment ON Students.StudentID = Enrollment.StudentID
  LEFT JOIN Courses ON Enrollment.CourseID = Courses.CourseID;
  

c. RIGHT JOIN (OUTER JOIN)

• Returns all rows from the right table, and matched rows from the left table. If no match, NULL values are returned for left table columns.
• Example:

  SELECT Students.Name, Courses.CourseName
  FROM Students
  RIGHT JOIN Enrollment ON Students.StudentID = Enrollment.StudentID
  RIGHT JOIN Courses ON Enrollment.CourseID = Courses.CourseID;
  

d. FULL JOIN (OUTER JOIN)

• Returns all rows when there is a match in one of the tables. NULLs are returned for non-matching rows from both tables.
• Example:

  SELECT Students.Name, Courses.CourseName
  FROM Students
  FULL JOIN Enrollment ON Students.StudentID = Enrollment.StudentID
  FULL JOIN Courses ON Enrollment.CourseID = Courses.CourseID;
  

---

6. Subqueries

A subquery is a query nested inside another query. Subqueries can be used in SELECT, INSERT, UPDATE, and DELETE statements.

• Example of Subquery in SELECT:

  SELECT Name, Age
  FROM Students
  WHERE StudentID IN (SELECT StudentID FROM Enrollment WHERE CourseID = 101);
  

---

7. Conclusion

SQL is an essential language for managing and manipulating data in relational databases. Its powerful commands allow for a wide range of operations, from simple queries to complex data manipulations and transactions. Mastering SQL is crucial for database administration, data analysis, and software development, as it provides the ability to efficiently query, manage, and update data in relational systems.