04
OctWebinar Alert : Mastering Manual and Automation Testing! - Reserve Your Free Seat Now
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.
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.
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
|
Examples are
|
Examples are
|
Examples are
|
Examples are ALTER Session, Set Role |
Get complete SQL server self learning module
Learn SQL Server in the Easiest Way
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
)
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);
DROP: Used to DROP a table from a database
DROP table testable
RENAME: To RENAME an existing table.
ALTER TABLE testtable
RENAME TO testtable1;
SELECT: Used to retrieve rows from a database.
SELECT * from [Person].[Person]
INSERT: Used To INSERT data into a table
INSERT into [dbo].[testtable]
values ('Test','TestCity',30,'test@test.com')
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'
DELETE: DELETE a row of a table.
DELETE from [dbo].[testtable] where name='Test'
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
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
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;
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
Symbol | Example | Description |
% |
|
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
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') |
|
Read: What is the Substring Function in the SQL? Example of SQL Server Substring
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: SQL Fiddle: The Best Resource to Practice SQL online
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 | 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.
|
Here ‘per’ is a table alias.
|
Here ‘fname’ and ‘mname’ are column aliases |
Read: How to Get Your Career as an SSIS Developer Rolling?
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
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.
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 |
Operator | Statement | Output | ||
+ | SELECT 1 + 2 | |||
* | SELECT 1 * 2 | |||
- | SELECT 2-1 |
Operator | Statement | Output |
& | SELECT 1&1 | |
| | SELECT 1|2 |
Read: The Evolution of SQL Server Versions and Editions
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 |
Operator | Statement | Output |
+= |
|
|
*= |
|
Read: How to Compare MongoDB and DynamoDB?
Operator | Statement | Output |
ALL |
|
Returns all rows where Unitprice into Ordger Qty is greater than 500 |
AND |
|
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
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?
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.
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Interviews