Following are some handy SQL queries which will be useful in your day to day testing activities. These queries are taken from MS book for TSQL - "Querying Microsoft SQL Server 2012". I haven't explained much here as the syntax are mostly self explanatory. If required you may drop your queries which I'll try to answer.
-- Logical Query Processing: Logically the following query is processed as FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY
SELECT country, YEAR(hiredate) AS yearhired, Count(*) AS [count]
FROM HR.Employees
WHERE hiredate >= '20030101'
GROUP BY country, Year(hiredate)
HAVING Count(*) > 1
ORDER BY country, yearhired;
--Declare
Variable
DECLARE @f AS INT = 10;
SELECT @f AS value;
-- Date/Time Important Type & Functions
SELECT CAST(SYSDATETIME() AS DATE)AS today;
SELECT CAST(SYSDATETIME() AS TIME)AS timenow;
SELECT DATENAME(Month,SYSDATETIME()) AS thisMonth;
SELECT DATENAME(DAY,SYSDATETIME()) AS thisDay;
SELECT DATENAME(HOUR,SYSDATETIME()) AS thisHour;
--Concatenation:
NULL in any value will return NULL
SELECT empid, country, region, city,
country + N',' + region + N',' + city AS location
FROM HR.Employees;
--Concatenation:
Replace NULL region with space
SELECT empid, country, region, city,
country + COALESCE(N',' + region, N' ') + N',' + city AS location
FROM HR.Employees;
-- ISNULL: Only Diff
with COALESCE - Type of return is determined by the first input
SELECT empid, country, region, city,
country + ISNULL(N',' + region, N' ') + N',' + city AS location
FROM HR.Employees;
--Other Important
Functions:
SELECT SUBSTRING('ABCDE',1,3) AS string; --
Returns ‘ABC’
SELECT SUBSTRING('ABCDE',1,50) AS string; --
Returns ‘ABCDE’
SELECT LEFT('ABCDE',3); -- Returns ‘ABC’
SELECT RIGHT('ABCDE',3); -- Returns ‘CDE’
SELECT CHARINDEX(' ', 'Itzik Ben-Gan'); --
Returns 6
SELECT PATINDEX ('%[0-9]%', 'abcd123efgh') AS position; -- Returns 5
SELECT LEN(N'xyz ') AS length1; --
Returns 3
SELECT LEN('xyz ') AS length2; --
Returns 3
SELECT DATALENGTH(N'xyz ') AS length3; --
Returns 8 (2 bytes/char + 2 bytes/space)
SELECT DATALENGTH('xyz ') AS length4; -- Returns 4 (1
byte/char + 1 byte/space)
SELECT REPLACE ('1.2.3', '.', '/') AS replaced; -- Returns 1/2/3
SELECT REPLICATE ('*',10) AS asterisk; -- Returns ‘**********’
SELECT STUFF('ABCDEF',1,3,'PQR') AS stuffed; -- Returns
‘PQRDEF’
SELECT LOWER('ABCDEF') AS lowered; -- Returns ‘abcdef’
SELECT UPPER('abcdef') AS uppered; -- Returns ‘ABCDEF’
SELECT LTRIM(' ABCDEF ') AS ltrimed; -- Returns
‘ABCDEF ’
SELECT RTRIM(' ABCDEF ') AS rtrimed; -- Returns
‘ ABCDEF’
SELECT LTRIM(RTRIM(' ABCDEF ')) AS trimed; -- Returns
‘ABCDEF’
SELECT NULLIF('ABC','ABC') AS [NULLIF]; -- Equal
Returns NULL
SELECT NULLIF('ABC',NULL) AS [NULLIF]; -- Returns
'ABC'
SELECT NULLIF(NULL,NULL) AS [NULLIF]; -- Error
--Simple Case:
SELECT productid, productname,
unitprice, discontinued,
CASE discontinued
WHEN 0 THEN 'yes'
WHEN 1 THEN 'no'
ELSE 'unknown'
END AS
discontinued_desc
FROM Production.Products;
--Searched form
Case:
SELECT productid, productname,
unitprice, discontinued,
CASE
WHEN unitprice < 20.00 THEN 'low'
WHEN unitprice < 40.00 THEN 'Medium'
WHEN unitprice >= 40.00 THEN 'high'
ELSE 'unknown'
END AS
unitprice_desc
FROM Production.Products;
--IS NULL / IS
NOT NULL
SELECT empid, firstname, lastname, country, region, city
FROM HR.Employees
WHERE region <> N'WA'
OR region IS NULL;
-- Predicate
Preferences: The NOT operator precedes AND and OR, and AND precedes OR.
SELECT empid, firstname, lastname, country, region, city
FROM HR.Employees
WHERE region <> N'WA' OR region IS NULL AND firstname = 'Sven' OR lastname = 'Suurs';
--Like: Search Options
SELECT empid, firstname, lastname, country, region, city
FROM HR.Employees
WHERE firstname LIKE 'S%' OR lastname = '%S'
OR firstname LIKE '_D' OR lastname = '__D'
OR firstname LIKE '[DEF]%'
OR lastname = '%[0-9]'
OR lastname = '^%[A]%';
-- ORDER BY in
DISTINCT Clause can only be used for attributes in the SELECT list:
SELECT DISTINCT city,
country
FROM HR.Employees
WHERE country = N'USA' AND region = N'WA'
ORDER BY city, country;
--Sorting of
Nulls: NULLS are displayed first by default.Use ORDER BY DESC to get the NULL rows below
SELECT orderid, shippeddate
FROM Sales.Orders
WHERE custid = 20
ORDER BY shippeddate;
-- TOP: TOP
& TOP PERCENT
DECLARE @n AS BIGINT = 5;
SELECT TOP(@n) orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC;
SELECT TOP(@n) PERCENT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC;
--Deterministic
TOP:
SELECT TOP (3) orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY (SELECT NULL);
--TOP WITH TIES
(Fetches more records if duplicates) - Following can return 4/5 or more records
SELECT TOP (3) WITH TIES orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC;
--Filtering Data
with OFFSET-FETCH:
-- FIRST/NEXT
are same, ROWS /ROW are same
SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC, orderid DESC
OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;
SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY (SELECT NULL)
OFFSET 0 ROWS FETCH FIRST 3 ROWS ONLY;
--JOINS:
--CROSS JOIN
SELECT D.n AS [day], S.n AS [shift]
FROM DBO.nums AS D
CROSS JOIN
DBO.nums AS S
WHERE D.n <= 7
AND S.n
<= 3
ORDER BY [day], [shift];
--INNER JOIN
SELECT
S.companyname
AS supplier, S.country,
P.productid, P.productname, P.unitprice
FROM Production.Suppliers AS S
INNER JOIN
Production.Products AS
P
ON S.supplierid = P.supplierid
WHERE S.country = N'Japan';
-- ON &
WHERE are the same in INNER JOIN
SELECT
S.companyname
AS supplier, S.country,
P.productid, P.productname, P.unitprice
FROM Production.Suppliers AS S
INNER JOIN
Production.Products AS
P
ON S.supplierid = P.supplierid
AND S.country = N'Japan';
-- Self INNER
JOIN
SELECT E.empid,
E.firstname
+ N' ' + E.lastname AS emp,
M.firstname
+ N' ' + M.lastname AS mgr
FROM HR.Employees AS E
INNER JOIN
HR.Employees AS
M
ON E.mgrid = M.empid;
--OUTER JOIN
--LEFT &
RIGHT OUTER JOIN
SELECT
S.companyname
AS supplier, S.country,
P.productid, P.productname, P.unitprice
FROM Production.Suppliers AS S
LEFT OUTER
JOIN Production.Products
AS P
ON S.supplierid = P.supplierid
WHERE S.country = N'Japan';
-- Returning
even NULL manager from previous query:
SELECT E.empid,
E.firstname
+ N' ' + E.lastname AS emp,
M.firstname
+ N' ' + M.lastname AS mgr
FROM HR.Employees AS E
LEFT OUTER
JOIN HR.Employees
AS M
ON E.mgrid = M.empid;
-- Multi-JOIN
Query (Left To Right)
-- Multi-JOIN
Query: Leaves records with productid = NULL
SELECT S.companyname AS
supplier, S.country,
P.productid, P.productname, P.unitprice, C.categoryname
FROM Production.Suppliers AS S
LEFT OUTER
JOIN Production.Products
AS P
ON S.supplierid = P.supplierid
INNER JOIN Production.Categories
AS C
ON C.categoryid = P.categoryid
WHERE S.country = N'Japan';
-- Multi-JOIN
Query: Counts records with productid = NULL
SELECT S.companyname AS
supplier, S.country,
P.productid, P.productname, P.unitprice, C.categoryname
FROM Production.Suppliers AS S
LEFT OUTER
JOIN
(Production.Products AS P
INNER JOIN Production.Categories
AS C
ON C.categoryid = P.categoryid)
ON S.supplierid = P.supplierid
WHERE S.country = N'Japan';
-- Subqueries:
--
Self-Contained Subqueries
SELECT productid, productname,
unitprice
FROM Production.Products
WHERE unitprice =
(SELECT MIN(unitprice)FROM Production.Products);
SELECT productid, productname,
unitprice
FROM Production.Products
WHERE supplierid
IN
(SELECT supplierid
FROM Production.Suppliers
WHERE country = N'Japan');
-- Correlated
Subqueries (To find minimum unit price/category
SELECT categoryid, productid,
productname, unitprice
FROM Production.Products AS P1
WHERE unitprice =
(SELECT MIN(unitprice)
FROM Production.Products AS P2
WHERE P2.categoryid = P1.categoryid);
-- Customers who
placed orders on February 12, 2007.
SELECT custid, companyname
FROM Sales.Customers AS C
WHERE EXISTS
(SELECT *
FROM Sales.Orders AS O
WHERE O.custid
= C.custid
AND O.orderdate = '20070212');
-- Customers who
did not place orders on February 12, 2007.
SELECT custid, companyname
FROM Sales.Customers AS C
WHERE NOT EXISTS
(SELECT *
FROM Sales.Orders AS O
WHERE O.custid
= C.custid
AND O.orderdate = '20070212');
--Top 5 freights
for each shipcountry (Correlated Sub query)
SELECT E.shipcountry, E.freight
FROM Sales.Orders AS E
WHERE E.freight IN(
SELECT TOP
5 F.freight
FROM Sales.Orders AS F
WHERE F.shipcountry
= E.shipcountry
ORDER BY
F.freight DESC)
ORDER BY E.shipcountry, E.freight DESC;
-- Table
Expressions
-- Derived
Tables: Top 5 freights for each shipcountry
SELECT
shipcountry, freight
FROM
(SELECT E.shipcountry, E.freight,
ROW_NUMBER() OVER(PARTITION BY E.shipcountry
ORDER BY E.freight DESC) AS rownum
FROM Sales.Orders AS E) AS D
WHERE rownum <=5;
-- Common table
expression (CTE):
WITH D AS
(SELECT E.shipcountry, E.freight,
ROW_NUMBER() OVER(PARTITION BY E.shipcountry
ORDER BY E.freight DESC) AS rownum
FROM Sales.Orders AS E)
SELECT
shipcountry, freight
FROM D
WHERE rownum <=5;
-- CTEs also
have a recursive form
WITH EmpsCTE AS
(SELECT empid, mgrid, firstname, lastname, 0 AS distance
FROM HR.Employees
WHERE empid = 9
UNION ALL
SELECT M.empid, M.mgrid, M.firstname, M.lastname, S.distance + 1 AS distance
FROM EmpsCTE AS S
JOIN HR.Employees AS M
ON S.mgrid = M.empid)
SELECT empid, mgrid, firstname, lastname, distance
FROM EmpsCTE;
-- Views
IF OBJECT_ID('Sales.TopFreight','V') IS NOT NULL
DROP VIEW
Sales.TopFreight;
GO
CREATE VIEW Sales.TopFreight
AS
SELECT E.shipcountry, E.freight,
ROW_NUMBER() OVER(PARTITION BY E.shipcountry
ORDER BY E.freight DESC) AS rownum
FROM Sales.Orders AS E;
GO
SELECT
shipcountry, freight
FROM Sales.TopFreight
WHERE rownum <=5;
-- IF (Inline
Table-Valued Functions)
IF OBJECT_ID('Sales.TopFreightIF','IF') IS NOT NULL
DROP FUNCTION Sales.TopFreightIF;
GO
CREATE FUNCTION Sales.TopFreightIF(@num AS INT) RETURNS TABLE
AS
RETURN
WITH D AS
(SELECT E.shipcountry, E.freight,
ROW_NUMBER() OVER(PARTITION BY E.shipcountry
ORDER BY E.freight DESC) AS rownum
FROM Sales.Orders AS E)
SELECT shipcountry, freight
FROM D
WHERE rownum <= @num;
GO
SELECT
shipcountry, freight
FROM Sales.TopFreightIF(5);
-- Set
Operators:
-- Set operators
have precedence: INTERSECT precedes UNION and EXCEPT, and UNION and EXCEPT are
considered equal.
-- UNION
SELECT country, region, city
FROM HR.Employees
UNION
SELECT country, region, city
FROM Sales.Customers;
-- UNION ALL
SELECT country, region, city
FROM HR.Employees
UNION ALL
SELECT country, region, city
FROM Sales.Customers;
-- INTERSECT
SELECT country, region, city
FROM HR.Employees
INTERSECT
SELECT country, region, city
FROM Sales.Customers;
-- EXCEPT
(Returns only A minus common in A & B)
SELECT country, region, city
FROM HR.Employees
EXCEPT
SELECT country, region, city
FROM Sales.Customers;
--GROUP BY
SELECT shipperid, YEAR(shippeddate) AS shippedyear,
COUNT(*) AS numorders
FROM Sales.Orders
WHERE
shippeddate IS NOT
NULL
GROUP BY shipperid, YEAR(shippeddate)
HAVING COUNT(*) < 100;
--
COUNT(shippeddate) ignores NULL
SELECT shipperid,
COUNT(*) AS numorders,
COUNT(shippeddate) AS shippedorders,
MIN(shippeddate) AS firstshipdate,
MAX(shippeddate) AS lastshipdate,
SUM(val) AS totalvalue
FROM Sales.OrderValues
GROUP BY shipperid;
--GROUP BY with
DISTINCT
SELECT shipperid, COUNT(DISTINCT shippeddate) AS numshippingdates
FROM Sales.Orders
GROUP BY shipperid;
-- Workaround is
to apply an aggregate function like MAX to the column, for GROUP BY
SELECT S.shipperid,
MAX(S.companyname) AS numorders,
COUNT(*) AS shippedorders
FROM Sales.Shippers AS S
INNER JOIN
Sales.Orders AS
O
ON S.shipperid = O.shipperid
GROUP BY S.shipperid;
---------------------------------------------------------------------
-- Section A -
for on-premises SQL Server only
---------------------------------------------------------------------
-- 1. Connect to
SQL Azure, master database
USE master; -- used only as a test; will fail if not connected to
master
-- 2. Run
following if TSQL2012 database already exists, otherwise skip
IF DB_ID('TSQL2012') IS NOT NULL DROP DATABASE TSQL2012;
GO
-- 3. Run the
following code to create an empty database called TSQL2012
CREATE DATABASE TSQL2012;
GO
-- 4. Connect to
TSQL2012 before running the rest of the code
USE TSQL2012; -- used only as a test;
will fail if not connected to TSQL2012
GO
---------------------------------------------------------------------
-- Create Schemas
---------------------------------------------------------------------
CREATE SCHEMA HR AUTHORIZATION
dbo;
GO
---------------------------------------------------------------------
-- Create Tables
---------------------------------------------------------------------
-- Create table
HR.Employees
CREATE TABLE HR.Employees
(
empid INT NOT NULL IDENTITY,
lastname NVARCHAR(20) NOT NULL,
firstname NVARCHAR(10) NOT NULL,
title NVARCHAR(30) NOT NULL,
titleofcourtesy NVARCHAR(25) NOT NULL,
birthdate DATETIME NOT NULL,
hiredate DATETIME NOT NULL,
address NVARCHAR(60) NOT NULL,
city NVARCHAR(15) NOT NULL,
region NVARCHAR(15) NULL,
postalcode NVARCHAR(10) NULL,
country NVARCHAR(15) NOT NULL,
phone NVARCHAR(24) NOT NULL,
mgrid INT NULL,
CONSTRAINT
PK_Employees PRIMARY KEY(empid),
CONSTRAINT
FK_Employees_Employees FOREIGN KEY(mgrid)
REFERENCES
HR.Employees(empid),
CONSTRAINT
CHK_birthdate CHECK(birthdate
<= CURRENT_TIMESTAMP)
);
CREATE NONCLUSTERED INDEX
idx_nc_lastname ON HR.Employees(lastname);
CREATE NONCLUSTERED INDEX
idx_nc_postalcode ON HR.Employees(postalcode);
---------------------------------------------------------------------
-- Populate
Tables
---------------------------------------------------------------------
SET NOCOUNT ON;
-- Populate
table HR.Employees
SET IDENTITY_INSERT HR.Employees
ON;
INSERT INTO HR.Employees(empid, lastname, firstname, title, titleofcourtesy,
birthdate, hiredate,
address, city, region, postalcode, country, phone, mgrid)
VALUES(1, N'Davis', N'Sara', N'CEO', N'Ms.', '19581208 00:00:00.000',
'20020501 00:00:00.000',
N'7890 - 20th Ave. E., Apt. 2A', N'Seattle', N'WA', N'10003', N'USA', N'(206) 555-0101', NULL);
INSERT INTO HR.Employees(empid, lastname, firstname, title, titleofcourtesy,
birthdate, hiredate,
address, city, region, postalcode, country, phone, mgrid)
VALUES(2, N'Funk', N'Don', N'Vice President, Sales',
N'Dr.', '19620219 00:00:00.000',
'20020814 00:00:00.000',
N'9012 W. Capital Way',
N'Tacoma', N'WA', N'10001', N'USA', N'(206) 555-0100', 1);
SET IDENTITY_INSERT HR.Employees
OFF;
-- This INSERT
will also work:
SET IDENTITY_INSERT HR.Employees
ON;
INSERT INTO HR.Employees(empid, lastname, firstname, title, titleofcourtesy,
birthdate, hiredate,
address, city, region, postalcode, country, phone, mgrid)VALUES
(1, N'Davis', N'Sara', N'CEO', N'Ms.', '19581208 00:00:00.000', '20020501 00:00:00.000', N'7890 - 20th Ave. E., Apt.
2A', N'Seattle', N'WA', N'10003', N'USA', N'(206) 555-0101', NULL),
(2, N'Funk', N'Don', N'Vice President, Sales', N'Dr.', '19620219 00:00:00.000', '20020814 00:00:00.000', N'9012 W. Capital Way', N'Tacoma', N'WA', N'10001', N'USA', N'(206) 555-0100', 1);
SET IDENTITY_INSERT HR.Employees
OFF;
-- This INSERT
will also work:
SET IDENTITY_INSERT HR.Employees1
ON;
INSERT INTO HR.Employees1(empid, lastname, firstname, title, titleofcourtesy,
birthdate, hiredate,
address, city, region, postalcode, country, phone, mgrid)
SELECT empid, lastname, firstname, title,
titleofcourtesy, birthdate, hiredate, address, city, region, postalcode, country, phone, mgrid
FROM HR.Employees;
SET IDENTITY_INSERT HR.Employees
OFF;
-- Update:
UPDATE HR.Employees1
SET birthdate += DAY(300)
WHERE lastname = 'Davis';
SET NOCOUNT OFF;
GO
-- TRUNCATE:
TRUNCATE TABLE HR.Employees1;
-- DELETE:
DELETE FROM HR.Employees1;
DELETE FROM HR.Employees1
WHERE lastname = 'Davis';
/*The DELETE and
TRUNCATE statements have a number of important differences between
them:
1. The TRUNCATE
statement deletes all rows from the target table. Unlike the DELETE statement,
it doesn’t have
an optional filter, so it’s all or nothing.
2. The DELETE
statement writes significantly more to the transaction log compared to the
TRUNCATE
statement. For DELETE, SQL Server records in the log the actual data that
was deleted. For
TRUNCATE, SQL Server records information only about which pages
were
deallocated. As a result, the TRUNCATE statement tends to be substantially
faster.
3.The DELETE
statement doesn’t attempt to reset an identity property if one is associated
with a column in
the target table. The TRUNCATE statement does. If you use
TRUNCATE and
would prefer not to reset the property, you need to store the current
identity value
plus one in a variable (using the IDENT_CURRENT function), and reseed
the property
with the stored value after the truncation.
4.The DELETE
statement is supported if there’s a foreign key pointing to the table in
question as long
as there are no related rows in the referencing table. TRUNCATE is not
allowed if a
foreign key is pointing to the table—even if there are no related rows in
the referencing
table, and even if the foreign key is disabled.
5.The DELETE
statement is allowed against a table involved in an indexed view. A TRUNCATE
statement is
disallowed in such a case.
6.The DELETE
statement requires DELETE permissions on the target table. The TRUNCATE
statement
requires ALTER permissions on the target table.*/
--DROP
Table/Database
DROP TABLE HR.Employees1;
DROP DATABASE TSQL2012;
-- ALTER
ALTER TABLE HR.Employees
ADD
DateOfBirth DATE;
ALTER TABLE HR.Employees
ALTER COLUMN DateOfBirth DATETIME;
ALTER TABLE HR.Employees
DROP COLUMN DateOfBirth;
