What is SQL?

Key Takeaways

SQL is used to store, retrieve, and manipulate data in relational databases. SQL helps manage data relationships using schemas in RDBMS.
SQL allows querying data effectively using commands like SELECT, enabling data analysis and retrieval from complex datasets.
Primary SQL operations are divided into DDL (CREATE, ALTER, DROP), DML (INSERT, UPDATE, DELETE, SELECT), and DCL (GRANT, REVOKE).

What is a structured query language and RDBMS?

Structured Query Language (SQL) is a standard data-oriented language that is primarily used to store, retrieve, and manipulate data using simple code snippets, called queries, in a relational database management system (RDBMS).

An RDBMS organizes data into tables containing rows and columns, with relations between the different entities and variables. The database schema defines these relations, specifying not only the relation between various entities but also the organization of data associated with those entities in the database.

Using SQL, we can create and modify databases, manage data, and ensure database integrity with primary and foreign key constraints.

Get a hands-on practice with SQL operators with this project, Build a CRUD Application Using Golang and React.

SQL commands

SQL commands are grouped into two main categories—DDL, DML, and DCL:

Data Definition Language (DDL) commands affect the schema/structure of the database and do not usually interact with the data inside. These commands define and manage the structure of databases and tables. Commonly used DDL in SQL querying are:
1. CREATE: Creates new databases and tables.
2. ALTER: Modifies the structure of existing tables or databases.
3. DROP: Deletes tables or entire databases.
Data Manipulation Language (DML) operations involve data retrieval and manipulation, allowing users to interact with and modify the data. These commands handle the manipulation of data stored within the tables. Commonly used DML in SQL querying are:
1. INSERT INTO: Adds new data into tables.
2. UPDATE: Modifies existing data.
3. DELETE: Removes records from a table.
4. SELECT: Retrieves data from one or more tables.
Data Control Language (DCL) commands control access to data in the database, defining permissions for users or roles. Commonly used DCL commands are:
1. GRANT: Gives specific privileges to a user or role.
2. REVOKE: Removes specific privileges from a user or role.

Practice building an application with SQL in an actual project by attempting this project, Create a Chat Application using Angular, Flask, and Socket.IO.

Code example

Now let's look at an example of how to use the DDL, DML, and DCL commands in a real-world example. We will create a small employee management system to demonstrate CREATE, ALTER, INSERT, SELECT, UPDATE, DELETE, GRANT, and REVOKE commands.

-- Step 1: Create a database
CREATE DATABASE CompanyDB;
-- Step 2: Use the created database
USE CompanyDB;
-- Step 3: Create a table for employees
CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  FirstName VARCHAR(100),
  LastName VARCHAR(100),
  Position VARCHAR(50),
  Salary DECIMAL(10, 2),
  Department VARCHAR(50)
);
-- Step 4: Insert employee records into the Employees table
INSERT INTO Employees (EmployeeID, FirstName, LastName, Position, Salary, Department) VALUES
(1, 'Alice', 'Johnson', 'Manager', 75000.00, 'HR'),
(2, 'Bob', 'Smith', 'Developer', 85000.00, 'IT');
-- Step 5: Retrieve all employee records
SELECT * FROM Employees;
-- Step 6: Update an employee's salary (for EmployeeID = 2)
UPDATE Employees
SET Salary = 95000.00
WHERE EmployeeID = 2;
-- Verify the update
SELECT * FROM Employees;
-- Step 7: Delete a record (EmployeeID = 1)
DELETE FROM Employees
WHERE EmployeeID = 1;
-- Verify the deletion
SELECT * FROM Employees;
-- Step 8: Create a user for database access
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password123';
-- Step 9: Grant permissions to the user
GRANT SELECT, INSERT ON CompanyDB.Employees TO 'user1'@'localhost';
-- Step 10: Revoke a permission from the user
REVOKE INSERT ON CompanyDB.Employees FROM 'user1'@'localhost';
-- Step 11: Drop the Employees table
DROP TABLE Employees;
-- Verify the table no longer exists
SHOW TABLES;

Explanation

Line 2: We create a new database named CompanyDB using the CREATE command.
Line 5: We switch to the CompanyDB database to perform subsequent operations.
Lines 8-15: We create a table named Employees with columns for employee details, including EmployeeID, FirstName, LastName, Position, Salary, and Department.
Lines 18-20: We insert two employee records into the Employees table.
Line 23: We retrieve and display all records from the Employees table to verify the insertion.
Lines 26-28: We update the salary of the employee with EmployeeID = 2 from 85000 to 95000.
Lines 31: We display all records to confirm that the salary update was successful.
Lines 34-35: We delete the record of the employee with EmployeeID = 1.
Line 38: We display all records to verify that the deletion was successful.
Line 41: We create a new user before assigning privileges.
Line 44: We give user1 permission to select and insert data into the Employees table.
Line 47: We remove the INSERT permission from user1.
Line 50: We remove the Employees table from the database.
Line 53: We display all tables in the current database to confirm that the Employees table has been dropped.

Try out this project to get real-world SQL related expereince, Build a Python Airline Reservation System.

Frequently asked questions

Haven’t found what you were looking for? Contact Us

How does SQL ensure data integrity?

SQL ensure data integrity via primary keys, foreign keys, and unique constraints, which prevent duplicate or invalid entries.

Can SQL queries be optimized for performance?

Yes, SQL queries can be optimized using indexes, query tuning techniques, and efficient schema designs to improve database performance.

What is the difference between SQL and NoSQL?

SQL databases are structured and relational, making them ideal for storing data with clear relationships, such as customer details, transaction records, or inventory management. NoSQL databases, on the other hand, are non-relational and better suited for unstructured or large-scale distributed data systems, such as social media feeds, IoT data, or real-time analytics.

Free Resources

Learn in-demand tech skills in half the time

PRODUCTS

Mock Interview

New

Courses

Skill Paths

Projects

Assessments