SQL Database MasterClass: Go From Pupil To Master!

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.

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.

In this chapter under Section 4 of our tutorial, we will learn how to add data to the database.
We provided you with a sample data that we can work on and for you to figure it out. This video will demonstrate in adding all those data under Sally and Steve classes.

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.

Enjoy.

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.

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.

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.
Just go try it yourself.
See you in our next video.

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.

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:

• date/time functions
• string functions
• Match functions
• Miscellaneous functions.

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.
Use these statements for your practice.
You really should try it out.

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.
Use these statements for your practice.
Try this one and see what it can do.

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.

Cool right?

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!