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 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 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.

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.

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.

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.

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.

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

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 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.

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.

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.

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.

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.

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.

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 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.

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 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.

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.

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 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 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.