The Complete SQL Server For Beginners

Introduction to section two.

In this lecture you are going to create your first SQL Server database and see where the physical files are stored and how to work with them directly.

Now you will learn how to create users in SQL server and give them certain permissions.

Learn what is a database schema and why people use schema and how to create them.

Exercise on creating users .

Let's download and install AdventureWorks database.

Summary of the section to remind you of the points we discussed in brief.

Introduction to section three.

In this lecture you are going to work with the most important database object which is the tables, think of tables as the heart of relational databases.

Explore more SQL Server datatypes and see some of the differences between the similar ones.

Let's dive deeper in creating tables with applying constraints to the columns.

We are going to see how to delete tables and delete data in the tables.

In this exercise you are going to build some of the tables for the human resources database.

We can use a feature called column properties in management studio to add more properties and features to the columns visually.

Now it's time to discuses one of the most important concepts in relational database which is primary keys, you are going to learn how to create them in different ways.

In this lecture we are going to see how to create foreign keys and make relationships between tables.

In this exercise you will be modifying the country column in the employees table.

In this exercise you will be asked to create a new called contains the previous experience of employees in case some employees had experience in the past.

This is an interesting lecture we are going to fill our tables with data using Import wizard feature in management studio.

We can use the Import wizard to create tables from scratch and fill with data without typing any SQL script.

In this exercise you will be asked to create both primary and foreign keys for statues table.

We have an interesting lecture, we are going to see how to generate ERD and visually view database objects and track relationships.

Summary of the section to remind you of the points we discussed in brief.

Introduction to section four.

Select statement is the most common used statement in SQL, it's used to retrieve data from database, let's figure out how to use it efficiently.

Small exercise to practice what you have learned about the select statement.

Where clause is usually used with select statement to filter data based on conditions as we are going to see.

In this exercise you are going to select some data from AdventureWorks database based on specific conditions.

Order by statement is used to order the records based on one or multiple columns.

In this lecture we are going to create a table from an already existing table.

Exercise on how to create table from another table.

Let's see how to work with operators like and, or , not and is null and how can you combine them in one where clause.

Let's practice the conditions operators that we have just learned in the previous lecture.

Like is very powerful keyword in SQL it enable you to search for text patterns in your data as we are going to see.

In this exercise you will be asked to create a pattern to display set of records in database.

There is more advanced wildcards that we can use to define our patterns, let's go a check them.

In is a small word but it a save effort saver when typing queries as we are going to see in this lecture.

Between also is a very powerful keyword it is used to perform a range condition between two values, let's see how to use it.

Time for an exercise about in and between and keywords.

Summary of the section to remind you of the points we discussed in brief.

Introduction to section five.

Distinct is another powerful keyword used to select only the distinct values only in a table, let's see how to use it.

Time with another keyword in can help you when selecting data from a huge table and you would like to get only a percentage of the data.

We can change how many rows Management Studio returns by default, in this lecture we are going to see how.

Union is used to combine two different queries into one, sounds interesting...let's see how to use it.

Exercise to test your knowledge of the union operators.

In this lecture we are going to learn about two keywords in SQL they are the Intersect and Except.

Another powerful statement we are going to learn in this lecture which is ....case statement.

Exercise testing your case statement skills.

Another exercise to fill table with data.

Subquery, one of the very powerful features of SQL, learn how to make dynamic conditions using subquery.

Time for an exercise about the subquery

Summary of the section to remind you of the points we discussed in brief.

Introduction to section six.

In this lecture we are going to see the powerful aggregate functions, starting withe minimum and maximum functions, let's see how to use them.

Continue our lectures about the aggregate functions, this lecture we will learn how to use a new function...count.

Exercise to help you practice aggregate functions.

Yet with another aggregate functions, we have two more, average and summation.

Group by is a statement used with aggregate functions, which will take your querying skills to another level.

having also is used with group by and aggregate functions, it's used like the where clause, but there is a difference, let's see what is the difference.

In this exercise you will need to use different techniques that we have learned through the course.

Summary of the section to remind you of the points we discussed in brief.

Introduction to section seven.

In this lecture we are going to learn what is meant by DML and DDL.

Finally let's see how to write the insert statement in SQL Server.

Exercise about the Insert Statement.

We have another insertion related lecture, we are going to discuss insert into select.

Delete statement used to delete one or more records from a tables but with some rules, let's see how to do it.

Update statement also is used to update one or multiple columns based on conditions, let's see how to write it.

Exercise about the update statement.

Another exercise about the update statement to make sure you understand it correctly.

One of the tricks of Management Studio is we can generate scripts.

Summary of the section to remind you of the points we discussed in brief.

Introduction to section eight.

In this lecture we are going to start exploring some basic built-in string functions.

Moving on to a more advanced string functions we are going to discuess replace, substring and concat.

Exercise on the string functions in SQL Server

Another exercise testing your knowledge of the string functions.

Len is a simple function used to get the length of the records.

In this lecture we are going to discuss a very powerful string function CharIndex.

We have an interesting exercise I want you to extract the email username from the email field.

In this lecture I want you to extract the email service provider from email field.

In this lecture we are going to discuss one of very powerful SQL server functions it is coalesce.

Replicate simply repeats a string value a specified number of times. let's see how to use it.

Right Extract a substring from a string starting from right and Left extract a substring from a string starting from left.

We can use a function called trim to removes the space character.

Summary of the section to remind you of the points we discuss in brief.

Introduction to section nine.

Let's explore the date functions we are going to start with two functions, GetDate and DateAdd.

An exercise about the dates.

Returns the count of the specified date part boundaries crossed between the specified startdate and enddate.

Returns a character string that represents the specified date part of the specified date.

In this exercise I need you to calculate the age of each employee based on the DOB column in Employees table.

Now let's see how the math functions work.

Another math functions we are going to discuss in this lecture they are the abs, rand and sign.

Summary of the section to remind you of the points we discussed in brief.

Introduction to section ten.

By using joins, you can retrieve data from two or more tables based on logical relationships between the tables. The most important and frequently used of the joins is the INNER JOIN. 

Still discussing about the inner joins so that you can fully understand it.

Let's see how to add multiple inner joins in one query.

Exercise on joins, to create join between Cities and Countries tables in HumanResources Database.

More advanced exercise we have here, getting the number of cities in each country.

Right outer join performs a join starting with the second (right-most) table and then any matching first (left-most) table records.

The left outer join returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.

A view is a virtual table whose contents are defined by a query. Like a table, a view consists of a set of named columns and rows of data. 

In this lecture we are going to see how to modify the structure of a view after we have already created it.

Another easier alternative way to create views, is to create views using Management Studio.

Now it's time for an exercise about the views.

Summary of the section to remind you of the points we discussed in brief.