March 30, 2013 at 10:13 am
Hi!
I was wondering if I could find some queries and database tables for them. I would like to have some complicated tasks for which I need to write queries and some tables with inserted rows to check if my queries are good. It's too boring and takes much time to make up my own queries and create tables.
March 30, 2013 at 11:00 am
use the adventureworks database that comes with SQL
---------------------------------------------------------------------
March 30, 2013 at 12:37 pm
lazarjojic (3/30/2013)
Hi!I was wondering if I could find some queries and database tables for them. I would like to have some complicated tasks for which I need to write queries and some tables with inserted rows to check if my queries are good. It's too boring and takes much time to make up my own queries and create tables.
I agree that you could certainly use Adventure Works for practice but you won't always have such a nice example database for real life. Further, Adventure Works is classified as a "tiny" database so it's almost useless for testing performance code. When you get to real world scenarios that might not have such a test database, you're going to need to learn how to build tables with millions of rows quickly to test with.
For example...
/**********************************************************************************************************************
Purpose:
Create a voluminous test table with various types of highly randomized data.
--Jeff Moden
**********************************************************************************************************************/
--===== Conditionally drop the test table to make reruns easier
IF OBJECT_ID('dbo.JBMTest','U') IS NOT NULL
DROP TABLE dbo.JBMTest
;
--===== Create and populate a 1,000,000 row test table.
-- "SomeID" has a range of 1 to 1,000,000 unique numbers
-- "SomeInt" has a range of 1 to 50,000 non-unique numbers
-- "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings
-- "SomeMoney has a range of 0.00 to 100.00 non-unique numbers
-- "SomeDateTime" has a range of >=01/01/2000 and <01/01/2020 non-unique date/times
-- "SomeDate" has a range of >=01/01/2000 and <01/01/2020 non-unique "whole dates"
-- "SomeName" contains random characters at random lengths from 2 to 20 characters
SELECT TOP 1000000
SomeID = IDENTITY(INT,1,1),
SomeInt = ABS(CHECKSUM(NEWID())) % 50000 + 1,
SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID())) % (ASCII('Z')-ASCII('A')+1) +ASCII('A'))
+ CHAR(ABS(CHECKSUM(NEWID())) % (ASCII('Z')-ASCII('A')+1) +ASCII('A')),
SomeMoney = CAST(RAND(CHECKSUM(NEWID())) * 100 AS DECIMAL(9,2)), --Note rounding
SomeDateTime = RAND(CHECKSUM(NEWID())) * DATEDIFF(dd,'2000','2020') + CAST('2000' AS DATETIME),
SomeDate = ABS (CHECKSUM(NEWID())) % DATEDIFF(dd,'2000','2020') + CAST('2000' AS DATETIME),
SomeName = RIGHT(NEWID(),ABS(CHECKSUM(NEWID())) % 19 + 2)
INTO dbo.JBMTest
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
ALTER TABLE dbo.JBMTest
ADD CONSTRAINT PK_JBMTest PRIMARY KEY CLUSTERED (SomeID) WITH FILLFACTOR = 90
;
--Jeff Moden
Change is inevitable... Change for the better is not.
March 30, 2013 at 12:57 pm
Jeff Moden (3/30/2013)
lazarjojic (3/30/2013)
Hi!I was wondering if I could find some queries and database tables for them. I would like to have some complicated tasks for which I need to write queries and some tables with inserted rows to check if my queries are good. It's too boring and takes much time to make up my own queries and create tables.
]
Hi... following on from Jeff's comments..please see below a script that I built for internal training..based on Jeff's code.
I specifically modelled this around the business requirements at the time.....please take time to read thro and understand how you can redefine this for your own requiremements and business areas.
your view of the world will probably be different to mine...i.e manufacturing / health / finance / sales etc..but with a little effort it is very easy to
build reasonable size databases for testing purposes.
code below...hope it helps
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- this code createa a test rig of customers, products, transactional data and associated related tables
-- 100K customers, 10K products , 5M transactions
-- also include a Tally table and a Calendar table
--PLEASE NOTE THAT THIS WILL DELETE ANY EXG TABLES WITH SAME NAME IN TEMPDB....please amend if required
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
use [tempdb]--==== start in safe place!!!
GO
--====Conditionally delete tables from [tempdb}
IF OBJECT_ID('tempdb..Tally', 'U') IS NOT NULL DROP TABLE tempdb..Tally ;
IF OBJECT_ID('tempdb..Calendar', 'U') IS NOT NULL DROP TABLE tempdb..Calendar ;
IF OBJECT_ID('tempdb..Products', 'U') IS NOT NULL DROP TABLE tempdb..Products ;
IF OBJECT_ID('tempdb..Product_Groups', 'U') IS NOT NULL DROP TABLE tempdb..Product_Groups ;
IF OBJECT_ID('tempdb..Product_Class', 'U') IS NOT NULL DROP TABLE tempdb..Product_Class ;
IF OBJECT_ID('tempdb..Customers', 'U') IS NOT NULL DROP TABLE tempdb..Customers ;
IF OBJECT_ID('tempdb..Territory_Managers', 'U') IS NOT NULL DROP TABLE tempdb..Territory_Managers ;
IF OBJECT_ID('tempdb..Regional_Sales_Managers', 'U') IS NOT NULL DROP TABLE tempdb..Regional_Sales_Managers ;
IF OBJECT_ID('tempdb..TransData', 'U') IS NOT NULL DROP TABLE tempdb..TransData ;
--***************** 'STANDARD TABLES' STRUCTURE STARTS HERE *********************
--==== Create a Tally table and a Calendar table
SELECT TOP 60000 IDENTITY(INT, 1, 1) AS N
INTO dbo.Tally
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
CROSS JOIN sys.all_columns ac3
ALTER TABLE dbo.Tally ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
SET DATEFORMAT DMY
DECLARE @Date_Start AS DATETIME
DECLARE @Date_End AS DATETIME
SET @Date_Start = '01/01/1900'
SET @Date_End = '31/12/2030'
CREATE TABLE dbo.Calendar
(
calendar_date_ID INT IDENTITY(1, 1) NOT NULL,
calendar_week_ID INT,
calendar_date DATETIME PRIMARY KEY CLUSTERED,
calendar_year SMALLINT,
calendar_month TINYINT,
calendar_day TINYINT,
calendar_quarter TINYINT,
first_day_in_month DATETIME,
last_day_in_month DATETIME,
day_of_week TINYINT,
week_of_year TINYINT,
days_in_month TINYINT,
day_of_year SMALLINT,
is_weekday INT,
day_name VARCHAR (10),
month_name VARCHAR (10),
iso_date CHAR (8),
fiscal_year SMALLINT,
fiscal_month TINYINT
);
INSERT INTO dbo.Calendar
(calendar_date)
SELECT t.N - 1 + @Date_Start
FROM dbo.Tally t
WHERE t.N - 1 + @Date_Start <= @Date_End
UPDATE dbo.Calendar
SET calendar_week_ID = calendar_date_id / 7 + 1,
calendar_year = Datepart (YEAR, calendar_date),
fiscal_year = CASE
WHEN Datepart(M, calendar_date) >= 10 THEN Datepart (YEAR, calendar_date) + 1
ELSE Datepart (YEAR, calendar_date)
END,
calendar_month = Datepart (MONTH, calendar_date),
fiscal_month = CASE
WHEN Datepart(M, calendar_date) >= 10 THEN Datepart(M, calendar_date) - 9
ELSE Datepart(M, calendar_date) + 3
END,
calendar_day = Datepart (DAY, calendar_date),
calendar_quarter = Datepart (QUARTER, calendar_date),
first_day_in_month = Dateadd(mm, Datediff(mm, 0, calendar_date), 0),
last_day_in_month = Dateadd(mm, Datediff(mm, 0, calendar_date) + 1, 0) - 1,
day_of_week = Datepart (WEEKDAY, calendar_date),
week_of_year = Datepart (WEEK, calendar_date),
day_of_year = Datepart (DAYOFYEAR, calendar_date),
is_weekday = Isnull (( CASE
WHEN ( ( @@DATEFIRST - 1 ) + ( Datepart (WEEKDAY, calendar_date) - 1 ) )%7 NOT IN ( 5, 6 )
THEN 1
END ), 0),
day_name = Datename (WEEKDAY, calendar_date),
month_name = Datename (MONTH, calendar_date),
iso_date = CONVERT(CHAR(8), calendar_date, 112),
days_in_month = Datepart(dd, ( Dateadd(mm, Datediff(mm, 0, calendar_date) + 1, 0) - 1 ))
--SELECT TOP 1000 *
--FROM date_calendar
--*****************PRODUCT STRUCTURE STARTS HERE *********************
--==== Create a Product Hierarchy
--==== Product Class > Product Group > Product
--==== Product also contains an additional attribute of Market_Segment
--***************** PRODUCT CLASS *********************
SELECT TOP 5 ---- Product Class
Product_Class_ID = IDENTITY(INT, 1, 1),
Product_Class_Name = CAST('' AS VARCHAR(30))
INTO Product_Class
FROM sys.all_columns
UPDATE Product_Class
SET Product_Class_Name = 'Product_Class_' + CAST(Product_Class_ID AS VARCHAR)
ALTER TABLE [dbo].[Product_Class] ADD CONSTRAINT [PK_Product_Class] PRIMARY KEY CLUSTERED ( [Product_Class_ID] ASC )
--SELECT * FROM Product_Class
--***************** PRODUCT GROUP *********************
SELECT TOP 30 ---- Product Group
Product_Group_ID = IDENTITY(INT, 1, 1),
Product_Group_Name = CAST('' AS VARCHAR(30)),
PC_ID = 0
INTO Product_Groups
FROM sys.all_columns ac1
UPDATE Product_Groups
SET Product_Group_Name = 'Product_Group_' + CAST(Product_Group_ID AS VARCHAR),
PC_ID = ( Product_Group_ID % 4 ) + 1
ALTER TABLE [dbo].[Product_Groups] ADD CONSTRAINT [PK_Product_Groups] PRIMARY KEY CLUSTERED ( [Product_Group_ID] ASC )
GO
CREATE NONCLUSTERED INDEX [IX_PC]
ON [dbo].[Product_Groups] ( [PC_ID] ASC )
GO
--SELECT * FROM Product_Groups
--***************** PRODUCTS *********************
SELECT TOP 10000 ---- Products
ProductID = IDENTITY(INT, 1, 1),
Product_Name = CAST('' AS VARCHAR(30)),
PG_ID = 0,
Market_Segment = CHAR(Abs(Checksum(Newid())) % 10 + 65)
INTO Products
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
CROSS JOIN sys.all_columns ac3
UPDATE Products
SET Product_Name = 'Product_' + CAST(ProductID AS VARCHAR),
PG_ID = ( ProductID % 30 ) + 1
ALTER TABLE [dbo].[Products] ADD CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED ( [ProductID] ASC )
GO
CREATE NONCLUSTERED INDEX [IX_PG]
ON [dbo].[Products] ( [PG_ID] ASC )
GO
--SELECT * FROM Products
--***************** PRODUCT STRUCTURE ENDS HERE *********************
-->>>>>>> NEW SECTION <<<<<<
--***************** CUSTOMER STRUCTURE STARTS HERE *****************
--==== Create a Customer Hierarchy
--==== Regional Sales Manager > Territory Sales Manager > Customer
--==== Customer also contains an additional attribute of Customer Type
--***************** RSManagers *********************
SELECT TOP 8 ---- Rgeional Sales Managers
Regional_Sales_Manager_ID = IDENTITY(INT, 1, 1),
Regional_Sales_Manager_Name = CAST('' AS VARCHAR(30))
INTO Regional_Sales_Managers
FROM sys.all_columns ac1
UPDATE Regional_Sales_Managers
SET Regional_Sales_Manager_Name = 'Regional_Sales_Manager_' + CAST(Regional_Sales_Manager_ID AS VARCHAR)
ALTER TABLE [dbo].[Regional_Sales_Managers] ADD CONSTRAINT [PK_Regional_Sales_Managers] PRIMARY KEY CLUSTERED ([Regional_Sales_Manager_ID] ASC)
GO
--SELECT * FROM Regional_Sales_Managers
--***************** TERRITORY MANAGERS *********************
SELECT TOP 50 ---- Territory Managers
Territory_Manager_ID = IDENTITY(INT, 1, 1),
Territory_Manager_Name = CAST('' AS VARCHAR(30)),
RSM_ID = 0
INTO Territory_Managers
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
UPDATE Territory_Managers
SET Territory_Manager_Name = 'Territory_Manager_' + CAST(Territory_Manager_ID AS VARCHAR),
RSM_ID = ( Territory_Manager_ID % 8 ) + 1
ALTER TABLE [dbo].[Territory_Managers] ADD CONSTRAINT [PK_Territory_Managers] PRIMARY KEY CLUSTERED ( [Territory_Manager_ID] ASC )
GO
CREATE NONCLUSTERED INDEX [IX_RSM]
ON [dbo].[Territory_Managers] ( [RSM_ID] ASC )
GO
--SELECT * FROM Territory_Managers
--***************** CUSTOMERS *********************
SELECT TOP 100000 ---- Customers
CustomerID = IDENTITY(INT, 1, 1),
Customer_Name = CAST('' AS VARCHAR(30)),
Customer_Type = CHAR(Abs(Checksum(Newid())) % 2 + 65)
+ CHAR(Abs(Checksum(Newid())) % 2 + 65)
+ CHAR(Abs(Checksum(Newid())) % 2 + 65),
TM_ID = 0,
DOB = DATEADD(dd,ABS(CHECKSUM(NEWID()))%DATEDIFF(dd,'1920','2000'),'1920'),
Gender = (Abs(Checksum(Newid())) % 2 + 1) --=== 1 = MALE / 2 = FEMALE
INTO Customers
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
CROSS JOIN sys.all_columns ac3
UPDATE Customers
SET Customer_Name = 'Customer_' + CAST(CustomerID AS VARCHAR),
TM_ID = (CustomerID % 50) + 1
ALTER TABLE [dbo].[Customers]
ADD CONSTRAINT [PK_Customers]
PRIMARY KEY CLUSTERED ( [CustomerID] ASC )
GO
CREATE NONCLUSTERED INDEX [IX_TM]
ON [dbo].[Customers] ( [TM_ID] ASC )
GO
--SELECT * FROM Customers
--***************** CUSTOMER STRUCTURE ENDS HERE *********************
-->>>>>>> NEW SECTION <<<<<<
--***************** TRANSACTIONAL DATA FROM HERE *****************
SELECT TOP 5000000 ---- NOTE 5 MILLION rows .....
TranID = IDENTITY(INT, 1, 1),
CustomerID = CAST(Abs(Checksum(Newid()) % 100000 + 1) AS INT),
ProdID = CAST(Abs(Checksum(Newid()) % 10000 + 1) AS INT),
SalesAmount = CAST(Rand(Checksum(Newid())) * 99 + 1 AS DECIMAL(5, 2)),
TransDate = Dateadd(dd, Abs(Checksum(Newid()))%Datediff(dd, '2009', '2011'), '2009')
INTO TransData
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
CROSS JOIN sys.all_columns ac3
ALTER TABLE [dbo].[TransData] ADD CONSTRAINT [PK_TransData] PRIMARY KEY CLUSTERED ([TranID] ASC)
GO
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 30, 2013 at 1:26 pm
Very cool, JLS!
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2013 at 2:05 am
thank you guys, but this is still too complicated for me
April 1, 2013 at 3:01 am
lazarjojic (4/1/2013)
thank you guys, but this is still too complicated for me
perhaps you can be more explicit in what you are looking for......
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 1, 2013 at 4:10 am
http://www.sql-ex.ru/learn_exercises.php#answer_ref
something like this, but a little more complicated 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply