MONTH START OFFER: Flat 15% Off with Free Self Learning Course | Use Coupon MONTH15

- 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: Microsoft Power BI Tutorial For Beginners

 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

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

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: How To Become Expert In Sql Server Developer?

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,'[email protected]')

Used To INSERT data into a table

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

Read: Top 50 MongoDB Interview Questions and Answers

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: All About SQL Joins and Subqueries

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: SQL Data Types for Oracle PL/SQL, MySQL, SQL Server, and MS Access

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: Power BI - Getting Started with Query Editor in Power BI

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

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')

Used To INSERT data into a table

Read: SQL Server Indexes-All You Need to Know

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

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

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

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

Read: MSBI Certification & Learning Path: My Success Story You Must Know

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

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

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: MSBI Interview Questions & Answers for Fresher, Experienced

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: What Is The Difference Between Having And Where Clause In SQL?

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: What is the Career Path for a SQL Server DBA?

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: SSRS Tutorial for Beginners

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’

Sign up for the SQL server training to be a part of a growing career run!

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: SSRS Interview Questions & Answers For Experienced


    Sanchayan Banerjee

    I love to learn new things and also like sharing my knowledge with others. As an experienced IT Professional I like to update myself constantly with new and upcoming technologies. The database management system is one of my favorite subjects which I constantly explore.


Comments

Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

-0 day 04 Dec 2020

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

8 days 12 Dec 2020

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

5 days 09 Dec 2020

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

7 days 11 Dec 2020

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

-0 day 04 Dec 2020

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

1 day 05 Dec 2020

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

-0 day 04 Dec 2020

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

-0 day 04 Dec 2020

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation

Upcoming Class

-0 day 04 Dec 2020

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks

Upcoming Class

1 day 05 Dec 2020

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning

Upcoming Class

16 days 20 Dec 2020

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop

Upcoming Class

15 days 19 Dec 2020

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews