Day 16: SQL Introduction and Data Types

Hello Dear Students,
  Hope you all are doing good.

Today we are going to cover our SQL introduction and data types. As most of students wants for Hindi language learning. Thus, this topic is in Hindi language for you. Do message us if you want us to continue in Hindi language. 

Let's get started...

SQL - SQL stands for Structured Query Language. SQL ek query language hai jo ki almost saare hi RDBMS use karte hain. Jaise ki ek language normal samajhne ke liye hoti hai waise hi ek RDBMS ko samajhne aur uski queries ko perform karne ke liye SQL language use ki jati hai. SQL mein user apni query declare karta hai aur SQL us query ko optimize karke user ko result provide karta hai.

3GL and 4GL -

 3GL stands for 3rd Generation Languages. 3GL basically wo saari languages hai jisme user ko khudd sab kuch specify karna padta hai. User ko batana padta hai ki kya karna hai aur kaise karna hai. For example,  C,C++,Cobol, Fortran, Java, etc.In languages me user ko batana padta hai ki loop lagani hai ya variable declare karna hai, and so on.

4GL stands for 4th Generation Languages. 4GL basically wo languages hai jisme user sirf yeh hi batata hai ki kya result chahiye, user ko nahi pata ki konsa algorithm ya konsi technique use ho rahi hai result produce karne mein. For example, Oracle SQL, etc. Jisme user sirf query likhta hai aur result ko access karta hai, user yeh nahi declare karta ki kaise karna hai, wo sab language manage karti hai.

Note:- Oracle and SQL dono me difference hai. Ye same nahi hai. Oracle ek RDBMS hai aur SQL us RDBMS ke liye language hai. 

Basic Rules -  Kuch basic rules hai SQL ki queries ke liye-
  1. Every SQL statement must end with a semicolon(;).
  2. SQL is not a case sensitive language, means uppercase and lowercase letters are not differentiated.
  3. SQL statements can be entered in whether single line or split multiple lines for clarity purpose. Means clarity ke liye hum SQL ki statements ko ek line mein bhi likh sakte hai ya fir usi statement ko split kar ke multiple lines mein bhi likh sakte hain. 
  4. Parameters are separated by Commas. 

DATA TYPES - RDBMS mein data tables ki form mein store kiya jata hai. Table ke columns mein jo values fill hoti hai usse specify karne ke liye data types declare kiye jate hain. Jaise agar Name ka column hai to usme hum character data type ko declare karenge. Data type basically data ka type hai jis type ka data hum store karana chahte hain. Oracle data types are basically categorized in 4 categories-

 CategoryAvailable Data Types 
 Date/Time Date 
 LOB's (Large Objects)BFILE, BLOB, CLOB, NCLOB. 
Data types available in Oracle 8i.

1. CHARACTER DATA TYPE -  Character data type columns ki values ko character type declare karti hai. 
  • CHAR(n) - Ye data type fixed length ke character strings ko store karti hai. Length ko declare karne ke liye n hai jaise CHAR(10) means ye character type ke 10 strings ko hi store karega only. Agar user n ko declare nahi karta to default size is 1 character. Maximum size 2000 characters. For example, GENDER(M-Male,F-Female). 
  • VARCHAR(n) - Ye data type variable length character strings ko store karti hai. But Oracle ise recommend nahi karta. Oracle VARCHAR2(n) ko recommend karta hai. Isme hum letters and numbers dono ko store kar sakte hain.
  • VARCHAR2(n)- Ye data type type variable length character strings ko store karti hai. Isme letters and numbers dono store ho sakte hain. Isme size ko declare karna compulsory hai. Jaise CHAR type mein optional hai size ko declare karna but isme compulsory hai. Maximum size is 4000 characters. Agar hum Name(20) value dete hain and name 10 characters tak store ho jata hai to baaki declaration ko hum blank space se fill nahi karte and agar hun Name(20) value dete hai and 30 characters tak value jaayegi to wo store nahi hogi because wo overflow ho jayegi and error message show ho jayega.
  • NCHAR(n) - Is data type mein fixed length ke national character set ko store kiya jata hai. NCHAR ka size declare karne ke liye characters or bytes ko specify kiya jata hai. Maximum column size is 2000 bytes. 
  • NVARCHAR2(n) - Is data type mein variable length string ko national character set mein store kiya jata hai. Isme size ko characters or bytes ki form mein declare kiya jata hai. Maximum size is 4000 bytes. 
  • LONG(n) - Ye data type large data ko store karne ke liye use ki jati hai. Mostly text strings store ki jati hai large amount mein. Maximum size is 2GB for long columns. Is data type par bahut sari restrictions lagayi jati hai is liye ye commonly use nahi hota.
  • RAW and LONGRAW - Ye data types binary data ko store karne ke liye use ki jati hai. Example, sound, graphics, documents, etc. RAW maximum size is 2000 bytes and LONGRAW maximum size is 2GB. But recommend kiya jata hai ki RAW and LONGRAW ki jagah LOB's data types ko use kiya jaye.

2. NUMBER DATA TYPE - NUMBER (p,s) - Ye data type numeric data ko store karne ke liye use ki hati hai. Numeric data can be- integers, floating-point numbers, real numbers. NUMBER(p,s) data type mein 0, +ve, -ve fixed and floating numbers store kiye jate hain. p stands for precision , and s stands for scale. Precision is total no. of digits from 1 to 38, and scale is positive and negative. Positive- no. of digits to right of decimal point. Negative- round off the no. by no.of places to left of the decimal. Default scale value is 0, and it can range upto -84 to +127. For example, NUMBER(8) - Precision is 8 and scale is 0. 

3. DATE DATA TYPE - Ye data type data/time ko store karne ke liye use ki jati hai. It includes Date, Month, Year and also hours, minutes and seconds. Default date format is - DD-MM-YY. For example, 16-04-19. and agar time specify nahi kiya to default time is 12:00:00 AM(midnight). Oracle date datatype uses 7 bytes and 1 byte for length data.

 byte7byte6 byte5 byte4 byte3 byte2 byte1 
seconds minutes hours day month year century 

4. LOB's (LARGE OBJECTS) - Ye data type large and flexible data ko store karne ke liye use kiya jata hai. Large amount of binary and character based data such as text, image, video, sound, etc.
  • BLOB - BLOB stands for Binary Large Objects. Ye data type binary data ko store karne ke liye use kiya jata hai such as images, audio, video, etc. It can store upto 4GB of binary data.
  • CLOB - CLOB stands for Character Large Objects. Ye data type large amount of character data ko store karne ke liye use kiya jata hai. Agar size 4000 bytes se zyada ho to wo VARCHAR2 me store nahi ho sakta to wo data is data type se store kiya ja sakta hai. Iska maximum size 4GB hai.
  • BFILE - BFILE stands for Binary Files data type. Ye data types pointers ko use karti hai database server se files ko fetch karne ke liye. Large binary files such as images, videos, etc. Size is limited to the operating system. 

Don't forget to message us for your language preference. 

Best of Luck Students,
 Do share, subscribe and comments if you like our efforts..