Day 1: Getting Started with PSQL: The Powerful SQL DataBase.

A database is an organized collection of data that is stored and managed electronically. It allows users to store, retrieve, update, and delete data efficiently. Databases are used in various applications, such as websites, banking systems, customer management, and more.

PostgreSQL(PSQL)

PostgreSQL (PSQL) is an advanced, open-source relational database management system (RDBMS) known for its strong performance, reliability, and extensibility. It follows the SQL (Structured Query Language) standard but also provides powerful features beyond traditional relational databases.

RDMS:-
Relational Database (RDBMS) – Stores data in tables with relationships (e.g., MySQL, PostgreSQL, Oracle, SQL Server).

Basic PSQL Commands

step 1:-

1.This collection of data that is stored and manged electronically.
-> Create database payilagam;
2.tha database delete .
-> drop database payilagam;

*3.All information store the Students table. *

example:-
CREATE TABLE Students (
students_id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
age INT NOT NULL,
course VARCHAR(100) NOT NULL,
course_fees INT NOT NULL,
gender VARCHAR(50) NOT NULL
);
4.students information insert the table .

example:-
INSERT INTO Students (students_id, name, age, course, course_fees, gender) VALUES
(1, 'sugumar', 21, 'java full stack', 23000, 'male'),
(2, 'vasu', 24, 'java full stack', 23000, 'male'),
(3, 'bagavath', 21, 'python full stack', 21000, 'male'),
(4, 'kaviya', 21, 'java full stack', 23000, 'female'),
(5, 'harni', 21, 'DSA', 30000, 'female'),
(6, 'tamili', 26, 'python full stack', 21000, 'male'),
(7, 'vignesh', 22, 'DSA', 30000, 'male'),
(8, 'vimal', 23, 'java full stack', 23000, 'male'),
(9, 'iyyappan', 23, 'python full stack', 21000, 'male'),
(10, 'ilai', 21, 'DSA', 30000, 'male'),
(11, 'sudhakar', 25, 'java full stack', 23000, 'male');

5.SELECT * FROM Students;

$ SELECT * FROM Students -is used retrive all colums and rows from the students table in a database.

step 2:-

Alter table .
1.create the new column named teacher.
$ ALTER TABLE Students add teachers varchare(100);

2.Delete the column named teacher.
$ ALTER TABLE Students drop teacher ;

step 3:-

Where class and condition.
-> Where is used to filter the records.the rows are filtered based on Conditions.

1. only course fees above 23000. $ ** SELECT * FROM Students WHERE course_fees>23000;**

2.only course fees within 23000.
$ ** SELECT * FROM Students WHERE course_fees<23000;

1. only course fees above 22000 and only course within 30000. $ SELECT * FROM Students WHERE course_fees>22000 And course_fees<30000;

4.retrive name onley.
$ ** SELECT * FROM Students WHERE name = 'sugumar';**

5.SELECT * FROM Students WHERE course_fees BETWEEN 22000 AND 30000

step 4:

Using LIKE in a SELECT Query:

The Like operator in SQL is used for Pattern matching in text-based columns. it helps to filter results based on partial matches using wildcards (% and_).

1.filter name starting with s.
$ ** SELECT * FROM Students WHERE name LIKE 's%';**

2.filter name starting with v and ending with u.
$ **SELECT * FROM Students WHERE name LIKE 'v%u';

3.filter name containin v.
$ SELECT * FROM Students WHERE name LIKE '%v%';

4.filter name starting with *v%* is the escape character.
$ SELECT * FROM Students WHERE name LIKE 'v\%';

step 5:-

Aggregution:- (count,sum,avg,max,min,round)

1.SELECT * FROM Students WHERE course = 'java full stack';
2.SELECT * FROM Students WHERE course = 'python full stack';
3.SELECT COUNT() FROM Students;
4.SELECT COUNT() FROM Students where gender = 'male';
5.SELECT AVG(course_fees) FROM Students;
6.SELECT ROUND(AVG(course_fees),2) FROM Students;
7.SELECT MAX(course_fees) FROM Students;
8.SELECT * FROM Students WHERE course_fees=(SELECT MAX(course_fees) FROM Students);
9.SELECT * FROM Students WHERE course_fees=(SELECT SUM(course_fees) FROM Students);