You will be able to
- Create and drop databases and tables with CREATE and DROP statements, including primary keys and auto increment.
- Insert, update, and delete records safely by using WHERE clauses.
- Modify table structure with ALTER TABLE to add, change, and drop columns.
- Filter, sort, and deduplicate query results using comparison operators, BETWEEN, LIKE, IN, ORDER BY, and DISTINCT.
- Connect related tables with foreign keys and combine them using INNER, LEFT, and RIGHT joins.
- Summarize data with aggregate functions, GROUP BY, HAVING, and string functions such as CONCAT, UCASE, and LCASE.
Good to know first
- A local MySQL setup, such as XAMPP or WAMP, or access to a MySQL server
- Basic understanding of tables, rows, and columns
- Familiarity with running commands in a web-based or desktop database tool
Syllabus
10 lessons
- 1
Intro to SQL and setting up a local database server Free
You will learn what SQL is, which databases and tools use it, and why a local server such as XAMPP is a good place to practise. You will also see how to open phpMyAdmin and set a password for the root user.
1 code4 build steps2 exercises3 questions - 2
Creating databases and the customers table Beyond Pass
You will learn how to create and drop a database with SQL, then build a customers table with typed columns, a primary key and auto increment. You will do it yourself in phpMyAdmin's SQL tab or any MySQL tool.
2 code5 build steps2 exercises3 questions - 3
Inserting, updating and deleting records Beyond Pass
You will learn how to add rows with INSERT INTO, change existing rows with UPDATE, and remove rows with DELETE. The most important rule is to always use a WHERE clause with UPDATE and DELETE so you only touch the row you mean.
4 code5 build steps3 exercises3 questions - 4
Changing a table's structure with ALTER TABLE Beyond Pass
You learn how ALTER TABLE adds a column, changes a column's data type, and drops a column. You also see how each change shows up in the table's structure.
- 5
Selecting, Sorting, and Removing Duplicates Beyond Pass
You will learn how to pick columns and rows with SELECT and WHERE, how to sort results with ORDER BY, and how to remove repeated values with DISTINCT. Each idea is shown on a real customers table in phpMyAdmin.
6 code7 build steps3 exercises3 questions - 6
Filtering with operators, patterns, and indexes Beyond Pass
You will learn how WHERE clauses use comparison operators, BETWEEN, LIKE, and IN to filter rows, and how an index can speed up searches on a column. You will practise each one on a customers table.
6 code5 build steps3 exercises4 questions - 7
Designing related tables with foreign keys Beyond Pass
You will create an orders table whose columns point at the products and customers tables, using foreign key constraints. You will also see why those constraints stop a database from holding references to rows that do not exist.
1 code3 build steps3 exercises3 questions - 8
Joining Tables with INNER, LEFT and RIGHT Joins Beyond Pass
You will learn how a JOIN combines rows from two or more tables using a shared column, and how INNER, LEFT and RIGHT joins differ in which rows they keep. You will also see how to join three tables in one query.
4 code6 build steps3 exercises3 questions - 9
Using aliases and CONCAT to format results Beyond Pass
You will learn how to rename columns with AS (aliases) so results are easier to read, and how to join several columns into one with CONCAT. You will also see how table aliases shorten queries that use more than one table.
3 code3 build steps4 questions - 10
Aggregate functions, GROUP BY, HAVING and string case functions Beyond Pass
You will learn how aggregate functions (AVG, COUNT, MAX, MIN, SUM) turn many rows into one summary value, how GROUP BY and HAVING summarise rows by category, and how UCASE and LCASE change the case of text. You will try each one on a customers table in SQL.
6 code8 build steps3 exercises4 questions
Free preview · Lesson 1 in full
Intro to SQL and setting up a local database server
You will learn what SQL is, which databases and tools use it, and why a local server such as XAMPP is a good place to practise. You will also see how to open phpMyAdmin and set a password for the root user.
Type the lesson's commands, break things, and see why.
Draw and write next to the video.
The idea
SQL stands for Structured Query Language. It is a special-purpose programming language made to work with relational databases, where data is stored in tables made of rows and columns. SQL is still the main way an application talks to a relational database, and it is used everywhere from small personal blogs to large enterprise systems.
Many database systems speak SQL. The video names MySQL (the one used in this course), PostgreSQL, Oracle, Microsoft SQL Server, SQLite, MariaDB and others. Because they share the same core SQL syntax, what you learn here carries over to most of them.
You can work with a database in several ways. The command line client is a shell program you log into from a terminal, where you type SQL yourself. Graphical tools such as phpMyAdmin, Adminer, MySQL Workbench, PG Admin III and HeidiSQL give you buttons and forms, and many of them also include a SQL box. This course focuses on writing SQL, so you will mostly use the SQL shell, and phpMyAdmin only to view the rows that come back.
To practise on your own computer, install a local server bundle such as XAMPP or WAMP. It gives you Apache, MySQL (or MariaDB), PHP and more on your machine, so you do not need a remote server. Once it runs, open phpMyAdmin in your browser at localhost/phpmyadmin.
A fresh install often has no password for the root user, which is the main administrator account. In phpMyAdmin, open User accounts, find the row where the user is root and the host is localhost, and check the Password column. If it says No, click Edit privileges, choose Change password, type a password, and click Go. If phpMyAdmin then logs you out, open the phpMyAdmin config file (config.inc.php in the phpMyAdmin folder), put the same password in the password field, save, and reload the page.
Key points
- SQL is the structured query language for relational databases, and it is still the main way apps talk to them. 0:19
- MySQL, PostgreSQL, Oracle, SQLite and MariaDB are among the many databases that use SQL. 2:31
- A local stack like XAMPP or WAMP runs Apache, MySQL and PHP on your own machine, so no remote server is needed. 4:50
- phpMyAdmin is opened at localhost/phpmyadmin; this course uses it mainly to look at results. 5:30
- Give the root user a password under User accounts; if phpMyAdmin then boots you out, put that password in config.inc.php. 6:06
Code from the screen
$cfg['Servers'][$i]['password'] = '123456';Open the file C:\xampp\phpMyAdmin\config.inc.php in a text editor, find this line (it is on line 21 in the video) and set the value to the password you chose, then save and reload phpMyAdmin.
Build along in the practice terminal
- Download and install XAMPP (or WAMP) from apachefriends.org, then start Apache and MySQL from its control panel. 4:50
- Open your browser and go to localhost/phpmyadmin to check that phpMyAdmin loads. 5:30You should see: The phpMyAdmin home page appears with a list of databases on the left.
$ http://localhost/phpmyadmin - Click User accounts, find root with host localhost, and check whether its Password column says Yes or No. If No, click Edit privileges, then Change password, enter a password twice, and click Go. 6:06You should see: The Password column shows Yes, or a message confirms the password was changed.
- If you are logged out, open the phpMyAdmin folder, edit config.inc.php, put the same password in the password field, save and reload. 6:42You should see: phpMyAdmin opens again without asking you to log in.
Watch out
- The root user with host localhost often has no password after a fresh install. Set one before you rely on it, because a shared or open root account is a security risk. 6:06
- The config file's password must match the one you set in User accounts, or phpMyAdmin will keep refusing to log you in. 6:42
- The video uses phpMyAdmin only as a viewer; the lessons ahead ask you to type SQL in the SQL shell or SQL box. 0:25
Practice
In your own words, explain what a relational database is and why SQL is used to work with it. Use the words table and row in your answer.
Solution
- A relational database stores data in tables, where each table has columns (the kinds of information) and rows (the individual records).
- SQL is the language that lets an application or a person ask the database to create tables, add rows, read rows and change them.
- So SQL is the common way to talk to these tables, which is why many systems such as MySQL and PostgreSQL share it.
Model answer: A relational database keeps data in tables made of rows and columns. SQL is the structured language used to create those tables and to read, add, change and delete the rows inside them, which is why many different database systems use it.
Set up your local server: install XAMPP or WAMP, open localhost/phpmyadmin, and check whether the root user (host localhost) has a password. Write down what the User accounts page shows in the Password column.
Hint
Look in the User accounts tab for the row with root and localhost.
Solution
- Install XAMPP or WAMP and start Apache and MySQL.
- Open localhost/phpmyadmin in your browser.
- Click User accounts and find the row root / localhost.
- Read the Password column: Yes means a password is set, No means it is not.
The Password column shows Yes or No for root@localhost; if No, set a password through Edit privileges, then Change password.
On your scratchpad
Draw a simple diagram of three boxes: your app, SQL, and the database. Draw arrows between them and label the arrows with the word query.
Quiz
What does SQL stand for?
- Structured Query Language
- Simple Query Logic
- Standard Question List
- System Query Layer
Show the answer
A. Structured Query Language · SQL stands for Structured Query Language, a language for relational databases.
0:25 watch the momentWhy does the course use the SQL shell rather than phpMyAdmin for most work?
- phpMyAdmin cannot connect to MySQL
- The course focuses on writing the SQL syntax itself, and phpMyAdmin is used mainly to view results
- The SQL shell is faster to install
- phpMyAdmin only works on Mac
Show the answer
B. The course focuses on writing the SQL syntax itself, and phpMyAdmin is used mainly to view results · The video says it puts away the graphical interfaces to focus on the actual SQL programming.
0:07 watch the momentWhere do you open phpMyAdmin on a local XAMPP setup?
- localhost/phpmyadmin
- phpmyadmin.com
- localhost:3306/sql
- C:/phpmyadmin/start
Show the answer
A. localhost/phpmyadmin · The video says to go to localhost/phpmyadmin once the local server is set up.
5:30 watch the moment
Lessons 2–10
They open with Beyond Pass: every lesson of every course, in full. One payment: $14.99 for 90 days or $29 for a year. No subscription.
Made by Beyond from the video “SQL Crash Course - Beginner to Intermediate” by Traversy Media. The video plays in YouTube's own player. Watch it on YouTube · All lessons