postgresql (dml &ddl)

DATA : information.

Database : is a place where we can store out data.

Types of database:
types:

1. Rdbms: relational database managnment systom.
   • Rdbms : tables format (rows and columns). rdb type: example = mysql , oracle , microsoft server sql.

2) Ordbms = object relational database managnment systom.

      * postgresql  (only open sources)
      * graph database
      * mapping database

first of all what is sql ?
sql : structured query language // (neccessary details)
psql install : 2 type

      i)  cli: comment line interface
      ii)  ui: user interface

psql install step by step linux for (ui) method

       i)    start windows button
       ii)   search for software manager
       iii)  search for postgresql 

cli method install for linux

        i)open terminal
        ii)sudo apt update 
        iii) sudo apt install postgresql 

 iv)  check status = (sudo systemctl status postgresql) 

go to the inside 
          v)   -i -u postgres
          vi)   psql

 corrently  inside  a what data base this postgres 
           i) \l        //it will list all tha data base

            ii)\q        // just exit

              iii) create database student;

               iv) \c student;
      sql:

            1) data reterievel language/query language
            2) data manipilation language
                 * inselt,delete,update
            3) data definition language
                 * create,alter,drop,truncate

drop : drop tha table
truncate : remove tha inner all datas

            4) data control language
                    grant,revoke
            5) data transaction language
                    commit, rollback 



          (i) : create table student ( sno int, name varchar(30),age int,trainer varchar(20),doj date;

         ii) \dt        // (one database inside ,i need see this how many table )

          iii) inselt into student values ( 1, 'ranjith', 25, 'prithivi', '2025-09-25';

           just check this (select * from student; )


            key point 

         i)   sudo -i -u postgres
         ii)  psql
         iii) \l                            //it will list all tha data base
         iv)  create database student;
         v)   \c student;                    //just check 
         vi)  create table student ( sno int, name varchar(30),age int,trainer varchar(20),doj date;
         vii) \dt  //(one database inside ,i need see this how many table )

         viii) inselt into student values ( 1, 'ranjith', 25, 'prithivi', '2025-09-25';

I need to create 3 users with different DML, DDL, DQL on newly created clean Database in PostgreSQL.

DML should have SELECT, UPDATE, DELETE
DDL should have CREATE, DROP, ALTER, TRUNCATE, INSERT
DQL should have SELECT

all of this in standard scheme public. Important is that user inherit right on newly created tables by DDL user.

           echeck p(select * from student; )

database name=grt
table name = scheme

s_no	group_code	name	date	mobile_number	age	amount
1	2527	Athavi	2025-08-11	7358956288	28	2000
2	2528	Karthik	2025-08-12	9876543210	30	1500
3	2529	Divya	2025-08-13	9123456789	27	20002
4	2530	Mani	2025-08-14	9988776655	32	1000
5	2531	Priya	2025-08-15	9234567890	26	6000
6	2532	Arun	2025-08-16	9345678901	29	8000
7	2533	Vijay	2025-08-17	9456789012	31	7000
8	2534	Sneha	2025-08-18	7358956288	24	2000
9	2535	ranjith	2025-08-19	6381952919	25	2000
10	2536	abdul	2525-08-20	7358952268	28	7000

DDL (Data Definition Language)

1) create
2) alter
3) drop
4) truncate

1. CREATE
   Purpose: To create a new database object (table, view, schema, index).
   Syntax (for table):
   CREATE TABLE students (s_no INT PRIMARY KEY,name VARCHAR(50),age INT,dob DATE);

2. ALTER
   Purpose: To modify an existing table (add, drop, or change columns).

3. CREATE

Purpose: To create a new database object (table, view, schema, index).

Syntax (for table)

CREATE TABLE scheme (
s_no INT PRIMARY KEY,
name VARCHAR(50),
age INT,
dob DATE
);

1. ALTER

Purpose: To modify an existing table (add, drop, or change columns).
add a column
ADD COLUMN mobile_number BIGINT;
Drop a column:

     ALTER TABLE scheme DROP COLUMN age;

1. DROP

Purpose: To delete a table or other database object permanently.

Syntax:

DROP TABLE scheme;

1. TRUNCATE

Purpose: To delete all rows from a tabDML (Data Manipulation Language)

 1) INSERT
 2) UPDATE
 3) DELETE
 4) SELECT

1. INSERT

Purpose: Add new rows into a table.

Syntax: INSERT INTO table_name
VALUES (value1, value2, ...)

1. UPDATE

Purpose: Modify existing data in a table.
Syntax: UPDATE scheme SET age = 25 WHERE s_no = 1;

1. DELETE

Purpose: Remove data rows from a table.

Syntax: delete from scheme

1. Select purpose: The SELECT statement is used to retrieve data from a database table

syxtax :
select * from scheme;

DDL (Data Definition Language)

DDL is the data definition language and is concerned with the SQL statements that modify how the data is stored. It includes CREATE, ALTER, and DROP.

DML replication
PGD doesn't replicate the DML statement. It replicates the changes caused by the DML statement. For example, an UPDATE that changed two rows replicates two changes, whereas a DELETE that didn't remove any rows doesn't replicate anything.

reffer :
https://www.enterprisedb.com/docs/pgd/latest/reference/appusage/dml-ddl/
https://www.geeksforgeeks.org/sql-ddl-dql-dml-dcl-tcl-commands/
https://stackoverflow.com/questions/74284045/create-dml-ddl-dql-user-in-postgresql
chatgpt :