Business Analyst - SQL Survival Guide

In this lesson we'll examine what a database is, and how they are used by applications.

Understanding how databases are used reveals how data is getting into a database in the first place and provides the context not only for later lessons, but also for the typical environment you'll be faced with as a Business Analyst where you'll be defining requirements that at some point will require an application to process data or capture new data.

In this lesson we'll look at data warehouses and answer the questions, what are they, and how organisations use them?

We will also look at how access to databases and data warehouses is controlled, and how you might go about requesting the access you need in order to connect to it.

In this lesson we will look at an Entity Relationship Diagram (ERD). An ERD is to a database what a blue print is to a house and understanding the concepts will not only help you create your own database in a later lesson, but also understand the relationship types between tables, which is crucial for designing effective queries.

In this lesson we will download and install MS SQL Server, and MS SQL Server Management Studio v17.

Important

• If you don't feel comfortable creating your own database just now, you only need to download and install MS SQL Management Studio and then proceed to lesson 3-5.

• SQL Management Studio is currently only available for Microsoft Windows. Before you attempt to download and install, please check the lesson resources for a link to the minimum system requirements.

In this lesson we'll take a quick tour around SQL Management Studio's user interface (UI) and look at the following;

• What each part of the UI is for

• How to navigate a database structure

• How to start a new query

• How to execute a query

• How to save a query to a specific location

In this lesson we'll create a test database from an ERD using SQL Management Studio that will bring to life some of the database design concepts we discussed in the previous section.

If you don't feel confident enough to do this manually you can cheat by using the 'Create Database.sql' resource file and following these instructions;

1. Save the 'Create Database.sql' file to a location on your computer

2. In SQL Management Studio, goto 'File - Open - File' and select the 'Create Database.sql' file from where you saved it and then click 'Open'

3. When the file has loaded, click on the Execute button.

4. The database should now be created.

If you don't feel comfortable creating your own database just yet, or are having problems getting it configured, you can cheat by using a test database on my server - please go to Lesson 3-5 to find out how.

In this lesson we'll run a script that will populate the test database with data that will be used in the next section.

If you don't feel comfortable creating your own database, or are having problems and want to get started whilst you're figuring them out, you can connect to a test database on my server that is designed for this course.

Important: You will only have read only access to the databases on this server. If you want to practise the commands that are covered in the bonus section (Section 5 - Insert, Update, and Delete) you will need to create your own local database as shown in Sections 3-3 and 3-4.

In this lesson we will look at querying data from a database using SELECT statements.

In this section we will look at some additional commands that can be used to make your queries more powerful so you get the data you need from a database.

In this lesson we'll look at joining tables together in a database using the JOIN command.

In this lesson we will look at transforming data using the SELECT CASE command.

In this lesson we will be looking at how to insert data into a database using the INSERT statement.

In this lesson we will be looking at how to update data that is already in the database using the UPDATE statement.

In this lesson we will be looking at how to delete data from a database using the DELETE statement.

Note - if you accidentally delete more data than you intended, or you want to put the original data back into the database, go back to Lesson 3-3 and run the Insert Data into tables.sql file.

In this lesson we'll review the course against the objective that was set at the beginning of the course and revisit some of the key concepts we have covered.

If SQL was brand new to you when you started the course, it is worthwhile repeating some of the introductory lessons after you have completed the entire course as they will now probably make more sense.

Don't forget to practice, practice, practise! SQL is a language like any other so using it frequently is key to keeping your skills sharp.