Learn SQL (using MySQL) in One Day and Learn It Well

1. SQL is the Language of Relational Databases

Simply stated, SQL stands for Structured Query Language and is a language used to manage data stored in a relational database.

SQL's Core Function. SQL (Structured Query Language) is the standard language for interacting with relational databases. It allows users to define, manipulate, and control data. Understanding SQL is fundamental for anyone working with data storage and retrieval systems.

Database Management Systems (DBMS). SQL is implemented across various DBMS, including MySQL, Oracle, Microsoft SQL Server, and IBM DB2. While each DBMS may have its own SQL version, they all support major SQL commands like SELECT, UPDATE, DELETE, and INSERT in a similar manner. Learning one version of SQL makes it easier to adapt to others.

Practical Application. This book uses MySQL, a popular and free DBMS, to teach SQL. The hands-on approach involves building a database and performing exercises to solidify understanding. This practical experience is crucial for mastering SQL and applying it effectively in real-world scenarios.

2. Database Design: The Foundation of Data Management

A database is a collection of data organized in some format so that the data can be easily accessed, managed and updated.

Importance of Structure. Effective database design is crucial for efficient data management. A well-designed database ensures data integrity, reduces redundancy, and simplifies data retrieval. This involves creating tables, defining columns, and specifying constraints.

Key Elements of Table Design. When creating tables, it's essential to specify the data type for each column (e.g., INT, VARCHAR, DATE) and any constraints (e.g., NOT NULL, UNIQUE, PRIMARY KEY). Constraints enforce rules on the data, ensuring its accuracy and consistency. Table constraints, like primary and foreign keys, define relationships between tables.

Practical Steps. The book guides readers through creating a database called companyHR with tables like employees and mentorships. It covers creating tables, specifying columns with appropriate data types and constraints, and altering or deleting tables as needed. This hands-on approach reinforces the principles of database design.

3. Data Manipulation: Inserting, Updating, and Deleting

To insert data into MySQL, we use the following syntax: INSERT INTO table_name (column1, column2, column3, …) VALUES (value1, value2, value3, …);

Core Operations. Data manipulation involves inserting new data, updating existing data, and deleting unwanted data. These operations are fundamental for maintaining an accurate and up-to-date database. SQL provides specific commands for each of these tasks.

SQL Commands. The INSERT command adds new rows to a table, the UPDATE command modifies existing rows, and the DELETE command removes rows. Each command requires specific syntax and conditions to ensure the correct data is affected. Constraints defined during table creation can impact these operations.

Practical Examples. The book provides examples of inserting data into the employees and mentorships tables, updating contact numbers, and deleting employee records. It also demonstrates how constraints, such as foreign keys, can prevent certain data manipulation actions to maintain data integrity.

4. Data Retrieval: Mastering the SELECT Statement

SELECT column_names_or_other_information [AS alias] [ORDER BY column(s)] [DESC] FROM table_name [WHERE condition];

The Power of SELECT. The SELECT statement is the primary tool for retrieving data from a database. It allows users to specify which columns to retrieve, filter rows based on conditions, sort results, and create aliases for columns. Mastering the SELECT statement is crucial for extracting valuable insights from data.

Filtering and Sorting. The WHERE clause filters rows based on specified conditions, using operators like =, !=, >, <, BETWEEN, LIKE, IN, and NOT IN. The ORDER BY clause sorts the results based on one or more columns, in ascending or descending order. Subqueries can be used within the WHERE clause to filter results based on queries on other tables.

Practical Application. The book demonstrates various uses of the SELECT statement, including selecting all columns, filtering columns, using aliases, limiting results with LIMIT, removing duplicates with DISTINCT, and sorting rows with ORDER BY. It also covers using subqueries to filter results based on data from other tables.

5. Functions: Enhancing Data Selection and Manipulation

A function is a block of code that does a certain job for us.

Extending SQL Capabilities. Functions are pre-written blocks of code that perform specific tasks, enhancing the capabilities of SQL. MySQL provides a wide range of built-in functions for string manipulation, date and time operations, and aggregate calculations.

Types of Functions. String functions like CONCAT() and SUBSTRING() manipulate text data. Date and time functions like NOW(), CURDATE(), and CURTIME() work with date and time values. Aggregate functions like COUNT(), AVG(), MAX(), MIN(), and SUM() perform calculations on sets of values.

Practical Use. The book demonstrates how to use these functions in SELECT statements to format data, perform calculations, and extract specific information. It also covers using the GROUP BY clause to group data for aggregate calculations and the HAVING clause to filter grouped data.

6. Joins and Unions: Combining Data from Multiple Tables

Like the name suggests, a join is used to join data from different tables based on a related column between the tables.

Combining Data Sources. Joins and unions are powerful tools for combining data from multiple tables. Joins combine rows from two or more tables based on a related column, while unions combine the results of two or more SELECT statements.

Types of Joins. There are three main types of joins: inner join, left join, and right join. An inner join selects rows where a common value exists in both tables. A left join selects all rows from the left table and matching rows from the right table. A right join selects all rows from the right table and matching rows from the left table.

Practical Examples. The book provides examples of joining the employees and mentorships tables to retrieve employee and mentorship information. It also demonstrates using the UNION keyword to combine the results of two SELECT statements, removing duplicates by default or including them with UNION ALL.

7. Views: Simplifying Complex Queries

Simply stated, an SQL view is a virtual table.

Abstraction and Security. A view is a virtual table based on a SELECT statement. It simplifies complex queries by encapsulating them into a single object. Views do not store data but provide a way to access data from one or more tables as if it were a single table. Views can also restrict access to certain data, enhancing security.

Creating and Using Views. The CREATE VIEW statement creates a view based on a SELECT statement. Once created, a view can be queried like a regular table. The ALTER VIEW statement modifies an existing view, and the DROP VIEW statement deletes a view.

Practical Application. The book demonstrates creating a view called myView that joins the employees and mentorships tables. This view simplifies querying employee and mentorship information by combining data from the two tables into a single virtual table.

8. Triggers: Automating Database Actions

A trigger is a series of actions that is activated when a defined event occurs for a specific table.

Event-Driven Automation. A trigger is a set of actions that are automatically executed when a specific event occurs on a table. These events can be INSERT, UPDATE, or DELETE. Triggers can be invoked before or after the event, allowing for automated data validation, auditing, and other tasks.

Trigger Syntax. The CREATE TRIGGER statement creates a trigger, specifying the trigger name, the event that activates it, and the table it applies to. The BEFORE or AFTER keyword indicates when the trigger is invoked. The FOR EACH ROW clause specifies that the trigger is activated for each row affected by the event.

Practical Example. The book demonstrates creating a trigger called update_ex_employees that inserts a record into the ex_employees table before a record is deleted from the employees table. This trigger automates the backup of employee data before deletion.

9. Stored Routines: Reusable SQL Code

A stored routine is a set of SQL statements that are grouped, named and stored together in the server.

Modularity and Efficiency. A stored routine is a set of SQL statements grouped, named, and stored in the server. There are two types of stored routines: stored procedures and stored functions. Stored routines promote code reusability, improve performance, and enhance security.

Stored Procedures vs. Stored Functions. Stored procedures are executed using the CALL keyword and can have IN, OUT, and INOUT parameters. Stored functions are executed within a SELECT statement, must return a value, and can only have IN parameters.

Practical Application. The book provides examples of creating stored procedures to insert new members, delete members, update member information, make bookings, and update payments. It also demonstrates creating a stored function to calculate bonuses.

10. Control Flow: Adding Logic to SQL

In order to achieve the above, we need to use control flow tools.

Conditional Execution. Control flow statements add logic to SQL code, allowing for conditional execution of statements. These include IF, CASE, WHILE, REPEAT, and LOOP statements.

Types of Control Flow Statements. The IF statement executes different tasks based on conditions. The CASE statement matches a variable against a set of values or performs complex matches using ranges. The WHILE statement repeats tasks while a condition is true. The REPEAT statement repeats tasks until a condition is met. The LOOP statement repeats tasks indefinitely, requiring LEAVE or ITERATE to exit.

Practical Examples. The book demonstrates using IF statements to check conditions and execute different tasks, CASE statements to match values and perform actions, and WHILE, REPEAT, and LOOP statements to perform repetitive tasks.

11. Cursors: Processing Data Row by Row

A cursor is a mechanism that allows us to step through the rows returned by a SQL statement.

Row-by-Row Processing. A cursor allows you to iterate through the rows returned by a SELECT statement, processing each row individually. This is useful for tasks that require row-by-row manipulation or analysis.

Cursor Syntax. The DECLARE CURSOR statement declares a cursor for a SELECT statement. The DECLARE CONTINUE HANDLER statement defines what to do when the cursor reaches the end of the result set. The OPEN statement opens the cursor, the FETCH statement retrieves the current row, and the CLOSE statement closes the cursor.

Practical Application. The book demonstrates using a cursor to retrieve the names and genders of all employees and combine them into a single line of text. This example illustrates how to declare a cursor, open it, loop through the rows, fetch data, and close the cursor.

12. Project: Building a Sports Complex Booking System

This project requires us to build a simple database to help us manage the booking process of a sports complex.

Real-World Application. The project involves building a database for a sports complex booking system. This project integrates all the concepts covered in the book, providing a practical application of SQL skills.

Database Design and Implementation. The project requires creating tables for members, pending terminations, rooms, and bookings. It also involves creating a view, stored procedures, a trigger, and a stored function. The stored procedures handle tasks such as inserting new members, making bookings, updating payments, and canceling bookings.

Testing and Validation. The project concludes with testing the database to ensure it functions correctly. This involves inserting data, executing stored procedures, and verifying the results. The project provides a comprehensive learning experience, solidifying SQL skills and preparing readers for real-world database development tasks.

Last updated: April 15, 2025