Earlier in my career I felt I knew what I wanted to do. Cloud. And I do love it, but something I was scared of was data and everything in it. I’m sure everyone feels like that about something, but to me it seems that data was something a lot of people were or are scared of. Surely, I cannot be the only one that feels this way. However, I decided to take something I am scared of and to become the master of it. Or at least get that going and so far, I am absolutely LOVING it. It’s like exercising which I love to do after work or during my work lunches. Not necessarily the action itself but the fact that sometimes I don’t want to. It’s hard to lift heavy. It’s scary. But I enjoy pushing myself to the limits and discovering what I am capable of. It’s amazing.

Anyways, being in data is like that. It’s scary but every day that I learn more about this world, the more I love it and the less I fear it. However, I do also enjoy labbing on my personal time and decided to learn more about SQL. Seems like something I use every day, so why not learn more about, like how I did with PowerShell.

SQL LAB SETUP

Platform: Azure

Infra: SQL VM running Free SQL Server License on SQL Server 2022 running on Windows Server 2022

Region: EUS

Compute: B2ms cause I’m poor lol

Cost: .0912 USD per hour

Notes: added a inbound rule to allow RDP from my local IP only. But by default, no public inbound ports are open.

Software Installed: SQL Server Management Studio, Azure Data Studio

I setup my SQL Lab environment using this guide from MSFT: https://microsoftlearning.github.io/dp-080-Transact-SQL/Instructions/Labs/00-setup.html

Plan of Lab:

Currently going through the Learning Path called: Get Started Querying with Transact-SQL and going through each module:

I completed Module called Introduction to Transact-SQL which is what Microsoft uses for their SQL variant. However, there are exercises towards the end that wow…I have learned a ton from. If you are curious where this lab is found, you can find it here: https://microsoftlearning.github.io/dp-080-Transact-SQL/Instructions/Labs/01-get-started-with-tsql.html

What did I learn?

Learned about schemas and FQDN of a table within a schema within a database within a server.

Learned a lot about NULL values and data manipulation. Didn’t know you could concatenate and convert data to other data types and apply math to it. Similar to PowerShell. Ran through the challenges and delved deeper into how to look at columns within tables and how to essentially retrieve specific data for reports.

One of the cool things I did with the sample database was actually creating simple SQL queries based on what I learned, especially on how to handle NULL values.

Challenge 1A: Retrieve customer data

Simple.

SQL query for that: SELECT * FROM SalesLT.Customer

Explanation: SELECT statement to retrieve all rows from Customer table in SalesLT schema from the AdventureWorks database.

Challenge 1B: Retrieve customer order data. Retrieve customer name data. Create a list of all customer contact names that includes the title, first name, middle name (if any), last name, and suffix (if any) of all customers.

More complete. Working with handling of NULL values.

SQL Query:

SELECT Title, FirstName, ISNULL(MiddleName, ‘None’) AS MiddleName, LastName, ISNULL(Suffix, ”) AS Suffix

FROM SalesLT.Customer

Explanation: From the Customer table in the SalesLT schema, select the Title, First Name, Middle Name, Last Name, and Suffix except from the Middle Name, if there is a NULL value instead of showing NULL, show the value ‘None’. For the Suffix, if none, show as empty.

Challenge 1C: Retrieve customer names and phone numbers. Each customer has an assigned salesperson. You must write a query to create a call sheet that lists: The salesperson, A column named CustomerName that displays how the customer contact should be greeted (for example, Mr Smith) and the customer’s phone number.

SQL Query:

SELECT SalesPerson, Title + FirstName AS CustomerName, Phone
FROM SalesLT.Customer

Explanation: Pull all rows from the Customer table, the select columns SalesPerson, Title, FirstName and Phone. Then concatenate the Title column name with the column FirstName and create a new alias for it called CustomerName. Also list Phone.

Challenge 2A: Retrieve customer order data. Retrieve a list of customer companies. You have been asked to provide a list of all customer companies in the format Customer ID : Company Name – for example, 78: Preferred Bikes.

SQL Query:

SELECT CAST(CustomerID AS varchar(3)) + ‘: ‘ + CompanyName
FROM SalesLT.Customer

Explanation: From the Customer table, we are to pull all rows where columns are CustomerID and CompanyName however we must convert the data type on column CustomerID from integer to varchar so that we can then concatenate that with ‘:’ and the CompanyName values. We use CAST for that conversion.

Challenge 2B: Retrieve a list of sales order revisions. The SalesLT.SalesOrderHeader table contains records of sales orders. You have been asked to retrieve data for a report that shows: The purchase order number and revision number in the format * ()* – for example *PO348186287 (2)* and the order date converted to ANSI standard 102 format (yyyy.mm.dd – for example 2015.01.31).

Man, for this one, I just wanted to quickly quit. I thought this is too much work. But realized it was because I simply didn’t know. And when I started digging into what I had learned and referencing the notes and documentation, I realized I could do it and I learned more about converting data types.

SQL Query:

SELECT ‘*’ + PurchaseOrderNumber + ‘ (‘ + CAST(RevisionNumber AS nvarchar) + ‘)*’ AS ‘PO+Rev#’, CONVERT(nvarchar(30), OrderDate, 102) AS ANSIStandard102

FROM SalesLT.SalesOrderHeader

Explanation: I have a feeling that this query is kind of ugly, but it works for what the lab requests. And the way I think about this is the following way: I pull the PurchaseOrderNumber column from the SalesOrderHeader table and add some manipulation to how its presented; in this case, adding the stars at the beginning and end plus concatenating the RevisionNumber value around parenthesis. I also pull the OrderDate column and this time I convert it from datetime standard to using ANSI Standard 102.