5
\$\begingroup\$

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)
            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
\$\endgroup\$
6
  • 2
    \$\begingroup\$ 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. \$\endgroup\$ Jan 1 '21 at 18:56
  • \$\begingroup\$ @GertArnold much appreciated advice. Will start working on it right away. Will be posting updates. \$\endgroup\$ Jan 1 '21 at 19:15
  • \$\begingroup\$ Why MSSQL? Is this for work? \$\endgroup\$
    – Reinderien
    Jan 2 '21 at 1:50
  • \$\begingroup\$ @Reinderien school. Taking it one step further just to obtain the right knowledge. \$\endgroup\$ Jan 2 '21 at 2:55
  • 1
    \$\begingroup\$ @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. \$\endgroup\$ Jan 2 '21 at 15:54
1
\$\begingroup\$

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

You ask:

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

When you ask

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.

\$\endgroup\$
9
  • \$\begingroup\$ 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. \$\endgroup\$ Jan 5 '21 at 13:14
  • 1
    \$\begingroup\$ I've attempted to address those questions. \$\endgroup\$
    – Reinderien
    Jan 8 '21 at 2:11
  • 1
    \$\begingroup\$ 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). \$\endgroup\$
    – Christoph
    Jan 28 '21 at 23:34
  • 1
    \$\begingroup\$ 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. \$\endgroup\$
    – Christoph
    Jan 28 '21 at 23:53
  • 1
    \$\begingroup\$ @Christoph Half of these comments seem like they should be in your own answer, not on mine. \$\endgroup\$
    – Reinderien
    Jan 29 '21 at 0:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.