SQL or Structured Query Language is a programming language used to communicate, manage, and analyze data in databases. In this blog, we will discuss in detail how to use SQL for data retrieval, reporting, and analysis. We will assume that you have zero experience with SQL so that you can learn easily. Moreover, we will use the free tools in this blog to write SQL queries. So, let's get started!
Why Learn SQL?
The very first question that should be addressed is why learn SQL when there are so many other programming languages? The one-word answer is "data analytics".
All organizations manage their data in databases. The data holds many hidden insights that are highly valuable. For example, an employee can write a SQL query to know how many times a specific button on the website is pressed. Based on the results received from the query, the team can then decide whether to make the button more prominent or do other stuff around it. Therefore, SQL helps in extracting valuable hidden insights from the data. This blog is specifically focusing on how to use SQL for extracting data from databases, but you can also use SQL for creating a database, adding new tables, etc.
What is SQL?
SQL stands for Structured Query Language. It is a programming language for databases that helps you easily interact with them, such as retrieve data, filter, sort, update, remove, and do a lot more with the databases.
What is a Database?
In simple words, the database is where the data resides in the form of tables. For example, an e-commerce store uses databases that include tables, such as order ID, customer name, product information, inventory details, and so on. In short, all businesses use databases to manage data and access it easily when needed.
Relational Database Management System (RDMS)
RDMS is one of the database management systems in which data is stored in a row-based table structure. You probably have heard about Oracle, MySQL, Postgres, SQL Server, and similar others. All these are Relational Database Management Systems (RDMS) that use SQL as the standard language for database management. So, in order to learn how to use SQL, we will use Microsoft SQL Server, as it is free to use and highly popular.
Install SQL Server
The first step to learning SQL is to download the RDMS of your choice. Since we are going to use SQL Server, so follow the below steps to install it:
- Go to the SQL Server download link https://www.microsoft.com/en-us/sql-server/sql-server-downloads.
- Scroll down until you see two download options, i.e., Developer and Express.
- Developer: This version offers full SQL Server functionality and is meant to only be used for development and testing. Therefore, it is not for business purposes.
- Express: This version is meant for business purposes, but offers limited functionality, such as the database can be only 10 GB in size.
For this tutorial, both versions can serve the job. But let's pick the Developer version. So, click the "Download now" button beneath the Developer option.
- Once the installation starts, you get three installation types, i.e., Basic, Custom, and Download Media. Let's go for "Basic" for now.
Once the installation is completed, you won't see an app icon on your desktop that you usually see when you install other apps normally. However, your SQL Server is now running in the background. To check that, open the Windows search bar, type "SQL Server Configuration Manager", and open the best match option. From there, you will see that the SQL Server state is "Running". Moreover, you can also pause or stop it from here.
Install SQL Server Management Studio
Now that the server is running successfully, you have to install the SQL Server Management Studio (SSMS). It will act as a graphical front-end that will make it easy for you to write SQL queries for the database. Follow the below steps to install SSMS:
- Go to the SSMS download link https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms
- Scroll down and click "Free Download for SQL Server Management Studio".
- After the download, follow the on-screen installation process to complete the installation.
Connect to Server
Once SSMS is installed, launch it. As soon as it launches, it will ask you to connect to the server, which we have just created above. Under the "Server name", it should automatically fill it with your PC name. If not, then click the drop-down menu, expand the "Database Engine" category, and select your PC name. Once done, click "Connect".
Object Explorer
After connecting to the server, you will see the main dashboard of SSMS that you will now use to write SQL queries. On the left side, you see "Object Explorer" which offers a lot of functionality. If you click the plug-shaped icon, you can connect to more servers and access their databases all at once.
Under your current connected server, you see different folders, such as databases, security, server objects, etc. For this tutorial, we will only stick with the "Databases" folder.
Add Database
If you click the "+" icon next to the "Databases" folder, you will see that it shows no database currently. This means that you either have to create a new database or add an existing one. Since we are focusing on learning data retrieval and analysis, so let's use an existing database. We will use the sample database created by Kevin Stratvert. So, follow the below steps to add a database in SSMS:
- Download the sample database by clicking here.
- Right-click the "Databases" folder and then click "Restore Database".
- Click "Device" and then click the 3-dot icon.
- Click "Add" and then open the location where you saved the sample database file. Select it and click "OK".
This way, the database will be added to SSMS and you can also see it from the left sidebar. Click the "KCC" database and there you will see multiple folders. We will mostly focus on the "Tables" folder. Click the "+" icon of tables and there you will see four tables, i.e., Customers, Order_Product, Orders, and Product.
Why Use Multiple Tables?
You probably are thinking why do we use multiple tables when we can put everything in one table. The reason for creating multiple tables is to organize the data efficiently and keep the tables small. For example, if you are running an e-commerce store and you prefer to keep one table only. What it will mean is that the customer names, customer addresses, product details, and similar other information will repeat multiple times with every order ID. So, the table will look like a mess. Alternatively, you can create a separate customer table that contains customer details and give each customer a specific ID number. This way, your order table will just point to the customer ID, not all his/her details. This is why it's called relational databases because the tables are linked to each other.
Table Naming
You might notice that the four tables in the sample database contain the common word "dbo". dbo stands for Database Owner, which is a default schema name that helps to bucket together multiple tables. Moreover, it is also used to access control such that some users have access to one schema, while others have access to different ones.
Table View
Now let's explore what we have got in the four tables of our sample database. To view the table, right-click "dbo.Customers" and click "Select Top 1000 Rows". Once done, you will see two sections on the right. The top section shows the SQL query and the bottom section shows the result of the query, i.e., the top 1000 rows of the "Customers" table. The table structure you see at the bottom is similar to any spreadsheet with multiple rows and columns. In a database, columns are referred as "Fields", while rows are referred to as "Records".
Now let's open the "dbo.Orders" table by right-clicking it and then clicking "Select Top 1000 Rows". In this table, you will notice a column named "Customer ID". It narrates that this ID customer has made the order. To know more details about the customer, go to the "Customers" table and see the details of the customer with that ID.
Primary Key
Before we jump deep into SQL queries, it's important that you know what is a primary key. A primary key is the minimum number of columns required to uniquely identify a row. Let's take the "Customers" table as an example. There you cannot set customer name, address, city, or other similar columns as the primary key because they can match multiple times for different customers. Therefore, the primary key is set as customer ID because it is unique and quickly gives the idea of the row to look for.
Database Diagrams
SSMS allows you to visualize how the database tables are linked including their fields through an intuitive database diagram. To access the database diagram of our sample database, click the "+" icon next to "Database Diagrams" from the left sidebar and then click "dbo.Diagram". You can also create a new database diagram, by right-clicking the "Database Diagrams" folder and then click "New Database Diagram".
Once the database diagram loads, you can see all 4 tables of your database including all the fields inside those tables. You will also see a key icon, indicating which one is the primary key in every table. Moreover, you will see a line connecting tables together. The two-circles icon indicate that the specific field has one-to-many relationships with the other table fields. For example, the "CustomerID" has a relationship with all the fields of the "Order" table. This way, you can easily visualize your database using the diagram.
Data Types
You can also check the data types of the fields within a table. To check that, right-click any table from the left sidebar and then click "Design". There you will see all the fields of that table and their corresponding data types. This will help you when writing SQL queries.
Writing SQL Queries
Now that you know a lot of the basics of SQL and SSMS, it's time to start writing SQL queries. To create a query, click "New Query" from the top menu bar. It will open a new window where you can write the query. But first, you have to recheck one thing. Under the top menu bar, there is a drop-down menu that says "KCC" currently. It indicates which database is selected. So, if you have multiple databases, you can change them from here.
- SQL Query – Extract Customer Names
For the first query, let's write a query to see all the customer names present in the "Customers" table. From the left sidebar, click the "+" icon next to "dbo.Customers" and then click the "+" icon next to "Columns". This way, now you have a clear view of all the field names in the table.
The syntax for the first query will be:
Select CustomerName From dbo.Customers
Here "Select" represents which field you want to access and "From" represents which table you want to access. Once you have written this query in the query window, click the "Execute" button from the top. This way, it will show all the customer names.
- SQL Query – Extract Customer Names and Records
The first SQL query was an easy one. So, let's now write a similar but a different one. This time, we intend to extract both the "Customer Names" and "Notes" for each customer from the "Customers" table. The syntax for this query will be:
Select CustomerName, Notes From dbo.Customers
Once you execute this query, you will now also see the notes along with the customer names.
- SQL Query – Extract Customer Names and Records involving Multiple Databases
As you get more involved with SQL, you might be using multiple databases at one time. So, if another database is selected currently and you intend to execute the above query, then it would fail. So, if you are using multiple databases, then the syntax for the above query should be:
Select CustomerName, Notes From KCC.dbo.Customers
This way, you are now also mentioning the database.
- Fourth Query – Aliases
From the above queries, if you look into the results section, you will notice that the column header "CustomerName" has no space currently. You can easily add the space by writing the query using aliases, like this:
Select CustomerName as [Customer Name], Notes From KCC.dbo.Customers
This way, the column header will have space. You can also change the header name to anything else this way.
- SQL Query – Extract only Distinct Customer Names
If you look into the "Customers" table, you will see that one customer's name is written twice. It might be because the customer had two different addresses. So, let's write a query where we intend to extract only distinct customers and all the duplicates excluded. The syntax of this query will be:
Select distinct CustomerName as [Customer Name] From KCC.dbo.Customers
That's it. So, now when you execute this query, you will see only distinct customer names.
- SQL Query – Extract All Fields of Customers Table
Let's say you want to show all the fields of the "Customers" table. So, it does not mean you have to write all the field names in the Select part. There is an easy way to do it, as follows:
Select * From KCC.dbo.Customers
This way, it will populate all the fields of the "Customers" table. Similarly, if you just want to extract only the top few records, then the syntax will be:
Select top (3) * From KCC.dbo.Customers
This way, you will only see the top three records.
- Filtering Query
Let's write a more advanced query now. Since your "Customers" table contains a field named "State", let's say you want to extract all the customers present in Washington State. The syntax of the query will be:
Select * From KCC.dbo.Customers where State = 'WA'
Here "State" represents the field name we want to search for and "WA" as the characters to look for. Once you execute it, you will see a list of all customers located in Washington State.
You can also add spacing in your query syntax. So, the above query can also be written as:
Select *
From KCC.dbo.Customers
where State = 'WA'
Similarly, you can also add comments to your query by typing "--" and then writing the comment.
Other than looking for customers in Washington State, you can also look for customers not located in Washington State. In that case, the syntax will be:
Select * From KCC.dbo.Customers where State <> 'WA'
This way, you will see all customers other than the ones located in Washington. You can also replace "<>" with "!=" and get the same results.
- Filtering Query with OR
What if you want to extract customers that are located in Washington or New York State? For that, you will have to use the "OR" function. Here's how to do it:
Select * From KCC.dbo.Customers where State = 'WA' or State = 'NY'
Similarly, if you also want to include Utah state, then the syntax will be;
Select * From KCC.dbo.Customers where State = 'WA' or State = 'NY' or State = 'UT'
However, you might notice that the syntax is becoming lengthy. This is where you can use IN function to reduce the syntax as:
Select * From KCC.dbo.Customers where State IN('WA', 'NY', 'UT')
This syntax will also provide the same result. You can replace "IN" with "OUT" in the above syntax to get the list of customers not present in the above states.
- Filtering Query with AND
Let's say you want to look for a specific person's name (John) and also want to make sure that he/she is located in the United States. For this query, you will replace "OR" with "AND" and also use the "Country" field instead of the "State" field from the "Customers" table. The syntax will be as follows:
Select * From KCC.dbo.Customers where CustomerName = 'John' AND Country = 'United States'
This way, you can easily use AND to filter out the database more precisely.
- Filtering Query with AND OR Combined
You can also use both the AND OR functions together. Check out the below syntax:
Select * From KCC.dbo.Customers where CustomerName = 'John' AND Country = 'United States' OR Country = 'France'
What this syntax will do is it will look for a customer name John that is located in the United States or France. You can also add parenthesis in the syntax to make the AND OR statement easier to read, as follows:
Select * From KCC.dbo.Customers where CustomerName = 'John' AND (Country = 'United States' OR Country = 'France')
- Filtering Query with LIKE
Instead of looking for one specific person, let's say you want to look for all the customers whose names start from the character "A". For that, you have to use the LIKE function. The syntax will be like this:
Select * From KCC.dbo.Customers where CustomerName Like 'A%' AND (Country = 'United States' OR Country = 'France')
Here the "%" means that the first character should be "A", but the remaining characters can be different. You can also change "Like" to "Not Like" to see all the customer names not starting with A.
- Filtering Query with Numerical Values
Since we have learned a lot about filtering queries involving characters, let's now shift to numerical values. For that, let's take the "Orders" table and write a query that only shows those order details that have an order total of above 1000.
For that, first right-click the "Orders" table and click "Select Top 1000 Rows". It will populate the syntax and the query result. Now beneath the query, type this:
WHERE OrderTotal > 1000
Now when you execute it, the query will show only orders with a total above 1000. You can change ">" with ">=" or "<". Similarly, you can also write something like:
WHERE OrderTotal between 1000 and 2000
This will show all the orders with a total between 1000 and 2000.
- SQL Query – Inner Join & Outer Join
Until now, we have learned how to write a query for one specific table. Now let's discuss how you can write a query involving multiple tables. For example, the "Orders" table contains customer IDs but not their information. So, let's write a query that extracts the customer details from the "Customers" table and populate them against the right customer IDs along with the "Orders" table fields. The syntax will look like this:
Select OrderID, OrderDate, OrderTotal, CustomerName, Phone
From dbo.Orders
Join dbo.Customers on dbo.Orders.CustomerID = dbo.Customers.CustomerID
Here the Join operator is used to link the "Customers" table with the "Orders" table and the linking field between them is "CustomerID". Once you execute the query, it will populate the "Orders" fields along with customer names and phone numbers.
If you change "Join" with "Inner Join", you will get the same results. What the "Inner Join" command means is that you will get all the customers who have the order along with all the orders that have a customer. But what if you have a customer that does not has an order currently? Then it won't show up in the list. To show up that customer, the syntax will become:
Select OrderID, OrderDate, OrderTotal, CustomerName, Phone
From dbo.Orders
Right outer Join dbo.Customers on dbo.Orders.CustomerID = dbo.Customers.CustomerID
Once you execute it, you will also see all the customers that don't even have the orders currently. So, what this "right outer" operator does is that it gives back all the customers even if they don't have the orders.
Similarly, if you want to see all orders whether or not they have customers, the syntax will become:
Select OrderID, OrderDate, OrderTotal, CustomerName, Phone
From dbo.Orders
Left outer Join dbo.Customers on dbo.Orders.CustomerID = dbo.Customers.CustomerID
Since the "Orders" table is on the left side of our syntax, so we simply changed "Right" to "Left".
One point to note here is that, if you also want to show customer IDs in the result and you type a syntax like this:
Select OrderID, OrderDate, OrderTotal, CustomerName, Phone, CustomerID
From dbo.Orders
Left outer Join dbo.Customers on dbo.Orders.CustomerID = dbo.Customers.CustomerID
You will get an error message. It is because the CustomerID field is present in both "Customers" and "Orders" tables. So, it does not know from where to pick. The right syntax will be:
Select OrderID, OrderDate, OrderTotal, CustomerName, Phone, dbo.Customers.CustomerID
From dbo.Orders
Left outer Join dbo.Customers on dbo.Orders.CustomerID = dbo.Customers.CustomerID
- SQL Query – Order By
Continuing on our above query, let's say you now want to order the results based on order totals. The syntax will become:
Select OrderID, OrderDate, OrderTotal, CustomerName, Phone, dbo.Customers.CustomerID
From dbo.Orders
Left outer Join dbo.Customers on dbo.Orders.CustomerID = dbo.Customers.CustomerID
Order by OrderTotal
This way, you will get the results based on order totals in ascending order. You can also change it to descending order by changing the syntax to "Order by OrderTotal desc".
- SQL Query using Functions
You also get access to tons of functions, such as sum, count, getdate, and many more. For example, if you want to get a list of orders for the past month, the syntax will be:
Select * From dbo.Orders where OrderDate >= '7/11/2022'
This way, you get all the orders that are on and after 11th July. But let's say you want to run the same query after one week and want to see the orders of the past month from that date. Now the above query will not work rightly. Instead, we will use the function called "Dateadd" with which you don't have to specify the new dates every time. The format of the Dateadd function is as follows:
DATEADD(interval, increment int, expression smalldatetime) RETURNS smalldatetime. So, the syntax will become:
Select * From dbo.Orders where OrderDate >= Dateadd (month, -1, getdate())
Here "-1" indicates the last month and getdate() is another function that provides the current date. So, now when you execute this query anytime, it will show the order list of the last month from that date.
You can also use aggregate functions such as count, sum, and similar others for getting insights about your order. For example, if you want to count the number of orders for the past month, then the syntax will be:
Select count(*) From dbo.Orders where OrderDate >= Dateadd (month, -1, getdate())
Similarly, if you want to sum all the order totals from the past month, the syntax will become:
Select sum(OrderTotal) From dbo.Orders where OrderDate >= Dateadd (month, -1, getdate())
This way, you can use other aggregate functions for getting useful insights.
- SQL Query – Group By
You can also find the order total based on customer ID. For that, you have to use the "Group By" operator. The syntax for this query will be:
Select sum(OrderTotal) From dbo.Orders where OrderDate >= Dateadd (month, -1, getdate()) group by CustomerID
This way, you can see the order total specific to the customer ID.
Bonus Tip: Query Designer
We have written many queries till now including both simple and complex ones. But there is a much easier way to write queries using Query Designer. Open a new query window, right-click, and then click "Design Query in Editor". It will open the Query Designer. From there, select "Customers" and "Orders" tables using the CTRL button and then click "OK".
You will see the table diagram of both the tables including how they are linked. Now let's say you want to create a query that shows customer names and all of their orders. So, tick the checkbox next to CustomerName, OrderID, OrderDate, and OrderTotal. Afterward, click "OK". You will see that it automatically writes the query for you. Now if you execute the query, it will show the results just as you wanted.
To go back to the Query Designer, select the whole query, right-click, and then click "Design Query in Editor". It will take you back to the Query Designer. From there, you can also notice that you can add Alias, sort, and even filter. Moreover, right-click the diagram part and then click "Add Group By". This way, you can add Group By as discussed above. In short, Query Designer makes it a lot easier to design queries without remembering the syntax.
SQL is a powerful programming language that makes it easy to extract useful insights from large databases. The above-discussed queries can help you grab the initial concepts on how to write SQL queries. So, test out the above queries yourself to have a better hands-on experience.
If you need more IT-related services, then Sun IT Solutions, the leading managed service Provider in Toronto, is readily available to assist you. We are the reputed provider of IT solutions in Canada, including services such as network support, remote IT support, IT consulting, managed IT services, business continuity planning, managed firewall, and many more. In short, we are the one-stop, trustworthy managed services provider in Toronto or anywhere you want.