Practice advanced SQL queries with MySQL 5.7+ (2016)

Build on your current knowledge and get to a next level in SQL. Learn by writing - lots of examples are waiting for you.
4.7 (85 ratings)
Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
2,514 students enrolled
Instructed by Michal Juhas Development / Databases
$19
$50
62% off
Take This Course
  • Lectures 74
  • Length 5 hours
  • Skill Level Expert Level
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 5/2016 English

Course Description

I designed this course especially for those who already know the basics and wonder how to get to the next level. Maybe you’re a BI analyst, IT specialist, QA analyst, automation tester, DB admin, aspiring data scientist or just passionate to learn SQL. You’ll learn how to use analytic functions, how to take advantage of table partitioning, how to write user-defined functions, procedures with cursors and even how to write beautiful code. All of that with the focus on the most popular open-source database: MySQL.

In this course we’ll write the code & MySQL queries together. I prepared 2 test databases, 70+ lectures, 4+ hours of video and lots of examples and practices so that you can learn advanced SQL techniques by writing the code. You’ll not learn just by watching me and eating pop-corn. You need to write the queries and I’ll help you! I believe that’s the only way for you to really adopt this new knowledge.

At the end of this course you will not just know these advanced techniques, but also know how to use them in practice.

To help you out, I also created a Slack group where you can join and chat with me or my colleagues if you get stuck.

Anyways, you have nothing to lose. Try this course and if you can honestly say that you learnt nothing, just email Udemy and they will give you a 100% refund within 30 days.

  • Last course update: August 2016
  • Video quality: Very high (1080p HD, 1920x1080)

Enjoy & have fun with SQL & MySQL! :-)

What are the requirements?

  • Your genuine interest in SQL is required. If you’re passionate about the data analysis, optimisation, advanced queries, it will be so much easier for me to get you to a next level!
  • Access to a MySQL database
  • Basic or intermediate SQL knowledge such as:
  • ...already can query data: SELECT COUNT, DISTINCT, LIMIT, ORDER BY, WHERE
  • ... already know conditions: BETWEEN, LIKE, IS NULL, IN, NOT IN
  • ... already know aggregation: GROUP BY, HAVING, MAX, MIN, AVG, SUM
  • ... already know joins: INNER JOIN, OUTER JOIN, CROSS JOIN
  • ... already know views: CREATE VIEW AS
  • ... already know data management: CREATE TABLE, INERT INTO, UPDATE, DELETE
  • ... already know sub-queries

What am I going to get from this course?

  • write user defined functions
  • write stored procedures
  • simulate some of the analytic (window) functions
  • work with cursors
  • use the right indexes and partitions
  • write advanced & beautiful queries

What is the target audience?

  • Those who know SQL basics and want to get to next level
  • Business intelligence (BI) analysts
  • BI developers
  • Data scientists
  • IT specialists
  • QA analysts & testers
  • Automation testers
  • DB administrators
  • NOT beginners (in this course you already need to have basic / intermediate SQL knowledge)

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: S01 Kick-off
05:36

Course kick-off:

  • instructor introduction
  • course structure & highlights

Attached resources:

  • Slides: S01-L01-slide.pdf
04:35

Learn how to get most out of your investment (time & money). As I prepared lots of SQL queries, practices and additional reading materials, I strongly recommend you go through the attached materials and practice, practice, practice!

Attached resources:

  • Slides: S01-L02-slide.pdf
03:30

We'll take a look at the tools you should have installed at your localhost.

For Mac I use & recommend:

  • MySQL version 5.7+
  • Postgres version 9.5+

For Windows & Linux you can use any other tools, there's plenty of them. The tool itself is not important for this course as long as you can write & execute queries in it.

Attached resources:

  • Slides: S01-L03-slide.pdf (306.7 kB)
  • SQLs: S01-L03-Environment.pdf (19.9 kB)
05:24

I prepared for this course 2 test databases:

  1. sample_staff
  2. sample_ip

In this lecture we'll take a deeper look at where to get the database and the tables in both databases.

Attached resources:

  • Slides: S01-L04-slide.pdf
  • SQLs: S01-L04-Sample-database.pdf
