Sample Table: employees
employee_id first_name last_name department salary
1 John Doe Sales 60000
2 Jane Smith Marketing 75000
3 Alice Johnson Sales 65000
4 Bob Brown HR 70000
5 Charlie Davis IT 80000
SQL Commands with Examples
1. SELECT
Retrieves data from the employees table.
Get all columns:
SELECT * FROM employees;
Get specific columns:
SELECT first_name, last_name FROM employees;
2. INSERT
Adds a new row to the employees table.
Insert a new employee:
INSERT INTO employees (employee_id, first_name, last_name,
department, salary)
VALUES (6, 'Eve', 'White', 'Marketing', 72000);
3. UPDATE
Modifies existing data in the employees table.
Update salary for an employee:
UPDATE employees
SET salary = 68000
WHERE employee_id = 3;
4. DELETE
Removes rows from the employees table.
Delete an employee record:
DELETE FROM employees
WHERE employee_id = 1;
5. CREATE TABLE
Creates a new table.
Create a new departments table:
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50)
);
6. ALTER TABLE
Modifies the structure of an existing table.
Add a new column to employees:
ALTER TABLE employees
ADD hire_date DATE;
7. DROP TABLE
Deletes a table and all of its data.
Drop the departments table:
DROP TABLE departments;
8. GRANT
Gives a user permission to perform actions.
Grant SELECT permission to a user:
GRANT SELECT ON employees TO user_name;
9. REVOKE
Removes permissions from a user.
Revoke SELECT permission from a user:
REVOKE SELECT ON employees FROM user_name;
10. COMMIT and ROLLBACK
Manage transactions.
Start a transaction, update, and commit:
BEGIN;
UPDATE employees SET salary = 70000 WHERE employee_id = 2;
COMMIT;
Rollback if something goes wrong:
BEGIN;
UPDATE employees SET salary = 70000 WHERE employee_id = 2;
ROLLBACK; -- This undoes the update if executed.
Additional Clauses
11. WHERE
Filters results based on a condition.
Get employees in the Sales department:
SELECT * FROM employees WHERE department = 'Sales';
12. ORDER BY
Sorts the result set.
Get all employees sorted by salary in descending order:
SELECT * FROM employees ORDER BY salary DESC;
13. GROUP BY
Groups rows that have the same values.
Count employees by department:
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;
14. HAVING
Filters groups based on aggregate conditions.
Get departments with more than one employee:
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;