Skip to content

readme.md

Latest commit

 

History

History
320 lines (238 loc) · 8.42 KB

readme.md

File metadata and controls

320 lines (238 loc) · 8.42 KB

PostgreSQL Database Management Commands

This repository contains a collection of PostgreSQL commands for various database management tasks.

Introduction

This repository provides a set of PostgreSQL commands and examples for managing databases, tables, and data. Each section covers a specific aspect of database management, and the commands are presented with explanations for better understanding.

Grant Permissions

Grant the necessary permissions to users for performing specific actions within the database.

  • Grant the "CONNECT" permission to the "test" database for the "postgres" user: 
    GRANT CONNECT ON DATABASE test TO postgres;
  • Grant the permission to create databases to the "postgres" user: 
    ALTER USER postgres CREATEDB;

Database Operations

  • Create a new database named "test2": 
    CREATE DATABASE test2;
  • Rename the database "test2" to "test3": 
    ALTER DATABASE test2 RENAME TO test3;
  • Delete the "test3" database: 
    DROP DATABASE test3;

Table Operations

  • Create a "student" table with columns for student information: 
    CREATE TABLE student (
  student_id INT,
  first_name VARCHAR(15),
  last_name VARCHAR(15),
  cgpa NUMERIC(1, 2) );
  • Rename the "student" table to "learner": 
    ALTER TABLE student RENAME TO learner;
  • Delete the "learner" table: 
    DROP TABLE learner;

Table Operations

  • Create a "users" table with columns for user information and constraints:

    CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(50) NOT NULL,
  age INT DEFAULT 18,
  UNIQUE(username, email));

  • Remove all data from the "users" table while keeping its structure:

    TRUNCATE TABLE users;

  • Insert user data into the "users" table using various methods:

  • Method 1:

    INSERT INTO users(user_id, username, email, age)
VALUES ('sam', 'sam@gmail.com', 21);

  • Method 2:

    INSERT INTO users(username, email, age)
VALUES ('clark', 'clark@gmail.com', 35),
     ('clark2', 'clark2@gmail.com', 35);

  • Method 3:

    INSERT INTO users
VALUES (1, 'persian', 'persian@gmail.com'),
     (2, 'persian2', 'persian2@gmail.com');

  • Add a "password" column to the "users" table with a default value:

    ALTER TABLE users
ADD COLUMN password VARCHAR(255) DEFAULT 'admin123' NOT NULL;

  • Delete the "age" column from the "users" table:

    ALTER TABLE users DROP COLUMN age;

  • Add and modify columns in the "users" table:

    ALTER TABLE users ADD COLUMN demo INT;
ALTER TABLE users ALTER COLUMN demo TYPE TEXT;
ALTER TABLE users ALTER COLUMN country SET DEFAULT 'bangladesh';
ALTER TABLE users ALTER COLUMN demo DROP DEFAULT;

  • Insert a user with specific values into the "users" table:

    INSERT INTO users VALUES(4, 'john1', 'john1@gmail.com');

  • Rename the "demo" column to "country" in the "users" table:

    ALTER TABLE users RENAME COLUMN demo TO country;
ALTER TABLE users ALTER COLUMN country SET NOT NULL;

  • Drop the "NOT NULL" constraint from the "country" column:

    ALTER TABLE users ALTER COLUMN country DROP NOT NULL;

  • Add a unique constraint to the "email" column in the "users" table:

    ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE(email);

  • Delete the unique constraint from the "email" column in the "users" table:

    ALTER TABLE users DROP CONSTRAINT unique_email;

Retrieving Data

  • Retrieve all data from the "users" table: 
    SELECT * FROM users;

Foreign Keys Constraint And Data Integrity

  • Create a table named "Employees" to store employee information.: 
    CREATE TABLE Employees (
    empID SERIAL PRIMARY KEY,
    empName VARCHAR(50) NOT NULL,
    departmentID INT,
    CONSTRAINT fk_constraint_dept FOREIGN KEY (departmentID) REFERENCES Department(deptID)
);

Explanation: Create a table to store employee details with a foreign key constraint.

  • Retrieve Specific table column: 
    SELECT empid, name, email, joining_date, salary, from employees;

SELECT *
FROM employees
WHERE
    name <> 'Eve'
    AND salary > 9000
    OR name = 'Greta'
  • Update Course Data: 
    UPDATE courses
