DEV Community: Lakshmi Pritha Nadesan

Day 2 - GIT commands

Lakshmi Pritha Nadesan — Wed, 26 Feb 2025 01:02:58 +0000

Creating Git Folder:

Now, let's create a new folder for our project

mkdir Projectname
cd Projectname

mkdir makes a new directory.
cd changes the current working directory.

Clone an Existing Repository:

Copies a remote repository to your local machine.

git clone <repository_url>

This is useful when you want to contribute to an existing project.

Check the Status of Your Repository:

Shows which files have been modified, staged, or untracked.

git status

Always run this before committing to see what’s changed.

Add Files to Staging:

Moves changes from the working directory to the staging area.

git add <file_name>    # Add a specific file
git add .              # Add all changes

Staging means preparing files to be committed.

Commit Changes:

Saves the staged changes permanently in the repository.

git commit -m "Your commit message"

The commit message should describe what changes were made.

Check Your Git Configuration:

To verify your Git username and email, run:

git config --global user.name
git config --global user.email

List All Branches:

Shows all available branches in the repository.

git branch

The current branch will have a * next to it.

Create a New Branch:

Creates a new branch without switching to it.

git branch <branch_name>

Branches help in working on different features separately.

Switch to Another Branch:

Moves to an existing branch.

git checkout <branch_name>

Check Commit History:

Shows a list of all previous commits.

git log

Show Changes in Files:

Compares modified files with the last committed version.

git diff

Day 1 - GIT Introduction

Lakshmi Pritha Nadesan — Tue, 25 Feb 2025 00:57:09 +0000

What is version controller?

A version controller, also known as Version Control System (VCS), is a tool that helps manage changes to files, code, and documents over time.
It allows multiple people to collaborate on a project, track revisions, and revert to previous versions if needed.

Types of VCS:

There are three main types of Version Control Systems (VCS)

1. Local Version Control System (LVCS)

Tracks changes on a single computer.
Typically uses simple file backups or a local database.

2. Centralized Version Control System (CVCS)

Uses a single central server to store all versions of files.
Developers pull and push changes from/to the server.

3. Distributed Version Control System (DVCS)

Each user has a full copy of the entire repository, including history.
Changes can be committed locally and later pushed to a remote repository.

Introduction to Git:

Git is a distributed version control system used for tracking changes in source code during software development.
It allows multiple developers to work on the same project efficiently while keeping track of modifications.

History of Git:
Pre-Git Era: Using BitKeeper
Before 2005, the Linux community used BitKeeper, a commercial version control system.
In 2005, a dispute led to BitKeeper revoking free access, pushing the need for an alternative.

Creation of Git (2005)
Linus Torvalds developed Git in April 2005 within a few weeks.
Developers rapidly adopted Git in open-source projects.
By 2008, major platforms like GitHub emerged, making Git the go-to system.

Why Use Git?

Version Control: Keeps track of every change made to files.
Collaboration: Multiple developers can work on a project simultaneously.
Branching & Merging: Work on new features without affecting the main codebase.
Backup & Recovery:Prevents data loss with repositories stored locally and remotely.

what is GitLab?

GitLab is a web-based DevOps platform that provides Git repository management, CI/CD (Continuous Integration & Deployment), issue tracking, and security in one integrated solution.
It is an alternative to GitHub and Bitbucket, designed for teams that want full control over their code and development workflow.

Basic Terminology of version:

Working Directory (Local Files)

The working directory is where you create, edit, and manage project files on your local machine.
These files are not yet tracked by Git until you add them to the staging area.

Staging Area (Index)

The staging area is where you add selected changes before committing them.
It acts as a preparation step, allowing you to decide which files should be included in the next commit.

Local Repository

Once the changes are staged, they need to be committed to the local repository.
A commit is like a checkpoint, allowing you to track changes over time.

Remote Repository (GitLab)

After committing changes locally, you need to push them to a remote repository on GitLab.
This allows collaboration with other developers.

Git vs GitLab:

Installation of git in linux:

Run the command in terminal

sudo apt install git

To verify whether installed and check the version of git:

git --version

Task 5 - Database

Lakshmi Pritha Nadesan — Tue, 18 Feb 2025 16:57:39 +0000

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.

Task 4 - Database

Lakshmi Pritha Nadesan — Tue, 18 Feb 2025 09:31:00 +0000

Create customer table:

create table customer(customer_id int primary key,
first_name text, 
last_name text, 
email text,
city text, 
state text);

CREATE TABLE

INSERT INTO customer (customer_id, first_name, last_name, email, city, state) VALUES 
(101, 'Arun', 'Kumar', 'arjun@gmail.com', 'Chennai', 'Tamil Nadu'),(102, 'Mani', 'Kandan', 'mani@yahoo.com', 'Coimbatore', 'Tamil Nadu'),
(103, 'Ravi', 'Shankar', 'ravi@yahoo.com', 'Madurai', 'Tamil Nadu'),(104, 'Neha', 'Sharma', 'neha@gmail.com', 'Chennai', 'Tamil Nadu'),(105, 'Vickram', 'Kumar', 'vikram@gmail.com', 'Trichy', 'Tamil Nadu');

INSERT 0 5

select * from customer;

 customer_id | first_name | last_name |      email       |    city    |   state    
-------------+------------+-----------+------------------+------------+------------
         101 | Arun       | Kumar     | arjun@gmail.com  | Chennai    | Tamil Nadu
         102 | Mani       | Kandan    | mani@yahoo.com   | Coimbatore | Tamil Nadu
         103 | Ravi       | Shankar   | ravi@yahoo.com   | Madurai    | Tamil Nadu
         104 | Neha       | Sharma    | neha@gmail.com   | Chennai    | Tamil Nadu
         105 | Vickram    | Kumar     | vikram@gmail.com | Trichy     | Tamil Nadu
(5 rows)

create orders table:

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date DATE,
    amount INTEGER,
    product_name TEXT,
    product_category TEXT,
    status TEXT,
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ON DELETE CASCADE
);

CREATE TABLE

insert into orders (order_id,customer_id, order_date, amount, product_name, product_category, status) values
(1001,101,'05-01-2024', 25000,'Laptop', 'Electronics', 'Shipped'),(1002,101,'10-01-2024', 5000,'Smartphone', 'Electronics', 'Shipped'),
(1003,102,'12-01-2024', 15000,'Chair', 'Furniture', 'Pending'),(1004,103,'15-01-2024', 20000,'Table', 'Furniture', 'Delivered'),(1005,101,'01-02-2024', 12000,'Note&Book', 'Book$stationery', 'Shipped'),
(1006,104,'05-02-2024', 8000,'Sofa', 'Furniture', 'Shipped'),(1007,102,'15-02-2024', 6000,'Smartphone', 'Electronics', 'Delivered');
(1008,104,'25-02-2024', 12000,'Desk', 'Furniture', 'Delivered')

INSERT 0 7

select * from orders;

 order_id | customer_id | order_date | amount | product_name | product_category |  status   
----------+-------------+------------+--------+--------------+------------------+-----------
     1001 |         101 | 2024-01-05 |  25000 | Laptop       | Electronics      | Shipped
     1002 |         101 | 2024-01-10 |   5000 | Smartphone   | Electronics      | Shipped
     1003 |         102 | 2024-01-12 |  15000 | Chair        | Furniture        | Pending
     1004 |         103 | 2024-01-15 |  20000 | Table        | Furniture        | Delivered
     1005 |         101 | 2024-02-01 |  12000 | Note&Book    | Book$stationery  | Shipped
     1006 |         104 | 2024-02-05 |   8000 | Sofa         | Furniture        | Shipped
     1007 |         102 | 2024-02-15 |   6000 | Smartphone   | Electronics      | Delivered
     1008 |         104 | 2024-02-25 |  12000 | Desk         | Furniture        | Delivered
(8 rows)

Create product table:

CREATE TABLE product (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT,
    unit_price INTEGER,
    category TEXT,
    manufacturer TEXT,
    stock_quantity INTEGER,
    order_id INTEGER,
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE
);

CREATE TABLE

insert into product (product_id,product_name, unit_price, category, manufacturer, stock_quantity, order_id) values
(201,'Laptop', 50000,'Electronics', 'Brand A',10,1001),(202,'Smartphone', 15000,'Electronics', 'Brand B',15,1002),
(203,'Chair', 3000,'Furniture', 'Brand C',20,1003),
(204,'Table', 8000,'Furniture', 'Brand D',25,1004),
(205,'Sofa', 15000,'Furniture', 'Brand E',5,1006),
(206,'Note&Book', 550,'Book&Stationery', 'Brand G',8,1005),
(207,'Desk', 12000,'Furniture', 'Brand F',12,1008);

INSERT 0 7

select* from product;

 product_id | product_name | unit_price |    category     | manufacturer | stock_quantity | order_id 
------------+--------------+------------+-----------------+--------------+----------------+----------
        201 | Laptop       |      50000 | Electronics     | Brand A      |             10 |     1001
        202 | Smartphone   |      15000 | Electronics     | Brand B      |             15 |     1002
        203 | Chair        |       3000 | Furniture       | Brand C      |             20 |     1003
        204 | Table        |       8000 | Furniture       | Brand D      |             25 |     1004
        205 | Sofa         |      15000 | Furniture       | Brand E      |              5 |     1006
        206 | Note&Book    |        550 | Book&Stationery | Brand G      |              8 |     1005
        207 | Desk         |      12000 | Furniture       | Brand F      |             12 |     1008
(7 rows)

1. Write a DDL query to create the Orders table with appropriate columns give above:

