Guru prasanna

Posted on Feb 19

PostgreSql Tasks - Interview Questions

#payilagam #postgres #sql #database

Tasks:

1. What is the difference between WHERE and HAVING clauses?

WHERE Clause: Filters rows before any grouping or aggregation occurs.
HAVING Clause: Filters groups after aggregation using the GROUP BY clause.

2. What is a foreign key?

A foreign key is a column or set of columns in one table that establishes a link to the primary key in another table.

3. What is the difference between UNION and UNION ALL?

UNION: Combines the result of two queries and removes duplicate rows.
UNION ALL: Combines the result of two queries and does not remove duplicate rows.

4. What are the differences between DELETE, TRUNCATE, and DROP?

DELETE: Removes specific rows using WHERE, can be rolled back, slower. Syntax: DELETE FROM table_name WHERE condition;
TRUNCATE: Removes all rows, cannot be rolled back, faster than DELETE, resets identity columns. Syntax: TRUNCATE TABLE table_name;
DROP: Removes the entire table structure and data permanently, cannot be rolled back. Syntax: DROP TABLE table_name;

5. What are the different types of joins in SQL?

INNER JOIN: Returns records that have matching values in both tables
LEFT JOIN: Returns all records from the left table, and the matched records from the right table
RIGHT JOIN: Returns all records from the right table, and the matched records from the left table
FULL JOIN: Returns all records when there is a match in either left or right table

Employees Table:

movie=# create table employees(employeeid int not null,employeename varchar(10),department varchar(10),salary float,age int,hiredate date,managerid int);
CREATE TABLE

insert into employees(employeeid, employeename, department, salary, age ,hiredate,managerid ) values (101,'Alice','HR',6000.00,30,'2015-06-15',null),(102,'Bob','Sales',12000.00,35,'2017-08-20',101),(103,'Charlie','HR',5500.00,28,'2018-03-10',101),(104,'David','Sales',8500.00,32,'2019-09-15',102),(105,'Eva','Marketing',7000.00,27,'2020-11-01',102),(106,'Frank','Sales',12000.00,40,'2016-07-23',null),(107,'Grace','Marketing',9500.00,33,'2018-01-12',105),(108,'Hannah','HR',6500.00,29,'2021-01-12',101);

movie=# select * from employees;
 employeeid | employeename | department | salary | age |  hiredate  | managerid 
------------+--------------+------------+--------+-----+------------+-----------
        101 | Alice        | HR         |   6000 |  30 | 2015-06-15 |          
        102 | Bob          | Sales      |  12000 |  35 | 2017-08-20 |       101
        103 | Charlie      | HR         |   5500 |  28 | 2018-03-10 |       101
        104 | David        | Sales      |   8500 |  32 | 2019-09-15 |       102
        105 | Eva          | Marketing  |   7000 |  27 | 2020-11-01 |       102
        106 | Frank        | Sales      |  12000 |  40 | 2016-07-23 |          
        107 | Grace        | Marketing  |   9500 |  33 | 2018-01-12 |       105
        108 | Hannah       | HR         |   6500 |  29 | 2021-01-12 |       101
(8 rows)

6. Write a SQL query to find the second highest salary from the Employees table.

movie=# select max(salary) from employees where salary in (select max(salary) from employees where salary not in (select max(salary) from employees));
 max  
------
 9500
(1 row)

7. Write a SQL query to count the number of employees in each department from the Employees table.

movie=# select department,count(*) from employees group by department;
 department | count 
------------+-------
 Marketing  |     2
 Sales      |     3
 HR         |     3
(3 rows)

8. Write a SQL query to retrieve all employees whose salary is between 5000 and 10000.

movie=# select employeename,salary from employees where salary between 5000 and 10000;
 employeename | salary 
--------------+--------
 Alice        |   6000
 Charlie      |   5500
 David        |   8500
 Eva          |   7000
 Grace        |   9500
 Hannah       |   6500
(6 rows)

9. Write a SQL query to update the salary of an employee with EmployeeID = 101 by 10%.

movie=# update employees set salary = round(salary * 1.10) where EmployeeID = 101;
UPDATE 1

10. Write a SQL query to delete all records from the employees table where the employee’s age is less than 25.

movie=# delete from employees where age < 25;
DELETE 0

11. Write a SQL query to find the employees who have the same salary as the highest-paid employee.

movie=# select employeename,salary from employees where salary in (select max(salary) from employees);
 employeename | salary 
--------------+--------
 Bob          |  12000
 Frank        |  12000
(2 rows)

12. Write a SQL query to retrieve all employees who do not have a manager (Manager ID is NULL).

movie=# select * from employees where managerid is null;
 employeeid | employeename | department | salary | age |  hiredate  | managerid 
------------+--------------+------------+--------+-----+------------+-----------
        106 | Frank        | Sales      |  12000 |  40 | 2016-07-23 |          
        101 | Alice        | HR         |   6600 |  30 | 2015-06-15 |          
(2 rows)

13. Scenario: You have two tables:
Orders (OrderID, CustomerID, OrderDate, Total Amount)
Customers (CustomerID, CustomerName, Email, PhoneNumber)
Question: Write a SQL query to retrieve all orders along with the corresponding customer
information (CustomerName, Email, PhoneNumber).

SELECT orders.OrderID, orders.OrderDate, orders.TotalAmount,customers.CustomerName, customers.Email, customer.PhoneNumber
FROM Orders
LEFT JOIN Customers ON orders.CustomerID = customers.CustomerID;

14. Scenario: You have two tables:
Employees (EmployeeID, EmployeeName, DepartmentID)
Departments (DepartmentID, DepartmentName)
Question: Write a SQL query to retrieve all employees who work in the same department as ‘John Doe’

movie=# SELECT EmployeeID, employeename, DepartmentID from Employee 
where DepartmentID = (select DepartmentID from Employee where employeename = 'John Doe') and employeename != 'John Doe';

15. Scenario: You have two tables:
Employees (EmployeeID, EmployeeName, DepartmentID, Salary)
Departments (DepartmentID, DepartmentName)
Question: Write a SQL query to find the employees who earn more than the average salary in
their department.

SELECT Employee.employeename, Employee.Salary
FROM Employee
WHERE Employee.Salary > (select avg(Salary) from Employee where Employee.DepartmentID = Employee.DepartmentID);

16. Write a DDL query to add a new column “phone_Number” to the customers table.


ALTER TABLE Customers ADD PhoneNumber VARCHAR(15);

17. Write a query to count the number of orders placed by each customer in the orders
table.

SELECT CustomerID, COUNT(*) from Orders group by CustomerID;

18. Write a DML query to update the city of “customer_id” 101 to “Mumbai”.

UPDATE Customers SET City = 'Mumbai' WHERE CustomerID = 101;

19. Write a DML query to delete the customer with “customer_id” 105

DELETE FROM Customers WHERE CustomerID = 105;

20. List all customers who have placed orders in the month of January 2024.

select distinct customers.*  
from customers  
join orders on customers.customerid = orders.customerid  
where orders.orderdate between '2024-01-01' and '2024-01-31';

21. Write any 5 inbuilt functions in SQL.

22. What is the DISTINCT Keyword in SQL?

DISTINCT removes duplicate values from the result set.
Used to fetch unique records from a column.

Ex:

SELECT DISTINCT Department FROM Employees;

23. Explain DBMS

--> DBMS (Database Management System) is software used to store, retrieve, and manage data in a structured way.
--> It's like an electronic filing cabinet that helps you find and access information quickly.

24. What is PostgreSQL?

PostgreSQL is an open-source relational database used to store and manage data.
It supports SQL queries and advanced features like JSON support, indexing, and transactions.
Known for its stability, security, and scalability, making it ideal for small and large applications.
Used by companies for web applications, analytics, and enterprise solutions.

25. What is DML,DDL,DQL,DCL,TCL

DML:(Data Manipulation Language)
- Used to modify data in tables.
- Includes commands like insert, update, delete.
DDL:(Data Definition Language)
- Defines or modifies database structure.
- Includes commands like create, alter, drop, truncate.
DQL:(Data Query Language)
- Used to retrieve data from the database.
- Includes the select command.
DCL:(Data Control Language)
- Manages user permissions and security.
- Includes commands like grant, revoke.
TCL:(Transaction Control Language)
- Controls database transactions.
- Includes commands like commit, rollback, savepoint.