The Aggregate function is used to perform calculations on a set of values and return a single value, ignoring all null values. The Aggregate functions are often used with the group by clause of the select statement in SQL. The database management aggregate function is a function in which the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning
 
All aggregate statements are deterministic In other words, aggregate functions return the same value each time that they are called when called with a specific set of input values.
 
See Deterministic and Nondeterministic Functions for more information about function determinism The over clause may follow all aggregate functions, except the STRING_AGG, grouping or GROUPING_ID functions
 
Aggregate functions can we use the expressions only in the following situations
    The select list of a select statement (either a subquery or an outer query).
    A having clause.

Functions 
SQL provides the following aggregate functions based off of the following Student table
 
Syntax
   SELECT * FROM Student;

Example

APPROX_COUNT_DISTINCT()
This statement returns the approximate number of unique non-null values in a group.
 
Approx_count_distinct expression evaluates an expression for each row in a group, and returns the approximate number of unique non-null values in a group.
 
This function is designed to provide aggregations across large data sets where responsiveness is more critical than absolute precision.
 
Approx_count_distinct is designed for use in big data scenarios and is optimized for the following conditions

    Access of data sets that are millions of rows or higher and
    Aggregation of a column or columns that have many distinct values

This function implementation guarantees up to a 2% error rate within a 97% probability. Approx_count_distinct requires less memory than an exhaustive count distinct operation, given the smaller memory footprint
 
Approx_count_distinct is less likely to spill memory to disk compared to a precise count distinct operation.
 
To learn more about the algorithm used to achieve this, see hyperloglog.
 
Syntax
    SELECT Count (StudentId) AS Approx_Distinct_OrderKey        
    FROM Student;     

Example

AVG () 
The AVG>statement calculates the average of non-null values in a set.  
The AVG is the data type of expression is an alias data type, the return type is also of the alias data type.
However, if the base data type of the alias data type is promoted, for example from tinyint to int, the return value will take the promoted data type, and not the alias data type
AVG () computes the average of a set of values by dividing the sum of those values by the count of nonnull values
 
If the sum exceeds the maximum value for the data type of the return value, AVG() will return an error. AVG is a deterministic function when used without the over and orderby clauses. It is nondeterministic when specified with the over and order by clauses. for more information.
 
Syntax
    SELECT AVG(StudentAge) FROM Student;      

Example

CHECKSUM_AGG()
This statement returns the checksum of the values in a group. CHECKSUM_AGG ignores null values.
The over clause can follow CHECKSUM_AGG
CHECKSUM_AGG can detect changes in a table
The CHECKSUM_AGG result does not depend on the order of the rows in the table. Also, CHECKSUM_AGG functions allow the use of the Distinct keyword and the Group By clause
 
If an expression list value changes, the list checksum value list will also probably change. However, a small possibility exists that the calculated checksum will not change
 
Syntax   

    SELECT CHECKSUM_AGG(CAST(StudentID AS int))          
    FROM Student;          
    GO  

Example  

COUNT()  
It is used to count the number of rows returned in a select statement.
Count function returns the number of items found in a group. count  operates like the COUNT_BIG function
These functions differ only in the data types of their return values.
Count always returns an int data type value.COUNT_BIG always returns a bigint data type value.
 
Syntax
    SELECT COUNT (StudentName) from Student   

Example  

COUNT_BIG ()
This statement returns the number of items found in a group.COUNT_BIG operates like the count function. These functions differ only in the data types of their return values.
COUNT_BIG always returns a bigint data type value. Count always returns an int data type value. 
The COUNT_BIG(*) returns the number of items in a group. This includes null values and duplicates.
The  COUNT_BIG (all expression) evaluates expression for each row in a group, and returns the number of nonnull values.
COUNT_BIG (distinct expression) evaluates expression for each row in a group, and returns the number of unique, nonnull values.
 
Syntax
    SELECT COUNT(*)          
    FROM Student          
    GO     

Example 

GROUPING ()
The Grouping statement indicates whether a specified column expression in a Group by  list is aggregated or not Grouping returns 1 for aggregated or 0 for not
 
Aggregated in the result set. Grouping can be used only in the SELECT <select> list, HAVING and ORDER BY clauses when group by is specified.
 
Grouping is used to distinguish the null values that are returned by Rollup, cube or Grouping sets from standard null values.
 
The null returned as the result of a Rollup, Cube or grouping sets operation is a special use of null. This acts as a column placeholder in the result set and means all.
 
Syntax
    SELECT GROUPING(StudentName) AS 'Grouping'          
    FROM Student          
    GROUP BY StudentName  WITH ROLLUP;          
    GO   

 Example

GROUPING_ID statement ()
This function computes the level of grouping. GROUPING_ID can be used only in the SELECT <select> list, HAVING, or ORDER BY clauses when GROUP BY is
specified.
 
Syntax
    SELECT GROUPING_ID(StudentId, StudentName)          
    FROM Student           
    GROUP BY CUBE(StudentId, StudentName)   

Example

In this article, you learned how to use a SQL Aggregate statement with various options.

HostForLIFEASP.NET SQL Server 2019 Hosting

In this article, we will learn how to implement the Audit feature in SQL server. This includes storing all the Audit information in a local table with respect to operations such as creating, altering, and dropping of all tables at the server level.
 
Why is Auditing essential?
Auditing is indeed required to keep track of all the changes to objects that took place within your SQL Server. It has all information such as event type, changed time, server name, login name, database name, object name and actual SQL script used to make the change.
 
How to achieve Auditing in SQL?
To capture DDL activities locally, you need to achieve the below steps,
    Creation of Trigger
    We need to create a trigger for all DDL activities such as Create, Alter and Drop operations at server level.

    Use of Event Data function
    This function helps to capture data associated with a DDL operation in an XML form which will be later used to store in a local table.

    Creation of a local Audit table
    We will be using a local table to store Audit data from the data returned by EventData function.

    Creation of Stored procedure
    A simple stored procedure is required to capture audit data into an XML parameter using EventData function and storing the same data into the local Audit table.

Detailed steps with SQL query to perform Auditing
 
First, we will create a local table named ‘AuditChanges’ to capture Audit changes into it using the below SQL script,
    CREATE TABLE [dbo].[AuditChanges]  
    (  
        [DatabaseName] [nchar](50) NULL,  
        [TableName] [nchar](50) NULL,  
        [EventType] [nchar](50) NULL,  
        [LoginName] [nchar](50) NULL,  
        [SchemaName] [nchar](50) NULL,  
        [SQLCommand] [nchar](50) NULL,  
        [CaptureTime] [nchar](50) NULL  
    )   

Now we have to create a trigger named ‘Audit_Trigger’ on server level which will store the event data as well as storing the information into AuditChanges table. Here [1] refers to the top 1 and datatype varchar means we are storing it into string.
    ALTER Trigger Audit_Trigger  
    ON ALL SERVER  
    FOR Create_Table, Alter_Table, Drop_Table  
    As Begin  
    DECLARE @EventData XML  
    SELECT @EventData = EVENTDATA()  
    INSERT INTO Test.dbo.AuditChanges  
    (DatabaseName, TableName, EventType, LoginName, SchemaName, SQLCommand, CaptureTime)  
    Values   
    (  
       @EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(50)'),  
       @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(50)'),  
       @EventData.value('(/EVENT_INSTANCE/EventType)[1]','varchar(50)'),  
       @EventData.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(50)'),  
       @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]','varchar(50)'),  
       @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]','varchar(50)'),  
       GETDATE()  
    )  
    END  

All setup is  done. Now we will be creating a table named 'Student' to verify if event data relating to its creation is getting stored in AuditChanges table or not. Below is the query to create the table student.
    CREATE TABLE [dbo].[Student]  
    (  
        [ID] [int] IDENTITY(1,1) NOT NULL,  
        [Name] [char](50) NULL  
    )  

Now as soon as the table is created, its event data will be stored in AuditChanges. Query the Audit table to get the result. As seen below, the Audit table has successfully captured the Student dbo creation details.

HostForLIFEASP.NET SQL Server 2019 Hosting

You might be asking why on earth would you want to get a database into an undesirable state, more specifically into a Recovery Pending state.  Well, in my case, I had a client database that had entered into this state due to a failure of storage.  Thankfully, this database was not being used for production, so I had some time to determine the best way to fix the issue.

A phrase that was often used during my time in the fire service, was "Try Before You Pry".  Does the front door of the house need to be kicked in?  It may be unlocked and by trying before prying (with my boot) I can prevent damage to the door.  In these types of scenarios, this philosophy holds true.  Try things out on non-critical databases will help prevent any further damage.

In this instance, I want to try it before forcing something that might be damaging.  This meant I had to get a test database into a recovering state.  Once it is in the state I need, then I can attempt different methods to recover the database properly.  Once a successful solution is determined, I can then confidently pry against the damaged database in production knowing that I am using a validated solution.

DISCLAIMER: DO NOT DO THIS ON A PRODUCTION SQL SERVER INSTANCE.

You have been warned.

DISCLAIMER: DO NOT DO THIS ON A PRODUCTION SQL SERVER INSTANCE.

You have been warned. Again.
How do you get a database into a recovery pending state?

Here is how I did this:

    Start a new transaction.
    Create a new table.
    Stop the SQL Server service
    Rename/Delete the database log file.
    Restart the SQL Server Service

The database will be in a recovery pending state upon the restart of the SQL Server service.
Why is the database in recovery pending?

When the database attempts to come back online, it will be put into a recovery pending state because the log file is not present but there was an open transaction when the service was shut down.  In normal operations, even with an open transaction, SQL Server would go through the recovery phase of the transaction log.  In the rollback phase of recovery, SQL Server would attempt to rollback and transaction that was open at the point of restart and undo the changes.  Since the log file no longer exists, it is unable to do so.

Therefore, the database is now in the recovery pending status.  It’s pending recovery because there was an open transaction, but SQL Server is unable to bring the database into a consistent state.

When this occurs, you will see something like this in the error log:

If the database is shut down cleanly and the transaction log file remove/renamed/etc., SQL Server will just rebuild the log file for you.

Summary

Sometimes it is useful to be able to put a database into a specific state of being so that you can validate solutions before attempting to perform an action in a Production environment.  Just remember to try it before prying it.  Not doing so could just make things worse so being overly cautious is not a bad thing.

HostForLIFEASP.NET SQL Server 2019 Hosting

In this blog, we will see how to use 'CROSS JOIN In SQL'. When each row of Table 1 is merged within each row of Table 2, then it's called a CROSS JOIN or Cartesian Join or in simple words, we can say that like CROSS JOIN it always returns the Cartesian product of the sets of the record from (another two or more tables) joined table.

We can write it with a comma-separated table name in order to achieve the same result or we can just write CROSS JOIN just like INNER JOIN without where clause,
SELECT TABLE1.Name, TABLE1.Age, TABLE2.ProjectName,TABLE2.DoA
FROM [EmpTable] AS TABLE1, [Projects] AS TABLE2
OR
SELECT TABLE1.Name, TABLE1.Age, TABLE2.ProjectName, TABLE2.DoA
FROM [EmpTable] AS TABLE1 CROSS JOIN [Projects] AS TABLE2

Let's consider the following two tables,
Table 1 - EmpTable

Table 2 - Projects
Now it's time to see the result. As we already know in CROSS JOIN, each row from Table 1 merged (joins/combined) with all rows of Table 2 (other tables / joined tables ) if Table 1 is having X number of rows and Table 2 having some Y number of rows then the result will be X * Y number of rows.

Now it's time to see the result. As we already know in CROSS JOIN, each row from Table 1 merged (joins/combined) with all rows of Table 2 (other tables / joined tables ) if Table 1 is having X number of rows and Table 2 having some Y number of rows then the result will be X * Y number of rows.

HostForLIFEASP.NET SQL Server 2019 Hosting

In this article, we will learn how to create a database backup scheduler in the SQL Express version as it has no built-in auto backup tool like SQL Server. With the help of Task Scheduler (Windows OS) and batch file, we will create a schedular to take the database backup from the SQL Express version in the specified folder.

Requirement

Batch File
Stored Procedure (Database Backup) which is not available in SQL Express version
Configuration on Windows Task Scheduler

Let's get started,

Batch File

Create a batch file as below,

Open notepad and paste the below code and change the parameters as per your configuration and save this file as a .bat extension.
sqlcmd -S .\SQLEXPRESS_Instance_Name -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\Backup\', @databaseName='DatabaseName', @backupType='F'"

ActionScript

In the above statement, sp_BackupDatabases is a stored procedure name that we will create in the next step.

Stored Procedure (Database Backup)

