Connecting to SQL Server using SSMS,Install Sql server 2019 Step by Step

Introduction In this post, we'll go over step-by-step instructions for installing Microsoft SQL Server on Windows computers. We'll use Windows Server 2019 and SQL Server 2019 Developer edition for this tutorial. Pre-requisites Using a Windows computer with a 2 core and 4 GB of RAM Download the 2019 Developer Edition of SQL Server. Download the SQL Server installation...

Read More

Creating altering and dropping a database

 Creating database From GUI:-Open SQL Server Management and right-click of database and click new database opti...

Read More

Creating and working with table

The aim of this post is to create tblperson and tblgender tables and establish primary key and foreign key constrain...

Read More

Adding A default constraints

A column default can be specified using default constraints. The default constraint is used to insert a default value into a column. The default value will be added to all new record, if no other values is specified, including null.Altering an existing column to add a default constraintSyntax:-Alter table table_name add constraint (constraints_name) default  (default_value)...

Read More

Cascading referential integrity constraint

Cascading referential integrity constraint allow to define the action MS sql server should taken when a user attempt to delete or update a key to which exiting foreign key poin...

Read More

Adding a check Constraint

 Check Constraint:-  Check Constraint is used to limit the range of the values, Which can be entered into a column.The general formula for check constraint in sql server Alter tabla (tablname) add constraint (constraint name) check (Boolean expression...

Read More

Identity Column in SQL Server

What is an Identity Column?If a column is marked as an identity column, then the value for this column are automatically generated, when you inserted a new row in the table.Create an Identity column Create a table tblemployee, this statement marks PersonId as an identity column with seed = 1 and Identity Increment = 1. Seed and Increment values are optional. If you do...

Read More

How to get the last generated identity column value in SQL Server

...

Read More

Unique key constraint

 We use the UNIQUE constraint to enforce the uniqueness of a column i.e. the column should not allow any duplicate values. We can add a unique constraint through the designer or using a query.To add a unique constraint using MS SQL Server Management Studio Designer:1. Right click on the table and select Design option.2. Right click on the column and select Indexes/Keys...

Read More

Select statement in MS sql server

 SELECT Column_ListFROM Table_NameIf you want to select all the data(column), you can also use (* ) For better performance use the column list, instead of using (*.)SELECT * FROM Table_NameTo Select distinct rows use DISTINCT keywordSELECT DISTINCT Column_List FROM Table_NameExample: Select distinct city from tblPersonFiltering rows with WHERE clauseSELECT Column_List FROM...

Read More

Group by in MS sql server

Group By In SQL Server we have got many aggregate functions. Examples1. Count()2. Sum() 3. avg() 4. Min() 5. Max()The GROUP BY statement groups rows that have the same values into summary rows,. It is often used in conjunction with one or more aggregate functions.I want an MS SQL query, which gives total salaries paid by City. show belowQuery for display total salaries by...

Read More

Joins in SQL server

Joins in SQL servers are used to query for retrieve data from 2 or more related tables. A Join clause is used to combine rows from one , two or more tables, based on a related column.In SQL, a join operation combines rows from two or more tables based on a related column between them. The result of a join is a new table that contains only the rows where the join condition...

Read More

Advanced or intelligent joins in sql server,

Considers Employees (tblEmployee) and Departments (tblDepartment) tablesEmployee Table (tblEmployee)Departments Table (tblDepartment)How to retrieve only the non-matching rows from the left table. The output should be as  below:Query:SELECT       Name, Gender, Salary, DepartmentNameFROM           tblEmployee...

Read More

Self join in sql server

Before post, we have seen joining 2 different tables - tblEmployees and tblDepartments. Have you ever thought of a need to join a table by itself? Consider tblEmployees table shown below.Write a query that gives the following result.Self Join Query:A MANAGER is also an EMPLOYEE. Both the EMPLOYEE and MANAGER rows are present in the same table. Here we are joining tblEmployee with itself using...

Read More

Different ways to replace NULL in sql server

 Consider the Employees table below. Next POst, we have learned to write a LEFT OUTER SELF JOIN query, which produced the following output.In the output, the MANAGER column, for Todd's rows is NULL. I want to replace the NULL value, with 'No Manager'Replacing NULL value using ISNULL() function: We are passing 2 parameters...

Read More

Stored procedures in MS Sql server

Stored Procedures:- A stored Procedures is a group of a T-SQL statement. If you have a situation,  where you write the same query over and over again, You can save that specific query as a Stored Procedure and call it just by its name.Syntax:- Create procedure procedures name As beginSQL statement EndExecute a Stored Procedures:- exec procedure nameStored...

Read More