SQL fundamentals (MySQL)
Tables, queries, joins and user privileges.
Level 101 · 21 lessons · 18 practice labs
Lessons
Getting started
- Server and client: the mysql command line: You will understand the difference between a database server and the client that connects to it, and connect with the mysql command line.
- Installing and configuring MySQL (my.cnf): You will install and start a MySQL server, read its configuration file and control which network address it listens on.
- MySQL databases: create, select, list, drop: You will create, list, select and safely drop databases in MySQL on your own lab server.
- MySQL system databases and information_schema: You will browse the system databases where MySQL keeps its own metadata and list tables, columns and users by querying them.
- MySQL tables and data types: You will create tables in MySQL and choose a fitting data type, such as number, text or date, for each column.
- MySQL constraints and keys: You will understand how constraints and keys in MySQL block broken or duplicate data and make each record unique.
- MySQL: ALTER TABLE, DROP and TRUNCATE: You will add or change columns on an existing table and know the difference between dropping a table and emptying it.
- Short task: set up the tables for a small shop
Queries
- Inserting, updating and deleting data in MySQL: You will insert, change and delete records in MySQL, and learn to add a condition so you never touch every record by mistake.
- MySQL: SELECT and WHERE: You will read data from a MySQL table with SELECT and use WHERE to pick only the records you want.
- MySQL: LIKE, sorting and limiting: You will search text for patterns in MySQL, sort the results and limit how many records come back.
- MySQL: NULL, DISTINCT and CASE: You will compare empty values (NULL) correctly, remove duplicate rows and produce labels based on a condition.
- MySQL string, number and date functions: You will trim and join text, calculate the gap between dates and round numbers using built-in functions.
- Relationships between tables in MySQL: You will link several MySQL tables with keys and understand why we keep information in separate tables instead of repeating it.
- Joins in MySQL: You will use MySQL joins to bring data from related tables into one result and see what each type of join returns.
- MySQL: Aggregates, GROUP BY and HAVING: You will count and total records in MySQL, summarise them by group and filter the groups themselves.
- Short task: ask the sales data five questions
Administration
- Users and least privilege in MySQL: You will create MySQL database users, give each only the privileges its job needs and check those privileges.
- First hardening with mysql_secure_installation: You will close the empty password, anonymous account and unneeded test database on a fresh MySQL install.
- Backup and restore with mysqldump: You will back up a database with mysqldump, restore it elsewhere and test that the backup really works.
- Capstone: set up and authorise a MySQL customer database: For a fictional company you set up a customer database in MySQL, add sample records and give each user only the access it needs.
Practice labs
- Create the first tables for a stationery shop: Create product and category tables with suitable data types for Papatya Kırtasiye.
- Fix the wrong data types: Convert a table that stores prices as text and dates as VARCHAR to proper types without losing data.
- Harden a schema missing constraints: Add NOT NULL, UNIQUE, CHECK and foreign keys to a schema that allows negative stock and orphan orders.
- Insert a batch of new products: Add Lale Market's new-season products with a single INSERT and verify the count.
- Undo a wrong price increase: Correct with one UPDATE a 10 percent price increase that was applied twice to one category by mistake.
- Prevent a DELETE without WHERE: Enable safe updates, delete cancelled orders safely and block an accidental whole-table delete.
- List products running low: Return the first 20 products whose stock fell below 10, sorted by price.
- Work out late deliveries: Calculate the day difference between promised and actual delivery dates for Mavikuş Kargo's shipments.
- Label customers by spending: Use CASE to classify customers as bronze, silver and gold and count each class.
- Show orders with customer names: Join orders and customers to list the last 30 days' orders with customer names.
- Calculate revenue per city: Group revenue by city and use HAVING to show only cities above 50 thousand.
- Find why a total is inflated: Find a revenue total that doubled after a join, explain why and fix the query.
- Give a report user read-only access: Create a SELECT-only user for the accounting team and test that it cannot write.
- Trim an over-privileged app account: Restrict an application account that was given ALL PRIVILEGES to the tables it actually needs.
- Shut off remote root login: Harden a server that allows root login from anywhere, drop anonymous accounts and remove the test database.
- Back up one database to a file: Dump the stationery database with mysqldump and locate the table definitions in the file.
- Restore a backup under a new name: Load yesterday's backup into a new test database without touching the live one.
- Recover a single table from a dump: Extract only the deleted customers table from a full database dump and restore it.
About this course
Tables, queries, joins and user privileges.
What you will learn
- Getting started
- Queries
- Administration
Prerequisites
No prior knowledge is needed; the course starts from zero.
Lab environment
Lessons run in your own Kali Linux terminal in the browser, connected to a private lab network. Nothing to install.
Frequently asked questions
- Is this course right for my level?
- The course is at beginner level; every term is explained where it first appears.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.