In SQL Express we have to add the below-stored procedure in the default Master database as SQL Express does not provide built-in auto back feature.

We will create the below-stored procedure in the Master database which is a default database provided by SQL Express.

Note
The below-stored procedure is written by Microsoft.

Copied from https://raw.githubusercontent.com/microsoft/mssql-support/master/sample-scripts/backup_restore/SQL_Express_Backups.sql
USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_BackupDatabases]    Script Date: 17/07/2021 11:35:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Microsoft
-- Create date: 2010-02-06
-- Description: Backup Databases for SQLExpress
-- Parameter1: databaseName
-- Parameter2: backupType F=full, D=differential, L=log
-- Parameter3: backup file location
-- =============================================
ALTER PROCEDURE [dbo].[sp_BackupDatabases]
        @databaseName sysname = null,
        @backupType CHAR(1),
        @backupLocation nvarchar(200)
AS
   SET NOCOUNT ON;
        DECLARE @DBs TABLE
        (
              ID int IDENTITY PRIMARY KEY,
              DBNAME nvarchar(500)
        )
         -- Pick out only databases which are online in case ALL databases are chosen to be backed up
         -- If specific database is chosen to be backed up only pick that out from @DBs
        INSERT INTO @DBs (DBNAME)
        SELECT Name FROM master.sys.databases
        where state=0
        AND name= ISNULL(@DatabaseName ,name)
        ORDER BY Name
        -- Filter out databases which do not need to backed up
        IF @backupType='F'
              BEGIN
              DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')
              END
        ELSE IF @backupType='D'
              BEGIN
              DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
              END
        ELSE IF @backupType='L'
              BEGIN
              DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
              END
        ELSE
              BEGIN
              RETURN
              END
        -- Declare variables
        DECLARE @BackupName varchar(100)
        DECLARE @BackupFile varchar(100)
        DECLARE @DBNAME varchar(300)
        DECLARE @sqlCommand NVARCHAR(1000)
        DECLARE @dateTime NVARCHAR(20)
        DECLARE @Loop int
        -- Loop through the databases one by one
        SELECT @Loop = min(ID) FROM @DBs
  WHILE @Loop IS NOT NULL
  BEGIN
-- Database Names have to be in [dbname] format since some have - or _ in their name
  SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'
-- Set the current date and time n yyyyhhmmss format
  SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' +  REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')
-- Create backup filename in path\filename.extension format for full,diff and log backups
  IF @backupType = 'F'
        SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'
  ELSE IF @backupType = 'D'
        SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'
  ELSE IF @backupType = 'L'
        SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'
-- Provide the backup a name for storing in the media
  IF @backupType = 'F'
        SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime
  IF @backupType = 'D'
        SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime
  IF @backupType = 'L'
        SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime
-- Generate the dynamic SQL command to be executed
   IF @backupType = 'F'
              BEGIN
           SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
              END
   IF @backupType = 'D'
              BEGIN
           SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
              END
   IF @backupType = 'L'
              BEGIN
           SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
              END
-- Execute the generated SQL command
   EXEC(@sqlCommand)
-- Goto the next database
SELECT @Loop = min(ID) FROM @DBs where ID>@Loop
END

Windows Task Scheduler Configuration
Now we will create a windows scheduler to run the batch file to run the above-stored procedure to create an automated backup which is a final step.

Open Task Scheduler as below (type task scheduler from the search bar of windows 10 and press enter).

Create a new task as below,

We will give the name to our scheduler task and select the appropriate option as below. We need to run this scheduler all time so we select this selected option shown below screen.

Now we will set our scheduler - when to run this task using the Trigger tab as below screen and then click on the new button.

Now will create a new trigger to tell the system when to run this task as below. Please configure it as per your requirement.

Once the trigger is set we will now configure the action tab and then click the new button to setup our above-created batch file. We will call our batch file with the help of the action tab as below,

Click on the new button to set the path of our batch file as below,

Once we browse and select the created batch file then click the ok button. And then click on the condition tab to configure it. We will keep default settings as below,

And now final configuration, click on the settings tab as below. Again we will keep default settings as below,

Click the ok button will create your task and will run this task as per your configuration. Note: It will ask to enter windows credentials which have admin rights if you do not have enough rights to create such a task.

Once all set, the scheduler will run the task to call the batch file and that batch file will call the stored procedure to get the backup of mentioned database in the batch file.

Note
Please make sure that the information you mention in the batch is correct.
Happy Coding!!

HostForLIFEASP.NET SQL Server 2019 Hosting

In this article, we will learn how to get all the column information of a table with one click, including Column Name, DataType, Data Length, Column Description, etc. I found this technique very useful when I had to share the table column information along with some sample data into an Excel sheet with other clients.

Sample Script
In this script, we need to pass only the table name and schema name.

set @TableSchema ='dbo' --Schema name table 'Item' set @TableName= 'Item' -- Name of table.
declare @TableSchema varchar(50)
declare @TableName varchar(50)

set @TableSchema ='dbo' --Edit as per your table schema
set @TableName= 'Item' --Edit as per your table name

select @TableSchema +'.' +@TableName TableName

SELECT ORDINAL_POSITION AS [SNo],

COLUMN_NAME AS [Column Name],

DATA_TYPE AS [Data Type],

CASE
    WHEN DATA_TYPE ='decimal'  THEN  '(' + convert(varchar(20),NUMERIC_PRECISION) + ','  + convert(varchar(20),NUMERIC_SCALE) + ')'
    WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN ''
    WHEN CHARACTER_MAXIMUM_LENGTH ='-1' then 'MAX'
    ELSE CONVERT(varchar(50), CHARACTER_MAXIMUM_LENGTH)
End  AS [Length],

CASE WHEN COL.IS_NULLABLE = 'No' THEN 'Y' ELSE 'Yes' End [Is Mandatory],

ISNULL(prop.value,'') [Description]

FROM INFORMATION_SCHEMA.TABLES AS tbl
INNER JOIN INFORMATION_SCHEMA.COLUMNS AS col
ON col.TABLE_NAME = tbl.TABLE_NAME
AND tbl.TABLE_SCHEMA=col.TABLE_SCHEMA
AND tbl.table_schema=@TableSchema
AND tbl.TABLE_NAME = @TableName
INNER JOIN sys.columns AS sc
ON sc.object_id = object_id(tbl.table_schema + '.' + tbl.table_name)
AND sc.NAME = col.COLUMN_NAME
AND tbl.TABLE_SCHEMA=@TableSchema
AND tbl.TABLE_NAME = @TableName
LEFT JOIN sys.extended_properties AS prop
ON prop.major_id = sc.object_id
AND prop.minor_id = sc.column_id
AND prop.NAME = 'MS_Description'
WHERE tbl.table_schema=@TableSchema and tbl.TABLE_NAME = @TableName
order by ORDINAL_POSITION

When you execute this script, it shows the column information like the following screenshot,

How to Prepare Excel Sheet With Sample Data

Step 1
Copy this result data with Headers information. Right click on the first cell -> click on the menu [Copy with Headers] and paste it into your excel sheet.

Step 2
Now, we have to paste few sample data into this excel sheet. For this, I will execute the following script.
Select top 2 * From dbo.Item  --

Step 3
Now copy the result and data without header columns.

Step 4
Now paste this copied data into another excel sheet.

Step 5
Now again copy this data from the excel sheet where you have pasted it.

Step 6
Now transpose this data into the first sheet where you have pasted column information.

Note
In Excel paste options, you can find this transpose option which will paste your records in column format.

Finally, it will look like the following screenshot,

HostForLIFEASP.NET SQL Server 2019 Hosting

A SQL trigger is a database object which fires when an event occurs in a database. We can execute a SQL query that will "do something" in a database when a change occurs on a database table such as a record is inserted or updated or deleted. For example, a trigger can be set on a record insert in a database table. For example, if you want to increase the count of blogs in the Reports table when a new record is inserted in the Blogs table, we can create a trigger on the Blogs' table on INSERT and update the Reports table by increasing blog count to 1.

Types of Triggers
There are two types of triggers:
    DDL Trigger
    DML Trigger

DDL Triggers
The DDL triggers are fired in response to DDL (Data Definition Language) command events that start with Create, Alter and Drop, such as Create_table, Create_view, drop_table, Drop_view and Alter_table.

Code of a DDL Trigger
create trigger saftey
on database
for
create_table,alter_table,drop_table
as
print'you can not create ,drop and alter table in this database'
rollback;

When we create, alter or drop any table in a database then the following message appears:

DML Triggers
The DML triggers are fired in response to DML (Data Manipulation Language) command events that start with Insert, Update, and Delete. Like insert_table, Update_view and Delete_table.

create trigger deep
on emp
for
insert,update,delete
as
print'you can not insert,update and delete this table i'
rollback;

When we insert, update or delete in a table in a database then the following message appears,

There are two types of DML triggers
 
AFTER Triggers
AFTER triggers are executed after the action of an INSERT, UPDATE, or DELETE statement.

create trigger insertt
on emp
after insert
as
begin
insert into empstatus values('active')
end

INSTEAD Of Triggers
It will tell the database engine to execute the trigger instead of executing the statement. For example an insert trigger executes when an event occurs instead of the statement that would insert the values in the table .
CREATE TRIGGER instoftr
ON v11
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO emp
SELECT I.id, I.names
FROM INSERTED I

INSERT INTO emp1values
SELECT I.id1, I.name1
FROM INSERTED I
END

When we insert data into a view by the following query then it inserts values in both tables :
insert into v11 values(1,'d','dd')

You can see both tables by the folowing query:
select * from emp
select * from emp1values

In this article, I described triggers in SQL Server. I hope this article has helped you in understanding this topic. Please share it. If you know more about this, your feedback and constructive contributions are welcome.

Continue learning more, Trigger in SQL

In this blog, we will create a PowerShell script that inserts the data into a SQL table. In this scenario, we have one SQL table of student details. We insert the Student name, standard, and division into the ‘Student’ table. Here we have the ‘Student’ table in SQL Server. We have two different columns ‘Name’, ‘STD’. We will enter the 5 static details of students.

Let’s get started!
We will define the variables. We have 5 variables ‘serverName’, ‘databaseName’, ‘tableName’, ‘studentName’ and ‘standard’. 

$serverName = "HostForLIFE"
$databaseName = "StudentDetails"
$tableName = "dbo.Student"
$studentName = 'John','Debo','Carry','Mini'
$standard = '5'

We will establish a new connection for the SQL Server database using the below code. We will use the ‘ServerName’ and ‘databaseName’ for establishing the connection string.
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server='$serverName';database='$databaseName';trusted_connection=true;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection

We will apply for each student’s name and execute the command for inset into the SQL table. Here we use into query command and execute the command. This query will insert the employee name and standard field in the student table.

foreach($Name in $studentName){
  $insertquery="
  INSERT INTO $tableName
      ([Name],[STD])
    VALUES
      ('$Name','$standard')"
  $Command.CommandText = $insertquery
  $Command.ExecuteNonQuery()
}

Close the connection of SQL.
$Connection.Close();

Here is the whole code for inserting the data into the table.
$serverName = "HostForLIFE"
$databaseName = "StudentDetails"
$tableName = "dbo.Student"
$studentName = 'John','Debo','Carry','Mini'
$standard = '5'
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server='$serverName';database='$databaseName';trusted_connection=true;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
foreach($Name in $studentName){
  $insertquery="
  INSERT INTO $tableName
      ([Name],[STD])
    VALUES
      ('$Name','$standard')"
  $Command.CommandText = $insertquery
  $Command.ExecuteNonQuery()
}
$Connection.Close();

Output

HostForLIFEASP.NET SQL Server 2019 Hosting

In this article, we will learn about the important SQL commands with explanation which are commonly used in every project. Let's start with these commands,
DDL Commands
 
CREATE TABLE
Using CREATE TABLE you can create a new table in the database. You can set the table name and column name in the table.
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

SQL
ALTER TABLE

The ALTER TABLE is used to add, delete, or modify columns in a table.

ALTER TABLE table_name
ADD column_name datatype;

SQL
DROP Table

The DROP TABLE is used to drop an existing table in a database.
DROP TABLE table_name;

SQL
TRUNCATE Table

The SQL TRUNCATE TABLE command is used to delete complete data from an existing table.

TRUNCATE TABLE table_name;

SQL
COMMENT

When we do not want our code to be executed we comment them.

There are three types of comment in SQL,
    Single line comments.
    Multi-line comments
    Inline comments

Single line comments
-- single line comment example
SELECT * FROM employees;

Lua
Multi-line comments
/* multi line comment line 1
line 2 */
SELECT * FROM employees;

SQL
Inline comments
SELECT * FROM /* employees; */

SQL
DML Commands
 
SELECT
SELECT statements are used to fetch data from a database. Every query will begin with SELECT.

SELECT column_name
FROM table_name;

SQL
INSERT
INSERT statements are used to add a new row to a table.

INSERT INTO table_name (column_1, column_2, column_3)
VALUES (value_1, 'value_2', value_3);

SQL
UPDATE

UPDATE statements allow you to edit rows in a table.

UPDATE table_name
SET column_name = new_value
WHERE column_name = old_value;

SQL
DELETE
DELETE statements are used to remove rows from a table.
DELETE FROM table_name
WHERE column_name = column_value;

SQL
DCL Commands
GRANT
GRANT used to provide access or privileges on the database objects to the users.
GRANT privileges ON object TO user;

SQL
REVOKE
REVOKE command removes user access rights or privileges to the database objects.
REVOKE privileges ON object FROM user;

SQL
TCL Commands
COMMIT

COMMIT is used to save changes by a transaction to the database.

COMMIT;

SQL
ROLLBACK
The ROLLBACK command is used to undo transactions that have not saved to the database. The command is only be used to undo changes since the last COMMIT.
ROLLBACK;

SQL
SAVEPOINT
SAVEPOINT command is used to temporarily save a transaction to a point so that you can rollback to that point whenever required.

SAVEPOINT SAVEPOINT_NAME;

SQL
Other Useful Commands
 
AND

And is used to combines two conditions. Both conditions must be true to display the record.

SELECT column1, column2
FROM table_name
WHERE condition1 AND condition2;

SQL
AS
With the help of AS you can rename a column or table using an alias.

SELECT column_name AS 'Alias_Example'
FROM table_name;

SQL
AVG()
AVG() function returns the average value of a numeric column.

SELECT AVG(column_name)
FROM table_name;

SQL
BETWEEN
BETWEEN operator selects values (values can be numbers, text, or dates) within a given range.

SELECT column1
FROM table_name
WHERE column1 BETWEEN value1 AND value2;

SQL
CASE
In SQL we are using CASE like an if-then-else statement.

SELECT column_name,
  CASE
    WHEN condition THEN 'Result1'
    WHEN condition THEN 'Result2'
    ELSE 'Result3'
  END
FROM table_name;

SQL
COUNT()
The COUNT() function returns the number of rows in a column.
SELECT COUNT(column_name)
FROM table_name;

SQL
GROUP BY
The GROUP BY statement groups rows that shows the identical data into groups.

SELECT column_name1, column_name2
FROM table_name
GROUP BY column_name1;

SQL
HAVING
WHERE keyword cannot be used with aggregate functions that's why the HAVING clause was added to SQL.

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition;

SQL
INNER JOIN
An inner join will combine rows that have matching values in both tables.
SELECT column_name
FROM table1
INNER JOIN table2
On table1.column_name = table2.column_name;

SQL
IS NULL / IS NOT NULL
We used IS NULL and IS NOT NULL with the WHERE clause to test the value empty or not.

IS NULL Syntax,

SELECT column_name
FROM table_name
WHERE column_name IS NULL;

SQL
IS NOT NULL Syntax,

SELECT column_name
FROM table_name
WHERE column_name IS NOT NULL;

SQL
LIKE
LIKE is a special operator used to search for a specific pattern in a column with the WHERE clause.

SELECT column_name
FROM table_name
WHERE column_name LIKE pattern;

SQL
MAX()

MAX() is a function that returns the largest value in that column.

SELECT MAX(column_name)
FROM table_name;

SQL
MIN()

MIN() is a function that returns the smallest value in that column.

SELECT MIN(column_name)
FROM table_name;

SQL
OR
The OR operator displays the result where one of two conditions is true.

SELECT column1, column2
FROM table_name
WHERE condition1 OR condition2;

SQL
ORDER BY
ORDER BY is used to sort the result in ascending or descending order. By default, it sorts the records in ascending order for descending order, we will use the DESC keyword.
SELECT column_name
FROM table_name
ORDER BY column_name ASC | DESC;

SQL
ROUND()

The ROUND function returns a number rounded to a certain number of decimal places.
SELECT ROUND(column_name, integer)
FROM table_name;

SQL
SELECT DISTINCT

