SQL and Database for Analysts - Increase your team value

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

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.

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

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

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

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.

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

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

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!

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.