SQL-1

#srnsql #sql #postgres #beginners

SQL

Structured Query Language (SQL) is the standard language used to interact with relational.

SQL is a standard language for storing, manipulating and retrieving data in databases.
Mainly used to manage data. Whether you want to create, delete, update or read data, SQL provides commands to perform these operations.
Widely supported across various database systems like MySQL oracle, PostgreSQL, SQL Server and many others.
Mainly works with Relational Databases (data is stored in the form of tables).

There are many Relational Database Management Systems (RDBMS) that run on SQL but mainly:

MySQL: The world's most popular open-source database, powering major websites like WordPress, Facebook, and YouTube due to its speed and simplicity.
PostgreSQL: Known as the most advanced open-source database, favored by developers for complex data types, heavy writing operations, and enterprise features.
SQLite: A tiny, serverless database embedded directly inside nearly every smartphone, web browser, and desktop application on earth.

Log files
Log files are typically stored as plain text (.log, .txt), JSON, or CSV formats, recording a time-stamped chronological list of system or application events. You can read small logs using basic text editors or large logs using specialized command-line tools and viewers.

PostgreSQL

PostgreSQL is a free open-source database system that supports both relational (SQL) and non-relational (JSON) queries and extends the SQL language combined with many features that safely store and scale the most complicated data workloads. It is a back-end database for dynamic websites and web applications.

Why we want PostgreSQL
PostgreSQL comes with many features aimed to help developers build applications, administrators to protect data integrity and build fault-tolerant environments, and help you manage your data no matter how big or small the dataset. In addition to being free and open source, PostgreSQL is highly extensible. For example, you can define your own data types, build out custom functions, even write code from different programming languages without recompiling your database.

To open it

sudo -i -u postgres

sudo: super user do
i: Start a login shell. [Loading the User’s Environment automatically]
u: user
postgres: username
Why do we use it?
When PostgreSQL is installed, it creates a special Linux user called postgres.
This user:
• Owns the PostgreSQL server process.
• Has permission to administer PostgreSQL.
• Can connect to the database using peer authentication without entering a database password.

psql
Then type psql it is the interactive command-line client for PostgreSQL. It allows you to connect to a PostgreSQL server, execute SQL queries, create databases and tables,
and administer the database.

Some basic function
\l-To list all databases.
\h-To help.
\r-To stop or recover.
\c-To connect to database or table.

create database Movie;
By this we can create database.

\c movie or psql movie
To Connect to newly created database.

select version();
To know version of postgres.

select current_date;
To know current date.

Now we can learn SQL with PostgreSQL

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

Note:

Two dashes (“--”) introduce comments. Whatever follows them is ignored up to the end of the line.
SQL is case insensitive about key words and identifiers, except when identifiers are double-quoted to preserve the case (not done above).

Datatypes:

1) varchar, char
2) int, smallint, numeric
3) real, double
4) date, time, timestamp
5) point [postgresql specific datatype]

Populating a Table with rows:
insert- insert into movie values('jananayagan','2026-07-15', 120);
insert into movie (release_date, movie_name, ticket) values ('2025-01-10','sirai',120);
insert into movie(movie_name, ticket) values ('karuppu',120);

select * from movie; or select movie_name, release_date, ticket from movie;
here * universal selector select all table

Select movie_name, release_date, ticket*4 as family_price from movie;
it show the value by perform this multiplication function.