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.5 (23 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,246 students enrolled
Instructed by Tom Gillies Development / Databases
$19
$50
62% off
Take This Course
  • Lectures 57
  • Length 7.5 hours
  • Skill Level Beginner Level
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 4/2014 English

Course 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.

What are the 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.

What am I going to get from this course?

  • 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.

What is the target audience?

  • Business Analysts
  • Technical Analysts
  • IT Specialists

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Introductions: The course, databases and the tools
07:42

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

15:52

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
20:31

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.

Test yourself: Relational Databases
7 questions
Article

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.

1 page

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.

Section 2: Structured Query Language (SQL), DML and SELECT
06:48

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
16:20

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
04:38

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.

Article

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

Test yourself: The SELECT statement
6 questions
Article

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.

Section 3: Introducing Derived Columns and Functions
04:19

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
05:30

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
08:04

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
Article

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

Test yourself: Derived Columns and Functions
8 questions
Section 4: Changing data in a table
07: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
09:57

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
12:04

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
09:16

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
05:42

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
07:49

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
Article

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

Test yourself: INSERT, UPDATE and DELETE
7 questions
Section 5: More with SELECT
12:06

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
08:13

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

10:24

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

Article

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.

ORDER BY, LIKE and GROUP BY
9 questions
Section 6: The Database Manager and the Database Administrator
19:26

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”.

Article

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.

Test yourself: The Database Manager and the DBA
4 questions
Section 7: Defining the database: Data Definition Language (DDL)
06:35

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.

15:58

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

12:14

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

Article

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!

Test yourself: DDL
7 questions
Article

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.

Section 8: NULL: a load of fuss over nothing!
07:55

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.

18:04

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.

Article

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.

Test yourself: NULL
9 questions
Article

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.

Section 9: A question of identity?
09:50

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.

13:41

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.

Article

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).

Test yourself: Identity and keys
5 questions
Article

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.

Section 10: Indexes
13:19

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.

13:06

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.

Exercise: Creating and Dropping INDEXes for yourself
Article
Test yourself: INDEXes
7 questions
Article

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.

Section 11: Normalization, Data Modelling and JOIN
07:50

In this lecture I: debrief the “More and more information” exercise, introduce Data Modelling, look in detail at the structure of PhoneBook, point out some problems with PhoneBook, and propose a solution to those problems.

15:54

In this lecture I: introduce the concept of “Normalization” and how it relates to Data Modelling, show you how to design the 3 tables (Person, PhoneNumber and Address), introduce the idea of a “Foreign Key” and the idea of a “Data Model” and demonstrate how to build and populate a database containing the new tables.

Article

The objectives of this exercise are for you to: Create the 3 table database using the example SQL and explore the new tables and confirm to yourself that you can run SELECT queries on them.

15:30

Understanding how JOIN works is absolutely critical to using SQL with real data. In this lecture I explain what we mean by “join”, I show you how to: use SELECT taking columns from 2 or more tables (I show you two methods: “traditional method”, and the more recent “explicit join”), and I show you the “Cartesian Join” which is a problem which you are bound to encounter, a sooner or later.

Article

The objectives of this exercise are for you to: create SELECT queries which take data from more than one table and think about data modelling and Normalization.

Test yourself: Normalization, Data Modelling and JOIN
3 questions
Section 12: Temporary tables and VIEWs
06:42

This lecture is about creating tables using the SELECT statement. Such tables are usually to hold “temporary” data, but they are just as permanent as other tables in the database. In this lecture I explain why you might want to create a table this way, and I show you how to create a table using SELECT.

17:45

This lecture is about using Views in relational databases. Views are one of my favourite features in SQL and relational databases. Creating them is so simple, and yet the way they can be used can be so elegant! In this lecture I explain what we mean by “a VIEW”, I show you how to create a View, I compare Views with the temporary tables we created in a previous lecture and I explain the rules for updating the data in views.

Article

The objectives of this exercise are for you to: try creating tables using SELECT, Create Views and think about the characteristics of temporary tables and views.

Test yourself: Temporary tables and VIEWs
3 questions
Section 13: Securing the database: Data Control Language (DCL)
24:11

In this lecture I explain why I think even an analyst needs to know a little about security in general and DCL in particular.I introduce the idea of “Privileges”, which are what someone is allowed to do, I show you the GRANT statement which gives a privilege to someone, and the REVOKE statement which takes a privilege away. In order to define the “someone” we are giving privileges, we need to be able to define USERS. This brings in some MS SQL Server specific material.

12:38

In this lecture, I show you how Roles can be used to simplify administration of security. I remind you what DCL is and show you where Role fits in. I explain what a Role is, show you how to create a Role, and show you how to grant and revoke privileges to a role. Next, I explain how Users and Roles are related and show you how to change which Users belong to a particular role. Finally, I demonstrate how roles can be used to control access to tables and views.

Article

The objective of this exercise is to give you an opportunity to work with DCL and user privileges.

Test yourself: Security and DCL
6 questions
Section 14: Course review
06:11

In this lecture, I remind you of the key points of what we covered in the course. For Relational Databases, we covered, definitions, history and principles. SQL is the language used to interact with an relational database and we covered the three components of SQL: DML, DDL and DCL; Data Manipulation, Definition and Control languages respectively.

Article

There is an enormous amount that can be learned about Relational Databases and SQL. My advice is: “do not try and know everything at once”. Instead identify your priorities and concentrate on those areas. This final "lecture" suggests some topics you may want to study in the future.

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Tom Gillies, 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.

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.

My courses are based on my real-world experiences. I am teaching as I wish I had been taught it. 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 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.

Ready to start learning?
Take This Course