The Complete Sql And Mysql Course - From Beginner To Expert

Learn the Basics of Managing Data with SQL - Basics, Transactions, Views, Pages and Stored Procedures

What you'll learn
understand the basic concepts of databases
get a good grasp of SQL and MySQL queries
understand database management systems (DBMSs) and database languages
understand table manipulation
understand pages
understand views
understand strored procedures
understand the data structures (B trees and B+ trees) behind databases
Requirements
Internet connection
Description
In this course you can learn the fundamental basics of data management with SQL and MySQL. Learn everything that you will need to make queries with the most popular data manipulating programming language - SQL. This course is about SQL commands in the main. You will learn the basics of queries. Let's take a look at what you are going to learn in this course:Section 1 - Setting up The Environment:setting up the environmentinstalling MySQL server on Windows and MacOSSection 2 - Database Theorywhat are databases?what are database management systems (DBMSs)?what are database languages?the data structures (B-trees) behind databasesprocedural and non-procedural programming languagesSection 3 - Creating and Manipulating Tablesbasic data types (integers, floating point numbers and characters)enums and dateshow to create databases?how to insert and remove items?how to update existing items or database tables?Section 4 - Basic SQLbasic SQL and MySQL commandsselect, where and wildcardslogical operatorsORDER BY and GROUP BY commandsSection 5 - Multiple Tablesjoining multiple database tablesinner joinleft and right joinSection 6 - Database Normalizationwhat is database normalisation?first normal form (NF1)second normal form (NF2)third normal form (NF3)Section 7 - Subqueries:advanced SQL and MySQLwhat are subqueries?correlated and non-correlated subqueriesSection 8 - Transactionswhat is database locking?the ACID principles?COMMIT, ROLLBACK and SAVEPOINTSection 9 - Viewswhat are views and why are they crucial?creating viewsupdating viewsdropping viewsSection 10 - Indexes and Constraintswhat are indexes and why to use them?primary keys and foreign keys revisitedwhat are constraints?Section 11 - Data Structures Behind Databaseswhat are pages?the basic concept of paging in operating systemsexternal memory and internal memoryB tree data structureB+ tree data structureSection 12 - Stored Procedureswhat are stored procedures?variablesloopsconditional statementsLearning the fundamentals of SQL and MySQL is a good choice and puts a powerful tool at your fingertips. SQL is easy to learn as well as it has excellent documentations. The most important fact about SQL that it is used in almost every field from software engineering to financial analysis so understanding the concepts and basic language features can prove to be very marketable.Thanks for joining the course, let's get started!

Overview

Section 1: Introduction

Lecture 1 Introduction

Section 2: Environment

Lecture 2 Installing SQL and MySQL on Windows

Lecture 3 Installing SQL and MySQL on Mac

Section 3: Databases Theory

Lecture 4 What are databases and database management systems (DBMS)?

Lecture 5 The relational model

Lecture 6 Data structures behind database servers

Lecture 7 Procedural and non-procedural languages

Lecture 8 Types of DBMS and database languages

Section 4: Table Manipulation

Lecture 9 Data types

Lecture 10 Creating databases

Lecture 11 CODE - creating databases

Lecture 12 Creating a table

Lecture 13 CODE - creating database tables

Lecture 14 Inserting into a table

Lecture 15 Inserting multiple items in one statement

Lecture 16 CODE - inserting into tables

Lecture 17 Delete row from a table

Lecture 18 CODE - delete row

Lecture 19 Updating a row

Lecture 20 CODE - updating rows

Lecture 21 Changing table structure

Lecture 22 CODE - change table structure

Lecture 23 Drop tables

Lecture 24 What are primary keys?

Lecture 25 The auto increment feature

Lecture 26 CODE - auto incremented primary keys

Lecture 27 Enums

Lecture 28 Booleans

Lecture 29 Floating point numbers with decimals

Lecture 30 Dates

Lecture 31 Exercise

Lecture 32 Solution

Lecture 33 Chapter summary & source code

Section 5: Basic SQL

Lecture 34 SQL dump - the city and country tables

Lecture 35 SELECT

Lecture 36 WHERE

Lecture 37 IN

Lecture 38 BETWEEN

Lecture 39 NOT - negation in SQL

Lecture 40 Logical operators

Lecture 41 Wildcards

Lecture 42 Functions

Lecture 43 ORDER BY

Lecture 44 Code - order by

Lecture 45 GROUP BY

Lecture 46 CODE - group by

Lecture 47 Exercise

Lecture 48 Solution

Lecture 49 HAVING

Lecture 50 Chapter summary & source code

Section 6: Multiple Tables

Lecture 51 What is a foreign key?

Lecture 52 Foreign key example

Lecture 53 CODE - foreign keys

Lecture 54 How to handle changes when dealing with multiple tables?

Lecture 55 Updating dependent tables example

Lecture 56 CODE - updating tables

Lecture 57 Exercise

Lecture 58 Solution

Section 7: Database Normalization

Lecture 59 What is database normalization?

Lecture 60 The first normal form

Lecture 61 The second normal form

Lecture 62 The third normal form

Section 8: Joining Multiple Tables

Lecture 63 Configuring the database tables - PERSON and UNIVERSITY

Lecture 64 Inner join introduction

Lecture 65 Inner join example

Lecture 66 CODE - inner join

Lecture 67 Left join introduction

Lecture 68 Left join example

Lecture 69 CODE - left join

Lecture 70 Right join introduction

Lecture 71 Right join example

Lecture 72 CODE - right join

Lecture 73 JOIN example - number of cities

Lecture 74 CODE - joining cities

Lecture 75 Exercise

Lecture 76 Solution

Lecture 77 Online SQL console

Lecture 78 JOIN example - customers and orders

Lecture 79 JOIN example - number of orders by customers

Lecture 80 JOIN example - number of orders by country

Lecture 81 Chapter summary & source code

Section 9: Advanced SQL - Subqueries

Lecture 82 What are subqueries?

Lecture 83 Non-correlated scalar subquery

Lecture 84 CODE - scalar subquery

Lecture 85 Non-correlated multiple-row subquery

Lecture 86 CODE - multiple row subquery

Lecture 87 Correlated subquery

Lecture 88 CODE - correlated subquery

Lecture 89 Subqueries and joins

Lecture 90 Transforming subquery into join

Lecture 91 CODE - subquery and join

Lecture 92 Chapter summary & source code

Section 10: Advanced SQL - Transactions

Lecture 93 Transactions introduction - locking

Lecture 94 Transactions introduction - transactions

Lecture 95 What are the ACID principles?

Lecture 96 Transaction example - COMMIT

Lecture 97 CODE - commit

Lecture 98 Transaction example - ROLLBACK

Lecture 99 CODE - rollback

Lecture 100 Transaction example - SAVEPOINT

Lecture 101 CODE - savepoint

Lecture 102 Chapter summary & source code

Section 11: Advanced SQL - Views

Lecture 103 What are views?

Lecture 104 Views example - initialize database

Lecture 105 Views example - create views

Lecture 106 CODE - creating views

Lecture 107 Views example - update views

Lecture 108 Views example - dropping views

Lecture 109 CODE - update and drop views

Lecture 110 Exercise

Lecture 111 Solution

Lecture 112 Chapter summary & source code

Section 12: Advanced SQL - Indexes And Constraints

Lecture 113 What are indexes?

Lecture 114 Indexes example

Lecture 115 Constraints introduction

Lecture 116 Primary keys

Lecture 117 Unique constraints

Lecture 118 Check constraints

Lecture 119 What is bitmap indexing?

Section 13: Data Structures Behind Databases

Lecture 120 External memory and paging

Lecture 121 B tree data structures

Lecture 122 Using B+ trees

Section 14: Stored Procedures

Lecture 123 What are stored procedures?

Lecture 124 Creating our first stored procedure

Lecture 125 CODE - first stored procedure

Lecture 126 Listing and removing stored procedures

Lecture 127 CODE - listing and removing stored procedures

Lecture 128 Variables

Lecture 129 Exercise

Lecture 130 Solution

Lecture 131 Session variables

Lecture 132 Parameters

Lecture 133 CODE - parameters

Lecture 134 Exercise

Lecture 135 Solution

Lecture 136 Conditional statements

Lecture 137 CODE - conditional statements

Lecture 138 Loops

Lecture 139 ITERATE

Lecture 140 WHILE

Lecture 141 CODE - loops

Lecture 142 Returning multiple values

Lecture 143 CODE - returning multiple values

Section 15: Course Materials (DOWNLOADS)

Lecture 144 Course materials

This course is mean for newbies who are not familiar with SQL syntax or just want to refresh the knowledge