MySQL Database MasterClass: Go From Pupil To Master!

Here are all the slides for this section so you can follow along while we design and build our database.

In here i take you on a quick tour of php My admin so that you can understand the IDE.

In this chapter of our tutorial, we will figure out how to create a table for UDEMY’s customers. We will identify the necessary data associated with the table such as the identity of the column, email addresses, password, addresses and name.

This time, we will encode all the needed data for customer’s details to the table. We will show you the environment inside the MySQL Database.

So sit back and watch for more exciting tutorials.

In this MySQL video tutorial, we will create a new table for video. Then we will identify what makes up a class and check out any associated relationships to each elements. In addition, we will also have personType and be able to insert data base on personType.

See how far you go in this tutorial. Try out the quiz after this lecture.

Hey guys!

As you hit play button, we provided you with an automated play slides for your notes.

This is a brief introduction to primary and foreign keys. We identify the importance of each for our tables. We provided you with a sneak-peak of our goal in linking table people to table classes, video classes and how does it work.
Excited? See the next video.

To supplement further, we will identify foreign keys and primary keys and its purpose to our table.

As we discussed, foreign keys used to link two tables together. On the other hand, primary keys are ID’s field on a table that makes a table unique and must not contain any NULL values. In MySQL, it supports foreign keys that allows us to do cross-referencing across table and its constraints help keep in consistent spreading of data in our tables.

This video will show you how to create table for StudentClass, VideoClass table and insert all data need in phpMySQL that includes 3 fields [ID, personID and classID]. Then be able to use foreign keys on it and be able to use relations for each table.

Welcome to Section 4: The Select Statement.

Hit play button in an auto play of slides in our next lesson. This slide will show you the actual SQL, the Select Statement syntax and how does it works in our database.

The SELECT Statement, helps you retrieve your data records from one or more SQL tables. Select data from the database which includes the results stored in a table.

To use the SELECT Statement, we need to declare the command statement, then what do we want to select [* - Select all fields], also it could be a specific table, FROM and WHERE to retrieve the data. Technically, it filters data from our database.

SELECT * FROM table_name;
We provided you with samples in order for you to understand the processes in using a SELECT Statement and run it.

Go check it out!

Lets see where are we right now in this tutorial.

Hey! Check this out.

The previous video provided you with 4 writing activities using a SELECT statement. So let’s compare our work if we’re on the same page.
To write a SELECT Statement to the following:

1. Finding all classes under Sally’s teaching
2. Finding all classes under Steve’s teaching
3. Retrieving email addresses for each person
4. Writing same SELECT statement, but aliases, email address as the Username.

Guess what? This 19-minute video will perform a step by step process with different scenarios and show you with necessary points to consider while using a SELECT Statement.

There are many things we can do inside a SELECT Statement. We listed down all the possible things you can start with this statement. In line with this, we will show you the basics in retrieving data. What is nice about SELECT Statement it can perform mathematical operations seemingly, isn't it amazing?

So many things unlocked!

Hi there!

We provided you with useful queries that you can try on from this section.

Try it and see the results.

Press the play button for an auto play of slide shows for our next tutorial.

This slide covers a sneak-peak of our upcoming lessons.
ORDER BY and WHERE clauses of Section 5 of our tutorial.

This section of MySQL tutorial, we will discuss the concept of ORDER BY clause.

ORDER BY, retrieves data by ascending or descending manner. ORDER clause by default filters data in ascending means. If you want the other way around you need to declare the DESC [descending] after column names.

We’re back and let us start with WHERE clauses. WHERE clause uses to filter data in a manner that it only retrieves those records that is specified in the statement. WHERE can be on any fields that we can call if needed.

This video will provide you useful examples that you can try. This 21-minute long advance tutorial, can show you possible scenarios and on how to deal with massive sets of data.

To use this clause statement, we need to understand its anatomy.
SELECT col_name, col_name
FROM table_name
WHERE col_name operator value;

FYI: Operators [Operator value] can be equal, not equal, greater than or lesser than, between and more to mention.

Hope you learn something today!
Hit replay button to experience awesomeness again!

Let’s proceed with the next clause the GROUP BY. It can be used in a SELECT statement to gather all data across multiple or group of records by one or more columns.

GROUP BY, takes analytics to another level of challenge for our database.

We will be using Student class as our example in this tutorial.

Ready? Hit play button.

Lecture 21:

Hey guys! We provided you with different WHERE clause OPERATORS to try with. At the same time, you can also check the following OPERATORS listed below.

= Equal

<> Not equal. for some SQL it is !=

