Scribd
Upload
21 views29 pages

SQL Queries for UC Davis Database

The document contains examples of SQL queries using various operators and functions. It shows queries using comparison, logical, arithmetic operators and functions like CASE, CONCAT, CAST in the WHERE clause to filter results. It also covers IS NULL, BETWEEN, IN, LIKE operators.

Uploaded by

clouditlab9
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
21 views29 pages

SQL Queries for UC Davis Database

The document contains examples of SQL queries using various operators and functions. It shows queries using comparison, logical, arithmetic operators and functions like CASE, CONCAT, CAST in the WHERE clause to filter results. It also covers IS NULL, BETWEEN, IN, LIKE operators.

Uploaded by

clouditlab9
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

SELECT *

FROM [Link];

SELECT *
FROM [Link];

SELECT BusinessEntityID, FirstName, MiddleName, LastName


FROM [Link];

SELECT FirstName, MiddleName, LastName, BusinessEntityID


FROM [Link];

SELECT FirstName
FROM [Link];

SELECT 'FirstName'
FROM [Link];
======
============================================================================
SELECT JobTitle
FROM [Link];

SELECT DISTINCT JobTitle


FROM [Link];

SELECT OrganizationLevel
FROM [Link];

SELECT DISTINCT OrganizationLevel


FROM [Link];

SELECT *
FROM [Link];

SELECT Title, Suffix


FROM [Link];

SELECT DISTINCT Title, Suffix


FROM [Link];
=============================================

SELECT NationalIDNumber, HireDate, VacationHours


FROM [Link];

SELECT NationalIDNumber, HireDate, VacationHours,


--FROM [Link];
CASE
WHEN VacationHours > 70 THEN 'Vacation hours over limit'
ELSE 'Vacation Hours within limit'
END AS VacationHourLimit
FROM [Link];

SELECT NationalIDNumber, HireDate, VacationHours,


--FROM [Link];
CASE
WHEN VacationHours > 70 THEN 'Vacation hours over limit'
WHEN VacationHours BETWEEN 40 AND 70 THEN 'Vacation hours average'
ELSE 'Vacation Hours within limit'
END AS VacationHourLimit
FROM [Link];

SELECT NationalIDNumber, HireDate, VacationHours,


--FROM [Link];
CASE
WHEN VacationHours > 70 THEN 'Vacation hours over limit'
WHEN VacationHours BETWEEN 48 AND 70 THEN 'Vacation hours average'
ELSE 'Vacation Hours within limit'
END AS VacationHourLimit
FROM [Link];

SELECT NationalIDNumber, HireDate, VacationHours,


--FROM [Link];
CASE
WHEN VacationHours > 70 THEN 'Vacation hours over limit'
WHEN VacationHours BETWEEN 48 AND 61 THEN 'Vacation hours average'
ELSE 'Vacation Hours within limit'
END AS VacationHourLimit
FROM [Link];
===================================================================
SELECT *
FROM [Link];

SELECT BusinessEntityID, NationalIDNumber, BirthDate, MaritalStatus


FROM [Link]
WHERE MaritalStatus = 'S';

SELECT BusinessEntityID, NationalIDNumber, BirthDate


FROM [Link]
WHERE BirthDate > '1985-01-20';

SELECT BusinessEntityID, NationalIDNumber, BirthDate


FROM [Link]
WHERE year(BirthDate) > '1985';

SELECT BusinessEntityID, NationalIDNumber, BirthDate


FROM [Link]
WHERE year(BirthDate) = '1985';
===========
Code used - Comparison Operators in WHERE Clause
SELECT StateProvinceID, CountryRegionCode
FROM Person. StateProvince
WHERE CountryRegionCode <> 'US';

SELECT StateProvinceID, CountryRegionCode


FROM Person. StateProvince
WHERE CountryRegionCode != 'US';

SELECT StateProvinceCode, CountryRegionCode


FROM Person. StateProvince
WHERE StateProvinceCode <> CountryRegionCode;

SELECT StateProvinceID, StateProvinceCode, CountryRegionCode


FROM Person. StateProvince
WHERE StateProvinceID >= 30;

SELECT StateProvinceID, StateProvinceCode, CountryRegionCode


FROM Person. StateProvince
WHERE StateProvinceID <= 30;
SELECT StateProvinceID, StateProvinceCode, CountryRegionCode
FROM Person. StateProvince
WHERE StateProvinceID < 30;

SELECT StateProvinceID, StateProvinceCode, CountryRegionCode


FROM Person. StateProvince
WHERE StateProvinceID <> 30;

SELECT StateProvinceID, StateProvinceCode, CountryRegionCode


FROM Person. StateProvince
WHERE StateProvinceID != 30;
===============
Code used - Arithmetic Operators in WHERE clause
SELECT *
FROM [Link];

SELECT StandardCost, ListPrice, StandardCost + ListPrice AS SumListPriceCost


FROM [Link]
WHERE SumListPriceCost > 0;

SELECT StandardCost, ListPrice, StandardCost + ListPrice AS SumListPriceCost


FROM [Link]
WHERE StandardCost + ListPrice > 0;

SELECT StandardCost, ListPrice


FROM [Link]
WHERE StandardCost + ListPrice > 0;

SELECT ProductID, Name, ReorderPoint, StandardCost


FROM [Link]
WHERE ListPrice - StandardCost > 10;

SELECT ProductID, Name, ReorderPoint, StandardCost


FROM [Link]
WHERE ReorderPoint*StandardCost > 80000;

SELECT ProductID, Name, ReorderPoint, StandardCost


FROM [Link]
WHERE ReorderPoint/StandardCost > 80000;

SELECT ProductID, Name, ReorderPoint, StandardCost


FROM [Link]
WHERE ReorderPoint/StandardCost > 3 AND StandardCost <> 0;
===================
Code used - Logical Operators in WHERE clause
SELECT ProductID, Name, StandardCost, ListPrice, SafetyStockLevel
FROM [Link]
WHERE ProductID < 600 AND StandardCost > 50;

SELECT ProductID, Name, StandardCost, ListPrice, SafetyStockLevel


FROM [Link]
WHERE ProductID < 600 AND StandardCost > 50 AND SafetyStockLevel BETWEEN 500 AND
1200;

SELECT ProductID, Name, StandardCost, ListPrice, SafetyStockLevel


FROM [Link]
WHERE ProductID < 600 OR StandardCost > 50;
SELECT ProductID, Name, StandardCost, ListPrice, SafetyStockLevel
FROM [Link]
WHERE ProductID = 1 OR ProductID = 10 OR ProductID = 15 OR ProductID = 20;

SELECT ProductID, Name, StandardCost, ListPrice, SafetyStockLevel


FROM [Link]
WHERE ProductID = 800 OR ProductID < 600 AND StandardCost > 50 AND SafetyStockLevel
BETWEEN 500 AND 1200;

SELECT ProductID, Name, StandardCost, ListPrice, SafetyStockLevel


FROM [Link]
--WHERE ProductID = 800;
WHERE (ProductID = 800 OR ProductID < 600) AND StandardCost > 50 AND
SafetyStockLevel BETWEEN 500 AND 1200;

SELECT ProductID, Name, StandardCost, ListPrice, SafetyStockLevel


FROM [Link]
WHERE ProductID = 800;

SELECT ProductID, Name, StandardCost, ListPrice, SafetyStockLevel


FROM [Link]
WHERE (ProductID = 800 OR ProductID < 600) AND StandardCost > 50 AND
SafetyStockLevel BETWEEN 500 AND 1200;

SELECT ProductID, Name, StandardCost, ListPrice, SafetyStockLevel


FROM [Link]
WHERE NOT ProductID = 4;

SELECT ProductID, Name, StandardCost, ListPrice, SafetyStockLevel


FROM [Link]
--WHERE NOT ProductID = 4;
WHERE Name = 'Adjustable Race';

SELECT ProductID, Name, StandardCost, ListPrice, SafetyStockLevel


FROM [Link]
--WHERE NOT ProductID = 4;
WHERE NOT Name = 'Adjustable Race';
=============
Code used - Concatenation operator(+) with WHERE clause
SELECT Title, FirstName, MiddleName, LastName,
Title + FirstName + MiddleName + LastName
FROM [Link];

SELECT Title, FirstName, MiddleName, LastName,


Title + FirstName + MiddleName + LastName AS FullName
FROM [Link];

SELECT Title, FirstName, MiddleName, LastName,


Title + ' ' + FirstName + ' ' + MiddleName + ' ' + LastName AS FullName
FROM [Link];

SELECT Title, FirstName, MiddleName, LastName,


Title + ' ' + FirstName + ' ' + MiddleName + ' ' + LastName AS FullName
FROM [Link]
WHERE Title + ' ' + FirstName + ' ' + MiddleName + ' ' + LastName
= 'Ms. Gail A Erickson';

SELECT StandardCost + ListPrice As Sumabc


FROM [Link];
SELECT StandardCost + ListPrice As Sumabc
FROM [Link]
WHERE StandardCost <> 0 OR ListPrice <> 0;
===============
Code used - concat function
SELECT Title, FirstName, MiddleName, LastName,
CONCAT(Title, ' ' , FirstName , ' ' , MiddleName , ' ' + LastName)
AS FullName
FROM [Link];

SELECT FirstName, BusinessEntityID, CONCAT_WS('.','adventure-works','com')


AS domain
FROM [Link];

SELECT FirstName, BusinessEntityID,


CONCAT(FirstName, BusinessEntityID,
'@' , CONCAT_WS('.','adventure-works','com')) AS PersonEmail,
CONCAT_WS('.','adventure-works','com') AS domain
FROM [Link];
=============================
Code used - IS NULL Value
SELECT ProductId, Name, Color
FROM [Link]
WHERE Color IS NULL;

SELECT ProductId, Name, Color


FROM [Link]
WHERE Color IS NOT NULL;
===================
Code used - Between Operator in WHERE Clause
SELECT PurchaseOrderId, ModifiedDate
FROM [Link]
WHERE ModifiedDate BETWEEN '20140203'AND '20150812';

SELECT PurchaseOrderId, ModifiedDate


FROM [Link]
WHERE ModifiedDate BETWEEN '2014-02-03'AND '2015-08-13';

SELECT BusinessEntityID, BirthDate


FROM [Link];

SELECT BusinessEntityID, CAST(BirthDate AS datetime)


FROM [Link];

SELECT BusinessEntityID, CAST(BirthDate AS datetime) AS BirthDateTime


FROM [Link];

SELECT CAST('2020-06-12' AS datetime);

SELECT PurchaseOrderId, ModifiedDate


FROM [Link]
WHERE ModifiedDate NOT BETWEEN '2014-02-03'AND '2015-08-13';

SELECT PurchaseOrderId, ModifiedDate


FROM [Link]
WHERE ModifiedDate NOT BETWEEN '2014-02-03'AND '2015-08-12';
====================
Code used - Better way to tackle dates with CAST Function
SELECT PurchaseOrderId, ModifiedDate
FROM [Link]
WHERE ModifiedDate BETWEEN '2014-02-03'AND '2015-08-13';

SELECT CAST(1.234 AS INT);


SELECT CAST(1.834 AS INT);

SELECT PurchaseOrderId, ModifiedDate


FROM [Link]
WHERE CAST(ModifiedDate AS DATE) BETWEEN '2014-02-03'AND '2015-08-12';
===========
Code used - IN Operator
SELECT ProductID, Name, StandardCost, ListPrice, SafetyStockLevel
FROM [Link]
WHERE ProductID = 1 OR ProductID = 10
OR ProductID = 15 OR ProductID = 20;

SELECT ProductID, Name, StandardCost, ListPrice, SafetyStockLevel


FROM [Link]
WHERE ProductID IN (1,10,15,20);

SELECT *
FROM [Link]
WHERE StateProvinceCode IN ('AK','AZ','CO','MB','IA');

SELECT *
FROM [Link]
WHERE StateProvinceCode IN ('AK','AZ','CO','MZ','IA');

SELECT *
FROM [Link]
WHERE StateProvinceCode IN ('AK','AZ','CO','ID','IA');

SELECT *
FROM [Link]
WHERE StateProvinceCode NOT IN ('AK','AZ','CO','ID','IA');
=========
Code used - LIKE Operator with percent to match 0 or more Characters
SELECT BusinessEntityId, JobTitle
FROM [Link]
WHERE JobTitle LIKE 'R%';

SELECT BusinessEntityId, JobTitle


