SQL Tutorial
SQL(Structured Query Language) is used to store, retrieve and manipulate data in a RDBMS.
SQL Tutorial
Learn the concepts of SQL Programming Language with this easy and complete SQL Tutorial. This tutorial discusses SQL concepts and procedures with examples and queries. Beginners, freshers, BE, BTech, MCA, college students will find it useful to develop notes, for exam preparation, solve lab questions, assignments and viva questions. Who is this SQL Tutorial designed for?
This tutorial is specially designed for beginners who want to learn and improve their SQL Programming skillsWhat do I need to know to begin with?
A basic knowledge of databases and a programming language will help you to quickly grasp the concepts of SQL Programming. SQL Programming syllabus covered in this tutorial
This tutorial covers:
SQL Introduction, Database, TABLE, SELECT Statement, CLAUSE, INSERT Statement, UPDATE Statement, DELETE Statement, FUNCTIONS, Joins, Constraints, UNION Operator and VIEWS.
By the end of this tutorial, you will be quite confident about your SQL Programming skills. So, let's begin!
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.
| ID | NAME | GRADE | COLLEGE |
|---|
| 1 | Martin | A | SSPMS |
| 2 | Ryan | A | PVG |
| 3 | Alex | B | VIIT |
DBMS vs RDBMS
| DBMS | RDBMS |
|---|
| 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 type | Description |
|---|
| INTEGER | Integer 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. |
| REAL | This is single precision floating point numeric value. |
| FLOAT(p) | Where, 'p' is precision value. |
| DOUBLE PRECISION | This is double precision floating point number. |
| DATE | Stores YY/MM/DD values. |
| TIME | Stores hour, minute and second values. |
| TIMESTAMP | Stores year, month, day, hour, minute and second values. |
| ARRAY | It is a set-length and ordered collection of elements. |
| XML | Stores 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.
| Operators | Description |
|---|
| '+' | 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.
| Operators | Description | Example |
|---|
| '=' | 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
| Operator | Description |
|---|
| ALL | It is used to compare a value to all values in another value set. |
| AND | It allows the existence of multiple conditions in SQL statement. |
| ANY | It compares the value in list according to the condition. |
| BETWEEN | It is used to search for values within the set of values. |
| IN | It is used to compare a value with the specified list values. |
| NOT | It reverse the meaning of any logical operator. |
| OR | It is used to combine multiple conditions in SQL statements. |
| EXIST | It is used to search for presence of a row in a specified table. |
| LIKE | It is used to compare a value to similar values using wildcard operators. |
