PostgreSQL(part-2)

Create table

CREATE TABLE employee (
    empid INTEGER,
    name VARCHAR(25),
    designation VARCHAR(25),
    dept VARCHAR(25),
    salary INTEGER
);

Insert to table employee

INSERT INTO employee
VALUES (123, 'Kavin', 'Software Engineer', 'DB', 25000);

INSERT INTO employee
VALUES (124, 'Viyan', 'Software Engineer', 'AI', 27000);

INSERT INTO employee
VALUES(101, 'Arul', 'Team Lead', 'Front End', 35000);

INSERT INTO employees
VALUES
(111, 'Mugilan', 'Team Lead', 35000),
(118, 'Pari', 'SQL Developer', 45000);

INSERT INTO employee
VALUES(101, 'Agaran', 'Team Lead', 'DB', 45000);

To display the table

SELECT * FROM employee;

empid	name	designation	dept	salary
123	Kavin	Software Engineer	DB	25000
124	Viyan	Software Engineer	AI	27000
101	Arul	Team Lead	Front End	35000
111	Mugilan	Team Lead	35000
118	Pari	SQL Developer	45000
101	Agaran	Team Lead	DB	45000

TO display any one part

select name from employee;

name

Kavin
Viyan
Arul
Mugilan
Pari
Agaran
(6 rows)

where

select * from employee where salary > 28000;

empid	name	designation	dept	salary
101	Arul	Team Lead	Front End	35000
101	Agaran	Team Lead	DB	45000

where(or)

select*from employee where dept='AI' or dept='DB';

empid	name	designation	dept	salary
123	Kavin	Software Engineer	DB	25000
124	Viyan	Software Engineer	AI	27000
101	Agaran	Team Lead	DB	45000

where(and)

select*from employee where dept='AI' and salary>25000;

empid	name	designation	dept	salary
124	Viyan	Software Engineer	AI	27000

where(not,<>)

select * from employee where not dept='AI';

empid	name	designation	dept	salary
123	Kavin	Software Engineer	DB	25000
101	Arul	Team Lead	Front End	35000
111	Mugilan	Team Lead	35000
118	Pari	SQL Developer	45000
101	Agaran	Team Lead	DB	45000

select * from employee where dept<>'AI';

empid	name	designation	dept	salary
123	Kavin	Software Engineer	DB	25000
101	Arul	Team Lead	Front End	35000
111	Mugilan	Team Lead	35000
118	Pari	SQL Developer	45000
101	Agaran	Team Lead	DB	45000

where(and,not)

select * from employee where dept<>'AI' and salary>30000;

empid	name	designation	dept	salary
101	Arul	Team Lead	Front End	35000
101	Agaran	Team Lead	DB	45000

Distinct
Used to remove duplicate values from the table

select empid from employee;

empid

101
123
101
124
118
(5 rows)
Before using distinct

select distinct empid from employee;

empid

101
124
118
123
(4 rows)
After using distinct