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:
- 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?
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
