DEV Community: technonotes-hacker

Jenkins - 1

technonotes-hacker — Sun, 01 Dec 2024 22:02:31 +0000

Open source integration tool.
It can be made as centralised server by integrating multiple source like source code management , build tools , deployment environment .
Complete INTEGRATION TOOL
Its Java based.
To automate the repeated task.
CI/CD ( continuous integration and continuous delivery )
Even you can do patching.
In simple , its a centralised server.

Advantages

CI/CD
Open source
Community driven
Browser based
Supports all Operating system.
Distributed build --> Master and Slave node , how ? If you are setting up Jenkins , it uses resource of the system where its installed and accordingly the Jobs will take the RAM , CPU and resources. To avoid this "Distributed build" was introduced , so that "Master and Slave node" with that the load will be distributed.
We can increase the scalability of master and slave.

CI/CD Workflow

Commit
Build
Test
Stage
Deploy

Stages will be different in other CI/CD process. Purpose will be the same.
Dedicated branch for each environment it will be a best practice to avoid any confusion here.

Installation & Configuration

sudo apt-get update
java -version

Add the official key of Jenkins and then the repo.

sudo wget -O /usr/share/keyrings/jenkins-keyring.asc \
  https://pkg.jenkins.io/debian-stable/jenkins.io-2023.key
echo "deb [signed-by=/usr/share/keyrings/jenkins-keyring.asc]" \
  https://pkg.jenkins.io/debian-stable binary/ | sudo tee \
  /etc/apt/sources.list.d/jenkins.list > /dev/null
sudo apt-get update
sudo apt-get install jenkins

Now JENKINS had been installed , please check with command also whether its installed or not.

dpkg -l | grep -i jenkins

As we have encountered error , install higher version of Java from 11 to 17 or 21.

sudo systemctl start jenkins
sudo systemctl enable jenkins
sudo systemctl status jenkins

Now open the console with the IP of the system.

If you want to know the username , then go to below location.

cat /var/lib/jenkins/users/users.xml
/var/lib/jenkins/users/admin_17980521444909415742

want to change the password , edit and restart the jenkins.

<hudson.security.HudsonPrivateSecurityRealm_-Details> <passwordHash>#jbcrypt:$2a$10$OTYB2osGPi/rasutjHcYOOhByiCoaEZTEQk52CABOwYdrtxeIPnBu</passwordHash> </hudson.security.HudsonPrivateSecurityRealm_-Details>

Give no password for jenkins user ( as its a internal user )

Check whether able to get output without sudo password.

User Management & security

Manage Jenkins > Security

Trying to use the OS user for security features , lets try.
Add the user to the group shadow like below and restart the Jenkins.

Now its integrated with the OS user (sathishpy1808) , you can even login with the OS users after integrating.

Jenkins own database

User created in Jenkins only can login , OS users can't.

User creation

Change to "Jenkins own database" then only you can view the user creation option like below ,

Go to the ravi user profile and explore.

Even you can terminate all sessions from the console.
You can change the password also and delete option is also available.

Authorisation

what type of authorisation is given to the users , you can see many options in the below screen.

BY default --> "logged-in users can do anything" its equal to admin access --> its not advisable.
Try to use with user based ,

Also ROLE based plugin it will be useful , lets install ( mostly used plugin for role based authorisation )

Manage & roles won't be available at first , it will come after the plugin installation only.

Manage roles and assign roles are very important option.
Lets create a role and then assign the users.
Pattern based users are created only in "Item Roles". Here if any user starts with word "Manage" , it should the managing job roles.

Create some Jobs to test this user specification.

Item roles in "Assign Roles" will be useful for pattern description for users.
You can see all roles which are added.
All users are added in this console.

Job Management

Jobs needs to be created to perform a task.

Build Triggers

"Trigger builds remotely (e.g., from scripts)" --> it will trigger once some other task gets triggered.
"GitHub hook trigger for GITScm polling" --> once the code gets committed then only the job needs to be triggered.

Build Environment

Build Steps

This is the main step , you can add number of steps here.
Raw commands , shell script , etc.
Can be added like stages.

Post-build Actions

Notes

https://www.jenkins.io/doc/book/installing/linux/ --> In this site , you can get the details of the key and the repo details which we have added in the first of the installation part. [TBD]
What is "openjdk-17-jdk-headless package" ?
Default port of Jenkins is 8080.
jenkins user will be always running.

Commands Used

java -version
locate jdk
whereis java

List of errors

Job for jenkins.service failed because the control process exited with error code.

