SQL Server Developer : SELECT query foundations

Name: SQL Server Developer : SELECT query foundations
Rating: 4.6 (12 reviews)

Learn how to use SELECT, FROM, WHERE, GROUP BY, HAVING and ORDER in SQL Server to retrieve and analyse data

Created byBen Osborn

Last updated 9/2021

English

What you'll learn

Querying SQL server databases using the SELECT clause
Joining multiple tables in different ways to combine information
Filtering data to retrieve only the information you need
Using aggregation functions and processes to find totals and averages for reports

Course content

5 sections • 41 lectures • 3h 20m total length

Course Introdution2:42

Download and Install SQL Server Express4:06
This lecture takes you through how to download and install the SQL Server Express Edition software from Microsoft.
The SQL Express software is a compact database engine that we can use to store data and run SQL queries.

You can download the software at:
https://www.microsoft.com/en-gb/sql-server/sql-server-downloads

after completing the lecture you will have the database engine software on your computer ready to run through the rest of the course, and will be able to use it for your own projects.
Download and Install SQL Server Management Studio (SSMS)4:03
SQL server management studio (SSMS) is a fully featured application that lets you administer and manage the databases server, as well as a tool to write and test queries.
This lecture will take you through how to download the software and then how to install it on your computer.

Download the SQL Server Management Studio software at:
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

once installed you will be able to write SQL queries against any Microsoft SQL server database.
AdventureWorks Database - Introduction2:13
to run the queries in the course we're going to need some data to work with. the AdventureWorks database backup file contains lots of data we can use to experiment with.
you have the choice to either download and restore the full AdventureWorks sample database from the Microsoft site, or to run a script file and create the database and a smaller amount of data from a file in the course.

Download the AdventureWorks backup file at:
https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure
Option 1 - Install AdventureWorks from a backup7:56
in this video we will restore the AdventureWorks backup file onto your version of SQL server.

after completing the process we'll have a full install of the sample database ready to use for the rest of the course, and for your own tests.
Option 2 - Install AdventureWorks from a script file2:10
if you prefer to install using the provided script then this lecture will take you through the quick process of using a script file to create the database and add tables and data.

SSMS Layout and Connecting to a Database7:29
this lecture outlines the process of connecting to a database and introduces you to the structure of SQL server.
Tree view, Menus and Editing code5:51
continuing on from the previous lecture, we go through the rest of the management studio.

we go through the menus and shortcuts, navigating through the serve objects using the object explorer treeview pane, and walk through the particulars of the detail pane.

SELECT clause - Introduction and Demonstration6:52
The SELECT clause defines the columns we get back in our results from the server.

this lecture shows how to use the SELECT clause with some text and numeric values to return a table of results.

The select clause defines the fields you want to return in your table, this can be static fields such as a string of characters or a fixed number, a field from one or more tables, or functions that combine the two or generate new fields based on existing data or calculations.
FROM clause - Introduction1:40
The FROM clause shows SQL server what table we want to use to bring back our results.

The FROM clause is where you specify what tables from your database you would like SQL to get rows from. You can also use the Inner, left or outer joins to join tables together based on matching up the fields inside each table.
FROM clause - Demonstration7:49
FROM Clause - Test Answers6:41
A note about Data Types7:12
WHERE Clause - Part 1 - Introduction2:20
You can use different operators against columns in your tables to filter out the rows you don’t want, this will reduce the number of rows that are sent over the network from your server, and the amount of data that needs to be processed by the application.

Use equals to filter a field to exactly one value, not-equals to filter everything that isn't a particular value, BETWEEN to filter everything between two values, IN to filter to a list of values, and LIKE to filter values that match a specific pattern.
WHERE Clause - Part 1 - Demonstration6:36
A Note about Dates7:03
this lecture details the nuances of the dates datatype, how to use it in WHERE clauses, and how the database system stores the values
WHERE Clause - Part 2 - LIKE operator - Introduction4:58
WHERE Clause - Part 2 - LIKE operator - Demonstration2:48
A note about NULLs4:43
this lecture clarifies the features of the NULL values in database tables, how to use them in queries and how to use the special functions.
WHERE Clause - Part 3 - Combining clauses - Demonstration5:58
WHERE Clause - Test Answers6:04
ORDER BY Clause - Introduction3:07
The order by clause lets you sort the results of your queries, you can use it to order one or more columns, or even order by functions and formula results.

