Blog

Introduction to SQL (Structured Query Language)

SQL (Structured Query Language) is a well-known, very powerful, and standard database operations programming language. SQL is used to manage and control multiple relational database tables or databases through SQL query operations. SQL database commands allow database administrators or users to interact with the database through command statements, which help perform multiple database operations, such as querying and extracting data from existing database data, inserting new data records, updating and deleting existing database records. SQL is traditionally the most basic and most widely used database language for managing and controlling database operations, particularly for such database operations performed using popular relational database management system (RDBMS) software, such as MySQL, PostgreSQL, Oracle, and SQL Server.

Remember, the SQL database structure language follows the relational model. This means that user-created database data is organized through relationships in a tabular format, where table rows represent records, and table columns represent fields or attributes.

Special Features of the SQL Database Language

• Declarative Language – SQL is a declarative database programming language that helps its users define what operations or tasks they want to perform with a database or data, such as retrieving an existing database, inserting a new database, updating an existing database, etc. SQL provides its users with several methods for executing queries to the database system.
• Standardized – SQL database concepts have been standardized by the International Organization for Standardization (ISO), which ensures consistency across multiple database systems. While some systems implement SQL with minor modifications or proprietary extensions.
• Comprehensive – SQL database operations provide users with a wide range of command statements that help handle nearly all aspects of user-database interaction, from accessing and retrieving stored database data to database system administration tasks.

Core SQL Database Operation Types.

SQL database software helps administrators and users perform many essential database operations. These operations are divided into several categories.

SQL Data Definition Language (DDL) Operations.

DDL command statements in SQL are used to define the structure of an existing database, performing operations such as creating database tables and other objects, modifying and deleting tables and databases, if needed.

SQL Data Definition Language (DDL) Explanation.

• CREATE – This SQL command helps users create a new table, database, or index.
• ALTER – This SQL statement helps database users modify or alter an existing database table. For example, adding a new column to a table or removing an existing column.
• DROP – This SQL command helps delete a table, database, or index.
• TRUNCATE – This SQL statement helps remove all rows from a table without deleting the database table.

Example of Data Definition Language (DDL).

CREATE TABLE employe (

id INT PRIMARY KEY,

emp_name VARCHAR(100),

emp_age INT,

dept VARCHAR(50)

);

SQL Data Manipulation Language (DML) Operations.

DML commands in SQL are used to manipulate or extract data stored in a database from query results. For example, retrieving existing database record information, inserting new database information, updating or deleting existing database record information, etc. are database operations.

Data Manipulation Language (DML) Explanation.

• SELECT – The SELECT statement in SQL helps the user select or retrieve record information from one or more database tables.
• INSERT – It helps add new records to one or more existing database tables in SQL.
• UPDATE – It helps to modify an existing record or update record information in a SQL database table.
• DELETE – It helps to delete a record from a SQL database table.

Example of Data Manipulation Language (DML).

SELECT emp_name, emp_age FROM employe WHERE dept = ‘sales’;

INSERT INTO employe (id, emp_name, emp_age, dept) VALUES (1, ‘Siddhi Deora’, 21, ‘IT’);

UPDATE employe SET emp_age = 24 WHERE id = 1;

DELETE FROM employe WHERE id = 1;

SQL Data Control Language (DCL) Operations.

In SQL database operations, DCL commands manage and control user administrator access to data, including setting database user admin privileges or permissions and granting and controlling multiple user access rights.

Data Control Language (DCL) Operations Explanation.

• GRANT – The GRANT SQL command grants a database user role permission to perform a particular special action on a database object.
• REVOKE – This command removes a user’s admin privileges or permissions in SQL.

Example of Data Control Language (DCL).

GRANT SELECT, INSERT ON employe TO employe1;

REVOKE DELETE ON employe FROM employe1;

SQL Transaction Control Language (TCL) Operations.

TCL commands in SQL are used to manage internal transactions for database tables. They fix transactions in the database so that database operations are performed in a way that maintains the integrity and accuracy of the current database.

• COMMIT – This SQL command stores all modifications made during the current table transaction in the database.
• ROLBACK – This SQL command undoes all modifications made during the current database transaction.
• SAVEPOINT – This sets a savepoint within an SQL transaction, allowing users to perform a rollback operation.
• SET TRANSACTION – This configures transaction properties in SQL.

Example of Transaction Control Language (TCL).

BEGIN TRANSACTION;

UPDATE employe SET emp_age = 44 WHERE id = 1;

COMMIT;

Some popular SQL command types.

SELECT – The SELECT statement is the most common and powerful SQL command or statement used to extract data from a SQL database table. It is mostly used to extract data from a database table.

SELECT column1, column2 FROM table_name WHERE condition;

Example of a SQL SELECT statement.

SELECT emp_name, dept FROM employe WHERE emp_age > 17;

WHERE – The WHERE clause or statement is used to filter table data in a SQL database and display records that meet a particular condition.

Example of a SQL WHERE statement.

SELECT * FROM employe WHERE dept = ‘sales’;

ORDER BY – The ORDER BY clause or statement is used to sort table data in a SQL database and display the result output in ascending order (ASC) or descending order (DESC).

Example of a SQL ORDER BY statement.

SELECT emp_name, emp_age FROM employe ORDER BY age DESC;

JOIN – The SQL JOIN operation is performed to join data from multiple existing tables in a SQL database. It allows the database user to join or merge rows from two or more tables based on related table columns.

Example of a SQL database (inner join).

SELECT employe.emp_name, departments.dept_name

FROM employe

INNER JOIN departments ON employe.department_id = departments.id;

GROUP BY – It aggregates data in a SQL database. Here, the GROUP BY clause groups table records by a specified column, and is mostly used with aggregate functions in numeric SQL database operations like COUNT, SUM, AVG, MAX, or MIN.

Example of a SQL GROUP BY clause.

SELECT dept, AVG(emp_age) FROM employe GROUP BY dept;

INSERT INTO – It helps insert new data into a SQL database. The INSERT INTO statement is used to add a new row of data to a database table.

Example of an INSERT INTO statement.

INSERT INTO employe(id, emp_name, emp_age, dept)

VALUES (2, ‘Bhavishi Deora’, 27, ‘Manager’);

UPDATE – This command helps modify existing table data in a SQL database. The UPDATE SQL command statement modifies existing records in a table based on a particular user-defined condition.

Example of UPDATE SQL.

UPDATE employe SET emp_age = 29 WHERE id = 2;

DELETE – This command deletes data information in a SQL database. The DELETE statement helps delete one or more rows from a table.

DELETE SQL command example.

DELETE FROM employe WHERE id = 2;

SQL command syntax rules.

• Case-insensitivity – Keywords in SQL are case-insensitive in nature; for example, SELECT and select are treated the same.
• Semicolon (;) – The semicolon operator in SQL is used to terminate a statement, especially when executing multiple queries simultaneously in a script.
• String literals – String text values ​​in SQL are enclosed in single quotes. For example, ‘Sales’.
• Column and table names – Column and table names in SQL are case-insensitive in nature, but some databases, such as PostgreSQL, treat them as case-sensitive when quoted, for example, “department”.