Mastering SQL (Using MySQL, Java, and Go)

Mastering SQL (Using MySQL, Java, and Go)

English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 21 Hours | 9.85 GB

A practical course to master SQL advanced queries using MySQL, and to connect Java & Golang to MySQL & PostgreSQL

Learning SQL (Structured Query Language), as the most commonly used database language, can be one of the greatest career decisions you make.

SQL is one of the most sought-after skills by hiring employers.

SQL that is the primary language responsible for managing data within a relational database management system (RDBMS) can be easily connected to any programming language.

SQL is a natural language for data analysis that is the center of every decision making process.

SQL is a productive language for writing queries and for performing CRUD (Create, Read, Update, Delete) operations on data.

SQL can be used by data scientists, data analysts, front- and back-end developers, business and systems analysts and in short anyone who’s willing to analyze data.

In this practical course, you will:

  • Learn CRUD (Create, Read, Update, Delete) operations
  • Create complex queries across multiple tables
  • Analyze data and create reports accordingly
  • Write complex joins (inner, outer, full)
  • Write summary queries using aggregate functions
  • Write sub-queries
  • Utilize String/Numeric/Datetime functions
  • Analyze data, group the related data into separate entities (tables), establish relationships (1-1, 1-m, m-m) between tables, and apply referential integrity on tables
  • Write views, triggers, stored procedures, cursors, and stored functions
  • Four Comprehensive Assignments: Create four sample databases from scratch, as well as to write advanced queries for each assignment
  • Assignments: Learn how views, triggers, stored procedures, cursors, and stored functions can interact
  • Connect MySQL and PostgreSQL to Java
  • Connect MySQL and PostgreSQL to Go (Google Golang)
Table of Contents

Introduction & Installation
1 Welcome to The Mastering SQL (Using MySQL, Java, and Go)
2 Sample Course Lectures (Only For Prospective Students)
3 A Brief History of SQL and MySQL
4 Database Concepts – Part 1 (Data and Tables)
5 Database Concepts – Part 2 (Columns, Rows, Tables, and Databases)
6 Database Concepts – Part 3 (DBMS, RDBMS, CRUD, and an Example) – Exercise
7 MySQL Installation on Windows

Your First Database
8 Section Introduction
9 First Data Types (INT and VARCHAR)
10 Creating & Managing Your First Database
11 Creating & Managing Your First Tables
12 Inserting Data into Tables
13 Querying Data using the SELECT Statement
14 Exercise – Working with Databases and Tables

Retrieving Data – Part 1 (Basics)
15 Section Introduction
16 The ORDER BY Clause, and the LIMIT Keyword – Exercise
17 Pattern Matching using the LIKE Operator
18 Complex Pattern Matching using the REGEXP Operator
19 NULL Values, Testing for NULL Values, Columns Alias, and Equality Operators
20 Primary Key, WHERE Clause, and AUTO INCREMENT
21 The DEFAULT Keyword, Logical Operators, and Exercises
22 Creating Primary Keys (2nd approach), Simple & Composite Primary Keys – Exercise
23 Data Types (DECIMAL, DATE, and ENUM) – Mathematical Operators
24 Exercise [DECIMAL, DATE, and ENUM, Mathematical Operators, CONCAT() and LEFT() ]
25 Managing Duplicate Rows – DISTINCT and ALL Keywords – Comparison Operators
26 Writing Subqueries Using the IN Keyword – The BETWEEN Keyword

Retrieving Data – Part 2 (Joins)
27 Section Introduction
28 Joining Multiple Tables – Exercise 1-3
29 Table Joins, Concepts (Inner and Outer Joins)
30 Table Joins, Examples
31 Inner Joins, Exercise 1,2
32 Implicit Inner Joins
33 Left Outer Joins, Exercise 4,5
34 Right Outer Joins, Exercise 6,7
35 Implicit and Explicit Self Joins – Equijoins – The USING Keyword – Exercise
36 Cross Joins, UNION, and Full Outer Joins – Exercise 1,2

Tables & Databases – Part 1 (CRUD Operations)
37 Section Introduction
38 Creating and Dropping Databases and Tables – CRUD Operations – Exercise 1
39 Creating Tables using the ‘SELECT AS’ and ‘LIKE’ keywords – Exercise 1,2
40 Modifying Structure of Tables using the ‘Alter Table’ Command – Exercise
41 Updating Data using the UPDATE statement – Exercise 1,2
42 Removing Data using the DELETE statement – Exercise

Retrieving Data – Part 3 (More Advanced Queries)
43 Section Introduction
44 The Group By Clause, Part 3-3 – Exercise 6
45 The Group By Clause, Part 3-4 – Exercise 7,8
46 Replacing Inner Joins with Implicit Joins – Exercise 1-5
47 Using Subqueries in Different Parts of Queries – Exercise 1-4
48 Subqueries – ALL, ANY, and SOME Operators
49 Summary Queries Aggregate Functions, Part 1 – AVG, MIN, MAX, SUM, COUNT
50 Summary Queries Aggregate Functions, Part 2 – Exercise 1,2
51 Summary Queries Aggregate Functions, Part 3 – Exercise 3,4
52 The Group By Clause, Part 1 – Exercise 1-3
53 Running MySQL Scripts
54 The Group By Clause, Part 2 – Exercise 1,2
55 The Group By Clause, Part 3-1 – Exercise 1,2
56 The Group By Clause, Part 3-2 – The HAVING Clause – Exercise 3-5

Data Types
57 Section Introduction
58 Data Types – CHAR, and VARCHAR
59 Data Types – Texts, Binary Large Objects, ENUM, and SET
60 Data Types-INT, BIGINT, …, BOOLEAN, DECIMAL, FLOAT, DOUBLE, DATE, TIME, DATETIME
61 Implicit and Explicit Data Conversion – CAST(), CONVERT(), FORMAT(), CHAR()

String & Numeric Functions
62 Section Introduction
63 Numeric Functions – Part 2 (Power, Sqrt, Truncate, Rand) – Exercise
64 String Functions – Part 1 (Lower, Upper, Lcase, Ucase)
65 String Functions – Part 2 (Concat, Concat WS, LTrim, RTrim, Trim)
66 String Functions – Part 3 (Length, Char Length, Lpad, Rpad)
67 String Functions – Part 4 (Left, Right, Reverse, Repeat, Space)
68 String Functions – Part 5 (Replace, Locate, Insert)
69 String Functions – Part 6 (Strcmp, Substring)
70 String Functions – Exercise 1,2
71 Numeric Functions – Part 1 (Mod, Sign, Abs, Ceiling, Floor)

Date-Time & Miscellaneous Functions
72 Section Introduction
73 The Case() Function – Exercise
74 The IF() Function
75 The IfNull() and Coalesce() Functions
76 Datetime Functions – Part 1 (Now, Sysdate, Curdate, Curtime, UTC Date, …)
77 Datetime Functions – Part 2 (Year, Quarter, Month, MonthName, Week)
78 Datetime Functions – Part 3 (Day, Hour, Minute, Second, DayOfWeek, …)
79 Datetime Functions – Part 4 (Extract)
80 Datetime Functions – Part 5 (Date Format, Time Format, Get Format)
81 Datetime Functions – Part 6 (Format Specifiers)
82 Datetime Functions – Part 7 (Date add, Date Sub, DateDiff, TimeDiff, …)
83 Datetime Functions – Part 8 – Searching for datetime values – Exercise 1,2

Tables & Databases – Part 2 (Relationships & Constraints)
84 Section Introduction
85 Altering Table Constraints (AddDropChange Column) – Exercise 1-3
86 Altering Table Constraints (AddDrop Primary or Foreign Key, …) – Exercise 4-8
87 Renaming, Truncating and Dropping Tables
88 One-to-One Relationships Primary and Foreign Keys
89 One-to-Many Relationships
90 Many-to-Many Relationships
91 One-to-One Relationships – Practice
92 One-to-Many Relationships – Practice
93 Many-to-Many Relationships – Practice (Including an ERD)
94 Attributes & Constraints (NOT NULL, UNIQUE, DEFAULT, AUTO INCREMENT, …)
95 Referential Integrity & Foreign Key Constraint – Exercise

Working with Views
96 Section Introduction
97 What is a View
98 Views, Examples
99 Views, Nested Views – Exercise 1-3
100 Read-Only and Updatable Views – Part 1
101 Read-Only and Updatable Views – Part 2

Database Design (Conceptual, Logical & Physical) Assignment 1 – ‘World’ DB
102 Section Introduction
103 Assignment 1 – Solution to Queries #8,9
104 Assignment 1 – Solution to Queries #10,11
105 Steps to Design a Database
106 Example of Designing a Database – Part 1
107 Example of Designing a Database – Part 2
108 Example of Designing a Database – Part 3
109 Assignment 1 (The World DB) – Assignment Explained
110 Assignment 1 – Creating the Database and Tables Assignment Resources
111 Assignment 1 – Solution to Queries #1-4
112 Assignment 1 – Solution to Queries #5-7

Assignment 2 – ‘Soccer League’ Database
113 Section Introduction
114 Assignment 2 – Solution to Query #6
115 Assignment 2 – Solution to Query #7
116 Assignment 2 Explained
117 Assignment 2 – Solution, Entity Relationship Diagram Assignment Resources
118 Assignment 2 – Creating the Database and Tables
119 Assignment 2 – Solution to Query #1
120 Assignment 2 – Solution to Query #2
121 Assignment 2 – Solution to Query #3
122 Assignment 2 – Solution to Query #4
123 Assignment 2 – Solution to Query #5

Assignment 3 – ‘Artists’ Database
124 Section Introduction
125 Assignment 3 – Solution to Query #9
126 Assignment 3 Explained
127 Assignment 3 – Solution, Entity Relationship Diagram Assignment Resources
128 Assignment 3 – Creating the Database and Tables
129 Assignment 3 – Solution to Query #1
130 Assignment 3 – Solution to Query #2-4
131 Assignment 3 – Solution to Query #5
132 Assignment 3 – Solution to Query #6,7
133 Assignment 3 – Solution to Query #8

Assignment 4 – ‘Online Banking’ Database
134 Section Introduction
135 Assignment 4 – Solution to Query #5
136 Assignment 4 – Solution to Query #6
137 Assignment 4 Explained
138 Assignment 4 – Solution, Entity Relationship Diagram – Part 1
139 Assignment 4 – Solution, Entity Relationship Diagram – Part 2
140 Assignment 4 – Solution, Entity Relationship Diagram – Part 3
141 Assignment 4 – Creating the Database and Tables
142 Assignment 4 – Queries
143 Assignment 4 – Solution to Query #1,2
144 Assignment 4 – Solution to Query #3,4

Stored Procedures & Program Control Structures
145 Section Introduction
146 WHILE Loop – Examples 1,2
147 Stored Procedures Concepts – Example 1
148 Stored Procedures – Examples 2,3
149 Stored Procedures Input Parameters – Examples 4,5 Exercise
150 Stored Procedures – Example 6 Exercise
151 Stored Procedures Output Parameters User-Defined Variables – Example 7
152 Stored Procedures INOUT Parameters – Examples 8,9
153 IF Statement – Examples 1,2
154 CASE Statement – Examples 1-3

Cursors, Stored Functions, Triggers & Assignment 5 (Loan App)
155 Section Introduction
156 Assignment 5, Solution (Using Procedures, Cursors, Functions & Triggers)
157 Cursors, Definition and Example
158 Stored Functions, Definition – Example 1
159 Stored Functions – Examples 2-4
160 Stored Functions – Examples 5-6
161 Triggers, Definition – Example 1
162 Triggers – Example 2
163 Triggers – Example 3
164 Assignment 5 (Processing Loan Applications) Explained

Installation – JDK, Go, PostgreSQL, Eclipse, Visual Studio Code, & Git Bash
165 Section Introduction
166 Installation of the Java Development Kit ( JDK) on Windows
167 Installation of the Eclipse IDE on Windows
168 Installation of the Go Compiler, Visual Studio Code, and Git Bash on Windows
169 Installation of the PostgreSQL on Windows
170 Creating Some Tables in PostgreSQL

Connecting Java to MySQL & PostgreSQL
171 Section Introduction
172 JDBC Stored Procedures – IN Parameters
173 JDBC Stored Procedures – INOUT Parameters
174 JDBC Stored Procedures – IN, OUT, and INOUT Parameters
175 JDBC Transaction Management
176 JDBC PostgreSQL and Basic Operations
177 JDBC PostgreSQL – Working with Large Objects and Transactions
178 Java Database Connectivity (JDBC), Concepts & Architecture
179 Setting up the Development Environment – Connecting to MySQL
180 JDBC Select Statement
181 JDBC Insert, Update, and Delete Statements
182 JDBC Prepared Statements
183 JDBC Views – Exercise
184 JDBC Writing Large Objects to Database
185 JDBC Reading Large Objects from Database

Connecting Go (Google Golang) to MySQL & PostgreSQL
186 Connecting Go to MySQL
187 Go & CRUD Statements in MySQL
188 Go & CRUD Statements in PostgreSQL