In the previous blog Getting Started with SQL Server Express – 1/2, we installed SQL Server. Now that SQL Server Express is up and running, we can now start tackling Big Data with SQL Management Studio. In the below, I’ll show you some key SQL Management Studio hacks to get you going.
However, before we start, a quick SQL joke.
2. Management Studio
SQL Management Studio is our interface for creating and manipulating databases. The most important things to know to get started are: logging in, creating a database, creating a table, importing data and using templates and the design query editor.
The first thing you need to know is how to connect to a database on your local machine. The trick is to log in using ‘.\SQLEXPRESS’ – make sure there is the dot then back slash.
Note that Management Studio is able to connect to both local instances of SQL Server, installed on your system, and remote instances installed on other servers. For most business analytics, connecting to a local instance will work fine.
2.2 Create a Database
The first thing you will want to do is add in a new database. This database will be the home for all tables in the project you are working on. Note that all the database and table set up is done in the ‘Object Explorer’ window on the left side, see image below. The steps to create a new database are:
- In the Object Explorer, right-click on ‘Databases’.
- Select ‘New Database’.
- Give the Database a Name e.g. ‘ChuckNorris’ in my example
2.3 Add a table
Now that we have created a database, we can add a new blank table.
- Expand your new database in Object Explorer.
- Right-click on “Tables”.
- Select “New Table…”.
- Specify the columns of the table.
- Specify the data type, see Data Types for more information.The most common are varchar for text and float for numbers.
- Close the design window and save the changes.
- Give the table a name in the “Choose Name” dialog, and press OK.
In practice for business analytics, a lot of your data will come from excel, CSV (comma separated value) flat files or other external files. The steps to import these files follows the same outline. I’ll walk you through importing a CSV file:
- Right click your Database and click on Tasks->Import Data
- Select ‘Flat File Source’ from the drop down for Data Sources
- Browse for your file, select *.* to see all the files in your directory
- Check the box ‘Column names in first row of data’ if there are headers in the CSV file
- If needed, alter any column data types in the Advanced tab on the left (this may be required when an import fails)
- Choose a destination, here we use SQL Server Native Client 10 and select the Database name e.g.ChuckNorris
- Click next and then execute the import
- If there is any error, it is usually from a data type mismatch. If ever in doubt, import the data as text such as varchar(100), where the number in the parentheses is the count of characters allowed in each cell e.g. 100 characters.
2.4 Run a query
We won’t go through writing queries in this blog, see the series Use SQL Like a Badass for the most important code for business analytics. However, to start a new Query, click on the button ‘New Query’ on the tool bar and start belting out some SQL expressions in the right part of the screen. In the below example I did a basic SELECT statement to see what is in the table Chuckquotes, which turns out to be some smashing Chuck Norris facts from Chuck Norris Facts.
2.5 Visually Create Joins with Query Designer
If you are more of a visual person, or if you forget the syntax to join tables like me, then Query Designer will help. Press CTRL+SHIFT+Q and the dialog will open. Add tables and change the join type by right clicking the join line. You can select the fields you want to include by ticking the boxes within the tables (note that clicking the first box will give you all columns.) You will see that code is generated at the bottom of the screen according to your selections.
2.6 Cheat Using Templates
It is easy to forget the correct syntax for SQL. If you press CTRL+ALT+T, you will bring up the template explorer. The template explorer has all basic commands that will help you query and manipulate your data. For instance, if I wanted to know the syntax of how to create a view, I could bring up the template explorer below on the right side of the screen.
Also, if you want to learn more about how to write a particular SQL expression, a terrific resource is W3Schools.
2.7 Adding Line Numbers
Once you start writing longer SQL expressions, it can be useful to have line numbers to see where errors are occurring if the code fails.
Click Tools->Options. Expand the ‘Text Editor’ section and then expand ‘Transact SQL.’ Under ‘General’, you should see a check box for line numbers.
That is it for Getting Started with SQL Server Express. We have gone through how to install SQL Server Express and through the basics of SQL Management Studio. For more information of how to write basic code for business analytics is SQL, have a look at the blog Use SQL Like a Badass.