Advertisements

Archive

Archive for the ‘SQL Basics’ Category

SQL Basics – Difference between WHERE, GROUP BY and HAVING clause

May 23, 2015 3 comments

All these three Clauses are a part/extensions of a SQL Query, are used to Filter, Group & re-Filter rows returned by a Query respectively, and are optional. Being Optional they play very crucial role while Querying a database.

–> Here is the logical sequence of execution of these clauses:

1. WHERE clause specifies search conditions for the rows returned by the Query and limits rows to a meaningful set.

2. GROUP BY clause works on the rows returned by the previous step #1. This clause summaries identical rows into a single/distinct group and returns a single row with the summary for each group, by using appropriate Aggregate function in the SELECT list, like COUNT(), SUM(), MIN(), MAX(), AVG(), etc.

3. HAVING clause works as a Filter on top of the Grouped rows returned by the previous step #2. This clause cannot be replaced by a WHERE clause and vice-versa.

As these clauses are optional thus a minimal SQL Query looks like this:

SELECT *
FROM [Sales].[SalesOrderHeader]

This Query returns around 32k (thousand) rows form SalesOrderHeader table. Thus, if somebody wants to do some analysis on this big row-set it would be very difficult and time consuming for him.

–> Use Case: Let’s say a Sales department wants to get a list of such Customers who bought more number of items last year, so that they can sell more some stuff to them this year. How they will go ahead?

1. Using WHERE clause: First of all they will need to apply filter on above ~32k rows and get list of Orders that were made last year (i.e. in 2014) to limit the row-set, like:

SELECT *
FROM [Sales].[SalesOrderHeader]
WHERE OrderDate >= '2014-01-01 00:00:00.000'
AND OrderDate < '2015-01-01 00:00:00.000'

This Query still gives ~12k records and its still difficult to identify such Customers who have more orders.

2. Using GROUP BY clause: Here we need to group the Customers with their number of Orders, like:

SELECT CustomerID, COUNT(*) AS OrderNos
FROM [Sales].[SalesOrderHeader]
WHERE OrderDate >= '2014-01-01 00:00:00.000'
AND OrderDate < '2015-01-01 00:00:00.000'
GROUP BY CustomerID

GROUP_BY clause
This query still returns ~10k records, and I’ve go through the entire list of records to identify such records. Is there any way where I can still filter out the unwanted records with lesser count?

3. USING HAVING clause: This will works on top of GROUP BY clause to filter the grouped records onCOUNT(*) AS OrderNos column values (like a WHERE clause), like:

SELECT CustomerID, COUNT(*) AS OrderNos
FROM [Sales].[SalesOrderHeader]
WHERE OrderDate >= '2014-01-01 00:00:00.000'
AND OrderDate < '2015-01-01 00:00:00.000'
GROUP BY CustomerID
HAVING COUNT(*) > 10

HAVING clause

Thus, by using all these these clauses we can reduce and narrow down the row-set to do some quick analysis.

Check this video tutorial on WHERE clause and difference with GROUP BY & HAVING clause.


Advertisements

SQL Basics – What are Row Constructors?

February 16, 2015 1 comment

Constructors, as the name suggests means to create an instance of an Object in any Object Oriented Programming language.
 

Here in SQL Server or T-SQL, ROW Constructor or Table Value Constructor means to create a row set by using the VALUES() clause. This allows multiple rows of data to be specified in a single DML statement. And this VALUES() clause can be used with the SELECT, INSERT and MERGE statements.
 

In the examples below we will see how they can be used and are helpful at times:
 

Usage #1. You can create a simple set of rows with a SELECT FROM statement:

SELECT * 
FROM (
VALUES
	(1, 'cust 1', '(111) 111-1111', 'address 1'),
	(2, 'cust 2', '(222) 222-2222', 'address 2'),
	(3, 'cust 3', '(333) 333-3333', 'address 3'),
	(4, 'cust 4', '(444) 444-4444', 'address 4'),
	(5, 'cust 5', '(555) 555-5555', 'address 5')
) AS C (CustID, CustName, phone, addr);

 

Usage #2. You can use it with INSERT statement while inserting rows in a table:

CREATE TABLE dbo.Customer (
	CustID		INT, 
	CustName VARCHAR(100), 
	phone		VARCHAR(20), 
	addr		VARCHAR(500)
)

INSERT INTO dbo.Customer (CustID, CustName, phone, addr)
VALUES
(1, 'cust 1', '(111) 111-1111', 'address 1'),
(2, 'cust 2', '(222) 222-2222', 'address 2'),
(3, 'cust 3', '(333) 333-3333', 'address 3'),
(4, 'cust 4', '(444) 444-4444', 'address 4'),
(5, 'cust 5', '(555) 555-5555', 'address 5');

 

Usage #3. You can create mixed row-sets from manually entered values and from other tables:

SELECT * 
FROM (
VALUES
	(1, 'cust 1', '(111) 111-1111', 'address 1'),
	(2, 'cust 2', '(222) 222-2222', 'address 2'),
	(3, 'cust 3', '(333) 333-3333', 'address 3'),
	(4, 'cust 4', '(444) 444-4444', 'address 4'),
	((SELECT CustID FROM dbo.Customer WHERE CustID IN (5)),
	 (SELECT CustName FROM dbo.Customer WHERE CustID IN (5)),
	 (SELECT phone FROM dbo.Customer WHERE CustID IN (5)),
	 (SELECT addr FROM dbo.Customer WHERE CustID IN (5))
	)
) AS C (CustID, CustName, phone, addr);

 

Usage #4. You can use them with JOINS, without need to create #Temp-Table or Table-Variable to store temporary data:

SELECT 
	C.CustName, 
	O.ProductName
FROM dbo.Customer C
LEFT JOIN (
VALUES 
	(101, 1, 'Apple'), 
	(102, 3, 'Orange'),
	(103, 5, 'Banana')
) AS O (OrderID ,CustID, ProductName)
ON O.CustID = C.CustID

Row Constructor 01
 

Usage #5. You can use them with MERGE statement, again without need to create #Temp-Table or Table-Variable to store temporary data:

MERGE INTO dbo.Customer as Target
USING (
VALUES
	(5, 'cust 5', '(555) 555-5555', 'address 5 updated'),
	(6, 'cust 6', '(666) 666-6666', 'address 6')
) AS Source (CustID, CustName, phone, addr)
ON Target.CustID = Source.CustID
WHEN MATCHED THEN UPDATE SET 
	Target.CustName = Source.CustName, 
	Target.phone = Source.phone, 
	Target.addr = Source.addr
WHEN NOT MATCHED BY Target THEN
INSERT (
	CustID, 
	CustName, 
	phone, 
	addr
) 
VALUES (
	Source.CustID, 
	Source.CustName, 
	Source.phone, 
	Source.addr
);

select * from  dbo.Customer;

Row Constructor 02
 

Thus, Row Constructors or Table Value Constructors are very handy when dealing with fixed set of row sets used for temporary purpose, without need of creating and storing them in #Temp-Tables or Table-Variables.
 

–> Final Cleanup

DROP TABLE dbo.Customer

SQL DBA – Move user Database (.mdf & .ldf files) to another drive

December 17, 2012 2 comments

 
Here in this post we will see how to move user created Database to an another drive from the default drive.
 

–> The first 2 ways I’ve discussed in my other blog posts, please check the links below:

1. Detach & Attach task, video

2. Backup & Restore task, link, video
 

3. By using ALTER command, Syntax below:

ALTER DATABASE SET OFFLINE;
GO

— Manually move the file(s) to the new location.

ALTER DATABASE Database name
MODIFY FILE (
NAME = ‘Database name’,
FILENAME = ‘New Location’
)
GO

ALTER DATABASE Database name SET ONLINE;
GO

 


 

–> Script used in above video:

Step #1. Create a new Database in SSMS with name “ManDB”.

Step #2. Check the current location of database:

select * from ManDB.sys.database_files

--ManDB		D:\Program Files (x86)\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\ManDB.mdf
--ManDB_log	D:\Program Files (x86)\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\ManDB_log.ldf

Step #3. Take the database offline, so that we can move files from one drive to another:

ALTER DATABASE ManDB SET OFFLINE;
GO

Step #4. Move the file(s) to the new location manually.

Step #5. Change the system catalog settings:

ALTER DATABASE ManDB
MODIFY FILE (
	NAME = 'ManDB',
	FILENAME = 'E:\SQLDBs\ManDB.mdf' 
)
GO

ALTER DATABASE ManDB
MODIFY FILE (
	NAME = 'ManDB_log',
	FILENAME = 'E:\SQLDBs\ManDB_log.ldf' 
)
GO

Step #6. Take the database back online:

ALTER DATABASE ManDB SET ONLINE;
GO

Step #7. Check the new location:

select * from ManDB.sys.database_files

--ManDB		E:\SQLDBs\ManDB.mdf
--ManDB_log	E:\SQLDBs\ManDB_log.ldf

SQL Basics – Working with VIEWs in SQL Server

December 2, 2011 1 comment

 
Views in SQL Server and other RDBMSs are simply Virtual Tables or Stored Queries. Views are like tables, contains rows and columns, but do not store any data within. Instead they use the Query that is defined to create the view to show data from one or more tables.
 

–> A view can be used for:

1. Simplifying a complex query, give it a simple name, and use it like a table.

2. Providing security by restricting the table access and showing only selected Columns and Rows via Views.

3. Providing compatibility with other client systems that cannot be changed.
 

–> Now let’s see how Views helps with above points:

Point #1: Consider Query below, it will be difficult and time consuming to write the same query every time. Also due to human error at times you may not be able to get same or desired results, because you can commit mistake or forget something while writing the same logic.

USE [AdventureWorks2014]
GO

SELECT 
	 P.BusinessEntityID AS EmpID
	,P.Title
	,CONCAT(P.FirstName, ' ', P.MiddleName, ' ', P.LastName) AS EmployeeName
	,P.Suffix
	,E.BirthDate
	,CASE 
		WHEN E.Gender = 'M' THEN 'Male' 
		ELSE 'Female' 
	 END as Gender
	,IIF(E.MaritalStatus = 'S', 'Single', 'Married') as MaritalStatus
FROM Person.Person P
JOIN [HumanResources].[Employee] E
ON E.BusinessEntityID = P.BusinessEntityID

views-01

… here we have Joined 2 tables and selected only required columns. You will also notice that we have changed the column names (alias), and created calculated columns, like EmployeeName, Gender & MaritalStatus.

So rather than writing this query every time, its better to store this query as a View, like below:

CREATE VIEW dbo.vwPersonEmployee
AS
SELECT 
	 P.BusinessEntityID AS EmpID
	,P.Title
	,CONCAT(P.FirstName, ' ', P.MiddleName, ' ', P.LastName) AS EmployeeName
	,P.Suffix
	,E.BirthDate
	,CASE 
		WHEN E.Gender = 'M' THEN 'Male' 
		ELSE 'Female' 
	 END as Gender
	,IIF(E.MaritalStatus = 'S', 'Single', 'Married') as MaritalStatus
FROM Person.Person P
JOIN [HumanResources].[Employee] E
ON E.BusinessEntityID = P.BusinessEntityID
GO

… and simply execute the view instead of the query now onwards, like:

SELECT * FROM dbo.vwPersonEmployee

 

Point #2: The above View uses 2 tables Person.Person & HumanResources.Employee. Now if you want a user to have restricted access to these 2 tables, but also want the user to query View to get desired and restricted data, then you can GRANT access only to the View, like:

CREATE USER userView WITHOUT LOGIN;

GRANT SELECT ON dbo.vwPersonEmployee TO userView;
GO

EXECUTE AS USER = 'userView';

SELECT * FROM dbo.vwPersonEmployee 	-- Displays View data

SELECT * FROM Person.Person				-- ERROR: The SELECT permission was denied on the object
SELECT * FROM HumanResources.Employee	-- ERROR: The SELECT permission was denied on the object

REVERT;
GO

DROP USER userView
GO

… here when the user executes the View he can see the data, but with only selected columns. But if he tries to use tables, he will get error.
 

Point #3: Now let’s say a client application which was getting data from an old table, let’s say dbo.Person, with following columns: PersonID, PersonFirstName, PersonLastName. Now on the new DB system the table is replaced by a new table Person.Person with different column names. This will make the system unusable and unstable.

But with the use of Views we can fill the gap, by creating a new View with name dbo.Person on top of Person.Person, and aliasing new columns with old column names, like:

CREATE VIEW dbo.Person
AS
SELECT
	 BusinessEntityID as PersonID
	,FirstName as PersonFirstName
	,LastName as PersonLastName
FROM Person.Person
GO

… so by this way the client application can talk to the new table by hitting the View instead of the Table.
 

–> Dropping/Deleting Views:

DROP VIEW dbo.vwPersonEmployee
DROP VIEW dbo.Person

 


SQL Basics – Working with NOT NULL, DEFAULT and CHECK Constraints (Domain Integrity)

April 11, 2011 Leave a comment

In my previous posts I discussed about [Entity Integrity] constraint which deals with Primary Keys & Unique Keys, and [Referential Integrity] constraint, which deals with Foreign Keys, please check the above links to know about them.
 

Here in this post I’ll discuss about Domain Integrity which validates the entries for a given column in a particular table. The Domain integrity can be enforced by:

1. Applying appropriate DATATYPES
 

2. NOT NULL constraint: enforces a column to NOT accept NULL values. Which means the columns must always contain a value and you cannot insert or update a row without specifying a value for that column.

For example: Employee name is mandatory while inserting an employee’s record, thus you can have [EmployeeName] column with NOT NULL constraint, like: [EmployeeName] nvarchar(100) NOT NULL
 

3. DEFAULT constraint: can be used to Insert a default value into a column, if no other value is provided while Inserting a row. This constraint works only with INSERT statement.

For example: To track the rows insert timestamp you can add this constraint to that column, like: [CreatedOn] datetime DEFAULT (getdate())
 

4. CHECK constraint: enforces a column to have only limited and pre-defined values. Means you cannot insert or update a row with values that are not defined with this constraint.

For example: An Employee gender could only be Male or Female, so you can have [Gender] column with CHECK constraint, like: [Gender] nchar(1) CHECK ([Gender] IN (‘M’, ‘F’))
 

–> Check the video on how to work with these three constraints (NOT NULL, CHECK and DEFAULT):

Constraints NOT NULL, DEFAULT, CHECK

–> SQL Code used in above video:


--// Domain Integrity Constraints:


-- 1. NOT NULL Constraint:

CREATE TABLE [dbo].[Employee](
	 [EmployeeID]	int IDENTITY (100, 1) PRIMARY KEY
	,[EmployeeName]	nvarchar(100) NOT NULL
	,[Gender]		nchar(1)
)
GO

insert into [Employee] ([EmployeeName], [Gender])
values (NULL, NULL)

/*
Msg 515, Level 16, State 2, Line 13
Cannot insert the value NULL into column 'EmployeeName', table 'TestManDB.dbo.Employee'; column does not allow nulls. INSERT fails.
The statement has been terminated.
*/

select * from [Employee]
GO



-- 2. DEFAULT Constraint:

DROP TABLE [dbo].[Employee]
GO

CREATE TABLE [dbo].[Employee](
	 [EmployeeID]	int IDENTITY (100, 1) PRIMARY KEY
	,[EmployeeName]	nvarchar(100) NOT NULL
	,[Gender]		nchar(1)
	,[isActive]		bit				DEFAULT (1)
	,[CreatedOn]	datetime		DEFAULT (getdate())
	,[CreatedBy]	varchar(100)	DEFAULT (SYSTEM_USER)
)
GO

insert into [Employee] ([EmployeeName], [Gender])
values ('Manoj Pandey', 'M')

insert into [Employee] ([EmployeeName], [Gender])
values ('Kanchan Pandey', 'F')

insert into [Employee] ([EmployeeName], [Gender], [isActive])
values ('Maria Y', 'F', 0)

insert into [Employee] ([EmployeeName], [Gender], [CreatedOn], [CreatedBy])
values ('Brock H', 'M', '2015-01-01 21:32:07.153', 'scott')

select * from [Employee]
GO



-- 3. CHECK Constraint:

DROP TABLE [dbo].[Employee]
GO

CREATE TABLE [dbo].[Employee](
	 [EmployeeID]	int IDENTITY (100, 1) PRIMARY KEY
	,[EmployeeName]	nvarchar(100) NOT NULL
	,[Gender]		nchar(1)		CONSTRAINT ck_emp_gender CHECK ([Gender] IN ('M', 'F'))
	,[isActive]		bit				CONSTRAINT ck_emp_isActive DEFAULT (1)
	,[CreatedOn]	datetime		CONSTRAINT ck_emp_CreatedOn DEFAULT (getdate())
	,[CreatedBy]	varchar(100)	CONSTRAINT ck_emp_CreatedBy DEFAULT (SYSTEM_USER)
)
GO

insert into [Employee] ([EmployeeName], [Gender])
values ('Manoj Pandey', 'X')

/*
Msg 547, Level 16, State 0, Line 67
The INSERT statement conflicted with the CHECK constraint "CK__Employee__Gender__4D5F7D71". The conflict occurred in database "TestManDB", table "dbo.Employee", column 'Gender'.
The statement has been terminated.
*/

insert into [Employee] ([EmployeeName], [Gender])
values ('Manoj Pandey', 'M')

select * from [Employee]
GO




--// Table Generated from SSMS - Object Explorer

USE [TestManDB]
GO

ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [ck_emp_gender]
GO

ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [ck_emp_CreatedBy]
GO

ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [ck_emp_CreatedOn]
GO

ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [ck_emp_isActive]
GO

/****** Object:  Table [dbo].[Employee]    Script Date: 3/30/2016 9:45:17 PM ******/
DROP TABLE [dbo].[Employee]
GO

/****** Object:  Table [dbo].[Employee]    Script Date: 3/30/2016 9:45:17 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Employee](
	[EmployeeID] [int] IDENTITY(100,1) NOT NULL,
	[EmployeeName] [nvarchar](100) NOT NULL,
	[Gender] [nchar](1) NULL,
	[isActive] [bit] NULL,
	[CreatedOn] [datetime] NULL,
	[CreatedBy] [varchar](100) NULL,
PRIMARY KEY CLUSTERED 
(
	[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Employee] ADD  CONSTRAINT [ck_emp_isActive]  DEFAULT ((1)) FOR [isActive]
GO

ALTER TABLE [dbo].[Employee] ADD  CONSTRAINT [ck_emp_CreatedOn]  DEFAULT (getdate()) FOR [CreatedOn]
GO

ALTER TABLE [dbo].[Employee] ADD  CONSTRAINT [ck_emp_CreatedBy]  DEFAULT (suser_sname()) FOR [CreatedBy]
GO

ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [ck_emp_gender] CHECK  (([Gender]='F' OR [Gender]='M'))
GO

ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [ck_emp_gender]
GO



-- Final Cleanup:

DROP TABLE [dbo].[Employee]
GO

To know about more on Constraints and their types check this blog post.