Introduction to SQL
- Swetha Y
- Jul 30, 2019
- 0 comment(s)
Structure Query Language (SQL) is a database query language used for storing and managing data in Relational DBMS. SQL was the first commercial language introduced for E.F Codd's Relational model of the database. Today almost all RDBMS (MySql, Oracle, Informix, Sybase, MS Access) use SQL as the standard database query language. SQL is used to perform all types of data operations in RDBMS.
What is SQL?
1. SQL stands for Structured Query Language, which is a standardised language for interacting with RDBMS (Relational Database Management System). Some of the popular relational database examples are MySQL, Oracle, MariaDB, PostgreSQL etc.
2. SQL is used to perform C.R.U.D (Create, Retrieve, Update & Delete) operations on relational databases.
3. SQL can also perform administrative tasks on a database such as a database security, backup, user management etc.
4. We can create databases and tables inside the database using SQL.
Types of Structured Query Language (SQL)
In the above section, we learned what we do with the database using SQL. SQL is basically a combination of four different languages, they are –
DQL (Data Query Language)
DQL is used to fetch the information from the database which is already stored there.
DDL (Data Definition Language)
DDL is used to define table schemas.
DCL (Data Control Language)
DCL is used for user & permission management. It controls access to the database.
DML (Data Manipulation Language)
DML is used for inserting, updating and deleting data from the database.
Without exception, all the online businesses use SQL (Structured Query Language) in one way or another. Thus, learning it is very useful for anyone who wants to have a career in the online world. But for a Data Analyst (or Data Scientist) it’s a definite must, since SQL is one of the most common data languages, too. Let’s see why:
· SQL is easy to learn
· SQL performs extremely well on bigger data sets (even a few million lines)
· SQL has been around for more than 40 years and it will be here for at least the next 40.
- No coding needed
It is very easy to manage the database systems without any need to write a substantial amount of code by using the standard SQL.
- Well defined standards
Long established are used by the SQL databases that are being used by ISO and ANSI. There are no standards adhered by the non-SQL databases.
SQL can be used in the program in PCs, servers, laptops, and even some of the mobile phones.
- Interactive Language
This domain language can be used for communicating with the databases and receive answers to the complex questions in seconds.
- Multiple data views
With the help of SQL language, the users can make different views of database structure and databases for the different users.
- Difficult Interface
SQL has a complex interface that makes it difficult for some users to access it.
- Partial Control
The programmers who use SQL doesn’t have full control over the database because of the hidden business rules.
Some of the databases go to the proprietary extensions to standard SQL for ensuring the vendor lock-in.
The operating cost of some SQL versions makes it difficult for some programmers to access it.
- Data Integration Scripts
The main application of SQL is to write data integration scripts by the database administrators and developers.
- Analytical Queries
The data analysts use structured query language for setting and running analytical queries on a regular basis.
- Retrieve Information
Another popular application of this language is to retrieve the subsets of information within a database for analytics applications and transaction processing. The most commonly used SQL elements are select, insert, update, add, delete, create, truncate and alter.
- Other Important Applications
The SQL is used for modification of the index structures and database table. Additionally, the users can add, update and delete the rows of the data by using this language.
The breadth and scope of the SQL commands provide the capability to create and manipulate a wide variety of database objects using the various
commands. Those database objects then can be loaded with data using commands such as
. The data can be manipulated using a wide variety of commands, such as
, as well as the cursor commands
. Transactions to manipulate the data are controlled through the
command, plus the
commands. And finally, other commands covered in this chapter include those that control a user’s access to database resources through commands such as
SQL Server lets you manage multiple users simultaneously and ensure that transactions observe the properties of the chosen isolation level. Locking guards data and the internal resources that enable a multiuser system to operate like a single-user system. You can choose to have your databases and applications use either optimistic or pessimistic concurrency control. With pessimistic concurrency, the locks acquired by data modification operations block users trying to retrieve data. With optimistic concurrency, the locks are ignored, and older committed versions of the data are read instead. In this chapter, we looked at the locking mechanisms in SQL Server, including full locking for data and leaf-level index pages and lightweight locking.
Author: Bharani G R