The previous lesson taught us to fill up the band data into our table for bands [tbband] under database [BILLFEST]. Now, we are about to add the vendor lists data that includes ART, FOOD and JEWELRY. But first things first we need to grab this text data by downloading it or simply copy it from this screen and paste it to any text editor.

This is now under SECTION 2 Of our tutorial and will cover Basic SQL [discussion on SELECT statement]

Same goes to this chapter, see LECTURE 18 about filling up data on tables but this time select the table for vendor [tbvendor] under BILLFEST.

The data includes the following:

• Vendor name
• Lot sizes
• Type [ART, FOOD and JEWELRY]
• Contact Number
• Contact Name
• Contact email

This may take up some time but learning is a process.
Got some error though, can you see your self out? Don’t forget to SAVE and go back to STRUCTURE to check out some errors. [NULL allow on total sales, since we miss it at first]

If done hit “GO” button and you are DONE!

Under this section of the PhpMyAdmin Tutorial we will demonstrate INSERT INTO to the table for customers [tbcustomers].

Here you can find instructions as you go to SQL window in PhpMyAdmin. There are six important buttons [SELECT*, SELECT, INSERT, UPDATE, DELETE and CLEAR] that is needed for basic SQL scripting. It will run SQL queries for database BILLFEST.

SNEAK-PEAK : The advantages of using scripting in inserting bunchful data without encoding it manually.Curious?

See Lecture 22.

A sneak peak was introduced prior to this video about inserting massive data for customer’s table in BILLFEST database. Moreover, the use of online free name generator as an example to demonstrate how effective SQL scripting is.

WAMP + SQL = AWESOME tutorial will assist you the step by step processes to manage enormous data, the creation of macro technique in the scripting to eliminate manual activities for data encoding.

To create a macro technique:

1. Place the cursor at the beginning of the first row.
2. Hit START RECORDING button
3. Select the value for lastname in the first row.
4. Remove the lastname and the comma
5. Press END key to move to the end of the line if you are dealing with hundreds of data else make sure to go to the end of the line.
6. Add comma
7. To paste the lastname after the firstname.
8. Make sure to return to the start of the line or press HOME key
9. Move to the next line then hit STOP RECORDING.
10. Press PLAY button then use your arrow keys to apply the MACRO that you just recorded.

Its like an action recorded and apply scripting automatically.

Everything is AWESOME.

See Lecture 22 for your 10 step guide in creating macro in SQL scripting.

Another massive data to import, so open up your notepad and copy these files as we proceed to the next video tutorial in working with examples on “SELECT" statement.

This lesson will focus on SQL “SELECT” statement. SELECT statement is used to retrieve data from a database table in the form of result table. By using our project, we will be able to understand SELECT statement in action.

See you after you are done with these exercises!!!

Welcome back!
Considering that you are done with the previous exercises [Lecture 24], we wil now proceed to this video.
In this chapter of our tutorial, we’ll do few exercises to check out how far are we now in terms of learning this topic.

We provided you with to do list.
1. Write the SELECT statement that will find all the FOOD VENDORS
2. Write the SELECT statement that will find all the ROCK BANDS with nore that 4 members
3. Write the SELECT statement that retrieves only the EMAIL ADDRESS of each bands.
4. Write the SELECT statement that but alias EMAIL ADDRESS as EMAIL

Now do this challenge as your homework.

This SQL tutorial explains how to use ORDER BY clause in band table for our festival. The statement ORDER BY clause is to sort our data from the database table either through in descending or ascending manner. You should consider that columns that are specified in the ORDER BY clause is of the columns selected in the SELECT column list.

In additional, aliases defined in the SELECT Statement can be used in an ORDER BY Clause. [Lecture 26, 4th task from the exercise]. Aliases gives temporary name to a database table or column table.

Let’s run down into details about the clause, considering by default ORDER BY Clause sort data in ascending order. In order to sort the data in descending order you must declare it obviously in writing DESC attribute to your data.

That sounds really interesting, right?

Now, hit the play button.

This video will now focus on WHERE clause in SQL using our project’s lot sizes under vendor table. WHERE clause condition filters the results then apply SELECT, INSERT, UPDATE, or DELETE statement and return all data from the table.

See our next video.

In this tutorial, we will introduce SQL LIKE condition and how to use it in our case study[table for customers].
This statement is used in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement to search for a specified requirements or matching pattern. It will return all corresponding data based on the conditon that you set. The parameter of expression requires column or field; its pattern is matching to either % or _.

Note:

% [PERCENT SIGN WILD CARD] It allows you to match any string of any length even zero length and use it mutiple times in the same string.

_ [UNDERSCORE WILD CARD] It allows you to match on a single character

This link is for you to follow instructions on how to perform the phpMyAdmin installation for your hosting account by bringing MySQL to the web.

It is an intuitive free software tool provided by phpMyAdmin constructed in PHP for web interfaces, support, data processes, administrations, creative and versatile.

DOWNLOAD 4.5.1

The file is less than 10MB, 9.9MB to be exact and it will only take you less than 5 minutes to download it.

Gear up!

This WAMP + SQL = AWESOME video tutorial will discuss a series of processes about the scenario of exporting and importing data.

You can select export or import method base, saving procedures, format-specific options, create object options in order for you to download or upload data.

Hit replay button if you want to review again.

In this part of our lesson we will be able to identify the purpose of redesigning the database by learning how to manage small details on our project.

First, it is to fix mistakes from the initial database design at the same time it allows the database to adapt changes in system requirements. We need to understand the current structure of our database so that we can test it or update it for changes. Significantly, we can create backup for our data. We can also read a database diagram and deisgn a schema that produces data model. [reversed engineering]

Hope this additional information is helpful to you.
See yeah!

1. tbband
2. tbcustomer
3. tbgenre
4. tbvendor

SECTION 2 of our tutorial ENDS here.

Excited for the next section.

This chapter is the start of SECTION 3: Creating Random Data for our project.

Keep in mind that we will be using FOREIGN KEY [tbbandID] in order for us to link the table[tbband]. In line with this, review Lecture 11 if you miss something. We will be doing some example like adding new table for the band members and it is for you to construct it further.

You guys go ahead and add the 3 remaining bands on your own. We provided you with this list for the exercise so that it will help you go through the experience the process in adding data to the different placeholders.

This video assumes that you are done adding the remaining band data and assign each data to its allocated band ID. Go and hit the play button so that we can compare our answers.

IN Statement is one of the coolest SQL statement in terms of saving time, it also allows you to specify for multiple values in a WHERE clause. The video will enumerate the structure of IN statement that will call data records from case study’s bandID.

This tutorial will discuss about MySQL had a RAND function [RAND ()] that can invoke to produce random numbers of floating-point value between the range of 0 to 1. This is to fill in numerical values to our case study at the same time update our records.

Keep watching!

This lesson will focus on the importance of handling our data for easy management. We need to know our data, analyze our data and make decisions for our data. By doing so, it will make us a better company.

This is the end of SECTION 3 tutorial.

SECTION 4 of our tutorial will focus on a SUB - QUERY.

As defined in MySQL, sub-query is a query within a query that can exist in WHERE clause, SELECT clause and FROM clause. This 7-minute long video will introduce the sub-query and how to use it. It will help us get data more quickly and use foreign keys without using the joint. To optimize our SQL statement tutorial, we will try subqueries in updating our data and fixing columns [how did you find us] field.

Start of Section 5: Analytics

In this chapter, we need to figure out how to solve and update our project. In which case, the genre ID doesn't match the genre holder. So you are expected to at least figure it out on your own.

Then after this the video we will reveal the answer.

Try not to sneak.

How was it?[Lecture 40]

This is the answer of the previous challenge. You can try this one and discover it yourself.

The tutorial prepared a thorough explanation about the solution of the last do it yourself exercise. This video will expound the details of tbGenre.ID and GenreID as sub queries.

In this tutorial, you will learn how to use the MySQL aggregate functions. The importance of learning these functions will help you perform some calculations on data values in order to retrieve data because not all data you need is in the table. We will discuss the basic analytics on your data, such as COUNT, MAX, MIN, SUM and AVG.

See our next video.

We provided you with a text file to start with MySQL Analytics on tickets bought from the customer’s table.

Go grab it now and lets have some fun today.

This two and a half minute video will explain our next activity using MySQL Analytics by getting the average ticket/s bought by every table for customers[tbcustomer].

We will be using aggregate functions such as to COUNT and to SUM analytics for us to know how much do we make on tickets. Getting the MAX and MIN functions that returns the set of values of tickets everybody bought for the said music event.

What a great day to see the festival’s summary report/s!

Any project in mind right now? In the meantime, lets proceed to our next tutorial.

To supplement MySQL Analytics, GROUP BY will bring you to another level of SQL scripting. MySQL extend to use this fancy function because there are non-aggregated columns and they are good in retrieving group information. The GROUP BY clause is most likely to be found in SELECT statement. It is used to scan all the database tables and create temporary tables where this transitory table will discover other groups that are arranged sequentially for the application of aggregation.

Excited for the next Lecture?