SQL for Beginners: Learn SQL using MySQL and Database Design

Explore databases, sql, and mysql, and learn how a dbms and relational databases relate. Install mysql and mysql workbench on Windows, Mac, or Linux, then write your first sql queries.

Explore what a database is, how SQL and DBMS enable querying and updating data in MySQL, an RDBMS with relational tables and relationships, using MySQL Workbench to run SQL commands.

Explore how relational databases organize data into tables with columns and rows, and how linking customer_id in orders to ID in customers creates relationships in MySQL.

Install and configure a MySQL server on Windows, including choosing the installer, setting up root and devuser accounts, enabling automatic startup, and testing connections with MySQL Workbench.

Install and configure MySQL server and MySQL Workbench on macOS, create a secure devuser with localhost access, and test connections to manage databases.

Install and configure MySQL server and Workbench on Linux (Ubuntu), set up apt repository, install packages, create a devuser with DBA privileges, and verify connections via Workbench.

Explore MySQL Workbench, an integrated development environment for design, administration, and SQL development. Learn to write, run, and manage SQL queries, connect to the MySQL service, and navigate schemas.

Install MySQL Workbench, connect as root or devuser, and explore users and privileges. Run basics like show databases, use database, and drop databases, while inspecting the result grid.

Learn how DDL creates database objects and DML manipulates data, focusing on table creation, data types, primary and foreign keys, and a coffee shop database with products, customers, and orders.

Learn the most common MySQL data types, including integers, floats, decimals, and text types. Explore date and time types, enums, booleans, and the role of null.

Explore how primary keys uniquely identify rows and how foreign keys link tables, using examples from customers, orders, and products to illustrate 1-to-N relationships and referential integrity.

Create a coffee store database in MySQL Workbench, building products, customers, and orders tables with primary keys, auto increment, varchar, decimal, and foreign keys linking orders to products and customers.

Modify your tables with alter table in mysql, and verify results using describe. Add coffee_origin, remove it with drop column, and observe how errors appear for duplicates.

Learn how to remove tables from a database using the drop table command, as demonstrated on the example database to delete the test table and verify its absence.

Delete all rows from a table with the truncate table command and verify with a select; it drops and recreates the table without restoring data.

Master advanced table alteration techniques in MySQL by adding and removing primary and foreign keys, applying unique constraints, renaming columns, and changing data types, plus a test database and exercises.

Create a dedicated test database in MySQL, then build three tables—addresses, people, and pets—defining primary keys and foreign keys, and practice typing the SQL statements to reinforce learning.

Learn to add and remove primary keys in MySQL tables using alter table, with practical examples on addresses and people tables, and understand null constraints and how to describe tables.

Learn how to add and remove foreign keys in MySQL, understand one-to-many relationships via an ERD, and apply alter table with foreign key constraints and references.

Add a unique constraint to a MySQL column using alter table and add constraint, then remove it with drop index, enforcing data integrity by preventing duplicates.

Rename a column in MySQL tables using alter table, with change or rename column, while preserving the data type and avoiding spaces by using underscores.

Modify column data types in MySQL with two commands: change column and modify column; explore renaming and changing types using the addresses table, including varchar, char, and int considerations.

Practice six alter table challenges to reinforce primary keys, foreign keys, adding columns, unique constraints, renaming columns, and datatype changes within MySQL database design.

Explore practical alter table techniques, including adding primary keys, defining foreign keys, adding email as varchar, enforcing unique constraints, renaming columns, and changing data types like char(7).

Learn data manipulation language (DML) basics in SQL, mastering insert, update, and delete operations in tables, illustrated with a coffee store database; future sections cover querying data.

Insert data into the products table using insert into, specify columns and values, and verify results with a select from the products table, handling auto-incremented id and multi-row inserts.

Master updating data in SQL databases with update, set, and where clauses to modify single or multiple rows, such as changing coffee origin.

Delete data from a table with delete from and a where clause to remove a single row or multiple rows, or all rows without a where clause, noting sql_safe_update cautions.

Download the customer_table and orders_table data, execute the insert into customers and insert into orders queries, then query all three tables to see 24 customers and over 100 orders.

