SQL queries: basic commands for database management

SQL Queries: Core Commands for Database Management

SQL (Structured Query Language) is the standard language for interacting with relational database management systems (RDBMS). Understanding SQL commands is essential for managing data, retrieving information, and maintaining the integrity of a database. This document outlines the fundamental SQL commands with detailed explanations and practical examples.

1. Data Definition Language (DDL): Defining the Database Structure

DDL commands are used to define and manage the structure of the database itself. This includes creating, altering, and deleting database objects like tables, indexes, and views.

• CREATE: Used to create new database objects.
  • CREATE DATABASE database_name; Creates a new database.
    • Example: CREATE DATABASE CompanyDB;
  • CREATE TABLE table_name (column1 datatype constraints, column2 datatype constraints, …); Creates a new table.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Department VARCHAR(50),
    Salary DECIMAL(10, 2)
);

• • • This creates a table named Employees with columns for EmployeeID, FirstName, LastName, Department, and Salary, specifying data types and constraints like PRIMARY KEY and NOT NULL.
  • CREATE INDEX index_name ON table_name (column1, column2, …); Creates an index on one or more columns of a table. Indexes improve query performance.
    • Example: CREATE INDEX idx_LastName ON Employees (LastName); This creates an index on the LastName column of the Employees table.
• ALTER: Used to modify existing database objects.
  • ALTER TABLE table_name ADD column_name datatype constraints; Adds a new column to a table.
    • Example: ALTER TABLE Employees ADD HireDate DATE; This adds a HireDate column of type DATE to the Employees table.
  • ALTER TABLE table_name MODIFY COLUMN column_name datatype constraints; Modifies the datatype or constraints of an existing column. (The exact syntax for this can vary slightly between different RDBMS)
    • Example: ALTER TABLE Employees MODIFY COLUMN Salary DECIMAL(12, 2); This modifies the Salary column to allow for larger salary values.
  • ALTER TABLE table_name DROP COLUMN column_name; Removes a column from a table.
    • Example: ALTER TABLE Employees DROP COLUMN Department; This removes the Department column from the Employees table.
• DROP: Used to delete database objects.
  • DROP DATABASE database_name; Deletes an entire database. (Use with extreme caution!)
    • Example: DROP DATABASE CompanyDB;
  • DROP TABLE table_name; Deletes a table and all its data. (Irreversible!)
    • Example: DROP TABLE Employees;
  • DROP INDEX index_name ON table_name; Deletes an index.
    • Example: DROP INDEX idx_LastName ON Employees;
• TRUNCATE TABLE table_name; Removes all rows from a table, but preserves the table structure. Faster than DELETE and resets the auto-increment counter (if applicable).
  • Example: TRUNCATE TABLE Employees;

2. Data Manipulation Language (DML): Managing Data Within Tables

DML commands are used to manipulate the data within the database tables. This includes inserting, updating, deleting, and retrieving data.

• INSERT: Used to insert new data into a table.
  • INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …); Inserts a single row of data. The column list is optional, but highly recommended for clarity and maintainability. If the column list is omitted, you must provide values for all columns in the table in the order they are defined.
    • Example: INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary) VALUES (1, 'John', 'Doe', 'Sales', 50000.00);
  • INSERT INTO table_name VALUES (value1, value2, …); (Less explicit version of the above; use with caution)
    • Example: INSERT INTO Employees VALUES (2, 'Jane', 'Smith', 'Marketing', 60000.00); (Only works if you provide values for all columns in the correct order.)
  • INSERT INTO table_name (column1, column2, …) SELECT columnA, columnB, … FROM some_other_table WHERE condition; Inserts data from one table into another.
    • Example: INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary) SELECT EmployeeID, FirstName, LastName, 'New Department', Salary FROM OldEmployees WHERE Department = 'Old Department'; This inserts employees from the OldEmployees table into the Employees table, but only those from the ‘Old Department’, changing the department to ‘New Department’ in the process.
• UPDATE: Used to modify existing data in a table.
  • UPDATE table_name SET column1 = value1, column2 = value2, … WHERE condition; Updates rows that match the specified condition. Always include a WHERE clause to avoid unintentionally updating every row in the table.
    • Example: UPDATE Employees SET Salary = 55000.00 WHERE EmployeeID = 1; This updates the salary of the employee with EmployeeID 1 to 55000.00.
      • Example: UPDATE Employees SET Salary = Salary * 1.10 WHERE Department = 'Sales'; This gives all employees in the Sales department a 10% raise.
• DELETE: Used to delete rows from a table.
  • DELETE FROM table_name WHERE condition; Deletes rows that match the specified condition. Always include a WHERE clause to avoid deleting all rows from the table.
    • Example: DELETE FROM Employees WHERE EmployeeID = 1; This deletes the employee with EmployeeID 1.
    • Example: DELETE FROM Employees WHERE Department = 'Finance' AND Salary < 40000; This deletes all employees in the Finance department who earn less than $40,000.
• SELECT: Used to retrieve data from a table. This is the most frequently used SQL command.
  • SELECT * FROM table_name; Retrieves all columns and all rows from the table. Avoid using * in production code; it’s better to explicitly specify the columns you need.
    • Example: SELECT * FROM Employees;
  • SELECT column1, column2, … FROM table_name; Retrieves specific columns from the table.
    • Example: SELECT FirstName, LastName, Salary FROM Employees;
  • SELECT column1, column2, … FROM table_name WHERE condition; Retrieves specific columns from the table, filtering the rows based on the specified condition.
    • Example: SELECT FirstName, LastName FROM Employees WHERE Department = 'Sales';
  • SELECT column1, column2, … FROM table_name ORDER BY column1 ASC/DESC; Retrieves specific columns, filtering rows and sorting the results. ASC is ascending order (default), DESC is descending order.
    • Example: SELECT FirstName, LastName, Salary FROM Employees WHERE Department = 'Sales' ORDER BY Salary DESC; This retrieves the first name, last name, and salary of all employees in the Sales department, sorted by salary in descending order (highest salary first).
  • SELECT DISTINCT column_name FROM table_name; Retrieves unique values from a specified column.
    • Example: SELECT DISTINCT Department FROM Employees; This retrieves a list of all unique department names from the Employees table.
  • SELECT COUNT(*) FROM table_name; Counts the total number of rows in a table.
    • Example: SELECT COUNT(*) FROM Employees; This returns the total number of employees in the Employees table.
  • SELECT AVG(column_name), SUM(column_name), MIN(column_name), MAX(column_name) FROM table_name; Calculates the average, sum, minimum, and maximum values of a specified column.
    • Example: SELECT AVG(Salary), SUM(Salary), MIN(Salary), MAX(Salary) FROM Employees; This returns the average, sum, minimum, and maximum salaries of all employees.
  • SELECT column1, COUNT(*) FROM table_name GROUP BY column1; Groups rows based on the values in a column and performs aggregate functions on each group.
    • Example: SELECT Department, COUNT(*) FROM Employees GROUP BY Department; This returns the number of employees in each department.
  • SELECT column1, COUNT() FROM table_name GROUP BY column1 HAVING COUNT() > 10; Groups rows and filters the groups based on a condition, often used with aggregate functions.
    • Example: SELECT Department, COUNT(*) FROM Employees GROUP BY Department HAVING COUNT(*) > 5; This returns only those departments that have more than 5 employees.

3. Data Control Language (DCL): Controlling Access to Data

DCL commands are used to control access to the database and its objects. These commands are often used by database administrators.

• GRANT: Grants permissions to users or roles.
  • GRANT permission ON database_object TO user/role; Grants a specific permission (e.g., SELECT, INSERT, UPDATE, DELETE) on a database object (e.g., table, view) to a user or role.
    • Example: GRANT SELECT ON Employees TO 'user1'@'localhost'; This grants the SELECT permission on the Employees table to the user user1 from the localhost.
• REVOKE: Revokes permissions from users or roles.
  • REVOKE permission ON database_object FROM user/role; Revokes a specific permission on a database object from a user or role.
    • Example: REVOKE SELECT ON Employees FROM 'user1'@'localhost'; This revokes the SELECT permission on the Employees table from the user user1 from the localhost.

4. Transaction Control Language (TCL): Managing Transactions

TCL commands are used to manage transactions, which are logical units of work that consist of one or more SQL statements. Transactions ensure data consistency and integrity.

• COMMIT: Saves all changes made during a transaction.

START TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 123;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 456;
COMMIT; -- Makes these changes permanent.

ROLLBACK: Undoes all changes made during a transaction, reverting the database to its previous state.

START TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 123;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 456;
ROLLBACK; -- Cancels both updates, reverting the balance to its original values.

SAVEPOINT savepoint_name: Creates a point within a transaction to which you can rollback.

    START TRANSACTION;
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 123;
    SAVEPOINT sp1;
    UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 456;
    ROLLBACK TO sp1; -- Rolls back only the second UPDATE statement. The first update remains.
    COMMIT;

Joins: Combining Data from Multiple Tables

Joins are essential for retrieving data from multiple related tables.

• INNER JOIN: Returns rows only when there is a match in both tables.
  • SELECT column1, column2, … FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table, and the matching rows from the right table. If there is no match, the right table’s columns will contain NULL values.

• SELECT column1, column2, … FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table, and the matching rows from the left table. If there is no match, the left table’s columns will contain NULL values.

• SELECT column1, column2, … FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;

   SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
   FROM Employees
   RIGHT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

• • This retrieves all departments and their employees. If a department has no employees, the FirstName and LastName will be NULL.
• FULL OUTER JOIN: Returns all rows from both tables. If there is no match, the columns from the non-matching table will contain NULL values.
  • SELECT column1, column2, … FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name; *Example: sql SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName FROM Employees FULL OUTER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID; This retrieves all employees and all departments, regardless of whether they have a match.

Subqueries: Queries Within Queries

A subquery is a query nested inside another query. Subqueries can be used in the SELECT, FROM, and WHERE clauses.

• Subquery in the WHERE clause:
  • SELECT column1, column2, … FROM table_name WHERE column_name IN (SELECT column_name FROM another_table WHERE condition);
    • Example: SELECT FirstName, LastName FROM Employees WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Location = 'New York'); This retrieves employees who work in departments located in New York.
• Subquery in the SELECT clause: (Use with caution, can be inefficient)
  • SELECT column1, (SELECT COUNT(*) FROM another_table WHERE condition) AS alias_name FROM table_name;
    • Example: SELECT FirstName, LastName, (SELECT AVG(Salary) FROM Employees) AS AverageSalary FROM Employees; This retrieves the first name, last name, and the average salary of all employees for each employee row.
• Subquery in the FROM clause: (Used to treat the result set of a query as a table)
  • SELECT column1, column2, … FROM (SELECT columnA, columnB FROM table_name WHERE condition) AS alias_name WHERE condition;
    • Example: SELECT Department, AVG(Salary) FROM (SELECT Department, Salary FROM Employees WHERE Salary > 50000) AS HighEarners GROUP BY Department; This finds the average salary for each department, considering only employees who earn more than $50,000.

Other Important SQL Features:

• Aliases: Using AS to rename columns or tables for brevity and clarity.
• CASE statements: Used to perform conditional logic within a query.
• String functions: Functions for manipulating text data (e.g., UPPER(), LOWER(), SUBSTRING(), CONCAT()).
• Date functions: Functions for working with dates and times (e.g., NOW(), DATE(), YEAR(), MONTH(), DAY()).
• Window functions: Functions that perform calculations across a set of table rows that are related to the current row (e.g., ROW_NUMBER(), RANK(), LAG(), LEAD()).
• Common Table Expressions (CTEs): Temporary named result sets defined within the execution scope of a single SQL statement (using the WITH clause). CTEs help improve readability and can be used to break down complex queries into smaller, more manageable parts.

This overview covers the core SQL commands and concepts necessary for managing databases effectively. Different database systems (MySQL, PostgreSQL, SQL Server, Oracle, etc.) may have slightly different syntax or additional features, so consulting the specific documentation for your RDBMS is always recommended. Understanding and mastering these commands will allow you to effectively store, retrieve, and manage data.