Description
In this course, you will 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.
Syllabus :
1. Introduction
- 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/ Why comments in SQL?
- Basic SQL query structure
2. SELECT and ORDER BY clauses
- How to use a basic SELECT clause?
- How to select all columns ?
- How to select some columns using column name and display only the top n values?
- 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?
3. 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?
4. 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?
5. 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
6. Create Tables, Relationships and Views
- 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?
7. 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
8. 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?
9. 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?