Skip to main content

pelatihan.orgLearn. Practice. Grow.

Sign in Sign up

pelatihan.org

Learn web development with a clear roadmap, code practice, and quizzes with instant feedback.

Catalog Sign in Subscription

MySQL Curriculum: 5 Chapters, 68 Lessons — pelatihan.org

Home/Catalog/MySQL/Curriculum

Curriculum

MySQL Curriculum

Learn MySQL online with 68 structured lessons, code examples, and 340 quiz questions.

68 lessons 5 chapters 340 questions

Summaries and key points are free to preview. Full lessons, code examples, and quizzes require a subscription.

01

MySQL Tutorial

3 lessons

01MySQL HOMEMySQL is one of the most popular open source relational database management systems (RDBMS) in the world, and is the backbone of many popular web applications. To communicate…

02MySQL IntroMySQL is a relational database server developed by MySQL AB and now owned by Oracle Corporation. The server runs on a variety of operating systems, supports SQL standards, and…

03MySQL RDBMSRDBMS (Relational Database Management System) is the foundation of MySQL: data is represented as a table consisting of rows and columns. Each table has a primary key as a unique…

02

MySQL SQL

43 lessons

01MySQL SQLSQL is the standard language for managing relational databases, and MySQL uses it with a number of customizations of its own. SQL commands can be grouped by function: DDL for…02MySQL SELECTThe SELECT command is the most commonly used command in MySQL because it reads data from one or more tables. You can select a specific column or use an asterisk to take all the…03MySQL SELECT DISTINCTThe SELECT DISTINCT command is used when you want results without duplicate values on selected columns. MySQL compares the value combinations on all the columns mentioned, so one…04MySQL WHEREThe WHERE clause is used to filter the row to only meet certain conditions that are involved in the processing. The condition can compare the column with a fixed value or the…05MySQL ORDER BYThe ORDER BY clause is used to sort the SELECT results based on one or more columns. By default the sequence is ascending, and you can add DESC to the down sequence. MySQL also…06MySQL ANDThe AND operator is used to combine two or more conditions that all must be TRUE values in order for a row to be displayed. You can install as many ANDs as possible, for example…07MySQL OROR operators are used when a line satisfies one of several conditions to participate in display. OR makes the result wider than AND, so it's often combined with parentheses to…08MySQL NOTThe NOT operator reverses the evaluation results of a condition, so that the previous line TRUE becomes FALSE and vice versa. NOT is often used with IN, BETWEEN, and LIKE to…09MySQL INSERT INTOThe INSERT INTO command is used to add new lines to a table. You can write the column name explicitly (recommended) and then provide the corresponding value in the VALUES…10MySQL NULL ValuesThe value NULL means data is unknown or unfilled, not equal to zero or empty string. Because of this particular property, comparing NULL with the operator = or <> will always…11MySQL UPDATEThe UPDATE command is used to change the value of an existing row in a table based on the condition in the WHERE clause. Without WHERE, the entire row of the table will change,…12MySQL DELETEThe DELETE FROM command is used to remove a row from a table according to the WHERE condition. Similar to UPDATE, DELETE without WHERE will delete all the contents of the table,…13MySQL LIMITThe LIMIT clause is used to limit the number of rows returned by a SELECT query. The basic form LIMIT n takes n of the first row in the specified order, and can be combined…14MySQL Aggregate FunctionsThe aggregate function counts a group of rows into one summarized value, such as total, average, sum, smallest, and largest. This function is almost always used with GROUP BY to…15MySQL MIN()The MIN() function returns the smallest value of a column to the group of rows that is calculated. This function works on the type of numbers, text, or dates, and ignores the…16MySQL MAX()The inverse of MIN(), the MAX() function returns the largest value of a column, such as the most expensive price or last date. This function also ignores NULL and can be used for…17MySQL COUNT()The COUNT() function calculates the number of rows that meet the criteria in your query. COUNT(*) counts all rows including those with columns NULL, whereas COUNT(column) only…18MySQL SUM()The SUM() function sums all the numerical values in a column, e.g. total sales or total points. Because it only works on numbers, the SUM() on the text column will produce either…19MySQL AVG()The AVG() function calculates the arithmetic mean of a numerical value on a column, e.g. the test value average or the price average. Remember, the value of NULL is not counted,…20MySQL LIKEThe LIKE operator is used to match the value of a text column to a pattern, not the exact value. The pattern is written with the help of a wildcard percentage (%) representing…21MySQL WildcardsWildcard is a special character that represents one or more other characters in a search pattern, and is most often used with the LIKE operator. The two main wildcards in MySQL…22MySQL INThe IN operator checks whether the value of a column matches one of the values from a list. In many cases, IN becomes a more neat alternative to writing many OR conditions, e.g.…23MySQL BETWEENThe Between operator checks whether a value is within a certain range, including both of its limits. BETWEEN is commonly used on the range of numbers and dates, e.g. the price of…24MySQL AliasesThe alias temporarily names columns or tables in a query to make the results easier to read and refer to. The column aliases are written after the column name using the US…25MySQL JoinsJOIN is used to combine rows of two or more tables based on the relationships between columns, usually foreign keys versus primary keys. With JOIN, separate data in multiple…26MySQL INNER JOININNER JOIN only displays the row that has matching pairs on both tables. If a row in the left table does not find a pair in the right table (or vice versa), the row does not…27MySQL LEFT JOINLEFT JOIN (or LEFT OUTER JOIN) displays the entire row from the left table, then pairs it with the right table column when appropriate. If there is no pair, the right table…28MySQL RIGHT JOINRIGHT JOIN (or RIGHT OUTER JOIN) is the opposite of the position of LEFT JOIN: the entire row of the right table is guaranteed to appear, even if it has no pair in the left…29MySQL CROSS JOINCROSS JOIN returns the result of the cross multiplication: each row of the left table is paired with all the rows of the right table. If the left table has 3 rows and the right…30MySQL Self JoinSelf join is the merging of a table with itself, which is usually done by giving two different aliases to the same table. This technique is used when a table represents…31MySQL UNIONThe UNION operator combines the results of two or more SELECT queries into one vertical set result, provided that the number of columns and types are aligned. Intuitively, the…32MySQL UNION ALLUNION ALL is used to combine the results of two or more SELECT queries into one final result. Unlike ordinary UNION, UNION ALL maintains all lines including duplicate ones so…33MySQL GROUP BYGROUP BY is used to group data rows based on one or more columns, e.g. per city, category, or department. Each group can then be calculated by an aggregate function such as…34MySQL HAVINGHAVING is used to filter the results of groupings after GROUP BY is run. If WHERE filters the row before counting, HAVING filters the group based on the results of an aggregate…35MySQL EXISTSEXISTS is a subquery operator that returns TRUE if the subquery produces at least one row. EXISTS is often used to check the presence of related data in other tables, such as…36MySQL ANYANY (and its synonym SOME) compares a value with each result of the subquery and is worth TRUE when at least one comparison is met. This operator is combined with the comparison…37MySQL ALLALL compares the value with all the results of the subquery and is only worth TRUE when the entire comparison is met. Because the requirements are strict, > ALL is often used to…38MySQL INSERT SELECTINSERT INTO ... SELECT allows us to directly copy data from the SELECT query results into other tables without having to write one by one. Suitable for filing, light migration,…39MySQL CASECASE provides the possibility of if-else logic within a MySQL query so we can create the result column conditionally. There are two forms: simple CASE that compares values, and…40MySQL Null FunctionsMySQL provides several functions to handle the NULL values, including IFNULL, COALESCE, and NULLIF. IFNULL replaces the NULL value with a substitute value so that the calculation…41MySQL Stored ProceduresA stored procedure is a collection of SQL statements stored on a server and can be called many times by a specific name. With procedures, business logic such as reports or update…42MySQL CommentsComment is used to write explanatory notes in SQL scripts without affecting command execution. MySQL supports single-line comments with the -- or #, as well as multi-line…43MySQL OperatorsMySQL supports a wide variety of operators, from arithmetic operators, comparison operators, logic operators, to special operators such as IN, BETWEEN, LIKE, and <=>. Each…

