SQL and Types of SQL Commands in DBMS

What is SQL?

• SQL, or Structured Query Language, is created to handle and enhance relational databases.
• SQL allows users to manipulate data in databases by performing operations like inserting, updating, deleting, and querying data.
• It enables defining and modifying database schemas, tables, etc.
• SQL plays a fundamental role in database management systems by providing a standardized language for interacting with databases.

Characteristics of SQL

Declarative Language

• SQL is a statements language, which means that users describe what data they wish to obtain or modify rather than how they want to accomplish it.
• Users define the desired result, and the database management system determines the most efficient way to execute the query.

English-like Syntax

Set-Oriented Operations

Platform Independence

Advantages of SQL

a. Simplicity and Ease of Use

• SQL's intuitive syntax and declarative nature make it relatively easy to learn and use, even for individuals with limited programming experience.
• Its straightforward structure simplifies complex database operations and enhances productivity.

b. High Scalability and Performance

• Large amounts of data can be handled by SQL databases, which also support concurrent user access..
• With proper database design and optimization, SQL databases can deliver excellent performance and handle demanding workloads efficiently.

c. Data Integrity and Security

• SQL databases provide robust mechanisms for ensuring data integrity and security.
• Features such as primary keys, constraints, and user access controls help maintain the accuracy and consistency of data.

d. Standardization and Compatibility

• SQL is an industry-standard language, that ensures compatibility across different database management systems.
• This standardization enables seamless integration and interoperability between various applications and databases.

SQL Data Types and Literals

• In SQL, data types define the nature of the data stored in a database table's columns.
• Each column is assigned a specific data type, which determines the kind of values it can hold. Here are some commonly used SQL data types:

1CREATE TABLE myTable (
2  id INT,
3  price FLOAT,
4  name CHAR(50),
5  description VARCHAR(255),
6  birthdate DATE,
7  isActive BOOLEAN
8);

1. Integer: Represents whole numbers (e.g., 8, -16, 0).
2. Float: Stores floating-point numbers with decimal precision (e.g., 0.008)
3. Char: Stores fixed-length character strings (e.g., 'hello', 'SQL').
4. Varchar: Stores variable-length character strings (e.g., 'open', 'database').
5. Date: Represents a date value (e.g., '2024-03-25').
6. Boolean: Represents logical values (e.g., true or false).

Basic SQL Queries

a. SELECT Statement

• Data from one or more tables can be retrieved using the SELECT statement.
• It allows users to specify the columns to retrieve and apply conditions to filter the data. For example:

1SELECT column1, column2
2FROM table
3WHERE condition;

b. INSERT Statement

• The INSERT statement is used to insert or add new records into a table.
• It specifies the values to be inserted into the table's columns. For example:

1INSERT INTO table (column1, column2)
2VALUES (value1, value2);

c. UPDATE Statement

• The UPDATE statement is utilized to alter or make changes to records that already exist within a table.
• It allows users to update specific columns based on certain conditions. For example:

1UDATE table
2SET column1 = value1, column2 = value2
3WHERE condition;

d. DELETE Statement

• A table's records can be deleted using the DELETE statement if certain criteria are met.
• For example:

1DELETE FROM table
2WHERE condition;

SQL language Command Types

• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Control Language (DCL)

Data Definition Language (DDL)

• DDL queries are used for setting up and handling database object structures.
• DDL statements are used to define the overall structure of the database, including tables, views and relationships.
• They include commands like CREATE, ALTER, and DROP, which are used to create tables, modify their structure, or remove them from the database.

CREATE TABLE

• This statement is employed to generate a fresh table within the database.

1CREATE TABLE employees (
2  id INT,
3  name VARCHAR(50),
4  age INT,
5  salary FLOAT
6);

ALTER TABLE

1ALTER TABLE employees
2ADD COLUMN email VARCHAR(100);

DROP TABLE

1DROP TABLE employees;

Below is an example of SQL code that includes all three types of Data Definition Language (DDL) statements: CREATE, ALTER, and DROP

1-- CREATE Statement: Creating a new table
2CREATE TABLE Employees (
3    ID INT PRIMARY KEY,
4    Name VARCHAR(50),
5    Salary DECIMAL(10, 2),
6    Department VARCHAR(50)
7);
8
9-- ALTER Statement: Adding a new column to the existing table
10ALTER TABLE Employees ADD COLUMN JoiningDate DATE;
11
12-- INSERT Statement: Inserting data into the Employees table
13INSERT INTO Employees (ID, Name, Salary, Department, JoiningDate) 
14VALUES (1, 'Sehaj Bindra', 80000, 'IT', '2024-08-17'),
15       (2, 'Japanjot Singh', 80000, 'HR', '2022-12-20'),
16       (3, 'Harjas Singh', 55000, 'Finance', '2023-02-10');
17
18-- SELECT Statement: Retrieving data from the Employees table
19SELECT * FROM Employees;
20
21-- UPDATE Statement: Modifying data in the Employees table
22UPDATE Employees SET Salary = 52000 WHERE ID = 3;
23
24-- DELETE Statement: Deleting a record from the Employees table
25DELETE FROM Employees WHERE ID = 2;
26
27-- DROP Statement: Dropping the Employees table
28DROP TABLE Employees;

