Let’s Decode MSSQL
A database is a collection of information that is organized so that it can be easily accessed, managed and updated.
A Row represents a record.
A column represents a field.
✨SQL lets us access and manipulate databases.
😄A DBMS is a collection of related records and a set of programs that access and manipulate these records and enables the user to enter, store and manage data.
😄A Relational Database is a database divided into logical units called tables, where tables are related to one another within the database.
SQL Server is a relational database management system developed by Microsoft.
🤔What is the difference between Microsoft SQL Server and SQL Management Studio (SSMS)?
😃Microsoft SQL Server is the RDBMS that handles data storage and management using the SQL language, while SQL Server Management Studio (SSMS) is a GUI tool designed for managing and administering SQL Server instances. SQL Server focuses on the core database engine and functionality, while SSMS provides a user-friendly interface, visual tools, and scripting capabilities for database management and administration.
🙄What is SQL Server Instance?
The SQL Server allows us to install multiple instances of the SQL Server database engines. These engines run side by side and completely isolated from each other. Each database engine installation is known as the SQL Server instance.
Every instance is a complete SQL Server Installation along with its own copy of server files, database engine, databases, Users and credentials.
A computer can have one or more than one instance of SQL Server installed. Each instance of SQL Server can contain one or many databases.
The server name in SQL Management studio often consists of two parts.
Firstly, there is the portion that identifies the computer, followed by the portion which identifies the SQL instance.
When you install MSSQL Server to your computer, you install an ‘instance’ which typically has a name associated with it. You may have multiple instances, and you can even elect one of these to be the ‘default instance’ which is used if no instance name is specified.
For example:
To connect to the default instance, use
.To connect to instance ‘MySQLservice’ on Computer ‘BobsComputer’
BobsComputer\MySQLservice
Creating Database
create database DatabaseName
use DatabaseName
Creating Table
create table tableName (ColumnName dataType)
Select * from tableName → displaying all the data from the table
Inserting Data in the table
Method 1:
insert into tableName values(data)
Method 2: By this you can store values only in selected columns
insert into tableName (columnName1, columnName2) values (value1, value2)
Update Command
update tableName set columnName = value where condition
Delete Command
delete from tableName where condition
Truncate Command
truncate table tableName →deletes all the rows from the table
Drop Command
drop table tableName →deletes the table entirely along with its schema.
SQL Constraints → used to specify rules for the data in a table.
🎫Constraints can be column level or table level
✅NOT NULL Constraint →Ensures that a column cannot have a null value
create table tableName (columnName datatype not null)
✅UNIQUE Constraint →Ensures that all values in a column are different
create table tableName (columnName datatype unique)
✅PRIMARY KEY Constraint →A combination of a NOT NULL and UNIQUE constraints. Uniquely Identifies each row in a table.
create table tableName(columnName datatype primary key)
🎫A table can have only one primary key.
✅FOREIGN KEY Constraint →Uniquely identifies a row/record in another table.
✅CHECK Constraint →Ensures that all values in a column satisfies a specific condition.
create table tableName(columnName datatype check(columnName ≥ condition))
✅DEFAULT Constraint →Sets a default value for a column when no value is specified.
create table tableName(columnName datatype default(value))
Order BY (sorts the data)
Select * from tableName order by columnName desc
Primary Key and Foreign Key
Primary key of one table becomes the foreign key of another table to create relationship between two table.
#Primary Key
create table tableName1 (columnName datatype primary key)
#Foreign Key
create table tableName2 (columnName dataType primary key, columName2, dataType, foreign key references tableName1(columnName->from tableName1)
🏓Note: create table demo(address varchar(max)) →Max means it can contain any number of characters
✅ForeignKey
📍Foreign Key can be NULL or Duplicate.
📍Foreign Key will only contain values that are already present in the primary key column which it is referencing.
📍From the referenced column of foreign key you can not delete values until and unless they are removed from foreign key column
Dropping Foreign Key Constraint
Alter table tableName drop constraint constraintName
Whenever we make keys in table SQL by default makes a keys folder.
alter table demo drop constarint (copy the name of that foreign key from keys folder and place it here)
Adding Foreign Key in Existing Table
Alter table tableName add foreign key(columnName) references referencedTableName(referencedColumnName)
Cascading Referential Integrity/Cascading Foreign Key
A table can have more than one Foreign Keys. But a table can have only one Primary Key.
Now in customer table if I will try deleting the row with id 4 then I will be getting a Foreign Key constraint error because this id also has made some orders in order table or If I will try to update the id of row with id no 4 then also I will be getting the error. This is the default behavior of foreign key.
🎯The cascading referential integrity constraints are the foreign key constraints that tell SQL server to perform certain actions whenever a user attempts to delete or update a primary key to which an existing foreign key points.
Following options are provided under Cascading referential Integrity, When we add a foreign key to a column one of these options has to be applied compulsorily:
😄No Action (By Default) → Specifies that if an update or delete statement affects rows in foreign key tables, then the action will be denied and rolled back. An error message will be raised.
😄Cascade → Specifies that if a user tries to delete the statements which will affect the rows in the foreign key table, then those rows will be deleted when the primary key record is deleted. Similarly, if an update statement affects rows in foreign key table, then those rows will be updated with the value from the primary key record after it has been updated.
create table [order](ordId int primary key, cid int foreign key references customerTbl(Cid) on delete cascade on update cascade)
😄Set Default →If a user tries to delete or update statement that will affect rows in foreign key table,then all rows containing those foreign keys are set to the default value mentioned in the default constraint.
create table [order](ordId int primary key, cid int foreign key references customerTbl(Cid) on delete set default on update set default)
#Thing to note here is that referenced table should have that default value in the referenced column.
😄Set Null → If a user tries to delete or update statement that will affect rows in foreign key table, then those values will be set to null when the primary key record is deleted or updated in the primary key table.
create table [order](ordId int primary key, cid int foreign key references customerTbl(Cid) on delete null on update null)
👍🏼Note: You cannot add ON DELETE CASCADE to an already existing constraint. You will have to drop and re-create the constraint.
Alter table tableName drop constraint constraintName
Alter table tableName add constraint constraintName foreign key(fid) references other table(id) on delete cascade on update no action
ALTER Command
😎Changing the database name
Alter database databaseName modify name=NewName
#changing database name using in-built stored procedure
execute sp_renamedb ‘oldDatabaseName’, ‘newName’
😎Changing the table name
execute sp_rename ‘oldTableName’ ‘newTableName’
😎Adding a new column
Alter table tableName add columnName dataType
#Adding value into the newly added column
Update tableName set columnName = value where condition
😎Deleting a column
Alter table tableName drop column columnName
😎Changing dataype of a column
alter table tableName alter column columnName newDataType
Adding and Dropping SQL Constraints Using Alter Command
🧐Adding Not Null constraint to an already existing table
Alter table tableName alter column columnName dataType not null
🧐Deleting Not Null constraint
Alter table tableName alter column columnName dataType null
🧐Adding Unique constraint
Alter table tableName add unique(columnName)
#In SSMS you can check this constraint in index folder
🧐Deleting Unique constraint
Alter table tableName drop constraint (put constraint name from the index folder)
🧐Adding Primary key constraint
Alter table tableName add primary key (columnName)
This column should have not null constraint before running this query
🧐Dropping the Primary Key constraint
Alter table tableName drop constraint (put constraint name from the keys folder)
🧐Adding Foreign key constraint
Alter table tableName2 add foreign key (columnName2) references tableName1(columnName1)
🧐Dropping the Foreign Key constraint
Alter table tableName drop constraint (put constraint name from the keys folder)
🧐Adding CHECK constraint
alter table tableName add check(column ≥ condition)
🧐Dropping the CHECK constraint
Alter table tableName drop constraint (put constraint name from constraints folder)
🧐Adding DEFAULT constraint
Alter table tableName add default defaultValue for columnName
🐇Alias
Select columnName as nameYouWant from tableName
👻Joins → Used to combine two or more table data in a result set.For joins a condition is that all the tables which we are joining should have atleast one same column. Types of Joins:
→Inner Join
→Left Join
→Right Join
→Full outer Join
🧠INNER JOIN
Select * from tableName1 as alias1 inner join tableName2 as alias2 on alias1.commonColumn = alias2.commonColumn
Select alias1.columnName1 , alias1.columnName2, alias2.columnName3 from tableName1 as alias1 inner join tableName2 as alias2 on alias1.commonColumn = alias2.commonColumn
#In the inner join only that data will be displayed which is attached to the commonColumn values present in both the tables. Entries having common columnValues(those values should be present in both the tables) will be displayed in inner join
🧠LEFT JOIN/LEFT OUTER JOIN
Select * from tableName1 as alias1 left join tableName2 as alias2 on alias1.commonColumn = alias2.commonColumn
#If left table will be having an entry which is not present in the commonColumn of right table, then on doing left Join that entry will also be displayed.
🧠RIGHT JOIN/RIGHT OUTER JOIN
Select * from tableName1 as alias1 left join tableName2 as alias2 on alias1.commonColumn = alias2.commonColumn
#If right table will be having an entry which is not present in the commonColumn of leftt table, then on doing right Join that entry will also be displayed.
🧠RIGHT JOIN/RIGHT OUTER JOIN
Select * from tableName1 as alias1 left join tableName2 as alias2 on alias1.commonColumn = alias2.commonColumn
#If right table will be having an entry which is not present in the commonColumn of leftt table, then on doing right Join that entry will also be displayed.
🧠FULL OUTER JOIN
Select * from tableName1 as alias1 full outer join tableName2 as alias2 on alias1.commonColumn = alias2.commonColumn
This join will bring the records based upon the commonColumn in both the tables and also brings extra(unmatched) records from left as well right table.
🧠SELF JOIN
A self join is a regular join, but the table is joined with itself.
Select A.empName as Employee, B.empName as Manager from EmployeeManager as A inner join EmployeeManager as B on A.Manager_id = B.Employee_id
🐱Identity or Auto Increment
Column value on which we apply auto increment or identity increase by itself when we add records. We don’t need to pass value of that column while inserting the records.
create table tableName (columnName dataType identity)
#If I want that value in that column should start from the value I give and should incremnt by value I give then I can do:
create table tableName (columnName dataType identity(100, 5))
🐹Union and Union All
Union and Union all merges the values of the columns of the two tables.In union and union all it is mandatory that column names and data types should be same in both the tables.
Select * from tableName1 union Select * from tableName2
#In union it sorts the records and also remove the duplicate entries while union all doesn’t.
Select * from tableName1 union all Select * from tableName2
🐦Intersect and Except
Select * from tableName1 intersect Select * from tableName2
Select * from tableName1 except Select * from tableName2
🍳Aggregate Functions
Used to perform calculations on a set of values and return a single value. These aggregate functions only works on the columns having the integer datatype.
Select sum(columnName) as aliasName from tableName
Select max(columnName) as aliasName from tableName
Select min(columnName) as aliasName from tableName
Select avg(columnName) as aliasName from tableName
Select count(columnName) as aliasName from tableName →count how many rows this column contains.
🌜Group By
→Group By statement is used with aggregate functions(sum, max etc) to group the result-set by one or more columns.
→Suppose a table contains the salary and city of the employees.Now I want to sum the salary of all the employees belonging to a particular city:
Select city, sum(salary) as Total salary based on city from tableName group by city
#We can only select those columns which were present in group by command and aggreagate function.
Select EmpName city, sum(salary) as Total salary based on city from tableName group by city →This will give error
# We can use one or more than one column in a GROUP BY clause.
→Suppose I want to know total salary of both genders based on cities.
Select Gender, city, sum(salary) as Total salary based on city from tableName group by Gender, city
🌞Having Clause
Having clause is used to specify conditions like where clause but this is used with GROUP BY command.
Select columnName aggregateFunc(columnName) as aliasName from tableName group by columnName having condition.
💣We can use where clause before group by like this:
Select product sum(sales from ProductSales where product not in (‘Software’) group by product
#But remember we can’t use where clause after group by clause.
🚍Having is slower than where clause in case of performance.
⛅Views in SQL
A view in SQL is just a saved SQL Query. A view can also be considered as a virtual table.We can create view from one table or more than one table.
create view vW_ViewName as Select * from tableName1 as aliasName1 inner join tableName2 as aliasName2 on aliasName1.commonColumn = aliasName2.commonColumn
→ vW is a keyword here which tells that this is a view.as here is not the one which we use for alias.as here is used to ensure that Select statement has to be written only after this as.
#To see this view
Select * from vw_ViewName
Views can be used as a mechanism to implement row and column level security.
sp_helptext viewName →To see the query of this view
drop view viewName →to delete a view.
Inserting the data in view
Whenever we will be inserting anything into the view then it will be getting inserted also into the original table because view is nothing, it’s just a virtual table.
#Creating the view
create view viewName as Select * from tableName
#Seeing that view
Select * from viewName
#Inserting data into the view
insert into viewName values (data)
🛴Update in View
When we will be updating the data in view then data in original table will also be affected.
update viewName set columnName=’newValue’ where condition
🛴Delete with Views
When we delete some data from views that gets deleted from original table also .
delete from viewName where condition
🧨Like Operator
Used in where clause to search for a specified pattern in a column. Wildcards used in conjunction with LIKE operator are:
🎁% →zero, one or multiple characters.
🎁_->single character
🎁[ ]->multiple characters
SELECT * FROM demoTable WHERE name LIKE 'a%'-->means name starts with a
SELECT * FROM demoTable WHERE name LIKE 'a___'-->means it has three characters after a
SELECT * FROM demoTable WHERE name LIKE '[a-g]%'-->Display all the names starting with alphabets between a and g
SELECT * FROM demoTable WHERE name LIKE '[a,g,w]%'-->Display names starting with a, g or w🎨Subquery
A subquery or inner query or nested query is query within another SQL query and embedded within the WHERE clause.Few Rules for subqueries are:
🛒Subqueries must be enclosed within the parenthesis.
🛒A subquery can have only one column name in the SELECT clause.
🛒An ORDER BY command cannot be used in the sub-query although it can be used in the main query.
SELECT * FROM tableName WHERE columnName IN (SELECT columnName from TableName WHERE condition)🎏Scalar and Multi-Valued Subqueries
🧵Scalar subqueries: Subqueries that return one row to the outer SQL statement. Operators that can be used with these queries are =,>,<,!=.
🧵Multi-Valued Subqueries: Subqueries that return more than one row to the outer SQL statement. Operators that can be used with these are in,any and all
#Since it's a multi valued subquery so '<' can't be used alone.So any is used here.Any will return all the rows which are less than all the results of subquery.
SELECT * FROM Students WHERE attendance < any (SELECT attendance from Students where studentName='abc' or studentName='jkl')
#all will work like suppose subquery result contains two values 40 and 50.So all those rows wil be returned now which are having attendance less than 40
SELECT * FROM Students WHERE attendance < all (SELECT attendance from Students where studentName='abc' or studentName='jkl')
🎭Self Contained and Co-Related Subquery
🥽Self-contained Subquery →These queries are written as standalone queries, without any dependency over outer query. In this case the subquery is processed first and then result is passed to outer query.
🥽Co-Related Subquery →These queries refer one or more columns from the outer query and therefore depend on the outer query. These cannot run separately without outer query.
✨Note: Self-contained or Co-related subqueries both can be multi valued or scalar subqueries.
#self contained
SELECT * FROM Employees WHERE DeptId IN (SELECT ID FROM Department WHERE name = 'HR')
#Co-Related Subquery
SELECT * FROM Employees AS e WHERE e.DeptId IN (SELECT d.id FROM department AS d WHERE e.gender='female')🧶WHERE clause do not allow to use scalar aggregate functions with itself but we can use scalar aggregate functions with subqueries.
#This will run fine
SELECT * FROM Employees WHERE Salary IN (SELECT min(Salary) from Employees)
#This will give error
SELECT * FROM Employees WHERE Salary = min(Salary)🎡Between, Top, Percent, Distinct and In Operator
SELECT * FROM tableName WHERE columnName between 1000 and 2000
SELECT TOP 5 * FROM tableName -->top 5 rows it will return
SELECT TOP 40 PERCENT * FROM tableName -->top 40% rows will be returned
SELECT DISTINCT Name FROM tableName -->unique results will be returned
SELECT * FROM tableName WHERE columnName IN ('abc', 'def')
SELECT * FROM tableName WHERE columnName NOT IN ('abc', 'def')🔮SELECT INTO
Selects the data from one table and inserts it into a new table. We can copy all the rows and columns or selected ones from an existing table into a new table.
SELECT * INTO newTableName FROM existingAllTableName🎃We can also create a new table by inserting data from two existing tables using joins.
#Bring all columns from A and DeptName column from B
SELECT A.*, B.DeptName INTO newTableName from ExistingTable1 as A
inner join
ExistingTable2 as B
ON B.Id = A.DeptId🎪We can create a new table in another database by copying data from the table in some other database.
SELECT * INTO anotherDatabaseName.dbo.tableName FROM existingTableIUnCurrentDatabase🎎If we want to create a new table while copying the schema of some existing table but don not want to insert data from the existing table.
SELECT * INTO newTableName FROM oldTableName WHERE 1<>1🔮INSERT INTO
This append rows from one existing table to another existing table. Both the table should have similar column definitions.
🎯Note: Difference between SELECT INTO and INSERT INTO is that in SELECT INTO a new table is created and then the data is inserted into it while in INSERT INTO data is appended to an already created table.
INSERT INTO TargetTableName(Column1, Column2) SELECT Column1, Column2 FROM SourceTable 🎹Renaming the Database and the Table
sp_renamedb 'oldName', 'newName' -->Renaming the database
sp_rename 'oldName', 'newName' -->Renaming the Table
#sp_rename is the stored procedure here💼Stored Procedure
A stored Procedure is a set of Structured Query Language statements with an assigned name. These can be reused and can be shared by multiple programs.
Types of Stored Procedures:
📍System stored procedures
📍User defined stored procedures
#Creating procedure having no arguments
create procedure/proc spProcedureName ->It has to be prefixed with sp
as
begin-->This is like we have open bracket in a function defintion
Select columnName1, columnName2 from tableName
end-->This is like the closing bracket in a function definition
Ways to run a stored procedure:
spProcedureName
exec spProcedureName
execute spProcedureName
#Creating procedure having one argument
create procedure spProcedureName
@id int-->argument variable
as
begin
SELECT * FROM tableName WHERE id = @id
end
execute spProcedureName argumentYouWantToPass
#Creating procedure with multiple parameters
create procedure spProcedureName
@id int-->argument variable1
@Name varchar(50)
as
begin
SELECT * FROM tableName WHERE id = @id and Name = @Name
end
execute spProcedureName argumentYouWantToPass1, argumentYouWantToPass2
#Altering the existing stored procedures
alter procedure spProcedureName
@id int-->argument variable1
@Name varchar(50)
as
begin
SELECT name, class FROM tableName WHERE id = @id and Name = @Name
end
#To see the code of any stored procedure
sp_helptext procedureName
#Deleting the stored procedure
DROP PROCEDURE procedureName
#Microsoft prefixes system stored procedures with sp_
#If we want that no one should be able to see the code of our stored procedures using sp_helptext,then we can encrypt it like this:
alter procedure spProcedureName
@id int-->argument variable1
@Name varchar(50)
with encryption
as
begin
SELECT name, class FROM tableName WHERE id = @id and Name = @Name
end
#Removing the encryption
alter procedure spProcedureName
@id int-->argument variable1
@Name varchar(50)
with encryption-->Just remove this line and execute this for decryption
as
begin
SELECT name, class FROM tableName WHERE id = @id and Name = @Name
end
#Stored Procedure with Output Parameters-->Means it will return something ob being getting executed
CREATE PROC spProcedureName
@Gender varcher(50) -->It is an input parameter i.e argument
@EmployeeCount int output -->It is an output parameter so write Output after it's name
as
begin
SELECT @EmployeeCount = Count(EmpId) from tableName WHERE Gender=@Gender
end
Declare @TotalEmployee int -->Making a variable.When we make a variable inside a stored procedure then we do not need to use word Declare.
execute spProcedureName 'male', @TotalEmployee output-->It will cache value returned by stored procedure
SELECT @TotalEmployee as 'MaleEmployees'-->Seeing the value cached in it by stored procedure🚊Functions in SQL server
SQL server functions are useful objects in SQL server databases. A function is a set of SQL statements that performs a particular task. The SQL server comes with a set of built-in functions that perform a variety of tasks. A function is a stored program that will surely return some value.
Note: We could also have created a stored procedure to group a set of SQL statements and execute them but stored procedures cannot be called within SQL statements.
If you are working with a large dataset then with functions you can hit performance issues, so prefer to use store procedures in that case.
💊Functions can be used only with SELECT statements.
💊Functions compile every time.
💊Functions must return a value or result.
💊Functions only work with input parameters.
💊TRY and CATCH statements are not used in functions.
There are three types of user-defined functions in SQL server:
🌌Scalar functions →Takes one or more parameters and returns a single (scalar) value.
🌌Inline table-valued functions
🌌Multi statement table-valued functions
#Function without a parameter
create function demoFunction1()
returns varchar(100)
as
begin
return 'I am a demo function'
end
SELECT dbo.demoFunction1
#dbo-->database owner
#Function with a single parameter
create function demoFunction2(@num as int)-->Taking num as parameter
returns int
as
begin
return (@num*@num)
end
SELECT dbo.demoFunction2(2)
#Altering a function
alter function demoFunction2(@num as int)-->Taking num as parameter
returns int
as
begin
return (@num*@num*@num)
end
#Dropping a function
drop function dbo.demoFunction1
#We can also use if and while statements inside these scalar functions
create function demoFunction2(@age as int)
returns varchar(100)
as
begin
declare @str varchar(100) -->declaring a variable inside function
if @age >= 18
begin
set @str = 'You can vote!'
end
else
begin
set @str = 'You cannot vote!'
end
return @str
end
#Scalar functions can also call other functions within them