select * from orders;

 order_id | customer_id | order_date | amount | product_name | product_category |  status   
----------+-------------+------------+--------+--------------+------------------+-----------
     1001 |         101 | 2024-01-05 |  25000 | Laptop       | Electronics      | Shipped
     1002 |         101 | 2024-01-10 |   5000 | Smartphone   | Electronics      | Shipped
     1003 |         102 | 2024-01-12 |  15000 | Chair        | Furniture        | Pending
     1004 |         103 | 2024-01-15 |  20000 | Table        | Furniture        | Delivered
     1005 |         101 | 2024-02-01 |  12000 | Note&Book    | Book$stationery  | Shipped
     1006 |         104 | 2024-02-05 |   8000 | Sofa         | Furniture        | Shipped
     1007 |         102 | 2024-02-15 |   6000 | Smartphone   | Electronics      | Delivered
     1008 |         104 | 2024-02-25 |  12000 | Desk         | Furniture        | Delivered
(8 rows)

2. Write a DDL query to add a new column “Phone_Number” to the Customers table:

alter table customer add phone_number int;

ALTER TABLE

select * from customer;

 customer_id | first_name | last_name |      email       |    city    |   state    | phone_number 
-------------+------------+-----------+------------------+------------+------------+--------------
         101 | Arun       | Kumar     | arjun@gmail.com  | Chennai    | Tamil Nadu |             
         102 | Mani       | Kandan    | mani@yahoo.com   | Coimbatore | Tamil Nadu |             
         103 | Ravi       | Shankar   | ravi@yahoo.com   | Madurai    | Tamil Nadu |             
         104 | Neha       | Sharma    | neha@gmail.com   | Chennai    | Tamil Nadu |             
         105 | Vickram    | Kumar     | vikram@gmail.com | Trichy     | Tamil Nadu |             
(5 rows)

3. Write a DDL query to Insert a new customer into the Customers table:

INSERT INTO customer (customer_id, first_name, last_name, email, city, state)                                                                         VALUES  (106, 'Lakshmi', 'Pritha', 'pritha@gmail.com', 'Chennai', 'Tamil Nadu');

INSERT 0 1

select * from customer;

 customer_id | first_name | last_name |      email       |    city    |   state    | phone_number 
-------------+------------+-----------+------------------+------------+------------+--------------
         101 | Arun       | Kumar     | arjun@gmail.com  | Chennai    | Tamil Nadu |             
         102 | Mani       | Kandan    | mani@yahoo.com   | Coimbatore | Tamil Nadu |             
         103 | Ravi       | Shankar   | ravi@yahoo.com   | Madurai    | Tamil Nadu |             
         104 | Neha       | Sharma    | neha@gmail.com   | Chennai    | Tamil Nadu |             
         105 | Vickram    | Kumar     | vikram@gmail.com | Trichy     | Tamil Nadu |             
         106 | Lakshmi    | Pritha    | pritha@gmail.com | Chennai    | Tamil Nadu |             
(6 rows)

4. Write a query to Count the number of orders placed by each customer in the Orders table:

select customer_id, count(order_id) from orders group by customer_id order by customer_id ;

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

5. Retrieve all order details from order table with an amount not less than 10,000 and not greater than 25,000:

select * from orders where amount between 10000 and 25000;

 order_id | customer_id | order_date | amount | product_name | product_category |  status   
----------+-------------+------------+--------+--------------+------------------+-----------
     1001 |         101 | 2024-01-05 |  25000 | Laptop       | Electronics      | Shipped
     1003 |         102 | 2024-01-12 |  15000 | Chair        | Furniture        | Pending
     1004 |         103 | 2024-01-15 |  20000 | Table        | Furniture        | Delivered
     1005 |         101 | 2024-02-01 |  12000 | Note&Book    | Book$stationery  | Shipped
     1008 |         104 | 2024-02-25 |  12000 | Desk         | Furniture        | Delivered
(5 rows)

6. Write a DML query to Update the city of “customer_id” 101 to “Mumbai”:

update customer  set city='Mumbai' where customer_id=101;

UPDATE 1

select * from customer;

 customer_id | first_name | last_name |      email       |    city    |   state    | phone_number 
-------------+------------+-----------+------------------+------------+------------+--------------
         102 | Mani       | Kandan    | mani@yahoo.com   | Coimbatore | Tamil Nadu |             
         103 | Ravi       | Shankar   | ravi@yahoo.com   | Madurai    | Tamil Nadu |             
         104 | Neha       | Sharma    | neha@gmail.com   | Chennai    | Tamil Nadu |             
         105 | Vickram    | Kumar     | vikram@gmail.com | Trichy     | Tamil Nadu |             
         106 | Lakshmi    | Pritha    | pritha@gmail.com | Chennai    | Tamil Nadu |             
         101 | Arun       | Kumar     | arjun@gmail.com  | Mumbai     | Tamil Nadu |             
(6 rows)

7. Write a DML query to Delete the customer with “Customer_ID” 105:

delete from customer where customer_id=105;

DELETE 1

select * from customer;

 customer_id | first_name | last_name |      email       |    city    |   state    | phone_number 
-------------+------------+-----------+------------------+------------+------------+--------------
         102 | Mani       | Kandan    | mani@yahoo.com   | Coimbatore | Tamil Nadu |             
         103 | Ravi       | Shankar   | ravi@yahoo.com   | Madurai    | Tamil Nadu |             
         104 | Neha       | Sharma    | neha@gmail.com   | Chennai    | Tamil Nadu |             
         106 | Lakshmi    | Pritha    | pritha@gmail.com | Chennai    | Tamil Nadu |             
         101 | Arun       | Kumar     | arjun@gmail.com  | Mumbai     | Tamil Nadu |             
(5 rows)

8. Retrieve allproducts from the “Product Table” that have never been ordered:

SELECT product.product_id, product.product_name
FROM product
LEFT JOIN orders  ON product.order_id = orders.order_id
WHERE orders.order_id IS  NULL;

 product_id | product_name 
------------+--------------
(0 rows)

9. Retrieve all customers details who llive in Chennai and have made an order value more than 20,000:

select customer.customer_id, customer.first_name, customer.last_name, customer.email, customer.city, customer.state from customer left join orders on customer.customer_id=orders.customer_id where customer.city='Chennai' and orders.amount> 20000; 

 customer_id | first_name | last_name | email | city | state 
-------------+------------+-----------+-------+------+-------
(0 rows)

10. Retrieve the “Customer_ID” and total Number of order count for each customer who has placed more than two orders:

select customer_id, count(order_id) from orders group by customer_id having count(order_id)>2; 

 customer_id | count 
-------------+-------
         101 |     3
(1 row)

11. Retrieve the top 5 customers based on the number of orders they have placed, along with their “First_name” and “email addresses”:

select customer.customer_id,customer.first_name, customer.email, count(order_id) from customer join orders on customer.customer_id= orders.customer_id group by customer.customer_id order by count desc limit 5;

 customer_id | first_name |      email       | count 
-------------+------------+------------------+-------
         101 | Arun       | arjun@gmail.com  |     3
         104 | Neha       | neha@gmail.com   |     2
         102 | Mani       | mani@yahoo.com   |     2
         103 | Ravi       | ravi@yahoo.com   |     1
         106 | Lakshmi    | pritha@gmail.com |     0
(5 rows)

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

select customer.customer_id, customer.first_name, customer.last_name, customer.email, customer.city, customer.state from customer join orders on customer.customer_id=orders.customer_id where order_date between '01-01-2024' and '31-01-2024';

 customer_id | first_name | last_name |      email      |    city    |   state    
-------------+------------+-----------+-----------------+------------+------------
         102 | Mani       | Kandan    | mani@yahoo.com  | Coimbatore | Tamil Nadu
         103 | Ravi       | Shankar   | ravi@yahoo.com  | Madurai    | Tamil Nadu
         101 | Arun       | Kumar     | arjun@gmail.com | Mumbai     | Tamil Nadu
         101 | Arun       | Kumar     | arjun@gmail.com | Mumbai     | Tamil Nadu
(4 rows)

13. Find the total number of orders placed and the average “order amount” for each city:

select customer.city, count(order_id), avg(round(amount)) from customer join orders on customer.customer_id=orders.customer_id group by customer.city;

    city    | count |  avg  
------------+-------+-------
 Mumbai     |     3 | 14000
 Chennai    |     2 | 10000
 Coimbatore |     2 | 10500
 Madurai    |     1 | 20000
(4 rows)

14. Identity the products which are ordered on weekends, And the customer details who had ordered them:

select orders.product_name, orders.order_date, customer.customer_id, customer.first_name, customer.last_name, customer.email, customer.city, customer.state from customer join orders on customer.customer_id=orders.customer_id WHERE EXTRACT(DOW FROM orders.order_date) IN (0, 6);

 product_name | order_date | customer_id | first_name | last_name |     email      |  city   |   state    
--------------+------------+-------------+------------+-----------+----------------+---------+------------
 Desk         | 2024-02-25 |         104 | Neha       | Sharma    | neha@gmail.com | Chennai | Tamil Nadu
(1 row)

15. Find the most expensive product in the along with the price in the Products table and the customer details(Customer_ID,First_Name) who have ordered it:

select product.product_name, product.unit_price, customer.customer_id, customer.first_name from product join orders on product.order_id=orders.order_id join customer on orders.customer_id=customer.customer_id order by product.unit_price desc limit 1;

 product_name | unit_price | customer_id | first_name 
--------------+------------+-------------+------------
 Laptop       |      50000 |         101 | Arun
(1 row)