• The CREATE statement creates a new table named Employees with columns ID, Name, Salary, Department, and JoiningDate.
• The ALTER statement adds a new column JoiningDate to the Employees table.
• The INSERT statement inserts data into the Employees table.
• The SELECT statement retrieves all data from the Employees table.
• The UPDATE statement modifies the Salary of an employee in the Employees table.
• The DELETE statement removes a record from the Employees table.
• Finally, the DROP statement drops (deletes) the Employees table from the database.

Data Manipulation Language (DML)

• DML commands are used to manipulate the data stored within the database tables.
• They include commands like SELECT, INSERT, UPDATE, and DELETE, which allow users to retrieve, insert, update, or delete records from the database.

Types of DML Statements

Data Modification

• DML includes statements like UPDATE to modify existing data in tables based on specified conditions.

Data Deletion

• It provides statements like DELETE to remove specific records or data from tables based on conditions.
• DML statements can be used to control transactions, such as committing changes (COMMIT) or rolling back transactions (ROLLBACK).

SELECT

1SELECT * FROM employees;

INSERT INTO

1INSERT INTO employees (id, name, age, salary)
2VALUES (1, 'John Doe', 30, 50000.00);

UPDATE

1UPDATE employees
2SET salary = 55000.00
3WHERE id = 1;

DELETE FROM

1DELETE FROM employees
2WHERE id = 1;

Let's understand all the statements in one example as follows:

1-- CREATE TABLE Statement: Creating a new table for web developers
2CREATE TABLE WebDevelopers (
3    ID INT PRIMARY KEY,
4    Name VARCHAR(50),
5    Expertise VARCHAR(50),
6    YearsOfExperience INT
7);
8
9-- INSERT INTO Statement: Inserting data into the WebDevelopers table
10INSERT INTO WebDevelopers (ID, Name, Expertise, YearsOfExperience) 
11VALUES (1, 'Sehaj Bindra', 'Full Stack Web Development', 3),
12       (2, 'Japanjot Singh', 'Frontend Development', 2),
13       (3, 'Jasraj Singh', 'Backend Development', 4),
14       (4, 'Harjas Singh', 'UI/UX Design', 5);
15
16-- SELECT Statement: Retrieving data from the WebDevelopers table
17SELECT * FROM WebDevelopers;
18
19-- UPDATE Statement: Modifying data in the WebDevelopers table
20UPDATE WebDevelopers SET YearsOfExperience = 6 WHERE ID = 3;
21
22-- DELETE Statement: Deleting a record from the WebDevelopers table
23DELETE FROM WebDevelopers WHERE ID = 2;
24
25-- Transaction Control Statements: Committing or rolling back changes
26COMMIT;  -- Committing the changes made in the previous DML statements
27
28-- ROLLBACK Statement: Rolling back the changes (optional)
29ROLLBACK;  -- Undoing the changes made in the previous DML statements
30
31-- SELECT Statement after changes (optional to check changes after rollback)
32SELECT * FROM WebDevelopers;

• The CREATE TABLE statement creates a new table named WebDevelopers with columns ID, Name, Expertise, and YearsOfExperience.
• The INSERT INTO statement adds data to the WebDevelopers table for four web developers.
• The SELECT statement retrieves all data from the WebDevelopers table.
• The UPDATE statement modifies the YearsOfExperience for one developer in the WebDevelopers table.
• The DELETE statement removes one record from the WebDevelopers table.
• The COMMIT statement commits the changes made in the previous DML statements (INSERT, UPDATE, DELETE).
• The ROLLBACK statement can be used to undo changes if needed (optional).
• Finally, another SELECT statement can be used to check the data after performing the DML statements.

Data Control Language (DCL)

• DCL commands are used to control access to the database and manage user permissions.
• Commands like GRANT and REVOKE are used to grant or revoke privileges to users, determining their level of access to the database objects.

Grant Statement

• The GRANT statement is used to give specific permissions to users or roles in a database.
• Let's say we have a database with a table named "WebDevelopers" and we want to give the user "User1" permission to only select and insert data into this table.

1-- Grant SELECT and INSERT permissions on WebDevelopers table to User1
2GRANT SELECT, INSERT ON WebDevelopers TO User1;
3

Revoke Statement

• The REVOKE statement is used to take back or revoke previously granted permissions from users or roles in a database.
• If we later decide that we want to revoke the DELETE permission from "User2" on the "WebDevelopers" table, we would use the REVOKE statement.

1-- Revoke UPDATE permission on WebDevelopers table from User2
2REVOKE UPDATE ON WebDevelopers FROM User2;

Conclusion