PSQL DAY 2

Here are some essential SQL commands with example syntax:

1. CREATE DATABASE – Creates a new database

CREATE DATABASE mydatabase;

1. USE DATABASE – Selects a database

USE mydatabase;

1. CREATE TABLE – Creates a new table

CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
department VARCHAR(50)
);

1. INSERT INTO – Inserts data into a table

INSERT INTO employees (id, name, age, department)
VALUES (1, 'Alice', 30, 'HR');

1. SELECT – Retrieves data from a table

SELECT * FROM employees;

SELECT name, age FROM employees WHERE department = 'HR';

1. UPDATE – Modifies existing records

UPDATE employees
SET age = 31
WHERE id = 1;

1. DELETE – Deletes records from a table

DELETE FROM employees WHERE id = 1;

1. DROP TABLE – Deletes a table

DROP TABLE employees;

1. 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;

1. JOIN – Combines rows from two or more tables

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department = departments.id;

1. GROUP BY & HAVING – Groups data and filters groups

SELECT department, COUNT() as total_employees
FROM employees
GROUP BY department
HAVING COUNT() > 5;

1. ORDER BY – Sorts results

SELECT * FROM employees ORDER BY age DESC;

1. LIMIT – Restricts the number of results

SELECT * FROM employees LIMIT 5;

1. UNION – Combines results of multiple queries

SELECT name FROM employees
UNION
SELECT name FROM managers;

1. EXISTS – Checks for existence of data

SELECT name FROM employees WHERE EXISTS (
SELECT 1 FROM departments WHERE department_name = 'IT'
);