What is SQL?
Structured Query Language, popularly known as SQL, is a programming language used to manage stored data in relational databases. It is a standard language for accessing and manipulating databases and is widely used in various applications and industries. With SQL, we can perform tasks such as inserting, deleting, and updating data in a database and creating and modifying a database’s structure. SQL can be used for data analysis, warehousing, and creating and managing user access to databases.
What is a Relational Database?
A relational database is a type of database that stores and organizes data in tables (tuples). These tables are related to each other using foreign keys, which are used to link data between tables, and each table has its primary key. This allows for data organization in a way that makes it easy to search and retrieve specific information from the database. Relational databases are widely used in many applications and are a standard way of storing and managing data. Some examples of relational database management systems (RDBMS) include MySQL, Oracle, and Microsoft SQL Server.
Types of SQL commands
SQL uses various commands to carry out operations. These commands can be broadly classified into five categories:-
- DDL – (Data Definition Language)
- DQL – (Data Query Language)
- DML – (Data Manipulation Language)
- DCL – (Data Control Language)
- TCL – (Transaction Control Language)
Data Definition Language (DDL)
DDL is a language used to define data structures in a database management system. DDL statements are used to create, modify, and delete database objects such as tables, indexes, and constraints but not the data inside the table.
List of DDL commands
CREATE: is used to create the database and its various objects such as tables, indexes, triggers, views, and functions.
Syntax to create a new database
CREATE DATABASE database_name;
Example – CREATE DATABASE university;
Syntax to create a new table inside a database –
CREATE TABLE table_name;
Example – CREATE TABLE students(
Enrollment_number int,
Name varchar(200),
Gender varchar(10));
Syntax to create an index of the table–
CREATE INDEX index_name;
Example – CREATE INDEX idx_name;
Syntax to create a view of the virtual table –
CREATE VIEW [name_of_table] ;
Example – CREATE VIEW [female students] AS
SELECT Name, Gender
FROM Students
WHERE Gender=’Female’;
DROP: This command is used to delete elements from the database like table, index, view, or the entire database.
Syntax to drop a table–
DROP OBJECT object_name_to_be_deleted;
Example – DROP TABLE student;
DROP DATABASE university;
ALTER: This is used to change the structure of the database by adding, dropping, renaming, or modifying the columns.
Syntax to alter a table–
To add –
ALTER TABLE table_name
ADD (column_name, data type);
To drop –
DROP COLUMN column_name;
To modify –
ALTER TABLE table_name
MODIFY column_name, column type;
Example – ALTER TABLE student
DROP COLUMN Gender;
To rename a table –
ALTER TABLE table_name
RENAME TO new_table_name;
Example – ALTER TABLE Student
RENAME TO candidates;
To rename a column –
ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;
Example – ALTER TABLE Student
RENAME Enrollment_number TO Registration_number;
TRUNCATE: is used to remove the entire table. It is fast compared to the DROP function. Moreover, it also preserves the structure of the table for future purposes, unlike the DROP command.
Syntax to truncate a table–
TRUNCATE TABLE table_name_to_be_deleted;
Example – TRUNCATE TABLE student;
COMMENT: Like any other programming language, SQL, too, supports various types of comment lines.
- — single-line comment
- /* multiple line comment */
Example –
/* happy
learning */
SELECT Name_of_column — use to select a tuple from the table.
Data Query Language (DQL)
DQL is used to pull in data from the database. DQL has only one command, i.e., SELECT
Syntax:
SELECT attribute_name FROM table_name WHERE conditions;
Example:
SELECT Name FROM Student WHERE Gender=’Female’;
We can select the entire attribute of a table by using * (asterisk) in place of attribute_name
Example –
SELECT * FROM Student – it will show all the attributes of the student table
Data Manipulation Language (DML)
DML commands are used to manipulate the data within the database. It contains most of the commands. Moreover, it also regulates access to data and databases. DML and DCL commands are usually grouped.
List of DML commands
INSERT: it is used to insert new data into the row of a table.
Syntax –
INSERT INTO table_name
VALUES (value1, value2, value3, ….);
Example –
INSERT INTO Student (Enrollment_number, DOB, Mobile, Email)
VALUES (‘20258963’, ’10-02-2002, 12356985, ‘[email protected]’);
UPDATE: is used to update existing data in the table.
Syntax –
UPDATE table_name SET column1=value1, column2=value2 WHERE condition.
Example –
UPDATE student SET Name=’Nitish’, Gender=’Male’ WHERE Enrollment_number=202002596;
DELETE: as the name suggests, it is used to delete the data in the table using various conditions. We can delete single as well as multiple records at a time.
Syntax –
DELETE FROM table’s_name WHERE condition;
Example –
DELETE FROM Student WHERE Age<19;
Data Control Language (DCL)
Data control language (DCL) is a Structured Query language used to control access to data in a database. It includes commands such as GRANT and REVOKE, which are used to give users access to certain data, and to take that access away.
GRANT: gives access privilege of the database to the user. It can be used to grant SELECT, DELETE, CREATE, UPDATE, DROP, ALTER, and INDEX on a single as well as multiple tables.
Syntax –
GRANT SELECT, UPDATE ON table_name TO name_of_user1, name_of_user2;
Example –
GRANT SELECT, UPDATE ON Student TO Aditi, Nitin;
REVOKE: it is the exact opposite of GRANT command
Syntax –
REVOKE SELECT, UPDATE ON table_name FROM name_of_user1, name_of_user2;
Example –
REVOKE SELECT, UPDATE ON Student FROM Aditi, Nitin;
Transaction Control Language (TCL)
TCL, or Transaction Control Language, is a language used to manage transactions in a database management system. This includes commands for managing the changes made to the data in a database, such as committing or rolling back transactions. TCL commands are often used in conjunction with SQL (Structured Query Language) to create a complete database management system. For example, a TCL command might be used to start a transaction, and then an SQL command might be used to insert data into a database table.
List of TCL commands
COMMIT: is used to save the transaction or commit the entire changes to the database.
Syntax –
COMMIT;
Example –
UPDATE Student SET Age=’1996-09-18’ WHERE Name=’Joel’;
COMMIT;
Rollback: in case of any failure, it rolls back the entire transaction.
Syntax –
ROLLBACK;
Example –
UPDATE Student SET Age=’1996-09-18’ WHERE Name=’Joel’;
ROLLBACK;
SAVEPOINTS: is used to roll back the transaction to a specific point instead of the entire transaction.
Syntax to create a save point–
SAVEPOINT s1;
Example –
UPDATE Student SET Age=’1996-09-18’ WHERE Name=’Joel’;
SAVEPOINT s1;
RELEASE SAVEPOINT: used to remove or delete the save points that we create.
Syntax –
RELEASE SAVEPOINT s1;
Example –
UPDATE Student SET Age=’1996-09-18’ WHERE Name=’Joel’;
RELEASE SAVEPOINT s1;
Apart from the 5 broad classifications, we are still left with some important commands. Let us have a look at those commands.
JOINS
JOIN commands combine rows of two or more tables based on a similar column among those tables. Following are the types of joins:
INNER JOIN: This join returns records with a matching value in both tables.
Syntax –
SELECT column_name
FROM Table1
INNER JOIN Table2 ON Table1.column_name = Table2.ColumnName;
Example –
SELECT Department
FROM Student
INNER JOIN Faculty ON Student.Department = Faculty.Department;
FULL JOIN: This join returns all records that have a match in the left or right table.
Syntax –
SELECT column_name
FROM Table1
FULL JOIN Table2 ON Table1.column_name = Table2.ColumnName;
Example –
SELECT Department
FROM Student
FULL JOIN Faculty ON Student.Department = Faculty.Department;
LEFT JOIN: This join selects records from the left table that satisfy the condition from the right table.
Syntax –
SELECT column_name
FROM Table1
LEFT JOIN Table2 ON Table1.column_name = Table2.ColumnName;
Example –
SELECT Department
FROM Student
LEFT JOIN Faculty ON Student.Department = Faculty.Department;
RIGHT JOIN: This join selects records from the right table that satisfy the condition from the left table.
Syntax –
SELECT column_name
FROM Table1
RIGHT JOIN Table2 ON Table1.column_name = Table2.ColumnName;
Example –
SELECT Department
FROM Student
RIGHT JOIN Faculty ON Student.Department = Faculty.Department;
SET OPERATIONS
In SQL, set operations allow you to combine the results of two or more SELECT statements to create a single result set. The set operations available in SQL are:
UNION: combines the results of two or more SELECT statements, eliminating duplicates
INTERSECT: returns the rows that are common to the results of two SELECT statements
MINUS: returns the rows that are in the first SELECT statement but not in the second
Example –
SELECT * FROM customers WHERE country IN (‘US,’ ‘Canada’);
UNION
SELECT * FROM customers WHERE country = ‘Mexico’;
This would return a list of all US, Canadian, and Mexico customers, with duplicates eliminated.
We hope that this Blog will be a helpful quick-reference resource for SQL commands. However, copy-pasting code won’t do the trick if you’re serious about developing your SQL skills. Visit our interactive SQL courses to get started practising what you’re learning.
