SQL The Complete Developers Guide MySQL PostgreSQL

SQL The Complete Developers Guide MySQL PostgreSQL

What you’ll learn

Understand the core SQL syntax and concepts
Be able to install MySQL & PostgreSQL + different clients
Perform SQL CRUD operations
Query and filter (simple and complex) data
Normalize data and split data across multiple tables
Merge data and perform data aggregation operations

Requirements

NO prior SQL or database knowledge is required!
NO programming experienced is required!

Description

SQL is THE most important query language you can learn!It’s used by many popular relational database management systems like MySQL or PostgreSQL. But it’s also used by data analysis and big data frameworks and tools like Apache Spark.Hence knowing SQL opens up an endless amount of opportunities and jobs – no matter if you’re going to work with relational databases or if you’re becoming a data scientist, knowing SQL will be key!This course teaches you SQL from the ground up and in extremely high detail! In addition, this course comes with two main paths that you can take: The “Complete Course” path where you go through the entire course, step by step, learning about ALL the key features and concepts you have to knowThe “Quick Summary” path where you will learn the key SQL essentials within a few hoursBoth paths are included in this course (i.e. with one single purchase) and you can switch between them or take both – simply as it makes most sense for you! Just dive into the free first course section to learn all about this course and these two paths (and how to take them).SQL is a standardized language and therefore learning SQL will help you apply it in all kinds of contexts. Nonetheless, different database systems also support different aspects of SQL or bring their own variations of the SQL language. Therefore, this course dives into SQL by exploring all key features at the example of the two most popular database systems: MySQL and PostgreSQL. All query and command examples are shown for both database systems, ensuring that you feel comfortable working with SQL in either environment!This course will enable you to write your own (simple or complex) SQL queries, create basic or advanced databases and table structures and work with data of different shape and complexity!In detail, you will learn:What exactly SQL is and how the core syntax looks likeHow to write SQL commandsHow to install MySQL & PostgreSQL as well as various clientsHow to create and structure database tablesWhich data types you may use and when to use which data typeHow to perform CRUD operations: Create, Read, Update & Delete DataHow to insert data into tablesHow to query and filter dataWhy you should split data across multiple tables (and how to do that)How to join (merge) data into combined result setsHow to write basic and more complex queriesHow to aggregate and group dataHow to use built-in database functions to work with numbers, text or datesHow to optimize databases with indexesAnd much, much more!Explore the full course curriculum to get a thorough overview of the course content and watch the free preview section to learn how this course will help you!This course also comes with a 30 day refund period, so that you can try it risk-free :)We’d love to welcome you on board of this course and explore SQL together with you!

Overview

Section 1: Getting Started

Lecture 1 Welcome To The Course!

Lecture 2 What Is SQL?

Lecture 3 A Closer Look At SQL & Databases

Lecture 4 Join Our Learning Community!

Lecture 5 Course Content

Lecture 6 There Are Two Ways Of Taking This Course!

Lecture 7 How To Get The Most Out Of This Course

Lecture 8 Course Code & Resources

Section 2: SQL Basics: Syntax & Core Features

Lecture 9 Module Introduction

Lecture 10 Understanding The Core SQL Syntax

Lecture 11 SQL in Action

Lecture 12 The Core SQL Rules

Lecture 13 Data Definition vs Data Manipulation

Section 3: Course Setup: Installing MySQL & Postgresql

Lecture 14 Module Introduction

Lecture 15 Installing Different Database Management Systems

Lecture 16 What We Need To Install

Lecture 17 You Can Skip The Next Lectures!

Lecture 18 MySQL Setup Overview & macOS Setup

Lecture 19 MySQL Windows Setup

Lecture 20 Postgresql Setup Overview & macOS Setup

Lecture 21 Postgresql Windows Setup

Lecture 22 Starting & Stopping Servers + Uninstalling Database Systems

Lecture 23 Connecting To Database Servers (MySQL & Postgresql) With Various Clients

Lecture 24 Setting Up Visual Studio Code With The SQLTools Extension (Course Environment)

Lecture 25 Configuring VS Code & Exploring SQLTools

Lecture 26 More On VS Code & SQLTools

Section 4: Data Definition: Managing Tables & Working With Data Types

Lecture 27 Module Introduction

Lecture 28 Key Terms When Working With Data Definition Statements & Commands

Lecture 29 Introducing The Course Section Example

Lecture 30 Introducing Key Data Definition Clauses

Lecture 31 Creating a new Database (CREATE DATABASE)

Lecture 32 Deleting Databases & IF NOT EXISTS

Lecture 33 The Importance Of Data Types (Value Types)

Lecture 34 Introducing Key Text Value Types

Lecture 35 Introducing Numeric Values, Date Types & More

Lecture 36 An Overview Of Numeric Value Types

Lecture 37 How Do You Store Files?

Lecture 38 Getting Started With The CREATE TABLE Statement & Syntax

Lecture 39 Creating a First Text Column

Lecture 40 Creating a Numeric Value Column

Lecture 41 Working with Enums & Finishing Table Creation

