The following SQL code serves the purpose of assessing basic SQL Server literacy.
I am creating database and tables schemas, inserting data from .csv files. Constructing 8 simple queries as answers to assignment tasks.
The code works well, but as I am very new to MSSQL Server I need some advice on the following:
- Are there any better ways for constructing database and table schemas?
- Are there any better ways to populate tables from .csv files?
- Best practices for error-handling. Print on external file or as query output?
- How the syntax can be improved, but provide somewhat foolproofness if the wrong database is in use. Practical and convenient, or complicated and accurate?
Assessment questions can be found commented-out prior to every query. Comma-separated sample datasets can be found at the bottom.
USE master
GO
---------------------------------------------------------------------------------------
-- Create database
IF NOT (EXISTS (
SELECT
*
FROM
SYS.DATABASES
WHERE
NAME = N'SQLAssessment'
)
)BEGIN
CREATE DATABASE [SQLAssessment] ON PRIMARY (
NAME = N'[SQLAssessment]',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\SQLAssessment.mdf',
SIZE = 10096KB,
FILEGROWTH = 1024KB
) LOG ON (
NAME = N'Products_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\SQLAssessment.ldf',
SIZE = 1024KB,
FILEGROWTH = 10 %
)
END
--USE [SQLAssessment]
--GO
---------------------------------------------------------------------------------------
-- Create Customers table
IF NOT (EXISTS (
SELECT
*
FROM
INFORMATION_SCHEMA.TABLES
WHERE
OBJECT_ID(N'[SQLAssessment].[dbo].[Customers]') IS NOT NULL
)
)BEGIN
CREATE TABLE [SQLAssessment].[dbo].[Customers] (
customerid INT not null,
firstname VARCHAR (50) null,
lastname VARCHAR (50) null,
city VARCHAR (50) null,
state VARCHAR (50) null,
CONSTRAINT PK_Customers_customerid PRIMARY KEY CLUSTERED (customerid)
)
END
GO
---------------------------------------------------------------------------------------
-- Populate Customers table from .csv
BEGIN TRY
BULK INSERT [SQLAssessment].[dbo].[Customers]
FROM
'C:\Users\tsenk\OneDrive\Работен плот\customers_data.csv' WITH ( -- <--- fit for purpose
FIRSTROW = 1,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
ERRORFILE = 'C:\Users\tsenk\OneDrive\CustomersErrorRows.csv',
TABLOCK
)
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
GO
---------------------------------------------------------------------------------------
-- Create Customer_Purchases table
IF NOT EXISTS(
SELECT
*
FROM
INFORMATION_SCHEMA.TABLES
WHERE
OBJECT_ID(N'[SQLAssessment].[dbo].[Customer_Purchases]') IS NOT NULL
) BEGIN
CREATE TABLE [SQLAssessment].[dbo].[Customer_Purchases] (
customerid INT NOT NULL,
order_date DATE NULL,
item VARCHAR (200) NULL,
quantity INT NULL,
price DECIMAL (20,2) NULL,
CONSTRAINT FK_CustomerPurchases_CustomerID FOREIGN KEY (customerid)
REFERENCES [SQLAssessment].[dbo].[Customers](customerid)
ON DELETE CASCADE
ON UPDATE CASCADE
)
END
GO
---------------------------------------------------------------------------------------
-- Populate Customer_Purchases table from .csv
BEGIN TRY
BULK INSERT [SQLAssessment].[dbo].[Customer_Purchases]
FROM
'C:\Users\tsenk\OneDrive\customer_purchases_data.csv' WITH ( -- <--- fit for purpose
FIRSTROW = 1,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
ERRORFILE = 'C:\Users\tsenk\OneDrive\CustomerPurchasesErrorRows.csv',
TABLOCK
)
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
GO
---------------------------------------------------------------------------------------
-- 1. Count clients by state and show in descending order.
SELECT
[SQLAssessment].[dbo].[Customers].*,
COUNT([SQLAssessment].[dbo].[Customers].[customerid]) AS [customercount]
FROM
[SQLAssessment].[dbo].[Customers]
WHERE
[SQLAssessment].[dbo].[Customers].[state] IS NOT NULL
GROUP BY
[SQLAssessment].[dbo].[Customers].[customerid],
[SQLAssessment].[dbo].[Customers].[firstname],
[SQLAssessment].[dbo].[Customers].[lastname],
[SQLAssessment].[dbo].[Customers].[state],
[SQLAssessment].[dbo].[Customers].[city]
ORDER BY
[customercount] DESC
---------------------------------------------------------------------------------------
-- 2. Show first and last name of the customers who are presented with their first name more than once.
SELECT
[SQLAssessment].[dbo].[Customers].[customerid],
[SQLAssessment].[dbo].[Customers].[firstname],
[SQLAssessment].[dbo].[Customers].[lastname],
[SQLAssessment].[dbo].[Customers].[city],
[SQLAssessment].[dbo].[Customers].[state]
FROM
[SQLAssessment].[dbo].[Customers]
WHERE
[SQLAssessment].[dbo].[Customers].[firstname] IN(
SELECT
[SQLAssessment].[dbo].[Customers].[firstname]
FROM
[SQLAssessment].[dbo].[Customers]
GROUP BY
[SQLAssessment].[dbo].[Customers].[firstname]
HAVING
COUNT([SQLAssessment].[dbo].[Customers].[firstname]) > 1
)
GROUP BY
[SQLAssessment].[dbo].[Customers].[customerid],
[SQLAssessment].[dbo].[Customers].[firstname],
[SQLAssessment].[dbo].[Customers].[lastname],
[SQLAssessment].[dbo].[Customers].[city],
[SQLAssessment].[dbo].[Customers].[state]
---------------------------------------------------------------------------------------
-- 3. How many flashlights are bought by John Gray?
SELECT
c.[customerid],
c.[firstname],
c.[lastname],
cp.[item],
SUM(cp.[quantity]) AS [quantity_bought]
FROM
[SQLAssessment].[dbo].[Customers] AS c
INNER JOIN [SQLAssessment].[dbo].[Customer_Purchases] AS cp
ON c.[customerid] = cp.[customerid]
WHERE
cp.[item] IN (
SELECT
[SQLAssessment].[dbo].[Customer_Purchases].[item]
FROM
[SQLAssessment].[dbo].[Customer_Purchases]
WHERE
[SQLAssessment].[dbo].[Customer_Purchases].[item] LIKE 'Flashlight'
GROUP BY
[SQLAssessment].[dbo].[Customer_Purchases].[item]
HAVING
count(*) > 1
)
AND c.[customerid] = 10101
GROUP BY
c.[customerid],
c.[firstname],
c.[lastname],
cp.[item]
---------------------------------------------------------------------------------------
-- 4. Show 5 random customers and purchases (item and quantity).
SELECT TOP 5
c.[customerid],
c.[firstname],
c.[lastname],
c.[city],
c.[state],
cp.[item],
cp.[quantity]
FROM [SQLAssessment].[dbo].[Customers] AS c
INNER JOIN [SQLAssessment].[dbo].[Customer_Purchases] AS cp
ON cp.[customerid] = c.[customerid]
ORDER BY
RAND(CHECKSUM(*) * RAND())
---------------------------------------------------------------------------------------
-- 5. What is the earliest item purchased by Isabela Moore?
SELECT TOP 1
c.[customerid],
c.[firstname],
c.[lastname],
cp.[item],
cp.[order_date]
FROM [SQLAssessment].[dbo].[Customers] AS c
LEFT JOIN [SQLAssessment].[dbo].[Customer_Purchases] AS cp
ON c.[customerid] = cp.[customerid]
WHERE
c.[customerid] = 10449
GROUP BY
c.[customerid],
c.[firstname],
c.[lastname],
cp.[item],
cp.[order_date]
ORDER BY
cp.[order_date]
---------------------------------------------------------------------------------------
-- 6. What did Isabela Moore purchase in the period 01 August to 31 December?
SELECT
cp.[customerid],
c.[firstname],
c.[lastname],
cp.[item],
cp.[order_date]
FROM [SQLAssessment].[dbo].[Customers] AS c
LEFT JOIN [SQLAssessment].[dbo].[Customer_Purchases] AS cp
ON c.[customerid] = cp.[customerid]
WHERE
c.[customerid] = 10449
AND cp.[order_date]
BETWEEN '2009-08-13'
AND '2009-12-31'
GROUP BY
cp.[customerid],
c.[firstname],
c.[lastname],
cp.[item],
cp.[order_date]
ORDER BY
cp.[order_date]
---------------------------------------------------------------------------------------
-- 7. Show the total number of items, total amount and weighted average price by state.
SELECT
c.[state],
SUM(cp.[quantity]) AS [total_items],
SUM(cp.[price] * cp.[quantity]) AS [total_amount],
SUM(cp.[price] * cp.[quantity]) / SUM(cp.[quantity]) AS [weighted_average_price]
FROM [SQLAssessment].[dbo].[Customers] AS c
INNER JOIN [SQLAssessment].[dbo].[Customer_Purchases] AS cp
ON c.[customerid] = cp.[customerid]
GROUP BY
c.[state]
---------------------------------------------------------------------------------------
-- 8. Show in separate columns Isabela Moore`s purchases item and order date.
SELECT
cp.[item],
cp.[order_date]
FROM [SQLAssessment].[dbo].[Customers] AS c
INNER JOIN [SQLAssessment].[dbo].[Customer_Purchases] AS cp
ON c.[customerid] = cp.[customerid]
WHERE
c.[customerid] = 10449
GROUP BY
cp.[item],
cp.[order_date]
Customers Dataset:
10101,John,Gray,Lynden,Washington
10298,Leroy,Brown,Pinetop,Arizona
10299,Elroy,Keller,Snoqualmie,Washington
10315,Lisa,Jones,Oshkosh,Wisconsin
10325,Ginger,Schultz,Pocatello,Idaho
10329,Kelly,Mendoza,Kailua,Hawaii
10330,Shawn,Dalton,Cannon Beach,Oregon
10338,Michael,Howell,Tillamook,Oregon
10339,Anthony,Sanchez,Winslow,Arizona
10408,Elroy,Cleaver,Globe,Arizona
10410,Mary Ann,Howell,Charleston,South Carolina
10413,Donald,Davids,Gila Bend,Arizona
10419,Linda,Sakahara,Nogales,Arizona
10429,Sarah,Graham,Greensboro,North Carolina
10438,Kevin,Smith,Durango,Colorado
10439,Conrad,Giles,Telluride,Colorado
10449,Isabela,Moore,Yuma,Arizona
Customer Purchases Dataset:
10330,30-Jun-09,Pogo stick,1,28
10101,30-Jun-09,Raft,1,58
10298,01-Jul-09,Skateboard,1,33
10101,01-Jul-09,Life Vest,4,125
10299,06-Jul-09,Parachute,1,1250
10339,27-Jul-09,Umbrella,1,4.5
10449,13-Aug-09,Unicycle,1,180.79
10439,14-Aug-09,Ski Poles,2,25.5
10101,18-Aug-09,Rain Coat,1,18.3
10449,01-Sep-09,Snow Shoes,1,45
10439,18-Sep-09,Tent,1,88
10298,19-Sep-09,Lantern,2,29
10410,28-Oct-09,Sleeping Bag,1,89.22
10438,01-Nov-09,Umbrella,1,6.75
10438,02-Nov-09,Pillow,1,8.5
10298,01-Dec-09,Helmet,1,22
10449,15-Dec-09,Bicycle,1,380.5
10449,22-Dec-09,Canoe,1,280
10101,30-Dec-09,Hoola Hoop,3,14.75
10330,01-Jan-00,Flashlight,4,28
10101,02-Jan-10,Lantern,1,16
10299,18-Jan-10,Inflatable Mattress,1,38
10438,18-Jan-10,Tent,1,79.99
10413,19-Jan-10,Lawnchair,4,32
10410,30-Jan-10,Unicycle,1,192.5
10315,02-Feb-10,Compass,1,8
10449,28-Feb-10,Flashlight,1,4.5
10101,08-Mar-10,Sleeping Bag,2,88.7
10298,18-Mar-10,Pocket Knife,1,22.38
10449,19-Mar-10,Canoe paddle,2,40
10298,01-Apr-10,Ear Muffs,1,12.5
10330,19-Apr-10,Shovel,1,16.75