FROM [Link]
WHERE JobTitle LIKE 'Research and Development%';

SELECT BusinessEntityId, JobTitle


FROM [Link]
WHERE JobTitle LIKE '%Manager%';

SELECT BusinessEntityId, JobTitle


FROM [Link]
WHERE JobTitle LIKE '%Manager';

SELECT BusinessEntityId, JobTitle


FROM [Link]
WHERE JobTitle LIKE 'Manager%';
================================
Code used - LIKE Operator with [...] Character classes
SELECT *
FROM [Link]
WHERE StateProvinceCode LIKE 'A[BKL]';

SELECT *
FROM [Link]
--WHERE StateProvinceCode LIKE 'A[BKL]%';
WHERE StateProvinceCode LIKE '[IL]A%';

SELECT *
FROM [Link]
--WHERE StateProvinceCode LIKE 'A[BKL]%';
--WHERE StateProvinceCode LIKE '[IL]A%';
WHERE StateProvinceCode LIKE '[IL][ABCDEFG]';

SELECT *
FROM [Link]
--WHERE StateProvinceCode LIKE 'A[BKL]%';
--WHERE StateProvinceCode LIKE '[IL]A%';
WHERE StateProvinceCode LIKE '%[IL][ABCDEFG]%';
======================
Code used - LIKE Operator with character range
SELECT *
FROM [Link];

SELECT *
FROM [Link]
WHERE ProductNumber LIKE 'L[IJKLMN]%';

SELECT *
FROM [Link]
WHERE ProductNumber LIKE 'L[I-N]%';

SELECT *
FROM [Link]
WHERE ProductNumber LIKE 'L[I-N]%';

SELECT *
FROM [Link]
WHERE ProductNumber LIKE 'L%';

SELECT *
FROM [Link]
--WHERE ProductNumber LIKE 'L%';
WHERE ProductNumber LIKE 'L[A-Z]-[0-9]%'

SELECT *
FROM [Link]
--WHERE ProductNumber LIKE 'L%';
--WHERE ProductNumber LIKE 'L[A-Z]-[0-9]%'
WHERE ProductNumber LIKE 'L[I-N]-[135]%';
===========
Code used - LIKE Operator with Negation Character
SELECT *
FROM [Link]
WHERE ProductNumber LIKE 'L[^I-N]-[135]%';

SELECT *
FROM [Link]
WHERE ProductNumber LIKE 'L[^I-N]-[^135]%';
============
Code used - LIKE Operator with Negation Character
SELECT *
FROM [Link]
WHERE ProductNumber LIKE 'L[^I-N]-[135]%';

SELECT *
FROM [Link]
WHERE ProductNumber LIKE 'L[^I-N]-[^135]%';
===========
Code used - Test Your skills
SELECT DISTINCT PersonType
FROM [Link]
WHERE PersonType LIKE 'S_';

SELECT DISTINCT PersonType


FROM [Link]
WHERE PersonType NOT LIKE 'S_';
===========
Code used - Escaping single characters
SELECT *
FROM [Link];

SELECT *
FROM [Link]
WHERE NAME like '%,%';

SELECT *
FROM [Link]
WHERE NAME like '%.%';

SELECT *
FROM [Link]
--WHERE NAME like '%.%';
WHERE Name LIKE '%''%';
================
Code used - ORDER BY Clause - Part 1
SELECT JobCandidateId, ModifiedDate
FROM [Link];

SELECT JobCandidateId, ModifiedDate


FROM [Link]
ORDER BY ModifiedDate DESC;

SELECT JobCandidateId, ModifiedDate


FROM [Link]
ORDER BY ModifiedDate DESC;

SELECT *
FROM [Link]
ORDER BY AddressID

SELECT CONCAT(AddressLine1, ' ', AddressLine2, ' ',


City, ' ', StateProvinceID, ' ', PostalCode)
FROM [Link]
ORDER BY AddressID;

SELECT AddressID, CONCAT(AddressLine1, ' ', AddressLine2, ' ',


City, ' ', StateProvinceID, ' ', PostalCode)
FROM [Link]
ORDER BY AddressID;

SELECT AddressID, CONCAT(AddressLine1, ' ', AddressLine2, ' ',


City, ' ', StateProvinceID, ' ', PostalCode) AS
PostalAddress
FROM [Link]
ORDER BY AddressID;

SELECT *
FROM [Link]

SELECT ProductID, Name, StandardCost, Listprice


FROM [Link]
ORDER BY StandardCost;

SELECT ProductID, Name, StandardCost, Listprice


FROM [Link]
ORDER BY StandardCost ASC;

SELECT ProductID, Name, StandardCost, Listprice


FROM [Link]
ORDER BY StandardCost ASC, ListPrice DESC;
--Rec1 - standcost - 100, Listprice - 10
--Rec2 - standcost - 100, Listprice - 15
--100, 15
--100,05

SELECT ProductID, Name, StandardCost, Listprice


FROM [Link]
ORDER BY StandardCost DESC, ListPrice DESC;

SELECT ProductID, Name, StandardCost, Listprice


FROM [Link]
WHERE StandardCost > 0 AND ListPrice > 0
ORDER BY StandardCost DESC, ListPrice DESC;

SELECT ProductID, Name, StandardCost, Listprice


FROM [Link]
WHERE StandardCost > 0 AND ListPrice > 0
ORDER BY 4;

SELECT ProductID, Name, StandardCost, Listprice


FROM [Link]
WHERE StandardCost > 0 AND ListPrice > 0
ORDER BY 3 DESC, 4;
==============
Code used - ORDER BY Clause - Part 2
SELECT BusinessEntityID, NationalIDNumber, HireDate
FROM [Link]
ORDER BY BirthDate DESC;

SELECT AddressID, AddressLine1


FROM [Link]
ORDER BY LEN(AddressLine1) ASC;

SELECT AddressID, AddressLine1, LEN(AddressLine1)


FROM [Link]
ORDER BY LEN(AddressLine1) ASC;

SELECT AddressID, AddressLine1, LEN(AddressLine1) AS LengthAddL1


FROM [Link]
ORDER BY LEN(AddressLine1) ASC;
===========
Code used - ORDER BY Clause - Part 3
SELECT BusinessEntityID, NationalIDNumber, HireDate
FROM [Link]
ORDER BY HireDate ASC;

SELECT BusinessEntityID, NationalIDNumber, HireDate


FROM [Link]
ORDER BY HireDate ASC
OFFSET 5 ROWS
FETCH NEXT 20 ROWS ONLY;

SELECT BusinessEntityID, NationalIDNumber, HireDate


FROM [Link]
ORDER BY HireDate DESC
OFFSET 0 ROWS
FETCH NEXT 5 ROWS ONLY;

SELECT TOP 5 BusinessEntityID, NationalIDNumber, HireDate


FROM [Link]
ORDER BY HireDate DESC;
=====
Code used - ORDER BY Clause - Part 3
SELECT BusinessEntityID, NationalIDNumber, HireDate
FROM [Link]
ORDER BY HireDate ASC;

SELECT BusinessEntityID, NationalIDNumber, HireDate


FROM [Link]
ORDER BY HireDate ASC
OFFSET 5 ROWS
FETCH NEXT 20 ROWS ONLY;

SELECT BusinessEntityID, NationalIDNumber, HireDate


FROM [Link]
ORDER BY HireDate DESC
OFFSET 0 ROWS
FETCH NEXT 5 ROWS ONLY;

SELECT TOP 5 BusinessEntityID, NationalIDNumber, HireDate


FROM [Link]
ORDER BY HireDate DESC;
=============
Code used - GROUP BY Clause - Part 1
SELECT SalesOrderID, ORderQty
FROM [Link]

SELECT SalesOrderID, sum(ORderQty)


FROM [Link]
GROUP BY SalesOrderID;
======================================
Code used - GROUP BY Clause - Part 2
SELECT *
FROM [Link];
SELECT GroupName
FROM [Link]
GROUP BY GroupName;

SELECT GroupName, COUNT(GroupName)


FROM [Link]
GROUP BY GroupName;

SELECT GroupName, COUNT(*)


FROM [Link]
GROUP BY GroupName;
==============
Code used - GROUP BY Clause - Part 3
SELECT *
FROM [Link];

SELECT PayFrequency, SUM(Rate)


FROM [Link];

SELECT PayFrequency, SUM(Rate) AS TotalRatePerPayFrequency


FROM [Link]
GROUP BY PayFrequency;

SELECT PayFrequency, SUM(Rate) AS TotalRatePerPayFrequency


FROM [Link]
GROUP BY PayFrequency
ORDER BY PayFrequency;

SELECT PayFrequency, SUM(Rate) AS TotalRatePerPayFrequency


FROM [Link]
GROUP BY PayFrequency
ORDER BY PayFrequency DESC;

SELECT ProductID, Shelf


FROM [Link]

SELECT ProductID, Shelf, SUM(Quantity)


FROM [Link]
GROUP BY ProductID, Shelf;

SELECT ProductID, Shelf, SUM(Quantity) AS SumPerProductPerShelf


FROM [Link]
GROUP BY ProductID, Shelf
ORDER BY shelf DESC;
=============================
Code used - sum() Function
SELECT *
FROM [Link];

SELECT SUM(StandardCost)
FROM [Link];

SELECT SUM(ALL StandardCost)


FROM [Link];

SELECT SUM(DISTINCT StandardCost)


FROM [Link];
=============
Code used - sum() Function
SELECT *
FROM [Link];

SELECT SUM(StandardCost)
FROM [Link];

SELECT SUM(ALL StandardCost)


FROM [Link];

SELECT SUM(DISTINCT StandardCost)


FROM [Link];
================
Code used - max() and min() functions
SELECT StandardCost
FROM [Link];

SELECT MAX(StandardCost) AS MaxStdCost


FROM [Link];

SELECT MakeFlag, MAX(StandardCost) AS MaxStdCost


FROM [Link]
GROUP BY MakeFlag;

SELECT MakeFlag, MAX(StandardCost) AS MaxStdCost


FROM [Link]
GROUP BY MakeFlag
ORDER BY MAX(StandardCost);

SELECT MIN(StandardCost)
FROM [Link];

SELECT MIN(StandardCost)
FROM [Link]
WHERE StandardCost > 0;

SELECT MIN(StandardCost) AS MinStdCost


FROM [Link]
WHERE StandardCost > 0;
================
Code used - Less used Numerical functions
SELECT ABS(-12345.45);

SELECT ABS(+12345.45);

SELECT ABS($12345.45);

SELECT StandardCost, CEILING(StandardCost)


FROM [Link];

SELECT StandardCost, CEILING(StandardCost)


FROM [Link]
WHERE StandardCost > 0;

SELECT StandardCost, FLOOR(StandardCost)


FROM [Link]
WHERE StandardCost > 0;

SELECT RAND()*5 + 3;
SELECT CEILING(RAND()*5 + 3);

SELECT ROUND(345.678,2);

SELECT ROUND(345.67856,3);
================
Code used - String Functions - Part 1
SELECT CHARINDEX('D','DAD');
SELECT CHARINDEX('D','DAD',2);

SELECT Name
FROM [Link]

SELECT Name, CHARINDEX('Cr', Name)


FROM [Link];

SELECT Name, CHARINDEX('Cr', Name)


FROM [Link]
WHERE Name LIKE '%Cr%';
============
Code used - String Functions - Part 2
SELECT FirstName,Datalength(FirstName) AS NumberOfBytes
FROM [Link]
GROUP BY FirstName;

SELECT FORMAT(20200616,'####/##/##');
=========
Code used - String Functions - Part 3
SELECT FirstName,LEFT(FirstName,2) AS ExtractNameFromLeft
FROM [Link];

SELECT FirstName,RIGHT(FirstName,2) AS ExtractNameFromRight


FROM [Link];

SELECT FirstName,LEN(FirstName) AS ExtractLengthOfName


FROM [Link];
========
Code used - String Functions - Part 4
SELECT FirstName, LOWER(FirstName) AS LowerFName
FROM [Link];

SELECT FirstName, UPPER(FirstName) AS LowerFName


FROM [Link];

SELECT Ltrim(' Trim Left Side');

SELECT rtrim('Trim Left Side ');

SELECT trim(' Trim Both The Sides ');


===========
Code used - String Functions - Part 5
SELECT Name, PATINDEX('%Ball%',Name)
FROM [Link]
WHERE Name LIKE '%Ball%';

SELECT Name, Patindex('%Cr_nk%',Name)


FROM [Link]
WHERE Name LIKE '%Cr%';
SELECT LoginID, REPLACE(LoginID,'adventure-works','aw') AS ReplaceString
FROM [Link];

SELECT FirstName, REPLICATE(FirstName,3) AS ReplicateFName


FROM [Link];
============
Code used - String Functions - Part 6
SELECT FirstName, REVERSE(FirstName) AS ReverseFName
FROM [Link];

SELECT Top 100 LastName, Substring(LastName,3,5) AS SubstringLName


FROM [Link]
ORDER BY LastName;

SELECT ProductID, STR(ProductID) AS StringProductID


FROM [Link];
=================
Code used - Date Functions - Part 1
SELECT ProductID, SellStartDate,dateadd(yyyy,3,sellstartdate) AS NewSellStartDate
FROM [Link];

SELECT ProductID, SellStartDate,dateadd(q,3,sellstartdate) AS NewSellStartDate


FROM [Link];

SELECT ProductID, SellStartDate,dateadd(m,3,sellstartdate) AS NewSellStartDate


FROM [Link];

SELECT ProductID, SellStartDate,dateadd(d,3,sellstartdate) AS NewSellStartDate


FROM [Link];

SELECT ProductID, SellStartDate,dateadd(hh,3,sellstartdate) AS NewSellStartDate


FROM [Link];

SELECT ProductID, SellStartDate,dateadd(n,50,sellstartdate) AS NewSellStartDate


FROM [Link];

SELECT ProductID, SellStartDate,dateadd(s,10,sellstartdate) AS NewSellStartDate


FROM [Link];

SELECT ProductID, SellStartDate,SellEndDate, dateadd(ms,50,sellstartdate) AS


NewSellStartDate
FROM [Link];

SELECT ProductID, SellStartDate,SellEndDate, dateadd(m,50,sellstartdate) AS


NewSellStartDate
FROM [Link];

SELECT ProductID, SellStartDate,SellEndDate,


datediff(yyyy,sellstartdate,sellenddate) AS diffDate
FROM [Link];

SELECT ProductID, SellStartDate,SellEndDate, datediff(q,sellstartdate,sellenddate)


AS diffDate
FROM [Link];

SELECT ProductID, SellStartDate,SellEndDate, datediff(q,sellstartdate,sellenddate)


AS diffDate
FROM [Link]
WHERE SellEndDate IS NOT NULL;
=================================
Code used - Date Functions - Part 2
SELECT ProductID, SellStartDate,datepart(month,sellstartdate) AS Extractmonth
FROM [Link];

SELECT ProductID, SellStartDate,day(sellstartdate) AS ExtractDay


FROM [Link];

SELECT ProductID, SellStartDate,month(sellstartdate) AS ExtractMonth


FROM [Link];

SELECT ProductID, SellStartDate,year(sellstartdate) AS ExtractYear


FROM [Link];
===========
Code used - Date Functions - Part 3
SELECT ProductID, SellStartDate,datename(month,sellstartdate) AS Extractmonth
FROM [Link];

SELECT ProductID, SellStartDate,eomonth(sellstartdate) AS EndOfMonth


FROM [Link];
============
Code used - Date Functions - Part 4
SELECT CURRENT_TIMESTAMP;
SELECT getdate();
SELECT sysdatetime();
SELECT SYSDATETIMEOFFSET();
==============
Code used - isdate(), isnull() and isnumeric() functions
SELECT SellStartDate, isdate(SellStartDate)
FROM [Link];

SELECT isdate(1) As booleanValue;

SELECT isnull(NULL,'Abc');

SELECT isnull('123','Abc');

SELECT isnull(NULL,0);

SELECT isnull(0,5);

SELECT isnull(' ',5);

SELECT Color, isnull(Color,'hello') as CheckforNull


FROM [Link];

SELECT StandardCost, isnumeric(StandardCost) AS testNumeric


FROM [Link];

SELECT Color, isnumeric(Color) AS testNumeric


FROM [Link];
===========
Code used - coalesce, nullif, convert, iif functions
SELECT coalesce(NULL,NULL,'Welcome',NULL,'SQL');

SELECT coalesce(NULL,NULL,NULL,NULL,'SQL');

SELECT Title, MiddleName, coalesce(title,middlename)


FROM [Link];

SELECT NULLIF(14,12);

SELECT StandardCost, convert(nvarchar(20),StandardCost) AS convertedType


FROM [Link];

SELECT ProductId,StandardCost, ListPrice,


iif(ListPrice>=StandardCost,'Profit','Loss') AS testCondition
FROM [Link]
WHERE Listprice < StandardCost;

SELECT ProductId,StandardCost, ListPrice,


iif(ListPrice>=StandardCost,'Profit','Loss') AS testCondition
FROM [Link];
--WHERE Listprice < StandardCost;

SELECT ProductId,StandardCost, ListPrice,


iif(ListPrice<StandardCost,'Profit','Loss') AS testCondition
FROM [Link];
--WHERE Listprice < StandardCost;
===============
Code used - coalesce, nullif, convert, iif functions
SELECT coalesce(NULL,NULL,'Welcome',NULL,'SQL');

SELECT coalesce(NULL,NULL,NULL,NULL,'SQL');

SELECT Title, MiddleName, coalesce(title,middlename)


FROM [Link];

SELECT NULLIF(14,12);

SELECT StandardCost, convert(nvarchar(20),StandardCost) AS convertedType


FROM [Link];

SELECT ProductId,StandardCost, ListPrice,


iif(ListPrice>=StandardCost,'Profit','Loss') AS testCondition
FROM [Link]
WHERE Listprice < StandardCost;

SELECT ProductId,StandardCost, ListPrice,


iif(ListPrice>=StandardCost,'Profit','Loss') AS testCondition
FROM [Link];
--WHERE Listprice < StandardCost;

SELECT ProductId,StandardCost, ListPrice,


iif(ListPrice<StandardCost,'Profit','Loss') AS testCondition
FROM [Link];
--WHERE Listprice < StandardCost;
================

Code used - Subquery - Part 1


SELECT NationalIDNumber, JobTitle, HireDate
FROM [Link]
WHERE BusinessEntityID IN
(SELECT BusinessEntityID
FROM [Link]
)
ORDER BY JobTitle;
SELECT BusinessEntityID,NationalIDNumber, JobTitle, HireDate
FROM [Link]
WHERE BusinessEntityID IN
(SELECT BusinessEntityID
FROM [Link]
)
ORDER BY JobTitle;

SELECT BusinessEntityID,NationalIDNumber, JobTitle, HireDate


FROM [Link]
WHERE BusinessEntityID IN
(SELECT BusinessEntityID
FROM [Link]
WHERE BusinessEntityID <= 100
)
ORDER BY JobTitle;
==============
Code used - Subquery - Part 2
SELECT Min(UnitPrice)
FROM [Link]
WHERE UnitPrice IN
(SELECT TOP 2 UnitPrice
FROM [Link]
ORDER BY UnitPrice DESC
);

SELECT Min(UnitPrice)
FROM [Link]
WHERE UnitPrice IN
(SELECT TOP 3 UnitPrice
FROM [Link]
ORDER BY UnitPrice DESC
);

SELECT Min(UnitPrice)
FROM [Link]
WHERE UnitPrice IN
(SELECT TOP 3 UnitPrice
FROM [Link]
GROUP BY UnitPrice
ORDER BY UnitPrice DESC
);

SELECT PurchaseOrderID, UnitPrice


FROM [Link]
WHERE UnitPrice >
(SELECT Avg(ListPrice)
FROM [Link]
);

SELECT PurchaseOrderID, UnitPrice


FROM [Link]
WHERE UnitPrice <
(SELECT Avg(ListPrice)
FROM [Link]
);
=================
Code used - Subquery - Part 2
SELECT Min(UnitPrice)
FROM [Link]
WHERE UnitPrice IN
(SELECT TOP 2 UnitPrice
FROM [Link]
ORDER BY UnitPrice DESC
);

SELECT Min(UnitPrice)
FROM [Link]
WHERE UnitPrice IN
(SELECT TOP 3 UnitPrice
FROM [Link]
ORDER BY UnitPrice DESC
);

SELECT Min(UnitPrice)
FROM [Link]
WHERE UnitPrice IN
(SELECT TOP 3 UnitPrice
FROM [Link]
GROUP BY UnitPrice
ORDER BY UnitPrice DESC
);

SELECT PurchaseOrderID, UnitPrice


FROM [Link]
WHERE UnitPrice >
(SELECT Avg(ListPrice)
FROM [Link]
);

SELECT PurchaseOrderID, UnitPrice


FROM [Link]
WHERE UnitPrice <
(SELECT Avg(ListPrice)
FROM [Link]
);
=====================

Code used - Subquery - Part 4


SELECT ProductSubCategoryID
FROM [Link]
WHERE ProductSubCategoryID IN
(SELECT ProductSubCategoryID
FROM [Link]
WHERE ProductCategoryID IN
(SELECT ProductCategoryID
FROM [Link]
WHERE Name LIKE '%Bikes%'
)
);

SELECT DISTINCT ProductSubCategoryID


FROM [Link]
WHERE ProductSubCategoryID IN
(SELECT ProductSubCategoryID
FROM [Link]
WHERE ProductCategoryID IN
(SELECT ProductCategoryID
FROM [Link]
WHERE Name LIKE '%Bikes%'
)
);

==========================================
Code used - Subquery - Part 5
SELECT PurchaseOrderId, TaxAmt,
(SELECT sum(OrderQty)
FROM [Link] AS pod
WHERE [Link] = [Link]
GROUP BY [Link]
) AS SumOrderQty
FROM [Link] AS poh
ORDER BY [Link];
=========
Code used - Subquery - Part 6
SELECT avg(minUnitPrice) AS avgMinUnitPricePerOrder
FROM
(SELECT PurchaseOrderID, min(UnitPrice) AS minUnitPrice
FROM [Link]
GROUP BY PurchaseOrderID
) AS minUnitPricePerOrder;

SELECT avg(minUnitPrice) AS avgMinUnitPricePerOrder


FROM
(SELECT min(UnitPrice) AS minUnitPrice
FROM [Link]
GROUP BY PurchaseOrderID
) AS minUnitPricePerOrder;
====================
Code used - Subquery - Part 7
SELECT PurchaseOrderId, DueDate, UnitPrice
FROM [Link]
WHERE DueDate >=
ANY (SELECT ORderDate
FROM [Link]);
=====================
Code used - Subquery - Part 8
SELECT PurchaseOrderId, DueDate, UnitPrice
FROM [Link]
WHERE DueDate =
ANY (SELECT ORderDate
FROM [Link]);

SELECT PurchaseOrderId, DueDate, UnitPrice


FROM [Link]

SELECT PurchaseOrderId, DueDate, UnitPrice


FROM [Link]
WHERE DueDate = '2011-05-14 00:00:00.000';

SELECT PurchaseOrderId, OrderDate


FROM [Link]
WHERE OrderDate = '2011-05-14 00:00:00.000';
=============
Code used - Subquery - Part 9
SELECT PurchaseOrderId, DueDate, UnitPrice
FROM [Link]
WHERE DueDate <>
ALL (SELECT ORderDate
FROM [Link]);

SELECT PurchaseOrderId, DueDate, UnitPrice


FROM [Link]
WHERE DueDate =
ANY (SELECT ORderDate
FROM [Link]);

SELECT PurchaseOrderId, DueDate, UnitPrice


FROM [Link]
WHERE DueDate >=
ANY (SELECT ORderDate
FROM [Link]);
======================
Code used - UNION and UNION All - Part 2
SELECT PurchaseOrderId
FROM [Link]
UNION
SELECT PurchaseOrderID
FROM [Link];

SELECT PurchaseOrderDetailID
FROM [Link]
UNION
SELECT TaxAmt
FROM [Link]
ORDER BY PurchaseOrderDetailID ASC;

SELECT TaxAmt
FROM [Link]
ORDER BY TaxAmt;
===========
Code used - UNION and UNION All - Part 3
SELECT BusinessEntityID
FROM [Link]
UNION
SELECT BusinessEntityID
FROM [Link]
UNION
SELECT BusinessEntityID
FROM [Link]
UNION
SELECT CustomerId
FROM [Link]
UNION
SELECT BusinessEntityID
FROM [Link];

SELECT NationalIDNumber
FROM [Link]
UNION
SELECT FirstName
FROM [Link];
====================
Code used - UNION and UNION All - Part 4
-- The query which is just below will not work because of incompatible data types
SELECT BusinessEntityID
FROM [Link]
UNION
SELECT FirstName
FROM [Link];

---The below query will not work because of ORDER BY clause


SELECT FirstName + MiddleName + LastName AS FullName
FROM [Link]
UNION
SELECT Name
FROM [Link]
ORDER BY FullName, Name;

SELECT FirstName + MiddleName + LastName AS FullName


FROM [Link]
UNION
SELECT Name
FROM [Link]
ORDER BY FullName;

SELECT Name
FROM [Link]
UNION
SELECT FirstName + MiddleName + LastName AS FullName
FROM [Link]
ORDER BY Name DESC;
===========
Code used - UNION and UNION All - Part 4
-- The query which is just below will not work because of incompatible data types
SELECT BusinessEntityID
FROM [Link]
UNION
SELECT FirstName
FROM [Link];

---The below query will not work because of ORDER BY clause


SELECT FirstName + MiddleName + LastName AS FullName
FROM [Link]
UNION
SELECT Name
FROM [Link]
ORDER BY FullName, Name;

SELECT FirstName + MiddleName + LastName AS FullName


FROM [Link]
UNION
SELECT Name
FROM [Link]
ORDER BY FullName;

SELECT Name
FROM [Link]
UNION
SELECT FirstName + MiddleName + LastName AS FullName
FROM [Link]
ORDER BY Name DESC;
==========
Code used - INTERSECT Operator
SELECT ProductSubCategoryID
FROM [Link]
INTERSECT
SELECT ProductSubCategoryID
FROM [Link];
=======
Code used - EXCEPT Operator
SELECT SalesOrderID
FROM [Link]
EXCEPT
SELECT CustomerID
FROM [Link];
=====================
Code used - Implicit Joins
SELECT [Link],
[Link],
[Link],
[Link],
[Link]
FROM [Link], [Link]
WHERE [Link]
=
[Link];

SELECT [Link],
[Link],
[Link],
[Link],
[Link]
FROM [Link] pod, [Link] poh
WHERE [Link]
=
[Link];

=======================
Code used - INNER JOIN - Part 1
SELECT [Link],
[Link],
[Link],
[Link],
[Link]
FROM [Link] pod
INNER JOIN
[Link] poh
ON [Link] = [Link];

SELECT [Link],
[Link],
[Link],
[Link],
[Link]
FROM [Link] pod
INNER JOIN
[Link] poh
ON [Link] = [Link]
WHERE year([Link]) = 2014;
SELECT [Link],
[Link],
[Link],
[Link],
[Link]
FROM [Link] pod
JOIN
[Link] poh
ON [Link] = [Link]
WHERE year([Link]) = 2014;

SELECT [Link],
[Link],
[Link],
[Link],
[Link]
FROM [Link] poh
INNER JOIN
[Link] pod
ON [Link] = [Link]
WHERE year([Link]) = 2014;
========
Code used - INNER JOIN - Part 1
SELECT [Link],
[Link],
[Link],
[Link],
[Link]
FROM [Link] pod
INNER JOIN
[Link] poh
ON [Link] = [Link];

SELECT [Link],
[Link],
[Link],
[Link],
[Link]
FROM [Link] pod
INNER JOIN
[Link] poh
ON [Link] = [Link]
WHERE year([Link]) = 2014;

SELECT [Link],
[Link],
[Link],
[Link],
[Link]
FROM [Link] pod
JOIN
[Link] poh
ON [Link] = [Link]
WHERE year([Link]) = 2014;

SELECT [Link],
[Link],
[Link],
[Link],
[Link]
FROM [Link] poh
INNER JOIN
[Link] pod
ON [Link] = [Link]
WHERE year([Link]) = 2014;
=============
Code used - LEFT JOIN - Part 2
SELECT [Link], [Link], [Link], [Link]
FROM [Link] c
LEFT JOIN
[Link] pcc
ON [Link] = [Link];

SELECT BusinessEntityId
FROM [Link]
ORDER BY BusinessEntityID ASC;

SELECT CustomerID
FROM [Link]
ORDER BY CustomerID ASC;

SELECT [Link], [Link], [Link], [Link]


FROM [Link] c
LEFT OUTER JOIN
[Link] pcc
ON [Link] = [Link];
====
Code used - LEFT JOIN - Part 3
SELECT [Link], [Link], [Link], [Link]
FROM [Link] c
LEFT OUTER JOIN
[Link] pcc
ON [Link] = [Link];

SELECT PErsonID
FROM [Link];

SELECT [Link], [Link], [Link], [Link]


FROM [Link] c
LEFT OUTER JOIN
[Link] pcc
ON [Link] = [Link]
WHERE [Link] IS NULL;

SELECT [Link], [Link], [Link], [Link]


FROM [Link] c
LEFT OUTER JOIN
[Link] pcc
ON [Link] = [Link]
WHERE [Link] IS NOT NULL;

SELECT [Link], [Link], [Link], [Link]


FROM [Link] pcc
LEFT OUTER JOIN
[Link] c
ON [Link] = [Link];
===============
Code used - RIGHT JOIN
SELECT [Link], [Link], [Link], [Link]
FROM [Link] c
RIGHT JOIN
[Link] pcc
ON [Link] = [Link];

SELECT CustomerId, StoreId


FROM [Link]
WHERE CustomerID IN (703, 705, 709);

SELECT [Link], [Link], [Link], [Link]


FROM [Link] pcc
LEFT JOIN
[Link] c
ON [Link] = [Link];
=============
Code used - FULL JOIN
SELECT [Link], [Link], [Link], [Link]
FROM [Link] pcc
FULL JOIN
[Link] c
ON [Link] = [Link];

SELECT [Link], [Link], [Link], [Link]


FROM [Link] pcc
FULL OUTER JOIN
[Link] c
ON [Link] = [Link];

==============
Code used - CROSS JOIN
SELECT count(*)
FROM [Link];

SELECT count(*)
FROM [Link];

SELECT * FROM [Link], [Link];

SELECT * FROM [Link] CROSS JOIN [Link];


=============================================
Code used - CREATE Database
CREATE DATABASE MyTestDb;

DROP Database Database123;

=====================
Code used - Create TABLE without constraints
CREATE TABLE Stores
(
StoreId INT IDENTITY,
StoreNumber Varchar(50),
PhoneNumber CHAR(14),
Email Varchar(50),
Address Varchar(120),
City Varchar(40),
State Varchar(10),
ZipCode INT
);
DROP TABLE [Link];

CREATE TABLE [Link]


(
StoreId INT IDENTITY,
StoreNumber Varchar(50),
PhoneNumber CHAR(14),
Email Varchar(50),
Address Varchar(120),
City Varchar(40),
State Varchar(10),
ZipCode INT
);
================
Code used - Create Table under retail Schema
CREATE TABLE [Link]
(
StoreId INT IDENTITY,
StoreNumber Varchar(50),
PhoneNumber CHAR(14),
Email Varchar(50),
Address Varchar(120),
City Varchar(40),
State Varchar(10),
ZipCode INT
);

DROP TABLE [Link];


======================
Code used - CREATE TABLE with NOT NULL Constraint
CREATE TABLE [Link]
(
StoreId INT IDENTITY,
StoreNumber Varchar(50) NOT NULL,
PhoneNumber CHAR(14) NOT NULL,
Email Varchar(50) NOT NULL,
Address Varchar(120) NOT NULL,
City Varchar(40) NOT NULL,
State Varchar(10) NOT NULL,
ZipCode INT NOT NULL
);
===========
Code used - CREATE TABLE with CHECK Constraint
CREATE TABLE CheckItems
(
ItemId INT IDENTITY,
ItemName Varchar(255) NOT NULL UNIQUE,
MinQty INT CHECK(MinQty >=10)
);

CREATE TABLE CheckItems1


(
ItemId INT IDENTITY,
ItemName Varchar(255) NOT NULL UNIQUE,
MinQty INT,
CONSTRAINT CHK_CheckItems1_MinQty_ItemName CHECK(MinQty >=10
AND ItemName <> 'General Store')
);
===============
Code used - CREATE TABLE with UNIQUE Constraint
CREATE TABLE [Link]
(
StoreId INT IDENTITY,
StoreNumber Varchar(50) NOT NULL,
PhoneNumber CHAR(14) NOT NULL,
Email Varchar(50) NOT NULL,
Address Varchar(120) NOT NULL,
City Varchar(40) NOT NULL,
State Varchar(10) NOT NULL,
ZipCode INT NOT NULL,
CONSTRAINT UK_UniqueStores_StoreId_StoreNumber UNIQUE (StoreId,StoreNumber)
);
============
Code used - CREATE TABLE with DEFAULT Constraint
CREATE TABLE [Link]
(
ItemId INT IDENTITY,
ItemName Varchar(255) NOT NULL DEFAULT 'General Stores',
MinQty INT
);

CREATE TABLE [Link]


(
StoreId INT NOT NULL,
StoreName Varchar(255) NOT NULL,
StoreOrderDate datetime DEFAULT getdate()
);
=========
Code used - CREATE TABLE with Primary Key
CREATE TABLE [Link]
(
StoreID INT PRIMARY KEY,
StoreName Varchar(40) NOT NULL,
ProdID INT NOT NULL
);

CREATE TABLE [Link]


(
StoreID INT,
StoreName Varchar(40) NOT NULL,
ProdID INT NOT NULL,
Threshold SMALLINT,
PRIMARY KEY (StoreID, ProdID)
);

CREATE TABLE [Link]


(
StoreID INT,
StoreName Varchar(40) NOT NULL,
ProdID INT NOT NULL,
Threshold SMALLINT,
CONSTRAINT PK_StoreProds PRIMARY KEY (StoreID, ProdID)
);
===============
Code used - Foreign Key - Part 2
CREATE TABLE [Link]
(
ItemId INT IDENTITY PRIMARY KEY,
ItemQty INT NOT NULL,
ItemStatus BIT
);

CREATE TABLE [Link]


(
ProductID INT IDENTITY,
ProductName Varchar(40) NOT NULL,
ProductCode CHAR(10) NOT NULL,
ProductStdCost Money NOT NULL,
ProductDescription Varchar,
ProductListPrice Money NOT NULL,
CONSTRAINT FK_ProductDetails_ItemStatus_ItemId FOREIGN KEY (ProductID)
REFERENCES [Link] (ItemId)
);

CREATE TABLE retail.ProductDetails1


(
ProductID INT IDENTITY PRIMARY KEY,
ProductName Varchar(40) NOT NULL,
ProductCode CHAR(10) NOT NULL,
ProductStdCost Money NOT NULL,
ProductDescription Varchar,
ProductListPrice Money NOT NULL,
CONSTRAINT FK_ProductDetails1_ItemStatus_ItemId FOREIGN KEY (ProductID)
REFERENCES [Link] (ItemId)
);
=============
Code used - Foreign Key - Part 3
CREATE TABLE [Link]
(
StoreID INT,
StoreName Varchar(40) NOT NULL,
ProdID INT NOT NULL UNIQUE,
Threshold SMALLINT,
CONSTRAINT PK_StoreProdsUK PRIMARY KEY (StoreID, ProdID)
);

CREATE TABLE [Link]


(
ProductID INT IDENTITY,
ProductName Varchar(40) NOT NULL,
DemandQty INT,
CONSTRAINT FK_ProductDemandsUK_StoreProdsUK_ProdID FOREIGN KEY (ProductID)
REFERENCES [Link](ProdID)
);

===========
Code used - UNIQUE INDEX with Primary and Foreign Key
CREATE TABLE [Link]
(
StoreID INT,
StoreName Varchar(40) NOT NULL,
ProdID INT NOT NULL,
Threshold SMALLINT,
CONSTRAINT PK_StoreProds PRIMARY KEY (StoreID, ProdID)
);
CREATE UNIQUE INDEX IDX_StoreProds_ProdID ON [Link](ProdID);

CREATE TABLE [Link]


(
ProductID INT IDENTITY,
ProductName Varchar(40) NOT NULL,
DemandQty INT,
CONSTRAINT FK_ProductDemands_StoreProds_ProdID FOREIGN KEY (ProductID)
REFERENCES [Link](ProdID)
);
==========
Code used - INDEX
CREATE INDEX IDX_CheckItems_ItemName ON CheckItems(ItemName);

CREATE INDEX IDX_CheckItems_ItemId_ItemName ON CheckItems(ItemId, ItemName);


=================================

You might also like