Table employee:
empid | name | designation | dept | salary
-------+----------+----------------------+-----------+--------
101 | gopi | junior executive | Finance | 17000
102 | Guru | Senior developer | IT | 70000
103 | Pritha | full stack developer | IT | 80000
104 | Gokul | junior hr | HR | 18000
105 | prasanna | Sales Head | Marketing | 20000
106 | krishna | Accounting Manager | Accounts | 30000
(6 rows)
For Table Name Change:
alter table employee rename to employees;
To insert multiple rows in an existing table:
insert into employees values(105, 'Bala', 'Team Lead', 'AI', 100000), (106, 'Kani', 'Manager', 'CS', 150000);
Output with table name- employees:
empid | name | designation | dept | salary
-------+----------+----------------------+-----------+--------
101 | gopi | junior executive | Finance | 17000
102 | Guru | Senior developer | IT | 70000
103 | Pritha | full stack developer | IT | 80000
104 | Gokul | junior hr | HR | 18000
105 | prasanna | Sales Head | Marketing | 20000
106 | krishna | Accounting Manager | Accounts | 30000
107 | Bala | Team Lead | AI | 100000
108 | Kani | Manager | CS | 150000
(8 rows)
Retrieve all department names from the employees table, including duplicates.
select dept from employees;
Retrieve unique department names from the employees table, eliminating duplicates.
select distinct dept from employees;
Where clause:
The WHERE clause in PostgreSQL is used to filter records based on specific conditions. It helps retrieve only the rows that meet the given criteria.
Syntax:
SELECT column1, column2 FROM table_name WHERE condition;
Retrieve employees from the 'CS' department.
select * from employees where dept = 'CS';
Retrieve employees from the 'CS' department or those named 'Kani'.
select * from employees where dept='CS' or name ='Kani';
Retrieve employees who are in the 'CS' department and also named 'Kani'.
select * from employees where dept='CS' and name ='Kani';
Display the 'designation' column as 'post' in the result.
select designation as post from employees;
Operators used in postgreSql:
-
=
: Equal to -
<
: Less than -
>
: Greater than -
<=
: Less than or equal to -
>=
: Greater than or equal to -
<>
: Not equal to -
!=
: Not equal to -
LIKE
: Check if a value matches a pattern (case sensitive) -
ILIKE
: Check if a value matches a pattern (case insensitive) -
AND
: Logical AND -
OR
: Logical OR -
IN
: Check if a value is between a range of values -
BETWEEN
: Check if a value is between a range of values -
IS NULL
: Check if a value is NULL -
NOT
: Makes a negative result e.g. NOT LIKE, NOT IN, NOT BETWEEN
Refer: https://www.w3schools.com/postgresql/postgresql_operators.php
Retrieve employees who are not in the 'IT' department.(<> --> not equal to)
SELECT * FROM EMPLOYEES WHERE DEPT<>'IT';
Retrieve employees whose salary is greater than 50,000.
select * from employees where salary>50000;
Retrieve employees whose salary is less than 50,000.
select * from employees where salary <50000;
Retrieve employees whose salary falls between 30,000 and 1,00,000.
select * from employees where salary between 30000 and 100000;
Retrieve employees who hold the position of either 'Team Lead' or 'Manager'.
select * from employees where designation in ('Team Lead', 'Manager');
Retrieve employees who do not hold the position of 'Team Lead' or 'Manager'.
select * from employees where designation not in ('Team Lead', 'Manager');
Retrieve employees whose designation is not 'Team Lead'.
select * from employees where not designation = 'Team Lead';
(or)
select * from employees where designation != 'Team Lead';
Order by
The ORDER BY
clause in PostgreSQL is used to sort the result set in ascending (ASC) or descending (DESC) order based on one or more columns.
Sort employees in ascending order based on their designation.
select * from employees order by designation;
Sort employees alphabetically by name, and in case of duplicate names, sort them by salary.
select * from employees order by name, salary;
Sort employees by highest salary first, and if salaries are the same, sort them alphabetically by name.
SELECT * FROM employees ORDER BY salary DESC, name ASC;
Retrieve the top 3 lowest-paid employees.
SELECT * FROM employees ORDER BY salary LIMIT 3;
Retrieve employees whose name starts with 'p'.
SELECT * FROM employees WHERE name LIKE 'p%';
Retrieve employees whose name has 'an' as the second and third letters.
select * from employees where name like '_an%';
Retrieve employees whose name ends with 'a'.
select * from employees where name like '%a';
Retrieve employees whose name starts with 'K', has two more characters, and ends with 'i'.
select * from employees where name like 'K__i';
Retrieve employees whose name does not end with 'a'.
select * from employees where name not like '%a';
Find the highest salary among employees.
select max(salary) from employees;
Find the name of the employee with the lowest salary.
select name from employees order by salary limit 1;
Aggregate Functions:
Aggregate functions in PostgreSQL perform calculations on a set of rows and return a single result.
To find count,average with decimal points limit,sum,max:
select count(*) from employees;
select avg(salary) from employees;
select (round(avg(salary), 3) from employees;
select sum(salary) from employees;
select max(salary) from employees;
GROUP BY:
--> The GROUP BY
clause groups rows that have the same values into summary rows, like "find the number of customers in each country".
--> The GROUP BY
clause is often used with aggregate functions like COUNT(), MAX(), MIN(), SUM(), AVG() to group the result-set by one or more columns.
Total Salary per Department:
select sum(salary) from employees group by dept;
Count of Employees per Department:
select count(name) from employees group by dept;
Subquery:
A subquery (also called an inner query) is a query nested inside another SQL query.
Find the Employee(s) with the Highest Salary:
select name from employees where salary = (select max(salary) from employees);
Find the Second Highest Salary:
select max(salary) from employees where salary in (select max(salary) from employees where salary not in (select max(salary) from employees));
Find the Employee(s) with the Second Highest Salary:
select name from employees where salary in (select max(salary) from employees where salary not in (select max(salary) from employees));
Top comments (0)