SQL Database MasterClass: Go From Pupil To Master!
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
Find online courses made by experts from around the world.
Take your courses with you and learn anywhere, anytime.
Learn and practice real-world skills and achieve your goals.
Low pay SQL developers make $60,000 per year. In this course you will learn the skills necessary to rock out SQL Statements, Views, Stored Procedures and more.
In this course you will Design a database from scratch so that you understand the thought process that goes into such a design. We will then bring in the basic skills of SQL and give you example upon example of how to use this skill while building on those basic skills adding in more technique and more difficulty until they become more advanced. You will learn skills that even you average DBA doesn't do properly.
When you are done with this course, you will be able to walk into a interview with a calm confidence and show them that you know what you are doing and apply these skills to build the database back end for an application that will stand the test of time.
If you want a better career take this course you will not be disappointed.
Enroll with confidence! Your enrollment is backed by Udemy's 30-day, no-questions-asked, money-back guarantee!
Not for you? No problem.
30 day money back guarantee.
Learn on the go.
Desktop, iOS and Android.
Certificate of completion.
|Section 1: Intro|
This course teaches you all you need to know in SQL by starting with a quick review about most basic concepts in creating databases.
Each database requires elements such as tables, views, stored procedures and functions. So to start with this tutorial, you need to consider UDEMY as your business. Lets understand how to design a database that would work for this concept.
Lets stop for a little bit and consider what tables would Udemy's database contain.
We will go through this course and this is the database we will design and use this basic concept to build something that can be bigger and more useful.
Follow the arrow in this video because it will offer you with High-Definition resolution that refers you to a more fluid video tutorial for an effective learning experience.
|Quiz 1||3 questions|
Section 1 quiz
|Section 2: Lets Recreate a Database Like Udemy's Database|
This is the start of our SECTION 2. We will experience the re-creation of UDEMY’s database from a data stand point. Let’s jump into our first class in creating the anatomy of our table for the re-creation of UDEMY database. We will discuss the importance of identity columns, foreign key columns and data columns in designing a table and how to use them in SQL.
If we think about the things Udemy's database needs to work we are talking about a very basic structure. What roles does this database require. It requires someone to make videos, It requires someone to watch the videos. It also requires a place to store the classes, and a place to store the videos.
Now let’s focus on the Person’s table. This video will help you manage your table by sorting out the important data need for the re-construction of UDEMY’s database. The simpler the table is the better, but not way too simple, simpler by means that you can make it less complicated in retrieving and managing data.
We will enumerate the Person’s table by filling up the column name, data type and allow null if needed. Then we will identify the different usage of tables for teachers, students, classes and videos to make links possible in the database using foreign keys. [ID, VideoID and ClassID]
After this 15-minute-long video we will have our quiz#2 for a re-cap.
Section 2 quiz
|Section 3: Keys Both Primary and Foreign|
This is the start of SECTION 3 of our course for both primary and foreign keys. SQL primary keys is technically known as a field in a table which uniquely identifies each row/record in a database table and cannot use NULL values.
A Primary Key is generally the ID field on a table. it is to be unique for every record in the table. A primary key is a number that in this case increments by 1 value for every record that is inserted. A foreign key is a number that resides in another table that relates to the primary key record by having the same value.
The video will discuss what happens by default when you create a new database diagram by adding all of your tables. The SQL will automatically generate a sleek diagram derived from your foreign keys and primary keys. The produced diagram of the collected data will show you the relationship of your classes connected by these keys.
Section 3 quiz
|Section 4: Lets Fill In Our Datapoints|
In this chapter under Section 4 of our tutorial, we will learn how to add data to the database.
|Section 5: THe Select|
Section 5 tutorial will focus on the SELECT statement in SQL. We will enumerate the anatomy of this statement through discussions and exercises that will help you do SQL faster.
To start this video, you need to understand the SQL SELECT statement through a constructive definition. The statement SELECT is used to select data from database and it uses data manipulation language comands. What it [SELECT]usually does, it queries to specify a result but does not calculate it simply retrieves zero or more rows from one or more database as per requirement of the case.
This tutorial will focus on giving you an easy way to create a database and relationship links inside it. The SQL SELECT statement helps you retrieve data from different tables.
Through a practical example, we will help you go through in understanding the structure of the SELECT statement. By declaring the command first, knowing the what the data is and where to get the data.
The purpose of analyzing the data before and after having it, inserting new data, deleting data, emptying data and retrieving report statistics is your one step closer to become a better company.
On the next video, we will talk about the command of the SELECT statement.
Anatomy of a SELECT statement in SQL is not a stranger to everybody, but to discuss this further stay tuned for the next tutorial.
This video will display your next exercise. Write SELECT statement for the following task to check out for the classes that Sally teaches, same as with Steve classes. You need to call back emails of each person at the same time same select statement, but under in alias email addresses as the username.
Go try it!
After doing so, we will provide you with the thorough discussions about this exercise.
In this SQL tutorial, we will go down further to do a lot more in this STATEMENT.
Basically, there is a lot more of these string functions to look forward to. They can return the same values any time with a specific set of input instruction/s provided by the condition of retrieving your data.
More cool stuff is up like we can do mathematical processes using SELECT statement to retrieve data from the table. We can call out substrings and manipulate data by nesting its values.
This introductory video about SQL “Order-By” clause, will discuss about how to retrieve data in ascending and descending manner. By default, “Order-By” clauses sorts data in ascending order but if you want to do the reverse sorting declare DESC attribute [descending] to your data.
“Order-By” keyword is used to sort the result-set by single or multiple fields and is usually used in SELECT command that extracts data from a database.
The video will demonstrate the usage of the person’s table. In our use case, we need to be familiarize with the processes in using ASC and DEC clauses applicable to different conditions based on a specific requirement.
Welcome to our next tutorial!
Today we will focus on WHERE clauses. The WHERE clauses help us control our data records by filtering and start limiting the necessary data from our database. We need to specify where to locate the data and what conditions or operators that we are supposed to use to retrieve our data. There are 9 recorded WHERE clause operators, so far. Such operators are as equal, lesser than, greater than, between, like and in to name a few.
We will also provide you with a brief example that will further discuss the importance of WHERE clause by using our existing table.
SQL LIKE and SQL IN operators will be the main topics of our tutorial today. Both of this SQL statement works on WHERE filter data in a specified pattern. This LIKE operator allows us to perform basic pattern-matching using characters such as _, % to name a few, that works like a formula that helps you in retrieving data from a table column. The IN requires you to take on values or for multiple values in a WHERE clause with a list.
As you scan down with this video tutorial, we will give you corresponding sample that will help you in understanding this set-up for re-creating UDEMY database.
This chapter of our tutorial, we will cover the most aggregate functions used in database analytics. We will focus on how to use the 5 functions such as: count, max, min, sum and average as we go along the with different sample analytics. Expect that you will get a demonstration in this video using some of each functions so that you can see what happens to our data after using some mathematical expressions.
Excited for more? See next the video.
The GROUP BY clause is used under a SELECT statement to call upon all of the rows together that contains all the data across multiple records. This statement is used to return data from specified column/s and aggregate functions to be performed on the one or more columns.
Its syntax requires it to declare SELECT statement, expression that we need to include, aggregate functions [See lecture 16] and expression or column, table to retrieve data and its required condition.
Our example will further give you an overview of this concept.
Section 5 quiz
|Section 6: Sub-queries|
This is the start of Section 6 for sub-queries. We're about to talk about the proper use of Sub-queries in SQL. It is commonly known as nested queries inside SELECT, INSERT, UPDATE or DELETE statement embedded in the WHERE clause or inside another sub-query.
So, let's get started.
Section 6 quiz
|Section 7: Course Slides and Material Up To Here|
|Lecture 19||39 pages|
We provided you with 39 course slides entitled: SQL for Beginners by Bill Sims. This is a summary note from our previous video tutorials. We hope that you can use these comprehensive materials as your basis in reviewing your way in SQL.
|Section 8: Joins|
In this SQL tutorial, we will study about INNER join. SQL INNER joins works on giving database an instruction to combine data from two or more tables that are related to each other. Technically, this keyword selects result-sets limited to rows from two or more tables as long as there is a relationship between the columns of each table.
We need to understand that both of the left and right join must be equal in order to call out the data from our database.
We provided you with an example that will help you visualize the process in using this statement.
There are different types of SQL joins, the INNER joins, LEFT OUTER joins, RIGHT OUTER joins, FULL OUTER joins, CROSS OUTER joins and SELF joins.
As mentioned from our last video, we already introduced the purpose of INNER joins and its concept.
The next SQL join is the LEFT OUTER joins. LEFT OUTER joins is based on result-sets where all of the rows from the first from the left area, table are well-kept. Simply means that it returns all unmatched data in the rows from left table and matches it with a NULL row to the right table. We will discuss a very good example by using our classes in re-creating our UDEMY database.
Checking out the relationship between teachers, classes and students in SQL INNER join? Go hit the play button to get a dose of this tutorial.
Section 8 quiz
|Section 9: User Defined Functions|
Today's tutorial is all about the overview of user defined functions in SQL. So gear up and let’s start with this lesson. User – defined functions are created using CREATE FUNCTION statement. This function can be revised by ALTER FUNCTIONS at the same time we can remove it using DROP FUNCTION statement. In this video, we will discuss the two types of functions the scalar and table variable that differs from its result set values.
Stay tuned with the next tutorial for scalar functions.
This tutorial will give you an overview about the advantages of using scalar functions in extending the functionality of our database. This function allows you to write more flexible SQL statements. We will run tests from our tables using this function by performing transformations to our data that can be determined at a certain run time. It can answer concatenation activities based on its specified requirements.
The purpose of scalar function is to perform these categories:
The date/time functions aid us on how to process the data or time. Math functions give us an array of arithmetic collections. String functions allow us to convert string expression. The miscellaneous functions are for the data that doesn’t fall on the categories above mention.
We included a helpful example for you to understand it more.
We provided you with a text file that you can copy and paste to test out Scalar Functions for SQL Statements.
One of the two important types of UDF [User-Defined Functions] is the “table functions.” Table functions are used to call one or more data fields. It declares table-valued parameters for stored procedures or functions. We will be giving a sample in checking Bob’s data results in creating this function to return a value.
Note: You cannot let the table functions select class.
We provided you with a text file that you can copy and paste to test out Table Functions for SQL Statements.
Section 9 quiz
|Section 10: The View|
This video tutorial talks about VIEWS.
A view outside a function is a beginning of more complicated, complex data type in SQL. Its purpose is a way of storing your queries for later use. The view is like your virtual table that contains rows and columns where you can pull out your data anytime.
Surely, we will give you a demonstration on how to use VIEW by using the table for calling out person’s queries where its type is for teachers. Where you can save your queries and re-use it again if you need it.
Hey guess what guys?
We provided you with SQL view statements that allows you to call out or view queries from the person’s table by retrieving teachers’ profiles. It includes his/ her name, address and contact.
So you can try this out, too!
|Section 11: Stored Procedures The V8 of the SQL World|
The SQL stored procedures are one of the important statements in our database. This is to support a consistent implementation of logic commands across applications in order to run data results all over again. Once this statement is inside your stored procedures you can do specific tasks such as designing, coding and testing.
This video will guide you in creating your first stored procedures. We will be doing the person’s table to call out the total spent by that person through its ID. The process allows you to accept input parameters and return multiple values as output parameters.
Hey, you guys! Check out this technique for SQL stored procedures. We provided you with these statements that will further help you out in retrieving data from persons’s table.
This allows you to alter stored procedures by declaring the float-integer value of the total spent based on the price from the person’s ID table.
Section 11 quiz
|Section 12: Insert Update Delete The Fabulous 3|
A quick start for Section 12: Insert, update & delete.
Let’s jump in with SQL INSERT statements. Ideally, this statement allows us to insert one or more new data records to our table.
The syntax arguments for the INSERT statement works out this way.
We will share some techniques in bringing all the columns together in the SQL scripting stage and reuse data from this simple concept. Add comma to INSERT more data to our existing table, filling up for the person’s name, contact details, type and price values. We can verify our data results if we run down the results using SELECT* of the person’s table.
Hit the play button for examples that will help you use INSERT statements.
We got something new today! SQL UPDATE statements.
UPDATE statement is for updating one or multiple records. To update our data, we need to declare the UPDATE statement with the table’s name. We need to SET the values of each column that corresponds to each other.
[col1=value1, col2=value2 etc.]
Then we need to call on the WHERE clause, to specify the column of data records that should be update. NOTE: If in case, we missed to declare the WHERE clause in our statement, it will update all the records of our table.
Don't want that trouble, right? You better double check your work else triple check will do!
Okay! Let’s move on to the SQL DELETE statements.
DELETE statements used to delete one or several data from our table. Since we don’t want any unnecessary and duplicated data in our database, we will use this DELETE statement to get rid of it. To delete the data, we need to declare the DELETE statement first and then we need to specify WHERE to delete the data.
Henceforward, in order not to delete the entire data, we need to set a specific filter calling out all duplicated data and delete duplicate data only.
What a relief, right?
Play this video and you’ll learn more.
This is the summary of Section 12 : The Fabulous 3 statements in SQL.
Insert new records that include all person’s profile.
We need to be very careful in updating our table. As discussed earlier, if we missed out the WHERE clause, it will UPDATE the entire data. In this example, Update new records for a person, setting the type of that person at the same time we can change the person’s password to a certain group of records.
Delete one or multiple records from our table. We will delete a person’s ID from our existing table based on a specific condition [duplicated data] that we should meet, this will delete the entire row of this person’s profile.
Section 12 quiz
|Section 13: Getting Real Deep In SQL|
How are we doing now in SQL?
What we're going to do today is to create some random data.In this case, we snatched these data from randomname.com in which we can use this to insert to our table. Let’s open our spreadsheets or simply open our notepads/notepad++ to do some cool stuff there.
This is to transfer the following:
For each random name, our table will be used INSERT ID’s for the following 50 rows of data.
So you better stay close for our next tutorial.
We're back with lists of random data [text file] that you can insert into person’s table. As a requirement of this activity, we need to INSERT INTO student’s table the following entries: per personID, classID, person’s contact details [email and address] and Price. SELECT data that are needed to our table and at the same time remove all bad data from this list to avoid duplication.
By doing so, we can INSERT and ID all the data we needed and get rid of those data that are unnecessary for the new set of table records.
As continuation for the adding of new data, we provided you with 77 random data so that we can test some conditions to filter 62 students from different classes. Initially, there are only few students enrolled for a certain class. So by doing this activity, we can INSERT more data [random data] by adding these students to the classes.
As we go along, we will show you some situations that we “might” encounter in INSERTING data to our table. So stay tuned in.
|Section 14: Introducing the Pivot|
Let’s start for our Section 14 topic, the introduction of SQL pivot.
The simplest way of describing SQL pivot is, it responsible in transforming data from a row-level data to columnar data. It rotates data by converting table-valued expression into another table by turning unique queries from a single column into multiple ones. The purpose of this clause is to help us improve our previous data from bad data or we need to see our data in a different way.
The unpivot works with the opposite concept, it converts columns to rows.
Excited to start?
Hit the play button to get started.
This chapter of our tutorial, will start with a demonstration that asks how much each student spends on each class. Considering that we have these data to the database wherein we included the price for each class. Now, we can get the data we want, by identifying the person’s ID, Class ID and average price. We will aggregate our data for our column and apply pivot table to get the average price of our 3 classes.
See you to our next tutorial.
We are going to cover the codes for our pivot lecture.
It allows the user to aggregate values in a data set and use pivot FROM clause to rotate it.
The argument of this operator:
SELECT col_list // is to declare the SELECT statement for all [*] or individual columns
//classID and the price
FROM table_exp //specify a table
PIVOT //rotates the data
aggregate_func (aggregate_col) //the function and the name of the column
FOR pivot_col //pivot will base on it
IN (pivot_col_values ) // specify the pivot column values that will be turned into columns
AS pivot_table_alias //codename or tentative name
[ORDER BY col_list];
Try it yourself!
Section 14 quiz
|Section 15: Trouble Shooting and Optimizing Your Database|
Let’s start Section 15 with the introduction to SQL Profiler. This discussion is for troubleshooting and optimization of narrowing down information to our database.
A graphical user interface for SQL. Designated under tool bar, SQL server profiler as selected it pops out with a trace properties box. It has two tabs the general and events section for us to manage our database properly. Its interface provides you with a check box to tick SQL batch completed and SQL batch starting commonly used in the set-up. We can also filter or database by clicking the column filters located at the lower right of this pop up box and then run this.
Learning about data optimization is very important, especially when we need to manage a bunch of records from your database. It's given, that if we have a larger database to manage with millions of records on it will eventually make the performance of our database slower.
We have a sequence of possibilities to optimize SQL from easiest to hardest situations that we can apply. These decisions will help us sort out possible solutions that way we can make our data faster to manage.
Adding Indexes are one of the easiest ways to get some speed improvement in retrieving your data from your database. It allows us to create indexes in the table to find it fast and efficient without going through the entire table.
The video will demonstrate how to properly use indexes, it uses the table by queries of personID and classID all the time. In line with this, we added AND clause to check how much they pay. We will learn how to add a new index by renaming the non-clustered indexes with the table name on it and put the field of our actual index. For a faster database, we need to add indexes common for both from classID and personID with name assignment to join the tables together.
Hope you get to enjoy it too.
See you to our next video.
Bear with this tutorial more, this 25-minute long video needs your full attention in understanding indexes. This SQL tutorial will create a script that queries our system table and get columns using primary and foreign keys. To start, in order to check out our system tables in the database, we need to type “SELECT* from sys.objects.” Here we can see the list of results of the database columns after calling out “SELECT* from sys.columns.” Where we found our objects and columns. At the same time, we can encounter foreign key columns and call out our parent object id in this matter.
After keying this script, it will eventually create a column for constraint object id, constraint column id, parent object id, parent column id, reference object id and reference column id.
These useful queries are at your disposal for building the script to the index with all foreign keys to create a better database. We can SELECT from our system’s object by knowing where to get our object ID for the student class table, class table and an ID column of our class table.
Try this out before we proceed with our next tutorial SQL Server Reporting Services.
Section 15 quiz
|Section 16: SQL Server Reporting Services (SSRS)|
Now we're back with this new Section which we will focus on SQL Server Reporting Services.
This tutorial will help you install SQL report builder 3.0.
We will jump now to the Microsoft download center and hit the download button in SQL Server 2014 Report Builder. This application allows you to run it without actually installing it. An authoring tool that permits us to create, update, report, manages reports and deployment of data records.
In this chapter of our tutorial, we will introduce you to the environment of SQL Server Reporting Services by creating our first SSRS report.
We will go through number of stages in building our first report:
[The project is a sequel server connection]
[In this case, we used the windows authentication to see our UDEMY database.]
[Display list of tables. As sample, we will select person’s table then hit NEXT]
[Can move the sequence by dragging it, it also changes the table report]
Then we are done.
Isn't it amazing?
|Section 17: Random Tips That Make Life Easier|
In this tutorial, we will go through the processes in using templates for a quick access to SQL. As we hover to our menu bar, let’s select view and hit template explorer it will display an array of browser templates.
This demonstration will focus on how to create a new template for our personal folder and how to edit and save these queries. These quick access lists of templates that can help us in creating sample scripts later.
Something new to learn right?
You'd better try it!
Hey Guys! We provided you with the whole database copy that we run- through in this tutorial. So grab this text file and make sure to find time to practice.
You are welcome to scan all the videos all over again. Enjoy!
Thanks for watching and don’t forget to check out some of our tutorials!
See you all around!
I have spent my life teaching others how to be better developers and master the art that is Software Engineering.
I have designed many Enterprise level applications as well as IOS and Android Applications. The skills I teach in my courses is the same skills that I use every day to do everything from running my teams to consulting on Enterprise level projects.
In my career I have found classes like this to be helpful in advancing my career and I wanted to make courses for people that really showed you end to end classes that weren't as simple as "Hello World". My goal as an instructor is to drive you to the next level in your careers, and if you want to do that, it takes 3 things. Knowledge, Leadership, Ingenuity. These are the 3 skills that i have used to take my career to the next level.
I have also been a father for 9 years. Throughout those years I have learned so much about myself and raising children. I have been on both sides of the fence from raising step-children and now raising my own son. It has been the experience of a lifetime and I would love to share any of my experiences with you so that you don't have to go through all the same learning curves that I did.