16. List all customers along with the count of their orders in the format “Customer (Order Count)”. Example Output: ARUN Kumar (3):

select concat(customer.first_name,' ', customer.last_name, '(', count(orders.order_id), ')' ) from customer join orders on customer.customer_id=orders.customer_id group by customer.customer_id order by customer.first_name;
     concat      
-----------------
 Arun Kumar(3)
 Mani Kandan(2)
 Neha Sharma(2)
 Ravi Shankar(1)
(4 rows)

17. Write an SQL query to select all customers who live in Chennai and have ordered the second most expensive product:

select customer.customer_id, customer.first_name, customer.last_name, customer.email from customer join orders on customer.customer_id=orders.customer_id join product on product.order_id=orders.order_id where customer.city='Chennai' and product.unit_price=(select distinct unit_price from product order by unit_price desc limit 1 offset 1);

 customer_id | first_name | last_name |     email      
-------------+------------+-----------+----------------
         104 | Neha       | Sharma    | neha@gmail.com
(1 row)

18. Get the total revenue generated from each customer, and label the customer as ‘High Spender’ if they have spent more than 50,000, otherwise ‘Low Spender’:

select customer.customer_id, customer.first_name,customer.last_name, sum(orders.amount) as totalrevenue, case when sum(orders.amount) > 50000 then 'High Spender' else 'Low Spender' end from customer join orders on customer.customer_id= orders.customer_id group by customer.customer_id order by totalrevenue desc;

 customer_id | first_name | last_name | totalrevenue |    case     
-------------+------------+-----------+--------------+-------------
         101 | Arun       | Kumar     |        42000 | Low Spender
         102 | Mani       | Kandan    |        21000 | Low Spender
         103 | Ravi       | Shankar   |        20000 | Low Spender
         104 | Neha       | Sharma    |        20000 | Low Spender
(4 rows)

19. Retrieve the total number of orders placed by each customer for each month in the year:

select customer.customer_id,customer.first_name, customer. last_name, EXTRACT(month from orders.order_date) as ordermonth, EXTRACT(year from orders.order_date) as orderyear, count(order_id) as total from customer join orders on customer.customer_id = orders.customer_id GROUP BY customer.customer_id, EXTRACT(month from orders.order_date), EXTRACT(year from orders.order_date) ORDER BY customer.customer_id, orderyear, ordermonth;

 customer_id | first_name | last_name | ordermonth | orderyear | total 
-------------+------------+-----------+------------+-----------+-------
         101 | Arun       | Kumar     |          1 |      2024 |     2
         101 | Arun       | Kumar     |          2 |      2024 |     1
         102 | Mani       | Kandan    |          1 |      2024 |     1
         102 | Mani       | Kandan    |          2 |      2024 |     1
         103 | Ravi       | Shankar   |          1 |      2024 |     1
         104 | Neha       | Sharma    |          2 |      2024 |     2
(6 rows)

20. Write an SQL query to retrieve all customers who satisfy the following conditions
Condition 1: The city is not either ‘chennai’ or Trichy’.
Condition 2: The email domain is ‘@yahoo.com’.
Condition 3: The customer has at least one order.
Sort them in alphabetical order by last name in descending order, followed by first name in ascending order.

select customer.customer_id,customer.first_name, customer. last_name, customer.city, count(orders.order_id) from customer join orders on customer.customer_id=orders.customer_id where customer.city not in('Chennai','Trichy') and customer.email like '@yahoo.com' GROUP BY customer.customer_id having count(orders.order_id)>=1 ORDER BY customer.last_name desc, customer.first_name asc;

 customer_id | first_name | last_name | city | count 
-------------+------------+-----------+------+-------
(0 rows)

21. Find customers who have not ordered any products in the Furniture category.

select customer.customer_id,customer.first_name, customer. last_name, customer.city from customer where  NOT EXISTS (SELECT 1                                                                                                                                             
FROM orders WHERE orders.customer_id = customer.customer_id
AND orders.product_category = 'Furniture' GROUP BY customer.customer_id);

 customer_id | first_name | last_name |  city   
-------------+------------+-----------+---------
         106 | Lakshmi    | Pritha    | Chennai
         101 | Arun       | Kumar     | Mumbai
(2 rows)

Task 3 - Database

Lakshmi Pritha Nadesan — Sun, 16 Feb 2025 09:22:47 +0000

Create cinema table:

employee=# select * from cinema;

 id |   movie_name    |   actor   | year | minutes 
----+-----------------+-----------+------+---------
  1 | Dharbar         | Rajini    | 2021 |     121
  2 | Vikram          | Kamal     | 2023 |     125
  3 | Mersal          | Vijay     | 2020 |     123
  4 | Beast           | Vijay     | 2019 |     134
  5 | Viswasam        | Ajith     | 2021 |     117
  6 | Attakasam       | Ajith     | 2006 |     119
  7 | Jai Bhim        | Surya     | 2018 |     127
  8 | Kaithi          | Karthi    | 2017 |     125
  9 | Ayothi          | Sasikumar | 2023 |     124
 10 | Deivathirumagan | Vikram    | 2017 |     121
(10 rows)

create c_ratings table:

employee=# create table c_ratings(id int,ImDBRating float,FanRating float,CritiqueRating float);

CREATE TABLE

employee=# insert into c_ratings values(1,7.2,9.1,7.7),(2,8.1,9.3,7.3),(3,6.5,9.2,7.3),(4,6.2,8.7,6),(5,5.1,6.6,6),(6,7.6,8.8,9),(7,8.9,9.7,9.7),(8,4.5,7,6.5),(9,5.3,6.5,6),(10,8.3,8.7,8.2);

INSERT 0 10

employee=# select * from c_ratings;

 id | imdbrating | fanrating | critiquerating 
----+------------+-----------+----------------
  1 |        7.2 |       9.1 |            7.7
  2 |        8.1 |       9.3 |            7.3
  3 |        6.5 |       9.2 |            7.3
  4 |        6.2 |       8.7 |              6
  5 |        5.1 |       6.6 |              6
  6 |        7.6 |       8.8 |              9
  7 |        8.9 |       9.7 |            9.7
  8 |        4.5 |         7 |            6.5
  9 |        5.3 |       6.5 |              6
 10 |        8.3 |       8.7 |            8.2
(10 rows)

1) Find ImDB Rating and Critique Rating for each movie:

employee=# select cinema.movie_name, c_ratings.imdbrating, c_ratings.critiquerating from c_ratings inner join cinema on c_ratings.id=cinema.id;

   movie_name    | imdbrating | critiquerating 
-----------------+------------+----------------
 Dharbar         |        7.2 |            7.7
 Vikram          |        8.1 |            7.3
 Mersal          |        6.5 |            7.3
 Beast           |        6.2 |              6
 Viswasam        |        5.1 |              6
 Attakasam       |        7.6 |              9
 Jai Bhim        |        8.9 |            9.7
 Kaithi          |        4.5 |            6.5
 Ayothi          |        5.3 |              6
 Deivathirumagan |        8.3 |            8.2
(10 rows)

2) Find Movies that have better ImDB rating than critique rating:

employee=# select cinema.movie_name, c_ratings.imdbrating, c_ratings.critiquerating from c_ratings inner join cinema on c_ratings.id=cinema.id where c_ratings.imdbrating > c_ratings.critiquerating;

   movie_name    | imdbrating | critiquerating 
-----------------+------------+----------------
 Vikram          |        8.1 |            7.3
 Beast           |        6.2 |              6
 Deivathirumagan |        8.3 |            8.2
(3 rows)

3) List down all movies based on their ImDB Rating in ascending order:

employee=# select cinema.movie_name,c_ratings.imdbrating from c_ratings inner join cinema on c_ratings.id=cinema.id order by c_ratings.imdbrating;

   movie_name    | imdbrating 
-----------------+------------
 Kaithi          |        4.5
 Viswasam        |        5.1
 Ayothi          |        5.3
 Beast           |        6.2
 Mersal          |        6.5
 Dharbar         |        7.2
 Attakasam       |        7.6
 Vikram          |        8.1
 Deivathirumagan |        8.3
 Jai Bhim        |        8.9
(10 rows)

4) List down all movies for which ImDB rating and Fan Rating are greater than 8:

employee=# select cinema.movie_name, c_ratings.imdbrating, c_ratings.fanrating from c_ratings inner join cinema on c_ratings.id=cinema.id where c_ratings.imdbrating>8 and  c_ratings.fanrating>8;

   movie_name    | imdbrating | fanrating 
-----------------+------------+-----------
 Vikram          |        8.1 |       9.3
 Jai Bhim        |        8.9 |       9.7
 Deivathirumagan |        8.3 |       8.7
(3 rows)

5) List down all movies released in the year 2017,2018 and 2019 and have >8 as ImDB Value:

employee=# select cinema.movie_name,cinema.year, c_ratings.imdbrating from c_ratings inner join cinema on c_ratings.id=cinema.id where c_ratings.imdbrating>8 and cinema.year between 2017 and 2019;

   movie_name    | year | imdbrating 
-----------------+------+------------
 Jai Bhim        | 2018 |        8.9
 Deivathirumagan | 2017 |        8.3
(2 rows)

6) List down all movies for which actor name contains the letter ‘j’ and have ImDB rating (>8):

employee=# select cinema.movie_name,cinema.actor, c_ratings.imdbrating from c_ratings inner join cinema on c_ratings.id=cinema.id where c_ratings.imdbrating>8 and cinema.actor='%j%';

 movie_name | actor | imdbrating 
------------+-------+------------
(0 rows)

7) List down all movies with less than 7 ImDB and Critique rating released between 2010 – 2020:

