# Basic schemas for database and tables creation, bulk import from .csv and basic queries

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:

1. Are there any better ways for constructing database and table schemas?
2. Are there any better ways to populate tables from .csv files?
3. Best practices for error-handling. Print on external file or as query output?
4. 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)
)
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 Moores 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
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
10298,01-Apr-10,Ear Muffs,1,12.5
10330,19-Apr-10,Shovel,1,16.75

• Some quick remarks (from standard ETL practices): load data into a staging area, temp tables will do, and then load (insert) them into the target tables, so you can first apply validations and reject an entire batch when something isn't OK. It also allows you to transform data before loading. For example, you should normalize the customer data: store cities and states in separate tables and have foreign keys from customer to city and city to state. Jan 1, 2021 at 18:56
• @GertArnold much appreciated advice. Will start working on it right away. Will be posting updates. Jan 1, 2021 at 19:15
• Why MSSQL? Is this for work? Jan 2, 2021 at 1:50
• @Reinderien school. Taking it one step further just to obtain the right knowledge. Jan 2, 2021 at 2:55
• @Reinderien, MSSQL Server specifically. The goal of the course is for the student to obtain skills and knowledge enough to build and manage Data Mart, which is the final assignment of the course. What is posted above is an assessment of the first few lessons in general MSSQL Server. The sole goal of the question is educational as well. The aim is to obtain solid foundations in SQL Database Administration by learning best practices. Jan 2, 2021 at 15:54

## Data location

This is probably a weirdness from MSSQL itself and not specifically your setup, but your database location:

FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\SQLAssessment.mdf',



as well as MSSQL's own default Database Engine data file location:

\Program Files\Microsoft SQL Server\MSSQL{nn}.<InstanceID>\


are surprising to me. Program Files should only contain application binaries and non-user-data resources. A better place for database files would either be ProgramData or the home directory of the service user. Unix-like operating systems enforce this more often than Windows. All of that said, changing this might be an up-hill battle if there are spooky dependencies on the data directory setting.

## Parametric paths

C:\Users\tsenk\OneDrive\Работен плот should not appear in your script, and this kind of thing should not be hard-coded. There are reasonable ways to pass parameters into your script instead.

## Unneeded group-by

Are these group clauses from queries 2

GROUP BY
[SQLAssessment].[dbo].[Customers].[customerid],
[SQLAssessment].[dbo].[Customers].[firstname],
[SQLAssessment].[dbo].[Customers].[lastname],
[SQLAssessment].[dbo].[Customers].[city],
[SQLAssessment].[dbo].[Customers].[state]


and 3

GROUP BY
c.[customerid],
c.[firstname],
c.[lastname],
cp.[item]


strictly necessary? There's no corresponding aggregate in either select.

## Don't hard-code IDs

Queries 3, 5, 6 and 8 should not hard-code c.[customerid] in a where. Compare the first and last names instead.

## Data types

Consider either smallmoney or money for your price column.

## Logging

Best practices for error-handling. Print on external file or as query output?

This is difficult to answer in general, as it will be influenced by context; but thinking to the lowest-barrier-to-entry for all of the common logging aggregators, file-based logs will present less of an integration fight. So keep your

    ) LOG ON (
NAME = N'Products_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\SQLAssessment.ldf',
SIZE = 1024KB,
FILEGROWTH = 10 %
)


but, per "Data Location" above, it doesn't make sense to live in Program Files.

## Fully-qualified object references

How the syntax can be improved, but provide somewhat foolproofness if the wrong database is in use. Practical and convenient, or complicated and accurate?

That's a little difficult for me to decode, but I assume you're talking about this reference style:

[SQLAssessment].[dbo].[Customers]


You're debating whether or not database and schema prefixes should exist in your object references. Generally I would want to omit these, the reason being maintainability. If the database or schema name ever change, a script littered with fully-qualified references will require many changes, but a script that can assume a correctly set current schema will need few or no changes. SQL Server has well-understood mechanisms for setting the current schema.

• I am grateful for the time you invested in providing an answer. I have taken note to the corrections that you suggest and applied them. If you can throw in some pointers to error handling best practices and on how a script can be made more secure in terms of referencing databases and tables (points 3 and 4), I will gladly mark your answer as a solution. Jan 5, 2021 at 13:14
• I've attempted to address those questions. Jan 8, 2021 at 2:11
• I agree with the data location. On a production environment, you probably also would put the log file on a different (logical and physical) drive than the data files (e.g. C:\Data\SQLAssessment.mdf and D:\Data\SQLAssessment.ldf) and an auto-growth value of 1MB would give you terrible fragmentation, use 50 or 100MB instead (in production). Jan 28, 2021 at 23:34
• I would only put database names in a query if it spans multiple databases. Reason: Imagine you need to perform some challenging database reengineering and decide to restore a copy of your DB called SQLAssessmentTryOut` side-by-side by your normal SQLAssessment DB on the test system. You would now have to adjust every single query in the database as well as the application to point to the new database. If you don't database-qualify the objects, you only adjust the connection string of the application and everything works just the same - just on a different database. Jan 28, 2021 at 23:53
• @Christoph Half of these comments seem like they should be in your own answer, not on mine. Jan 29, 2021 at 0:20