Task 5 - Database

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

2.What is a foreign key?

A foreign key is a column or a set of columns in a relational database table that is used to establish a link between the data in two tables. It is a constraint that helps maintain referential integrity by ensuring that the value in the foreign key column corresponds to a valid entry in another table.

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

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

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

Create employees table:

create table employees(employeeid int, employeename text, department text, 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); 

INSERT 0 8

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:

select distinct salary from employees order by salary desc limit 1 offset 1;

 salary 
--------
   9500
(1 row)

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

select department,count(employeeid) 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:

select * from employees where salary between 5000 and 10000;

 employeeid | employeename | department | salary | age |  hiredate  | managerid 
------------+--------------+------------+--------+-----+------------+-----------
        101 | Alice        | HR         |   6000 |  30 | 2015-06-15 |          
        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
        107 | Grace        | Marketing  |   9500 |  33 | 2018-01-12 |       105
        108 | Hannah       | HR         |   6500 |  29 | 2021-01-12 |       101
(6 rows)

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

update employees set salary = round(salary * 1.10)  where employeeid = 101;

UPDATE 1

select * from employees;

 employeeid | employeename | department | salary | age |  hiredate  | managerid 
------------+--------------+------------+--------+-----+------------+-----------
        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
        101 | Alice        | HR         |   6600 |  30 | 2015-06-15 |          
(8 rows)

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

delete from employees where age<25;

DELETE 0

select * from employees;

 employeeid | employeename | department | salary | age |  hiredate  | managerid 
------------+--------------+------------+--------+-----+------------+-----------
        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
        101 | Alice        | HR         |   6600 |  30 | 2015-06-15 |          
(8 rows)

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

select * from employees where salary=(select max(salary ) from employees);

 employeeid | employeename | department | salary | age |  hiredate  | managerid 
------------+--------------+------------+--------+-----+------------+-----------
        102 | Bob          | Sales      |  12000 |  35 | 2017-08-20 |       101
        106 | Frank        | Sales      |  12000 |  40 | 2016-07-23 |          
(2 rows)

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

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.customerid, orders.orderdate, orders.totalamount,customers.customername,customers.email, customers.phonenumber from orders 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’.

select * from employees where departmentid(select departmentid from employees 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 employeeid,employeename,departmentid,salary from employees where salary>(select avg(salary) from employees as e_avg where e_avg.departmentid=employees.departmentid);

Create customers table:

create table customers(customerid int, customername text, city text);

CREATE TABLE

insert into customers(customerid,customername,city) values (101,'Alice','Delhi'),(102,'Bob','Chennai'),(103,'Charlie','Kolkata'),
(104,'David','Bangalore'),
(105,'Eva','Hyderabad');

INSERT 0 5

select* from customers;

 customerid | customername |   city    
------------+--------------+-----------
        101 | Alice        | Delhi
        102 | Bob          | Chennai
        103 | Charlie      | Kolkata
        104 | David        | Bangalore
        105 | Eva          | Hyderabad
(5 rows)

Create orders table:

create table orders (orderid int, customerid int, orderdate date);

CREATE TABLE


insert into orders(orderid,customerid,orderdate) values(1,101,'2024-01-15'),(2,102,'2024-01-20'),
(3,101,'2024-02-10'),
(4,103,'2024-01-05'),
(5,104,'2024-02-15');

INSERT 0 5

select * from orders; 

 orderid | customerid | orderdate  
---------+------------+------------
       1 |        101 | 2024-01-15
       2 |        102 | 2024-01-20
       3 |        101 | 2024-02-10
       4 |        103 | 2024-01-05
       5 |        104 | 2024-02-15
(5 rows)

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

alter table customers add phone_number int;

ALTER TABLE

select* from customers;   

 customerid | customername |   city    | phone_number 
------------+--------------+-----------+--------------
        101 | Alice        | Delhi     |             
        102 | Bob          | Chennai   |             
        103 | Charlie      | Kolkata   |             
        104 | David        | Bangalore |             
        105 | Eva          | Hyderabad |             
(5 rows)

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

select customerid, count(orderid) from orders group by customerid order by customerid;

 customerid | count 
------------+-------
        101 |     2
        102 |     1
        103 |     1
        104 |     1
(4 rows)

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

update customers set city='Mumbai'  where customerid = 101;

UPDATE 1

select* from customers;

 customerid | customername |   city    | phone_number 
------------+--------------+-----------+--------------
        102 | Bob          | Chennai   |             
        103 | Charlie      | Kolkata   |             
        104 | David        | Bangalore |             
        105 | Eva          | Hyderabad |             
        101 | Alice        | Mumbai    |             
(5 rows)

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

delete from customers where customerid=105; 

DELETE 1

select* from customers;

 customerid | customername |   city    | phone_number 
------------+--------------+-----------+--------------
        102 | Bob          | Chennai   |             
        103 | Charlie      | Kolkata   |             
        104 | David        | Bangalore |             
        101 | Alice        | Mumbai    |             
(4 rows)

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

select customers.customerid, customers.customername,customers.city from customers join orders on customers.customerid=orders.customerid where orderdate between '01-01-2024' and '31-01-2024';

 customerid | customername |  city   
------------+--------------+---------
        102 | Bob          | Chennai
        103 | Charlie      | Kolkata
        101 | Alice        | Mumbai
(3 rows)

21. Write any 5 inbuilt functions in SQL:

count()
sum()
avg()
min()
max()

22. What is the DISTINCT keyword in SQL?

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

23. Explain DBMS.

A Database Management System (DBMS) is software that allows users to create, manage, and interact with databases efficiently. It ensures data storage, retrieval, security, and integrity while providing a structured way to organize information.

24. What is postgres?

PostgreSQL, commonly known as Postgres, is a powerful, open-source, object-relational database management system (ORDBMS). It is known for its reliability, extensibility, and performance.

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

Data Definition Language (DDL)
DDL is used to define and manage the structure of database objects, such as tables, indexes, and views.

Data Manipulation Language (DML)
DML is used for manipulating the data stored in the database. It includes operations like inserting, updating, deleting, and retrieving data.

Data Control Language (DCL)
DCL is used to control access to data in the database. It defines permissions for users and roles.

Transaction Control Language (TCL)
TCL is used to manage transactions in a database, which are groups of DML operations. It ensures data integrity and consistency.

Data Query Language (DQL)
DQL is primarily concerned with querying and retrieving data from the database.