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:

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:

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:

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:

d. DELETE Statement

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

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.

ALTER TABLE

DROP TABLE

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

• 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

INSERT INTO

UPDATE

DELETE FROM

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

• 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.

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.

Conclusion