English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 331 lectures (11h 56m) | 2.93 GB
SQL that will get you hired – SQL for Business Analysis, Marketing, and Data Management
How important is database management in the age of big data and analytics?
It is really important.
How many employers would be happy to hire employees who can use data for the purposes of business intelligence?
All of them.
How many people have these skills?
Not enough.
This is why now is the time to learn SQL and gain a competitive advantage in the job market. Remember, the average salary of a SQL developer is $92,000! That’s a lucrative career.
How come?
Well, when you can work with SQL, it means you don’t have to rely on others sending you data and executing queries for you. You can do that on your own. This allows you to be independent and dig deeper into the data to obtain the answers to questions that might improve the way your company does its business. For instance, Database management is the foundation for data analysis and intelligent decision making.
Worried that you have no previous experience?
Not an issue. We will start from the very basics and gradually teach you everything you need to know. Step by step. With no steps skipped.
Why take this course in particular? Isn’t it like the rest of the SQL courses out there?
We would like to think it isn’t. Our team worked hard to create a course that is:
- Easy to understand
- Time efficient and concise
- Shows how SQL can be crucial for data analytics and business intelligence
- Comprehensive – it covers several topics not shown in other SQL courses
- Practical – it teaches you how to work with a real-life database
- Corresponds to professional best practices
- Taught in MySQL – The most popular SQL database management system
- Contains plenty of downloadable exercises, course notes, and quiz questions
Some of these aspects have been covered in other courses. Others haven’t. However, no one provides such a variety of topics in one place.
We firmly believe this course is the best training material out there. It is a truly interactive experience preparing you for a real-life working environment.
What you’ll learn
- Become an expert in SQL
- Learn how to code in SQL
- Boost your resume by learning an in-demand skill
- Create, design, and operate with SQL databases
- Start using MySQL – the #1 Database Management System
- Prepare for SQL developer, Database administrator, Business Analyst, and Business Intelligence job opportunities
- Adopt professionally tested SQL best practices
- Gain theoretical insights about relational databases
- Work with a sophisticated real-life database throughout the course
- Get maximum preparation for real-life database management
- Add data analytical tools to your skillset
- Develop business intuition while solving tasks with big data
- Study relational database management theory that you will need in your workplace every day
- Learn how to create a database from scratch
- The ability to take control of your dataset – insert, update, and delete records from your database
- Be confident while working with constraints and relating data tables
- Become a proficient MySQL Workbench user
- Acquire top-notch coding techniques and best practices
- Know how to answer specific business questions by using SQL’s aggregate functions
- Handle complex SQL joins with ease
- Approach more advanced topics in programming like SQL’s triggers, sequences, local and global variables, indexes, and more
- Merge coding skills and business acumen to solve complex analytical problems
- Become a proficient SQL user by writing flawless and efficient queries
- Tons of exercises that will solidify your knowledge
- The freedom to query anything you like from a database
Table of Contents
Introduction to databases, SQL, and MySQL
1 What does the course cover
2 Why SQL
3 Why MySQL
4 Introduction to databases
SQL theory
5 SQL as a declarative language
6 Data definition language (DDL)
7 SQL keywords
8 Data manipulation language (DML)
9 Data control language (DCL)
10 Transaction control language (TCL)
Basic database terminology
11 Relational database essentials
12 Databases vs spreadsheets
13 Database terminology
14 Relational schemas – Primary key
15 Relational schemas – Foreign key
16 Relational schemas – Unique key and null values
17 Relationships
Installing MySQL and getting acquainted with the interface
18 Installing MySQL
19 Additional note – Installing – Visual C
20 Installing MySQL on macOS and Unix systems
21 The Client-Server Model
22 Setting up a connection
23 Read me!!!
24 New Authentication Plugin – Creating a New User
25 Introduction to the MySQL interface
First steps in SQL
26 Creating a Database – Part I
27 SQL files
28 Creating a Database – Part I – exercise
29 Creating a Database – Part I – solution
30 Creating a Database – Part II
31 Creating a Database – Part II – exercise
32 Creating a Database – Part II – solution
33 Introduction to data types
34 String data types
35 Integers
36 Fixed and floating-point data types
37 Other useful data types
38 Creating a table
39 Creating a table – exercise
40 Creating a table – solution
41 Using databases and tables
42 Using databases and tables – exercise
43 Using databases and tables – solution
44 Additional notes on using tables
45 Additional notes on using tables – exercise
MySQL constraints
46 PRIMARY KEY constraint
47 PRIMARY KEY constraint – exercise
48 PRIMARY KEY constraint – solution
49 FOREIGN KEY constraint – Part I
50 FOREIGN KEY constraint – Part II
51 FOREIGN KEY constraint – Part II – exercise
52 FOREIGN KEY constraint – Part II – solution
53 UNIQUE Constraint
54 UNIQUE Constraint – exercise
55 DEFAULT Constraint
56 DEFAULT Constraint – exercise
57 DEFAULT Constraint – solution
58 NOT NULL Constraint – Part I
59 NOT NULL Constraint – Part I – exercise
60 NOT NULL Constraint – Part I – solution
61 NOT NULL Constraint – Part II
SQL best practices
62 Coding techniques and best practices – Part I
63 Coding techniques and best practices – Part II
Loading the ’employees’ database
64 Loading the ’employees’ database
65 Loading the ’employees’ database
SQL SELECT statement
66 SELECT – FROM
67 SELECT – FROM – exercise
68 SELECT – FROM – solution
69 WHERE
70 WHERE – exercise
71 WHERE – solution
72 AND
73 AND – exercise
74 AND – solution
75 OR
76 OR – exercise
77 OR – solution
78 Operator precedence
79 Operator precedence – exercise
80 Operator precedence – solution
81 IN – NOT IN
82 IN – NOT IN – exercise 1
83 IN – NOT IN – solution 1
84 IN – NOT IN – exercise 2
85 IN – NOT IN – solution 2
86 LIKE – NOT LIKE
87 LIKE – NOT LIKE – exercise
88 LIKE – NOT LIKE – solution
89 Wildcard characters
90 Wildcard characters – exercise
91 Wildcard characters – solution
92 BETWEEN – AND
93 BETWEEN – AND – exercise
94 BETWEEN – AND- solution
95 IS NOT NULL – IS NULL
96 IS NOT NULL – IS NULL – exercise
97 IS NOT NULL – IS NULL – solution
98 Other comparison operators
99 Other comparison operators – exercise
100 Other comparison operators – solution
101 SELECT DISTINCT
102 SELECT DISTINCT – exercise
103 SELECT DISTINCT – solution
104 Introduction to aggregate functions
105 Introduction to aggregate functions – exercise
106 Introduction to aggregate functions – solution
107 ORDER BY
108 ORDER BY – exercise
109 ORDER BY – solution
110 GROUP BY
111 Using Aliases (AS)
112 Using Aliases (AS) – exercise
113 Using Aliases (AS) – solution
114 HAVING
115 HAVING – exercise
116 HAVING – solution
117 WHERE vs HAVING- Part I
118 WHERE vs HAVING- Part II
119 WHERE vs HAVING – Part II – exercise
120 WHERE vs HAVING – Part II – solution
121 LIMIT
122 LIMIT – exercise
123 LIMIT – solution
SQL INSERT statement
124 The INSERT statement – Part I
125 The INSERT statement – Part II
126 The INSERT statement – exercise 1
127 The INSERT statement – solution 1
128 The INSERT statement – exercise 2
129 The INSERT statement – solution 2
130 Inserting data INTO a new table
131 Inserting Data INTO a New Table – exercise
132 Inserting Data INTO a New Table – solution
SQL UPDATE Statement
133 TCL’s COMMIT and ROLLBACK
134 The UPDATE Statement – Part I
135 ROLLBACK; ARTICLE
136 The UPDATE Statement – Part II
137 The UPDATE Statement – Part II – exercise
138 The UPDATE Statement – Part II – solution
SQL DELETE Statement
139 The DELETE Statement – Part I
140 The DELETE Statement – Part II
141 The DELETE Statement – Part II – exercise
142 The DELETE Statement – Part II – solution
143 DROP vs TRUNCATE vs DELETE
MySQL – Aggregate functions
144 COUNT()
145 COUNT() – exercise
146 COUNT() – solution
147 SUM()
148 SUM() – exercise
149 SUM() – solution
150 MIN() and MAX()
151 MIN() and MAX() – exercise
152 MIN() and MAX() – solution
153 AVG()
154 AVG() – exercise
155 AVG() – solution
156 ROUND()
157 ROUND() – exercise
158 ROUND() – solution
159 COALESCE() – Preamble
160 IFNULL() and COALESCE()
161 Another Example of Using COALESCE()
162 Another example of using COALESCE() – exercise 1
163 Another example of using COALESCE() – solution 1
164 Another example of using COALESCE() – exercise 2
165 Another example of using COALESCE() – solution 2
SQL Joins
166 Introduction to JOINs
167 Intro to JOINs – exercise 1
168 Intro to JOINs – solution 1
169 Intro to JOINs – exercise 2
170 INNER JOIN – Part I
171 INNER JOIN – Part II
172 INNER JOIN – Part II – exercise
173 INNER JOIN – Part II – solution
174 A Note on Using Joins
175 Duplicate Records
176 LEFT JOIN – Part I
177 LEFT JOIN – Part II
178 LEFT JOIN – Part II – exercise
179 LEFT JOIN – Part II – solution
180 RIGHT JOIN
181 The new and the old join syntax
182 The new and the old join syntax – exercise
183 The new and the old join syntax – solution
184 JOIN and WHERE Used Together
185 Important – Prevent Error Code 1055!
186 JOIN and WHERE Used Together – exercise
187 JOIN and WHERE Used Together – solution
188 CROSS JOIN
189 CROSS JOIN – exercise 1
190 CROSS JOIN – solution 1
191 CROSS JOIN – exercise 2
192 CROSS JOIN – solution 2
193 Using Aggregate Functions with Joins
194 JOIN more than two tables in SQL
195 Join more than two tables in SQL – exercise
196 Join more than two tables in SQL – solution
197 Tips and tricks for joins
198 Tips and tricks for joins – exercise
199 Tips and tricks for joins – solution
200 UNION vs UNION ALL
201 UNION vs UNION ALL – exercise
202 UNION vs UNION ALL – solution
SQL Subqueries
203 SQL Subqueries with IN nested inside WHERE
204 SQL Subqueries with IN nested inside WHERE – exercise
205 SQL Subqueries with IN nested inside WHERE – solution
206 SQL Subqueries with EXISTS-NOT EXISTS nested inside WHERE
207 SQL Subqueries with EXISTS-NOT EXISTS nested inside WHERE – exercise
208 SQL Subqueries with EXISTS-NOT EXISTS nested inside WHERE – solution
209 SQL Subqueries nested in SELECT and FROM
210 SQL Subqueries nested in SELECT and FROM – exercise 1
211 SQL Subqueries nested in SELECT and FROM – solution 1
212 SQL Subqueries nested in SELECT and FROM – exercise 2
213 SQL Subqueries nested in SELECT and FROM – solution 2
SQL Self Join
214 Self Join
SQL Views
215 Views
216 Views – exercise
217 Views – solution
Stored routines
218 Introduction to stored routines
219 The MySQL syntax for stored procedures
220 Stored procedures – Example – Part I
221 Stored procedures – Example – Part II
222 Stored procedures – Example – Part II – exercise
223 Stored procedures – Example – Part II – solution
224 Another way to create a procedure
225 Stored procedures with an input parameter
226 Stored procedures with an output parameter
227 Stored procedures with an output parameter – exercise
228 Stored procedures with an output parameter – solution
229 Variables
230 Variables – exercise
231 Variables – solution
232 User-defined functions in MySQL
233 Error Code 1418
234 User-defined functions in MySQL – exercise
235 User-defined functions in MySQL – solution
236 Stored routines – conclusion
Advanced SQL Topics
237 Types of MySQL Variables – Local Variables
238 Session Variables
239 Global Variables
240 User-Defined vs System Variables
241 MySQL Triggers
242 MySQL Triggers
243 MySQL Triggers – exercise
244 MySQL Triggers – solution
245 MySQL Indexes
246 MySQL Indexes – exercise 1
247 MySQL Indexes – solution 1
248 MySQL Indexes – exercise 2
249 MySQL Indexes – solution 2
250 The CASE Statement
251 The CASE Statement – exercise 1
252 The CASE Statement – solution 1
253 The CASE Statement – exercise 2
254 The CASE Statement – solution 2
255 The CASE Statement – exercise 3
256 The CASE Statement – solution 3
SQL Window Functions
257 Introduction to MySQL Window Functions
258 The ROW_NUMBER() Ranking Window Function and the Relevant MySQL Syntax
259 The ROW_NUMBER() Ranking Window Function – Exercises
260 The ROW_NUMBER() Ranking Window Function – Solution
261 A Note on Using Several Window Functions in a Query
262 A Note on Using Several Window Functions – Exercise
263 A Note on Using Several Window Functions – Solution
264 MySQL Window Functions Syntax
265 MySQL Window Functions Syntax – Exercise
266 MySQL Window Functions Syntax – Solution
267 The PARTITION BY Clause VS the GROUP BY Clause
268 The PARTITION BY Clause VS the GROUP BY Clause – Exercise
269 The PARTITION BY Clause VS the GROUP BY Clause – Solution
270 The MySQL RANK() and DENSE_RANK() Window Functions
271 The MySQL RANK() and DENSE_RANK() Window Functions – Exercise
272 The MySQL RANK() and DENSE_RANK() Window Functions – Solution
273 Working with MySQL Ranking Window Functions and Joins Together
274 Working with MySQL Ranking Window Functions and Joins Together – Exercise
275 Working with MySQL Ranking Window Functions and Joins Together – Solution
276 The LAG() and LEAD() Value Window Functions
277 The LAG() and LEAD() Value Window Functions – Exercise
278 The LAG() and LEAD() Value Window Functions – Solution
279 MySQL Aggregate Functions in the Context of Window Functions – Part I
280 MySQL Aggregate Functions in the Context of Window Functions – Part I-Exercise
281 MySQL Aggregate Functions in the Context of Window Functions – Part I-Solution
282 MySQL Aggregate Functions in the Context of Window Functions – Part II
283 MySQL Aggregate Functions in the Context of Window Functions – Part II-Exercise
284 MySQL Aggregate Functions in the Context of Window Functions – Part II-Solution
SQL Common Table Expressions (CTEs)
285 MySQL Common Table Expressions – Introduction
286 An Alternative Solution to the Same Task
287 An Alternative Solution to the Same Task-Exercise
288 An Alternative Solution to the Same Task-Solution
289 Using Multiple Subclauses in a WITH Clause – Part I
290 Using Multiple Subclauses in a WITH Clause – Part II
291 Using Multiple Subclauses in a WITH Clause-Exercise
292 Using Multiple Subclauses in a WITH Clause-Solution
293 Referring to Common Table Expressions in a WITH Clause
SQL Temporary Tables
294 MySQL Temporary Tables – Introduction
295 MySQL Temporary Tables in Action
296 MySQL Temporary Tables in Action-Exercise
297 MySQL Temporary Tables in Action-Solution
298 Other Features of MySQL Temporary Tables
299 Other Features of MySQL Temporary Tables-Exercise
300 Other Features of MySQL Temporary Tables-Solution
Combining SQL and Tableau – Introduction
301 The Advantages of Software Integration
302 Why Combining SQL and Tableau is useful
303 Installing Tableau Public
304 Loading the ’employees_mod’ Database
305 Loading the ’employees_mod’ Database
Combining SQL and Tableau – Task 1
306 Task 1
307 Task 1 – Text
308 Important clarification!
309 Task 1 SQL Solution
310 Task 1 SQL Solution – Code
311 Transferring a Dataset from SQL to Tableau
312 Chart 1 Visualizing and Analyzing the Solution in Tableau – Part I
313 Chart 1 Visualizing and Analyzing the Solution in Tableau – Part II
Combining SQL and Tableau – Task 2
314 Task 2
315 Task 2 – Text
316 Task 2 SQL Solution
317 Task 2 SQL Solution – Code
318 Chart 2 Visualizing and Analyzing the Solution in Tableau
Combining SQL and Tableau – Task 3
319 Task 3
320 Task 3 – Text
321 Task 3 SQL Solution
322 Task 3 SQL Solution – Code
323 Chart 3 Visualizing and Analyzing the Solution in Tableau
Combining SQL and Tableau – Task 4
324 Task 4
325 Task 4 – Text
326 Task 4 Solution in SQL
327 Task 4 Solution in SQL – Code
328 Chart 4 Visualizing and Analyzing the Solution in Tableau
Combining SQL and Tableau – Task 5
329 Task 5 Organizing Charts 1-4 into a Beautiful Dashboard using Tableau
Practice SQL – 10 Final Query Questions
330 Practice SQL – 10 Final Query Questions
BONUS LECTURE
331 Bonus Lecture Next Steps
Resolve the captcha to access the links!