03:18

Take the same quiz before and after this course and compare how you've progressed and what you've learned.

Attached resources:

  • Slides: S01-L05-slide.pdf
03:32

Before you proceed to next sessions & lessons, please make sure you tick all the prerequisites. In this quick lecture we'll take a look at all the prerequisites and hopefully will tick all the boxes :-)

Attached resources:

  • Slides: S01-L06-slide.pdf
  • SQLs: S01-L06-Checklist.pdf
Section 2: S02: Coding and style conventions
06:08

An easy kick-off of the "real" lectures :-) In this one we'll take a look at the difference between coding style and naming convention. We'll take a look at some really ugly and also beautiful SQL code.

Attached resources:

  • Slides: S02-L01-slide.pdf
12:48

I'll walk you through several best practices which will make you a master in writing beautiful queries.

This lecture is divided into 2 parts, because Udemy has a limit 20 minutes per one video.

  • Use inline comments
  • Use tabs instead of spaces
  • Use 1=1
  • Split column names to new lines

Attached resources:

  • Slides: S02-L02-slide.pdf
  • SQLs: S02-L02-Beautiful-query.pd
07:50

I'll walk you through several best practices which will make you a master in writing beautiful queries.

This lecture is divided into 2 parts, because Udemy has a limit 20 minutes per one video.

  • Add empty lines for complex queries
  • Do not shorten table name aliases
  • Use the backtick (`) before and after table & column names

Attached resources:

  • Slides: S02-L02-slide.pdf
  • SQLs: S02-L02-Beautiful-query.pd
18:30

We'll go through my personal best practices in writing SQL code which I adopted in the last years. The video with ~20 best practices is divided into 3 parts because of Udemy's limit of 20 mins / video.

  • Store code in a git repository
  • Do not use `select *`
  • Separate attributes (columns) to rows
  • Set naming convention and don’t allow exceptions

Additional resources:

  • Slides: S02-L03-Best-practices.pdf
  • SQL: S02-L03-slide.pdf
18:44

We'll go through my personal best practices in writing SQL code which I adopted in the last years. The video with ~20 best practices is divided into 3 parts because of Udemy's limit of 20 mins / video.

  • Be descriptive, don’t use acronyms
  • Use audit columns
  • Batch delete & updates (example)
  • Reference the owner of the object
  • Table names always singular
  • WHERE 1=1 (and / or)

Additional resources:

  • Slides: S02-L03-Best-practices.pdf
  • SQL: S02-L03-slide.pdf
13:00

We'll go through my personal best practices in writing SQL code which I adopted in the last years. The video with ~20 best practices is divided into 3 parts because of Udemy's limit of 20 mins / video.

  • Old vs. new JOIN style
  • Prefix database objects
  • Don’t use column row number in ORDER BY
  • Use LIMIT 1 as much as possible
  • Use correct data type, it makes a difference

Additional resources:

  • Slides: S02-L03-Best-practices.pdf
  • SQL: S02-L03-slide.pdf
Article

This is your chance to shine! I prepared an assignment for you to train new skills, so let's roll up the sleeves and get to work.

Attached resources:

  • SQLs: 
Article

This is your chance to shine! I prepared an assignment for you to train new skills, so let's roll up the sleeves and get to work.

Attached resources:

  • SQLs: S02-P02-Practice.pdf
Article

This is your chance to shine! I prepared an assignment for you to train new skills, so let's roll up the sleeves and get to work.

Section 3: S03 Indexes
12:15

Introduction to indexes and a demonstration why it's good to use them - how they can impact query performance with 2-3 mil. rows in a sample table.

Attached resources:

  • Slides: S03-L01-slide.pdf
11:05

We'll create a unique index in one of the tables in sample database. Then we'll play with INSERT ... ON DUPLICATE KEY UPDATE syntax - there's a nice example waiting for you!

Attached resources:

  • Slides: S03-L02-slide.pdf
  • SQLs: S03-L02-Unique-index.pdf
12:22

We'll be looking at a table with a composite multi-column index consisting of 4 columns and we'll analyze the execution plan based on the different where conditions in the query.

Attached resources:

  • Slides: S03-L03-slide.pdf
  • SQLs: S03-L03-Composite-index.pdf
07:50

We'll go through a few examples to demonstrate the difference in performance between 2 different partial indexes vs. a regular (non-partial) index.

Attached resources:

  • Slides: S03-L04-slide.pdf
  • SQLs: S03-L04-Partial-index.pdf
07:03

We'll take a look at 3 index hints, examples how to use them and how they affect query performance:

  • USE INDEX
  • FORCE INDEX
  • IGNORE INDEX

Attached resources:

  • Slides: S03-L05-slide.pdf
  • SQLs: S03-L05-Use-force-index.pdf
03:47

This is your chance to shine! I prepared an assignment for you to train new skills, so let's roll up the sleeves and get to work.

Attached resources:

  • Slides: S03-L06-slide.pdf
  • SQLs: S03-L06-Functions-indexes.pdf
05:51

This is your chance to shine! I prepared an assignment for you to train new skills, so let's roll up the sleeves and get to work.

Attached resources:

  • Slides: S03-L07-slide.pdf
  • SQLs: S03-L07-Using-two-indexes.pdf
Article

This is your chance to shine! I prepared an assignment for you to train new skills, so let's roll up the sleeves and get to work.

Attached resources:

  • SQLs: S03-P01-Practice.pdf
Article

This is your chance to shine! I prepared an assignment for you to train new skills, so let's roll up the sleeves and get to work.

Attached resources:

  • SQLs: S03-P02-Practice.pdf
Article

Additional reading from MySQL 5.7 reference manual (PDF attached).

Article

Additional reading from MySQL 5.7 reference manual (PDF attached).

Article

Additional reading from MySQL 5.7 reference manual (PDF attached).

Article

Additional reading from MySQL 5.7 reference manual (PDF attached).

Section 4: S04 Partitions
09:04

Introduction to partitions, why it's good to use them and overview of the main partition types:

  • PARTITION BY RANGE
  • PARTITION BY LIST
  • PARTITION BY HASH
  • PARTITION BY KEY

Attached resources:

  • Slides: S04-L01-slide.pdf
  • SQLs: S04-L01-Intro.pdf
13:19

We'll go through a few examples about how to work with partitions. We'll partition the sample_staff.invoice table by different partitions (first by RANGE, then by LIST).

Attached resources:

  • Slides: S04-L02-slide.pdf
  • SQLs: S04-L02-How-to.pdf
Article

This is your chance to shine! I prepared an assignment for you to train new skills, so let's roll up the sleeves and get to work.

Attached resources:

  • SQLs: S04-P01-Practice.pdf
Article

Additional reading from MySQL 5.7 reference manual (PDF attached).

Article

Additional reading from MySQL 5.7 reference manual (PDF attached).

Article

Additional reading from MySQL 5.7 reference manual (PDF attached).

Article

Additional reading (PDF attached).

Section 5: S05 Variables
05:58

Introduction to variables & their types: 

  • global variables
  • user-defined session variables
  • local (declared in a function or procedure)

Attached resources:

  • Slides: S05-L01-slide.pdf
  • SQLs: S05-L01-Intro.pdf
18:27

We'll focus on the session variables and will write a few queries to demonstrate what are they good for.

Attached resources:

  • Slides: S05-L02-slide.pdf
  • SQLs: S05-L02-Session-variable.pdf
Article

This is your chance to shine! I prepared an assignment for you to train new skills, so let's roll up the sleeves and get to work.

Attached resources:

  • SQLs: S05-P01-Practice.pdf
Article

Additional reading from MySQL 5.7 reference manual (PDF attached).

Article

Additional reading from MySQL 5.7 reference manual (PDF attached).

Article

Additional reading (PDF attached).

Section 6: S06 Analytic functions
03:48

Introduction to the analytic (also called window) functions. I'll show you the regular usage on Postgres and we'll dive into details in the following lectures.

Attached resources:

  • Slides: S06-L01-slide.pdf
  • SQLs: S06-L01-Intro.pdf
13:17

Let's look at how can we simulate a window function ROW_NUMBER() which doesn't exist in MySQL.

Article

This is your chance to shine! I prepared an assignment for you to train new skills, so let's roll up the sleeves and get to work.

Article

Additional reading (PDF attached).

Article

Additional reading (PDF attached).

Article

Additional reading (PDF attached).

Article

Additional reading (PDF attached).

Section 7: S07 User-defined functions
04:52

Introduction to the user-defined stored functions. A simple example will demonstrate what are functions good for.

Attached resources:

  • Slides: S07-L01-slide.pdf
  • SQLs: S07-L01-Intro.pdf
08:55

We'll take a look at several best practices related to functions (& procedures). 

Attached resources:

  • Slides: S07-L02-slide.pdf
  • SQLs: S07-L02-Coding-standards-functions-procedures.pdf
Article

This is your chance to shine! I prepared an assignment for you to train new skills, so let's roll up the sleeves and get to work.

Attached resources:

  • SQLs: S07-P01-Practice.pdf
Article

Additional reading from MySQL 5.7 reference manual (PDF attached).

Article

Additional reading from MySQL 5.7 reference manual (PDF attached).

Article

Additional reading from MySQL 5.7 reference manual (PDF attached).

Article

Additional reading from MySQL 5.7 reference manual (PDF attached).

Section 8: S08 Stored procedures
07:48

Introduction to the stored procedures. We'll look at the difference between:

  • functions
  • procedures
  • views

Additional resources:

  • Slides: S08-L01-slide.pdf
  • SQLs: S08-L01-Intro.pdf
11:07

We'll create two different procedures: one to insert data, the other to select data which we inserted.

Attached resources:

  • SQLs: S08-L02-How-to-write.pdf
08:48

We'll create new events in the Event scheduler, a new table event_logger and a procedure to save data at defined interval.

Attached resources:

  • Slides: S08-L03-slide.pdf
  • SQLs: S08-L03-Events.pdf
Article

This is your chance to shine! I prepared an assignment for you to train new skills, so let's roll up the sleeves and get to work.

Article

This is your chance to shine! I prepared an assignment for you to train new skills, so let's roll up the sleeves and get to work.

S08-P03 Coding practice
Preview
Article
Article

Additional reading from MySQL 5.7 reference manual (PDF attached).

Article

Additional reading from MySQL 5.7 reference manual (PDF attached).

Article

Additional reading from MySQL 5.7 reference manual (PDF attached).

Section 9: S09 Cursors
07:55

Introduction to cursors. What are they good for and how to write them? 

Attached resources:

  • Slides: S09-L01-slide.pdf
  • SQLs: S09-L01-Intro.pdf
09:26

We'll dive deeper into procedures and will define a new cursor in a stored procedure.

Attached resources:

  • SQLs: S09-L02-Cursor.pdf
Article

This is your chance to shine! I prepared an assignment for you to train new skills, so let's roll up the sleeves and get to work.

Attached resources:

  • SQLs: S09-P01-Practice.pdf
Article

Additional reading from MySQL 5.7 reference manual (PDF attached).

Article

Additional reading from MySQL 5.7 reference manual (PDF attached).

Article

Additional reading from MySQL 5.7 reference manual (PDF attached).

Article

Additional reading from MySQL 5.7 reference manual (PDF attached).

Article

Additional reading from MySQL 5.7 reference manual (PDF attached).

Section 10: Next steps
02:49

Take the same quiz before and after this course and compare how you've progressed and what you've learned.

02:52

Let's take a look at what should you do next. There's a few resources included in the next steps for you to follow.

Please review the course here on Udemy - it will help others decide if they should enroll to this course or not.

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Michal Juhas, CTO & co-founder @ HotelQuickly

As a CTO I'm responsible for our product & engineering, technical infrastructure and databases - we use MySQL & PostgreSQL. Prior to joining HotelQuickly I spent 4 years working as a data warehouse consultant, developing and optimising data marts running on Oracle

I'm passionate about databases, ETL tools, business intelligence (BI) solutions and new trends in software development.

Here on Udemy I run a course "Practice advanced SQL queries with MySQL 5.7+"

See more on my Linkedin profile.

Ready to start learning?
Take This Course