03

MySQL Database

18 lessons

01MySQL Create DBA new database is created using the CREATE DATABASE command, while the USE is used to select the database to be used by the current session. MySQL also provides the SHOW…02MySQL Drop DBThe database was permanently deleted from the server with the DROP DATABASE command. This command removes all the contents of the database, such as tables, indexes, and data, so…03MySQL Create TableA new table is created with the CREATE TABLE command followed by the definition of each column and its data type. MySQL provides data types such as INT, VARCHAR, DECIMAL, DATE,…04MySQL Drop TableThe table is removed from the database by the DROP TABLE command, which also deletes all the data in it. If you just want to empty the data but maintain the table structure, use…05MySQL Alter TableALTER TABLE is used to alter the structure of an existing table, such as adding, changing, or removing columns. This command can also be used to add constraints, change data…06MySQL ConstraintsConstraints are built-in rules of the database that restrict the type of value that can be entered into a column or table. MySQL recognizes several major constraints: primary…07MySQL Not NullConstraint NOT NULL requires a column to always contain a value every time a line is created or changed. Without the NOT NULL, the column is allowed to store the value NULL which…08MySQL UniqueThe UNIQUE constraint ensures that each value in the column is different, so that the double data cannot be stored. Unlike the primary key, a table may have multiple UNIQUE…09MySQL Primary KeyA PRIMARY KEY is a column (or a combination of columns) that is the unique identity of each row in a table. The property is always UNIQUE and NOT NULL, so no two rows should have…10MySQL Foreign KeyFOREIGN KEY maintains the reference integrity by linking the columns in the child table to the primary key or unique key in the parent table. With this relationship, nonexistent…11MySQL CheckConstraint CHECK limits the value of the column based on a condition, e.g. price must not be negative or minimum zero stock. MySQL started to fully enforce CHECK since version…12MySQL DefaultThe default constraint assigns an in-built value to a column so that the value does not have to be written always when INSERT. These default values can be either literal such as…13MySQL Create IndexAn index is a specialized structure that speeds up the search, sequencing, and merging of data without changing the contents of a table. Indexes are created with a CREATE INDEX…14MySQL Auto IncrementAUTO_INCREMENT automatically adds a column of value every time a new line is inserted, so you don't have to fill in the ID manually. These columns are usually paired with a…15MySQL DatesMySQL provides data types DATE, TIME, DATETIME, TIMESTAMP, and YEAR to store time, complete with built-in functionality to take, format, and compare dates. Functions such as…16MySQL ViewsView is a stored query that can be used like a table without storing the data itself in it. View simplifies complex queries, hides sensitive columns, and gives a consistent view…17MySQL InjectionSQL injection is an attack where an attacker inserts a malicious SQL snippet through an application input, such as a login column, to manipulate a server query. A classic example…18MySQL Prepared StatementsA prepared statement is a mechanism where a query is set with a placeholder and then executed multiple times with different parameter values. Parametric values are always treated…

04

MySQL Examples

4 lessons

01MySQL ExamplesAn example page invites you to frame various MySQL concepts in real-world scenarios like sales reports, product searches, or customer data retrieval. Learning is most effective…02MySQL EditorA MySQL editor or query tool is a place to write, run, and easily check query results, both via command line and graphical applications such as MySQL Workbench and phpMyAdmin.…03MySQL SyllabusMySQL syllabus maps the learning sequences from basic syntax like SELECT, compiling queries with JOIN and GROUP BY, to advanced topics such as view, stored procedure, security,…04MySQL Study PlanA study plan sets goals, schedules, and practical activities so that MySQL learning progress remains directed and consistent. For starters, just allocate 30-60 minutes a day to…

All-course subscription

$5.00 / 30 days

Access every course, lesson, and quiz. Progress is saved automatically.

This curriculum is free to browse. Activate a subscription to unlock full lessons, code examples, and quizzes.

Activate subscription Back to course