Basic SQL Operations— SQL fundamentals
It’s been a while since I’ve had some hands-on experience with SQL so I wanted to take a stab at creating some tables using SQL Server Management Studio 19 and using some basic clauses and functions of SQL. We will create two tables of imaginative employee data and use SQL to gather more specific data from them.
Creating Tables:
We start by creating some tables that have employee demographics starting with EmployeeID, first and last name, age and gender while setting the type of data that will be captured.
Next we will create an EmployeeSalary table that will include EmployeeID, JobTitle, and Salary with corresponding data types.
Inserting Data:
We will add some employee demographic information to the EmployeeDemographic table.
Let’s insert the salary information into the EmployeeSalary table
Viewing Tables:
Using Various Functions:
Now that we have some tables with basic information, we can utilize SQL to grab whatever we may need.
Suppose we wanted to identify the distinct EmployeeIDs in our EmployeeDemographics table. We could use the DISTINCT clause:
This will pull up our 11 distinct employees by their EmployeeID.
Let’s suppose we wanted to determine the maximum, minimum or average salary of our staff in the tables. We can use the MAX, MIN, or AVG function to tell us:
Here we selected the average salary using AVG(Salary) and used the AS function to name the column ‘AverageSalary’. This helps identify the new column by the name we give it otherwise it will return a column without a name. We can see the average salary for our 11 employees is $66,818.
WHERE:
Using WHERE allows us to drill down and pull specific information. If we wanted to pull information on a specific employee, we can use the WHERE clause to select only what we need:
Above we selected all of the employees who are over the age of 30 years old. We can also use the AND and OR clause to select more specific information from the tables. Suppose we wanted to select employees who are older than 30 years old AND/OR who are ‘Female’ we could do so by using:
SELECT *
Get Brent Maloney’s stories in your inbox
Join Medium for free to get updates from this writer.
FROM EmployeeSalary
WHERE Age > 30 AND/OR Gender = ‘Female’
The AND clause will return staff who are 30 years old or older AND Female while the OR clause will return employees who are either over 30 or Female. Since the data in the tables are very small compared to realistic datasets, there are other uses of the WHERE clause that can be helpful when working with larger datasets. We’ll save those for a later project.
GROUP BY and ORDER BY:
Let’s use GROUP BY to help us count how many male and female employees we have:
Notice we didn’t use the AS clause thus we return a count column with no name. Let’s re-run the code but using AS to name the column:
Now let’s order our table based on age and put it in ascending order:
Another, possibly simpler, way of doing this when ordering by multiple columns is to use the column number rather than the name. In the example, the statement above could be written as follows:
SELECT *
FROM EmployeeDemographics
ORDER BY 4 ASC (4 represents the age column, which we are ordering in ascending order, which is the 4th column in the table).
GROUP and ORDER BY are helpful clauses when needing to select specific observations and attributes from tables that are much larger than the one we are using today. They can also be combined meaning we could select, group and order by multiple columns.
Removing Duplicates:
When originally creating the EmployeeDemographic table, I realized I accidentally added two duplicates. We can use the DISTINCT and ORDER BY clause to remove those:
We used DISTINCT to identify each employee and GROUPed them BY their EmployeeID to get a cleaner table. I will explore other ways to remove duplicates as time goes on as there are a few different ways to remove duplicates. For the purposes of this, I’ll simply use this function.
JOINS:
We will now combine both tables for one output. We notice that both tables have one common observation, EmployeeID. We can use JOIN to combine both of these tables.
We selected all of the observations from EmployeeDemographics and INNER JOINed them with the EmployeeSalary while combining them with the ON statement and identified the tables and the common observation, in this case the EmployeeID. It’s helpful to remember that when using ON, the first statement will start with the table you are selecting from (in this case EmployeeDemographics) followed by a “.” then the observation that joins both tables (in this case, EmployeeID). We then finish with “=” and the same method again (in this case, EmployeeSalary.EmployeeID).
If we were to use RIGHT, LEFT or OUTTER JOINs we would return some NULL values. I will save those for another day but before concluding, let’s do one more JOIN:
Same join as before combining both tables but what we did here was remove Janet by using ‘not equal’ to function (“<>”) and then ordering the data by the employee salary and putting it in ascending order.
A quick note on UNION versus JOIN. When using the JOIN clauses, we can combine two different tables in what I like to refer to in a ‘side-by-side’ way meaning the two tables will be joined by a shared observation and the tables will essentially sit next to one another. UNION can do the same, but I mentally picture this clause as combining two tables by a shared observation ‘on top of one another’. Below is an helpful illustration from Data School.
It’s been a few weeks since I’ve been exposed and used SQL in Google’s Data Analyst Certification so this simple exercise was intended to help keep it fresh in my mind.
Help needed! Prior to this I had attempted to upload some data into SQL Server Management Studio 19 but due to the 32-bit vs. 64-bit issue, I was prompted with an error. My understanding is that SSMS 19 is 64-bit thus the SQL Server Import and Export is also 62-bit and I haven’t figured out how to get 32-bit. If anyone has any suggestions on how I can work around this issue, it would be greatly appreciated.
