Basics Types of Queries in SQL

Hello Readers, We often want to learn a language to brush up on our skills and be in the skilled workforce. In this blog, we will be exploring the basic types of SQL queries of which we should be aware. We will be looking at:

1. What is SQL?

2. Why SQL is a popular language?

3. Types of SQL queries.

1. What is SQL?

SQL has a full form of Structured Query Language. From this name we can understand that this language is more focussed on what is the structure of the language. For example, the basic format to write SQL query is that:

SELECT * --Select the columns which we want to get as output FROM Table1 --From indicates the name of the table from which we want the columns WHERE Condition1 -- WHERE condition to get the specific rows GROUP BY Value1 -- group by some values. For example, group sales amount based on products ORDER BY ColumnName -- Order in ascending or descending particular column

As we saw in the above query it has the basic structure of this format. Any SQL query will follow this basic structure. First, we SELECT the columns, then we define FROM table, and WHERE condition if any. If we have any condition to return in the result set. Then we focus on GROUP BY, where we define how we want to group the rows by. For example, we define that we want to group the sales by products. And last ORDER BY, where we define the on what column we want to order by. For example, the Date column is in ascending order or descending order. This is the basic query to select the desired rows. We can make this query more complex based on what are the needs of the project and the stakeholders.

2. Why SQL is a popular language? Now we have seen the basic structure of SQ language. We can now see why SQL is so a popular language. This is the most popular language because this language is simple to learn, and a lot of data is stored in a structured format. Data can be stored in two formats, one is structured and the other is a non-structured database. SQL is pretty popular for structured data storage. This makes everyone use and filter based on their needs. This gives cutting-edge speed to queries by multiple users. Also, SQL is the most simple but fast language to return millions of rows at the same time. There are a lot of options for users to interact with the servers. We can modify the calculation speed by increasing the power of computing. This is the most important benefit to return and store millions of rows at the same time.

3. Types of SQL queries. the SQL queries can be categorized into 5 categories based on the purpose of the user:

1. DDL- Data Definition Language

2. DML- Data Manipulation Language

3. DQL- Data Query Language

4. DCL- Data Control Language

5. TCL- Transaction Control Language

1.DDL- Data Definition Language: Data Definition Language defines the actual creation modifications, renaming, and dropping (deleting) of the tables, rows, or columns. This type uses basic queries. Any creation of a table starts with this type. Let's see some examples of DDL.

-- Creating the table -- We can use CREATE TABLE statement CREATE TABLE Products (SrNo INT IDENTITY(1,1), ProductName VARCHAR(100), ProductType VARCHAR(100), LocationonShelf VARCHAR(20) ) --Suppose we want to alter the table, and rename the column ALTER TABLE Products RENAME COLUMN Producttype To ProductCategory --Suppose we want to alter the table, and add the column ALTER TABLE Products ADD COLUMN ShelfLife --Suppose we want to alter the table, and drop the column ALTER TABLE Products DROP COLUMN ShelfLife -- Suppose we want to drop the table. Meaning if we want to delete --the table from the schema DROP TABLE Products These are a couple of queries that we use in the DDL. DDL defines, drops, adds, deletes, and renames, comments rows, columns, or tables.

2. DML- Data Manipulation Language DML is basically the manipulation of tables and columns. In this type, we use INSERT, UPDATE, DELETE, EXPLAIN, EXECUTE, LOCK, etc. Manipulation means we can manipulate the table rows and columns, We can insert new records, update the value of the specific value, delete specific records in the table, and execute the specific stored procedures. We can see the meaning of the stored procedure in some other blog.

-- After creating the table we can insert new records if we want --We can use INSERT INTO statement for inserting the new record or row INSERT INTO Products (SrNo, ProductName, ProductType, LocationonShelf) VALUES (4,'Peanut Butter','Butter','A-19') -- Updating the specific record in the tbale UPDATE Products SET ProductName = 'Milk',ProductType = 'Dairy Product' WHERE SrNo = 4 --DELETE records for specific SrNo DELETE FROM Products where SrNo=4 --EXECUTE command to run stored procedure EXUCUTE StoredProcedurename (Parameters if any)

3. DQL- Data Query Language The data query language is the most common and important type of language in SQL. It is important because all queries should start with the SELECT word. Suppose we want to select specific columns, we have to star the query with

SELECT * FROM TableName.

Let’s go to the SQL Editor to see what the query looks like.

SELECT * FROM Products -- The above query will select all t he orws from the Products table. -- '*' means that give the all the orws that are in the Products Table. -- We can use SELECT to make some arithma=etic calculations as well SELECT (100+100) --This will give us output as 200 --What is Alias? -- Alias is the naming that we give to the output. --Suppose we want to give name to above output, we will give. SELECT (100+100) as Total

4. DCL- Data Control Language Data Control Language is used to control the access to the database tables, views, stored procedures, and many more objects in SQL Server. DCL Commands are the most useful commands when it comes to the security of the database. There are two types of privileges: System Privilege and Object Privilege. DCL is important so that unauthorized people cannot access the data. There are 2 most important DCL commands: A. GRANT B. REVOKE GRANT is used to grant permission to a particular object. The example can be that if we don’t want anyone to access the views, they will not see the views. Or just permission to view but not to update the data.

GRANT privilege_list ON object_name TO user_name

REVOKE is used to remove permission from the user. If you want the users to return the permission that you granted in past, you can use REVOKE.

REVOKE privilege_list ON object_name TO user_name

5. TCL- Transaction Control Language

TCL are the commands which are used to make the data consistent in the server database. Whenever we execute some SQL Statements in the DBMS (Data Base Management System) it is called a transaction. These transactions are temporary in SQL, so in order to make them permanent, we use TCL commands. Basic commands used are: COMMIT, ROLLBACK, SAVEPOINT.

IMPORTANT NOTE: Hope you like this story. Please follow me for more content. Please be aware that these are my thoughts and anyone copying this content will be reported and blocked by me. Also, will be taken appropriate legal action for using this story.