Setting Up Your Database Environment

1. Install PostgreSQL

1. Download and Install:

   • Windows:

     • Download installer from PostgreSQL website
     • Run installer and follow the steps.
   • 
     

     macOS:

     
     

     brew install postgresql
     

     
     
   • 
     

     Linux:

     
     

     sudo apt-get update
     sudo apt-get install postgresql postgresql-contrib
     

     
     

2. Start the PostgreSQL Service

• Windows:

  • Start service from pgAdmin or PostgreSQL Service in Services.msc.
• 
  

  macOS & Linux:

  
  

  sudo service postgresql start
  

  
  

3. Access PostgreSQL Command Line

psql -U postgres

4. Create a New Database and User

-- Create a new database
CREATE DATABASE my_database;

-- Create a new user
CREATE USER my_user WITH ENCRYPTED PASSWORD 'password';

-- Grant privileges to the new user
GRANT ALL PRIVILEGES ON DATABASE my_database TO my_user;

5. Connect to the New Database

psql -U my_user -d my_database

6. Create a Table

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

7. Insert Example Data

INSERT INTO users (name, email) VALUES
('John Doe', 'john@example.com'),
('Jane Smith', 'jane@example.com');

8. Verify Data

SELECT * FROM users;

Execute the above steps to set up and verify your database environment.

-- Create table Employees
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    BirthDate DATE,
    City VARCHAR(50)
);

-- Insert data into Employees table
INSERT INTO Employees (EmployeeID, FirstName, LastName, BirthDate, City)
VALUES (1, 'John', 'Doe', '1985-05-15', 'New York');

INSERT INTO Employees (EmployeeID, FirstName, LastName, BirthDate, City)
VALUES (2, 'Jane', 'Smith', '1990-08-20', 'Los Angeles');

INSERT INTO Employees (EmployeeID, FirstName, LastName, BirthDate, City)
VALUES (3, 'Sam', 'Brown', '1975-01-30', 'Chicago');

-- Create table Departments
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50),
    Location VARCHAR(50)
);

-- Insert data into Departments table
INSERT INTO Departments (DepartmentID, DepartmentName, Location)
VALUES (1, 'HR', 'New York');

INSERT INTO Departments (DepartmentID, DepartmentName, Location)
VALUES (2, 'Engineering', 'San Francisco');

INSERT INTO Departments (DepartmentID, DepartmentName, Location)
VALUES (3, 'Sales', 'Chicago');

-- Create table Projects
CREATE TABLE Projects (
    ProjectID INT PRIMARY KEY,
    ProjectName VARCHAR(50),
    StartDate DATE,
    EndDate DATE
);

-- Insert data into Projects table
INSERT INTO Projects (ProjectID, ProjectName, StartDate, EndDate)
VALUES (101, 'Project Alpha', '2022-01-01', '2022-06-30');

INSERT INTO Projects (ProjectID, ProjectName, StartDate, EndDate)
VALUES (102, 'Project Beta', '2022-02-01', '2022-12-31');

INSERT INTO Projects (ProjectID, ProjectName, StartDate, EndDate)
VALUES (103, 'Project Gamma', '2022-03-01', '2023-03-31');

Updating Existing Records with UPDATE Statements

SQL Command for Updating Records

To update existing records in a table, you can use the UPDATE statement. Below are a few practical examples:

Example 1: Update a Single Column

UPDATE employees
SET salary = 60000
WHERE employee_id = 101;

Example 2: Update Multiple Columns

UPDATE employees
SET salary = 65000, department = 'Marketing'
WHERE employee_id = 102;

Example 3: Conditional Update

UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Sales' AND years_of_experience > 5;

Example 4: Update with a Subquery

UPDATE employees
SET salary = (SELECT MAX(salary) FROM employees WHERE department = 'HR')
WHERE department = 'HR' AND employee_id = 103;

Verifying Updates

After running an update statement, it’s important to verify the changes. Use the SELECT statement to check the relevant records:

SELECT * FROM employees WHERE employee_id IN (101, 102, 103);

This ensures that the updates applied as expected.

Removing Data with DELETE Statements

SQL DELETE Statement Usage

Below are practical implementations of the SQL DELETE statement to remove data from a database.

Delete All Records from a Table

DELETE FROM table_name;

Delete Specific Record Based on a Condition

DELETE FROM table_name
WHERE condition;

Delete Multiple Records Based on a Condition

DELETE FROM table_name
WHERE condition1 AND condition2;

Example Usage

Delete a specific user from ‘users’ table by user_id

DELETE FROM users
WHERE user_id = 123;

Delete all inactive users from ‘users’ table

DELETE FROM users
WHERE status = 'inactive';

Delete all orders from ‘orders’ table before a certain date

DELETE FROM orders
WHERE order_date < '2023-01-01';

Notes

• Always backup your database or ensure you have a restore point before performing delete operations.
• After executing a DELETE statement, verify the changes by querying the affected table(s).

Combining SQL Commands for Advanced Manipulation

Combining Queries using UNION and UNION ALL

-- UNION: Combines the result of two or more SELECT statements, removing duplicates.
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

-- UNION ALL: Combines the result of two or more SELECT statements, including duplicates.
SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;

Using Subqueries

-- Subquery within a SELECT statement.
SELECT column1, (SELECT COUNT(*) FROM table2 WHERE table2.foreign_key = table1.id) AS row_count
FROM table1;

-- Subquery within a WHERE clause.
SELECT column1, column2
FROM table1
WHERE column3 IN (SELECT column3 FROM table2 WHERE condition);

Joins and Nesting Joins

-- Inner Join
SELECT a.column1, b.column2
FROM table1 a
INNER JOIN table2 b ON a.id = b.foreign_key;

-- Left Join
SELECT a.column1, b.column2
FROM table1 a
LEFT JOIN table2 b ON a.id = b.foreign_key;

-- Nested Joins
SELECT a.column1, b.column2, c.column3
FROM table1 a
INNER JOIN table2 b ON a.id = b.foreign_key
INNER JOIN table3 c ON b.id = c.foreign_key;

Using CASE Statements

-- CASE statement within a SELECT clause.
SELECT column1,
CASE
    WHEN condition1 THEN 'Result1'
    WHEN condition2 THEN 'Result2'
    ELSE 'Result3'
END AS result_column
FROM table1;

Complex Aggregation with GROUP BY and HAVING

-- Aggregation with GROUP BY
SELECT column1, COUNT(*), AVG(column2)
FROM table1
GROUP BY column1;

-- Using HAVING for filtering grouped data
SELECT column1, COUNT(*)
FROM table1
GROUP BY column1
HAVING COUNT(*) > 5;

Transactions

-- Using transactions for atomic operations.
BEGIN TRANSACTION;

UPDATE table1
SET column1 = 'new_value'
WHERE condition;

INSERT INTO table2 (column1, column2)
VALUES ('value1', 'value2');

COMMIT;

-- In case of error, rollback the transaction
BEGIN TRANSACTION;

-- Operations...

ROLLBACK;

Combined Example

-- Combining multiple operations in one advanced query.
BEGIN TRANSACTION;

-- Insert new data.
INSERT INTO orders (customer_id, order_date, total)
VALUES (1, '2023-10-05', 100.00);

-- Update related stock based on inserted data.
UPDATE stock
SET quantity = quantity - 1
WHERE product_id = 1;

-- Select and combine related data into a single result.
SELECT o.order_id, o.total, c.name, s.quantity
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN stock s ON s.product_id = 1
WHERE o.order_date = '2023-10-05';

COMMIT;

You can directly apply these commands and examples to your current SQL-based database to effectively manage and manipulate your data.