Lecture 42 Inserting Data Into The Created Table (INSERT INTO)

Lecture 43 Inserting & Querying More Data

Lecture 44 Working With Fixed Point & Floating Point Numbers

Lecture 45 Adding Boolean Value Types

Lecture 46 Creating a new Table with Text & Timestamp Data

Lecture 47 Inserting Data Into The New Tables

Lecture 48 What About Time Zones?

Lecture 49 Introducing Default Column Values

Lecture 50 Deleting (Dropping) Tables & Inserting Data With Default Values

Lecture 51 Updating Tables & Columns

Lecture 52 Dealing With “No Data” (NULL) vs “0”

Lecture 53 NULL Values, Inserting (No) Data & Default Values

Lecture 54 Exploring the NOT NULL Constraint

Lecture 55 Exploring the CHECK Constraint

Lecture 56 We Need Unique Values & Identifiers!

Lecture 57 SERIAL in MySQL vs SERIAL in Postgresql

Lecture 58 Working With Auto Incrementing IDs & Primary Keys

Lecture 59 Auto-IDs, Primary Keys & Inserting Data

Lecture 60 Constraints – Summary

Lecture 61 Understanding Text Encoding & Collation

Lecture 62 Temporary Tables & Tables From Other Tables

Lecture 63 Creating Generated Columns

Lecture 64 Module Summary

Lecture 65 Time To Practice: Problem

Lecture 66 Time To Practice: Solution (1/2)

Lecture 67 Time To Practice: Solution (2/2)

Section 5: Data Manipulation & Querying: CRUD Operations With SQL

Lecture 68 Module Introduction

Lecture 69 What Are CRUD Operations?

Lecture 70 Inserting Data: Theory

Lecture 71 Selecting Data: Theory

Lecture 72 Updating Data: Theory

Lecture 73 Deleting Data: Theory

Lecture 74 Introducing The Section Example

Lecture 75 Setting Up An Example Database & Table

Lecture 76 Example: Inserting Data

Lecture 77 Inserting More (Dummy) Data

Lecture 78 Updating In Action

Lecture 79 Deleting In Action

Lecture 80 Selecting Data – The Basics

Lecture 81 SELECT, Column Names & Data Expressions

Lecture 82 Filtering with WHERE: Available Comparison Operators & Variations

Lecture 83 Filtering In Action (WHERE In Action)

Lecture 84 Combining Comparisons With AND & OR

Lecture 85 Greater, Smaller & Ranges

Lecture 86 Filtering Text Values

Lecture 87 Working With Dates & Date Differences

Lecture 88 Filtering Conditions Without Hard-Coded Values

Lecture 89 Introducing ORDER BY & LIMIT

Lecture 90 Ordering & Limiting Results In Action

Lecture 91 Looking For DISTINCT Values

Lecture 92 Subqueries & Views

Lecture 93 Module Summary

Section 6: Data Normalization & Relationships: Splitting & JOINing Data

Lecture 94 Module Introduction

Lecture 95 A First Look At Related Data

Lecture 96 Data Normalization – First Steps

Lecture 97 Splitting Data Into Tables

Lecture 98 Forms Of Data Normalization

Lecture 99 Our First Section Example

Lecture 100 Creating Tables With Relations

Lecture 101 Inserting Related Data

Lecture 102 Joining Data & Introducing INNER JOIN

Lecture 103 Using INNER JOIN

Lecture 104 Combining Multiple JOINs

Lecture 105 Data Joining & Filtering

Lecture 106 Introducing LEFT JOIN

Lecture 107 Using LEFT JOIN

Lecture 108 Combining Multiple LEFT JOINs

Lecture 109 What About RIGHT JOIN?

Lecture 110 Example Time & INNER JOIN vs LEFT JOIN

Lecture 111 Introducing CROSS JOIN

Lecture 112 UNION & Why It’s Different

Lecture 113 What’s Wrong With Our Foreign Keys?

Lecture 114 Introducing Foreign Key Constraints

Lecture 115 Diving Deeper Into Foreign Key Constraints

Lecture 116 Updating & Removing Foreign Key Constraints

Lecture 117 Foreign Key Constraints In Action

Lecture 118 REFERENCES & MySQL

Lecture 119 Data Relationship Types: One-to-Many, Many-to-Many, One-to-ONe

Lecture 120 A Bigger Example

Lecture 121 Example: Adding First Tables & Relations

Lecture 122 Example: Adding More Tables & Data

Lecture 123 Many-to-Many Relationships & Intermediate Tables (“Linking Tables”)

Lecture 124 Querying The Example Data

Lecture 125 Practicing JOINs With Filtering

Lecture 126 Experimenting With Referential Integrity

Section 7: More On Primary & Foreign Keys

Lecture 127 Module Introduction

Lecture 128 Remember: Primary Keys Don’t Have To Be Auto-Incrementing IDs!

Lecture 129 Primary Keys: When To Use Which Column

Lecture 130 Introducing Composite Primary Keys

Lecture 131 Defining Composite Primary Keys (Composite Keys In Action)

