PSQL DAY 2
Here are some essential SQL commands with example syntax: CREATE DATABASE – Creates a new database CREATE DATABASE mydatabase; USE DATABASE – Selects a database USE mydatabase; CREATE TABLE – Creates a new table CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50), age INT, department VARCHAR(50) ); INSERT INTO – Inserts data into a table INSERT INTO employees (id, name, age, department) VALUES (1, 'Alice', 30, 'HR'); SELECT – Retrieves data from a table SELECT * FROM employees; SELECT name, age FROM employees WHERE department = 'HR'; UPDATE – Modifies existing records UPDATE employees SET age = 31 WHERE id = 1; DELETE – Deletes records from a table DELETE FROM employees WHERE id = 1; DROP TABLE – Deletes a table DROP TABLE employees; ALTER TABLE – Modifies an existing table Add a column: ALTER TABLE employees ADD salary DECIMAL(10,2); Modify a column: ALTER TABLE employees MODIFY COLUMN age SMALLINT; Drop a column: ALTER TABLE employees DROP COLUMN salary; JOIN – Combines rows from two or more tables SELECT employees.name, departments.department_name FROM employees INNER JOIN departments ON employees.department = departments.id; GROUP BY & HAVING – Groups data and filters groups SELECT department, COUNT() as total_employees FROM employees GROUP BY department HAVING COUNT() > 5; ORDER BY – Sorts results SELECT * FROM employees ORDER BY age DESC; LIMIT – Restricts the number of results SELECT * FROM employees LIMIT 5; UNION – Combines results of multiple queries SELECT name FROM employees UNION SELECT name FROM managers; EXISTS – Checks for existence of data SELECT name FROM employees WHERE EXISTS ( SELECT 1 FROM departments WHERE department_name = 'IT' );

Here are some essential SQL commands with example syntax:
- CREATE DATABASE – Creates a new database
CREATE DATABASE mydatabase;
- USE DATABASE – Selects a database
USE mydatabase;
- CREATE TABLE – Creates a new table
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
department VARCHAR(50)
);
- INSERT INTO – Inserts data into a table
INSERT INTO employees (id, name, age, department)
VALUES (1, 'Alice', 30, 'HR');
- SELECT – Retrieves data from a table
SELECT * FROM employees;
SELECT name, age FROM employees WHERE department = 'HR';
- UPDATE – Modifies existing records
UPDATE employees
SET age = 31
WHERE id = 1;
- DELETE – Deletes records from a table
DELETE FROM employees WHERE id = 1;
- DROP TABLE – Deletes a table
DROP TABLE employees;
-
ALTER TABLE – Modifies an existing table
Add a column:
ALTER TABLE employees ADD salary DECIMAL(10,2);
Modify a column:
ALTER TABLE employees MODIFY COLUMN age SMALLINT;
Drop a column:
ALTER TABLE employees DROP COLUMN salary;
- JOIN – Combines rows from two or more tables
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department = departments.id;
- GROUP BY & HAVING – Groups data and filters groups
SELECT department, COUNT() as total_employees
FROM employees
GROUP BY department
HAVING COUNT() > 5;
- ORDER BY – Sorts results
SELECT * FROM employees ORDER BY age DESC;
- LIMIT – Restricts the number of results
SELECT * FROM employees LIMIT 5;
- UNION – Combines results of multiple queries
SELECT name FROM employees
UNION
SELECT name FROM managers;
- EXISTS – Checks for existence of data
SELECT name FROM employees WHERE EXISTS (
SELECT 1 FROM departments WHERE department_name = 'IT'
);