> Greater than

< Less than

>= Greater than or equal

<= Less than or equal

BETWEEN Between

LIKE Search for a pattern or filter keywords or values and etc.

IN To specify multiple possible values for a column

Try this, I know you can. See you on the next tutorial.

Hit the play button for auto slides.

We will discuss the following for the next Section:

• Analytics 101
• SQL statements for GROUP BY and Analytics.

Are you excited to start? Play the button!

This is analytics 101 and we will start to analyze data.

Let’s start with aggregate functions such as AVG, MIN, MAX, COUNT and SUM that we can use in the data analytics. We provided you with samples at phpMyAdmin under the class table for further understanding. This is how we should alias AVERAGE data and check out SUM, COUNT, MIN and MAX ID from our query.

Here it is!

Go on grab this example and try this out.
What a good challenge can wait to set up with you guys.

Welcome to Section 7 of our tutorial. This is a course slides for Sub-Queries.

To start with,

• What is a sub- query?
• Why use a sub- query?

We will learn about sub- query today because we have an independent data from another table.

A subquery may follow in the following:

- SELECT clause

- FROM clause

- WHERE clause

Now, let us use a sub-query, we need to understand the following guidelines to consider when using subqueries. Subqueries cannot manipulate their results internally, so ORDER BY clause cannot be added in to a subquery. At the same time, subquery can be treated as an inner query, another query called as outer query placed as part of another query.

Sub-query is fun! Get a dose of it by testing these statements.

This is the start of Joins in MySQL.

Inner joins, its purpose is to select rows from both tables as long as it matches between columns in both tables.

SELECT col_name(s) //declare column name

FROM table_1 //locate the table

INNER JOIN table_2 //apply INNER join

ON table_1.col_name = table_2.col_name; //processing INNER oins for results.

Okay, now let us use class table to check out the inner joins. We provided you with a demonstration which you can see the processes wherein ths statement returns the data queries. Use the inner joins that this tutorial provided you.

This tutorial provided you with lists of INNER joins that you can use in order to perform the exercise.

Left Outer Joins, technically returns all rows from the left that matches the rows from the other data table. If the data from the right side doesn’t match, it means it is NULL by value.

Let us go and fill up these placeholders for us to perform the left outer joins. The advantage of using LEFT outer joins, is that it ables to retrieve all data from the rows. It sbles to match the data from the left table, even if there are no matchces in the othe table.

Hey guys! We provided you with these useful statements that you can try out on this activity!

Section 9 of our MySQL online tutorial is all about installing MySQL locally. Copy and Paste this link to your address bar and it will lead you to download site. Follow instructions carefully and you'll have you MySQL ready.

Hey guys! Here it is! We will teach you how to download and install the MySQL.

Go to this link: http://dev.mysql.com/downloads/ and follow the rest of the instructions. Don’t forget to Windows update for a hassle free installation.

As we start to log-in to phpMyAdmin, we will export it and be able to select our database. We will configure our database and execute the process.

Then we’re good to go.

As you guys can see the SQL is running, we will connect to our server. We will import some data so that we have something to work with.

As a practice, we will use Udemy as our sample database.

This is the start of Section 10 of our tutorial: Creating our first views and stored procedures.

Lecture 36 is all about creating our first view and at the same time, we provided you with samples for better understanding.

A view is defined as a statement in our queries. It can contain rows of a table and SELECT rows from a table. The purpose of using views is to modify and customize the view for each user has of the database.
Interested? Hit play button.

See this text file as you practice in creating your first view. You should try this out. The view can provide us a backward compatible interface to match a table whose schema has changed. It allows us to summarize data from different tables that can generate reports from our queries. Best is, it allows the user to customize his/her table base on exactly on his/her condition and needs.

This video talks about stored procedures. What is nice about stored procedures is that it’s like snippets of prepared codes that you can recycle all over. It allows us to work faster than usual because it has list of statements that we can retrieve anytime.

As we go on, we provided you with samples that best explains stored procedures.
Enjoy!

Take a look at these samples that provided just for you. You can start by trying this out. There are a lot of stored procedures available that you can practice with. It assists the user in completing a consistent implementation of logic across the application by improving database’s performance.

Discover another topic in this section. Some examples of USER DEFINED FUNCTIONS are the scalar and tabular functions which are an excellent tool for certain programming task.

Today, we will take you on a quick tour about Basic Scalar UDF.

In a Basic Scalar UDF, the UDF stands for User-Defined-Functions. This video will show you how to create scalar functions. It can help us solved confusions with an enhancement to the SQL database engine speed performance.