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

1. "template0" is called pristine.

Questions

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

We are collecting open licensed dataset in tamil language, to build LLM, and other interesting applications in the coming days.

The ML models we build may have very short lifespan, but the open data will be there forever or at least for longer time than our life time.

Check the efforts part 1 and part 2 here.

part 1 – https://goinggnu.wordpress.com/2024/06/11/collecting-content-for-llm-dataset-part-1-tamil-wikipedia-content/

part 2 – https://goinggnu.wordpress.com/2024/06/16/collecting-content-for-llm-dataset-part-2-freetamilebooks/

here goes part 3.

Thamizh_mann publishers are publishing the public domain and nationalized tamil books for many years. Few years ago, with a collaboration with the Library at University of Toronto, Scarborough, Canada, and Thamizh_mann publishers, the kaniyam foundation team helped to release all the 1000+ tamil books as PDF and Docx formats for free online.

You can download them all here https://tamil.digital.utsc.utoronto.ca/61220/utsc35335

Thanks to UTSC, Thamizh_mann team for the great gift for the tamil Diaspora.

Now, we have 1000+ books in Unicode Docx format. Next is to convert them all as PlainText and use them. Natkeeran and Parathan helped on this.

Along with this, they helped to scrap project madurai books and tamil WikiSource books. They published all in a git repo here – https://github.com/KaniyamFoundation/open_tamil_texts along with the scripts and metadata.

I am adding those text in our open licensed tamil data collection.

Download them all here https://kaniyam.cloudns.nz/tamil_datasets/

here is the current size in text format and compressed format.

shrini@dell-optiplex-9100 v/w/h/tamil_datasets> du -h compressed
258M compressed/

shrini@dell-optiplex-9100 v/w/h/tamil_datasets> du -h text-files
355M text-files/project_madurai/data/text
355M text-files/project_madurai/data
355M text-files/project_madurai
110M text-files/tamil_wikisource/data
110M text-files/tamil_wikisource
374M text-files/FreeTamilEbooks-txt
714M text-files/thamizh_mann/data
716M text-files/thamizh_mann
1.6G text-files/

We have 1.6 G of text data to work on LLM or other works.

Go ahead, use it and build more models and tools using this data.

Hope this may not enough to get any good output. But, if we can bring something out of this, even though they are not good, then we can ask people to release their recent contents, blogs, social media posts in creative commons license.

There are few bloggers, magazines are already released their content in CC license. Now, we need your help to scarp them. If you know any programming language and can help for this project, please do webscrapping for the websites mentioned here. share the data and code.

https://github.com/KaniyamFoundation/ProjectIdeas/issues/198

Thanks for all the content providers and the contributors.

We are collecting open licensed dataset in tamil language, to build LLM, and other interesting applications in the coming days.

The ML models we build may have very short lifespan, but the open data will be there forever or at least for longer time than our life time.

Check the efforts part 1 and part 2 here.

part 1 – https://goinggnu.wordpress.com/2024/06/11/collecting-content-for-llm-dataset-part-1-tamil-wikipedia-content/

part 2 – https://goinggnu.wordpress.com/2024/06/16/collecting-content-for-llm-dataset-part-2-freetamilebooks/

here goes part 3.

Thamizh_mann publishers are publishing the public domain and nationalized tamil books for many years. Few years ago, with a collaboration with the Library at University of Toronto, Scarborough, Canada, and Thamizh_mann publishers, the kaniyam foundation team helped to release all the 1000+ tamil books as PDF and Docx formats for free online.

You can download them all here https://tamil.digital.utsc.utoronto.ca/61220/utsc35335

Thanks to UTSC, Thamizh_mann team for the great gift for the tamil Diaspora.

Now, we have 1000+ books in Unicode Docx format. Next is to convert them all as PlainText and use them. Natkeeran and Parathan helped on this.

Along with this, they helped to scrap project madurai books and tamil WikiSource books. They published all in a git repo here – https://github.com/KaniyamFoundation/open_tamil_texts along with the scripts and metadata.

I am adding those text in our open licensed tamil data collection.

Download them all here https://kaniyam.cloudns.nz/tamil_datasets/

here is the current size in text format and compressed format.

shrini@dell-optiplex-9100 v/w/h/tamil_datasets> du -h compressed
258M compressed/

shrini@dell-optiplex-9100 v/w/h/tamil_datasets> du -h text-files
355M text-files/project_madurai/data/text
355M text-files/project_madurai/data
355M text-files/project_madurai
110M text-files/tamil_wikisource/data
110M text-files/tamil_wikisource
374M text-files/FreeTamilEbooks-txt
714M text-files/thamizh_mann/data
716M text-files/thamizh_mann
1.6G text-files/

We have 1.6 G of text data to work on LLM or other works.