SET
    course_name = 'laravel',
    description = 'super'
    WHERE course_id = 1;

Explanation: Update course data where course_id is 1.

  • Update Multiple Course Data: 
    UPDATE courses
SET
    course_name = 'laravel',
    description = 'super'
    WHERE course_id > 1;

Explanation: Update course data for courses with course_id greater than 1.

  • Delete Course Data: 
    DELETE FROM courses WHERE course_id = 1;

Explanation: Delete course data where course_id is 1.

Employees Table (Continued)

  • Create Employees Table (Conditional): 
    CREATE TABLE IF NOT EXISTS employees (
  empID SERIAL PRIMARY KEY,
  name text NOT NULL,
  email TEXT NOT NULL,
  salary INT not NULL,
  joining_data DATE NOT NULL,
  deptID INT NOT NULL,
  CONSTRAINT fk_deptID FOREIGN KEY (deptID) REFERENCES department(deptID));

Explanation: Create employees table if it doesn't exist.

  • Select Specific Columns from Employees Table: 
    SELECT empid, name, email, joining_date, salary FROM employees;

Explanation: Retrieve specific columns from the employees table.

  • Filtering Rows with Conditions: 
    SELECT * FROM employees WHERE name LIKE '_r%';

SELECT * FROM employees WHERE name LIKE '__r__';

SELECT * FROM employees WHERE name LIKE 'G%a';

SELECT * FROM employees WHERE deptid IS NULL;

Explanation: Retrieve rows based on specified conditions.

  • Sorting and Limiting Results: 
    SELECT * FROM employees ORDER BY name ASC LIMIT 10 OFFSET 2;

Explanation: Retrieve sorted and limited rows from employees table.

  • Retrieving Maximum Salary: 
    SELECT * FROM employees ORDER BY salary DESC LIMIT 1;

Explanation: Retrieve employee with the highest salary.

  • IN, NOT IN, BETWEEN, and LIKE Operators: 
    SELECT * FROM employees WHERE empid NOT IN (2, 3, 5);
SELECT * FROM employees WHERE salary BETWEEN 10000 AND 15000;
SELECT * FROM employees WHERE name LIKE 'A%';

Explanation: Retrieve rows based on various filtering conditions.

Joining Tables

  • Inner Join with Explanation: 
    SELECT *
FROM employees
  INNER JOIN department ON employees.deptid = department.deptid;

Explanation: Retrieve all columns from both tables where the department ID matches in both tables.

  • Left Join: 
    SELECT *
FROM employees
LEFT JOIN department ON department.department_id = employees.department_id;

Explanation: Retrieve all rows from the left table and matching rows from the right table.

  • Right Join: 
    SELECT *
FROM employees
RIGHT JOIN department ON department.department_id = employees.department_id;

Explanation: Retrieve all rows from the right table and matching rows from the left table.

  • Full Outer Join: 
    SELECT *
FROM employees
FULL JOIN department ON department.department_id = employees.department_id;

Explanation: Retrieve all rows when there is a match in either the left or right table.

  • Cross Join: 
    SELECT * FROM employees CROSS JOIN department;

Explanation: Generate all possible combinations of rows between two tables.

Aggregate Functions

Perform calculations on groups of rows and summarize data

  • Aggregate Functions with Explanation: 
    SELECT
  d.name,
  AVG(e.salary),
  SUM(e.salary),
  MAX(e.salary)
FROM employees e
  FULL JOIN department d ON e.dept = d.dept
GROUP BY d.name
HAVING AVG(e.salary) > 60000;

Explanation: Calculate average, sum, and maximum salary for each department, filtering results using the HAVING clause.

  • Aggregate Functions with GROUP BY: 
    SELECT
  d.name,
  SUM(salary),
  AVG(salary),
  MIN(salary),
  COUNT(*)
FROM department d
  FULL JOIN employees e ON e.dept = d.deptid
GROUP BY d.deptid;

Explanation: Group and aggregate salary data by department, calculating sum, average, minimum, and count.

Subqueries

Use subqueries to perform complex queries within other queries

  • Aggregate Functions with GROUP BY: 
    SELECT
  film_id,
  title,
  rental_rate
FROM film
WHERE rental_rate > 2.98;

Explanation: Retrieve film details where the rental rate is greater than 2.98.