Learn to retrieve data with the select command and filter with where clauses using in, between, and like, then sort, limit, deduplicate, and alias columns.

Learn to write simple select statements to retrieve data from a SQL database, selecting all columns or specific ones from the coffee store customers table.

Learn to filter data with where clauses in a select statement, using and and or to combine conditions, handle varchar values with quotes, numeric price values, in the coffee_store database.

Discover how to use inequality operators in sql where clauses, including not equal to, greater than, less than, and their inclusive variants, with practical examples on the coffee_store database.

Explore handling null values in sql by using is null and is not null in where clauses. Use the coffee store database to filter customers with or without phone numbers.

Practice retrieving and filtering data with SQL in MySQL: exercise set 1 querying female Bluth customers’ phones, products over 3.00 or from Sri Lanka, and counting male customers without numbers.

Filter female Bluth customers by gender and last name. Display first name and phone, and show products with price over 3 or origin Sri Lanka; identify males with null phones.

Master filtering with IN and NOT IN in SQL where clauses, listing multiple values in parentheses and quoting strings, using examples with last names Taylor, Bluth, Armstrong.

Learn how to use the between operator for range queries in sql, including dates, numbers, and strings, with inclusive bounds.

Use the like operator in SQL to pattern-match data with percent and underscore in where clauses. Learn when to apply like to strings and numbers and compare with range filters.

Learn to sort sql query results using order by, including ascending and descending on price, last names, and multiple columns, with where filters and default id ordering.

Practice in, not in, between, and like operators in MySQL with three tasks: filter Colombia or Indonesia origins, February 2023 orders for specific ids, and last-name pattern ar.

Review sql filtering: select name and price from products where coffee origin in Columbia or Indonesia, and order by name; show February 2023 orders by customer ids and like patterns.

Learn to select distinct values in SQL with the distinct keyword. Explore origin, id, and price in a coffee store database and how where and order by shape results.

Learn how to limit and offset rows in SQL queries using limit and offset, including zero-based indexing, comma syntax, and ordering examples from the customers table.

Explore column name aliases in sql queries by renaming the result set columns, using as or omitting it, with examples like coffee for name and country for coffee_origin.

Practice distinct, limit, and aliases through exercises: select unique last names from customers, first four orders for product 3 in February 2023, and price aliased as retail price in products.

Review solutions to exercise set 3 for section 6, including distinct last names with alphabetical ordering, February 2023 orders for product 3, and renaming price to retail price in products.

Explore selecting data from multiple tables with joins in MySQL, including inner, left, and right joins, and practice queries across more than two tables with exercises and solutions.

Master retrieving data from multiple tables in MySQL using inner, left, and right joins, with no full join in MySQL and leveraging foreign keys like product ID and customer ID.

Master inner joins in MySQL by linking orders and products on product id to fetch product names and order times, using aliases and optional where and order by.

Explore how left joins in MySQL retrieve all data from the left table and only matching data from the right table when joining customers and orders.

Explore right joins in MySQL, retrieving all data from the right table and matching from the left, compare with left joins, and learn when to prefer left joins for portability.

Master multi-table joins in MySQL by linking products, customers, and orders in a single select with inner joins; retrieve product name, price, customer names, and order time.

Practice using joins in MySQL across three exercises: retrieve order id and customer phone for product 4; filter coffees in Q1 2023 by name and time; list January 2023 female orders.

Explains step-by-step mysql join queries: orders with customer phones for product 4; product names with order times for January–March 2023; and female January orders, plus an entity relationship diagram.

Create entity relationship diagrams in MySQL Workbench by reverse engineering a coffee_store schema, using foreign keys to define relationships and importing and arranging objects.

Learn database design concepts, including normalization, the first 3 normal forms, table constraints, and one-to-one, one-to-many, and many-to-many relationships to improve usability.

Normalize data to organize a database, reducing redundancy and insert, update, and delete anomalies by splitting related data into tables and using joins for complete results.

Learn the rules of first normal form (1NF): prevent duplicate rows, enforce single-valued columns, and define a primary key (possibly composite), illustrated with a student table.

