In a relational database, the data is stored in the form of tables. The data, therefore, is organised to a great extent. Users now need something to retrieve the data from the database. The solution is the structured query language (SQL). SQL stands for the structured query language. It is used to perform various types of operations on a relational database. The various operations which can be performed with the help of SQL are CREATE, INSERT, UPDATE, DROP, etc. SQL makes life very easy since the users can manipulate and retrieve data using simple English language commands. SQL is easy to understand, and most of the commands used in SQL are simple English words that have almost the same meaning in real life.
What is SQL?
Initially called SEQUEL (Structured English Query Language) and later renamed it due to brand trademark, it stands for Structured Query Language and was developed by Raymond Boyce and Donald Chamberlin at IBM in the early 1970s. The language was designed to get access and modify data held in databases. Today, SQL is an official standard for the ANSI (American National Standards Institute) and ISO (International Organisation for Standardisation).
What Can SQL do?
- It can execute queries against a database
- It can retrieve data from a database
- It can insert records in a database
- It can update records in a database
- It can delete records from a database
- It can create new databases
- It can create new tables in a database
- It can create stored procedures in a database
- It can create views in a database
- It can set permissions on tables, procedures and views
The various SQL commands are:
Data Definition Language (DDL)
The data definition language (DDL) is a set of SQL commands which is used to describe the database schema. Such commands are used to create new tables, drop the table, etc. The Data Definition Language contains all the commands that deal with the overall structure of the database schema. The various DDL commands are:
- CREATE: This command is used to create a new database or table.
- ALTER: It is an important command which is used to change the structure of the table after it is created. This may include adding a new column, changing the data type, etc.
- DROP: It is used to delete all the records as well as the structure of the table.
- TRUNCATE: It is used to delete all the rows in the table.
- RENAME: It is used to rename various objects in a database.
Data Manipulation Language (DML)
The Data Manipulation Language (DML) is a set of SQL commands that is used to manipulate the data in a table. By manipulating, we mean that it is used to insert, update, delete some records from the table. The various DML commands are:
- SELECT: It is used to select a set of attributes depending on the given condition if any.
- INSERT: Used to insert data values in the rows of the table.
- UPDATE: It is an important command used to update an existing record in the table.
- DELETE: It is used to delete rows or records from a table.
Data Control Language (DCL)
The Data Control Language commands are:
- GRANT: It is used to give access privileges to a user.
- REVOKE: It is used to revoke or withdraw the access privileges given.
Transaction Control Language (TCL)
The various TCL commands are:
- COMMIT: To commit a transaction.
- ROLLBACK: To roll back or cancel a transaction if any error occurs.
- SAVEPOINT: To set a save point.
Now that we are familiar with various SQL commands, it is time to see a practical example of how SQL works. For that, we will use a very simple table called the students table and try some SQL queries on it.
Example 1: To find the name of all the students.
Example 2: Delete the records for student with student id= 103
Example 3: Change branch of Ram from ECE to CSE
Example 4: Display the entire table
Example 5: Drop the table students
Best 10 SQL Projects by GitHub
- Sequelize: It is a promise-based Node.js ORM for Postgres, MySQL, SQLite and Microsoft SQL Server. It features solid transaction support, relations, read replication and more.
- TiDB: It is an open-source distributed scalable Hybrid Transactional and Analytical Processing (HTAP) database. It features infinite horizontal scalability, strong consistency, and high availability. TiDB is MySQL compatible and serves as a one-stop data warehouse for both OLTP (Online Transactional Processing) and OLAP (Online Analytical Processing) workloads.
- KNEX: A query builder for PostgreSQL, MySQL and SQLite3, designed to be flexible, portable, and fun to use.
- Vitess: It is a database clustering system for horizontal scaling of MySQL through generalised sharing.
- mycli: A command-line client for MySQL that can do auto-completion and syntax highlighting.
- Go-MySQL-Driver: It is a MySQL driver for Go’s (golang) database/sql package.
- gh-ost: GitHub’s Online Schema Migrations for MySQL.
- DBeaver: Free multi-platform database tool for developers, SQL programmers, database administrators and analysts. Supports any database which has JDBC driver (which means — ANY database). EE version also supports non-JDBC data sources (WMI, MongoDB, Cassandra, Redis).
Frequently Asked Questions
What does SQL stand for?
SQL stands for the structured query language.
What is the use of SQL?
It is used as a tool through which users can interact with a database and use it efficiently.
What are various SQL commands?
Data Definition Language, Data Manipulation Language, Data Control Language, Transaction Control Language.
What are some of the constraints in SQL?
NOT NULL, DEFAULT, UNIQUE, PRIMARY KEY, etc.
What is the difference between the drop and delete command?
DELETE is a DML command which is used to delete all or some tuples or records, whereas DROP is a DDL command used to delete the entire schema.
- SQL stands for the structured query language. It is used to perform various types of operations on a relational database.
- The various operations which can be performed with the help of SQL are CREATE, INSERT, UPDATE, DROP, etc.
- Various SQL commands are DDL, DML, TCL, DCL.
- The data definition language (DDL) is a set of SQL commands which is used to describe the database schema.
- The Data Manipulation Language (DML) is a set of SQL commands that are used to manipulate the data in a table.
- Some examples of SQL databases are MySQL, Oracle, SQL Server.
Never stop learning. Explore the top 100 SQL problems here.