Now you can get the details by typing ,

journalctl -xeu jenkins.service

Error clearly says , ( version not supported )

Dec 01 22:03:57 meet.sathishpy1808.org jenkins[40153]: Running with Java 11 from /usr/lib/jvm/java-11-openjdk-amd64, which is older than the minimum required version (J>
Dec 01 22:03:57 meet.sathishpy1808.org jenkins[40153]: Supported Java versions are: [17, 21]
Dec 01 22:03:57 meet.sathishpy1808.org jenkins[40153]: See https://jenkins.io/redirect/java-support/ for more information.
Dec 01 22:03:57 meet.sathishpy1808.org systemd[1]: jenkins.service: Main process exited, code=exited, status=1/FAILURE

Access Denied

change the ownership to the current user,

sudo chown -R sathishpy1808:sathishpy1808 /var/lib/jenkins/
sudo chown $(whoami) /var/lib/jenkins

Dangling meta character '*' near index 0

Postgres Session 03

technonotes-hacker — Wed, 27 Nov 2024 19:02:49 +0000

Lets start to bug the Postgres

https://github.com/syedjaferk/postgres_sample_database/blob/main/dvd_rental/dvdrental.tar

Above all screenshots are my mistakes , I should use my OS user as POSTGRES then execute all commands.

Select queries

Commands

to drop the database --> DROP DATABASE ;
"-\" --> list the database

Postgres - Session 02 ( Architecture )

technonotes-hacker — Mon, 25 Nov 2024 02:27:11 +0000

Who uses the DB frequently ? Application.

Lets deep dive into Architecture

Post Master

ஒரு specific portல incoming requestஅ collect பண்ணி வச்சிக்கும். It just re-direct.

Backend process pool uses RAM & CPU so we can't give any number. It will assigned with a specific value , if all are used then Backend process pool will ask the request to wait until the other request gets completed. Each Backend process pool as separate VIRTUAL memory. it won't all data , but specific information related to request. Even Backend process pool won't execute the request.

Now Backend process pool ( BP ) will re-direct to Backend workers pool.
Eg BP - 100 & BW - 100.
Status of these gets changed like ACTIVE , IDEAL , etc
Even this Backend workers pool will have separate MEMORY. ( keep in mind )

Query example

Take an example of the below one,

select * from employee where first_name=john ;

It will go to the Backend workers pool , then " select * from employee " goes and search in SHARED BUFFER. If its there then it returns the response.
If the result is not available in SHARED BUFFER , then it goes to DISK.
Here the catch is , even the SHARED BUFFER size is limited. This can be configured.
For eg., இப்ப அந்த SHARED BUFFERல 10 mb size allocate பண்ணி இருக்கு , அடுத்த request உள்ள வருது , SHARED BUFFERல இந்த request இல்ல so it goes to DISK and then gets the output of 20 mb file size . இப்ப ஏற்கனவே இருந்த 10 mb size file will be erased and new 20 mb file will be saved in that SHARED BUFFER.
It will be very fast when the output comes from SHARED BUFFER.
WORKER Memory will take the order by , sort , etc other than select ( main query ).

Auxiliary Process

WAL WRITER - 1

Write Ahead Log
If will take all the backup of the request and the query.
It as separate buffer space.
It will also go to DISK.
The command comes to WAL from two places 1. BACKEND WORKER 2. BACKGROUND WRITER.

DIRTY PAGES

When the output gets stored in the SHARED BUFFER , at the same time a DIRTY PAGES gets created. why it gets created ? Eg., If there is any update in the query which is stored in SHRED BUFFER , then it updates but this is not saved in DISK.
If the update or any operation didn't go to main DISK , then it will create a DIRTY PAGES.
At the same time BACKGROUND WRITER , takes the notes of DIRTY PAGES and asks WAL WRITER to take a not of it.
The uncommitted task still goes to OS FILE SYSTEM from DIRTY PAGES . Note : still the commit didn't reach the MAIN DISK.

WAL WRITER - 2

If there is any crash in SHARED BUFFER , then we can get it from WAL WRITER.
Data won't be recovered but the query can be recovered for the WAL.

WAL ARCHIVER

WAL WRITER will put all details to WAL ARCHIVER.

CHECK-POINTER

Whatever comes to OS FILE SYSTEM , CHECK-POINTER will take care to make sure that it reaches the DISK properly.

AUTOVACCUM

PostgreSQL works in UPPEND method , it takes a clone of the table and then it updates.
PostgreSQL is Multi-version control.
It will clean all the STALE process.
Eg.,

Arun , 24 , 900 - STALE Raj , 23 , 901 There is an update in Arun Mark , Arun , 24 , 910

இதுல Arunகு மட்டும் updation இருக்கு but இதில் update பண்ணும் போது தனியா ஒரு row create பண்ணி like duplicate and then update takes place.
AUTOVACCUM will clean all STALE rows.

STARTUP PROCESS

It will start at first and gets all data from WAL Archive Folder , then only Postgres allows the request.
Why we need this startup process ? So that all got closed properly.
This will happen before the "Post Master".

Replica

Standby unit.
Its like clone OR Master & Slave concept.
It receives all details from WAL sender.

Notes

In windows command path , if you give just "path". It will display all paths which are configured.
https://miro.com/app/board/uXjVLD2T5os=/

Postgres - Session 01

technonotes-hacker — Sat, 23 Nov 2024 05:11:30 +0000

Installation over Oracle Linux

Version of the Linux Distro which I am using ,

cat /etc/os-release NAME="Oracle Linux Server" VERSION="7.9" ID="ol" ID_LIKE="fedora" VARIANT="Server" VARIANT_ID="server" VERSION_ID="7.9" PRETTY_NAME="Oracle Linux Server 7.9" ANSI_COLOR="0;31" CPE_NAME="cpe:/o:oracle:linux:7:9:server" HOME_URL="https://linux.oracle.com/" BUG_REPORT_URL="https://github.com/oracle/oracle-linux" ORACLE_BUGZILLA_PRODUCT="Oracle Linux 7" ORACLE_BUGZILLA_PRODUCT_VERSION=7.9 ORACLE_SUPPORT_PRODUCT="Oracle Linux" ORACLE_SUPPORT_PRODUCT_VERSION=7.9

Install the repository RPM,

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Install PostgreSQL,

sudo yum install -y postgresql13-server

Optionally initialize the database and enable automatic start

sudo /usr/pgsql-13/bin/postgresql-13-setup initdb [TBD] sudo systemctl enable postgresql-13 sudo systemctl start postgresql-13

initdb

This file will create all DB related files , directories & Configuration files.

Also creation of system catalogs --> what is this ? --> It will store all indexes , roles , etc. --> you can see list of catalogue using below statement.

postgres=# select relname from pg_catalog.pg_class where relkind='r';

sudo -i -u postgres psql

By default a user , a role and a database will be created.
All the above 3 will be created in same name "postgres".
Then how to check the roles ?

postgres=# \du

Now see the list of databases, ( these 3 are created by postgres by default with the help of template1.

postgres=# \l

If you try to create a new DB , then it will follow the "template1", its like the blueprint.
Then why template0 ? --> you can edit template1 but not template0 ( you can't change and its not editable ).
so in short template0 --> NOT EDITABLE & template1 --> EDITABLE.

WAL

WRITE AHEAD LOG.
If the database gets crashed , with this help we can retrieve the data.

Roles

Librarian , Library member and visitor all these are roles.

Lets create a dummy role and test ,

You can see "Cannot login" , that means you can't login as it's not assigned.

creating one more ROLE,

Assigning the permissions or roles,

Here you can't see 'CANNOT LOGIN'.
If a ROLE can login then he/she is the user.
User can inherits the parents also.

How it works ?

Postmaster

All connection request goes first here. ( whatever client ask , server will respond )
Follows client server architecture.

WAL Writer

It will be write in a notebook , to keep the track of all.

Checkpoint

It periodically checks and responed.

Auto Vaccum

It periodically checks.

Stats Collector

Gathers all stats Eg., if a table is getting more request then it will collect the information and try to keep it in cache to respond very fast and also check whether index is there for the table.

Archiver

To backup the data.

Reference

https://www.postgresql.org/download/linux/redhat/ --> This site will provide steps to install in different distro's.

Notes

"template0" is called pristine.

Questions

what command initialize a PostgresSQL database cluster? initdb
which role is automatically created during PostgresSQL installation ? postgres
what is the purpose of template0 in PostgresSQL ? Pristine , unmodified template.
which PostgresSQL template database is used as the default for creating new databases ? template1
how to connect PostgresSQL interactive terminal ? psql
List all databases ? \l
Architecture of PostgresSQL ? Client Server

SQL Loader

technonotes-hacker — Wed, 20 Nov 2024 06:23:15 +0000

Its nothing but " Bulk Loader Utility ".
With this concept we can load the data to the table in bulk.
Main word is LOAD.
Then comes to your mind , what is the difference between load and insert ? Insert happens one by one.Load happens in one go.
what data ? which table ? loading script ? Execute --> these are the four things YOU NEED TO KEEP IN MIND.

Flat files --> csv ( comma separated value ) , txt , dat , excel , etc.
Always use notepad to load the data.

select employee_id || ',' || first_name || ',' || salary from employees_table where rownum <= 10 ; --> this will fetch only 10 rows.

save this file in a folder as csv.

select employee_id || ',' || first_name || ',' || salary from employees_table where employee_id between 150 and 170 ; --> this will fetch rows between those values.

Save this file as txt.

Now coming to table creation

create table sample(id number , name varchar2(25) , salary number);

Now coming to creation of script

loading script or control file both are same.

load data infile 'path_of_the_file.csv' infile 'path_of_the_file.txt' insert into table sample fields terminated by ',' (id,name,salary)

create the script and save as ALL FILES ( notepad ) with .ctl file.

Now coming to Execute

sqlldr hr_schema_name/password control='file_location_of_control_file_or_execution_file' direct = true

here why direct=true --> it will load very fast and it will by-pass all constraints and triggers.
if direct=false --> constraints and triggers it will check and then it execute.

In short ,

Excluding one column

If you some column should not be loaded , then use FILLER.

load data infile 'path_of_the_file.csv' infile 'path_of_the_file.txt' insert into table sample fields terminated by ',' (id,name,salary filler)

load data infile 'path_of_the_file.csv' infile 'path_of_the_file.txt' insert into table sample fields terminated by ',' (id,name filler,salary)

In above example , salary and name will be empty . It won't load the data.

Condition

WHEN --> loading data should obey the condition which you give. If the condition fails , then it stores the failed data in DISCARD FILE.
If there is Oracle error , then it gets captured in BAD FILE.

WHEN condition should be used here,

load data infile 'path_of_the_file.csv' infile 'path_of_the_file.txt' insert into table sample when ? fields terminated by ',' (id,name filler,salary)

How to get the process summary ?

It will be stored in log file.
you can set all the files in the command itself , like below.

sqlldr hr_schema_name/password control='file_location_of_control_file_or_execution_file' log = summary.log bad = sample.bad discard = sample.dsc direct = true

If you are giving any file name here , then it will generate automatically.
So Import take here is ,

skip

If you want to skip the rows while loading , then you can specify in the command itself.

sqlldr hr_schema_name/password control='file_location_of_control_file_or_execution_file' skip = 2 direct = true

2 rows will be skipped.

Notes

SQL loader short key word is sqlldr.
insert into table sample --> this will work only when the table is EMPTY. If you try to execute again , then it throw below error.

so you can use ,

load data infile 'path_of_the_file.csv' infile 'path_of_the_file.txt' append into table sample fields terminated by ',' (id,name,salary)

Also you use truncate ( it will delete old data and insert new data again )

load data infile 'path_of_the_file.csv' infile 'path_of_the_file.txt' truncate into table sample fields terminated by ',' (id,name,salary)

Task

For a particular column instead of (,) separated it's used as (#) - how to load ?
how to load the excel file ?

Azure VNET

technonotes-hacker — Fri, 15 Nov 2024 19:43:07 +0000

Network --> communications between devices.
IP Address --> unique identifier to each device which is internet protocol address.

IPv4

4th of version of Internet protocol.
32 bit
Totally 4 blocks with 8 bit segments each. A , B , C & D.
2 types of IP address.
Public ( Mainly using internet routing ) & Private ( Office )
Range --> 0 to 255
0 & 255 reserved by system.
127 --> loop-back address. 253 address we can use.
Then how to find whether its public or private ? By classes.
A, B , C --> Commonly used.
D & E --> Multi-casting & Research purpose.
Class A --> 0 to 127 Public , Private 10 is only used eg., 10.0.0.1 ( 16 million hosts can be declared )
Class B --> 128 to 191 Public , Private 172 is only used eg., 172.16.0.1 to 171.16.255.254 ( 65,536 hosts can be declared ) - Med size networks.
Class C --> 192 to 223 Public , Private 192.168.1.1 small network for 254 hosts.
Class D --> 224 to 239 for multicast groups.
Class E --> 240 - 255 for research purpose.

As a whole , the private range is .

A --> 10.0.0.1
B --> 172.16.0.0
C --> 192.168.0.0

Subnetting

Slashing the network.

Virtual Network in Azure

Software based network connects virtual machines.

Subnet in Azure

Subdivison of VNET.
we can organise the resources within a network.
Features as follows ,

Azure Portal

All service >> Networking >> Virtual Networks >> Create

Gave a wrong IP , then you can see a prompt is coming.

VNET & Subnet creation

Notes

DNS , DHCP & Gateway , 255 Broadcast --> 4 IP's are reserved.

SETUP Oracle DB in Linux ( Standalone )

technonotes-hacker — Sun, 10 Nov 2024 04:51:09 +0000

Install Oracle Virtual Box , Winscip & Putty.
Load the lab.
Change the network setting from NAT to Bridged Adapter.

Click ON & OFF its like restart.
IP which is assigned 192.168.1.104.
Assign any IP from 192.168.1.(0 to 255) , so I have assigned as 192.168.1.104. This IP taken from IPv4 in laptop.
Open Terminal in the screen by right click.

Ping Google and check whether its working or not.

Now you can login with your IP which is assigned.

Now we need to understand the concept of SHARED FOLDER.
You can share the SOFTWARES to the VM from laptop folder , no need to use WINSCP. Lets explore.

Lets create directories and unzip the software.

mkdir -p /u01/app/oracle/product/19.0.0.0/dbhome_1 unzip /media/sf_Software/V982063-01-001.zip -d /u01/app/oracle/product/19.0.0.0/dbhome_1

Install all RPM required for DB installation

Once you install this RPM , oracle user will be created automatically.

we don't know the password of ORACLE user , now set it.

password is oracle and user : oracle
Now run the installer.
Before that execute xhost + --> Received error , its mainly used to set the DISPLAY.

Getting error while installing the runinstaller .

May be because of LOGIN root user. Lets try with new session with ORACLE user.

Now lets create Database

Password is ORACLE

DB installation is completed.
How many Oracle Softwares are installed in the servers ? How to check ? oraInventory file will help to do this.

Now comes the question , how to find the Inventory location ?

How many databases are created in the server , if this file is changes , complete database may corrupt.

How many are running in this server , that is the DB server ?

ps -ef|grep pmon
ps -ef|grep smon

How to start the DB ?

As I have faced issue with SID , changed completly to new SID and started the server.

Notes

Oracle Home / Oracle Software / Database Software / Database Home --> All these are same.
How many Oracle Softwares are installed in the servers ? How to check ? oraInventory file will help to do this.

Now comes the question , how to find the Inventory location ?

How many databases are created in the server , if this file is changes , complete database may corrupt.

How many are running in this server , that is the DB server ?

ps -ef|grep pmon
ps -ef|grep smon

Error & Solution

https://superuser.com/questions/1755254/virtualbox-guest-additions-update-got-error-missing-selinux-target-policy-file

Linux Commands Used

yum install oracle* --skip-broken 
yum -y install oracle-database-ee-19c
yum -y install oracle-database-ee-21c
# groupadd dba
# groupadd oinstall 
# useradd -g oinstall -G oinstall,dba oracle
#chown -R oracle:oinstall /u01

cd /u01/app/oracle/product/19.0.0.0/dbhome_1 --> Tool used to install Oracle Home / Database Home?
./runInstaller
cd /u01/app/oracle/product/19.0.0.0/dbhome_1/bin --> Tool used to create database?
./dbca

cat /u01/app/oraInventory/ContentsXML/inventory.xml
cat /etc/oraInst.loc
cat /etc/oratab
ps -ef|grep smon
ps -ef|grep pmon
su - oracle
. oraenv
env |grep ORA
sqlplus / as sysdba
passwd oracle
xhost +

ODI STUDIO Installation - WINDOWS

technonotes-hacker — Sat, 09 Nov 2024 14:40:06 +0000

Sorry GUYS its WINDOWS

Run as Administrator

Error & Solution

This is working
https://stackoverflow.com/questions/78013258/unable-to-launch-jdeveloper-for-missing-msvcr100-dll
Also try to install this,
https://learn.microsoft.com/en-gb/cpp/windows/latest-supported-vc-redist?view=msvc-170#latest-microsoft-visual-c-redistributable-version

Notes :

If its windows installation then always install via ADMIN user.
If you want to set JAVA , do the below but most of the cases its not required.
Many issues with Java download , the JDK should have JRE also . Please check after installation of Java.
It may take some time please wait patiently.

SQL - Dia 10

technonotes-hacker — Wed, 06 Nov 2024 04:33:49 +0000

Null Functions

nvl
nvl2
nullif
coalease

select 'NULL',null,'',' ' from dual;

Only 2 & 3 output are the NULL values.

nvl

if the value is NULL then print the value in the right hand side value.
Only two arguments are passed. ( Input , what to replace )

select nvl('',0) from dual; select nvl(NULL,0) , nvl(NULL , 'A') from dual;

As the left side argument is NOT null then it prints the same value.

nvl2

3 arguments.
( 'infosys' , 2 , 'B') --> If the first argument is NULL then 3rd arguments will be printed or else second argument will be printed.

select nvl2('A',0,4) from dual; select nvl2('',0,4) from dual;

nullif

two arguments
both should be NULL.

select nullif(1,7) , nullif('aa','aa') from dual; select nullif(1,7) , nvl(nullif('aa','aa'),'A') from dual;

coalesce

only one row.
It prints first NOT NULL Value.
It accepts n no of arguments, no limit.

select NULL, NULL , 30 , 40 , NULL from dual;
select coalesce(NULL, NULL , 30 , 40 , NULL) from dual;

Conversion Function

to_char
to_number
to_date
to_timestamp

to_char

select sysdate from dual ; select to_char(sysdate,'DAY'),to_char(sysdate,'day'),to_char(sysdate,'Day'),to_char(sysdate,'D'),to_char(sysdate,'DD') from dual ;

--select sysdate from dual ; select to_char(sysdate,'DAY'),to_char(sysdate,'day'),to_char(sysdate,'Day'),to_char(sysdate,'D'),to_char(sysdate,'DD') from dual ; select to_char(sysdate,'MONTH'),to_char(sysdate,'MONTH'),to_char(sysdate,'Month'),to_char(sysdate,'MM') from dual ;

select to_char(sysdate,'YEAR'),to_char(sysdate,'Year'),to_char(sysdate,'yyyy'),to_char(sysdate,'yy') from dual ;

select to_char(sysdate,'IW'),to_char(sysdate,'W'),to_char(sysdate,'Q') from dual ;

To Number

--select '123' , 123 , to_number('123') from dual ; - how to check its converted to number ? select '123' , 123 , reverse (to_number('123')) from dual ;

to_date

select '2024/12/24' , to_date('2024/12/24', 'yyyy/mm/dd') from dual ; select to_date('24-DEC-24', 'MON-YYYY-DD') from dual ; --> Error select to_date('DEC-2024-24', 'MON-YYYY-DD') from dual ;

to_timestamp

select TO_TIMESTAMP('DEC-2024-24 16:24:00', 'MON-YYYY-DD HH24:MI:SS') from dual ;

Note :

Anything which is given within single quotes is always STRING.
Joins , sub query , group functions , Analytics Functions & Set Operators are very import in SQL.
what is Index , global temp table , Objects , views , sequence ( why we use ? )
Reverse function will work only for STRING not for Numbers.
what is the dataype of NULL ? Its NULL only.
select sysdate , systimestamp from dual; --> Check this

In SQL plus it will be different :

SQL - History - A Journey

technonotes-hacker — Sun, 03 Nov 2024 10:35:53 +0000

RDBMS language.
Its ANSI --> standard language for operational relational databases.
Efficient , easy to learn and use.
Functionally complete.

How SQL Works

Data Sublanguage.
works with logical level.
common language for all relational databases.

SQL Statement types

Data Manipulation Language DML

Data Definition Language DDL

Data Control Language DCL

Transaction Control Language TCL

Oracle SQL Development Environments

Below are the Tools used for SQL:
Oracle SQL Developer --> debug , export , view reports ,etc
SQL Plus --> Batch query tool.
Oracle JDeveloper --> Multi-platform tool for web services.
Oracle Application Express --> Like a web browser.

Oracle SQL Developer

Concept on RDMS ( Relational Database Management Systems )

Relational Database --> Collection of relations or two-dimensional tables controlled by server processes.
Before entering RDMS , What is Data Model ?
Entity Relationship Model --> its business specifications.
Then Modelling conventions --> its called Entity Relationship Modelling conventions.

Relating Multiple Tables --> 1. Each row is unique by primary key 2. Logically a foreign key can be related with multiple tables.

HR Schema Model

SQL Statement

Not case sensitive.
one line or more lines.
keywords can't be across lines or it can't be abbreviated.
Clauses used in separate lines , what is clause here ? The main clauses are SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, INSERT, UPDATE, DELETE, and JOIN. [ TBD ]
We can use indents so that one have better enhanced readability.

dualis a table created by Oracle Database and its owned by SYS.
dual as one column called DUMMY( data type VARCHAR (1)) and contains one row with value x.
we can use to compute some expressions.

select * from dual;
select sysdate from dual;

Arithmetic Expression

It reads from LEFT to RIGHT. L--->R

You can see the difference after using Parenthesis.

select first_name,2*(salary+300), 2*salary+300 from hr.employees;

NULL--> what is the value of it ? its like unavailable , unassigned , unknown or inapplicable.
NULLis not as same as ZERO or BLANK SPACE.

Column Aliases

Rename the column headings
Column followed by AS.
without AS also you can declare.

In the column name , it becomes CAPS. To avoid this you can use "" for AS like below,

-- select first_name AS name , last_name as father_name from hr.employees;
select first_name AS "name" , last_name as "father_name" from hr.employees;
select first_name first  , last_name last  from hr.employees;

Concatenation Operators

To link the column or character strings to other column.
||

select first_name||last_name AS "name"  from hr.employees;

Literal Character Strings

Its a character , number or a date included in the select statement.
Date & character should be enclosed with single quotation marks.
Each character string is the output of each row.

select first_name || ' of ' || last_name OUTPUT from hr.employees;

Alternate Quote Operator

we can use own quotation mark delimiter.
specified with 'q'.
To increase the readability.

select department_name || q'[ Departments Name is :]' || manager_id "Department and Manager" from hr.departments ;
select department_name || 'Departments Name is :' || manager_id "Department and Manager" from hr.departments ;

DISTINCT Keyword

default select query will display all rows irrespective of duplicate rows.
To eliminate the duplicate we can use DISTINCT keyword.

-- select department_id from hr.employees ;
select distinct department_id from hr.employees ;

DESCRIBE Command

Its not a KEYWORD.
To display table structure.
Alternatively we can see in SQL developer in the column Tabs.

ORACLE QUESTIONS FOR CLEARING THE LEARNING EXAM

SQL - Dia 9

technonotes-hacker — Sat, 02 Nov 2024 19:01:01 +0000

General Functions

case
decode

case

its for condition.
Relation and logical operators will be used when implementing CONDITIONS.
It needs to have END word while finishing.

-- select first_name , salary , salary*10/100 from hr.employees; 10 % increment but we need only > 10000 ? how to do ?
-- select first_name , salary , CASE WHEN SALARY > 10000 THEN salary*10/100 END from hr.employees;
select first_name , salary , CASE WHEN SALARY > 10000 AND FIRST_NAME LIKE 'A%' THEN salary*10/100 END from hr.employees;

decode

NO Relation or logical operators will be used when implementing CONDITIONS.
decode ( input , p1 , p1 , p2 , p2 )
Read two two.

select decode (1,2,4,1,0) from dual;

என்னுடைய input வந்து 1 , now read two by two , the next inputs. ‘1’க்கு அடுத்தது ரெண்டு ரெண்டா படிங்க . இதுல input வந்து ஒன்று , என்னுடைய input ‘2’டா இருந்ததுன்னா 4 என்று print பண்ணனும் but our input is ‘1’.
என்னுடைய input ‘1’ இருந்ததுன்னா 0 என்று print பண்ணனும் but our input is ‘1’
So answer is 0.

select decode (1,2,4,8,0,5) from dual; --> here 5 is else part.

Now you will get data for all , because you have given else part in the query.

-- select first_name , salary , CASE WHEN SALARY > 10000 AND FIRST_NAME LIKE 'A%' THEN salary*10/100 END from hr.employees;
select first_name , salary , decode ( salary , 24000 , salary*10/100 , 17000 , salary*10/100 ) from hr.employees;

select first_name , salary , decode ( salary , 24000 , salary*10/100 , 17000 , salary*10/100 , salary*5/100 ) from hr.employees; --> Else is implemented.

select first_name , salary , CASE WHEN SALARY > 10000 THEN salary*10/100 
    WHEN SALARY < 10000 THEN salary*9/100 
    ELSE salary*5/100
    END AS BONUS from hr.employees;

-- select first_name , salary , COMMISSION_PCT , CASE WHEN COMMISSION_PCT IS NULL THEN 1.5 END from hr.employees;
select first_name , salary , CASE WHEN COMMISSION_PCT IS NULL THEN 1.5 ELSE COMMISSION_PCT END AS COMMISSION_PCT from hr.employees;

select FIRST_NAME , Hire_date , to_char (Hire_date,'day') from hr.employees;

To_char used to convert.

Notes:

19c and 23c --> Oracle
what is normalisation ?
Performance tuning ?
how to delete the duplicate ?
case is faster than decode , both are general functions .
D --> Day

SQL - Dia 8

technonotes-hacker — Sat, 02 Nov 2024 11:42:10 +0000

Number Function

Round
Trunc
Mod
Ceil
Floor
Abs
Sign

Round

If there is number => 5 after the decimal point , it will round off to the next number otherwise it will remain the same.

select round(67.34) from dual;
select round(67.49) from dual;
select round(67.57) from dual;

Trunc

It wont see any number after decimal.
Trunc is used only with the NUMBERS.
Lets do one more operation with trunc --> trunc(56.89898,2) means it will take the two digit after the decimal.

select trunc(67.7846434) from dual;
select trunc(67.10001) from dual;
select trunc(67.011234) from dual;

select sysdate from dual; --> it will display time also
select trunc(sysdate) from dual;

Mod

It will give the remainder.

select mod(10,7) from dual;
select mod(61,10) from dual;
select mod(6,2) from dual;

Ceil

It will go to the next number, whatever may the number.

select ceil(67.7846434) from dual;
select ceil(67.00001) from dual;
select ceil(67.011234) from dual;

Floor

It will crop the decimal.
Then what is the use of Trunc ? In trunc we can use, how many decimal can be included. Hope you remember.

select floor(67.7846434) from dual;
select floor(67.00001) from dual;
select floor(67.011234) from dual;

select floor(67.7846434),trunc(67.7846434,1) from dual;
select floor(67.00001),trunc(67.00001) from dual;
select floor(67.011234) , trunc(67.011234,0) from dual;

Abs

Only + & - are allowed.
It will remove the sign.
There is one more function similar to this sign i.e SIGN, it will give 1 & -1.

select abs(67.7846434), abs(-67.7846434), abs(10-15), sign(-10) , sign(+67.89) from dual;

Date Function

add_months
months_between
next_day
last_day

add_months

if you want to add MONTHS then this function is used.

select sysdate, sysdate+2 , sysdate+20 , sysdate-10 from dual; -- Days increases
select sysdate from dual;
select add_months(sysdate,1) from dual;
select add_months('01-DEC-2024',1) from dual;
select add_months(sysdate,-1) from dual; -- months increases

select * from hr.employees where hire_date < add_months(sysdate,-276) -- hired before 23 years , 276 is the month

months_between

Displays the no of months , sometime it will give in negative number that means subtraction happens between months Jan is 1 and Dec is 12.
Even the date will be considered.

-- select months_between((01-01-20024), (01-12-2024)) from dual;
-- select months_between((01-jan-2024), (01-dec-2024)) from dual;
select months_between(('01-JAN-2024'), ('01-DEC-2024')) , months_between(('01-DEC-2024'), ('01-jan-2024')) from dual;
select months_between(('17-JAN-2024'), ('01-DEC-2024')) , months_between(('01-DEC-2024'), ('25-jan-2024')) from dual;

If you don't like the decimal , what you can use ? For sure I forgot . Can't remember very quickly . After reading my notes , found its "TRUNC or FLOOR".
Find my age ? How ?

select months_between(('01-DEC-2024'), ('01-DEC-1989'))/12 from dual;
select months_between(('01-DEC-2024'), ('01-DEC-1989'))/12 AS AGE from dual;

next_day

Also will order you can specify.
Sunday --> 1 , Saturday --> 7

select sysdate from dual ;
select next_day(sysdate,'sunday') from dual;
select next_day(sysdate,1) from dual;

last_day

To find the last day of the month.

select last_day(sysdate) from dual;
select last_day('01-feb-2024') from dual;

General

greatest
least
distinct
unique
case
decode
concat
||

Greatest

select 12,67,98,90,100 from dual;
select greatest(12,67,98,90,100) , least (12,67,98,90,100) from dual;

Least

select 12,67,98,90,100 from dual;
select greatest(12,67,98,90,100) , least (12,67,98,90,100) from dual;

Distinct & Unique

It will check the duplicate.
Both will be same .

select unique FIRST_NAME , last_name from hr.employees;
select distinct FIRST_NAME , last_name from hr.employees;

Concat & ||

select concat(concat(FIRST_NAME,LAST_NAME),salary) from hr.employees;
select FIRST_NAME || LAST_NAME ||' '|| salary from hr.employees;

Note

In the Date function --> only months_between --> it returns number.