Duration
SQL for Developers and Analysts with MS SQL Server
5 days
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
Introduction
- Course resources
- What is a database?
- What is the difference between different Editions of SQL Server?
- How to Installing MS SQL Server and SSMS?
- What an SSMS environment looks like? Important Short cuts
- How to create the course sample database
- How to create the course sample database?
- How/ Why comments in SQL?
- Basic SQL query structure
- Quiz 1: Databases and SQL Basics
SELECT and ORDER BY clauses
- How to use a basic SELECT clause?
- How to select all columns ?
- Select all columns
- How to select some columns using column name and display only the top n values?
- Select only some columns
- How to change column headings and perform mathematical operations in SELECT?
- How to use other built in functions in SELECT clause?
- Conditional evaluations in SELECT clause
- How to filter duplicate in SELECT clause?
- How to use a basic ORDER BY clause?
- Sort SQL result by a column/s
Section 2 - Exercise solution
- Quiz 2: SELECT and ORDER BY Clauses in SQL
WHERE Clause
- How to use the basic WHERE clause?
- How to handle different conditions in WHERE clause?
- How to use Boolean operators AND, OR, NOT?
- How to use LIKE operator to filter rows that match a certain pattern?
- How to use IN operator in WHERE clause?
- How to use EXISTS operator in WHERE clause?
- How to use BETWEEN operator in WHERE clause?
- How to handle NULL in WHERE clause?
- Quiz 3: WHERE Clause in SQL
Section 3 - Exercise solution
GROUP BY and HAVING Clauses
- How to use a basic GROUP BY clause?
- How to write sub queries combined with in GROUP BY clause? How to use aggregat
- How to use aggregate functions in ORDER BY?
- How to use a basic HAVING clause?
Section 4 - Exercise solution
- Quiz 4: GROUP BY and HAVING Clauses in SQL
FROM Clause
- How to use a basic FROM clause?
- How to use "AS" or table alias in FROM clause
- Using simple JOIN
- Execution order in SQL Server
- Quiz 5: FROM Clause in SQL
Create Tables, Relationships and View
- CREATE TABLE
- ALTER tables: add columns and change table name and DROP tables
- How to Create Temporary Tables?
- How to Create Table Relationships and How to Creating a Database Diagram?
- Constraints in SQL Server
- Indexes in SQL Server
- How to Create Views?
- How to Grant and Revoke Permissions to Users in SQL Server?
- Create Tables and Relationships
Joins
- How to use INNER JOIN or simply JOIN?
- How to use LEFT OUTER JOIN and RIGHT OUTER JOIN?
- What is FULL OUTER JOIN and CROSS JOIN?
- How to use Subqueries in FROM clause and join tables using a WHERE clause?
- How to use CROSS APPLY in FROM clause?
- How to Use Set Operations UNION, INTERSECT and EXCEPT?
- Type Casting
- Joins in SQL
INSERT, UPDATE, and DELETE Data
- Using a basic INSERT INTO, INSERT SELECT, and SELECT INTO to Create a New Table
- Import data from external file
- Using a basic UPDATE clause and UPDATE with WHERE clause
- Using a basic DELETE clause and DELETE with WHERE clause
- Using OUTPUT operator to log inserted/deleted data to a different table?
- Quiz 8: INSERT, UPDATE & DELETE data in SQL
Advanced Topics
- What is the purpose of using WITH(NOLOCK) in SQL Server?
- What is a difference B/N UNION and UNION ALL in SQL Server?
- How to use Variables and Table Variables in SQL Server?
- How to use CURSORS in SQL Server?
- How to use Transaction and safe DELETE in SQL Server?
- How to create Functions in SQL Server?
- How to create Stored Procedures in SQL Server?
- Working with some of SQL Server built-in Functions
- How to Backup/Restore Databases in SQL Server?
- How to create Reports in Excel from data in SQL Server?
- How to create SSRS Reports from data in SQL Server?
- How to create Tableau Reports from data in SQL Server?
Programme scope, delivery arrangements and suitability are confirmed during enquiry.