postgreSQL - CRUD

why postgreSQL?

PostgreSQL is popular because it offers enterprise‑grade reliability, modern features, and zero licensing costs, making it the go‑to database for both small projects and large‑scale systems.

Steps
1.Open your terminal.
2.update package - sudo apt update
3.Install PostgreSQL and contrib packages - sudo apt install postgresql postgresql-contrib
4.Verify installation: - psql --version

Next Steps: Initialize and Use PostgreSQL

1. Switch to the postgres user - sudo -i -u postgres
2. Enter the PostgreSQL shell - psql Now the prompt like postgres=#

3.create database - CREATE DATABASE Employee_DB;
will actually be stored as employee_db.

4.list of database -\l

Drop query: drop database embloyee_db;

!...semicolon is important for each query.

5.Then connect - \c employee_db (Enter to which data we will use)
6.check connect - It is looking like this (embloyee_db=#)
7.create table - CREATE TABLE Employee (
emp_id SERIAL PRIMARY KEY,
name VARCHAR(100),
department_id INT,
salary NUMERIC(10,2)
);

• SERIAL → auto number generate.
• PRIMARY KEY → unique identity.
• VARCHAR → text (length limit).
• INT → whole number.
• NUMERIC(10,2) → decimal number (money, salary).

8.If you want one more column in table we use Alter - ALTER TABLE Employee ADD COLUMN email VARCHAR(100);

9.give Default value for column automatic assign value - ALTER TABLE Employee ADD COLUMN bonus INT DEFAULT 100;

10.Column name changing - ALTER TABLE Employee
RENAME COLUMN department_id TO dept_id;

11.drop column - ALTER TABLE EMPLOYEE DROP COLUMN bonus;
!!..if we drop one column we lose all the columns datas so use it carefully.

12.Table list - \dl

13.Table Strucuture - \d table_name;

14.Table view - SELECT * FROM Employee;

15.Values Inserting - INSERT INTO Employee(name,department_id,salary,email)VALUES('udaya',2,[e-mail],80000,);

16.update - UPDATE Employee
SET department_id = 3
WHERE emp_id = 1;

17.delete ROW - DELETE FROM Employee WHERE emp_id = 1;

Next blog we will discuss about constraints,Relationships(joins),Aggregate Function,Filtering & Sorting,Table alternations.....

see u soon...!!!!