Go ahead, use it and build more models and tools using this data.

Hope this may not enough to get any good output. But, if we can bring something out of this, even though they are not good, then we can ask people to release their recent contents, blogs, social media posts in creative commons license.

There are few bloggers, magazines are already released their content in CC license. Now, we need your help to scarp them. If you know any programming language and can help for this project, please do webscrapping for the websites mentioned here. share the data and code.

https://github.com/KaniyamFoundation/ProjectIdeas/issues/198

Thanks for all the content providers and the contributors.

• 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

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

Introduction to PostgreSQL database – free online course in Tamil

Monday, wednesday, Friday IST evening.

First class – 18-Nov-2024 7-8 PM IST

Syllabus: https://parottasalna.com/postgres-database-syllabus/

Trainer – Syed Jafer – contact.syedjafer@gmail.com

Get the meeting link here

Telegram Group – https://t.me/parottasalna
Whatsapp channel- https://whatsapp.com/channel/0029Vavu8mF2v1IpaPd9np0s Kaniyam Tech events Calendar – https://kaniyam.com/events/

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

IPv4

1. 4th of version of Internet protocol.
2. 32 bit
3. Totally 4 blocks with 8 bit segments each. A , B , C & D.
4. 2 types of IP address.
5. Public ( Mainly using internet routing ) & Private ( Office )
6. Range --> 0 to 255
7. 0 & 255 reserved by system.
8. 127 --> loop-back address. 253 address we can use.
9. Then how to find whether its public or private ? By classes.
10. A, B , C --> Commonly used.
11. D & E --> Multi-casting & Research purpose.
12. Class A --> 0 to 127 Public , Private 10 is only used eg., 10.0.0.1 ( 16 million hosts can be declared )
13. 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.
14. Class C --> 192 to 223 Public , Private 192.168.1.1 small network for 254 hosts.
15. Class D --> 224 to 239 for multicast groups.
16. 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

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

Introduction to PostgreSQL database – free online course in Tamil

Monday, wednesday, Friday IST evening.

First class – 18-Nov-2024 7-8 PM IST

Syllabus: https://parottasalna.com/postgres-database-syllabus/

Trainer – Syed Jafer – contact.syedjafer@gmail.com

Get the meeting link here

Telegram Group – https://t.me/parottasalna
Whatsapp channel- https://whatsapp.com/channel/0029Vavu8mF2v1IpaPd9np0s Kaniyam Tech events Calendar – https://kaniyam.com/events/

• 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.

• Login with VM machine to avoid the DISPLAY issue.

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 +

Hi everyone,
KanchiLUG’s Monthly meet is scheduled as online meeting this week on Sunday, Nov 10, 2024 17:00 – 18:00 IST

Meeting link : https://meet.jit.si/KanchiLugMonthlyMeet

Can join with any browser or JitSi android app.
All the Discussions are in Tamil.

Talk Details

Talk 0:
Topic : Postgres Architecture
Description : In this talk, we will explore the architecture of postgres Duration : 30 mins
Name : Sethupandian
About : My name is Sethu and I work as a practice manager for an Insurance company in Canada. Back in India, I am from Salem. Completed my engineering in Electrical & Electronics, at Kongu Engineering College(2000-2004). Started my IT career in the year 2005 and worked in companies like Ramco Systems, Verizon, TCS, Cognizant before joining my current employer. I have always got an interest towards learning things that is fascinating. And through Payilagam and Muthu sir, I came to know about Kaniyam and KanchiLUG. I am happy to be part of this great initiative. I wish and hope I can contribute whatever possible from my side.

Talk 1:
Topic : Intro to GDB
Description : Based on my recent translation of Beej’s guide to Tamil on same. Duration : 20 mins
Name : Annamalai N
About : a GNU/Linux user interested in Embedded Systems. Final year engineering undergrad.

After Talks : Q&A, General discussion

About KanchiLUG : Kanchi Linux Users Group [ KanchiLUG ] has been spreading awareness on Free/Open Source Software (F/OSS) in
Kanchipuram since November 2006.

Anyone can join! (Entry is free)
Everyone is welcome
Feel free to share this to your friends

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 :

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

Hi everyone,
KanchiLUG’s Monthly meet is scheduled as online meeting this week on Sunday, Nov 10, 2024 17:00 – 18:00 IST

Meeting link : https://meet.jit.si/KanchiLugMonthlyMeet

Can join with any browser or JitSi android app.
All the Discussions are in Tamil.

Talk Details

