Thank you for choosing Simple SQL tutorials as your source for T-SQL training!
Click this link to be directed to a GitHub account that contains a backup of a database called SimpleSQLTutorialsData. This backup file was created in Microsoft SQL Server 2019. It can be restored to your local Microsoft SQL Server environment. The content of your EBook references data in this database.
If you have trouble downloading and restoring the backup, we’ll go with Plan B. Below are all the SQL statements which add all the data you need to have to follow along in your EBook.
If you go this route, it is strongly recommended that you create an entirely new testing database to add this data to. You can create an entirely new testing database by running the following statements.
CREATE DATABASE SimpleSQLTutorialsData GO USE [SimpleSQLTutorialsData] GO
After running those statements, your database context ought to be pointing to the new SimpleSQLTutorialsData database. You can see your database context in SQL Server Management Studio here:
To populate this new testing database with data, run the following SQL statements:
CREATE TABLE [dbo].[Products]
(
[ProductID] [int] IDENTITY(20,2) NOT NULL,
[ProductName] [varchar](20) NULL,
[Price] [decimal](5, 2) NULL
)
GO
INSERT INTO [dbo].[Products] ([ProductName], [Price])
VALUES ('Large Bench', 198.00),
('Small Bench', 169.40 ),
('Coffee Table', 250.00),
('Side Tables', 265.20),
('Coat Rack', 45.00)
GO
CREATE TABLE [dbo].[Customers]
(
[CustID] [int] IDENTITY(50,5) NOT NULL,
[FirstName] [varchar](20) NULL,
[LastName] [varchar](20) NULL
)
GO
INSERT INTO [dbo].[Customers] ([FirstName], [LastName])
VALUES ('Joshua', 'Porter'),
('Andrew', 'Bluefield'),
('Jack', 'Donovan'),
('Cindy', 'Thatcher'),
('Gordon', 'Acres'),
('Gretchen', 'Hamilton')
GO
CREATE TABLE [dbo].[ContactInfo]
(
[ContactID] [int] IDENTITY(5000,1) NOT NULL,
[CustID] [int] NOT NULL,
[Phone] [varchar](13) NULL,
[Email] [varchar](50) NULL,
[Addr] [varchar](30) NULL,
[Country] [varchar](8) NULL
)
GO
INSERT INTO [dbo].[ContactInfo] ([CustID], [Phone], [Email], [Addr], [Country])
VALUES (50, '850-225-8745', 'joshPorter@gmail.com', '1600 Pennsylvania', 'USA'),
(55, '850-333-8555', 'andrewBluefield@gmail.com', '34 Deronda Ave.', 'USA'),
(60, '555-887-5547', 'jackDonovan@gmail.com', '587 Student Ln.', 'India'),
(65, '585-325-4478', 'cindythatcher@gmail.com', '54 Royalton', 'UK'),
(70, '698-554-3365', 'gordonAcres@gmail.com', '128 Hamilton St.', 'UK'),
(75, '878-445-3432', 'gretchenHamilton@gmail.com', '344 Washington', 'USA')
GO
CREATE TABLE [dbo].[Orders]
(
[OrderID] [int] IDENTITY(100,10) NOT NULL,
[CustID] [int] NULL,
[ProdID] [int] NULL,
[Qty] [tinyint] NULL,
[Orderdate] [datetime] NULL
)
GO
INSERT INTO [dbo].[Orders] ([CustID], [ProdID], [Qty], [Orderdate])
VALUES (55, 22, 1, '2021-06-01'),
(60, 28, 2, '2021-06-06'),
(75, 26, 1, '2021-06-13'),
(50, 20, 1, '2021-07-01'),
(55, 28, 1, '2021-07-06'),
(65, 24, 1, '2021-07-14'),
(55, 26, 1, '2021-07-18'),
(50, 26, 1, '2021-07-24'),
(70, 24, 1, '2021-08-06'),
(70, 26, 1, '2021-08-06'),
(70, 22, 3, '2021-09-01')
GO
CREATE TABLE [dbo].[Employees]
(
[EmplID] [int] IDENTITY(50,10) NOT NULL,
[FirstName] [nvarchar](20) NULL,
[MiddleName] [nvarchar](20) NULL,
[LastName] [nvarchar](20) NULL,
[EmployeeCode] [nchar](4) NULL
)
GO
INSERT INTO [dbo].[Employees] ([FirstName], [MiddleName], [LastName], [EmployeeCode])
VALUES ('Michael', 'Douglas', 'Booth', 'MDB1'),
('George', 'Alexander', 'Gray', 'GAG1'),
('Rachel', NULL, 'Washington', 'RLW1'),
('Morgan', 'Denise', 'Bennett', 'MDB2'),
('Richard', 'Eugene', 'Paul', 'REP1'),
('George', NULL, 'Newman', 'GN1 '),
('Michelle', 'Daniel', 'Bond', 'MDB3'),
('Joshua', N'Gregory', 'Smith', 'JGS1'),
('Gillian', 'Alexis', 'George', 'GAG2'),
('Samuel', NULL, 'Laurence', 'SL1 '),
('Megan', 'Francis', 'Thompson', 'MFT1')
GO
CREATE TABLE [dbo].[Books]
(
[BookID] [int] IDENTITY(10,5) NOT NULL,
[Title] [varchar](50) NOT NULL,
[Author] [varchar](15) NULL,
[Pages] [int] NULL
)
GO
INSERT INTO [dbo].[Books] ([Title], [Author], [Pages])
VALUES ('As a man thinketh', 'Allen', 45),
('Eat that frog', 'Tracy', 108),
('The war of art', 'Pressfield', 165),
('Deep work', 'Newport', 263),
('The Pragmatic Programmer', 'Thomas', 283),
('The Education of a Bodybuilder', 'Schwarzenegger', 256),
('Debt Free Degree', 'O''Neil', 224)
GO
CREATE TABLE [dbo].[BoulderBranch]
(
[CustID] [int] IDENTITY(100,1) NOT NULL,
[FirstName] [varchar](15) NULL,
[LastName] [varchar](15) NULL,
[Email] [varchar](40) NULL
)
GO
INSERT INTO [dbo].[BoulderBranch] ([FirstName], [LastName], [Email])
VALUES ('Patti', 'Goldsworth', 'PattiGoldsworth@gmail.com'),
('John', 'Bolevue', 'JohnBolevue@gmail.com'),
('George', 'Panel', 'GeorgePanel@gmail.com'),
('Emily', 'Foxtrot', 'EmilyFoxtrot@gmail.com'),
('Doug', 'Bennington', 'DougBennington@gmail.com'),
('Francis', 'Thompson', 'FrancisThompson@gmail.com'),
('June', 'Braun', 'JuneBraun@gmail.com'),
('Michael', 'Gordon', 'MichaelGordon@gmail.com'),
('Charles', 'Rhett', 'CharlesRhett@gmail.com'),
('Simone', 'Sullivan', 'SimoneSullivan@gmail.com'),
('Sara', 'Winchester', 'SaraWinchester@gmail.com')
GO
CREATE TABLE [dbo].[ColoradoSpringsBranch]
(
[CustID] [int] IDENTITY(200,1) NOT NULL,
[FirstName] [varchar](15) NULL,
[LastName] [varchar](15) NULL,
[Email] [varchar](50) NULL
)
GO
INSERT INTO [dbo].[ColoradoSpringsBranch] ([FirstName], [LastName], [Email])
VALUES ('Larry', 'Parker', 'LarryParker@gmail.com'),
('Frank', 'Castle', 'FrankCastle@gmail.com'),
('Evan', 'Middleburg', 'EvanMiddleburg@gmail.com'),
('Jimmy', 'Franklin', 'JimmyFranklin@gmail.com'),
('Philip', 'Warner', 'PhilipWarner@gmail.com'),
('Casey', 'Longview', 'CaseyLongview@gmail.com'),
('Sara', 'Winchester', 'SaraWinchester@gmail.com'),
('Patti', 'Goldsworth', 'PattiGoldsworth@gmail.com')
GO
CREATE TABLE [dbo].[DenverBranch]
(
[CustID] [int] IDENTITY(20,1) NOT NULL,
[FirstName] [varchar](15) NULL,
[LastName] [varchar](15) NULL,
[Email] [varchar](45) NULL
)
GO
INSERT INTO [dbo].[DenverBranch] ([FirstName], [LastName], [Email])
VALUES ('Doug', 'Bennington', 'DougBennington@gmail.com'),
('Jack', 'Potter', 'JackPotter@gmail.com'),
('Emily', 'Foxtrot', 'EmilyFoxtrot@gmail.com'),
('Lucas', 'Rhinestone', 'LucasRhinestone@gmail.com'),
('Walter', 'Grey', 'WalterGrey@gmail.com'),
('June', 'Braun', 'JuneBraun@gmail.com'),
('Sara', 'Winchester', 'SWinchester@gmail.com'),
('Tim', 'Washington', 'TWashington@gmail.com'),
('Jennifer', 'West', 'JenniferWest@gmail.com'),
('Lauren', 'Shaul', 'LaurenShaul@gmail.com'),
('Jim', 'Broker', 'JBroker@gmail.com')
GO
CREATE TABLE [dbo].[CollegeMajors]
(
[MajorID] [int] IDENTITY(1,1) NOT NULL,
[MajorName] [varchar](30) NOT NULL,
PRIMARY KEY CLUSTERED
(
[MajorID] ASC
)
)
GO
INSERT INTO [dbo].[CollegeMajors] ([MajorName])
VALUES ('Computer Science'),
('Information Technology'),
('Snowman Engineering'),
('Graphic Design'),
('Computer Criminology'),
('Basket Weaving'),
('Left Handed Knitting'),
('Biology'),
('Chemistry'),
('Backflip Theory')
GO
CREATE TABLE [dbo].[Students](
[StudentID] [int] IDENTITY(100,5) NOT NULL,
[FirstName] [varchar](15) NULL,
[LastName] [varchar](15) NULL,
[DeclaredMajor] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[StudentID] ASC
)
)
GO
INSERT INTO [dbo].[Students] ([FirstName], [LastName], [DeclaredMajor])
VALUES ('Jeff', 'Alanzo', 2),
('Amy', 'Bell', 1),
('Marla', 'Bridge', 2),
('Andrew', 'Bucks', 8),
('Sara', 'Carlton', 5),
('Kimberly', 'Cutter', 8),
('Jack', 'Faun', 4),
('Sam', 'Green', 1),
('Max', 'Mills', 9)
GO
CREATE TABLE [dbo].[Shippers]
(
ShipperID [int] IDENTITY(800,10) NOT NULL,
ShipperName [varchar](50) NULL,
Region [char](2) NULL,
USState [varchar](15) NULL,
City [varchar](15) NULL,
OrdersFulfilled [int] NULL
)
GO
INSERT INTO [dbo].[Shippers] ([ShipperName], [Region], [USState], [City], [OrdersFulfilled])
VALUES ('Orangemen Deliveries','NE','New York','Syracuse',234),
('Capital City Shipments','SE','Florida','Tallahassee',453),
('Doyle Brothers Deliveries','SW','Nevada','Reno',351),
('Beaver State Transit','NW','Oregon','Portland',427),
('Buffalo Shipments LLP','NE','New York','Buffalo',243),
('Cali-Fast Deliveries','SW','California','Los Angeles',651),
('Sunshine Transit','SE','Florida','Orlando',445),
('Ben Frank Deliveries','NE','Pennsylvania','Philadelphia',267),
('Golden Gate Shipments','SW','California','San Francisco',490),
('Bennett Sisters Deliveries','NE','New York','Albany',275),
('Green Family Shipments','SE','South Carolina','Charlestown',395),
('Twin Pines Transit','NW','Washington','Seattle',269)
GO
A quick disclaimer:
The information referenced in the various Simple SQL Tutorials EBooks, including names, locations, and businesses is purely fictional and any references to actual names, locations, and businesses are purely coincidental. No real association or connection is intended, nor should any real association or connection be inferred.
The integrity and accuracy of the reader’s data is solely the responsibility of the reader. The author has made every effort to ensure the accuracy of the information within this book was correct at time of publication. The author does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause.