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

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)

Change table name:

employee=# alter table employee rename to employees;
ALTER TABLE

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               |           |          | 

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

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)

Employees from all department:

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

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)

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;

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

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)

Employee with the Lowest Salary:

employee=# select empname from employees order by salary limit 1;
 empname 
---------
 Gokul
(1 row)

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)

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)

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)

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)

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)

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)

Maximum Salary for Each Department:

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

Minimum Salary for Each Department:

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

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)