The SELECT DISTINCT Statement

#postgres #postgressql #database #software

Day -5

The SELECT DISTINCT statement is used to return only distinct (different) values.

Inside a table, a column often contains many duplicate values and sometimes you only want to list the different (distinct) values.

CREATE TABLE employees (
    emp_id INT,
    name VARCHAR(50),
    city VARCHAR(50),
    department VARCHAR(50),
    salary INT
);

INSERT INTO employees VALUES
(101, 'Vidhya', 'Chennai', 'IT', 35000),
(102, 'Anu', 'Chennai', 'HR', 30000),
(103, 'Ravi', 'Bangalore', 'IT', 40000),
(104, 'Kumar', 'Chennai', 'Finance', 45000),
(105, 'Priya', 'Bangalore', 'HR', 32000),
(106, 'Arun', 'Mumbai', 'IT', 38000),
(107, 'Divya', 'Mumbai', 'Finance', 42000),
(108, 'Siva', 'Chennai', 'IT', 35000);

Display all unique cities
Display all unique departments.
Display unique salaries.
Display unique combinations of city and department

select distinct city 
from empolyees

select distinct department 
from empolyees

select distinct salary 
from empolyees

select distinct department,city
from empolyees

Display unique cities where salary is greater than 35000.
Display unique departments where salary is greater than 30000.
Display unique cities of employees working in the IT department.
Display unique departments of employees working in Chennai.

select distinct city 
from employees_india
where salary>35000

select distinct department 
from employees_india
where salary>30000

select distinct city 
from employees_india
where department='IT'

select distinct department 
from employees_india
where city='Chennai'

Display unique combinations of city and department for employees whose salary is greater than 35000.
Display unique cities where employees belong to either IT or Finance.
Display unique departments where employees are from Chennai or Mumbai.
Display unique salary values for employees working in IT.

select distinct city,department 
from employees_india
where salary>35000

select distinct city 
from employees_india
where department in ('IT','Finance');

select distinct department 
from employees_india
where city in ('chennai','Mumbai);

select distinct salary 
from employees_india
where department='IT';