DEV Community

Lakshmi Pritha Nadesan
Lakshmi Pritha Nadesan

Posted on

Day 36 - Where clause, Order by, subquery ,Aggregate function ,Group by in Database

#python #database #aggregate #groupby

Employee Table:

empid | empname | designation | dept | salary -------+---------+-------------------+-----------+-------- 11 | Lakshmi | Software Engineer | IT | 50000 12 | Guru | Manager | HR | 40000 13 | Pritha | Manager | IT | 70000 14 | Gokul | Team lead | Sales | 30000 15 | Raja | HR | Marketing | 65000 16 | Rani | HR | sales | 45000 (6 rows)
Enter fullscreen mode Exit fullscreen mode

Change table name:

employee=# alter table employee rename to employees; ALTER TABLE
Enter fullscreen mode Exit fullscreen mode

Detailed information about the Employees table:

employee=# \d employees Table "public.employees" Column | Type | Collation | Nullable | Default -------------+-----------------------+-----------+----------+--------- empid | integer | | | empname | character varying(30) | | | designation | character varying(30) | | | dept | character varying(20) | | | salary | integer | | |
Enter fullscreen mode Exit fullscreen mode

Add muliple rows to the employees table:

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

Employees table:

employee=# select * from employees; empid | empname | designation | dept | salary -------+---------+-------------------+-----------+-------- 11 | Lakshmi | Software Engineer | IT | 50000 12 | Guru | Manager | HR | 40000 13 | Pritha | Manager | IT | 70000 14 | Gokul | Team lead | Sales | 30000 15 | Raja | HR | Marketing | 65000 16 | Rani | HR | sales | 45000 105 | Bala | Team Lead | AI | 100000 106 | Kani | Manager | CS | 150000 (8 rows)
Enter fullscreen mode Exit fullscreen mode

Employees from all department:

employee=# select dept from employees; dept ----------- IT HR IT Sales Marketing sales AI CS (8 rows)
Enter fullscreen mode Exit fullscreen mode

DISTINCT:
It ensures that the query returns only unique values for the
specified columns.

employee=# select distinct dept from employees; dept ----------- Marketing AI CS sales Sales IT HR (7 rows)
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

Employees from IT department:

employee=# select * from employees where dept = 'IT'; empid | empname | designation | dept | salary -------+---------+-------------------+------+-------- 11 | Lakshmi | Software Engineer | IT | 50000 13 | Pritha | Manager | IT | 70000 (2 rows)
Enter fullscreen mode Exit fullscreen mode

Employees in the 'IT' Department with the Designation 'Software Engineer':

employee=# select * from employees where dept = 'IT' and designation='Software Engineer'; empid | empname | designation | dept | salary -------+---------+-------------------+------+-------- 11 | Lakshmi | Software Engineer | IT | 50000 (1 row)
Enter fullscreen mode Exit fullscreen mode

Employee Designations Renamed to 'Post':

employee=# select designation as post from employees; post ------------------- Software Engineer Manager Manager Team lead HR HR Team Lead Manager (8 rows)
Enter fullscreen mode Exit fullscreen mode

Employees Not in the IT Department:

employee=# select * from employees where dept<> 'IT'; empid | empname | designation | dept | salary -------+---------+-------------+-----------+-------- 12 | Guru | Manager | HR | 40000 14 | Gokul | Team lead | Sales | 30000 15 | Raja | HR | Marketing | 65000 16 | Rani | HR | sales | 45000 105 | Bala | Team Lead | AI | 100000 106 | Kani | Manager | CS | 150000 (6 rows)
Enter fullscreen mode Exit fullscreen mode

Employees with Salary greater than 50,000:

employee=# select * from employees where salary> 50000; empid | empname | designation | dept | salary -------+---------+-------------+-----------+-------- 13 | Pritha | Manager | IT | 70000 15 | Raja | HR | Marketing | 65000 105 | Bala | Team Lead | AI | 100000 106 | Kani | Manager | CS | 150000 (4 rows)
Enter fullscreen mode Exit fullscreen mode

