SQL for Developers and Analysts with MS SQL Server

Guide for beginners to intermediate using real life examples

Created byBiz Nigatu

Last updated 9/2022

English

English [Auto],

What you'll learn

Hands-on learning of SQL and databases using Microsoft SQL Server
Download a free copy of SQL Server 2017 Edition and setting up a working environment
A basic structure of a SQL query structure
Different SQL clauses in a way that will make more sense for you to follow
Create various objects in SQL Server including tables, views, functions, and stored procedures
Manage data in SQL Server and protect it
Query data, sort, restrict the output and build sub-queries
Manipulate data insert, delete and update works
Group and summarize your data
Advanced concepts in SQL and how you can be able to build reports using Excel, SSRS, and Tableau
Where to get help when you face errors and some career advice to work as a SQL developer or analyst

Coding Exercises

This course includes our updated coding exercises so you can practice your skills as you learn.

Course content

11 sections • 72 lectures • 3h 41m total length

Welcome!0:13

Section 1 - Introduction0:44
Learn database basics by installing Microsoft SQL Server developer edition, using SSMS shortcuts, creating a sample CIA database, writing SQL, and applying comments, drag-and-drop, and context switching.
Course resources0:06
Here are the CIA-Factbook Database setup scripts used in this course. We will cover how to set up the database in Section 1 - Lecture 7
What is a database?0:58
What is the difference between different Editions of SQL Server?1:33
How to Installing MS SQL Server and SSMS?3:33
What an SSMS environment looks like? Important Short cuts8:57
Explore the SQL Server Management Studio environment, create a secure user, connect via localhost, navigate databases and server objects, and use shortcuts to run and debug queries.
How to create the course sample database?5:40
Learn to create a sample database in SQL Server, configure data files, switch context with use, and load a CIA Factbook dataset to explore tables and top 1000 rows.
How/ Why comments in SQL?3:39
Basic SQL query structure2:35
Quiz 1: Databases and SQL Basics

Section 2 - Introduction0:37
How to use a basic SELECT clause?1:46
Explore the basic select clause in SQL, showing how to select numbers, strings, and date literals, alias columns with AS, and run queries in SQL Server Management Studio.
How to select all columns ?0:54
Select all columns
How to select some columns using column name and display only the top n values?1:17
Select only some columns
How to change column headings and perform mathematical operations in SELECT?2:48
How to use other built in functions in SELECT clause?1:24
Conditional evaluations in SELECT clause2:50
How to filter duplicate in SELECT clause?1:07
How to use a basic ORDER BY clause?2:36
Sort SQL result by a column/s
Section 2 - Exercise1:24
Explore SQL Server exercises: identify top ten lakes by area, ensure unique river names, compute (4+5)*6, and display all airport table columns ordered by state.
Section 2 - Exercise solution8:31
Quiz 2: SELECT and ORDER BY Clauses in SQL

How to use the basic WHERE clause?1:32
How to handle different conditions in WHERE clause?0:47
Explore using comparison operators in a where clause, including greater than and not equal to, with literals and an example filtering elevations to illustrate the concept.
How to use Boolean operators AND, OR, NOT?1:53
How to use LIKE operator to filter rows that match a certain pattern?1:18
How to use IN operator in WHERE clause?1:38
How to use EXISTS operator in WHERE clause?3:38
How to use BETWEEN operator in WHERE clause?1:32
How to handle NULL in WHERE clause?1:42
Quiz 3: WHERE Clause in SQL
Section 3 - Exercise solution14:39

How to use a basic GROUP BY clause?1:27
How to write sub queries combined with in GROUP BY clause? How to use aggregat2:39
Learn to write subqueries with group by to summarize data and use a where clause to filter with a subselect, revealing the religion with the maximum followers per country.
How to use aggregate functions in ORDER BY?0:45
How to use a basic HAVING clause?0:45
Section 4 - Exercise solution26:59
Solve SQL exercise solutions by identifying the country with the smallest GDP, calculating city population growth using CTEs and window functions, and ranking top five countries by lake area.
Quiz 4: GROUP BY and HAVING Clauses in SQL

CREATE TABLE5:38
Create and manage SQL tables with constraints, data types, and identity columns in MS SQL Server. Learn data security, primary keys, and practical table design using SSMS.
ALTER tables: add columns and change table name and DROP tables2:36
How to Create Temporary Tables?4:18
How to Create Table Relationships and How to Creating a Database Diagram?5:19
Create a many-to-many relationship between students and courses with a third table, define primary and foreign keys, apply optional cascade, and generate an entity-relationship diagram.
Constraints in SQL Server3:36
Indexes in SQL Server2:12
How to Create Views?1:48
How to Grant and Revoke Permissions to Users in SQL Server?9:34
Master granting, revoking, and denying permissions in SQL Server across server and database levels. Create logins and users, assign roles, and use views to protect sensitive data while controlling access.
Create Tables and Relationships

How to use INNER JOIN or simply JOIN?1:29
Explore inner joins and other join types like right and cross join, and learn to implement them in queries and where clauses. Cover union, intersect, and except operations.
How to use LEFT OUTER JOIN and RIGHT OUTER JOIN?2:14
What is FULL OUTER JOIN and CROSS JOIN?2:02
Learn how full outer join combines left and right joins and how cross join produces a Cartesian product, with practical notes on when to use or avoid them for performance.
How to use Subqueries in FROM clause and join tables using a WHERE clause?4:12
Learn to use subqueries in the from clause and join tables with a where clause, including left and inner joins, to combine country and mountain data in MS SQL Server.
How to use CROSS APPLY in FROM clause?1:58
How to Use Set Operations UNION, INTERSECT and EXCEPT?2:28
Type Casting4:08
Joins in SQL

What is the purpose of using WITH(NOLOCK) in SQL Server?3:17
What is a difference B/N UNION and UNION ALL in SQL Server?1:21
How to use Variables and Table Variables in SQL Server?2:38
How to use CURSORS in SQL Server?3:19
How to use Transaction and safe DELETE in SQL Server?2:44
How to create Functions in SQL Server?2:27
Create user defined functions in SQL Server, including scalar and table-valued types, defining parameters and returns, and applying a function to convert Celsius to Fahrenheit in queries.
How to create Stored Procedures in SQL Server?3:18
Learn how to create stored procedures in SQL Server, including defining input and output parameters, returning data, and handling errors with try-catch, while using temporary tables to encapsulate operations.
Working with some of SQL Server built-in Functions2:42
How to Backup/Restore Databases in SQL Server?2:05
How to create Reports in Excel from data in SQL Server?1:33
Use Excel as a reporting tool to fetch data from SQL Server, run a SQL script, load sample CIA factbook data, and refresh the report to reflect changes.
How to create SSRS Reports from data in SQL Server?2:43
How to create Tableau Reports from data in SQL Server?5:06

Requirements

A free Developer Edition of SQL Server 2017. (How to download and setup will be discussed in course)
A free copy of SQL Server Management Studio 2017. (How to download and setup will be discussed in course)
A computer running Windows Desktop or Windows Server:
- Operating System Requirements: Windows 8,8.1,10 or Windows Server 2012, 2016
- Memory: Minimum: 1 GB, Recommended: At least 4 GB
- Hard Disk: requires a minimum of 6 GB of available hard-disk space.

Description

This course will give you hands-on learning of SQL using Microsoft SQL Server. The course will teach you how you could download a free copy of SQL Server 2017 Edition from Microsoft website and set up your working environment. It will teach you various objects in SQL Server including tables, views, stored procedures. After providing you a basic structure of a SQL query structure, it will show different SQL clauses in a way that will make more sense for students to follow. It will give you a basic understanding of how you can manage your data and protect it. The course will show you how you can be able to query data, sort, restrict the output and build sub-queries. You will also understand how you can manipulate data insert, delete and update works. The course will teach how you can group and summarize your data. The course will briefly touch some advanced concepts and how you can be able to build reports using Excel, SQL Server Reporting Service, and Tableau. Finally, the course will show you can get help when while working as a SQL developer and some career advice to work as a SQL developer or analyst.

Who this course is for:

Any student who want to learn the basics of SQL and databases using SQL Server
Anyone preparing for a SQL developer or reporting analysist job interviews
Anyone who is has a desire to learn new skills in SQL
This course doesn't assume any prior knowledge of SQL or databases, and it will be perfect for beginners and intermediate SQL developers and analysts

SQL for Developers and Analysts with MS SQL Server

What you'll learn

Explore related topics

Coding Exercises

Course content

Welcome!1 lecture • 1min

Introduction9 lectures • 28min

SELECT and ORDER BY clauses11 lectures • 25min

WHERE Clause9 lectures • 29min

GROUP BY and HAVING Clauses5 lectures • 33min

FROM Clause4 lectures • 4min

Create Tables, Relationships and Views8 lectures • 35min

Joins7 lectures • 19min

INSERT, UPDATE, and DELETE Data5 lectures • 12min

Advanced Topics12 lectures • 33min

Requirements

Description

Who this course is for: