Webinar Alert : Mastering  Manual and Automation Testing! - Reserve Your Free Seat Now

- 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: Difference Between Stored Procedure and Function in SQL Server

 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: SQL Server Views - Everything You Should Know

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 Long Does It Take To Become A SQL developer?

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: What Is SQL Queries? List Of All SQL Queries With Examples

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: What is the SQL Insert Query? How to Insert (Date, Multiple Rows, and Values in Table)

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: Difference Between Clustered and Non-Clustered Index in the SQL

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: What is the Substring Function in the SQL? Example of SQL Server Substring

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: SQL Fiddle: The Best Resource to Practice SQL online

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: How to Get Your Career as an SSIS Developer Rolling?

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: How To Start Your Career As MSBI Developer?

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 Evolution of SQL Server Versions and Editions

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: How to Compare MongoDB and DynamoDB?

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: How to Prevent SQL Injection Attacks?


     user

    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.


  • fb-15
  • twitter-15
  • linkedin-15

Comments

Trending Courses

salesforce

Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models
salesforce

Upcoming Class

9 days 11 Oct 2024

salesforce

QA

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

Upcoming Class

-0 day 02 Oct 2024

salesforce

Salesforce

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

Upcoming Class

2 days 04 Oct 2024

salesforce

Business Analyst

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

Upcoming Class

2 days 04 Oct 2024

salesforce

MS SQL Server

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

Upcoming Class

2 days 04 Oct 2024

salesforce

Data Science

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

Upcoming Class

10 days 12 Oct 2024

salesforce

DevOps

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

Upcoming Class

7 days 09 Oct 2024

salesforce

Hadoop

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

Upcoming Class

3 days 05 Oct 2024

salesforce

Python

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

Upcoming Class

17 days 19 Oct 2024

salesforce

Artificial Intelligence

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

Upcoming Class

10 days 12 Oct 2024

salesforce

Machine Learning

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

Upcoming Class

2 days 04 Oct 2024

salesforce

Tableau

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

Upcoming Class

3 days 05 Oct 2024

Interviews