DEV Community: Vigneswaran Manivannan

JOINS- 19-06-2025

Vigneswaran Manivannan — Wed, 18 Jun 2025 16:41:24 +0000

JOINS- A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

***Different Types of Joins
*
INNER JOIN: Returns records that have matching values in both tables

LEFT JOIN: Returns all records from the left table, and the matched records from the right table

RIGHT JOIN: Returns all records from the right table, and the matched records from the left table

FULL JOIN: Returns all records when there is a match in either left or right table

INNER JOIN- SYNTAX

**
select column1, column2
from table1
join
table2
on table1.column = table2.column;

select n.name,n.country,s.division,s.gender from new_staff_details n inner join staff_details s on n.name=s.name;

LEFT JOIN-

select column1, column2
from table1
left join
table2
on table1.column = table2.column;

select n.name,n.country,s.division,s.gender from new_staff_details n left join staff_details s on n.name=s.name;

RIGHT JOIN-

select column1, column2
from table1
right join
table2
on table1.column = table2.column;

select n.name,n.country,s.division,s.gender from new_staff_details n right join staff_details s on n.name=s.name;

FULL JOIN-

select column1, column2
from table1
Full join
table2
on table1.column = table2.column;

select n.name,n.country,s.division,s.gender from new_staff_details n full join staff_details s on n.name=s.name;

SUB QUERY, ORDER BY,LIMIT, OFFSET, GROUP BY- 17-06-2025

Vigneswaran Manivannan — Tue, 17 Jun 2025 17:07:08 +0000

*SUB QUERY- *

select min(salary)from staff_details;

2ND MINIMUM-

*Not selecting minimum salary
*
select*from staff_details where salary not in (select min(salary)from staff_details);

*select minimum again to get 2nd minimum
*
select min(salary)from staff_details where salary not in( select min(salary)from staff_details);

*To get the row
*
select*from staff_details where salary=15000; OR

select*from staff_details where salary=( select min(salary)from staff_details where salary not in( select min(salary)from staff_details));

ORDER BY :

to list it as order ( ascending, descending)

select*from staff_details order by salary;

descending;

select*from staff_details order by salary desc;

*LIMIT (Limit the data)- HOW MANY VALUES ( ROW)
*
select*from staff_details order by salary desc limit 2;

*OFFSET- SKIP
*
SKIP THE ROW FROM THE LIST

select*from staff_details order by salary desc limit 2 offset 1;

*GROUP BY
*

It must have aggregate value.

select count(nationality) from staff_details group by nationality;

select nationality, count(nationality) from staff_details group by nationality;

DQL- SELECT - 16-06-2025

Vigneswaran Manivannan — Mon, 16 Jun 2025 15:47:25 +0000

DQL- DATA QUERY LANGUAGE

SELECT

SELECT REQUIRED DETAIILS/columns

SELECT C1,C2 FROM TABLE NAME;

EX;
select name,category,gender from staff_details;

SELECT wont affect any data in the table.

RENAME (To display )- AS (alias)

SELECT C1 as "rename", from table name;

select name as "staff name",category,gender,salary from staff_details;

*WHERE- CONDITION.
*

select*from staff_details where DIVISION='CONSTRUCTION';

*BETWEEN
*

SELECT*FROM STAFF_DETAILS WHERE SALARY BETWEEN 15000 AND 30000;

*LIKE- To search from letters
*

SELECT*FROM STAFF DETAILS WHERE COLUMN LIKE 'A%'

SELECT*FROM STAFF_DETAILS WHERE NAME LIKE'A%';
OR 'A%A'
OR '%A'

*IN- LIST OF DATA.
*

SELECT*FROM STAFF_DETAILS WHERE SALARY IN (10000,20000);

SELECT*FROM STAFF_DETAILS WHERE SALARY NOT IN (10000,20000);

AGGREGATE FUNCTION

FUNCTION ()

*COUNT *:
SELECT COUNT (*FROM) STAFF_DETAILS;

SUM:

SELECT SUM (SALARY) FROM STAFF_DETAILS;

*AVERAGE:
*
SELECT AVG(SALARY) FROM STAFF_dETAILS;

*ROUND:
*

SELECT ROUND (AVG(SALARY)) FROM STAFF_DETAILS;

*ROUND ON DECIMALS:
*
SELECT ROUND (AVG(SALARY),2) FROM STAFF_DETAILS;

MIN VALUES

SELECT MIN(SALARY)FROM STAFF_DETAILS;

*SELECT ROW FROM MIN SALARY.
*
SELECT*FROM STAFF_DETAILS WHERE SALARY=(SELECT MIN(SALARY)FROM STAFF_DETAILS);

3rd Day Training. About Table creating, Alter table, insert values in table- 11-06-2025

Vigneswaran Manivannan — Wed, 11 Jun 2025 16:17:39 +0000

Rename a table
ALTER TABLE RENAME TO .

*Rename a column
*
ALTER TABLE

RENAME TO

*CHANGE DATA TYPE OF COLUMN *

ALTER TABLE

ALTER COLUMN < column name> TYPE

****To check the datatypes of the column

SELECT column_name, data_type FROM information_schema.columns where table_name='your_table_name';

**ADD NEW COLUMN

ALTER TABLE

ADD COLUMN < column name data type ;

CONSTRAINTS

SQL constraints are rules applied to columns or tables in a relational database to limit the type of data that can be inserted, updated, or deleted. These rules ensure the data is valid, consistent, and adheres to the business logic or database requirements.

Constraints can be enforced during table creation or later using the ALTER TABLE statement

TYPES-

PRIMARY- A PRIMARY KEY constraint is a combination of the NOT NULL and UNIQUE constraints. It uniquely identifies each row in a table. A table can only have one PRIMARY KEY, and it cannot accept NULL values. This is typically used for the column that will serve as the identifier of records.

Example:

CREATE TABLE Student
(
ID int(6) NOT NULL UNIQUE,
NAME varchar(10),
ADDRESS varchar(20),
PRIMARY KEY(ID)
);
Explanation: In this case, the ID column is set as the primary key, ensuring that each student’s ID is unique and cannot be NULL.

FOREIGN KEY

A FOREIGN KEY constraint links a column in one table to the primary key in another table. This relationship helps maintain referential integrity by ensuring that the value in the foreign key column matches a valid record in the referenced table.

*UNIQUE KEY
*
The UNIQUE constraint ensures that all values in a column are distinct across all rows in a table. Unlike the PRIMARY KEY, which requires uniqueness and does not allow NULLs, the UNIQUE constraint allows NULL values but still enforces uniqueness for non-NULL entries.

Example:

CREATE TABLE Student
(
ID int(6) NOT NULL UNIQUE,
NAME varchar(10),
ADDRESS varchar(20)
);
Explanation: Here, the ID column must have unique values, ensuring that no two students can share the same ID. We can have more than one UNIQUE constraint in a table.

** NOT NULL Constraint
**
The NOT NULL constraint ensures that a column cannot contain NULL values. This is particularly important for columns where a value is essential for identifying records or performing calculations.

Source- https://www.geeksforgeeks.org/sql-constraints/

*SERIES OF NUMBERS- SERIAL
*

CREATE TABLE < table name > (column name SERIAL PRIMARY KEY )

*Create table with primary keys and constraints-
*

CREATE TABLE < table name > (column name SERIAL PRIMARY KEY, COLUMN 2 DATA TYPE, COLUMN 3 DATA TYPE )

INSERTION

Insert values based on the above table created.

INSERT INTO

VALUES (1,MANGO,200)

*Multiple values.
*
INSERT INTO

(C2,C3,C4) VALUES (BANANA, 20) (APPLE, 30)

*UPDATE :
*

UPDATE

SET prod_name=' ALPHONSO MANGO' where prod_id=5;
UPDATE

SET prod_name='ALPHONSO MANGO' where prod_NAME=MANGO;
UPDATE

SET prod_name='ALPHONSO MANGO', MRP=60 where prod_ID=5

*DELETE query:
* DELETE from

where prod_id=2;

2nd Day, Introduction to SQL- 10-06-2025

Vigneswaran Manivannan — Tue, 10 Jun 2025 15:47:30 +0000

*SQL *- Structured Query Language-

used in Data analysis.
Software development purpose
To manage and manipulate relational DBMS.

*Uses of SQL
*

Querying the data. To find a specific information.
To insert, update , delete the data.

**SQL commands

**
DDL- Data definition Language.

Used to define/ manage the tables.

The commands are,

CREATE- Create a table
ALTER- To modify the table structure, which was created already.
DROP- To delete the complete table
RENAME- To rename the table
TRUNCATE- To delete all records except the table structure.

DML- Data manipulation language.

To manipulate the data into the table.

INSERT- Add new data in rows.
UPDATE- Modify the existing data. 3.DELETE- Remove data from the row.

DQL - Data Query Language

SELECT- To retrieve a data from the table.

( Select* from table name )

DCL- Data control language

Used for access control

GRANT- To provide access/ privileges to users.
REVOKE- To take back the privileges.

TCL- Transaction control Language

Used to manage the transactions/ data.

COMMIT- used to permanently save any transaction into the database
ROLLBACK- Used to restores the database to the last committed state. undo the changes since last commit
SAVE POINT- Set a point to rollback
SET TRANSACTION- To set the properties for transactions ( Like read only )

DATA TYPES

1. Numeric Type
**
**SMALLINT- It saves whole numbers in the specified range. Memory 2 bytes
INTEGER-It saves whole numbers in the specified range.-Memory 4 bytes
BIGINT- Memory is 8 bytes
NUMERIC @ DECIMAL
REAL@ FLOAT4- Single precision floating point number-

Single-precision floating-point format (sometimes called FP32 or float32) is a computer number format, usually occupying 32 bits in computer memory; it represents a wide dynamic range of numeric values by using a floating radix point.

DOUBLE PRECISION @FLOAT8 - Double-precision floating-point numbers are 64-bit floating-point numbers used to represent real numbers with greater precision and a wider range than single-precision floating-point numbers. This format, often referred to as FP64, is commonly used in scientific computing and other applications where high accuracy is crucial.

*2. CHARACTER
*

CHAR (n)- fixed length character string.

ex, USA,IND

VARCHAR (n)- Lengthy character string.
TEXT- Variable length characteristics.

( unlimited length )

*3. BOOLEAN TYPE
*

TRUE or FALSE.

4. DATE AND TIME TYPES

DATE- date
TIMESTAMP- (date and time )

To launch, psql -h localhost -U postgres

Create- CREATE DATABASE Students ;
drop- DROP DATABASE STUDENTS

TO view DATABASE - \L
To close- \q

alter/ edit - ALTER DATABASE Students RENAME TO Students_details;

\c Students; - to connect the DB.

CREATE TABLE students_info ( Name varchar(50), Class INTEGER, Joining_date DATE);

*to view- \dt
*

Its been my first day class for SQL- 09-06-2025

Vigneswaran Manivannan — Mon, 09 Jun 2025 14:52:07 +0000

At the 1st session, Today I got introduction about some basic terms such as Database, Types of Database..

Database

A database is an organized collection of structured data, which is stored electronically . It's managed by a database management system (DBMS).

Structured

It will be an organized level. That is an organized format contains tables. formed by rows and columns.

Unstructured-

No predefined format, It will be harder to analyze as it is an unstructured.
ex, Photos , videos

semi structured

It wont follow the table format.

DBMS-
A software which is used to interact with the database. In that we can store, manage( add, edit, remove), retrieve data.

Some key reason to use DBMS were,

To organize and align a data, so that we can quickly retrieve the data.

2.For data integration, get accurate data with consistency.

For security purpose. We can restrict to users accordingly.

4.Can be used by multiple users at same time.

Can do backup and recovery option available.

*TYPES OF DBMS
*

1. Hierarchical DBMS.

Tree like hierarchy
One to many relationship
Data were stored as records, connected via parent- child link. -They store data in a tree-like structure so that it is easy to find and use ex, Org >> State >> region >> city.

2. Network DBMS.

Many to many relationship
Graphical structure ex, Book, Author name, publisher, borrower.

3. Relational DBMS.

-Stored in tables as rows and column.

-They do not support many to many relationships and have pre-defined data types that they can support. They are the most popular DBMS type in the industry.

ex, Oracle, MySQL, MS SQL

4. OO DBMS

-Object oriented DBMS.
-They store data in the form of objects. It supports the storage of miscellaneous data types.

ex, DB40, Object DB, Gemstone

5. No SQL DBMS.

-Unstructured, semi structured.

ex, document based, key value, graph data. Applicable for big data, social network.

*FEATURES OF DBMS
*

ACID

A- ATOMICITY- All steps to be succeed or none of the steps
C-CONSISTENCY-Data remains valid before and after any transaction.
I- ISOLATION- Can run independently.
D- DURABILITY - Data can be saved permanently, even after the crash.