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' );

Mar 18, 2025 - 18:59
 0
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'
);

Image description