PostgreSQL Day-1 Database, RDBMS ,postgreSQL-Installation

Database:

--> A database is an organized collection of data stored and accessed electronically.
--> Databases can store structured, semi-structured, or unstructured data, such as text, images, videos, and files, making them indispensable for modern applications.
--> They are managed using Database Management Systems (DBMS), which provide tools for creating, retrieving, and modifying data.

Relational Database Management System:

--> A relational database management system (RDBMS) is a program used to create, update, and manage relational databases.
--> RDBMS is a type of DBMS that stores data in tables, while DBMS can store data in files
--> Some of the most well-known RDBMSs include MySQL, PostgreSQL, MariaDB, Microsoft SQL Server, and Oracle Database.

postgreSQL

--> PostgreSQL is an free open-source database system that supports both relational (SQL) and non-relational (JSON) queries.

--> PostgreSQL is a back-end database for dynamic websites and web applications.

--> PostgreSQL's psql works directly in the terminal.No need of separate UI tool.

--> PostgreSQL, by default, is case-sensitive for identifiers (table names, column names) and case-insensitive for string comparisons (unless explicitly changed).

Datatypes:

1. varchar,char
2. int,smallint, numeric
3. real,double
4. date,time,timestamp
5. point(postgres specific datatype)

Installation steps:

Refer: https://www.devart.com/dbforge/postgresql/how-to-install-postgresql-on-linux/

sudo -i -u postgres
[sudo] password for guru:          
postgres@guru-Aspire-A315-58:~$ psql
psql (16.6 (Ubuntu 16.6-0ubuntu0.24.04.1))
Type "help" for help.

postgres=# \c movie
You are now connected to database "movie" as user "postgres".
movie=#

To create database:

create database Movie;

Run these commands in terminal to enter postgres terminal and enter inside psql to run sql commands.

To create Table:

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

To insert values inside 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 into movie(movie_name, ticket) values('leo', 120);

Output of table:

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

To select only the movie name from the movie:

select movie_name from movie;

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

 empid |   name   |     designation      |   dept    | salary 
-------+----------+----------------------+-----------+--------
   101 | gopi     | junior executive     | Finance   |  17000
   102 | Guru     | Senior developer     | IT        |  70000
   103 | Pritha   | full stack developer | IT        |  80000
   104 | Gokul    | junior hr            | HR        |  18000
   105 | prasanna | Sales Head           | Marketing |  20000
   106 | krishna  | Accounting Manager   | Accounts  |  30000
(6 rows)

Task:2
Inserting multiple rows using single insert statement

insert multiple rows using a single INSERT statement by specifying multiple sets of values in the VALUES clause. The syntax is:

INSERT INTO table_name (column1, column2, column3)
VALUES
    (value1_1, value1_2, value1_3),
    (value2_1, value2_2, value2_3),
    (value3_1, value3_2, value3_3);