SQL and Database for Analysts - Increase your team value
4.7 (34 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
1,322 students enrolled
Wishlisted Wishlist

Please confirm that you want to add SQL and Database for Analysts - Increase your team value to your Wishlist.

Add to Wishlist

SQL and Database for Analysts - Increase your team value

Learn basic SQL so you can communicate better with DBAs and Developers, and increase your value as a team member.
4.7 (34 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
1,322 students enrolled
Created by Tom Gillies
Last updated 2/2015
English
Current price: $10 Original price: $50 Discount: 80% off
1 day left at this price!
30-Day Money-Back Guarantee
Includes:
  • 7 hours on-demand video
  • 20 Articles
  • 1 Supplemental Resource
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • By the end of the course you will have created databases, containing several tables and associated objects.
  • You will have added, updated and deleted data in those tables.
  • You will have created report queries which retrieve and manipulate that data.
  • You will have changed what a user is allowed to see and do in a database.
View Curriculum
Requirements
  • The primary requirement for this course is an inquisitive, analytical mind. You want to "understand how things work."
  • You do not need to be a programmer or mathematician. What little programming or mathematics is needed, I will explain during the course.
  • If you are going to do the exercises, then you will need a PC capable of running MS SQL Server, and have Administrator authority to install software.
  • You will find the course easier if you have experience using a text editor such as Notepad, or Word, and a spreadsheet such as Excel. You should also know how to create and use subdirectories in the Windows directory structure.
Description

This course teaches the principles of Relational Databases and the SQL language. It is intended for "Analysts". Knowledge of Relational Databases and SQL is one of the most valued technical skills an analyst can have. It will make you a more valued member of any team.

This course is suitable for a novice. I start by explaining how Relational Databases relate to your experience in the everyday world. When you have completed the course and exercises you will have created a database containing several related tables, updated the data they contain and written many queries which extract and summarise that data.

I teach using a mixture of lectures, demonstrations and exercises. You will learn by seeing, hearing and doing. With each step you build your knowledge using what you have learned already. An on-line course sets you free to work at your own pace and to review and revisit earlier material, even after you have completed the course.

Who is the target audience?
  • Business Analysts
  • Technical Analysts
  • IT Specialists
Students Who Viewed This Course Also Viewed
Curriculum For This Course
57 Lectures
07:18:21
+
Introductions: The course, databases and the tools
5 Lectures 45:15

Welcome to SQL and Relational Databases for Analysts! I'm glad to have you as a student. In this lecture I: tell you a little about myself, describe what the course will cover, and explain how the course is structured.

Lecture Contents:

00:29 About me (Tom Gillies)
01:09 Why you should learn about SQL and Relational Databases
02:26 Who is this course for?
03:35 What does this course cover?
05:24 How is this course organised?
06:16 Objectives
07:07 Next Steps

Preview 07:42

In this lecture I introduce Relational Databases. I explain what a "database" is and what makes a database "relational". Then I give a brief history of relational databases and explain the principles they use. Finally, I demonstrate these principles using a simulated database in Excel.

Lecture Contents:

00:31 What's in this lecture?
01:30 What is a database?
04:15 What makes a database "relational"?
05:09 Background to Relational Databases
06:40 Principles of Relational Databases
10:05 Demonstration of simulated database using Excel
13:02 Summary
15:03 Next Steps
Preview 15:52

In this lecture I show you how to: install MS SQL Server (Downloading the installation file, the actual installation, confirming the installation), start SQL Server Management Studio, set up the practice database and un-installing MS SQL Server.

Downloading and installing MS SQL Server
20:31

Test yourself: Relational Databases
7 questions

The objectives of this exercise are to: get you used to the idea of data presented as a table, to introduce the ideas of: Rows, Columns and values and to get you to choose: which columns and rows you can see.

Exercise: Experiment with the spreadsheet
01:10

You may wonder about my background and experience. This "lecture" contains more background information about me, and tells you a number of ways you can get in touch with me.

Preview 1 page
+
Structured Query Language (SQL), DML and SELECT
5 Lectures 29:36

In this lecture I introduce SQL (Structured Query Language). I explain that it is divided into three parts and what each part is used for. Then I introduce the first of these parts: DML (Data Manipulation Language), which is used to read and update the contents of the database.

Lecture Contents:

00:33 What's in this lecture?
00:49 What is SQL?
02:34 How is SQL Organised?
03:40 DML (Data Manipulation Language)
05:20 Summary
06:25 Next Steps
Preview 06:48

In this lecture we get down to business! I introduce the SELECT statement and show you how to use SELECT to choose which columns and which rows to retrieve from a table. Most of the lecture is taken up with demonstrations which will prepare you for the exercises where you can do-it-yourself.

00:32 What’s in this lecture?
01:11 DML (Data Manipulation Language)
01:27 The SELECT statement
01:43 Demonstration: SELECT * …
04:46 Demonstration: SELECT list of column names…
07:15 Demonstration: The WHERE clause
09:16 Demonstration: Combining conditions
10:44 Combining conditions
12:51 The SELECT statement
13:52 Summary
15:07 Next Steps
Reading from a table using SELECT
16:20

This lecture is not a formal part of the course. Instead, I answer some questions which I expect to arise around about this point in the course, like: What happens if I’m looking at the wrong database? What does “dbo.” mean? And, if you’ve seen SQL written by other people, do I need to put a “;” at the end of the statement? I also show you how Management Studio will write SQL statements for you.

Some Hints
04:38

The objectives of this exercise are to introduce the basic SELECT statement, along with *, the column list and the WHERE clause.

Exercise: Choose the information YOU want
00:58

Test yourself: The SELECT statement
6 questions

The objectives of this exercise are to get you to think about: possible columns, and “groups of friends” and provide some material you can use in the future.

Exercise: What else might you want to know?
00:51
+
Introducing Derived Columns and Functions
4 Lectures 19:02

In this lecture I debrief the exercises from the preceding section and introduce the ideas of derived values or columns and functions. Derived values allow us to calculate new values based on the contents of a row, and functions calculate values based on groups or “sets” of results.

00:32 What’s in this lecture?
01:18 Exercise debrief
03:17 Derived values
03:42 Functions
04:02 Next Steps
The database doesn't have to contain everything!
04:19

In this lecture I demonstrate using derived columns. I create derived columns from both numeric and text columns. From the numeric columns I calculate values from based on values stored in the table and constants. From the text columns I assemble a concatenated value.

00:32 What’s in this lecture?
00:46 Demonstration of derived columns – Numbers
02:23 Demonstration of derived columns – Text
04:27 Summary
05:15 Next Steps
Derived columns
05:30

In this lecture I introduce the functions COUNT, AVG, MAX, MIN and SUM. These names may be familiar if you already use Excel or another spreadsheet program. COUNT(*) gives us the number of rows in the result query. AVG, MAX, MIN and SUM all summarise the values held in one column of the result set.

00:40 What’s in this lecture?
01:06 About these functions – relates these functions to spreadsheets
01:29 COUNT(*) – The number of rows
01:59 AVG(…) – The average (mean) of the values in a column
02:52 MAX(…) – The maximum value in a column
03:40 MIN(…) – The minimum value in a column
04:19 SUM(…) – The sum of the values in a column
05:05 Demonstration of these functions
07:22 Next Steps
Functions (COUNT, MAX, MIN, AVG, SUM)
08:04

The objectives of this exercise are to give you opportunities to: ·Experiment with “derived columns”, and try the different functions: COUNT, MAX, MIN, AVG and SUM with numbers, and with character data

Exercise: Derived Columns and Functions
01:09

Test yourself: Derived Columns and Functions
8 questions
+
Changing data in a table
7 Lectures 53:04

In this lecture I introduce the DML data-changing statements: INSERT, UPDATE and DELETE. I outline what they do, explain why an analyst should know about how they work even if you do not expect to be allowed to change data and I discuss the importance of “safety” where updating data is concerned.

00:35 What’s in this lecture?
01:21 Context: DML: Data Manipulation Language
01:39 Context: DML: the INSERT, UPDATE and DELETE statements
02:08 INSERT
02:34 DELETE
02:57 UPDATE
03:22 Why bother?
04:28 Thinking about “safety”
06:33 Next steps
DML: Data-changing statements
07:04

In this lecture I explain INSERT: first, I remind you where it fits in DML and SQL; then , I show you an INSERT statement and explain what the various parts do. After demonstrating INSERT, I explain the rules that INSERT uses and answer a question I have been asked about INSERT.

00:35 What’s in this lecture?
01:07 Context: DML: the INSERT statement
01:36 What does INSERT do?
02:12 The INSERT statement
04:29 Demonstration of INSERT
07:07 Rules for INSERT
08:04 INSERT – People ask…
09:13 Next steps
Adding new data: the INSERT statement
09:57

In this lecture I explain DELETE: first, I remind you where it fits in DML and SQL; then , I show you an DELETE statement and explain what the various parts do. I demonstrate DELETE in two different ways to emphasise that it must be used with care, I explain the rules that DELETE uses and answer a question I have been asked about DELETE.

00:35 What’s in this lecture?
01:19 Context: DML: the DELETE statement
01:41 What does DELETE do?
02:07 The DELETE statement
03:13 What happens next? (the effect of the WHERE clause)
04:00 Some advice…
04:21 Demonstration of DELETE
07:20 Demonstration of DELETE (with no WHERE clause)
09:42 Rules for DELETE
10:31 DELETE – People ask…
11:17 Next steps
Removing data: the DELETE statement
12:04

In this lecture I explain UPDATE: first, I remind you where it fits in DML and SQL; then , I show you an UPDATE statement and explain what the various parts do. I demonstrate UPDATE in two different ways to emphasise that it must be used with care and, I explain the rules that UPDATE uses.

00:35 What’s in this lecture?
01:19 Context: DML: the UPDATE statement
01:38 What does UPDATE do?
02:10 The UPDATE statement
03:17 What happens next? (the effect of the WHERE clause)
04:06 Some advice…
04:28 Demonstration of UPDATE
06:16 Demonstration of UPDATE (with no WHERE clause)
07:59 Rules for UPDATE
08:43 Next steps
Changing existing data: the UPDATE statement
09:16

In this lecture I demonstrate that SQL Server Management Studio provides some very useful facilities which allow you to perform the equivalent of INSERT, UPDATE and DELETE.

00:35 What’s in this lecture?
00:53 Context: DML
01:26 Demonstration of Management Studio
02:15 “Script Table to…” SELECT
02:49 SELECT top 1000
03:29 Edit top 200
05:13 Next steps
INSERT, UPDATE and DELETE using Management Studio
05:42

In this lecture I summarize what you have learned about INSERT, UPDATE and DELETE and SQL Server Management Studio.

00:33 What’s in this lecture?
01:03 Context: DML
01:22 Context: DML – INSERT, UPDATE and DELETE
01:36 The INSERT statement
02:49 The DELETE statement
03:43 The UPDATE statement
05:00 Making changes using Management Studio
05:36 Sensible rules
06:35 DML: Data Manipulation Language - Progress
06:58 Next steps
Summary: INSERT, UPDATE and DELETE
07:49

The objectives of this exercise are to for you to: change the data in the PhoneBook table by: adding, deleting and updating rows using both Management Studio and SQL

Exercise: Experiment with changing data
01:12

Test yourself: INSERT, UPDATE and DELETE
7 questions
+
More with SELECT
4 Lectures 31:53

In this lecture I explain the ORDER BY clause of the SELECT statement. ORDER BY allows you to control the order of the rows in the results.

01:49 What does ORDER BY do?
02:18 SELECT: The ORDER BY clause
03:36 Identifying columns to ORDER BY
05:57 What can we ORDER BY?
06:27 Demonstration of ORDER BY
00:00 Style: a matter of opinion - Coding standards
00:00 Summary
The ORDER BY clause
12:06

In this lecture I explain the LIKE comparison, which is a flexible and convenient way to search fields containing text.

Working with text: the LIKE comparison
08:13

In this lecture I explain the GROUP BY clause of the SELECT statement together with aggregate functions.

Functions and GROUP BY
10:24

The objectives of this exercise are to reinforce what you have learned about:ORDER BY – Controlling the sequence of records in the results, LIKE – Comparing the contents of a column with a pattern, and GROUP BY – Getting multiple rows of results containing aggregate functions. ·You will do these activities using SQL.

Exercise: LIKE, ORDER BY and GROUP BY
01:10

ORDER BY, LIKE and GROUP BY
9 questions
+
The Database Manager and the Database Administrator
2 Lectures 20:53

In this lecture I introduce the concepts of: the Database Manager (DBM) and the Database Administrator (DBA), and I expand on what you know already about the concept of a “Database”.

The Database Manager and the Database Administrator
19:26

The objectives of this exercise are to give you an opportunity to look at the settings of the Database Manager (DBM) instance and the Database. In a sense you are pretending to be a Database Administrator. If you can get a real DBA to show you some of this stuff, so much the better.

Exercise: Exploring Instance and Database settings
01:27

Test yourself: The Database Manager and the DBA
4 questions
+
Defining the database: Data Definition Language (DDL)
5 Lectures 37:07

In this section I will introduce DDL: Data Definition Language, which is the part of SQL which is used to define the structure of the database.

Introducing DDL
06:35

In this lecture I explain how to create and change the definition of a table using Management Studio facilities, and SQL statements.

Changing and changing a table
15:58

In this lecture I explain how to create and change the definition of a database using Management Studio facilities, and SQL statements.

Create and changing a database
12:14

The objectives of this exercise for you to: Create and Alter a table, Populate that table and Create a database. You will be the DBA of your own database!

Exercise: Creating and changing the PhoneBook
01:00

Test yourself: DDL
7 questions

The objective of this exercise is for you to think about whether we should allow a column to have no value, or be empty and when this might happen. If you are working as part of a group, this exercise is more fun if you do it as a group exercise.

Exercise: Do you always have a value?
01:20
+
NULL: a load of fuss over nothing!
4 Lectures 30:03

In this lecture I discuss the concept of NULL. I give you a dictionary definition, and an explanation in the context of Relational Databases. I show you how NULL is used in column definitions and explain what NULL doesn’t mean: for numbers, and for character columns, and finally I outline the benefits and problems associated with NULL.

What does NULL really mean?
07:55

In this lecture I show you what practical effects NULL has in relational databases. I demonstrate: the effect of NULL in numeric columns, and how ORDER BY treats NULLs; NULL and character columns; the effect of NULL on arithmetic and derived columns; NULL and Functions; what happens with NULL in when performing INSERT and UPDATE; using NULL in the WHERE clause; and finally the use of NOT NULL and default values.

What does NULL mean in practice?
18:04

NULL produces all sorts of surprises. This exercise gives you an opportunity to write your own SQL queries based on SQL files in the supplementary material. You can re-run the demonstrations, and then manipulate the data in NewPhoneBook to investigate the behaviour of NULLs.

Exercise: NULL
02:10

Test yourself: NULL
9 questions

This exercise encourages you to think about the consequences of having non-trivial volumes of data, think about the idea of having an “identifier” or “key” and consider what the properties of an identifier should be.

Exercise: Who is that? Which one is that? (identity)
01:53
+
A question of identity?
4 Lectures 26:31

In this section: I Introduce the idea of “Identifiers” or “Keys”. In order to make it clear why we need them, I Debrief the “Who is that?” exercise, and I demonstrate that duplicate records will cause serious problems in a database. In the following lecture I show you how to avoid those problems.

What's the problem? Double trouble!
09:50

In the previous lecture I demonstrated that there were serious problems if our table allowed duplicate rows. In this lecture I introduce the idea of having a “Key” or “Identifier” that can be used to identify each row, I demonstrate creating a table with a “Primary Key”, I demonstrate the effect of having a primary key and I demonstrate making the primary key an “Identity” column so that SQL Server creates the keys for you. I also show you where to do some of these things using Management Studio.

The solution: "Identifiers" or "Keys"
13:41

The objectives of this exercise are for you to experience: the problems caused by duplicate records, creating and using tables with a Primary Key, and creating and using tables with an “Identity” column (one which automatically creates keys).

Exercise: Identity and keys
01:22

Test yourself: Identity and keys
5 questions

This exercise: gets you to think about searching a large volume of data, relates this to the concept of an index in a book and points out out some of the characteristics of an index.

Exercise: Using a real phone book
01:37
+
Indexes
4 Lectures 29:11

In this lecture I: Debrief the “Paper Phonebook” exercise, discuss indexes in the outside or “real” world and introduce the concept of indexes in relational databases.

What is an index and what does it do?
13:19

In this lecture I: Show you the statements used to create and remove indexes, which are: CREATE and DROP, I demonstrate creating and dropping an index, and then I examine the CREATE INDEX statement in more detail and explain the effect of some of the options.

Creating and using indexes
13:06

Exercise: Creating and Dropping INDEXes for yourself
01:19

Test yourself: INDEXes
7 questions

The objectives of this exercise are for you to think about: the implications of adding additional columns to our table and the consequences of someone having more than one address or telephone number.

If you still have whatever you produced for the earlier exercise: “What else might you want to know?”, then now would be a good time to find it. If several of you are working together, then this exercise is more fun performed as a group.

Exercise: More (and more) information
01:27
4 More Sections
About the Instructor
Tom Gillies
4.7 Average rating
38 Reviews
1,915 Students
4 Courses
Business Analyst and Educator

I'm Tom Gillies and I have been a Business and Technical Analyst in the Information Technology industry for the past thirty years.

My courses are based on my real-world experiences. I am teaching as I wish I had been taught. My objective is to give you enough knowledge to make you reasonably self-sufficient, and enough experience to give you reasonable confidence, while understanding your limitations. I think you will find working at your own pace liberating and you can contact me during the course if you wish to.

I started my working life as an engineer. I have a BSc in Chemical Engineering from Aston University in Birmingham, England. As a result of my work as an engineering designer, I became interested in computing and eventually I joined IBM as a Systems Engineer, working in pre-sales for customers in the aerospace industry.

Within IBM, I moved to a consultancy group and worked directly for customers as a Business or Technical Analyst for twenty-five years. I served a wide variety of customers from large “blue chip" corporations and government departments to start-ups. I have designed, developed and maintained computer systems, large and small, on a wide variety of platforms.

In my experience of the Information Technology industry, I have found that some skills have been of lasting value. SQL is one such technical skill. Problem solving, some analysis techniques and the so-called "soft skills" are others. All of these improve your ability to communicate with both the business and technical staff make you a more valuable member of a team.

I live in the Republic of Ireland and, when I'm not working for Customers, or writing and supporting courses, I am improving my skill in the Russian language.