This SQL tutorial is design for beginners as well as advanced professionals. Each tutorial is started from basic level so a SQL beginner will be able to understand each and every concept covered in this SQL tutorial.
SQL stands for Structured Query Language. SQL is a language which is used to interact with relational database management system (RDBMS). RDBMS is a collection of interrelated tables stored in a database. SQL is used to perform various operations such as read, write, update, delete etc. on the data stored in the database.
What is SQL?
- SQL is a query language that is used to instruct the database, which operation needs to be performed. SQL is not a database, it is a language that is used to manage the database.
- There are several database management softwares that supports SQL language as their query language to manage the data such as MySQL, PostgreSQL, Oracle, Microsoft Access, IDBM Db2, SQLite etc
- It allows the user to perform operations such as create, delete etc. on tables in database.
- All popular relational database management softwares use SQL to manage the database.
- SQL is a simple english like language which is used to query the database to perform various operations on the database.
Why we need SQL?
- SQL makes it easy to manage huge amount of data in an easy and efficient manner.
- SQL allows multiple users to access the data simultaneously.
- SQL is easy to learn language, which definitely reduces the amount of time required to perform any operation on the data.
- SQL allows scaling of database easy, it is very easy to add new tables and drop old tables from database using SQL.
- SQL is secure as it prevents unauthorised access by putting various access permission on the table.
- SQL also takes care of data integrity, we can add constraints on the tables which make sure what type of data can be inserted on this table.
SQL Processing
- When a SQL statement executes, system first performs various checks such as syntax check, semantic check, shared pool check on the SQL query during parsing phase.
- In the second phase, which is also known as hard parsing phase, system performs query optimization and generate various execution plans.
- In the next phase, row source generator software receives the optimal execution plan and generate a iterative plan that is used by the database.
- Last step is execution of the query based on the iterative plan generated in the previous step.
SQL vs NoSQL
NoSQL databases are different than relational databases like MQSql. In relational database you need to create the table, define schema, set the data types of fields etc before you can actually insert the data. In NoSQL you don’t have to worry about that, you can insert, update data on the fly.
NoSQL provides high scalability and high availability but less functionality compared to RDBMS (RDBMS uses SQL). Let’s have a look at the differences between SQL and NoSQL.
| SQL | NoSQL |
|---|---|
| 1. SQL query language is for relational database management system. | 1. NoSQL databases are different than relational databases. |
| 2. SQL is a structured query language. | 2. NoSQL is a non-declarative query language. |
| 3. SQL follows ACID (Atomicity, Consistency, Isolation and Durability) properties to maintain data integrity and consistency | 3. NoSQL doesn’t follow ACID properties. |
| 4. SQL can handle complex queries to access and modify the data in database. | 4. NoSQL cannot handle complex queries. |
| 5. SQL is not suitable for managing hierarchical data. | 5. NoSQL is suitable for managing hierarchical data. |
| 6. SQL supported database systems are MySQL, PostgreSQL, Oracle, Microsoft Access, IDBM Db2, SQLite etc. | 6. Popular NoSQL supported database systems are: MongoDB, Hbase, CouchDB, Redis etc. |
| 7. Data is stored in form of tables. | 7. Data is stored in form of documents. |
| 8. The schema of database is fixed, predefined and static. | 8. NoSQL supports dynamic database. |
| 9. Databases are vertically scalable. | 9. Databases are horizontally scalable. |
Advantages of SQL
- Easy to Learn: SQL is user-friendly, english like language that makes it easy to learn. Learning SQL doesn’t require prior knowledge.
- Portable language: SQL is a portable language, which means the software that supports SQL can be moved to another machine without affecting the capability of SQL interacting with the database on new machine.
- Supports wide variety of commands: SQL supports various useful commands such as:
- DDL (Data Definition Language) commands like CREATE, DROP, ALTER.
- DML (Data Manipulation Language) commands like INSERT, DELETE, UPDATE.
- DCL (Data Control Language) commands like GRANT, REVOKE.
- TCL (Transaction Control Language) commands like COMMIT, ROLLBACK.
- DQL (Data Query Language) commands like SELECT.
- Reusability: SQL promotes reusability by supporting stored procedures. These stored procedures are stored SQL statements that can be used to perform a specific task any number of times. This makes it easier to write SQL statements for a re-occurring task and reusing the saved stored procedure to perform the same task without rewriting the same SQL statements again.
- Supports JOIN: SQL supports join which is used to combine the data of two or more tables. This can be useful when we need to perform the operation on multiple tables.
- Supports UNION: UNION command can be used to join two or more DQL statement (SELECT statements).
- Integration: SQL allows integration to non-SQL database applications as well.
- Performance: Better performance even if the database size if huge.
- SQL is scalable and flexible.
- SQL is secure.
Disadvantages of SQL
- Cost: Cost of implementing and maintaining database management software that supports SQL is high. It is not suitable for very small businesses where data size is small.
- Complex interface: SQL interface is not user friendly as we need to write bunch of commands to access and perform operations on data.
- Cannot have full control: SQL doesn’t allow full control of database to the users, which is useful for the security of database but sometimes it doesn’t let authorized users to take full control of the data in database.
Leave a Reply