employee=# select cinema.movie_name,cinema.year, c_ratings.imdbrating,c_ratings.critiquerating from c_ratings inner join cinema on c_ratings.id=cinema.id where (c_ratings.imdbrating<7 and c_ratings.critiquerating<7) and (cinema.year between 2010 and 2020);

 movie_name | year | imdbrating | critiquerating 
------------+------+------------+----------------
 Beast      | 2019 |        6.2 |              6
 Kaithi     | 2017 |        4.5 |            6.5
(2 rows)

8) List down all movies with less than 120 Minutes and have Fan Rating greater than 8.5:

employee=# select cinema.movie_name, cinema.minutes, c_ratings.fanrating from c_ratings inner join cinema on c_ratings.id=cinema.id where c_ratings.fanrating>8.5  and cinema.minutes<120;

 movie_name | minutes | fanrating 
------------+---------+-----------
 Attakasam  |     119 |       8.8
(1 row)

9) List down all movies based on their ImDB Rating in descending order and year in ascending:

employee=# select cinema.movie_name,cinema.year,c_ratings.imdbrating from c_ratings inner join cinema on c_ratings.id=cinema.id order by c_ratings.imdbrating desc,cinema.year asc;

   movie_name    | year | imdbrating 
-----------------+------+------------
 Jai Bhim        | 2018 |        8.9
 Deivathirumagan | 2017 |        8.3
 Vikram          | 2023 |        8.1
 Attakasam       | 2006 |        7.6
 Dharbar         | 2021 |        7.2
 Mersal          | 2020 |        6.5
 Beast           | 2019 |        6.2
 Ayothi          | 2023 |        5.3
 Viswasam        | 2021 |        5.1
 Kaithi          | 2017 |        4.5
(10 rows)

10) List down all movies where both Actor name and Movie name starts with same letter with their ImDB value in descending order:

employee=# select cinema.movie_name, cinema.actor, c_ratings.imdbrating from c_ratings inner join cinema on c_ratings.id=cinema.id where left(cinema.actor,1)=left(cinema.movie_name,1) order by c_ratings.imdbrating desc;

 movie_name | actor  | imdbrating 
------------+--------+------------
 Attakasam  | Ajith  |        7.6
 Kaithi     | Karthi |        4.5
(2 rows)

Task 2 : Database

Lakshmi Pritha Nadesan — Thu, 13 Feb 2025 12:05:12 +0000

Create cinema table for storing movie information:

employee=# create table cinema(id int,movie_name varchar(20), actor varchar(20),year int,minutes int);
CREATE TABLE

Insert movie details into movie table:

employee=# insert into cinema values(1,'Dharbar','Rajini',2021,121),(2,'Vikram','Kamal',2023,125),(3,'Mersal','Vijay',2020,123),(4,'Beast','Vijay',2019,134),(5,'Viswasam','Ajith',2021,117),(6,'Attakasam','Ajith',2006,119),(7,'Jai Bhim','Surya',2018,127),(8,'Kaithi','Karthi',2017,125),(9,'Ayothi','Sasikumar',2023,124),(10,'Deivathirumagan','Vikram',2017,121);
INSERT 0 10

employee=# select * from cinema;
 id |   movie_name    |   actor   | year | minutes 
----+-----------------+-----------+------+---------
  1 | Dharbar         | Rajini    | 2021 |     121
  2 | Vikram          | Kamal     | 2023 |     125
  3 | Mersal          | Vijay     | 2020 |     123
  4 | Beast           | Vijay     | 2019 |     134
  5 | Viswasam        | Ajith     | 2021 |     117
  6 | Attakasam       | Ajith     | 2006 |     119
  7 | Jai Bhim        | Surya     | 2018 |     127
  8 | Kaithi          | Karthi    | 2017 |     125
  9 | Ayothi          | Sasikumar | 2023 |     124
 10 | Deivathirumagan | Vikram    | 2017 |     121
(10 rows)

1) List down 8th row values from the table:

employee=# select * from cinema limit 1  offset 7;
 id | movie_name | actor  | year | minutes 
----+------------+--------+------+---------
  8 | Kaithi     | Karthi | 2017 |     125
(1 row)

2) List down all the movies released between 2021 and 2023:

employee=# select movie_name from cinema where year between 2021 and 2023;
 movie_name 
------------
 Dharbar
 Vikram
 Viswasam
 Ayothi
(4 rows)

3) List down all the movies released not between 2021 and 2023:

employee=# select movie_name from cinema where year not between 2021 and 2023;
   movie_name    
-----------------
 Mersal
 Beast
 Attakasam
 Jai Bhim
 Kaithi
 Deivathirumagan
(6 rows)

4) List down first 3 movies based on released year in descending order:

employee=# select movie_name from cinema order by year desc limit 3 ;
 movie_name 
------------
 Vikram
 Ayothi
 Dharbar
(3 rows)

5) List down All movies by Vijay in the year 2020:

employee=# select movie_name from cinema where actor='vijay' or year=2020;
 movie_name 
------------
 Mersal
(1 row)

6) List down all movies where we have ‘as’ in the movie name:

employee=# select movie_name from cinema where movie_name like '%as%'; 
 movie_name 
------------
 Beast
 Viswasam
 Attakasam
(3 rows)

7) List down all actor names without duplicates:

employee=# select distinct actor from cinema;
   actor   
-----------
 Sasikumar
 Kamal
 Vijay
 Karthi
 Surya
 Ajith
 Rajini
 Vikram
(8 rows)

8) List down Ajith movies in alphabetical order:

employee=# select movie_name from cinema where  actor='Ajith' order by movie_name;
 movie_name 
------------
 Attakasam
 Viswasam
(2 rows)

9) List down movies where their names start with ‘A’ and actor name starts with ‘A’:

employee=# select movie_name from cinema where actor like 'A%' and movie_name like 'A%';
 movie_name 
------------
 Attakasam
(1 row)

10) List down movies if the movie name is ‘Vikram’ or the actor name is ‘Vikram’:

employee=# select movie_name from cinema where actor='Vikram' or movie_name='Vikram';
   movie_name    
-----------------
 Vikram
 Deivathirumagan
(2 rows)

Task : Database

Lakshmi Pritha Nadesan — Wed, 12 Feb 2025 12:02:52 +0000

Create cinema table for storing movie information:

employee=# create table cinema(id int,movie_name varchar(20), actor varchar(20),year int,minutes int);
CREATE TABLE

Insert movie details into movie table:

employee=# insert into cinema values(1,'Dharbar','Rajini',2021,121),(2,'Vikram','Kamal',2023,125),(3,'Mersal','Vijay',2020,123),(4,'Beast','Vijay',2019,134),(5,'Viswasam','Ajith',2021,117),(6,'Attakasam','Ajith',2006,119),(7,'Jai Bhim','Surya',2018,127),(8,'Kaithi','Karthi',2017,125),(9,'Ayothi','Sasikumar',2023,124),(10,'Deivathirumagan','Vikram',2017,121);
INSERT 0 10

employee=# select * from cinema;
 id |   movie_name    |   actor   | year | minutes 
----+-----------------+-----------+------+---------
  1 | Dharbar         | Rajini    | 2021 |     121
  2 | Vikram          | Kamal     | 2023 |     125
  3 | Mersal          | Vijay     | 2020 |     123
  4 | Beast           | Vijay     | 2019 |     134
  5 | Viswasam        | Ajith     | 2021 |     117
  6 | Attakasam       | Ajith     | 2006 |     119
  7 | Jai Bhim        | Surya     | 2018 |     127
  8 | Kaithi          | Karthi    | 2017 |     125
  9 | Ayothi          | Sasikumar | 2023 |     124
 10 | Deivathirumagan | Vikram    | 2017 |     121
(10 rows)

1) List down all the movies from the year 2018:

employee=# select movie_name from cinema where year=2018;
 movie_name 
------------
 Jai Bhim
(1 row)

2) List down all the movies where their minutes greater than 120:

employee=# select movie_name from cinema where minutes>120;
   movie_name    
-----------------
 Dharbar
 Vikram
 Mersal
 Beast
 Jai Bhim
 Kaithi
 Ayothi
 Deivathirumagan
(8 rows)

3) List down all Vijay and Ajith movies:

employee=# select movie_name from cinema where actor='Vijay' or actor='Ajith';
 movie_name 
------------
 Mersal
 Beast
 Viswasam
 Attakasam
(4 rows)

4) List down all movies where their name’s last letter is ‘i’:

employee=# select movie_name from cinema where movie_name like '%i';
 movie_name 
------------
 Kaithi
 Ayothi
(2 rows)

5) List down all movies where actor name length is greater than 5:

employee=# select movie_name from cinema where length(actor)>5;
   movie_name    
-----------------
 Dharbar
 Kaithi
 Ayothi
 Deivathirumagan
(4 rows)

6) List down all movies where actor name length is greater than 5 and release after 2020:

employee=# select movie_name from cinema where length(actor)>5 and year>2020;
 movie_name 
------------
 Dharbar
 Ayothi
(2 rows)

7) List down all movies where their names start with a or b or c or d:

employee=# select movie_name from cinema where movie_name like 'A%' or movie_name like 'B%' or movie_name like 'C%' or movie_name like 'D%';
   movie_name    
-----------------
 Dharbar
 Beast
 Attakasam
 Ayothi
 Deivathirumagan
(5 rows)

8) List down all movies acted by Vikram:

employee=# select movie_name from cinema where actor='Vikram';
   movie_name    
-----------------
 Deivathirumagan
(1 row)

9) List down all movies released in 2017 or acted by Rajini:

employee=# select movie_name from cinema where year=2017 or actor='Rajini';
   movie_name    
-----------------
 Dharbar
 Kaithi
 Deivathirumagan
(3 rows)

10) List down all movies released after 2019 and acted by either Sasikumar or Vijay:

employee=# select movie_name from cinema where year>2019 and actor='Sasikumar' or actor='Vijay';
 movie_name 
------------
 Mersal
 Beast
 Ayothi
(3 rows)

Day 38 - Primary key, Foreign key in Database, Types of SQL Languages

Lakshmi Pritha Nadesan — Wed, 12 Feb 2025 01:41:23 +0000

Primary key:

A primary key is a column (or a set of columns) in a database table that uniquely identifies each record in that table.

*Must be unique (no duplicate values).
*cannot have NULL values.
*Each table can have only one primary key.

Create a Table for Storing Book Information using primary key:

movie=# create table book 
(book_id integer PRIMARY KEY, name text, author text);
CREATE TABLE

Create a Table for Citizens with Aadhar and PAN Information as a primary key:

movie=# create table citizens(aadhar_no integer, pan_no text, name text, primary key(aadhar_no, pan_no));
CREATE TABLE

Insert a New Citizen Record into the Citizens Table:

movie=# insert into citizens values(123412,'g1234t','pritha');
INSERT 0 1

GENERATED ALWAYS AS IDENTITY:

The GENERATED ALWAYS AS IDENTITY clause is used to create an auto-incrementing column in SQL, which automatically generates a unique value for the column whenever a new row is inserted into the table.

Create a Customers Table with Auto-Incrementing Customer ID:

movie=# create table customers(customer_id int GENERATED ALWAYS AS IDENTITY, customer_name text not null, primary key(customer_id));
CREATE TABLE

Insert Multiple Customer Records into the Customers Table:

movie=# insert into customers(customer_name) values('guru'),('pritha'),('pritha'),('muthu');
INSERT 0 4

movie=# select * from customers;
 customer_id | customer_name 
-------------+---------------
           1 | guru
           2 | pritha
           3 | pritha
           4 | muthu
(4 rows)

Foreign key:

A Foreign Key is a column or a set of columns that establishes a relationship between two tables by referencing the Primary Key of another table.

Create a Contacts Table Linked to Customers:

movie=# CREATE TABLE contacts
(contact_id int GENERATED ALWAYS AS IDENTITY, 
customer_id int, 
contact_name text not null, 
mobile int, 
email text, 
PRIMARY KEY (contact_id), 
CONSTRAINT fk_customer  FOREIGN KEY(customer_id) REFERENCES customers(CUSTOMER_ID) 
on DELETE CASCADE
);
CREATE TABLE

Foreign Key (customer_id): Links each contact to a specific customer from the customers table. This establishes a relationship between the two tables.

ON DELETE CASCADE:If a record in the customers table is deleted, all corresponding records in the contacts table will also be deleted automatically.

Insert Contact Records Linked to Customers:

movie=# insert into contacts(customer_id, contact_name, mobile, email) values (1,'Guru', 1234, 'guru@guru.com'), (2, 'Pritha', 2345, 'lakshmi@pritha.com');
INSERT 0 2

movie=# select * from customers;
 customer_id | customer_name 
-------------+---------------
           1 | guru
           2 | pritha
           3 | pritha
           4 | muthu
(4 rows)

movie=# select * from contacts;
 contact_id | customer_id | contact_name | mobile |       email        
------------+-------------+--------------+--------+--------------------
          1 |           1 | Guru         |   1234 | guru@guru.com
          2 |           2 | Pritha       |   2345 | lakshmi@pritha.com
(2 rows)

Delete a Customer Record and Associated Contacts:

movie=# delete from customers where customer_id=1;
DELETE 1

movie=# select * from contacts;
 contact_id | customer_id | contact_name | mobile |       email        
------------+-------------+--------------+--------+--------------------
          2 |           2 | Pritha       |   2345 | lakshmi@pritha.com
(1 row)

Create a Students Table with Auto-Incrementing Student ID:

movie=# create table students_2
(student_id SERIAL primary key, 
name varchar(20) not null, 
class int);
CREATE TABLE

SERIAL: The student_id column is set as a SERIAL data type, which automatically increments with each new row. It behaves like an auto-incrementing integer column.

Create a Courses Table with a Foreign Key to Students:

movie=# create table courses
(course_id SERIAL primary key, 
student_id int, 
course_name text, 
FOREIGN KEY (student_id) REFERENCES students_2(student_id) on DELETE CASCADE);
CREATE TABLE

Insert Multiple Student Records into the Students Table:

movie=# insert into students_2 values(101,'guru',4),(102,'pritha',5),(103,'kuhan',3);
INSERT 0 3

Insert Multiple course Records into the courses Table:

movie=# insert into courses(student_id, course_name) values(101, 'Karate'), (102,'Kung fu'), (103,'Yoga');
INSERT 0 3

Delete a Student Record from the Students Table:

movie=# delete from students_2 where student_id = 102;
DELETE 1

movie=# select * from courses;
 course_id | student_id | course_name 
-----------+------------+-------------
         4 |        101 | Karate
         6 |        103 | Yoga
(2 rows)

Delete a course Record from the courses Table:

movie=# delete from courses where course_id = 4;
DELETE 1

movie=# select * from courses;
 course_id | student_id | course_name 
-----------+------------+-------------
         4 |        101 | Karate
         6 |        103 | Yoga
(2 rows)

Create an Employee Table with Employee Details:

movie=# create table emp(empno numeric, empname text, joining_date date, dept_no numeric);
CREATE TABLE

Insert Employee Record into the Employee Table:

movie=# insert into emp values(101, 'Raja', '26-Feb-2025', 1);
INSERT 0 1

Add a Salary Column to the Employee Table:

movie=# alter table emp add salary int;
ALTER TABLE

movie=# select * from emp;
 empno | empname | joining_date | dept_no | salary 
-------+---------+--------------+---------+--------
   101 | Raja    | 2025-02-26   |       1 |       
(1 row)

Drop the Department Number Column from the Employee Table:

movie=# alter table emp drop column dept_no;
ALTER TABLE

movie=# select * from emp;
 empno | empname | joining_date | salary 
-------+---------+--------------+--------
   101 | Raja    | 2025-02-26   |       
(1 row)

Alter Column Data Type for Employee Name in the Employee Table:

movie=# alter table emp alter column empname TYPE varchar(30);
ALTER TABLE

movie=# select * from emp;
 empno | empname | joining_date | salary 
-------+---------+--------------+--------
   101 | Raja    | 2025-02-26   |       
(1 row)

Update Employee Salary in the Employee Table:

movie=# update emp set salary = 25000 where empno = 101;
UPDATE 1

movie=# select * from emp;
 empno | empname | joining_date | salary 
-------+---------+--------------+--------
   101 | Raja    | 2025-02-26   |  25000
(1 row)

Remove All Records from the Employee Table:

movie=# truncate table emp;
TRUNCATE TABLE

movie=# select * from emp;
 empno | empname | joining_date | salary 
-------+---------+--------------+--------
(0 rows)

Drop the Employee Table from the Database:

movie=# drop table emp;
DROP TABLE

movie=# select * from emp;
ERROR:  relation "emp" does not exist
LINE 1: select * from emp;
                      ^

view:

A view in SQL is a virtual table based on the result of a query. It contains rows and columns just like a real table, but it does not store data physically.

Instead, the data is dynamically generated when the view is accessed, based on the query it is defined with.

movie=# create view emp_view as 
select * from student_2;
CREATE VIEW

movie=# select * from student_2;
 student_id | name | class  
------------+------+--------
        101 | abcd |  23132
        102 | sds  |  32443
        103 | fff  | 234334
(3 rows)

movie=# drop view emp_view;
DROP VIEW

movie=# select * from student_2;
 student_id | name | class  
------------+------+--------
        101 | abcd |  23132
        102 | sds  |  32443
        103 | fff  | 234334
(3 rows)

explain:

The EXPLAIN statement in SQL is used to obtain information about how the database engine executes a query.

It provides a detailed breakdown of the execution plan, including how tables are accessed, which indexes are used, and the order of operations performed by the query planner.

movie=# explain select * from student_2;
                         QUERY PLAN                          
-------------------------------------------------------------
 Seq Scan on student_2  (cost=0.00..18.60 rows=860 width=66)
(1 row)

movie=# explain select * from student_2 where name='abcd';
                        QUERY PLAN                         
-----------------------------------------------------------
 Seq Scan on student_2  (cost=0.00..20.75 rows=4 width=66)
   Filter: ((name)::text = 'abcd'::text)
(2 rows)

Index:

An index in SQL is a database object that improves the speed of data retrieval operations on a table at the cost of additional space and maintenance overhead.

movie=# create index name on student_2(name);
CREATE INDEX

movie=# explain select * from student_2 where name='abcd';
                        QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on student_2  (cost=0.00..1.04 rows=1 width=66)
   Filter: ((name)::text = 'abcd'::text)
(2 rows)

Drop the emp Table if It Exists:

movie=# drop table if exists emp;
NOTICE:  table "emp" does not exist, skipping
DROP TABLE

Rename Column in the student_2 Table:

movie=# alter table student_2 rename column name to Student_name;
ALTER TABLE

movie=# select * from student_2;
 student_id | student_name | class  
------------+--------------+--------
        101 | abcd         |  23132
        102 | sds          |  32443
        103 | fff          | 234334
(3 rows)

Add a CHECK Constraint on the class Column in student_2 Table:

movie=# alter table student_2 add check(class in (23132,32443,234334));
ALTER TABLE

movie=# insert into student_2 values(104,'frferf',23132);
INSERT 0 1

movie=# insert into student_2 values(105,'rferf',3423132);
ERROR:  new row for relation "student_2" violates check constraint "student_2_class_check"
DETAIL:  Failing row contains (105, rferf, 3423132).

SQL:

SQL (Structured Query Language) is a powerful language used to interact with relational databases.

It can be categorized into different types of languages based on the operations and tasks they perform.

1. Data Definition Language (DDL)

DDL is used to define and manage the structure of database objects, such as tables, indexes, and views.

CREATE: Used to create database objects (tables, indexes, views, etc.).
ALTER: Used to modify an existing database object (e.g., adding, deleting, or modifying columns).
DROP: Used to delete a database object (e.g., table, view).
TRUNCATE: Removes all rows from a table without logging individual row deletions.

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

SELECT: Used to retrieve data from one or more tables.
INSERT: Used to add new rows to a table.
UPDATE: Used to modify existing data within a table.
DELETE: Used to remove rows from a table.

3. Data Control Language (DCL)

DCL is used to control access to data in the database. It defines permissions for users and roles.

GRANT: Used to assign privileges to users or roles.
REVOKE: Used to remove privileges from users or roles.

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

COMMIT: Saves all changes made during the current transaction.
ROLLBACK: Undoes changes made during the current transaction.
SAVEPOINT: Sets a point within a transaction to which you can roll back.
SET TRANSACTION: Used to configure the transaction properties.

5. Data Query Language (DQL)

DQL is primarily concerned with querying and retrieving data from the database.

SELECT: Used to retrieve data from one or more tables (this command is also part of DML but mainly falls under DQL because it's used for querying data).

Day 37 - Joins and Constraints in database

Lakshmi Pritha Nadesan — Tue, 11 Feb 2025 01:36:35 +0000

Create a Table for Storing Team Information:

employee=# create table team(team_id int, player_id int, joining_date date);

CREATE TABLE

Insert Team Information with Joining Dates:

employee=# insert into team values(1, 101, '09-02-2025'), (2,102, '10-02-2025'), (3, 103, '31-01-2025'), (4,104, '29-12-2024');

INSERT 0 4

Create a Table for Storing player Information:

employee=# create table player(player_id int, player_name varchar(30), team_name varchar(20), country varchar(20));

CREATE TABLE

Insert Player Information:

employee=# insert into player values(101, 'yashasvi', 'CSK', 'India'), (102, 'Joe Root', 'RR', 'England'), (103, 'Hardik', 'DD','Srilanka'), (104, 'Jos', 'RCB', 'South Africa'), (105, 'Rohit', 'MI', 'India');

INSERT 0 5

Display All Data from the Player Table:

employee=# select * from player;

 player_id | player_name | team_name |   country    
-----------+-------------+-----------+--------------
       101 | yashasvi    | CSK       | India
       102 | Joe Root    | RR        | England
       103 | Hardik      | DD        | Srilanka
       104 | Jos         | RCB       | South Africa
       105 | Rohit       | MI        | India
(5 rows)

Display All Data from the team Table:

employee=# select * from team;

 team_id | player_id | joining_date 
---------+-----------+--------------
       1 |       101 | 2025-02-09
       2 |       102 | 2025-02-10
       3 |       103 | 2025-01-31
       4 |       104 | 2024-12-29
(4 rows)

Joins:

In SQL, JOINs are used to combine rows from two or more tables based on a related column. Here are the different types of joins commonly used in databases:

Inner Join Query to Fetch Team ID and Corresponding Player Name:

employee=# select team.team_id, player.player_name from team inner join player on team.player_id = player.player_id;

 team_id | player_name 
---------+-------------
       1 | yashasvi
       2 | Joe Root
       3 | Hardik
       4 | Jos
(4 rows)

Right Join to Display Player Names with Team IDs:

employee=# select team.team_id, player.player_name from team right join player on team.player_id = player.player_id;

 team_id | player_name 
---------+-------------
       1 | yashasvi
       2 | Joe Root
       3 | Hardik
       4 | Jos
         | Rohit
(5 rows)

Left Join to Fetch Teams and Players with Non-NULL Player IDs:

employee=# select team.team_id, player.player_name from team left join player on team.player_id = player.player_id where team.player_id is not null;

 team_id | player_name 
---------+-------------
       1 | yashasvi
       2 | Joe Root
       3 | Hardik
       4 | Jos
(4 rows)

Left Join to Find Teams Without Assigned Players:

employee=# select team.team_id, player.player_name from team left join player on team.player_id = player.player_id where team.player_id is null;
 team_id | player_name 
---------+-------------
(0 rows)

Full Outer Join to Fetch All Teams Ids and Players names:

employee=# select team.team_id, player.player_name from team full outer join player on team.player_id = player.player_id;

 team_id | player_name 
---------+-------------
       1 | yashasvi
       2 | Joe Root
       3 | Hardik
       4 | Jos
         | Rohit
(5 rows)

Full Outer Join to Display All Data from Team and Player Tables:

employee=# select *  from team full outer join player on team.player_id = player.player_id;

Retrieve Player Names and Team Names for Players in the IPL Using Joins:

employee=# select player.player_name, team.team_name
from Player
join team
    on player.player_id = team.player_id
join ipl_team
    on ipl_team.player_id = team.player_id;

Retrieve Unique Player IDs from Team and Player Tables Using UNION:

The UNION operator combines the results of two queries and eliminates duplicate values

employee=# select player_id from team
union
select player_id from player; 
 player_id 
-----------
       105
       102
       101
       103
       104
(5 rows)

Retrieve All Player IDs from Team and Player Tables Using UNION ALL:

This operator combines the results of two queries but does not remove duplicates.

employee=# select player_id from team
union all
select player_id from player;
 player_id 
-----------
       101
       102
       103
       104
       101
       102
       103
       104
       105
(9 rows)

Retrieve Common Player IDs Between Team and Player Tables Using INTERSECT:

This operator returns the common player_ids that exist in both the team and player tables. It only includes those player_ids that appear in both result sets.

employee=# select player_id from team
intersect
select player_id from player;
 player_id 
-----------
       101
       103
       104
       102
(4 rows)

Retrieve Common Player IDs Between Team and Player Tables, Ordered by Player ID:

employee=# select player_id from team
intersect
select player_id from player order by player_id;
 player_id 
-----------
       101
       102
       103
       104
(4 rows)

Constraints:

In SQL, constraints are rules that are applied to columns in a table to enforce data integrity.

Constraints ensure that the data entered into a table is accurate, reliable, and follows the business rules or logic defined for the database.

UNIQUE: Ensures all values in a column are unique.
PRIMARY KEY: Combines NOT NULL and UNIQUE, uniquely identifies each row.
FOREIGN KEY: Ensures referential integrity between tables.
CHECK: Validates values in a column based on a condition.
DEFAULT: Specifies a default value if none is provided.
INDEX: Optimizes query performance (not strictly a constraint but related).

Create Players Table with Score Constraint Greater Than 0:

employee=# create table players
(player_id int, player_name varchar(20), score int check(score>0));

CREATE TABLE

employee=# insert into players values(101, 'rohit', 112);

INSERT 0 1

employee=# insert into players values(102, 'virat', -10);

ERROR:  new row for relation "players" violates check constraint "players_score_check"
DETAIL:  Failing row contains (102, virat, -10).

Create Cricket Players Table with Constraints on Score, Balls Faced, and Score vs Balls Relationship:

employee=# CREATE TABLE cricket_players(player_id integer, player_name varchar(20), score integer check (score>=0), balls integer CONSTRAINT balls_faced CHECK (balls>0), CHECK (score>=balls));

CREATE TABLE

employee=# insert into cricket_players values(101,'raina',100,50);

INSERT 0 1

employee=# insert into cricket_players values(102,'rai',0,1);

ERROR:  new row for relation "cricket_players" violates check constraint "cricket_players_check"
DETAIL:  Failing row contains (102, rai, 0, 1).

employee=# insert into cricket_players values(103,'ra',10,1);

INSERT 0 1

employee=# select * from cricket_players;

 player_id | player_name | score | balls 
-----------+-------------+-------+-------
       101 | raina       |   100 |    50
       103 | ra          |    10 |     1
(2 rows)

Create Products Table with Constraints on Product ID, Name, and Price:

employee=# CREATE TABLE products(product_id int not null, product_name varchar(20) not null, price numeric NOT NULL CHECK (PRICE>0));

CREATE TABLE

employee=# insert into products values(11, null, 100);

ERROR:  null value in column "product_name" of relation "products" violates not-null constraint
DETAIL:  Failing row contains (11, null, 100).

employee=# insert into products values(11, 'soap', 100);

INSERT 0 1

employee=# select * from products;

 product_id | product_name | price 
------------+--------------+-------
         11 | soap         |   100
(1 row)

Create Student Table with Unique Student IDs:

employee=# create table student(student_id integer unique, name text);

CREATE TABLE

employee=# insert into student values(101,'bala');

INSERT 0 1

employee=# insert into student values(101,'aala');

ERROR:  duplicate key value violates unique constraint "student_student_id_key"
DETAIL:  Key (student_id)=(101) already exists.

employee=# select * from student;

 student_id | name 
------------+------
        101 | bala
(1 row)

Create Students Table with Unique Combination of Student ID and Mobile:

employee=# create table students(student_id integer, student_name text, student_mobile integer, unique(student_id, student_mobile));

CREATE TABLE

Create Book Table with Primary Key on Book ID:

employee=# create table book 
(book_id integer PRIMARY KEY, name text, author text);

CREATE TABLE

employee=# insert into book values(0,'database','muthu');

INSERT 0 1

employee=# insert into book values(1,'database','muthu');

INSERT 0 1

employee=# select * from book;

 book_id |   name   | author 
---------+----------+--------
       0 | database | muthu
       1 | database | muthu
(2 rows)

employee=# insert into book values(1,'database','muthu');

ERROR:  duplicate key value violates unique constraint "book_pkey"
DETAIL:  Key (book_id)=(1) already exists.

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

Lakshmi Pritha Nadesan — Mon, 10 Feb 2025 02:14:09 +0000

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)

