SQL Commands Overview

Study Notes

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;

Description

This quiz covers essential SQL commands such as SELECT, INSERT, and UPDATE. Each command is briefly described, along with syntax examples to illustrate their use in database management. Test your understanding of these fundamental SQL operations.