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.
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
SQL commands
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.
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.
SQL Queries
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.
SELECT name FROM students;
Example 2: Delete the records for student with student id= 103
DELETE FROM students WHERE stud_id=103;
Example 3: Change branch of Ram from ECE to CSE
UPDATE students
SET branch= ‘CSE’
WHERE name= ‘RAM’;
Example 4: Display the entire table
SELECT * FROM students;
Example 5: Drop the table students
DROP TABLE students;
Best 10 SQL Projects by GitHub
Sequelize: Itis 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.
mysqljs: A pure node.js JavaScript Client implementing the MySql protocol.
KNEX: A query builder for PostgreSQL, MySQL and SQLite3, designed to be flexible, portable, and fun to use.
TypeORM: It is an ORM that can run in NodeJS, Browser, Cordova, PhoneGap, Ionic, React Native and Electron platforms and can be used with TypeScript and JavaScript (ES5, ES6, ES7, ES8). Its goal is to always support the latest JavaScript features and provide additional features that help you to develop any kind of application that uses databases — from small applications with a few tables to large scale enterprise applications with multiple databases.
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).
305+ registered