Day 35 - Abstraction, Encapsulation in Python and Database

Lakshmi Pritha Nadesan — Fri, 07 Feb 2025 06:40:20 +0000

Abstraction:

It is the concept of hiding the complex implementation details and showing only the essential features or functionalities to the user.

We create an abstract class by inheriting from the ABC class (which stands for Abstract Base Class) and using the @abstractmethod decorator for abstract methods.

from abc import *
class Parent(ABC):
    @abstractmethod
    def study(self):
        pass

class Child(Parent):
    def study(self):
        print("Commerce")

child = Child()
child.study()

Commerce

The Parent class inherits from ABC, which makes it an abstract class.

The study() method in Parent is defined as an abstract method using the @abstractmethod decorator. This means that any subclass must implement this method.

from abc import *
class Parent(ABC):
    @abstractmethod
    def study(self):
        pass

class Child(Parent):
    def study(self):
        print("Commerce")

    @abstractmethod
    def test(self):
        pass

class GrandChild(Child):
    def test(self):
        pass 
child = GrandChild()
child.study()

Commerce

The code you provided will result in an error because of the way the @abstractmethod decorator is used in the Child class.

The GrandChild class implements the test() method, which is required by the abstract method in the Child class. However, the GrandChild class does not need to implement study() because it is already implemented in the Child class.

Encapsulation:

It restricts direct access to some of the object's attributes or methods to prevent accidental modification and to safeguard the internal state.

In Python, encapsulation is achieved using access modifiers to control the visibility and accessibility of class attributes and methods. There are three main types of access modifiers:

Public:Attributes or methods that are accessible from outside the class. By default, all members are public in Python.
Syntax: No special symbol is needed.
Example: self.attribute_name

Protected:Attributes or methods that should be accessible only within the class and its subclasses.
Syntax: Prefix the member name with a single underscore (_).
Example: _attribute_name

Private:Attributes or methods that should not be accessible from outside the class, making it less prone to accidental modification. Private members are only accessible within the class itself.
Syntax: Prefix the member name with two underscores (__).
Example: __attribute_name

class Infosys:
    employees_count = 100000
    _profit = 100
    __project_bid = 50

    def display(self):
        print(self.employees_count, self._profit, self.__project_bid)

inf = Infosys()
inf.display()

100000 100 50

When you call inf.display(), the method display will print:

self.employees_count (accessible directly, so it prints 100000).
self.profit (protected, but still accessible within the class, so it prints 100).
self._project_bid (private, so it will cause an error when accessed directly within the method).

class Infosys:
    employees_count = 100000
    _profit = 100
    __project_bid = 50

    def display(self):
        print(self.employees_count, self._profit, self.__project_bid)

inf = Infosys()
print(inf.employees_count)
print(inf._profit)
print(inf.__project_bid)

100000
100
AttributeError: 'Infosys' object has no attribute '__project_bid'

print(inf.employees_count):Since employees_count is a public attribute, it is directly accessible from outside the class.
Output: 100000

print(inf._profit):_profit is a protected attribute. It’s a convention in Python to indicate that this attribute should not be accessed directly outside the class. However, Python doesn't enforce protection, so you can access it directly, but it is not recommended.
Output: 100

print(inf.project_bid):project_bid is a private attribute. Python internally performs name mangling to prevent direct access. It will not be directly accessible from outside the class, and trying to access it like this will result in an AttributeError.

Database:

A database is an organized collection of structured information, or data, that is typically stored electronically in a computer system.

A database is designed to store, retrieve, and manage data efficiently, allowing users and applications to access, update, and manipulate the data in various ways.

Database Management System (DBMS):

A DBMS is software that manages databases. It provides an interface for interacting with the database, such as adding, updating, or querying data.
Examples of popular DBMS include MySQL, PostgreSQL, SQLite, Oracle, Microsoft SQL Server, and MongoDB.

Relational Databases (RDBMS):

These databases store data in tables and use SQL to query the data. Examples include MySQL, PostgreSQL, Oracle.

Tables:

In a relational database, data is organized into tables. A table consists of rows and columns, where each row represents a record and each column represents an attribute of that record.
For example, a table called Employees might have columns like EmployeeID, Name, Department, Salary, etc.

Hierarchical Database Model :

The Hierarchical Database Model is a type of database model in which data is organized in a tree-like structure, where each record has a single parent and possibly many children.

Network Database Model :

The Network Database Model is an extension of the Hierarchical Database Model, designed to overcome some of the limitations of the hierarchical model.

It allows for more complex relationships between records and supports many-to-many relationships.

Postgresql:

PostgreSQL is a powerful, open-source, object-relational database management system (ORDBMS) that is known for its advanced features, extensibility, and standards compliance.

It is widely used for storing and managing data in large-scale applications, web applications, and even for data warehousing.

Datatypes:

PostgreSQL Installation:

For Linux open the terminal and type the following commands:

sudo apt update
sudo apt install postgresql postgresql-contrib

Check current status of the postgresql service on a Linux based system.

sudo systemctl status postgresql

Switch to postgres user:

sudo -i -u postgres

To enter the PostgreSQL interactive terminal (psql), type:

psql

You’ll then be inside the psql prompt, where you can run SQL queries:

\l    # List all databases
\c movie  # Connect to a specific database(movie)

Information about the current version of the database system:

select version();

PostgreSQL 16.6 (Ubuntu 16.6-0ubuntu0.24.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.2.0-23ubuntu4) 13.2.0, 64-bit
(1 row)

Creating a table named movie with three columns:

create table movie
(movie_name varchar(80), 
release_date date, 
ticket int
);

CREATE TABLE

movie_name VARCHAR(80): The column movie_name is of data type VARCHAR(80), which means it will hold variable-length strings with a maximum length of 80 characters.

release_date DATE: The column release_date is of data type DATE, which means it will hold dates in the format YYYY-MM-DD.

ticket INT: The column ticket is of data type INT, which will store integer values. This could represent the number of tickets or some other numeric value related to tickets.

Insert values into table:

insert into movie values('vidamuyarchi', '2025-02-06',120); 
insert into movie values('thunivu', '2024-02-06',120); 
insert into movie values('goat', '2024-06-26',120);

INSERT 0 1
INSERT 0 1
INSERT 0 1

Another method to insert values into table:

insert into movie(movie_name, ticket) values('leo', 120);

INSERT 0 1

Select all the values from the movie:

select * from movie;

movie_name  | release_date | ticket 
--------------+--------------+--------
 vidamuyarchi | 2025-02-06   |    120
 thunivu      | 2024-02-06   |    120
 goat         | 2024-06-26   |    120
 leo          |              |    120
(4 rows)

Select only the movie name from the movie:

select movie_name from movie;

movie_name  
--------------
 vidamuyarchi
 thunivu
 goat
 leo
(4 rows)

Calculate ticket price for 4 persons:

select ticket*4 from movie;

?column? 
----------
      480
      480
      480
      480
(4 rows)

Describe the Table Structure:

\d movie;


                         Table "public.movie"
    Column    |         Type          | Collation | Nullable | Default 
--------------+-----------------------+-----------+----------+---------
 movie_name   | character varying(80) |           |          | 
 release_date | date                  |           |          | 
 ticket       | integer               |           |          |

Task:

Employee Table:
empid integer
empname varchar(30)
designation varchar(30)
dept varchar(20)
salary integer

 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)

Inserting multiple rows using single insert statement:

In PostgreSQL you can insert multiple rows into a table using a single INSERT statement.

This approach is much more efficient than inserting rows one by one, especially when dealing with large datasets.

Syntax for Inserting Multiple Rows:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES
    (value1_row1, value2_row1, value3_row1, ...),
    (value1_row2, value2_row2, value3_row2, ...),
    (value1_row3, value2_row3, value3_row3, ...),
    ...;

Example:


INSERT INTO employee (employee_id, name, designation_id, dept_id, hire_date)
VALUES
    (1, 'John Doe', 1, 1, '2025-01-01'),
    (2, 'Jane Smith', 2, 1, '2024-05-15'),
    (3, 'Alice Brown', 3, 2, '2023-11-10'),
    (4, 'Bob Johnson', 4, 3, '2022-09-20');

The employee_id values (1, 2, 3, 4) are being inserted into the employee_id column.
The name values ('John Doe', 'Jane Smith', etc.) are inserted into the name column.
Similarly, designation_id, dept_id, and hire_date are populated with the corresponding values for each employee.

Day 34 - Constructor, Inheritance

Lakshmi Pritha Nadesan — Wed, 05 Feb 2025 02:30:02 +0000

Constructor:

In Python, a constructor is a special method that is automatically called when an object of a class is created. It is used to initialize the object's attributes with initial values. The constructor in Python is defined using the __init__ method.

class Employee:
    def __init__(self, name, department,job, year):
        pass

    def work(self):
        print("Working", self.empName, self.dept, self.desingnation,self.joining_year)

emp1 = Employee("guru", "B.Com", "Development", 2025)
emp2 = Employee("pritha", "M.E.,", "Design", 2025)
emp1.work()

AttributeError: 'Employee' object has no attribute 'empName'

We are trying to print instance variables like self.empName, self.dept, self.desingnation, and self.joining_year. However, these instance variables are not being initialized in the __init__ method.

class Employee:
    def __init__(self, name, department,job, year):
        self.empName = name
        self.dept = department
        self.designation=job
        self.joining_year = year

    def work(self):
        print("Working", self.empName, self.dept, self.designation,self.joining_year)

emp1 = Employee("guru", "B.Com", "Development", 2025)
emp2 = Employee("pritha", "M.E.,", "Design", 2025)
emp2.work()

Working pritha M.E., Design 2025

self.empName = name: The parameter name is assigned to the instance variable empName. This means the employee's name will be stored in the empName attribute of the object.

Similarly, self.dept = department, self.designation = job, and self.joining_year = year store the values for the employee's department, job, and joining year, respectively

The work() method simply prints the employee's details (name, department, job, and joining year).

class Employee:
    salary = 25000
    def __init__(self, name, department,job, year):
        self.empName = name
        self.dept = department
        self.desingnation=job
        self.joining_year = year

    def work(self):
        print("Working", self.empName, self.dept, self.desingnation,self.joining_year)
        print(self.salary)

    def take_leave(self):
        print(self.empName)

emp1 = Employee("guru", "B.Com", "Development", 2025)
emp2 = Employee("pritha", "M.E.,", "Design", 2025)
emp2.work()
emp1.take_leave()

Working pritha M.E., Design 2025
25000
guru

The salary is a class attribute, so if you want to access it within the work method, you need to access it from the class, either using self.salary or Employee.salary.

While you can use self.salary, it's important to understand that self refers to instance attributes, but since salary is a class attribute, accessing it via self is perfectly valid as well.

The take_leave() method is called for emp1 and prints the employee’s name.

class Employee:
    def __init__(self, name, department,job, year):
        self.name = name
        self.department = department
        self.job=job
        self.year = year
emp1 = Employee("guru", "B.Com", "Development", 2025)
emp2 = Employee("pritha", "M.E.,", "Design", 2025)

print(emp1.__dict__)
print(emp2.__dict__)

{'name': 'guru', 'department': 'B.Com', 'job': 'Development', 'year': 2025}
{'name': 'pritha', 'department': 'M.E.,', 'job': 'Design', 'year': 2025}

The __dict__ attribute contains the attributes of the object, as well as their current values. It is a dictionary that maps the attribute names to their corresponding values.

Calling the class method directly on the class using decorator:

class Employee:
    def __init__(self, name, department,job, year):
        self.name = name
        self.department = department
        self.job=job
        self.year = year
    @classmethod
    def credit_salary(cls):
        print("Credit salary on last day of every month")

emp1 = Employee("guru", "B.Com", "Development", 2025)
emp2 = Employee("pritha", "M.E.,", "Design", 2025)

Employee.credit_salary()

Credit salary on last day of every month

The @classmethod decorator is used to define a method that is bound to the class, not the instance. The first parameter of the class method is cls, which refers to the class itself, not the instance.

We can call credit_salary() using Employee.credit_salary(), because it is a class method and can be accessed from the class itself.

Inner class:

The inner class has access to the outer class's attributes and methods, but it behaves as a separate class with its own namespace.

class College:
    def __init__(self):
        print("College Constructor")

    class Dept:
        def __init__(self):
            print("Dept Constructor")

        def work(self):
            print("Working")

principal = College()
hod = principal.Dept()
hod.work()

College Constructor
Dept Constructor
Working

The Dept class is nested inside the College class, so it needs to be referenced as College.Dept when creating an instance of Dept outside the College class.

principal = College() initializes the College instance, and the constructor for College is called.

hod = College.Dept() creates an instance of the nested Dept class.

hod.work() calls the work method of the Dept class.

class SuperMarket:
    def __init__(self, product_name, price, discount):
        self.product_name = product_name
        self.price = price
        self.discount = discount

    def buy(self):
        print(self.product_name, self.price, self.discount)


product1 = SuperMarket("soap", 50, 10)
product2 = SuperMarket("Brush", 60,20)
product1.buy()
product2.buy()

soap 50 10
Brush 60 20

SuperMarket class with an__init__ method that accepts product_name, price, and discount as parameters.

product1 and product2 are instances of the SuperMarket class with their respective details.

The buy method prints the product_name, price, and discount for each product.

Constructor overloading:

In Python, constructor overloading is not directly supported. This is because Python does not allow multiple constructors with the same name.

However, you can achieve similar functionality by using default arguments or variable-length argument lists (e.g *args and **kwargs).

class SuperMarket:
    def __init__(self, product_name, price, *discount):
        self.product_name = product_name
        self.price = price
        self.discount = discount

    def buy(self):
        print(self.product_name, self.price, self.discount)


product1 = SuperMarket("soap", 50, 10)
product2 = SuperMarket("Brush", 60,20)
product3 = SuperMarket("Rice", 60)

product1.buy()
product2.buy()
product3.buy()

soap 50 (10,)
Brush 60 (20,)
Rice 60 ()

The *discount allows you to pass any number of discount values. If no value is provided for discount, it defaults to a tuple containing a single value 0.

If there are 3 arguments, it sets product_name, price, and discount.

If there are only 2 arguments, it assumes the discount is 0.

Inheritance:

Inheritance allows a class to inherit methods and properties from another class.

The class that inherits is called the child class (or subclass), and the class being inherited from is called the parent class (or superclass).

Types of Inheritance in Python:

There are five main types of inheritance in Python

Single Inheritance:In single inheritance, a child class inherits from one parent class.

Multiple Inheritance:In multiple inheritance, a class can inherit from more than one class.

Multilevel Inheritance:In multilevel inheritance, a class inherits from a class that is also a subclass of another class.

Hierarchical Inheritance:In hierarchical inheritance, multiple child classes inherit from a single parent class.

Hybrid Inheritance:Hybrid inheritance is a combination of multiple types of inheritance, such as a combination of multiple and multilevel inheritance.

class Shapes:
    def find_area(self, side1, side2):
        print(side1 * side2)
class Square(Shapes):
    pass

s = Square()
s.find_area(5,5)

class Rectangle(Shapes):
    pass
r = Rectangle()
r.find_area(10,8)

25
80

Both Square and Rectangle classes inherit from the Shapes class, which has the find_area method that calculates the area of a shape given two sides.

The Square class does not define its own find_area method but inherits it from the Shapes class. Therefore, calling s.find_area(5, 5) will calculate the area of a square with side length 5, which results in 5 * 5 = 25.

Similar to Rectangle class.

Example for multiple inheritance:

class Father:
    def work(self):
        print("Mechanical Engineer")

class Mother:
    def work(self):
        print("Software Engineer")

class Child(Father, Mother):
    pass

child = Child()
child.work()

Mechanical Engineer

Multiple Inheritance in Python, where the Child class inherits from both Father and Mother. The Child class can access methods from both parent classes, but when you call child.work(), Python follows a method resolution order (MRO) to determine which method to execute.

Method Resolution Order(MRO):

In Python, when multiple classes are involved in inheritance, the MRO determines the order in which methods are called.

Child inherits from Father first, and then from Mother.

Therefore, Python will call the work method from the Father class (because it appears first in the inheritance list).

class Father:
    def work(self):
        print("Mechanical Engineer")

class Mother:
    def work(self):
        print("Software Engineer")

class Child(Mother, Father):
    pass

child = Child()
child.work()

Software Engineer

If you want the Child class to inherit the work method from the Mother class, you can simply change the order of inheritance.

Method Overriding:

Method Overriding occurs when a subclass defines a method that has the same name, same parameters, and same return type as a method in its parent class. When the subclass calls this method, the method in the subclass overrides (replaces) the method in the parent class.

It is also called runtime polymorphism.

class Father:
    def work(self):
        print("Mechanical Engineer")

class Mother:
    def work(self):
        print("Software Engineer")

class Child(Mother, Father):
    def work(self):
        print("Business person")

child = Child()
child.work()

Business person

Overrides the work method and prints "Business person".

Since the Child class defines its own work method, the method from the Child class is called, regardless of the inheritance from Mother and Father.

Operator overriding:

Operator overloading allows you to define how operators like +, -, *, etc., should behave when they are used with instances of a class. In Python, this is done by defining special methods in your class, such as__add__, __sub__, __mul__ etc.

class Book:
    def __init__(self, pages):
        self.pages = pages

    def __add__(self, second):
        return self.pages + second.pages

book1 = Book(300)
book2 = Book(200)
print(book1 + book2)

__add__ method: This is the method that is called when the + operator is used between two Book objects. Inside this method, you're returning the sum of the pages attribute from both book1 and book2.

When you run print(book1 + book2), Python calls book1.__add__(book2) behind the scenes, which adds the pages attribute from both books together and prints the result.

class Employee:
    def __init__(self, name, salary):
        self.name = name
        self.salary = salary

    def __mul__(self,other):
        return self.salary * other.days


class TimeSheet:
    def __init__(self, name, days):
        self.name = name
        self.days = days

emp = Employee("Pritha", 1000)
timesheet = TimeSheet("Pritha", 25)
print(emp * timesheet)

When emp * timesheet is executed, Python internally calls emp.__mul__(timesheet).
The __mul__method multiplies the salary of the Employee object (emp) with the days from the TimeSheet object (timesheet).