Black Friday Deal : Up to 40% OFF! + 2 free self-paced courses + Free Ebook - SCHEDULE CALL

Select Course
Blog
Corporate Training

+1 202 599 3842

(4.8/5 ) | 1.5K+ Ratings

- SQL Server Blogs -

The Types Of Database Query And How To Use Them

Introduction of Database Query

If you have been looking for the definition of a database query, types of database queries or SQL statements types, the following write-up is just for you. Those of us who are into software development must be familiar with the term databases, more precisely Relational Database management system, which we lovingly call RDBMS. We all know, it is a place or container where we store data in a structured manner. Endlessly looking for what is Database query? Let’s understand the definition of a database query first, before moving ahead to learn about types of database queries.

What is Database Query?

A database query is a process of extracting the data from different databases and formatting it so that it is understandable by human readers. The data is generated as results returned by SQL - Structured Query Language.

Read: What Is A Data Warehouse, And Why You Need It?

But the question is how we put the data into the database.

I know there are a couple of interesting tools like SQL Server Management Studio which allows us to upload bulk data efficiently into the database system. But when you are writing a software application and you need to INSERT data into your back end database, SQL is the only option you have.

Check Free SQL Demo Class- Click Here

Now we have learned what is database query, In the next few paragraphs, we would learn about these different types of database queries or SQL statements to CREATE a table, INSERT UPDATE and DELETE data and finally to get data from a table.

different types of SQL statements

SQL Statements Types

SQL Statements are divided into five different categories:

Data Definition Language (DDL) Data Manipulation Language (DML) Data Control Language (DCL) Transaction Control Statement (TCS) Session Control Statement (SCS)

Used to give privileges to access limited data Used for managing data within the table object Used to give privileges to access limited data Used to apply the changes permanently save into database. Manage properties dynamically of a user session

Examples are

CREATE,

ALTER,

DROP ,

TRUNCATE,

RENAME

Examples are

SELECT,

INSERT,

UPDATE,

DELETE

Examples are

GRANT,

REVOKE

Examples are

COMMIT,

ROLLBACK

Examples are

ALTER Session,

Set Role

Get complete SQL server self learning module

Learn SQL Server in the Easiest Way

Learn from the videos
Learn anytime anywhere
Pocket-friendly mode of learning
Complimentary eBook available

Buy Self-learning at Discounted Price

1). Data Definition Language

CREATE: Used to CREATE a new table or a database.

For creating database the command is

Read: Comparative Study of SQL and NoSQL Databases

CREATE database test

For table use the command

CREATE table testtable
(
name varchar(50),
address varchar(50),
age int
)

For table use the command

Read: 70+ Most Asked SSIS Interview Questions for Freshers & Experienced

ALTER: Used to modify DELETE and INSERT a column in a table

ALTER TABLE testtable

ADD Email varchar(255);

Used to modify DELETE and INSERT a column in a table

DROP: Used to DROP a table from a database

DROP table testable

RENAME: To RENAME an existing table.

ALTER TABLE testtable

RENAME TO testtable1;

2). Data manipulation language

SELECT: Used to retrieve rows from a database.

SELECT * from [Person].[Person]

Used to retrieve rows from a database.

INSERT: Used To INSERT data into a table

INSERT into [dbo].[testtable]

values ('Test','TestCity',30,'test@test.com')

Used To INSERT data into a table

UPDATE: UPDATE existing value of a particular field in the table with new value

Read: How to Create Table in SQL Server by SQL Query?

UPDATE [dbo].[testtable] set address='TestCity1' where name='Test'

Used To INSERT data into a table

DELETE: DELETE a row of a table.

DELETE from [dbo].[testtable] where name='Test'

Used To INSERT data into a table

3). Data Control Language

GRANT : To provide a privilege to a role

CREATE ROLE testing

This CREATEs a role

GRANT CREATE TABLE TO testing;

Read: SQL REPLACE() Function: A Step-By-Step Guide

This assigns a the CREATE table permission to the role

REVOKE: Removes a privilege from a role

REVOKE CREATE TABLE FROM testing

4). Transaction Control Statement

COMMIT: Used to store changes performed by a transaction

BEGIN TRANSACTION;

DELETE from [dbo].[Emp] where Emp_id='101'

COMMIT TRANSACTION;

ROLLBACK: Used to erase all the data modification made from the start of the transaction.

Read: 70+ Most Asked SSIS Interview Questions for Freshers & Experienced

BEGIN TRANSACTION;

DELETE from [dbo].[Emp] where Emp_id='101'

ROLLBACK Transaction

5). Session Control Statement

ALTER Session: Starts or stops an event session.

ALTER EVENT SESSION test_session ON SERVER

STATE = start;

Set Role: Enables and disables role for current session

SET ROLE ALL;

SQL Server wildcards

Symbol	Description
%	Represents zero or more characters
_	Represents a single character
[]	Represents any single character within the brackets
^	Represents any character not in the brackets

Read: How to Prevent SQL Injection Attacks?

Wildcard example

Symbol	Example	Description
%	`SELECT * from [Person].[Person] where FirstName like 'K%'`	Returns all records where FirstName starts with K
_	`SELECT * from [Person].[Person] where LastName like '_o'`	Returns all records where all lastname has ‘o’ has second character.
[]	`SELECT * from [Person].[Person] where LastName like 'S[a]%'`	Returns all records where Lastname starts with S and has ‘a’ as the second character and any character after that.
^	`SELECT * from [Person].[Person] where LastName like 'S[^a]%'`	Returns all records where the second character of last name can have any character other than ‘a’ and any character after that.

Take a free demo of SQL server training as new batches available now

SQL Server Training & Certification

No cost for a Demo Class
Industry Expert as your Trainer
Available as per your schedule
Customer Support Available

Enrol For a Free Demo Class

Single and multiline INSERT

Single line INSERT	Multiline INSERT
SQL Statement used to INSERT single row of data in a table	SQL Statement used to INSERT multiple rows of data in a table
`INSERT into [dbo].[Emp] values (123,'Test','Agra','Testdept')`	`INSERT into [dbo].[Emp] values` `(123,'Test','Agra','Testdept'),` `(124,'Test1','Agra','Testdept'),` `(125,'Test2','Agra','Testdept')`

Read: How To Become Expert In Sql Server Developer?

Filters and need to use them

This is used to return SELECTed set of data based on the parameter passed in to the query via the where clause.

SELECT * from [Person].[Person] where FirstName='Ken'

This returns all the data from Person table where Firstname is Ken

SELECT * from [Person].[Person] where BusinessEntityID > 200

This returns all the data from Person table where BusinessEntityID greater than 200

SELECT * from [Person].[Person] where ModifiedDate > '2009-01-22'

Read: How to Increase the Speed of SQL Query Execution

This returns all the data where ModifiedDate is greater than 22 Jan 2009

SELECT * from [Person].[Person] where FirstName in ('Ken','Janice','John')

Returns all the rows where FirstName is Ken,Janice and John

SELECT * from [Person].[Person] where PersonType='EM' and BusinessEntityID>13

This has two filter clause joined by and and returns all the records where Persontype is ‘EM’ and BusinessEntityID is greater than 13 from PersonType table.

The above examples are CREATEd using the Person table of Adventureworks2016 database.

Table Alias and Column Alias

Table Alias Column Alias

Sometimes if the table name is large than SQL allows using a shorter name to that table in a query so that they can be easily accessed.

Similar to table alias one can RENAME the columns of the table to make the columns easily accessible during query writing.

SELECT per.FirstName,per.MiddleName from [Person].[Person] per

Here ‘per’ is a table alias.

SELECT per.FirstName fname,per.MiddleName mname from [Person].[Person] per where per.FirstName='Syed'

Here ‘fname’ and ‘mname’ are column aliases

Read: How to Create Database in Microsoft SQL Server?

DELETE vs TRUNCATE

DELETE	TRUNCATE
DELETEs existing records from a table	TRUNCATE TABLE statement is used to remove all records from a table in SQL Server
It is a DML statement	It is a DDL statement
DELETE statements can be rolled back	TRUNCATE statement once executed cannot be rolled back
DELETE from employee where empid=101	TRUNCATE table employee

Read More: Difference Between DELETE and TRUNCATE

Types of SQL Server Operators

An operator is a reserved word or a character used primarily in an SQL statement's WHERE clause to operate, such as comparisons and arithmetic operations.

SQL Server Operators

Different types of SQL server operators:

Read: SQL Having Clause Functions

Arithmetic	Bitwise	Comparison	Compound	Logical
Operators used for arithmetic operations in query	Used to perform bit manipulation in SQL	Used to compare one expressions with another	Execute some operation and set an original value to the result of the operation	Compare two conditions at a time to determine whether a row can be SELECTed for the output
Examples are `+,-,*`	Examples are `&,I`	Example are `>,<,=`	Example are `+=,*=`	Example are `ALL,AND,NOT`

1). Arithmetic Operators

Operator	Statement	Output
+	SELECT 1 + 2
*	SELECT 1 * 2
-	SELECT 2-1

2). Bitwise Operator

Operator	Statement	Output
&	SELECT 1&1
\|	SELECT 1\|2

Read: The Database as Part of the System Architecture

3). Comparison Operator

Operator	Statement	Output
>	`SELECT * from [Sales].[SalesOrderDetail] where UnitPrice>400`	Returns all rows where UnitPrice is greater than 400
<	`SELECT * from [Sales].[SalesOrderDetail] where UnitPrice`	Returns all rows where UnitPrice is less than 400
=	`SELECT * from [Person].[Person] where PersonType='EM'`	Returns all rows where Persontype is ‘EM

4). Compound Operator

Operator Statement Output

DECLARE @x1 int = 27;

SET @x1 += 2 ;

SELECT @x1 AS Added_

DECLARE @x1 int = 27;

SET @x1 *= 2 ;

SELECT @x1 AS Multiply_

Read: SQL‌ ‌Server‌ ‌DBA‌ ‌Roles‌ and‌ ‌Responsibilities:‌ ‌What‌ ‌Should‌ ‌You‌ ‌Know?‌ ‌

5). Logical Operator

Operator	Statement	Output
ALL	`SELECT * FROM [Sales].[SalesOrderDetail]` `WHERE UnitPrice*OrderQty > ALL (SELECT ActualCost FROM [Production].[TransactionHistory] WHERE ActualCost > 200);`	Returns all rows where Unitprice into Ordger Qty is greater than 500
AND	`SELECT * from [Person].[Person] where PersonType='EM' and BusinessEntityID>13`	Returns all rows from Person table where PersonType is EM and BusinessEntityID is greater than 13
NOT LIKE	`SELECT * from [Person].[Person] where FirstName not like 'K%'`	Returns all rows where FirstName does not start with ‘K’

SQL Server Training & Certification

Personalized Free Consultation
Access to Our Learning Management System
Access to Our Course Curriculum
Be a Part of Our Free Demo Class

Summary

The above write-up is a comprehensionof the definition of a database query and different types of database query statements available in SQL and their usage. The blog also details the different types of wild card operators available in SQL Server with examples. It also touches upon different types of filters that SQL Server provides and finally ends with brief details of different types of operators available in SQL Server with examples.

Although this is not a comprehensive guide on what is available in SQL Server, this would give the readers a sneak peek on what SQL Server provides and basic SQL statements types. If you wish to study the definition of a database query, types of database queries in detail, enroll for SQL Server certification training now.

Read: MySQL vs SQL Server vs Oracle: A Detailed Comparison

JanBask Training

A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.