The order by clause should be the last clause in your select statement, remember that SQL server orders the results of the whole query rather than trying to do any ordering work before the complete set of results have been decided.
ORDER BY Clause - Demonstration3:18
ORDER BY Clause - Test Answers4:13
JOIN Clause - Part 1 - Introduction6:48
JOIN Clause - Part 1 - Demonstration6:36
JOIN Clause - Part 1 - Test Answers4:49
JOIN Clause - Part 2 - Joining multiple tables - Introduction5:24
JOIN Clause - Part 2 - Joining multiple tables - Demonstration2:43
JOIN Clause - Part 2 - Joining multiple tables - Test Answers5:42
Aggregates and Grouping - Introduction1:51
Aggregate Functions - Demonstration3:11
GROUP BY Clause - Introduction1:11
the GROUP BY Clause tells SQL server that you would like it to combine rows where the column values are the same as each other. With this clause in place, you can also use aggregate functions such as SUM or Count to perform calculations against your grouped rows.

The group by clause sits before our order by in our Select statement, and has contents similar to the SELECT clause, meaning you can group by column names, or functions
GROUP BY Clause - Demonstration3:18
GROUP BY Clause - Test Answers7:23
HAVING Clause - Introduction1:35
Use the having clause along with an aggregate function to limit to only the rows where the result of the aggregate is a certain value.

You can use any operator normally used in the WHERE clause in the having clause to evaluate the result.
HAVING Clause - Demonstration2:42
HAVING Clause - Test Answers3:42

Requirements

Some knowledge of data reporting advantageous, but not necessary.

Description

SQL Server SELECT query foundations aims to teach you the basics of retrieving data from the Microsoft SQL server database engine.

the course contains details on how to use the SELECT clause in the SQL server environment to query database tables and bring back data to present in reports or applications

This course is aimed at anyone looking to improve their understanding of database development, from data analysts looking to increase their skills on data querying, programmers looking to add database capabilities to their apps, or anyone who needs to get at the wealth of data contained in their companies' database servers.

the course includes lessons on :

Retrieving and combining columns from SQL server tables using the SELECT clause to define columns and functions to display to the end users.

Joining multiple tables in different ways to combine information. Using the FROM clause to specify which tables you want to connect to, and multiple JOIN clauses to connect tables to each other.
Filtering data to retrieve only the information you want. Using the WHERE clause to filter your data on one or multiple columns, and the ways to combine the WHERE clause with JOINs on your tables to efficiently retrieve information.
Using aggregate functions to GROUP BY information and find totals and averages of data to perform analysis and make reports

the course contains lessons on how to download and install all the tools you need to become proficient in coding SQL; I'll take you through how you can download and install SQL server express edition, a free personal use version of SQL server, and the sample 'AdventureWorks' database so you can try out all the examples, and develop and test your own queries.

We'll also download SQL Server Management Studio; your best interface into the database environment, and I'll go through the basics of how to connect to Databases, and how to use the tool more efficiently.

I've included handouts with summaries and further information on some topics, as well as increasingly difficult review questions for you to try yourself. each set of questions is followed by a video analysis of the solutions and the different ways you could choose to solve them.

There's also an end of course test that combines all the techniques you've learned into one final exam.

Who this course is for:

Data Science beginners
Someone curious to extend their data skills, perhaps used to working with Excel
Programmers looking to add database capabilities to their applications
Someone looking to enter the world of data analysis and machine learning

SQL Server Developer : SELECT query foundations

What you'll learn

Explore related topics

Course content

Introduction1 lecture • 3min

Downloading and installing the required software and data5 lectures • 20min

SQL Server Management Studio Overview2 lectures • 13min

SQL Server SELECT query foundations30 lectures • 2hr 18min

Summary and final test3 lectures • 25min

Requirements

Description

Who this course is for: