sql – Basic schemas for database and tables creation, bulk import from .csv and basic queries enhancement and improvement

The following SQL code is for the purpose of asesing basic SQL Server literacy for university assignment.

I am creating database and tables schemas, inserting data from .csv files.
Constructing 8 simple qeries as answers to assignment tasks.

The code works well, but as I am very new to MSSQL Server I need some advise 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?

Suggestions are welcome.

Assesment questions can be found commented-out prior to every query.

Full code provided below. Comma-separated sample datasets can be found at the bottom.

Thank you in advance.

USE master
GO

---------------------------------------------------------------------------------------
-- Create database

IF NOT EXISTS (
        SELECT
            *
        FROM
            SYS.DATABASES
        WHERE
            NAME = N'TelenorAssignment'
)BEGIN
    CREATE DATABASE (TelenorAssignment) ON PRIMARY (
        NAME = N'TelenorAssignment',
        FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL15.MSSQLSERVERMSSQLDATATelenorAssignment.mdf',
        SIZE = 10096KB,
        FILEGROWTH = 1024KB
        ) LOG ON (
            NAME = N'Products_log',
            FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL15.MSSQLSERVERMSSQLDATATelenorAssignment.ldf',
            SIZE = 1024KB,
            FILEGROWTH = 10 %
        )
END

USE (TelenorAssignment)
GO

---------------------------------------------------------------------------------------
-- Create Customers table 

IF NOT EXISTS (
    SELECT
        *
    FROM
        SYS.OBJECTS
    WHERE
        OBJECT_ID = OBJECT_ID(N'(dbo).(Customers)') 
        AND TYPE IN (N'U')
)BEGIN
    CREATE TABLE (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 (dbo).(Customers)
    FROM
        'C:customers_data.csv' WITH (  -- <--- fit for purpose
            FIRSTROW = 1,
            FIELDTERMINATOR = ',',
            ROWTERMINATOR = 'n',
            ERRORFILE = 'C:CustomersErrorRows.csv',
            TABLOCK
        )
END TRY
BEGIN CATCH
  SELECT
    ERROR_NUMBER() AS ErrorNumber,
    ERROR_LINE() AS ErrorLine,
    ERROR_MESSAGE() AS ErrorMessage;
END CATCH
GO

--SELECT * FROM (dbo).(Customers)

---------------------------------------------------------------------------------------
-- Create Customer_Purchases table

IF NOT EXISTS(
    SELECT
        *
    FROM
        SYS.OBJECTS
    WHERE
        OBJECT_ID = OBJECT_ID(N'(dbo).(Customer_Purchases)') 
        AND TYPE IN (N'U')
) BEGIN
    CREATE TABLE (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 (dbo).(Customers)(customerid)
            ON DELETE CASCADE
            ON UPDATE CASCADE
    )
END
GO

---------------------------------------------------------------------------------------
-- Populate Customer_Purchases table from .csv

BEGIN TRY
    BULK INSERT (dbo).(Customer_Purchases)
    FROM
        'C:customer_purchases_data.csv' WITH (  -- <--- fit for purpose
            FIRSTROW = 1,
            FIELDTERMINATOR = ',',
            ROWTERMINATOR = 'n',
            ERRORFILE = 'C:CustomerPurchasesErrorRows.csv',
            TABLOCK
        )
END TRY
BEGIN CATCH
  SELECT
    ERROR_NUMBER() AS ErrorNumber,
    ERROR_LINE() AS ErrorLine,
    ERROR_MESSAGE() AS ErrorMessage;
END CATCH
GO

SELECT * FROM (dbo).(Customers)
SELECT * FROM (dbo).(Customer_Purchases)



---------------------------------------------------------------------------------------
-- 1. Count clients by state and show in descending order. 

SELECT 
    (TelenorAssignment).(dbo).(Customers).(state), 
    COUNT((TelenorAssignment).(dbo).(Customers).(customerid)) AS (customercount) 
FROM 
    (TelenorAssignment).(dbo).(Customers)
WHERE 
    (TelenorAssignment).(dbo).(Customers).(state) IS NOT NULL 
GROUP BY (TelenorAssignment).(dbo).(Customers).(state)
ORDER BY 
    (customercount) DESC

---------------------------------------------------------------------------------------
-- 2. Show first and last name of the customers who are presented with their first name more than once. 

SELECT
    (TelenorAssignment).(dbo).(Customers).(customerid),
    (TelenorAssignment).(dbo).(Customers).(firstname), 
    (TelenorAssignment).(dbo).(Customers).(lastname),
    (TelenorAssignment).(dbo).(Customers).(city),
    (TelenorAssignment).(dbo).(Customers).(state)
FROM
    (TelenorAssignment).(dbo).(Customers)
WHERE
    (TelenorAssignment).(dbo).(Customers).(firstname) IN(
        SELECT
            (TelenorAssignment).(dbo).(Customers).(firstname)
        FROM
            (TelenorAssignment).(dbo).(Customers)
        GROUP BY
            (TelenorAssignment).(dbo).(Customers).(firstname)
        HAVING
            COUNT((TelenorAssignment).(dbo).(Customers).(firstname)) > 1
      )
GROUP BY
    (TelenorAssignment).(dbo).(Customers).(customerid),
    (TelenorAssignment).(dbo).(Customers).(firstname), 
    (TelenorAssignment).(dbo).(Customers).(lastname),
    (TelenorAssignment).(dbo).(Customers).(city),
    (TelenorAssignment).(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 
    (TelenorAssignment).(dbo).(Customers) AS c
INNER JOIN (TelenorAssignment).(dbo).(Customer_Purchases) AS cp
ON c.(customerid) = cp.(customerid)
WHERE 
    cp.(item) IN (
        SELECT
            (TelenorAssignment).(dbo).(Customer_Purchases).(item)
        FROM
            (TelenorAssignment).(dbo).(Customer_Purchases)
        WHERE 
            (TelenorAssignment).(dbo).(Customer_Purchases).(item) LIKE 'Flashlight'
        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 (TelenorAssignment).(dbo).(Customers) AS c
INNER JOIN (TelenorAssignment).(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 (TelenorAssignment).(dbo).(Customers) AS c
LEFT JOIN (TelenorAssignment).(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 (TelenorAssignment).(dbo).(Customers) AS c
LEFT JOIN (TelenorAssignment).(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 (TelenorAssignment).(dbo).(Customers) AS c
INNER JOIN (TelenorAssignment).(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 (TelenorAssignment).(dbo).(Customers) AS c
INNER JOIN (TelenorAssignment).(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