Structured Query Language in DBMS
SQL is a language that is used for operating databases. It includes creating a database, deleting, modifying, fetching the data, or performing other related tasks.
It is an American National Standards Institute’s (ANSI) standard language, but it has multiple versions.
In This Article
What Is SQL?
SQL stands for ‘Structured Query Language,’ – a computer language used to store, manipulate and retrieve the data present in the relational database.
It is the standard language used for a relational database system. MySQL, Oracle, post grace, SQL Server, Oracle, and Ms access are relational database management systems that use SQL as their standard database language. They also use different dialects like:
- Oracle using PL/SQL
- MS SQL Server using T-SQL
- A miss access version of the SQL is called jet SQL etc.
The structured query language is used for maintaining, creating, and retrieving the data from a relational database. The following are some interesting facts about SQL that every SQL user should know.
- It is not a case-sensitive language, but the generally recommended practice is to use keywords like update, select, create or other words in the uppercase and use the user-defined things like the table name or column name in the small letters.
- We can write the comments in SQL code with the help of double hyphens (–) at the beginning of the line.
- It is the programming language used for relational databases like MySQL, SQL Server, Oracle, etc. There are other non-relational databases like MongoDB, Dynamo DB which do not use SQL.
- There is an ISO standard for the SQL, and most of the implementations have a slight difference in the syntax. We can encounter a few queries which can work in the SQL Server, but they will not work successfully in MySQL.
Rules to be followed while using SQL:
- The structure query language is technically not case sensitive, but the keywords are generally written in uppercase.
- The SQL statements are dependent on the text line, and we can use a single SQL statement on one or multiple text lines.
- With the help of SQL statements, we can perform most of the actions in the database.
- The SQL depends on the tuple relational algebra and relational calculus.
Why Use SQL?
It is a popular language that is used for data management, and it offers the following advantages:
- Allows the user to describe the data.
- Allows the users to access data in relational database management systems.
- Allows users to define the data in the database and manipulate it.
- Allow the users to create and drop the tables and databases.
- Allows embedding within other languages with the help of SQL libraries, common modules, and free compilers.
- Allows the user to set the permissions on procedures, views, and tables.
- Allow the users to create functions, store procedures, and views in the database.
How SQL Works?
When we execute the SQL command for the relational database management system, the system will determine the best manner to perform the request. The SQL engine will then figure out how it can interpret the task. The basic components included in this process are:
- query dispatcher
- optimization engines
- classic query engine
- SQL query engine.
The classic query engine will handle all the non-SQL queries, but the SQL query engine will not handle the logical files. The following diagram shows the SQL architecture for the whole process.
What Is Relational Database Software?
If we’re talking about SQL, it cannot be complete without relational databases. These solutions are similar to the deposit box but with a twist.
The data present in these systems are structured. The information is stored in the form of tables with the help of columns and rows.
The only manner in which businesses can access it is with the help of query language. The query language used is needed to be compatible with the tables – this is where SQL comes into play.
The structured query language has statements and clauses intended specifically to understand structured information present in relational databases.
Thanks to the format of the relational database, the users can access data from multiple tables simultaneously.
Now the question arises why we should use relational databases. It can be answered with the help of the following benefits of having relational databases.
- They are accurate and have no scope for any redundant data.
- They are flexible and we can perform the most complicated queries easily.
- They are collaborative as they allow multiple users to pull out information from the same database simultaneously.
- They are secure as the access for the tables is limited to specific users only.
Basic SQL Commands
The standard SQL commands that can interact with the relational databases are CREATE, INSERT, SELECT, UPDATE, DROP, and DELETE. These commands can be classified into different categories based on their nature.
1. Data Definition Language
- Create – used for creating new tables, view tables or any other object in the database.
Create schema StudentDB;
Create table Student(
- Alter – used to modify existing database objects, like the table or column.
Alter table Student
Modify StudentName varchar(100);
- Drop – delete the whole table, view table or other objects in database
DROP TABLE Student;
2. Data Manipulation Language
- Select – retrieve certain records from single or multiple tables.
Select * from Student;
- Insert – Create a record in the table
Insert into Student value(1,’John’,15,’[email protected]’);
Insert into Student value(2,’Mark’,14,’[email protected]’);
- Update – Modify the record
Set StudentName = ‘John Smith’ where StudentId = 1;
- Delete – Delete records
Delete from Student where StudentId = 2;
3. Data Control Language
- Grant – Gives privilege to the user
GRANT ALL PRIVILEGES ON studentdb TO ‘user1’@’localhost’;
- Revoke – Takes back privileges which were granted to the user.
REVOKE ALL PRIVILEGES ON studentdb;