SQL Tutorial

What is SQL?

  • SQL stands for Structured Query Language.
  • It is used to store, retrieve and manipulate data in a RDBMS (Relational Database Management System).
  • SQL uses various commands to manipulate data from the stored data such as CREATE, ALTER, SELECT, INSERT, DELETE, DROP etc.

RDBMS

  • Relational Database Management System is a database management system (DBMS) based on the relational model, as defined by E.F Codd.
  • A relational database stores data in the form of table. Each table consists of rows and columns.
Example : The following table represents the stored academic information of students.

IDNAMEGRADECOLLEGE
1MartinASSPMS
2RyanAPVG
3AlexBVIIT

DBMS vs RDBMS

DBMSRDBMS
DBMS application stores data as file.RDBMS application stores data in the form of table.
Normalization is not applicable for DBMS.Normalization is applied in RDBMS.
DBMS does not provide any security regarding data manipulation.RDBMS defines the integrity constraint for the purpose of ACID.
It does not support distributed database.It supports distributed database.
DBMS is designed to handle small amount of data as compared to RDBMS and supports only single user.RDBMS is designed to handle large amount of data and provide support to multiple users.

What is Normalization?

Normalization is a process of organizing the data in a database to avoid redundancy and inconsistency of insert and delete operations.

SQL Syntax

  • SQL follows unique set of rules and provide guidelines called as syntax.
  • SQL is not case sensitive. SQL keywords are generally written in uppercase.
  • SQL is dependent on relational algebra and tuple.
  • User is able to perform several operations in a database with SQL statements.
Example:  
1) SELECT “column name” FROM “table name”;
2) SELECT * FROM Employee;

The following four basic operations are applicable to any database:

i) Select: Extracts the data from database
ii) Insert: Inserts a new data into database.
iii) Update: Updates data in database.
iv) Delete: Deletes data from database.

SQL Databases and Operators

  • A data type defines a sort of value that a column should contain.
  • In a database table, every column is necessary to have a name and data type.
Important Note: Data type may vary depending on the database.
For example: MySQL supports INT but Oracle supports NUMBER for integer values.

The general data types in SQL are listed below:

Data typeDescription
INTEGERInteger number (no decimal)
CHARACTER(n)Character string with fixed length of n.
VARCHAR(n)Character string with variable length of n
DECIMAL(p,s)Where, 'p' is precision value and 's' is scale value.
REALThis is single precision floating point numeric value.
FLOAT(p)Where, 'p' is precision value.
DOUBLE PRECISIONThis is double precision floating point number.
DATEStores YY/MM/DD values.
TIMEStores hour, minute and second values.
TIMESTAMPStores year, month, day, hour, minute and second values.
ARRAYIt is a set-length and ordered collection of elements.
XMLStores xml data

SQL Operators

SQL statements consist of reserved words or characters used to perform operations like comparisons or arithmetical operations. These reserved words or characters are called as operators.

The three types of operators in SQL are:

1. SQL arithmetic operators.

OperatorsDescription
'+'Performs addition.
'-'Performs subtraction.
'*'Performs multiplication.
'/'Performs division.
'%'Divides left hand operand by right hand operand and returns reminder.


2. SQL Comparison operators

Consider the value of a = 25 and b = 75 to understand examples in the following table.

OperatorsDescriptionExample
'='Checks if a is equal to b. If yes, condition becomes true, else false.(a = b) is not true.
'!='Checks if a is not equal to b. If yes, condition becomes true, else false.(a!= b) is true.
'<>'Checks if a is equal to b or not. If yes, condition becomes true, else false.(a<>b) is false.
'>'Checks if a is greater than b. If yes, condition becomes true, else false.(a>b) is false.
'<'Checks if a is less than b. If yes, condition becomes true, else false.(a<b) is true.
'>='Checks if value of a is greater than or equal to b. If yes, condition becomes true, else false.(a>=b) is false.
'<='Checks if value of a is less than or equal to b. If yes, condition becomes true, else false. (a<= b) is true.
'!<'Checks if value of a is not less than value of b. If yes, condition becomes true, else false.(a!<b) is false.
'!>'Checks if value of a is not greater than b. If yes, condition becomes true.(a!>b) is true.


3. SQL Logical Operator

OperatorDescription
ALLIt is used to compare a value to all values in another value set.
ANDIt allows the existence of multiple conditions in SQL statement.
ANYIt compares the value in list according to the condition.
BETWEENIt is used to search for values within the set of values.
INIt is used to compare a value with the specified list values.
NOTIt reverse the meaning of any logical operator.
ORIt is used to combine multiple conditions in SQL statements.
EXISTIt is used to search for presence of a row in a specified table.
LIKEIt is used to compare a value to similar values using wildcard operators.