The SELECT DISTINCT statement is used to returns unique values in the specified column(s).
SELECT DISTINCT column_name
FROM table_name;

SQL
SUM

The SUM() function returns the total sum of a column.

SELECT SUM(column_name)
FROM table_name;

SQL
WHERE

WHERE clause is used to filter records.

SELECT column1, column2
FROM table_name
WHERE condition;

HostForLIFEASP.NET SQL Server 2019 Hosting

In this blog, we will discuss how to work with GROUP BY, WHERE, and HAVING clauses in SQL and explain the concept with an example in a simple way. I hope this is very useful for beginners and intermediates to help them understand the basic concept.
 
Group by clause
The Group by clause is often used to arrange identical duplicate data into groups with a select statement to group the result-set by one or more columns. This clause works with the select specific list of items, and we can use HAVING, and ORDER BY clauses. Group by clause always works with an aggregate function like MAX, MIN, SUM, AVG, COUNT.
 
Let us discuss group by clause with an example. We have a VehicleProduction table and there are some models with a price and it has some duplicate data. We want to categorize this data in a different group with a respective total price.
 
Example

    Create table VehicleProduction    
    (    
    Id int primary key Identity,     
    Model varchar(50),    
    Price money    
    )    
        
    Insert into VehicleProduction values('L551', 850000),('L551', 850000),('L551', 850000),('L551', 750000),    
    ('L538', 650000),('L538', 650000),('L538', 550000),('L530', 450000),('L530',350000), ('L545', 250000)    
        
    Select * from VehicleProduction    

Output

Aggregate Functions
MAX()- function returns the maximum value of the numeric column of specified criteria.
 
Example
    Select max(Price) As 'MaximumCostOfModel' from VehicleProduction    

Output

MIN()- function returns the minimum of the numeric column of specified criteria.
 
Example
    Select Min(Price) As 'MinimumCostOfModel' from VehicleProduction    

Output

MIN()- function returns the minimum of the numeric column of specified criteria.
 
Example
    Select Min(Price) As 'MinimumCostOfModel' from VehicleProduction    

Output

SUM()- function returns the total sum of a numeric column of specified criteria.
 
Example
    Select SUM(Price) As 'SumCostOfAllModel' from VehicleProduction    

Output

AVG()- function returns the average value of a numeric column of specified criteria.
 
Example
    Select AVG(Price) As 'AverageCostOfModel' from VehicleProduction    

Output

COUNT()- function returns the number of rows that match specified criteria.
 
Example
    Select Count(Price) As 'TotalVehicleModels' from VehicleProduction    

Output

Distinct clause
The distinct clause is used to filter unique records out of the duplicate records that satisfy the query criteria.
 
Example
    Select Distinct(Model),  Price from VehicleProduction    

Output

Group by clause
The Group by clause is often used to arrange the identical duplicate data into groups with the select statement. This clause works with the select specific list of items, for that we can use HAVING, and ORDER BY clauses.
 
Syntax
    SELECT Column1, Column2    
    FROM TableName    
    GROUP BY Column1, Column2    

Example
    Select * from VehicleProduction     
     
    Select Model, Price from VehicleProduction     
    group by Model, Price    

Output

Let’s look at an example of a GROUP BY with aggregate functions.
 
GROUP BY with aggregate functions
Example
    Select Model, Price, Count(*) As QtyOfModel, Sum(Price) As TotPriceOfModel  from VehicleProduction     
    group by Model, Price  

Output

Where clause
Where clause works with select clause but won’t work on the group by or aggregate function condition.
 
Example 1
    Select Model, Price from VehicleProduction     
    where Model != 'L530'    
    group by Model, Price  

Output

Example 2
We can’t use where clause after group by clause

    Select Model, Price from VehicleProduction     
    group by Model, Price     
    where Model != 'L530'    

Output

Having clause
 
Having clause works with a group by clause but specifically works on aggregate function condition.
 
Example
    Select Model, Price from VehicleProduction     
    Group by Model, Price     
    Having SUM(Price)  > 600000.00   

Output

ORDER BY clause
Order By clause shows the records in ascending or descending order of the specific condition.
 
Example
    Select Model, Price from VehicleProduction     
    Group by Model, Price     
    Having SUM(Price)  > 400000.00     
    order by Price desc    

Output