Explore second normal form by ensuring every non-primary-key column depends on the whole primary key, and learn to split into grades and ages tables to remove unrelated data.

Learn third normal form (3NF) and how non-primary key columns depend on the whole primary key. Use a subject–year example to see how table splitting achieves 3NF.

Explore how relational databases link tables through primary and foreign keys, and examine one-to-one, one-to-many, and many-to-many relationships using a coffee store example.

Explore one-to-one relationships in databases, where a key appears at most once across two tables, with examples from department heads and product details, and ER diagram symbols.

Explore the one-to-many relationship, where a customer's primary key appears as a foreign key in many orders. See how a customer can place multiple orders, each linked to one customer.

Explore many-to-many relationships and how to use a junction table, like order details, with foreign keys to orders and products; learn constraints that enforce data integrity.

Explore how constraints shape MySQL data: enforce not null, unique values, primary and foreign keys, check ranges, and default values to ensure data integrity in your cinema booking system.

Design and implement a cinema booking system database in MySQL, creating seven tables, modeling the schema, and inserting data to prepare for future querying.

Explore the cinema booking database schema, including seven tables with primary and foreign keys, not null constraints, and one-to-many and many-to-many relationships via a junction table.

Create the cinema booking system database and implement the films table with an auto-incrementing primary key id, a unique, not-null name, and a not-null length_min column to capture film duration.

Create the customers table with id as an auto-incrementing int primary key, and first_name, last_name, and email as varchar(45) with not null on last_name and email, and email unique.

Define the rooms table in MySQL with an auto-incrementing id as the primary key, a not null name, and a not null seats column. Verify creation in the database.

Create the screenings table with an auto-incrementing primary key and not null film_id, room_id, and start_time. Define foreign keys to films.id and rooms.id, establishing one-to-many relationships with films and rooms.

Create the seats table in MySQL with id as an auto-incrementing primary key, non-null seat_row and number, and room_id as a foreign key to rooms.id to model the room-seats relationship.

Create the bookings table to link customers, screenings, and reservations, defining an auto-incrementing primary key and two foreign keys, and verify the table structure with describe and show tables.

Create the reserved seat table as the final step, linking bookings to seats via foreign keys and reflecting one-to-many relationships to access customer data through bookings.

Insert seven tables in order using the provided scripts, then verify with select queries in MySQL Workbench for films, customers, rooms, screenings, seats, bookings, and reserved seats.

Explore SQL aggregate functions such as count, min, max, sum, and avg, group data by columns with group by, and filter with the having clause, plus exercises and video solutions.

Master aggregate functions like count, sum, min, max, and average that perform calculations on a column and return a single value, and use group by with having for per-group results.

Use the count aggregate function to tally rows and count specific columns in SQL. Learn how null values affect counts and apply where clauses for filtered results.

Explore the sum aggregate function to total numeric columns, like seats in rooms, by selecting sum(seats) from rooms. Filter results with where ID > 1.

Explore min and max aggregate functions in MySQL to find longest and shortest films in the films table, and understand why grouping is needed when selecting name with max length.

Explore the avg aggregate function to calculate the average length of films from the length_min column, with optional where clauses to filter over 2 hours.

Practice aggregate functions with three questions on bookings and Blade Runner 2049 screenings, including date-range joins for October 2022 and April 2023, using COUNT, SUM, MIN, and MAX.

Explore step-by-step aggregate queries using count, joins, and date filters to analyze bookings and screenings, including Blade Runner 2049, and using distinct and month and year functions.

Master the group by clause to aggregate data in SQL by one or more columns, counting bookings per customer and per screening, with joins across films, screenings, bookings, and customers.

Learn how to filter grouped data using the having clause after a group by, and how it differs from the where clause before grouping, with bookings and screenings as examples.

Practice advanced sql with aggregate functions, using group by, having, and where to count seats, tally screenings of films over two hours, and analyze seating per screening.

Provide advanced sql solutions using group by, joining bookings and reserved seats, and counting seats and screenings; cover where vs having, film length over 120, aliasing, and pre-grouping filters.