Mastering SQL: From Fundamentals to Advanced Techniques

SQL Commands-An Introduction

Board Infinity
Board Infinity
SQL Commands-An Introduction

    Introduction To SQL: A Complete Guide

    SQL Commands-An Introduction

    Char and Varchar in SQL

    ORDER BY in SQL

    5 Resume Writing Myths That You Need to Stop Following Now!

    GROUP BY in SQL

    Learn How to Perform Union in SQL?

    Joins and Its Types in SQL

    Rename column name in SQL

    Learn How To Compare Two Or More Datetime Entities In SQL With Examples

    Difference Between SQL and NoSQL

    Constraints in SQL

    Learn about Aggregate Functions in SQL

    SQL Subqueries

    Nested Queries in SQL - Query In Query

    SQL WITH Clause – Tutorial and Examples

    How to Drop a Table in PostgreSQL?

    How to Perform Pivot in SQL?

    SQL Window Function

    Find Nth Highest Salary in SQL

    Working of Correlated SubQueries in SQL

    Learn about Stored Procedures in SQL

    Triggers in SQL: An Overview and Example

    PL/SQL Explained

Introduction

The accepted method for managing databases is called SQL (Structured Query Language). It is a database language that enables the management and retrieval of data from relational databases. The most crucial SQL commands and statements will be covered in this article.

Types of SQL Commands

Types of SQL commands will be covered in this section. DDL, DML, DCL, TCL, and DQL are the five main categories into which SQL Commands are separated. Below is further information about each category:

Data Definition Language(DDL):

A set of guidelines called the Data Definition Language (DDL) may be used to create a database's structure. DDL instructions automatically commit, which means they permanently save all database changes. These steps should not be carried out by a regular user who should be using an application to access the database.

  • CREATE Command:  With this command, the database or its objects are created (like table, index, function, views, store procedure, and triggers). In SQL, there are two different kinds of create statements: one creates a database, and the other creates a table.

A database is a planned gathering of data. The first step in using SQL is to create a database in order to store data in a well-structured way. Use the CREATE DATABASE command in SQL to create a new database.

We'll look at how to use the CREATE TABLE statement to create tables in computer programming. A table is a collection of rows and columns, and each row or column contains a name, type, size and so on. The CREATE ONLINE statement is used to create a new table.

Example:

CREATE TABLE table_name(
column1 data_type(size),
column2 data_type(size),
column3 data_type(size),
column4 data_type(size),
.....
);

Here, column1 is the name of the first column, table name is the name of the table, and data type designates the kind of data that will be kept in this column. For instance, integer data is represented by the int type. size describes the most data that may be stored in a particular column.

  • DROP Command: A full database or just a table can be eliminated with the DROP command, in which case all of the associated data will likewise be removed. Existing objects like databases, tables, indexes, and views are deleted by the DROP command.

Example:

DROP TABLE Employee;
DROP DATABASE Company;

In the above example, the first command will delete the Employee table, while the second will delete the whole database.

  • ALTER Command: This command is used to add, remove/drop, or edit columns in an existing table. Additionally, it may be used to add and take away restrictions from an existing table.

Example:

ALTER TABLE Employee ADD Address VARCHAR2(20);
ALTER TABLE Employee MODIFY(Emp_Name VARCHAR2(25));

The "Emp Name" column will be changed by the aforementioned command to be of data type VARCHAR2 and size 25.

  • TRUNCATE Command: used to show the table's deallocation extents (empty for reuse). This approach swiftly removes all data from a database while often avoiding many integrity checking procedures. For the first time, it was incorporated into the SQL:2008 standard. It functions similarly to the delete command.

Syntax:

TRUNCATE TABLE  table_name;

Example:

TRUNCATE TABLE Employee;

The aforementioned command will remove the information from the "Employee" table, but not the table itself..

Data Manipulation Language:

Who gets access to the data and the database is controlled by the Data Manipulation Language (DML), which is a component of the database. The DML command cannot permanently save all database alterations since it is not auto-committed. They might perhaps be rolled back.

  • INSERT Command: Data is inserted into the row of a table using it.

Example:

INSERT INTO Employee(Emp_Name, DOB, Mobile, Email)
VALUES('Joe', '1995-02-16', 7812865845, 'joe@gmail.com');

The values listed above will be added to the "Employee" database using the aforementioned command.

  • UPDATE Command: The UPDATE command in SQL is used to update data in a table already present in a database. Depending on our needs, we may edit a single column or a number of columns using the Alter statement.

Example:

UPDATE Employee SET Mobile=9935467903 WHERE Emp_Name='Joe';

In the aforementioned query, a specific column is given new values using the SET statement, and the rows for which the columns need to be changed are chosen using the WHERE clause. Without using the WHERE clause, all of the columns in the rows will be changed. As a result, to pick certain rows, the WHERE clause is used. As a result, the sample query will update the employee's cellphone number with the name "Joe".

  • DELETE Command: To remove records from a table in SQL, use the DELETE command. We can remove a single record or a large number of records depending on the criteria we specify in the WHERE clause.

Example:

DELETE FROM Employee WHERE Emp_Name='Joe';

The aforementioned command will remove the entry for Joe, the employee, from the "Employee" database.

Data Control Language(DCL):

Users can access and modify data stored in databases using the query language DCL (Data Controlling Language). Grant and Revoke are examples of the many sorts of Data Controlling Language commands.

  • GRANT Command: This command grants a user database access capabilities. It may be applied to provide a user access to select, insert, update, and delete data from one or more tables.

Example:

GRANT INSERT, SELECT on accounts to Alex

Alex may now query and insert data into the accounts database using this command, among other privileges.

  • REVOKE Command: REVOKE command is used to revoke a user's permissions. Depending on the circumstance, it may be used to revoke a privilege (by default) or a particular command, such as UPDATE or DELETE.

Example:

REVOKE UPDATE, SELECT on schools from Alex

John's permissions to query or insert data into the accounts database objects have been revoked using this command.

Transaction Control Language(TCL):

The database uses Transaction Control Language (TCL) commands to control transactions. To handle the adjustments to the DML statements, use this command. You may combine your assertions into logical transactions using TCL.

  • COMMIT Command: To save all the transactions in the database Commit is used.

Example:

UPDATE Employee SET DOB='1995-02-17' WHERE Emp_Name='Joe';
COMMIT;

This example will insert a record into a table with the value "Joe" and then COMMIT the database modifications.

  • ROLLBACK Command: If any of the SQL grouped statements fail, all alterations must be undone. Undoing modifications is referred to as "rolling back" in this context. Only transactions that have happened after the last COMMIT or ROLLBACK command may be reversed using this operation.

Example:

UPDATE Employee SET DOB='1995-02-17' WHERE Emp_Name='Joe';
ROLLBACK;

In this example, the database modifications would be ROLLBACKED once the dob was inserted into the table with the name Joe. The table would not be affected by this procedure.

  • SAVEPOINT Command: Instead of undoing the entire transaction, it is used to roll back a transaction to a particular point.

Example:

SAVEPOINT S1; //savepoint
DELETE FROM Employee WHERE Emp_Name = 'Atharva';
SAVEPOINT S2; //Savepoint created.

Data Query Language(DQL):

Depending on the query that is provided to it, the DQL Commands' objective is to produce a schema relation. It is a component of a SQL statement that enables you to retrieve data from a database and organize it. This is the same as the projection operation in relational algebra.

Example:

SELECT Emp_Name FROM Employee WHERE Mobile=9935467903;

The aforementioned command will choose the employee record whose mobile number is "9935467903" from the "Employee" database.

Conclusion

With the many commands available in SQL, you may create and modify a broad variety of database objects. We went through each command in detail, including its syntax and an example to help you write queries. After reading this blog, one may simply experiment with the instructions and communicate with their database.