SQL for Immediate Practical Use [Direct Instruction by the Author of SQL200]

Data has become more closely related to our real lives than ever before.
And I think about various social phenomena,
SQL is used as one of the programming techniques to find answers.

SQL has the power to ask questions and make you think.
If you search through data in various ways, you may find answers you never thought of.

Such questions can be of many types.
It could be a current social phenomenon or a prediction of the future.

This lecture is also a lecture that teaches SQL.
We'll show you how to find and gather data yourself to find information.

We find these things through data.

All data is stored and managed in a database.
SQL is a very powerful language for finding information in databases.

Oracle has built-in technology to process large amounts of data quickly.
And as the version is upgraded, the optimization technology is advancing.
Users can also easily implement machine learning with simple SQL.

The SQL lectures are structured from the basics up to practical application levels.
I hope this will be helpful to many people studying SQL.

Questions and Feedback

For questions and feedback on the SQL lecture, please feel free to ask questions at instructor Yoo Yeon-su’s cafe.
Improve your skills by checking out other people's queries!

A lecture directly delivered by the author of the SQL200 book published by Information Culture Publishing Company!

Over the years, countless data analysts have been able to sharpen their data search skills with instructor Yoo Yeon-su's SQL lectures.

#1 recommended by graduates! The course chosen by 2,000 people! SQL lectures are now available at Inflearn.

Data analysis employment status of non-major students

This is the employment or job change status of those who took instructor Yoo Yeon-su's SQL class.

If you want to get a job as a data analyst, please enroll now.

Lecture Table of Contents

PART 1 〈Introduction〉 Taking your first steps with SQL
Select specific columns (COLUMN) from table 001
002 Output all columns from table
003 Change the output column name using column alias
004 Using the concatenation operator (||)
005 Output by removing duplicate data (DISTINCT)
006 Sorting and outputting data (ORDER BY)
007 Learning the WHERE clause ① (Searching numeric data)
008 Learning the WHERE clause ② (Searching for characters and dates)
009 Learn Arithmetic Operators (*, /, +, -)
010 Learning Comparison Operators ①(〉, 〈, 〉=, 〈=, =, !=, 〈〉, ^=)
011 Learning Comparison Operators ② (BETWEEN AND)
012 Learning Comparison Operators ③ (LIKE)
013 Learning Comparison Operators ④(IS NULL)
014 Learning Comparison Operators ⑤(IN)
015 Learning Logical Operators (AND, OR, NOT)

PART 2 〈Beginner〉 Building SQL Basics
016 Learn case conversion functions (UPPER, LOWER, INITCAP)
017 Extracting specific letters from characters (SUBSTR)
018 Print the length of a string (LENGTH)
019 Output the position of a specific letter in a character (INSTR)
020 Change a specific spelling to another spelling (REPLACE)
021 Fill in N specific letters (LPAD, RPAD)
022 Trimming specific spellings (TRIM, RTRIM, LTRIM)
023 Rounding and printing (ROUND)
024 TRUNC the number and print it
025 Output the remainder after division (MOD)
026 Print the number of months between dates (MONTHS_BETWEN)
027 Print date with added months (ADD_MONTHS)
028 Print the date of the day following a specific date (NEXT_DAY)
029 Print the last day of the month with a specific date (LAST_DAY)
030 Convert data type to character type (TO_CHAR)
031 Converting data type to date type (TO_DATE)
032 Understanding Implicit Type Conversion
033 Outputting other data instead of NULL values (NVL, NVL2)
034 Implementing IF statement in SQL ① (DECODE)
035 Implementing IF statement in SQL ② (CASE)
036 Output the maximum value (MAX)
037 Print minimum value (MIN)
038 Output the average value (AVG)
039 Output total value (SUM)
040 Print the number of cases (COUNT)
041 Outputting rankings using data analysis function ① (RANK)
042 Outputting rankings with data analysis function ② (DENSE_RANK)
043 Outputting grades with data analysis function (NTILE)
044 Outputting the rank ratio using data analysis function (CUME_DIST)
045 Outputting data horizontally using data analysis function (LISTAGG)
046 Outputting the previous and next rows with data analysis functions (LAG, LEAD)
047 Output COLUMN as ROW ① (SUM+DECODE)
048 Output COLUMN as ROW ② (PIVOT)
Output 049 ROW as COLUMN (UNPIVOT)
050 Outputting accumulated data using data analysis function (SUM OVER)
051 Outputting ratios with data analysis function (RATIO_TO_REPORT)
052 Outputting aggregate results using data analysis function ① (ROLLUP)
053 Outputting aggregate results using data analysis function ② (CUBE)
054 Outputting aggregate results using data analysis functions ③ (GROUPING SETS)
055 Numbering output results with data analysis function (ROW_NUMBER)

PART 3 〈Intermediate〉 Building SQL skills
056 Limiting the number of rows printed ① (ROWNUM)
057 Limiting the rows output ② (Simple TOP-n Queries)
058 Joining and outputting data from multiple tables ① (EQUI JOIN)
059 Joining and outputting data from multiple tables ② (NON EQUI JOIN)
060 Joining and outputting data from multiple tables ③ (OUTER JOIN)
061 Joining and outputting data from multiple tables ④ (SELF JOIN)
062 Joining and outputting data from multiple tables ⑤ (ON clause)
063 Joining and outputting data from multiple tables ⑤ (USING clause)
064 Joining and outputting data from multiple tables ⑥ (NATURAL JOIN)
065 Joining and outputting data from multiple tables ⑦ (LEFT/RIGHT OUTER JOIN)
066 Joining and outputting data from multiple tables ⑧ (FULL OUTER JOIN)
067 Connecting data up and down with set operators ① (UNION ALL)
068 Connecting data up and down with set operators ② (UNION)
069 Outputting the intersection of data with a set operator (INTERSECT)
070 Outputting the difference in data using set operators (MINUS)
071 Using Subqueries ① (Single-row Subquery)
072 Using Subqueries ② (Multi-row Subqueries)
073 Using Subqueries ③ (NOT IN)
074 Using Subqueries ④ (EXISTS and NOT EXISTS)
075 Using subqueries ⑤ (Subqueries in the HAVING clause)
076 Using subqueries ⑥ (Subqueries in the FROM clause)
077 Using Subqueries ⑦ (Subqueries in SELECT clause)
078 Entering data (INSERT)
079 Modify data (UPDATE)
080 Deleting data (DELETE, TRUNCATE, DROP)
081 Saving and canceling data (COMMIT, ROLLBACK)
082 Enter, edit, and delete data at once (MERGE)
083 Understanding LOCK
084 Understanding the SELECT FOR UPDATE clause
085 Entering data using subqueries
086 Modifying data using subqueries
087 Deleting data using subqueries
088 Merging data using subqueries
089 Outputting data by giving a sequence using a hierarchical query ①
090 Outputting data by giving a sequence using a hierarchical query ②
091 Outputting data by giving a sequence using a hierarchical query ③
092 Outputting data by giving a sequence using a hierarchical query ④
093 Creating a general table (CREATE TABLE)
094 Creating a temporary table (CREATE TEMPORAY TABLE)
095 Simplifying complex queries ① (VIEW)
096 Simplifying complex queries ② (VIEW)
097 Speed up data retrieval (INDEX)
098 Create a number that never gets duplicated (SEQUENE)
099 Recovering accidentally deleted data ① (FLASHBACK QUERY)
100 Recovering accidentally deleted data ② (FLASHBACK TABLE)
101 Recovering accidentally deleted data ③ (FLASHBACK DROP)
102 Recovering accidentally deleted data ④ (FLASHBACK VERSION QUERY)
103 Recovering accidentally deleted data ⑤ (FLASHBACK TRANSACTION QUERY)
104 Improving data quality ① (PRIMARY KEY)
105 Improving data quality ② (UNIQUE)
106 Improving data quality ③ (NOT NULL)
107 Improving data quality ④(CHECK)
108 Improving data quality ⑤ (FOREIGN KEY)
109 Using WITH clause ① (WITH ~ AS)
110 Using WITH clause ② (SUBQUERY FACTORING)
111 Solving Algorithm Problems with SQL ① (Printing the 2-stage multiplication table)
112 Solving Algorithm Problems with SQL ② (Printing Multiplication Tables 1 to 9)
113 Solving Algorithm Problems with SQL ③ (Right Triangle Output)
114 Solving Algorithm Problems with SQL ④ (Triangle Output)
115 Solving Algorithm Problems with SQL ⑤ (Rhombus Output)
116 Solving Algorithm Problems with SQL ⑥ (Rectangular Output)
117 Solving Algorithm Problems with SQL ⑦ (Sum of Numbers from 1 to 10)
118 Solving Algorithm Problems with SQL ⑧ (Multiplication of Numbers from 1 to 10)
119 Solving Algorithm Problems with SQL ⑨ (Print only even numbers from 1 to 10)
120 Solving Algorithm Problems with SQL ⑩ (Print only prime numbers from 1 to 10)
121 Solving Algorithm Problems with SQL ⑪ (Greatest Common Divisor)
122 Solving Algorithm Problems with SQL ⑫ (Least Common Multiple)
123 Solving Algorithm Problems with SQL ⑬ (Pythagorean Theorem)
124 Solving Algorithm Problems with SQL ⑭ (Monte Carlo Algorithm)
125 Solving Algorithm Problems with SQL ⑮ (Finding Euler Constant and Natural Constant)

PART 4 〈Utilization〉 Building SQL Applications
126 How to load excel data into DB
127 What is the most common word in Steve Jobs’ speeches?
128 Are there more positive or negative words in Steve Jobs’ speeches?
129 What day of the week does theft occur the most?
130 Which college has the highest tuition fees in your country?
131 What are the most expensive items and prices in Seoul?
132 Where do most murders occur?
133 What is the biggest type of crime resulting from domestic conflict?
134 What is the biggest cause of arson?
135 Where in the country does the most traffic accidents occur?
136 What year had the most chicken restaurant closures?
137 Which country has the longest working hours in the world?
138 What are the most common cancers in men and women?

You can learn SQL by mastering the following 4-step skills. This subject is the first of these.