Chapter 1 What is a database?
__01-1 What is a database?
What is a ____database?
____What is a database management system?
__01-2 Understanding the types of databases
____Hierarchical database
____Network database
____key-value database
____relational database
__01-3 What is SQL?
Types of SQL according to ____DBMS
____Types of SQL grammar
__01-4 Why You Should Learn SQL
SQL is essential in the midst of a flood of ____data
How much are you using ____SQL?

Chapter 2: Creating Your Own SQL Practice Environment
__02-1 Check my computer environment
____Check your computer specifications in Windows
Check your computer specifications on ____macOS
__02-2 Installing MySQL
____Installing MySQL Community Edition on Windows
Installing MySQL Community Edition on macOS
__02-3 Installing MySQL Workbench
____Installing MySQL Workbench on Windows
____Installing MySQL Workbench on macOS
__02-4 Learn how to use MySQL Workbench
____Create a new connection
____Examining the MySQL Workbench screen layout

Chapter 3: Warm-up before starting SQL
__03-1 A Quick Look at DDL and DML
____Creating and Deleting Databases
____Creating and Deleting Tables
____Insert, modify, and delete data
__03-2 Understanding Database Modeling
____Concept and necessity of database modeling
____Types of data modeling
____Data Modeling Steps
Browse the ____sakila database
__Chapter 3 Review Problem

Chapter 4: Learning Basic SQL Grammar
__04-1 Retrieving data using the SELECT statement
____Retrieve columns with the SELECT statement
____Checking table column information in MySQL Workbench
__04-2 Retrieving data that meets conditions using the WHERE statement
____Retrieving specific values ​​using the WHERE statement
____Using comparison operators in the WHERE statement
____Using logical operators in the WHERE statement
__04-3 Sorting data with the ORDER BY statement
Sorting by column with the ____ORDER BY statement
____Sort in ascending or descending order
Retrieve top data with ____LIMIT
__04-4 Searching for strings with wildcards
Retrieving data containing a specific string using ____LIKE and %
Retrieving data containing special characters with ____ESCAPE
Retrieving data with a length determined by ____LIKE and _
Lookup strings with ____ _ and %
____Retrieving more diverse data with REGEXP
____Let's use wildcards more
__04-5 Grouping data with the GROUP BY statement
Grouping data with the ____GROUP BY statement
Filtering data grouped with the ____HAVING statement
Remove duplicate data with the ____DISTINCT statement
__04-6 Creating and Manipulating Tables
Automatically enter data with ____AUTO_INCREMENT
____Enter the query results into a table
__04-7 Organizing MySQL's data types
____Learning about numbers
Learn about ____ character types
____Learn about date and time types
Chapter __04 Review Problem

Chapter 5: Joining Two Tables
__05-1 Types of joins
Meaning of ____join
____inner join
____Outer Join
____Cross Join
____Self-Join
__05-2 Another query within the query, a subquery
Using subqueries in ____WHERE statements
Using subqueries in the ____FROM statement
Using subqueries in ____SELECT statements
__05-3 What is a common table expression?
____General CTE
____Recursive CTE
Chapter 05 Review Problem

Chapter 6: Using Various SQL Functions
__06-1 String Function
____A function that concatenates strings ─ CONCAT
____Data type conversion functions ─ CAST, CONVERT
Functions to replace ____NULL ─ IFNULL, COALESCE
____Function to change to lowercase or uppercase ─ LOWER, UPPER
____Functions to remove spaces ─ LTRIM, RTRIM, TRIM
____Functions that return the size or number of strings ─ LENGTH, CHAR_LENGTH
____Function that returns the length of a string up to a specific character ─ POSITION
____Function that returns a string of a specified length ─ LEFT, RIGHT
____A function that returns a string within a specified range ─ SUBSTRING
____A function that replaces a specific character with another character ─ REPLACE
A function that repeats ____ characters ─ REPEAT
____Function to generate a space character ─ SPACE
____Function to print a string in reverse order ─ REVERSE
____Function to compare strings ─ STRCMP
__06-2 Date Function
____Various functions that return the current date or time of the server
____Functions to add or subtract dates ─ DATE_ADD, DATE_SUB
____Functions to find the difference between dates - DATEDIFF, TIMESTAMPDIFF
____A function that returns the day of the week for a specified date ─ DAYNAME
Functions that retrieve year, month, week, and day values ​​from ____date ─ YEAR, MONTH, WEEK, DAY
____Functions to convert date formats ─ DATE_FORMAT, GET_FORMAT
__06-3 Aggregate function
COUNT function that counts the number of data that meet the ____condition
____Function to calculate the sum of data ─ SUM
____Function to find the average of data ─ AVG
____Functions to find minimum or maximum values ​​─ MIN, MAX
____Function to calculate partial sums and totals ─ ROLLUP
____Functions to calculate the standard deviation of data ─ STDDEV, STDDEV_SAMP
__06-4 Mathematical Functions
____Function to find absolute value ─ ABS
____Function to determine whether a number is positive or negative ─ SIGN
____Functions to find ceiling and floor values ​​─ CEILING, FLOOR
____A function that returns rounded numbers ─ ROUND
____Function to find the logarithm ─ LOG
Function to find the nth power of ____e ─ EXP
____Function to find the power of a number ─ POWER
Function to find the square root of ____ ─ SQRT
____Function to get random numbers ─ RAND
____Trigonometric functions ─ COS, SIN, TAN, ATAN
__06-5 Rank function
____A function that ranks by unique value ─ ROW_NUMBER
____A function that assigns a rank without considering priority ─ RANK
A function that assigns ranks without skipping ____─ DENSE_RANK
____Function that assigns group rankings ─ NTILE
__06-6 Analysis Function
Functions that refer to the previous or next row ─ LAG, LEAD
____Function to calculate cumulative distribution ─ CUME_DIST
____Function to calculate relative ranking ─ PERCENT_RANK
____Functions to find the value of the first or last row ─ FIRST_VALUE, LAST_VALUE
Chapter 6 Review Problem