Talk 0:
Topic : Postgres Architecture
Description : In this talk, we will explore the architecture of postgres Duration : 30 mins
Name : Sethupandian
About : My name is Sethu and I work as a practice manager for an Insurance company in Canada. Back in India, I am from Salem. Completed my engineering in Electrical & Electronics, at Kongu Engineering College(2000-2004). Started my IT career in the year 2005 and worked in companies like Ramco Systems, Verizon, TCS, Cognizant before joining my current employer. I have always got an interest towards learning things that is fascinating. And through Payilagam and Muthu sir, I came to know about Kaniyam and KanchiLUG. I am happy to be part of this great initiative. I wish and hope I can contribute whatever possible from my side.

Talk 1:
Topic : Intro to GDB
Description : Based on my recent translation of Beej’s guide to Tamil on same. Duration : 20 mins
Name : Annamalai N
About : a GNU/Linux user interested in Embedded Systems. Final year engineering undergrad.

After Talks : Q&A, General discussion

About KanchiLUG : Kanchi Linux Users Group [ KanchiLUG ] has been spreading awareness on Free/Open Source Software (F/OSS) in
Kanchipuram since November 2006.

Anyone can join! (Entry is free)
Everyone is welcome
Feel free to share this to your friends

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 :

• For the past few months, I was preparing for an English Exam called CELPIP. It is an exam to check the Listening, Reading, Writing and Speaking. Though we know English, preparing for an exam is an exhausting one. We took online training from “Galaxy Training Academy“. https://galaxytraining.in The coach “Jay Kumar” gave nice intro about exam pattern. He gave many mock tests and gave good feedback on how to improve, on each test. Last month, Nithya and I cleared the exam. It is a good feel to released from exam fear. Postponed many activities because of the exam preparation. Will roll out them all soon. If you are preparing for any English Exam, I suggest taking training and mock tests with “Galaxy Training Academy”.
  
  ——
  
  
• In Canada, Daylight saving ended yesterday. This happens every year in fall season and referred as “Fallback”. The clocks are moved one hour back. This is to adjust the dark winter season. It seems like all in a sudden, we got one hour extra to sleep in morning.
  
  ——
  
  
• On Oct 31, we had Deepavali, Halloween and our Marriage day. Deepavali day went with great remembering our childhood memories. The evening was filled with fun, as we went to neighbourhood houses, with friends and kids, to play “Trick or Treat”. Saw many weird, spooky decorated houses and people. Kids collected a bag full of chocolates. Last year, it was too cold. This year, the same day had a nice weather, to roam around in the evening.
  
  ——
  
  
• On Nov 1, we celebrated Deepavali with firing crackers. Bought a few crackers, which emit light. Here, we don’t get loud-full crackers like atom bombs, 1000 piece fireworks shots etc. With limited available crackers, kids enjoyed firing them, with all their friends together.
  
  ——
  
  
• On Nov 8, we are planning for a mega Deepavali event with around 250 people here. I am contributing on the planning/photography. Nithya and kids are practicing dance with their friends. Hope it will be a fun-filled evening.
  
  
  ——
  
  
• On Nov 2, gave a talk on tolkappiyam Canada monthly meeting, about our efforts on writing python code for tamil grammar rules in Tolkappiyam book. It was a good meeting. Few of the participants accepted to collaborate. You can read our progress here – https://github.com/KaniyamFoundation/ProjectIdeas/issues/214
  
  
  ——
  
  
• Few weeks ago, gave a talk on open-tamil python library, at Kanchi Linux Users Group ( KanchiLUG) meet. Video is here – https://www.youtube.com/watch?v=GtIrbvw2V-w
  
  
  ——
  
  
• Kids started going to tamil school on every Saturday morning. This week, they received books. Viyan is good at Tamil and English. Iyal started to read Tamil and English. Paari is trying to learn writing.
  
  ——
  
  
• We conduct daily meetings in a text based chat system called IRC (Internet Relay Chat). daily, 7-8 pm IST. Good to see many people are joining and discussing many things about open source software and mentoring to contribute to open source software. More details here – https://goinggnu.wordpress.com/2024/10/21/open-source-projects-mentoring-via-irc/
  
  ——
  
  
• Practicing Manual mode in photography for few weeks. Feeling like learning linux and Emacs. It gives the most flexible options and results are stunning. It is better to learn it in early days, so that we can do more magics with lighting.
  
  
  ——
  
  
  The one thing I follow in photography is – shoot a lot, share a little. I keep and share only 10%. All others are deleted. Though it is hard to select the best photos, sharing only 10% is easy for viewers and brings a Wow from them.
  
  
  ——
  
• Completed reading books in last week.
  • வாலிப வார்த்தைகள்: vaaliba vaarthaigal – book about poet Vaali – https://www.amazon.in/gp/product/B08F3KGF1B
  • எழுதுதல் பற்றிய குறிப்புகள்: (Art of Writing) – https://www.amazon.in/-/ta/dp/B09XHZYGZD kombu muLaithavan:
  • கொம்பு முளைத்தவன் – https://www.amazon.in/-/ta/dp/B07YJ4PJQK
    
    ——
    
    
    