Employees with Salary greater than or equal to 50,000:

employee=# select * from employees where salary>= 50000; empid | empname | designation | dept | salary -------+---------+-------------------+-----------+-------- 11 | Lakshmi | Software Engineer | IT | 50000 13 | Pritha | Manager | IT | 70000 15 | Raja | HR | Marketing | 65000 105 | Bala | Team Lead | AI | 100000 106 | Kani | Manager | CS | 150000 (5 rows)
Enter fullscreen mode Exit fullscreen mode

Employees with Salary less than or equal to 50,000:

employee=# select * from employees where salary<= 50000; empid | empname | designation | dept | salary -------+---------+-------------------+-------+-------- 11 | Lakshmi | Software Engineer | IT | 50000 12 | Guru | Manager | HR | 40000 14 | Gokul | Team lead | Sales | 30000 16 | Rani | HR | sales | 45000 (4 rows)
Enter fullscreen mode Exit fullscreen mode

Employees with Salary less than 50000:

employee=# select * from employees where salary< 50000; empid | empname | designation | dept | salary -------+---------+-------------+-------+-------- 12 | Guru | Manager | HR | 40000 14 | Gokul | Team lead | Sales | 30000 16 | Rani | HR | sales | 45000 (3 rows)
Enter fullscreen mode Exit fullscreen mode

Employees with Salaries Between 40,000 and 100,000:

employee=# select * from employees where salary between 40000 and 100000; empid | empname | designation | dept | salary -------+---------+-------------------+-----------+-------- 11 | Lakshmi | Software Engineer | IT | 50000 12 | Guru | Manager | HR | 40000 13 | Pritha | Manager | IT | 70000 15 | Raja | HR | Marketing | 65000 16 | Rani | HR | sales | 45000 105 | Bala | Team Lead | AI | 100000 (6 rows)
Enter fullscreen mode Exit fullscreen mode

Employees with Designations of 'Team Lead' or 'Manager':

employee=# select * from employees where designation in ('Team Lead', 'Manager'); empid | empname | designation | dept | salary -------+---------+-------------+------+-------- 12 | Guru | Manager | HR | 40000 13 | Pritha | Manager | IT | 70000 105 | Bala | Team Lead | AI | 100000 106 | Kani | Manager | CS | 150000 (4 rows)
Enter fullscreen mode Exit fullscreen mode

Employees with Designations Other Than 'Team Lead' and 'Manager':

employee=# select * from employees where designation not in ('Team Lead', 'Manager'); empid | empname | designation | dept | salary -------+---------+-------------------+-----------+-------- 11 | Lakshmi | Software Engineer | IT | 50000 14 | Gokul | Team lead | Sales | 30000 15 | Raja | HR | Marketing | 65000 16 | Rani | HR | sales | 45000 (4 rows)
Enter fullscreen mode Exit fullscreen mode

Employees Excluding Those with the Designation 'Team Lead':

employee=# select * from employees where not designation = 'Team Lead'; empid | empname | designation | dept | salary -------+---------+-------------------+-----------+-------- 11 | Lakshmi | Software Engineer | IT | 50000 12 | Guru | Manager | HR | 40000 13 | Pritha | Manager | IT | 70000 14 | Gokul | Team lead | Sales | 30000 15 | Raja | HR | Marketing | 65000 16 | Rani | HR | sales | 45000 106 | Kani | Manager | CS | 150000 (7 rows)
Enter fullscreen mode Exit fullscreen mode 
employee=# select * from employees where designation != 'Team Lead'; empid | empname | designation | dept | salary -------+---------+-------------------+-----------+-------- 11 | Lakshmi | Software Engineer | IT | 50000 12 | Guru | Manager | HR | 40000 13 | Pritha | Manager | IT | 70000 14 | Gokul | Team lead | Sales | 30000 15 | Raja | HR | Marketing | 65000 16 | Rani | HR | sales | 45000 106 | Kani | Manager | CS | 150000 (7 rows)
Enter fullscreen mode Exit fullscreen mode

Employee 'Raja' in IT or Marketing Departments :

employee=# select * from employees where empname='Raja' and (dept = 'IT' or dept='Marketing'); empid | empname | designation | dept | salary -------+---------+-------------+-----------+-------- 15 | Raja | HR | Marketing | 65000 (1 row)
Enter fullscreen mode Exit fullscreen mode

Orderby:

The ORDER BY clause in SQL is used to sort the result set of a query in either ascending (ASC) or descending (DESC) order based on one or more columns.

Employees Sorted by Designation (Ascending Order):

employee=# select * from employees order by designation; empid | empname | designation | dept | salary -------+---------+-------------------+-----------+-------- 16 | Rani | HR | sales | 45000 15 | Raja | HR | Marketing | 65000 13 | Pritha | Manager | IT | 70000 106 | Kani | Manager | CS | 150000 12 | Guru | Manager | HR | 40000 11 | Lakshmi | Software Engineer | IT | 50000 14 | Gokul | Team lead | Sales | 30000 105 | Bala | Team Lead | AI | 100000 (8 rows)
Enter fullscreen mode Exit fullscreen mode

Employees Sorted by Name and Salary (Ascending Order):

employee=# select * from employees order by empname,salary; empid | empname | designation | dept | salary -------+---------+-------------------+-----------+-------- 105 | Bala | Team Lead | AI | 100000 14 | Gokul | Team lead | Sales | 30000 12 | Guru | Manager | HR | 40000 106 | Kani | Manager | CS | 150000 11 | Lakshmi | Software Engineer | IT | 50000 13 | Pritha | Manager | IT | 70000 15 | Raja | HR | Marketing | 65000 16 | Rani | HR | sales | 45000 (8 rows)
Enter fullscreen mode Exit fullscreen mode

Employees Sorted by Salary (Descending) and Name (Ascending):

employee=# select * from employees order by salary desc,empname asc; empid | empname | designation | dept | salary -------+---------+-------------------+-----------+-------- 106 | Kani | Manager | CS | 150000 105 | Bala | Team Lead | AI | 100000 13 | Pritha | Manager | IT | 70000 15 | Raja | HR | Marketing | 65000 11 | Lakshmi | Software Engineer | IT | 50000 16 | Rani | HR | sales | 45000 12 | Guru | Manager | HR | 40000 14 | Gokul | Team lead | Sales | 30000 (8 rows)
Enter fullscreen mode Exit fullscreen mode

Top 3 Employees with the Lowest Salaries:

employee=# select * from employees order by salary limit 3; empid | empname | designation | dept | salary -------+---------+-------------+-------+-------- 14 | Gokul | Team lead | Sales | 30000 12 | Guru | Manager | HR | 40000 16 | Rani | HR | sales | 45000 (3 rows)
Enter fullscreen mode Exit fullscreen mode

Top 3 Employees with the Highest Salaries:

employee=# select * from employees order by salary desc limit 3; empid | empname | designation | dept | salary -------+---------+-------------+------+-------- 106 | Kani | Manager | CS | 150000 105 | Bala | Team Lead | AI | 100000 13 | Pritha | Manager | IT | 70000 (3 rows)
Enter fullscreen mode Exit fullscreen mode

Employees with Names Starting with 'R':

employee=# select * from employees where empname like 'R%'; empid | empname | designation | dept | salary -------+---------+-------------+-----------+-------- 15 | Raja | HR | Marketing | 65000 16 | Rani | HR | sales | 45000 (2 rows)
Enter fullscreen mode Exit fullscreen mode

Employees with 'a' as the Second Character in Their Name:

employee=# select * from employees where empname like '_a%'; empid | empname | designation | dept | salary -------+---------+-------------------+-----------+-------- 11 | Lakshmi | Software Engineer | IT | 50000 15 | Raja | HR | Marketing | 65000 16 | Rani | HR | sales | 45000 105 | Bala | Team Lead | AI | 100000 106 | Kani | Manager | CS | 150000 (5 rows)
Enter fullscreen mode Exit fullscreen mode

Employees with 'an' as the Second and Third Characters in Their Name:

employee=# select * from employees where empname like '_an%'; empid | empname | designation | dept | salary -------+---------+-------------+-------+-------- 16 | Rani | HR | sales | 45000 106 | Kani | Manager | CS | 150000 (2 rows)
Enter fullscreen mode Exit fullscreen mode

Employees with 'n' as the Third Character in Their Name:

employee=# select * from employees where empname like '__n%'; empid | empname | designation | dept | salary -------+---------+-------------+-------+-------- 16 | Rani | HR | sales | 45000 106 | Kani | Manager | CS | 150000 (2 rows)
Enter fullscreen mode Exit fullscreen mode

Employees with Names Matching the Pattern 'P_i__a':

employee=# select * from employees where empname like 'P_i__a'; empid | empname | designation | dept | salary -------+---------+-------------+------+-------- 13 | Pritha | Manager | IT | 70000 (1 row)
Enter fullscreen mode Exit fullscreen mode

Employee with the Lowest Salary:

employee=# select empname from employees order by salary limit 1; empname --------- Gokul (1 row)
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,sum,max,min:

employee=# select count(*) from employees; count ------- 8 (1 row) employee=# select avg(salary) from employees; avg -------------------- 68750.000000000000 (1 row) employee=# select sum(salary) from employees; sum -------- 550000 (1 row) employee=# select min(salary) from employees; min ------- 30000 (1 row) employee=# select max(salary) from employees; max ------- 100000 (1 row)
Enter fullscreen mode Exit fullscreen mode

Subquery:

A subquery (also called an inner query) is a query nested inside another SQL query.

Second Highest Salary in the Employees Table:

employee=# select max(salary) from employees where salary not in (select max(salary) from employees); max -------- 100000 (1 row)
Enter fullscreen mode Exit fullscreen mode

Third Highest Salary in the Employees Table:

employee=# select max(salary) from employees where salary in (select max(salary) from employees where salary not in (select max(salary) from employees)); max -------- 100000 (1 row)
Enter fullscreen mode Exit fullscreen mode

Employee(s) with the Third Highest Salary:

employee=# select empname from employees where salary in (select max(salary) from employees where salary not in (select max(salary) from employees)); empname --------- Bala (1 row)
Enter fullscreen mode Exit fullscreen mode

Groupby:

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 for Each Department:

employee=# select sum(salary) from employees group by dept; sum -------- 65000 100000 150000 45000 30000 120000 40000 (7 rows)
Enter fullscreen mode Exit fullscreen mode

Average Salary for Each Department:

employee=# select round(avg(salary),2) from employees group by dept; round ----------- 65000.00 100000.00 150000.00 45000.00 30000.00 60000.00 40000.00 (7 rows)
Enter fullscreen mode Exit fullscreen mode

Maximum Salary for Each Department:

employee=# select max(salary) from employees group by dept; max -------- 65000 100000 150000 45000 30000 70000 40000 (7 rows)
Enter fullscreen mode Exit fullscreen mode

Minimum Salary for Each Department:

employee=# select min(salary) from employees group by dept; min -------- 65000 100000 150000 45000 30000 50000 40000 (7 rows)
Enter fullscreen mode Exit fullscreen mode

Number of employees got Salary for Each Department:

employee=# select count(salary) from employees group by dept; count ------- 1 1 1 1 1 2 1 (7 rows)
Enter fullscreen mode Exit fullscreen mode

Top comments (0)