Chapter 7: Using SQL Properly
__07-1 Stored Procedure
____Why Use Stored Procedures
____Creating a stored procedure
____Calling a stored procedure
____Check the contents of the stored procedure
____Delete stored procedure
__07-2 SQL Programming
____IF statement
____CASE statement
____WHILE statement
____Dynamic SQL
__07-3 Index
Things to keep in mind when using the ____ index
____Type of index
____Creating and Deleting Indexes
__07-4 views
Why use ____views?
____Creating and viewing views
Edit ____View
Check ____view information
Delete ____view
Manipulating data using ____views
__07-5 Stored Functions and Cursors
____Understanding Stored Functions
____Cursor to know
__07-6 Trigger
____Type of trigger
____Trigger Execution Time
Create a ____trigger
Chapter 7 Review Problem

Chapter 8: Building a Stock Analysis System with Python and DB
__08-1 Setting up the practice environment
____Installing Python
____Installing Visual Studio Code
__08-2 Creating a Crawler and Storage System
____Setting up a stock analysis system
____Installing Python Libraries
____Creating the database schema and underlying data
____Building a Python Crawler
__08-3 Analyzing Stocks with MySQL
Analyzing stock prices over 52 weeks
____Analyzing stock price changes over the course of a day
____Analyze changes in stocks compared to the previous day
____Analyzing stocks with consecutive rising stock prices

supplement
__A-1 What is database normalization?
____First Normal Form (1NF)
____Second Normal Form (2NF)
____Third Normal Form (3NF)
____BCNF Normalization (Boyce-Codd Normal Form)
____Fourth Normal Form (4NF)
____Fifth Normal Form (5NF)
__A-2 MySQL Workbench Modeling Tool Overview
__A-3 Installing the database required for the practice

Do it! Reflection Problems - Answers and Solutions
Search

Main contents of this book
ㆍYou can learn the concepts of databases and SQL, and learn basic SQL grammar by creating an environment for practice.

ㆍBecome familiar with practical grammar by learning joins, subqueries, etc. for querying multiple tables.
ㆍLearn how to retrieve data using simple queries by practicing SQL functions frequently used in the field.

ㆍLearn how to use stored procedures, indexes, and views to effectively utilize SQL and improve work efficiency.

ㆍBased on what you've learned, you'll learn how to use SQL in real-world situations by analyzing actual stocks using Python and databases.


Recommended for these types of people!
ㆍBeginners who are new to SQL or databases
ㆍPlanners, marketers, and designers who work extensively with data
ㆍJob seekers who have qualifications but want to gain practical experience
ㆍInstructors looking for a textbook that can properly teach SQL grammar using MySQL

A well-written introduction to SQL from an experienced database expert!
3-step learning from concept → practice → problem, no prior knowledge required!
This book is based on the author's well-received online and offline lectures on the fundamentals of SQL.
It is designed to be easy to follow even for those without prior knowledge of SQL or programming, with a three-step learning process that progresses from concept to practice to practice problems.
This book covers everything from setting up a SQL development environment to learning basic SQL grammar like DML and DDL statements, practical grammar like joins and functions, and advanced grammar like stored procedures, indexes, and views.
In the final chapter, Chapter 8, you'll gain hands-on experience analyzing actual NASDAQ stock data using Python data analysis packages and Yahoo Finance DB.


A practical SQL book that contains the content that companies want today!
We've collected a variety of queries and practical tips that can be applied immediately in real life.
Unlike other SQL introductory books on the market, this book focuses on providing readers with a variety of queries and content with high practical applicability so they can apply it immediately in real-world situations.
This book contains over 300 examples and over 50 practice problems, and the author, who is currently working as a senior DBOps, provides practical 'honey tips' to readers through the 'Good to Know' corner and tips in the text.
Additionally, the book provides a “SQL Function Dictionary” on page 7, so that you can look up frequently used functions in practice whenever you need them, just like in an Excel book.


For those studying alone, 'Do it!' is here to help.
Study plan + lecture video + code provided!
A study plan is provided on page 8 of this book.
If you are studying alone, write down your target dates on this schedule.
You can escape from being a SQL beginner by studying for one hour a day for three weeks.
For self-study, the Aegis Publishing YouTube channel offers free SQL lectures from authors who are active instructors on popular online lecture platforms and developer meetups.
Additionally, the author provides practice code on GitHub, so you can enhance your learning by comparing it with your own queries.

Easys Publishing YouTube: youtube.com/@easyspub
ㆍAuthor Kang Seong-wook GitHub: github.com/sqlmvp/doitmysql

Apply for a study group at 'Do it! Study Room'!
You can meet friends and receive books as gifts.
If you plan and study alone, you will quickly become tired.
Try joining a study group through 'Do it! Study Room'.
You can meet people who study together, share what you are studying, and get a book gift if you verify it.
Also, if you have any questions, please leave them on the 'Do it! Study Room' bulletin board.
Experts and authors will provide clear answers.

Do it! Study Room: cafe.naver.com/doitstudyroom