• currently reading these books.
  • Crash Course Drupal 10 Site Building – https://www.amazon.in/gp/product/B0CKQ8MK8H
  • Archives, Ethics and the Law in India – https://ethics-law.archives.ncbs.res.in/
  • Understanding Large Language Models – https://www.amazon.ca/Understanding-Large-Language-Models-Technologies/dp/B0CJ2C8TXQ

• For the past few months, I was preparing for an English Exam called CELPIP. It is an exam to check the Listening, Reading, Writing and Speaking. Though we know English, preparing for an exam is an exhausting one. We took online training from “Galaxy Training Academy“. https://galaxytraining.in The coach “Jay Kumar” gave nice intro about exam pattern. He gave many mock tests and gave good feedback on how to improve, on each test. Last month, Nithya and I cleared the exam. It is a good feel to released from exam fear. Postponed many activities because of the exam preparation. Will roll out them all soon. If you are preparing for any English Exam, I suggest taking training and mock tests with “Galaxy Training Academy”.
  
  ——
  
  
• In Canada, Daylight saving ended yesterday. This happens every year in fall season and referred as “Fallback”. The clocks are moved one hour back. This is to adjust the dark winter season. It seems like all in a sudden, we got one hour extra to sleep in morning.
  
  ——
  
  
• On Oct 31, we had Deepavali, Halloween and our Marriage day. Deepavali day went with great remembering our childhood memories. The evening was filled with fun, as we went to neighbourhood houses, with friends and kids, to play “Trick or Treat”. Saw many weird, spooky decorated houses and people. Kids collected a bag full of chocolates. Last year, it was too cold. This year, the same day had a nice weather, to roam around in the evening.
  
  ——
  
  
• On Nov 1, we celebrated Deepavali with firing crackers. Bought a few crackers, which emit light. Here, we don’t get loud-full crackers like atom bombs, 1000 piece fireworks shots etc. With limited available crackers, kids enjoyed firing them, with all their friends together.
  
  ——
  
  
• On Nov 8, we are planning for a mega Deepavali event with around 250 people here. I am contributing on the planning/photography. Nithya and kids are practicing dance with their friends. Hope it will be a fun-filled evening.
  
  
  ——
  
  
• On Nov 2, gave a talk on tolkappiyam Canada monthly meeting, about our efforts on writing python code for tamil grammar rules in Tolkappiyam book. It was a good meeting. Few of the participants accepted to collaborate. You can read our progress here – https://github.com/KaniyamFoundation/ProjectIdeas/issues/214
  
  
  ——
  
  
• Few weeks ago, gave a talk on open-tamil python library, at Kanchi Linux Users Group ( KanchiLUG) meet. Video is here – https://www.youtube.com/watch?v=GtIrbvw2V-w
  
  
  ——
  
  
• Kids started going to tamil school on every Saturday morning. This week, they received books. Viyan is good at Tamil and English. Iyal started to read Tamil and English. Paari is trying to learn writing.
  
  ——
  
  
• We conduct daily meetings in a text based chat system called IRC (Internet Relay Chat). daily, 7-8 pm IST. Good to see many people are joining and discussing many things about open source software and mentoring to contribute to open source software. More details here – https://goinggnu.wordpress.com/2024/10/21/open-source-projects-mentoring-via-irc/
  
  ——
  
  
• Practicing Manual mode in photography for few weeks. Feeling like learning linux and Emacs. It gives the most flexible options and results are stunning. It is better to learn it in early days, so that we can do more magics with lighting.
  
  
  ——
  
  
  The one thing I follow in photography is – shoot a lot, share a little. I keep and share only 10%. All others are deleted. Though it is hard to select the best photos, sharing only 10% is easy for viewers and brings a Wow from them.
  
  
  ——
  
• Completed reading books in last week.
  • வாலிப வார்த்தைகள்: vaaliba vaarthaigal – book about poet Vaali – https://www.amazon.in/gp/product/B08F3KGF1B
  • எழுதுதல் பற்றிய குறிப்புகள்: (Art of Writing) – https://www.amazon.in/-/ta/dp/B09XHZYGZD kombu muLaithavan:
  • கொம்பு முளைத்தவன் – https://www.amazon.in/-/ta/dp/B07YJ4PJQK
    
    ——
    
    
    
• currently reading these books.
  • Crash Course Drupal 10 Site Building – https://www.amazon.in/gp/product/B0CKQ8MK8H
  • Archives, Ethics and the Law in India – https://ethics-law.archives.ncbs.res.in/
  • Understanding Large Language Models – https://www.amazon.ca/Understanding-Large-Language-Models-Technologies/dp/B0CJ2C8TXQ

• 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

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:

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

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

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