DEV Community

Guru prasanna
Guru prasanna

Posted on

PostgreSql Day-2 Where clause, Order by, subquery ,Aggregate function ,Group by

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)

Enter fullscreen mode Exit fullscreen mode

For Table Name Change:

alter table employee rename to employees;
Enter fullscreen mode Exit fullscreen mode

To insert multiple rows in an existing table:

insert into employees values(105, 'Bala', 'Team Lead', 'AI', 100000), (106, 'Kani', 'Manager', 'CS', 150000);
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

Retrieve all department names from the employees table, including duplicates.

select dept from employees;
Enter fullscreen mode Exit fullscreen mode

Retrieve unique department names from the employees table, eliminating duplicates.

select distinct dept from employees;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Retrieve employees from the 'CS' department.

select * from employees where dept = 'CS';
Enter fullscreen mode Exit fullscreen mode

Retrieve employees from the 'CS' department or those named 'Kani'.

select * from employees where dept='CS' or name ='Kani';
Enter fullscreen mode Exit fullscreen mode

Retrieve employees who are in the 'CS' department and also named 'Kani'.

select * from employees where dept='CS' and name ='Kani';
Enter fullscreen mode Exit fullscreen mode

Display the 'designation' column as 'post' in the result.

select designation as post from employees;
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

Retrieve employees whose salary is greater than 50,000.

select * from employees where salary>50000;
Enter fullscreen mode Exit fullscreen mode

Retrieve employees whose salary is less than 50,000.

select * from employees where salary <50000;
Enter fullscreen mode Exit fullscreen mode

Retrieve employees whose salary falls between 30,000 and 1,00,000.

select * from employees where salary between 30000 and 100000;
Enter fullscreen mode Exit fullscreen mode

Retrieve employees who hold the position of either 'Team Lead' or 'Manager'.

select * from employees where designation in ('Team Lead', 'Manager');
Enter fullscreen mode Exit fullscreen mode

Retrieve employees who do not hold the position of 'Team Lead' or 'Manager'.

select * from employees where designation not in ('Team Lead', 'Manager');
Enter fullscreen mode Exit fullscreen mode

Retrieve employees whose designation is not 'Team Lead'.

select * from employees where not designation = 'Team Lead';
Enter fullscreen mode Exit fullscreen mode

(or)

select * from employees where  designation != 'Team Lead';
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Sort employees alphabetically by name, and in case of duplicate names, sort them by salary.

select * from employees order by name, salary; 
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Retrieve the top 3 lowest-paid employees.

SELECT * FROM employees ORDER BY salary LIMIT 3;
Enter fullscreen mode Exit fullscreen mode

Retrieve employees whose name starts with 'p'.

SELECT * FROM employees WHERE name LIKE 'p%';
Enter fullscreen mode Exit fullscreen mode

Retrieve employees whose name has 'an' as the second and third letters.

select * from employees where name like '_an%';
Enter fullscreen mode Exit fullscreen mode

Retrieve employees whose name ends with 'a'.

select * from employees where name like '%a';
Enter fullscreen mode Exit fullscreen mode

Retrieve employees whose name starts with 'K', has two more characters, and ends with 'i'.

select * from employees where name like 'K__i';
Enter fullscreen mode Exit fullscreen mode

Retrieve employees whose name does not end with 'a'.

select * from employees where name not like '%a';
Enter fullscreen mode Exit fullscreen mode

Find the highest salary among employees.

select max(salary) from employees; 
Enter fullscreen mode Exit fullscreen mode

Find the name of the employee with the lowest salary.

select name from employees order by salary limit 1; 
Enter fullscreen mode Exit fullscreen mode

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; 
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Count of Employees per Department:

select count(name) from employees  group by dept;
Enter fullscreen mode Exit fullscreen mode

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); 
Enter fullscreen mode Exit fullscreen mode

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));
Enter fullscreen mode Exit fullscreen mode

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));
Enter fullscreen mode Exit fullscreen mode

Top comments (0)