Important SQL Queries

SQL Server

SQL Server is a relational database management system created by Microsoft and uses ANSI SQL as standard query language.SQL, or Structured Query Language. It uses SQL statements to store, manipulate, retrieve, and manage data in a database.

Types of SQL statements

In SQL Server, SQL Statements are divided into four categories,

DML(Data Manipulation Language) - Select, Insert, Update, Delete
DDL(Data Definition Language) - Create, Drop, Alter
DCL(Data Control Language) - Grant, Revoke
TCL(Transaction Control Language) - Begin, Tran, Commit, Rollback.

Introduction

In this article, we will learn how to use different SQL statements to create objects such as tables, views, stored procedures, and functions(system and user-defined) in a database and how to use the objects to store and manipulate data.

This step-by-step guide will explain how to use the SQL Server Management Studio tool to execute various SQL statements.

Step 1

Assuming you've SQL Server 2012 or higher version.

Open Sql Server Management Studio. After connecting, open a New Query Window.

Step 2

In the New Query Window, start writing and executing SQL queries. Before anything else, we must create a database. To create one, follow the following steps.

Database

A database is an organized collection of data and objects (tables, views, procedures, functions, etc.), generally stored and accessed electronically from a computer system.

Create a database,

Create database LearningBasicSQL

Tables in SQL Server

Tables are database objects that contain all the data in a database. In tables, data is logically organized in a row-and-column format similar to a spreadsheet. Each row represents a unique record, and each column represents a field in the record. Each row in a relational database is known as a tuple.

To find a detailed article about the Tables in SQL, please go through this: Tables in SQL

Create a table,

use LearningBasicSQL  
create table tbl_Employee(  
   EmpID int primary key not null identity(1,1),  
   EmpName varchar(50),  
   City varchar(20)  
)

Insert values in a table,

insert into tbl_Employee values('Shilpa','Kolkata'),('Sayantan','Howrah')

Get rows of a table,

select * from tbl_Employee

Insert values in a table using Insert Into Select statement,

insert into  tbl_Employee (EmpName,City)    
 select 'Anuja','Siliguri'

Delete a row from a table,

delete from tbl_Employee where EmpID=4

Truncate a table,

truncate table tbl_Employee

Drop a table,

drop table tbl_Employee

Stored Procedure in SQL

A stored procedure is a subroutine available to applications that access a relational database management system. Such procedures are stored in the database data dictionary. To find a detailed article about the stored procedure, please go through this: Stored Procedure. The following queries will help you create, update, and delete store procedures.

Create a stored procedure for add/update,

create procedure sp_AddEditEmployee (  
@EmpId int=0,  
@EmpName varchar(50),  
@City varchar(20),  
@Mode int  
)  
as  
begin  
if @Mode=0  
insert into tbl_Employee values(@EmpName,@City )  
if @Mode=1  
update tbl_Employee set EmpName=@EmpName,City=@City where EmpID=@EmpId  
end

In the above example, I have used a variable @Mode to add and update a table.Considering @Mode=0 for Addition and @Mode=1 for the update. The advantage of the above procedure is that we do not have to create two separate procedures for add and update. We can manage both operations using a single procedure.

Exec Procedure For Add,

exec sp_AddEditEmployee 0,'Amit','Nadia',0  
select * from tbl_Employee

Exec Procedure for Update,

exec sp_AddEditEmployee 1,'Shilpa','Darjeeling',1  
select * from tbl_Employee

Drop a procedure,

drop procedure sp_AddEditEmployee

View in SQL Server

Create a virtual table in which the query defines contents (columns and rows). Use this statement to create a view of the data in one or more tables in the database. A view contains a set of rows and columns.

To find a detailed article about Views in SQL, please go through this: Views in SQL

Create a view,

create view vw_Employee    
as     
select EmpID,EmpName from tbl_Employee

Display a View,

select * from vw_Employee

Drop a View,

drop view vw_Employee

Functions in SQL Server

Creates a user-defined function in SQL Server and Azure SQL Database. A user-defined function is a Transact-SQL or common language runtime (CLR) routine that accepts parameters, performs an action, such as a complex calculation, and returns the result as a value. The return value can be a scalar (single) value or a table.

To find a detailed article about Functions in SQL, please go through this: Function in SQL

Functions can be broadly classified into two types,

System Function in SQL Server
The functions provided by SQL Server and cannot be modified commonly used to solve complex calculations—for example, Mathematical functions, date and time functions, and cursor functions.
UUser-DefinedFunctions in SQL Server
SQL Server provides the user the facility to create functions stored in the database and help solve calculations quickly. The UDF(user-defined functions)are further classified into Scalar and Table Valued functions.
Scalar Functions in SQL Server
SQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of a value. The return value can either be a single scalar value or a result set

.In the following example, we will learn how to create a scalar UDF. The function defined in the example helps a user identify whether a number is odd or even. The input provided is 2.

create function CheckOdd_EvenNo(@input int)    
returns varchar(10)    
as    
begin    
declare @output varchar(10)    
if @input%2=0    
set @output='Even'    
else    
set @output='Odd'    
return @output    
end

To execute the function, use the statement as shown below.

select [dbo].[CheckOdd_EvenNo](2)

Table-valued functions in SQL Server

A table-valued function returns a single rowset (unlike stored procedures, which can return multiple result shapes). Because the return type of a table-valued function is Table, you can use a table-valued function anywhere in SQL that you can use a table.

In the following example, we will learn how to create a table-valued function. I have created a UDF to help us calculate the first 'N' natural numbers.

create function First_N_Natural_No (@item int)    
returns  @table table(itemValue varchar(20))    
as    
begin    
declare @count int=1    
while @count<=@item    
begin    
insert into @table values(@count)    
set @count=@count+1    
end    
return    
end

Use the following query to get the first 10 natural numbers. Since the UDF returns a table, we must use a select statement to get the result. The input provided is 10.

select * from First_N_Natural_No(10)

Summary

This article taught us how to create, insert, and update values, drop a simple table, and view using SQL Queries. Also, we got a brief idea about Stored Procedures and functions. Now we can create procedures and functions and use these objects to easily add and update a table and solve complex calculations.

In the next article, we will learn when to use procedures and functions and the advanced topics.

More on SQL Queries