SQL Commands Overview

SQL Commands Overview

• SELECT: Retrieves data from a database.

  • Syntax: SELECT column1, column2 FROM table_name;
  • Example: SELECT first_name, last_name FROM customers;

• INSERT: Adds new records to a table.

  • Syntax: INSERT INTO table_name (column1, column2) VALUES (value1, value2);
  • Example: INSERT INTO customers (first_name, last_name) VALUES ('Mary', 'Doe');

• UPDATE: Modifies existing records in a table.

  • Syntax: UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
  • Example: UPDATE employees SET employee_name = 'John Doe', department = 'Marketing';

• DELETE: Removes records from a table.

  • Syntax: DELETE FROM table_name WHERE condition;
  • Example: DELETE FROM employees WHERE employee_name = 'John Doe';

Database Structure Commands

• CREATE: Creates a new database or objects (e.g., tables, indices).

  • Syntax: CREATE TABLE table_name (column1 datatype1, column2 datatype2);
  • Example:

    CREATE TABLE employees (
        employee_id INT PRIMARY KEY,
        first_name VARCHAR(50),
        last_name VARCHAR(50),
        age INT
    );
    

• DROP: Deletes an existing table from the database.

  • Syntax: DROP TABLE table_name;
  • Example: DROP TABLE customers;

Data Retrieval Clauses

• WHERE Clause: Filters rows based on specified conditions.

  • Syntax: SELECT * FROM table_name WHERE condition;
  • Example: SELECT * FROM customers WHERE age > 30;

• ORDER BY Clause: Sorts the result set in ascending or descending order based on a specified column.

  • Syntax: SELECT * FROM table_name ORDER BY column_name ASC|DESC;
  • Example: SELECT * FROM products ORDER BY price DESC;

Joining Tables

• INNER JOIN: Returns rows with matching values in both tables.
  • Syntax: SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;
  • Example: SELECT * FROM employees INNER JOIN departments ON employees.department_id = departments.id;

Aggregate Functions

• COUNT(): Counts the number of rows or non-null values in a specified column.

  • Syntax: SELECT COUNT(column_name) FROM table_name;
  • Example: SELECT COUNT(age) FROM employees;

• SUM(): Calculates the sum of all values in a specified column.

  • Syntax: SELECT SUM(column_name) FROM table_name;
  • Example: SELECT SUM(revenue) FROM sales;

• AVG(): Calculates the average (mean) of all values in a specified column.

  • Syntax: SELECT AVG(column_name) FROM table_name;
  • Example: SELECT AVG(price) FROM products;

• MIN(): Returns the minimum (lowest) value in a specified column.

  • Syntax: SELECT MIN(column_name) FROM table_name;
  • Example: SELECT MIN(price) FROM products;

• MAX(): Returns the maximum (highest) value in a specified column.

  • Syntax: SELECT MAX(column_name) FROM table_name;
  • Example: SELECT MAX(price) FROM products;