Lecture 132 Composite Foreign Keys

Lecture 133 Composite Keys In Action

Lecture 134 Self-Referencing Relationships (Self-Referential Relationships)

Lecture 135 Self-Referencing Many To Many Relationships

Section 8: Grouping & Aggregate Functions

Lecture 136 Module Introduction

Lecture 137 The Module Project

Lecture 138 What are Aggregate Functions – Theory

Lecture 139 Understanding COUNT()

Lecture 140 Working with MIN() & MAX() and Adding Aliases to Aggregate Functions

Lecture 141 Using SUM(), AVG() & ROUND()

Lecture 142 Working with Filters & Joining Tables

Lecture 143 Theory Time – Understanding GROUP BY

Lecture 144 Applying GROUP BY in Practice

Lecture 145 GROUP BY & Joined Queries

Lecture 146 Understanding WHERE vs HAVING

Lecture 147 Applying HAVING in Practice

Lecture 148 Working with Nested Subqueries

Lecture 149 Introducing Window Functions

Lecture 150 Understanding ORDER BY and RANK()

Section 9: Built-in Query Functions & Operators

Lecture 151 Module Introduction

Lecture 152 The Module Project

Lecture 153 Working with Mathematical Functions & Arithmetic Operators

Lecture 154 Understanding String Functions with SELECT

Lecture 155 Using String Functions with INSERT

Lecture 156 Understanding Date / Time Functions

Lecture 157 Working with Weekdays

Lecture 158 Calculating INTERVALS

Lecture 159 Adding INTERVALS to Dates

Lecture 160 Understanding LIKE & Pattern Matching

Lecture 161 Understanding EXISTS

Lecture 162 Working with Subquery Expressions and EXISTS

Lecture 163 Working with Subquery Expressions and IN

Lecture 164 Introducing Conditional Expressions

Lecture 165 Challenge Solution

Lecture 166 Database (SQL) vs Application Level

Section 10: Database Transactions

Lecture 167 Module Introduction

Lecture 168 Understanding Transactions

Lecture 169 A Note About This Module’s Project

Lecture 170 Applying Transactions & ROLLBACK

Lecture 171 Committing Changes

Lecture 172 Working with Savepoints

Lecture 173 Transactions in PostgreSQL

Section 11: Using Indexes

Lecture 174 Module Introduction

Lecture 175 What Are Indexes? And Why Would You Need Them?

Lecture 176 Don’t Use Too Many Indexes!

Lecture 177 Index Types: An Overview

Lecture 178 Setting Up Some Example Data

Lecture 179 EXPLAINing Queries & Query Planning

Lecture 180 Creating & Using Single-Column Indexes

Lecture 181 More on Creating & Dropping Indexes

Lecture 182 Unique Indexes

Lecture 183 Working With Multi-Column Indexes (Composite Indexes)

Lecture 184 Partial Indexes

Section 12: Summary & Practice: A Complete Example

Lecture 185 Module Introduction

Lecture 186 What Is SQL?

Lecture 187 A Closer Look At Databases In General

Lecture 188 The Core SQL Syntax

Lecture 189 Course Setup (For Following Along)

Lecture 190 Analyzing The Section Example Project

Lecture 191 Introducing “Data Normalization”

Lecture 192 Planning Tables & Relationships

Lecture 193 Creating A New Database

Lecture 194 Choosing Identifier Names

Lecture 195 Defining Columns

Lecture 196 Exploring Important Data Types

Lecture 197 How To Store Files & Adding More Data Types

Lecture 198 Constraints: An Introduction

Lecture 199 Introducing Database Functions

Lecture 200 Understanding Primary Keys & Unique IDs

Lecture 201 INSERTing Data

Lecture 202 Basic Data Querying (via SELECT)

Lecture 203 UPDATE & DELETE In Action

Lecture 204 Filtering (WHERE) & Sorting (ORDER BY)

Lecture 205 Adding More Tables

Lecture 206 Relations & Foreign Keys

Lecture 207 Understanding ON DELETE & ON UPDATE

Lecture 208 Adding More Tables & Relationships

Lecture 209 Many To Many Relationships & Linking (Intermediate) Tables

Lecture 210 Inserting Related Data

Lecture 211 Connecting Data With INNER JOIN

Lecture 212 Combining Multiple Joins

Lecture 213 Introducing LEFT JOIN

Lecture 214 Joining Data & Filtering

Lecture 215 Filtering Text With LIKE

Lecture 216 Introducing Aggregate Functions

Lecture 217 Grouping Aggregate Results (GROUP BY)

Lecture 218 The HAVING Clause (vs WHERE)

Lecture 219 Module Summary

Section 13: Course Roundup

Lecture 220 Course Roundup

Beginner SQL developers or data scientists,(Web) developers who want to incorporate SQL into their applications,Data analysts and scientists

Course Information:

Udemy | English | 19h 42m | 7.17 GB
Created by: Academind by Maximilian Schwarzmüller

You Can See More Courses in the Developer >> Greetings from CourseDown.com