PostgreSql Tasks - Interview Questions

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.