Getting Started with SQL Databases (an Excel User’s Perspective)

Most of my readers are heavy Excel users. Today we will transition our strong Excel foundation into learning another complementary tool, SQL Databases.

Here are a few components that are fundamental to Databases and SQL.

Tables – Other concepts could come first, however, a Table is a familiar starting place for an audience of Excel users. Let’s see why:

As you probably know, a table is a collection of data with rows displayed horizontally and columns running vertically. In Excel, the columns are labeled with letters and Rows have numbers.

 

 

It’s the same way in a database, Rows, and Columns!

Using the Grid view in Microsoft SQL Server, you can see the familiar table layout.

 

 

Database – A database is simply a collection of tables

SQL– SQL stands for Structured Query Language. It is the language used to Maintain and Retrieve Database tables.

Here’s the simplest SQL statement that I can think of.

Select * from Table

It basically Says, Shows us(SELECT) Everything/ALL(*) FROM Table(insert table name instead of the word table).

Let’s Review the Structure of Microsoft SQL Server Management Studio, SSMS.

 

 

Server Name – The SQL Server running the databases.

Databases – Under the Server Name, the first folder should read “Databases”. Open this folder to see all the databases within a server.

Schema – A schema categorizes a collection of database objects and is specific to one database. If the schema is utilized in SSMS they are prefixed to the table name.

Tables – This is where we store all of our important data, as rows and columns.

Introduction to SQL

Remember this extremely simple query example from earlier?

Select * from Table

Let’s turn it into a real example and break down its components.

In Microsoft SQL, the first important step is making sure we are connected to the server.

Two of the most common methods of connecting to the server are:

Windows Authentication: This uses your Network Credentials or Active Directory to authenticate with the server. SSMS’s integration with Network login is a major benefit for security as permissions can be managed once by the Network Manager.

SQL Server Authentication- This form of authentication requires the use of a database Account separate from the windows network. This Account is used exclusively to log into the database. A Database Administrator has to set this up.

When possible, Use Server Name and Active Directory. It’s more manageable than having additional authorized database users outside of AD management. The Single point of Account Management reduces the security risk of having unauthorized or neglected users.

OK let’s take our simple example from earlier and make it into a real-life query.

Select * from Table

Select * from testdb.dbo.Posts

Put Simply

SELECT * From – Show us All Rows and Columns from…

testdb , This is our database Name

Dbo, Dbo is the default schema for a new database in SQL Server.

Posts, The Name of our table.

Let’s expand our example a little bit

If we wanted to only pull one record from the table we would add a WHERE condition.

   Select * from testdb.dbo.Posts where ID = '7';

Compare works similar to a filter in Excel.

Here’s the Where filter on the ID = 7 in SQL

Now here is a similar effort in Excel, filtering WHERE the number is 7. (Ctrl + Shift + L is the filter shortcut if you forgot)

 

Here are all of the basic levels of a SQL Statement. The top two are the only two mandatories for a valid query, so that’s all we used in the example above.

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

Now let’s define the rest of the levels:

SELECT – SELECT specifies which columns to display. SELECT indicates this is a query statement rather than a Modification or Addition to the data.
FROM – FROM points us to the tables we are pulling from
WHERE – WHERE determines the rows displayed and which records are filtered out
GROUP BY – This is an optional field for summarizing data
HAVING – HAVING is a filter for the Summarized data. It’s applied after the information is grouped
ORDER BY – SQL data results is inconsistently ordered unless you command it. Use ASC to display in Ascending order or DESC for descending order!

It’s like clicking this guy in Excel!

Here’s an example of the Query with all components working against valid data.

SELECT OwnerUserId, SUM(ViewCount) As SumViewCount
FROM testdb.dbo.Posts
WHERE title like ‘%Science%’
GROUP BY OwnerUserId, ViewCount, Body, Title
HAVING ViewCount >500
ORDER BY OwnerUserId ASC

Let’s break down this query by adding comments after every line of code. Two consecutive dashes – – in SQL means ignore this line of code and is used for adding comments.

The comments below are completely overkilled and for explanation purposes only. Please don’t use comments excessively because the next developer’s head will explode when they look at it.

SELECT OwnerUserId, SUM(ViewCOunt) As SumViewCount
– -Show user Owner IDs, Sum up the View Counts From the testdb
FROM testdb.dbo.Posts
– – From the testdb database,dbo schema, Posts Table
WHERE title like ‘%Science%’
– -We want to see where the Title contains the word science
GROUP BY OwnerUserId, ViewCount, Body, Title
– -Summarize the information by the Owners, Count of Views
HAVING ViewCount >500
– -Include only the sum of ViewCounts Totaling over 500.
ORDER BY SumViewCount ASC
– -Order by SumViewCount Highest to lowest

In MS SQL Server, F5 is the Shortcut to Execute a Query!

Here’s our query in Action!

Note that the HAVING operation works similar to the WHERE Clause but is used against the summarized data in the group by, not against individual records.

Hope this information serves as a helpful foundation for future database adventures. I welcome you to join me on this SQL learning journey. Please check out the other articles on Excel Shortcut’s Guide to SQL.