WHERE

Challenge: WHERE

Solution: WHERE

WHERE operators

Challenge: WHERE operators

Solution: WHERE operators

WHERE with AND/OR

Challenge: WHERE with AND/OR

Solution: WHERE with AND/OR

Quick note on time zones

BETWEEN

Challenge: BETWEEN

Solution: BETWEEN

IN

IN operator

Challenge: IN

Solution: IN

LIKE

Challenge: LIKE

Solution: LIKE

Comments & Aliases

Today's summary

Today's challenges

Introducing Role Plays

SQL Job Interview – Filtering Logic & Query Thinking

Today's plan

Aggregate functions

Challenge: Aggregate function

GROUP BY

Challenge: GROUP BY

Solution: GROUP BY

GROUP BY multiple columns

Challenge: GROUP BY multiple columns

Solution: GROUP BY multiple columns

HAVING

Challenge: HAVING

Solution: HAVING

Introducing the second project

Note for Mac users

Important: Problems & solutions of setting up the database

Today's summary

Today's plan

LENGTH, LOWER & UPPER

Challenge: LENGTH, LOWER & UPPER

Solution: LENGTH, LOWER & UPPER

LEFT & RIGHT

Challenge: LEFT & RIGHT

Solution: LEFT & RIGHT

Concatenate

Challenge: Concatenate

Solution: Concatenate

POSITION

Challenge: POSITION

Solution: POSITION

SUBSTRING

Challenge: SUBSTRING

Solution: SUBSTRING

EXTRACT

Challenge: EXTRACT

Solution: EXTRACT

TO_CHAR

Challenge: TO_CHAR

Solution: TO_CHAR

Intervals & Timestamps

Challenge: Intervals & Timestamps

Solution: Intervals & Timestamps

Today's slides

Mathematical functions and operators

Challenge: Mathematical functions and operators

Solution: Mathematical functions and operators

CASE WHEN - Theory

CASE WHEN - Hands-on

CASE WHEN

3 Challenge: CASE WHEN

Solutions

Solution: CASE WHEN

CASE WHEN & SUM

COALESCE

CAST

Challenge: CAST & COALESCE

Solution: CAST & COALESCE

REPLACE

Today's slides

What are joins?

INNER JOIN - Theory

INNER JOIN - Practice

FULL OUTER JOIN

JOIN & WHERE

LEFT OUTER JOIN

Challenge: LEFT OUTER JOIN

Solution: LEFT OUTER JOIN

RIGHT OUTER JOIN

Challenge: Joins

Solution: Joins

Joins on multiple conditions

Solution: Joins on multiple conditions

Joining multiple tables

Challenge: INNER JOIN

Solution: INNER JOIN

Joining multiple tables - Practice

Challenge: Joining multiple tables

Solution: Joining multiple tables

More challenges

Solutions

Today's slides

UNION

UNION - Practice

Subqueries in WHERE

Challenge: Subqueries in WHERE

Solution: Subqueries in WHERE

More challenges: Subqueries in WHERE

Solution: More challenges

Subqueries in FROM

Challenge: Subqueries in FROM

Solution: Subqueries in FROM

Subqueries in SELECT

Challenge: Subqueries in SELECT

Solution: Subqueries in SELECT

Correlated Subqueries in WHERE

Challenge: Correlated subqueries in WHERE

Solution: Correlated subqueries in WHERE

Correlated Subquery in SELECT

More challenges: Correlated subqueres

Today's slides

Project Introduction

Challenges

Solutions

Solution 1

Solution 2

Solution 3

Solution 4

Solution 5

Solution 6

Solution 7

Solution 8

Solution 9

Solution 10

Solution 11

Solution 12

Solution 13

Solution 14

Managing databases & tables

CREATE DATABASE

CREATE & DROP DATABASE

Data Types

Constraints

Primary Key & Foreign Key

Primary & Foreign Key

CREATE TABLE

INSERT

ALTER TABLE

Challenge: ALTER TABLE

Solution: ALTER TABLE

TRUNCATE & DROP TABLE

CHECK

Challenge: CHECK

Solution: CHECK

Today's slides

UPDATE

Challenge: UPDATE

Solution: UPDATE

DELETE

Challenge: DELETE

Solution: DELETE

CREATE TABLE AS

Challenge: CREATE TABLE AS

Solution: CREATE TABLE AS

CREATE VIEW

Challenge: CREATE VIEW

Solution: CREATE VIEW

CREATE MATERIALIZED VIEW

Managing views

Challenge: Managing views

Solution: Managing views

Import & Export

Today's slides

OVER() with PARTITION BY

Challenge: OVER() with PARTITION BY

Solution: OVER() with PARTITION BY

OVER() with ORDER BY

Challenge: OVER() with ORDER BY

Solution: OVER() with ORDER BY

RANK()

Challenge: RANK()

Solution: RANK()

FIRST_VALUE()

LEAD & LAG

Challenge: LEAD & LAG

Solution: LEAD & LAG

Today's slides

GROUPING SETS

Challenge: GROUPING SETS

Solution: GROUPING SETS

Additional challenge

Solution: Additional Challenge

ROLLUP

Challenge: ROLLUP

Solution: ROLLUP

CUBE

Challenge: CUBE

Solution: CUBE

Self joins

Challenge: Self joins

Solution: Self joins

CROSS JOIN

NATURAL JOIN

Today's slides

Project introduction

The challenges

Solutions

User-defined functions

User-defined functions - Example

Challenge: User-defined functions

Solution

Transactions

Challenge: Transactions

Solution: Transactions

Rollbacks

Stored procedures

Challenge: Stored procedures

Solution: Stored procedure

Today's slides

User management

CREATE USER | ROLE

GRANT & REVOKE privileges

Privileges - Hands-on

Challenge: Privileges

Solution: Privileges

What are indexes?

B-Tree index

Bitmap index

Indexes - Guidelines

Creating indexes

Execution plan & query performance

Today's slides

Why an extra day?

Common Table Expressions (CTEs) - First Example

Common Table Expressions - More Complex Example

Challenge 1: Create a simple CTE

Solution 1: Create a simple CTE

Common Table Expressions - Using Multiple CTEs

Challenge 2: Using Multiple CTEs

Solution 2: Using Multiple CTEs

Recursive CTEs - Concept

Recursive CTEs - Hierarchical Data

Recursive CTEs - Termination Condition

Challenge 3: Recursive CTE

Solution 3: Recursive CTE

Bonus