USE AdventureWorks2014;
SELECT @@VERSION;
select DB_NAME();
SELECT ORIGINAL_LOGIN() AS Orignal, CURRENT_USER AS CURRENTUSER,
SYSTEM_USER AS SYSTEMUSER,SUSER_NAME() AS SUSERNAME, USER_NAME() AS USERNAME;
--EXECUTE AS
--ORIGINAL_LOGIN() sprawdza pierwsze zalogowanie
--CURRENT_USER,SYSTEM_USER,SYSTEM_USER można oszukać przez impersonacje
--Create two temporary principals
CREATE LOGIN login1 WITH PASSWORD = 'J345#$)thb';
CREATE LOGIN login2 WITH PASSWORD = 'Uor80$23b';
GO
CREATE USER user1 FOR LOGIN login1;
CREATE USER user2 FOR LOGIN login2;
GO
--Give IMPERSONATE permissions on user2 to user1
--so that user1 can successfully set the execution context to user2.
GRANT IMPERSONATE ON USER:: user2 TO user1;
GO
--Display current execution context.
SELECT ORIGINAL_LOGIN() AS Orignal, CURRENT_USER AS CURRENTUSER,
SYSTEM_USER AS SYSTEMUSER,SUSER_NAME() AS SUSERNAME, USER_NAME()AS USERNAME;
-- Set the execution context to login1.
EXECUTE AS LOGIN = 'login1';
--Verify the execution context is now login1.
SELECT ORIGINAL_LOGIN() AS Orignal, CURRENT_USER AS CURRENTUSER,
SYSTEM_USER AS SYSTEMUSER,SUSER_NAME() AS SUSERNAME, USER_NAME() AS USERNAME;
--Login1 sets the execution context to login2.
EXECUTE AS USER = 'user2';
--Display current execution context.
SELECT ORIGINAL_LOGIN() AS Orignal, CURRENT_USER AS CURRENTUSER,
SYSTEM_USER AS SYSTEMUSER,SUSER_NAME() AS SUSERNAME, USER_NAME() AS USERNAME;
-- The execution context stack now has three principals: the originating caller, login1 and login2.
--The following REVERT statements will reset the execution context to the previous context.
REVERT;
--Display current execution context.
SELECT ORIGINAL_LOGIN() AS Orignal, CURRENT_USER AS CURRENTUSER,
SYSTEM_USER AS SYSTEMUSER,SUSER_NAME() AS SUSERNAME, USER_NAME() AS USERNAME;
REVERT;
--Display current execution context.
SELECT ORIGINAL_LOGIN() AS Orignal, CURRENT_USER AS CURRENTUSER,
SYSTEM_USER AS SYSTEMUSER,SUSER_NAME() AS SUSERNAME, USER_NAME() AS SUSERNAME;
--Remove temporary principals.
DROP LOGIN login1;
DROP LOGIN login2;
DROP USER user1;
DROP USER user2;
SELECT NationalIDNumber,
LoginID,
JobTitle
FROM HumanResources.Employee;
SELECT Title, FirstName, LastName
FROM Person.Person
WHERE Title = 'Ms.';
SELECT Title, FirstName, LastName
FROM Person.Person
WHERE Title = 'Ms.' AND LastName = 'Antrim';
SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'HumanResources';
SELECT BusinessEntityID AS 'ID Pracownika',
VacationHours AS 'Wakacje',
SickLeaveHours AS "Chorobowe"
FROM HumanResources.Employee;
SELECT BusinessEntityID AS 'ID Pracownika',
VacationHours AS 'Wakacje',
SickLeaveHours AS "Chorobowe"
FROM HumanResources.Employee AS e
SELECT BusinessEntityID AS "EmployeeID",
VacationHours + SickLeaveHours AS "Czas wolny"
FROM HumanResources.Employee
SELECT Title, FirstName, LastName
FROM Person.Person
WHERE NOT (Title = 'Ms.' OR Title = 'Mr.')
SELECT Title, FirstName, LastName
FROM Person.Person
WHERE Title = 'Ms.' AND
(FirstName = 'Catherine' OR
FirstName = 'Margaret' OR
LastName = 'Adams' OR LastName = 'Smith');
SELECT TOP(1) 1
FROM HumanResources.Employee
WHERE SickLeaveHours > 70;
SELECT 'Prawda'
WHERE EXISTS (
SELECT *
FROM HumanResources.Employee
WHERE SickLeaveHours > 40
);
SELECT SalesOrderID, ShipDate
FROM Sales.SalesOrderHeader
WHERE ShipDate BETWEEN '2011-07-03 00:00:00.0'
AND '2011-07-04 23:59:59.0';
SELECT SalesOrderID, ShipDate
FROM Sales.SalesOrderHeader
WHERE ShipDate >= '2011-07-03'
AND ShipDate < '2011-07-04';
SELECT SalesOrderID, ShipDate
FROM Sales.SalesOrderHeader
WHERE ShipDate BETWEEN '2011-07-03 00:00:00.0'
AND '2011-07-03 23:59:59.0';
SELECT SalesOrderID, ShipDate
FROM Sales.SalesOrderHeader
WHERE ShipDate >= '2011-07-03'
AND ShipDate < '2011-07-04';
SELECT ProductID, Name, Weight
FROM Production.Product
WHERE Weight IS NULL;
SELECT ProductID, Name, Weight
FROM Production.Product
WHERE Weight IS NULL;
SELECT 1
WHERE NULL = NULL
SELECT 1
WHERE NULL <> NULL
SELECT ProductID, Name, Color
FROM Production.Product
WHERE Color IN ('Blue', 'Black','Red');
--WHERE Color IN ('Blue', 'Black', 'Red')
--WHERE Color = 'Blue' OR Color = 'Black' OR Color = 'Red'
SELECT ProductID, Name, Color
FROM Production.Product
WHERE Color NOT IN ('Kaszubski', 'Wojonwnik');
SELECT ProductID, Name, Color
FROM Production.Product
WHERE Color NOT IN ('Kaszubski',NULL);
--NOT IN jest kiepskim pomysłem
SELECT ProductID, Name
FROM Production.Product
WHERE Name LIKE 'B%';
SELECT ProductID, Name
FROM Production.Product
WHERE Name LIKE '%/%%' ESCAPE '/'
SELECT h.EndDate,h.StartDate, h.ListPrice
FROM Production.ProductListPriceHistory h
ORDER BY h.EndDate, h.ListPrice
SELECT h.EndDate,h.StartDate, h.ListPrice
FROM Production.ProductListPriceHistory h
ORDER BY h.EndDate ASC, h.ListPrice DESC
UPDATE [Production].[Product]
SET [Name] = 'HL MOUNTAIN FRAME - Black, 42'
WHERE Name = 'HL Mountain Frame - Black, 42'
SELECT p.Name, p.ListPrice
FROM Production.Product p
WHERE p.Name LIKE 'HL Mountain Frame%'
ORDER BY p.Name COLLATE Latin1_General_BIN ASC
SELECT Name, Description
FROM fn_helpcollations();
SELECT Name, Description
FROM fn_helpcollations()
where NAME Like 'Pol%'
SELECT ProductID, Name, Weight,
ISNULL(Weight, 1)
FROM Production.Product
ORDER BY ISNULL(Weight, 1) DESC, Weight;
SELECT ProductID, Name, Weight,
IIF(Weight IS NULL, 1, 0)
FROM Production.Product
ORDER BY IIF(Weight IS NULL, 1, 0), Weight;
SELECT ProductID, Name
FROM Production.Product
ORDER BY Name
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
SELECT ProductID, Name
FROM Production.Product
ORDER BY Name
OFFSET 8 ROWS FETCH NEXT 10 ROWS ONLY;
SELECT ProductID, Name
FROM Production.Product
ORDER BY Name
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
SELECT ProductID, Name
FROM Production.Product
ORDER BY Name
OFFSET 8 ROWS FETCH NEXT 10 ROWS ONLY;
-- a co jeśli nowy rekord sie pojawił ?
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
/* Queries go here */
COMMIT;
/* Return to default */
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT *
FROM Purchasing.PurchaseOrderHeader
TABLESAMPLE (5 PERCENT);
--Nie koniecznie jest to 5 procent
SELECT *
FROM Purchasing.PurchaseOrderHeader
REPEATABLE TABLESAMPLE (5 PERCENT);
SELECT *
FROM Purchasing.PurchaseOrderHeader
TABLESAMPLE (200 ROWS);
--Nie koniecznie jest to 200 rekordów
SELECT * FROM Purchasing.PurchaseOrderHeader
WHERE (ABS(CAST(
(BINARY_CHECKSUM(*) *
RAND()) as int)) % 100) < 10
SQLCMD -e -s CEZMSI
-i D:\PanNiebieski\Projekty\SCRIPT.sql
-o D:\PanNiebieski\Projekty\output.txt
USE AdventureWorks2014;
SELECT TOP (10) [ProductID]
,[Name]
,[ProductNumber]
,[Color]
FROM [AdventureWorks2014].[Production].[Product]
GO
DECLARE @AddressLine1 NVARCHAR(60);
DECLARE @AddressLine2 NVARCHAR(60);
SELECT @AddressLine1 = AddressLine1, @AddressLine2 = AddressLine2
FROM Person.Address
WHERE AddressID = 66;
--To zwróci rekordy
SELECT @AddressLine1 AS Address1, @AddressLine2 AS Address2;
DECLARE @AddressLine1 NVARCHAR(60) = ''
DECLARE @AddressLine2 NVARCHAR(60) = ''
SELECT @AddressLine1 = AddressLine1, @AddressLine2 = AddressLine2
FROM Person.Address
WHERE AddressID = 156;
IF @@ROWCOUNT = 1
SELECT @AddressLine1, @AddressLine2
ELSE IF @@ROWCOUNT > 1
SELECT 'Znaleziono więcej niż 1 rekord';
ELSE
SELECT 'Brak rekordów';
DECLARE @totalviews INT
SELECT @totalviews = CAST(RAND() * 10 + 1 as INT)
print(@totalviews)
--Losowa liczba od 1 do 11
DECLARE @AddressLine1 NVARCHAR(60);
DECLARE @AddressLine2 NVARCHAR(60);
DECLARE @OneLine NVARCHAR(120);
SELECT @AddressLine1 = AddressLine1, @AddressLine2 = AddressLine2
FROM Person.Address
WHERE AddressID = 66;
SET @OneLine = @AddressLine1 + '; ' + @AddressLine2;
SELECT @OneLine;
DECLARE @QuerySelector int = 2;
IF @QuerySelector = 1
BEGIN
SELECT TOP 3 ProductID, Name, Color
FROM Production.Product
WHERE Color = 'Silver'
ORDER BY Name;
END
ELSE
BEGIN
SELECT TOP 3 ProductID, Name, Color
FROM Production.Product
WHERE Color = 'Black'
ORDER BY Name;
END;
IF EXISTS (
SELECT * FROM Production.Product
WHERE Color = 'Silver')
BEGIN
SELECT TOP 3 ProductID, Name, Color
FROM Production.Product
WHERE Color = 'Silver'
ORDER BY Name;
END
ELSE
BEGIN
SELECT TOP 3 ProductID, Name, Color
FROM Production.Product
WHERE Color = 'Black'
ORDER BY Name;
END;
DECLARE @Name nvarchar(50) = 'Engineering';
DECLARE @GroupName nvarchar(50) = 'Research and Development';
DECLARE @Exists bit = 0;
IF EXISTS (
SELECT Name
FROM HumanResources.Department
WHERE Name = @Name)
BEGIN
SET @Exists = 1;
GOTO SkipInsert;
END;
INSERT INTO HumanResources.Department
(Name, GroupName)
VALUES(@Name , @GroupName);
SkipInsert: IF @Exists = 1
BEGIN
PRINT @Name + ' already exists in HumanResources.Department';
END
ELSE
BEGIN
PRINT 'Row added';
END;
BEGIN TRY
ALTER TABLE Production.Product
DROP CONSTRAINT FK_Trap_Color;
END TRY
BEGIN CATCH
PRINT 'Ignore this failure.';
END CATCH;
GO
BEGIN TRY
DROP TABLE Production.TrapColor;
END TRY
BEGIN CATCH
PRINT 'Ignore this failure.';
END CATCH;
GO
CREATE TABLE Production.TrapColor (
Color NVARCHAR(15),
CONSTRAINT PK_TrapColor_Color
PRIMARY KEY (Color)
);
GO
BEGIN TRY
INSERT INTO Production.TrapColor (Color)
SELECT DISTINCT Color
FROM Production.Product;
END TRY
BEGIN CATCH
PRINT 'Fail!';
DROP TABLE Production.TrapColor;
THROW;
END CATCH;
GO
echo off
SQLCMD -e -s CEZMSI -i "D:\PanNiebieski\Sync\Praca\SQLQueriesforSQLSERVER\2-7.sql" -b -o
"D:\PanNiebieski\Sync\Praca\SQLQueriesforSQLSERVER\2-7output.txt"
if errorlevel 1 goto script_failure
echo "Jest okej"
exit
:script_failure
echo "Nie wykonał się Skrypt"
IF NOT EXISTS
(SELECT ProductID
FROM Production.Product
WHERE Color = 'Pink')
BEGIN
RETURN;
END;
SELECT ProductID
FROM Production.Product
WHERE Color = 'Pink';
--bez niczego
CREATE OR ALTER PROCEDURE ReportPink AS
IF NOT EXISTS
(SELECT ProductID
FROM Production.Product
WHERE Color = 'Pink')
BEGIN
--Zwróćmy 404 jako informacje, że nie ma koloru różowego
RETURN 404;
END;
DECLARE @ResultStatus int;
EXEC @ResultStatus = ReportPink;
PRINT @ResultStatus;
SELECT OBJECT_NAME(OBJECT_ID) AS TableName,
'Index' =
CASE
WHEN index_id = 0 THEN 'HEAP'
WHEN index_id = 1 THEN 'Clustered index.'
WHEN index_id > 1 THEN 'Nonclustered index'
ELSE 'ERROR'
END,
used_page_count,
reserved_page_count,
row_count
FROM sys.dm_db_partition_stats
SELECT DepartmentID, Name,
CASE
WHEN Name = 'Research and Development' THEN 'Room A'
WHEN (Name = 'Sales and Marketing' OR DepartmentID = 10) THEN 'Room B'
WHEN Name LIKE 'T%'THEN 'Room C'
ELSE 'Room D' END AS ConferenceRoom
FROM HumanResources.Department;
-- Declare variables
DECLARE @AWTables TABLE (SchemaTable varchar(100));
DECLARE @TableName varchar(100);
-- Insert table names into the table variable
INSERT @AWTables (SchemaTable)
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA + '.' + TABLE_NAME;
-- Report on each table using sp_spaceused
WHILE (SELECT COUNT(*) FROM @AWTables) > 0
BEGIN
SELECT TOP 1 @TableName = SchemaTable
FROM @AWTables
ORDER BY SchemaTable;
EXEC sp_spaceused @TableName;
DELETE @AWTables
WHERE SchemaTable = @TableName;
END;
DECLARE @I INT
SET @I = 0
WHILE (@I < 18000)
BEGIN
END;
DECLARE @I INT
SET @I = 0
WHILE (@I < 18000)
BEGIN
DECLARE @j INT = FLOOR(RAND(CHECKSUM(NEWID())) * 18008)
UPDATE [dbo].[SuperHeroes_ColumnIndex]
SET [PowerLevel] = RAND(CHECKSUM(NEWID()))*500
WHERE Id = @j;
END;
WHILE (1=1)
BEGIN
PRINT 'Endless While, because 1 always equals 1.';
IF 1=1
BEGIN
PRINT 'But we won''t let the endless loop happen!';
BREAK; --Because this BREAK statement terminates the loop.
END;
END;
DECLARE @n int = 1;
WHILE @n = 1
BEGIN
SET @n = @n + 1;
IF @n > 1
CONTINUE;
PRINT 'You will never see this message.';
END;
WAITFOR DELAY '00:00:10';
BEGIN
SELECT TransactionID, Quantity
FROM Production.TransactionHistory;
END;
WAITFOR TIME '20:22:00';
BEGIN
SELECT COUNT(*)
FROM Production.TransactionHistory;
END;
-- Do not show rowcounts in the results
SET NOCOUNT ON;
DECLARE @session_id smallint;
-- Declare the cursor
DECLARE session_cursor CURSOR FORWARD_ONLY READ_ONLY FOR
SELECT session_id
FROM sys.dm_exec_requests
WHERE status IN ('runnable', 'sleeping', 'running');
-- Open the cursor
OPEN session_cursor;
-- Retrieve one row at a time from the cursor
FETCH NEXT
FROM session_cursor
INTO @session_id;
-- Process and retrieve new rows until no more are available
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Spid #: ' + STR(@session_id);
EXEC ('sp_who ' + @session_id);
FETCH NEXT
FROM session_cursor
INTO @session_id;
END;
-- Close the cursor
CLOSE session_cursor;
-- Deallocate the cursor
DEALLOCATE session_cursor;
NULL + 10 = NULL
NULL AND TRUE = NULL
NULL OR FALSE = NULL
SELECT h.SalesOrderID,
h.CreditCardApprovalCode,
CreditApprovalCode_Display =
ISNULL(h.CreditCardApprovalCode,'**BRAK ZGODY**')
FROM Sales.SalesOrderHeader h
WHERE h.SalesOrderID BETWEEN 43736 AND 43740;
SELECT ISNULL(CAST(NULL AS CHAR(10)), 'UNKNOW')
SELECT ISNULL(CAST('Test' AS CHAR(10)), 'UNKNOW')
SELECT ISNULL(CAST(NULL AS INT), 'UNKNOW') ;
SELECT c.CustomerID,
SalesPersonPhone = spp.PhoneNumber,
CustomerPhone = pp.PhoneNumber,
PhoneNumber = COALESCE(pp.PhoneNumber, spp.PhoneNumber, '**BRAK TELEFONU*')
FROM Sales.Customer c
LEFT OUTER JOIN Sales.Store s
ON c.StoreID = s.BusinessEntityID
LEFT OUTER JOIN Person.PersonPhone spp
ON s.SalesPersonID = spp.BusinessEntityID
LEFT OUTER JOIN Person.PersonPhone pp
ON c.CustomerID = pp.BusinessEntityID
ORDER BY CustomerID
USE demo;
CREATE TABLE [dbo].[PeopleNames](
[Id] [int] NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[MiddleName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_PeopleNames] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO [dbo].[PeopleNames]
([Id]
,[FirstName]
,[MiddleName]
,[LastName])
VALUES
(1,'Cezary',NULL,'Walenciuk'),
(2,'Stefan','Viktor','Kowalski'),
(3,'Franko','Cyryl','Domanowski'),
(4,'Zdzisław',NULL,'Bohaterowicz')
CREATE OR ALTER PROCEDURE [dbo].[uspReadPeopleNames]
@FirstName VARCHAR(50) = NULL,
@LastName VARCHAR(50) = NULL,
@MiddleName VARCHAR(50) = NULL
AS
BEGIN
SELECT [Id]
,[FirstName]
,[MiddleName]
,[LastName]
FROM [dbo].[PeopleNames]
WHERE
[FirstName] LIKE COALESCE(@FirstName,[FirstName]) OR
[LastName] LIKE COALESCE(@LastName,[LastName]) OR
(([MiddleName] IS NULL AND @MiddleName IS NULL)
OR ([MiddleName] LIKE @MiddleName)
OR ([MiddleName] IS NOT NULL AND @MiddleName IS NULL))
END
DECLARE @return_value int
EXEC @return_value = [dbo].[uspReadPeopleNames]
@FirstName = N'F%',
@LastName = N'W%',
@MiddleName = NULL
SELECT 'Return Value' = @return_value
DECLARE @value INT = NULL;
SELECT CASE WHEN @value = NULL THEN 1
WHEN @value <> NULL THEN 2
WHEN @value IS NULL THEN 3
ELSE 4
END ;
SELECT TOP 5
LastName, FirstName, MiddleName
FROM Person.Person
WHERE MiddleName IS NULL ;
SELECT r.ProductID,
r.OperationSequence,
StartDateVariance =
AVG(DATEDIFF(day, ScheduledStartDate,ActualStartDate)),
StartDateVariance_Adjusted =
AVG(NULLIF(DATEDIFF(day,ScheduledStartDate,ActualStartDate), 0))
FROM Production.WorkOrderRouting r
WHERE r.ProductID BETWEEN 514 AND 520
GROUP BY r.ProductID,
r.OperationSequence
ORDER BY r.ProductID,
r.OperationSequence ;
USE tempdb;
CREATE TABLE dbo.Movie
(
MovieId INT NOT NULL
CONSTRAINT PK_Movie PRIMARY KEY CLUSTERED,
MovieName NVARCHAR(50) NOT NULL,
MovieCode NVARCHAR(50) NULL,
) ;
GO
CREATE UNIQUE INDEX UX_Movie_MovieCode ON dbo.Movie (MovieCode) ;
GO
INSERT INTO dbo.Movie (MovieId, MovieName,MovieCode) VALUES (1, 'M 1', 'a2q321');
INSERT INTO dbo.Movie (MovieId, MovieName,MovieCode) VALUES (2, 'M 2', 'a3232');
INSERT INTO dbo.Movie (MovieId, MovieName,MovieCode) VALUES (3, 'M 3', NULL);
INSERT INTO dbo.Movie (MovieId, MovieName,MovieCode) VALUES (4, 'M 4', NULL);
--Cannot insert duplicate key row in object 'dbo.Movie' with unique index 'UX_Movie_MovieCode'.
--The duplicate key value is (NULL).
DROP INDEX dbo.Movie.UX_Movie_MovieCode;
GO;
CREATE UNIQUE INDEX UX_Movie_MovieCode ON dbo.Movie(MovieCode) WHERE MovieCode IS NOT NULL
GO;
INSERT INTO dbo.Movie (MovieId, MovieName,MovieCode) VALUES (4, 'M 3', 'L9621');
INSERT INTO dbo.Movie (MovieId, MovieName,MovieCode) VALUES (5, 'M 4', 'N3322');
INSERT INTO dbo.Movie (MovieId, MovieName,MovieCode) VALUES (6, 'M 6', NULL);
CREATE TABLE dbo.Test01
(
TestValue NVARCHAR(10) NULL
);
CREATE TABLE dbo.Test02
(
TestValue NVARCHAR(10) NULL
) ;
GO
INSERT INTO dbo.Test01
VALUES ('C#'),
('SQL'),
(NULL),
('JavaScript'),
(NULL);
INSERT INTO dbo.Test02
VALUES (NULL),
('SQL'),
('C#'),
(NULL) ;
GO
SELECT t1.TestValue,
t2.TestValue
FROM dbo.Test01 t1
INNER JOIN dbo.Test02 t2
ON t1.TestValue = t2.TestValue ;
SELECT PersonPhone.BusinessEntityID,
FirstName,
LastName,
PhoneNumber
FROM Person.Person
INNER JOIN Person.PersonPhone
ON Person.BusinessEntityID = PersonPhone.BusinessEntityID
ORDER BY LastName,
FirstName,
Person.BusinessEntityID;
SELECT p.Name,
s.DiscountPct
FROM Sales.SpecialOffer s
INNER JOIN Sales.SpecialOfferProduct o
ON s.SpecialOfferID = o.SpecialOfferID
INNER JOIN Production.Product p
ON o.ProductID = p.ProductID
WHERE p.Name = 'All-Purpose Bike Stand';
SELECT s.CountryRegionCode,
s.StateProvinceCode,
t.TaxType,
t.TaxRate
FROM Person.StateProvince s
LEFT OUTER JOIN Sales.SalesTaxRate t
ON s.StateProvinceID = t.StateProvinceID;
CREATE TABLE [dbo].[Region](
[RegionId] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
CONSTRAINT [PK_Region] PRIMARY KEY CLUSTERED
(
[RegionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Country](
[CountryId] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[RegionId] [int] NULL,
CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED
(
[CountryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[Country] WITH CHECK ADD CONSTRAINT [FK_Country_Region] FOREIGN KEY([RegionId])
REFERENCES [dbo].[Region] ([RegionId])
GO
ALTER TABLE [dbo].[Country] CHECK CONSTRAINT [FK_Country_Region]
GO
INSERT INTO [dbo].[Region]
([RegionId]
,[Name])
VALUES
(1,'Europe'),(2,'Asia'),(3,'Africa'),
(4,'South America'),(5,'North America'),(6,'Australia')
INSERT INTO [dbo].[Country]
([CountryId]
,[Name]
,[RegionId])
VALUES
(1,'Poland',1),(2,'Japan',2),
(3,'Algeria',3),(4,'Argentina',4),
(5,'New Zealand',NULL),(6,'Federated States of Micronesia',NULL)
SELECT t1.Name AS Country,t2.Name AS Region FROM dbo.Country AS t1
INNER JOIN dbo.Region AS t2 ON t2.RegionId = t1.RegionId
SELECT t1.Name,t2.Name FROM dbo.Country AS t1
LEFT OUTER JOIN dbo.Region AS t2 ON t2.RegionId = t1.RegionId
SELECT t1.Name AS Country,t2.Name AS Region FROM dbo.Country AS t1
RIGHT OUTER JOIN dbo.Region AS t2 ON t2.RegionId = t1.RegionId
SELECT t1.Name AS Country,t2.Name AS Region FROM dbo.Country AS t1
FULL OUTER JOIN dbo.Region AS t2 ON t2.RegionId = t1.RegionId
SELECT t1.Name AS Country,t2.Name AS Region FROM dbo.Country AS t1
LEFT OUTER JOIN dbo.Region AS t2 ON t2.RegionId = t1.RegionId
WHERE t2.RegionId IS NULL
SELECT t1.Name AS Country,t2.Name AS Region FROM dbo.Country AS t1
RIGHT OUTER JOIN dbo.Region AS t2 ON t2.RegionId = t1.RegionId
WHERE t1.RegionId IS NULL
SELECT t1.Name AS Country,t2.Name AS Region FROM dbo.Country AS t1
FULL OUTER JOIN dbo.Region AS t2 ON t2.RegionId = t1.RegionId
WHERE t1.RegionId IS NULL OR t2.RegionId IS NULL
SELECT t1.Name AS Country,t2.Name AS Region FROM dbo.Country AS t1
CROSS JOIN dbo.Region AS t2
--Alternatywnie
SELECT t1.Name AS Country,t2.Name AS Region
FROM dbo.Country AS t1, dbo.Region AS t2
SELECT t1.Name FROM dbo.Country AS t1
INNER JOIN (SELECT r.RegionId
FROM dbo.Region as r
WHERE r.Name = 'Europe' OR r.Name ='ASIA'
)
as d ON t1.RegionId = d.RegionId;
SELECT DATEDIFF(MONTH,'2011-03-30','2011-12-30')
SELECT DATEADD(MONTH,1,'2011-12-30')
SELECT DATENAME(DAYOFYEAR,'2011-12-30')
SELECT DATENAME(MONTH,'2011-12-30')
SELECT DATENAME(MONTH,DATEADD(MONTH,DATEDIFF(MONTH,'2011-03-30','2011-12-30'),'2011-12-30'))
SELECT DATENAME(DAYOFYEAR,DATEADD(MONTH,DATEDIFF(MONTH,'2011-03-30','2011-12-30'),'2011-12-30'))
SELECT
DATENAME(MONTH,DATEADD(MONTH,DATEDIFF(MONTH,'2011-03-30',OrderDate),'2011-12-30')) AS Mth,
SUM(TotalDue) AS Total
FROM Sales.SalesOrderHeader
WHERE OrderDate>='20110101'
AND OrderDate<'20140101'
GROUP BY DATEADD(MONTH,DATEDIFF(MONTH,'2011-03-30',OrderDate),'2011-12-30')
ORDER BY DATEADD(MONTH,DATEDIFF(MONTH,'2011-03-30',OrderDate),'2011-12-30')
SELECT
DATENAME(MONTH,DATEADD(MONTH,DATEDIFF(MONTH,'2011-03-30',OrderDate),'2011-12-30')) AS Mth,
DATENAME(DAYOFYEAR,DATEADD(MONTH,DATEDIFF(MONTH,'2011-03-30',OrderDate),'2011-12-30')) AS dOFYEAR,
SUM(TotalDue) AS Total
FROM Sales.SalesOrderHeader
WHERE OrderDate>='20110101'
AND OrderDate<'20140101'
GROUP BY DATEADD(MONTH,DATEDIFF(MONTH,'2011-03-30',OrderDate),'2011-12-30')
ORDER BY
CONVERT(INT,SUBSTRING(
DATENAME(DAYOFYEAR,DATEADD(MONTH,DATEDIFF(MONTH,'2011-03-30',OrderDate),'2011-12-30')),
PATINDEX('%[0-9]%',
DATENAME(DAYOFYEAR,DATEADD(MONTH,DATEDIFF(MONTH,'2011-03-30',OrderDate),'2011-12-30'))
),LEN(DATENAME(DAYOFYEAR,DATEADD(MONTH,DATEDIFF(MONTH,'2011-03-30',OrderDate),'2011-12-30')))))
------ Numerical Sort
CONVERT(INT,SUBSTRING(NumStr,PATINDEX('%[0-9]%',NumStr),LEN(NumStr)))
SELECT t1.*, t2o.*
FROM t1
CROSS APPLY
(
SELECT TOP 3 *
FROM t2
WHERE t2.t1_id = t1.id
ORDER BY
t2.rank DESC
) t2o
SELECT r.*, t2o.*
FROM Region AS R
CROSS APPLY
(
SELECT TOP 3 *
FROM Country AS c
WHERE c.RegionId = r.RegionId
ORDER BY
c.CountryId DESC
) t2o
SELECT r.*, t2o.LOL
FROM Region AS R
CROSS APPLY
(
SELECT TOP(10)
RegionId + 120 AS LOL
FROM Country AS c
WHERE c.RegionId = r.RegionId
ORDER BY
c.CountryId DESC
) t2o
SELECT
DATENAME(MONTH,FirstDayOfMth) AS Mth,
DATENAME(DAYOFYEAR,FirstDayOfMth) AS dOFYEAR,
SUM(TotalDue) AS Total
FROM Sales.SalesOrderHeader
CROSS APPLY (
SELECT
DATEADD(MONTH,DATEDIFF(MONTH,'2011-03-30',OrderDate),'2011-12-30')
AS FirstDayOfMth,
) F_Mth
WHERE OrderDate>='20110101'
AND OrderDate<'20140101'
group by FirstDayOfMth
order by DATENAME(DAYOFYEAR,FirstDayOfMth);
SELECT s.PurchaseOrderNumber
FROM Sales.SalesOrderHeader s
WHERE EXISTS ( SELECT SalesOrderID
FROM Sales.SalesOrderDetail sod
WHERE sod.UnitPrice BETWEEN 10 AND 200
AND sod.SalesOrderID = s.SalesOrderID );
SELECT BusinessEntityID,
SalesQuota AS CurrentSalesQuota
FROM Sales.SalesPerson
WHERE SalesQuota = (SELECT MAX(SalesQuota)
FROM Sales.SalesPerson
);
SELECT t1.Name AS Country FROM dbo.Country AS t1
UNION ALL
SELECT t1.Name AS Country FROM dbo.Country AS t1
SELECT BusinessEntityID,
GETDATE() QuotaDate,
SalesQuota
FROM Sales.SalesPerson
WHERE SalesQuota > 0
UNION ALL
SELECT BusinessEntityID,
QuotaDate,
SalesQuota
FROM Sales.SalesPersonQuotaHistory
WHERE SalesQuota > 0
ORDER BY BusinessEntityID DESC,
QuotaDate DESC;
SELECT t1.Name AS Country FROM dbo.Country AS t1
UNION
SELECT t1.Name AS Country FROM dbo.Country AS t1
SELECT P1.LastName
FROM HumanResources.Employee E
INNER JOIN Person.Person P1
ON E.BusinessEntityID = P1.BusinessEntityID
UNION
SELECT P2.LastName
FROM Sales.SalesPerson SP
INNER JOIN Person.Person P2
ON SP.BusinessEntityID = P2.BusinessEntityID;
SELECT t1.Name AS Country FROM dbo.Country AS t1
EXCEPT
SELECT t1.Name AS Country FROM dbo.Country AS t1
where Name LIKE N'A%'
SELECT P.ProductID
FROM Production.Product P
EXCEPT
SELECT BOM.ComponentID
FROM Production.BillOfMaterials BOM;
--Uwaga nie będzie duplikatów
SELECT t1.Name AS Country FROM dbo.Country AS t1
INTERSECT
SELECT t1.Name AS Country FROM dbo.Country AS t1
where Name like N'A%'
SELECT PR1.ProductID
FROM Production.ProductReview PR1
WHERE PR1.Rating >= 4
INTERSECT
SELECT PR1.ProductID
FROM Production.ProductReview PR1
WHERE PR1.Rating <= 2;
--Uwaga nie będzie duplikatów
SELECT R.RegionId FROM Region AS R
EXCEPT
SELECT t1.RegionId AS Country FROM dbo.Country AS t1
SELECT R.RegionId, r.Name FROM Region AS R
WHERE NOT EXISTS ( SELECT *
FROM dbo.Country AS t1
WHERE R.RegionId = t1.RegionId );
SELECT P.ProductID,
P.Name
FROM Production.Product P
WHERE NOT EXISTS ( SELECT *
FROM Sales.SpecialOfferProduct SOP
WHERE SOP.ProductID = P.ProductID );
SELECT MIN(Rating) Rating_Min,
MAX(Rating) Rating_Max,
SUM(Rating) Rating_Sum,
AVG(Rating) Rating_Avg
FROM Production.ProductReview;
SELECT AVG(Grade) AS AvgGrade,
AVG(DISTINCT Grade) AS AvgDistinctGrade,
MAX(Grade) AS MaxGrade,
MIN(Grade) AS MinGrade
FROM (VALUES (1, 100),
(1, 99),
(1, 99),
(1, 98),
(1, 99),
(1, 32),
(2, 77),
(2, 56),
(2, 88),
(2, 60),
(2, 80)
) dt (StudentId, Grade)
SELECT AVG(Grade) AS AvgGrade,
AVG(DISTINCT Grade) AS AvgDistinctGrade,
MAX(Grade) AS MaxGrade,
MIN(Grade) AS MinGrade
FROM (VALUES (1, 100),
(1, 99),
(1, 99),
(1, 98),
(1, 99),
(1, 32),
(2, 77),
(2, 56),
(2, 88),
(2, 60),
(2, 80)
) dt (StudentId, Grade)
GROUP BY StudentId
SELECT TOP (10)
SalesOrderID,
SUM(LineTotal) AS OrderTotal,
MIN(LineTotal) AS MinLine,
MAX(LineTotal) AS MaxLine,
AVG(LineTotal) AS AvgLine,
COUNT(LineTotal) AS CountLine
FROM [Sales].[SalesOrderDetail]
GROUP BY SalesOrderID
ORDER BY SalesOrderID;
SELECT COUNT(FakeGrades),
COUNT(*)
FROM (VALUES (1, 100,88),
(1, 99,NULL),
(1, 99,NULL),
(1, 98,NULL),
(1, 99,NULL),
(1, 32,NULL),
(2, 77,NULL),
(2, 56,NULL),
(2, 88,NULL),
(2, 60,NULL),
(2, 80,NULL)
) dt (StudentId, Grade,FakeGrades)
GROUP BY StudentId
SELECT TOP (5)
Shelf,
COUNT(ProductID) AS ProductCount,
COUNT_BIG(ProductID) AS ProductCountBig
FROM Production.ProductInventory
GROUP BY Shelf
ORDER BY Shelf;
IF OBJECT_ID('tempdb.dbo.[#test5.4]')
IS NOT NULL DROP TABLE [#test5.4];
CREATE TABLE [#test5.4]
(
StudentID INTEGER,
Grade INTEGER
);
INSERT INTO [#test5.4] (StudentID, Grade)
VALUES (1, 100),
(1, 95)
SELECT StudentID, CHECKSUM_AGG(Grade) AS GradeChecksumAgg
FROM [#test5.4]
GROUP BY StudentID;
UPDATE [#test5.4]
SET Grade = 99
WHERE Grade = 95;
SELECT StudentID, CHECKSUM_AGG(Grade) AS GradeChecksumAgg
FROM [#test5.4]
GROUP BY StudentID;
SELECT
AVG(Grade)
FROM (VALUES (1, 100,88),
(1, 99,NULL),
(1, 99,NULL),
(2, 77,NULL),
(2, 56,NULL),
(2, 88,NULL),
(2, 60,NULL),
(2, 80,NULL)
) dt (StudentId, Grade,FakeGrades)
GROUP BY StudentId
HAVING COUNT(*) = 3
SELECT
AVG(Grade)
FROM (VALUES (1, 100,88),
(1, 99,NULL),
(1, 99,NULL),
(2, 77,NULL),
(2, 56,NULL),
(2, 88,NULL),
(2, 60,NULL),
(2, 80,NULL)
) dt (StudentId, Grade,FakeGrades)
GROUP BY StudentId
HAVING COUNT(*) = 5
SELECT s.Name,
COUNT(w.WorkOrderID) AS Cnt
FROM Production.ScrapReason s
INNER JOIN Production.WorkOrder w
ON s.ScrapReasonID = w.ScrapReasonID
GROUP BY s.Name
HAVING COUNT(*) > 50;
SELECT
COUNT(Grade) AS [Grade],
COUNT(DISTINCT Grade) AS [DistinctGrade]
FROM (VALUES (1, 100,88),
(1, 99,NULL),
(1, 99,NULL),
(2, 77,NULL),
(2, 88,NULL),
(2, 88,NULL),
(2, 60,NULL),
(2, 80,NULL)
) dt (StudentId, Grade,FakeGrades)
GROUP BY StudentId
SELECT [RateChangeDate],
COUNT([Rate]) AS [Count],
COUNT(DISTINCT Rate) AS [DistinctCount]
FROM [HumanResources].[EmployeePayHistory]
WHERE RateChangeDate >= '2008-12-01'
AND RateChangeDate < '2008-12-10'
GROUP BY RateChangeDate;
SELECT
TestName,StudentId,
SUM(Grade)
FROM (VALUES (1,'A',1, 100,88),
(1,'B',1, 99,NULL),
(1,'C',1, 99,NULL),
(2,'A',1, 77,NULL),
(2,'B',1, 88,NULL),
(2,'C',2, 88,NULL),
(2,'D',2, 60,NULL),
(2,'E',2, 80,NULL)
) dt (StudentId,TestName,YearS, Grade,FakeGrades)
GROUP BY ROLLUP(StudentId, TestName);
SELECT i.Shelf,
p.Name,
SUM(i.Quantity) AS Total
FROM Production.ProductInventory i
INNER JOIN Production.Product p
ON i.ProductID = p.ProductID
WHERE i.Shelf IN ('A','B')
AND p.Name LIKE 'Metal%'
GROUP BY ROLLUP(i.Shelf, p.Name);
SELECT
TestName,StudentId,
SUM(Grade)
FROM (VALUES (1,'A',1, 100,88),
(1,'B',1, 99,NULL),
(1,'C',1, 99,NULL),
(2,'A',1, 77,NULL),
(2,'B',1, 88,NULL),
(2,'C',2, 88,NULL),
(2,'D',2, 60,NULL),
(2,'E',2, 80,NULL)
) dt (StudentId,TestName,YearS, Grade,FakeGrades)
GROUP BY CUBE(StudentId, TestName);
SELECT Shelf,
LocationID,
SUM(Quantity) AS Total
FROM Production.ProductInventory
WHERE Shelf IN ('A','B')
AND LocationID IN (10, 20)
GROUP BY CUBE(Shelf, LocationID);
SELECT i.Shelf,
i.LocationID,
p.Name,
SUM(i.Quantity) AS Total
FROM Production.ProductInventory i
INNER JOIN Production.Product p
ON i.ProductID = p.ProductID
WHERE Shelf IN ('A', 'C')
AND Name IN ('Chain', 'Decal', 'Head Tube')
GROUP BY
GROUPING SETS((i.Shelf), (i.Shelf, p.Name),
(i.LocationID, p.Name));
SELECT CASE WHEN GROUPING(ReorderPoint) = 1 THEN '--GROUP--'
ELSE CONVERT(VARCHAR(15), ReorderPoint)
END AS ReorderPointCalc,
ReorderPoint,
CASE WHEN GROUPING(Size) = 1 THEN '--GROUP--'
ELSE CONVERT(VARCHAR(15), Size)
END AS SizeCalc,
Size,
CASE WHEN GROUPING(ReorderPoint) = 0 AND GROUPING(Size) = 1 THEN 'Size Total'
WHEN GROUPING(ReorderPoint) = 1 AND GROUPING(Size) = 0 THEN 'ReorderPoint
Total'
WHEN GROUPING(ReorderPoint) = 1 AND GROUPING(Size) = 1 THEN 'Grand Total'
ELSE 'Regular Row'
END AS RowType,
SUM(StandardCost) AS Total
FROM Production.Product
WHERE ReorderPoint = 3
GROUP BY CUBE(ReorderPoint, Size);
SELECT
TestName,StudentId,
SUM(Grade),
CASE GROUPING_ID(StudentId, TestName)
WHEN 1 THEN 'StudentId'
WHEN 2 THEN 'TestName'
WHEN 3 THEN 'StudentId/TestName'
WHEN 7 THEN 'ALL'
ELSE 'Regular Row'
END AS GroupingType
FROM (VALUES (1,'A',1, 100,88),
(1,'B',1, 99,NULL),
(1,'C',1, 99,NULL),
(2,'A',1, 77,NULL),
(2,'B',1, 88,NULL),
(2,'C',2, 88,NULL),
(2,'D',2, 60,NULL),
(2,'E',2, 80,NULL)
) dt (StudentId,TestName,YearS, Grade,FakeGrades)
GROUP BY CUBE(StudentId, TestName);
SELECT Shelf,
LocationID,
Bin,
CASE GROUPING_ID(Shelf, LocationID, Bin)
WHEN 1 THEN 'Shelf/Location Total'
WHEN 2 THEN 'Shelf/Bin Total'
WHEN 3 THEN 'Shelf Total'
WHEN 4 THEN 'Location/Bin Total'
WHEN 5 THEN 'Location Total'
WHEN 6 THEN 'Bin Total'
WHEN 7 THEN 'Grand Total'
ELSE 'Regular Row'
END AS GroupingType,
SUM(Quantity) AS Total
FROM Production.ProductInventory
WHERE LocationID IN (3)
AND Bin IN (1, 2)
GROUP BY CUBE(Shelf, LocationID, Bin)
ORDER BY Shelf,
LocationID,
Bin;
WITH cte AS
(
SELECT SalesOrderID
FROM Sales.SalesOrderDetail
WHERE UnitPrice BETWEEN 1900 AND 2000
)
SELECT s.PurchaseOrderNumber
FROM Sales.SalesOrderHeader s
WHERE EXISTS (SELECT SalesOrderID
FROM cte
WHERE SalesOrderID = s.SalesOrderID );
WITH CTE(N) AS
(
SELECT TOP (5) object_id
FROM sys.objects
)
SELECT N FROM CTE;
SELECT TOP 10
AccountNumber,
OrderDate,
TotalDue,
ROW_NUMBER() OVER (PARTITION BY AccountNumber ORDER BY OrderDate)
AS RowNumber
FROM AdventureWorks2014.Sales.SalesOrderHeader
ORDER BY AccountNumber;