English | MP4 | AVC 1920×1080 | AAC 44KHz 2ch | 333 lectures (11h 57m) | 4.50 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 ClientServer 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 floatingpoint data types
37 Other useful data types
38 Creating a table
39 Creating a table exercise
40 Creating a table solution
41 CODING EXERCISES Browserbased coding
42 Using databases and tables
43 Using databases and tables exercise
44 Using databases and tables solution
45 Additional notes on using tables
46 Additional notes on using tables exercise
MySQL constraints
47 PRIMARY KEY constraint
48 PRIMARY KEY constraint exercise
49 PRIMARY KEY constraint solution
50 FOREIGN KEY constraint Part I
51 FOREIGN KEY constraint Part II
52 FOREIGN KEY constraint Part II exercise
53 FOREIGN KEY constraint Part II solution
54 UNIQUE Constraint
55 UNIQUE Constraint exercise
56 DEFAULT Constraint
57 DEFAULT Constraint exercise
58 DEFAULT Constraint solution
59 NOT NULL Constraint Part I
60 NOT NULL Constraint Part I exercise
61 NOT NULL Constraint Part I solution
62 NOT NULL Constraint Part II
SQL best practices
63 Coding techniques and best practices Part I
64 Coding techniques and best practices Part II
Loading the employees database
65 Loading the employees database
66 Loading the employees database
SQL SELECT statement
67 SELECT FROM
68 SELECT FROM exercise
69 SELECT FROM solution
70 CODING EXERCISES the employees10 Database
71 WHERE
72 WHERE exercise
73 WHERE solution
74 AND
75 AND exercise
76 AND solution
77 OR
78 OR exercise
79 OR solution
80 Operator precedence
81 Operator precedence exercise
82 Operator precedence solution
83 IN NOT IN
84 IN NOT IN exercise 1
85 IN NOT IN solution 1
86 IN NOT IN exercise 2
87 IN NOT IN solution 2
88 LIKE NOT LIKE
89 LIKE NOT LIKE exercise
90 LIKE NOT LIKE solution
91 Wildcard characters
92 Wildcard characters exercise
93 Wildcard characters solution
94 BETWEEN AND
95 BETWEEN AND exercise
96 BETWEEN AND solution
97 IS NOT NULL IS NULL
98 IS NOT NULL IS NULL exercise
99 IS NOT NULL IS NULL solution
100 Other comparison operators
101 Other comparison operators exercise
102 Other comparison operators solution
103 SELECT DISTINCT
104 SELECT DISTINCT exercise
105 SELECT DISTINCT solution
106 Introduction to aggregate functions
107 Introduction to aggregate functions exercise
108 Introduction to aggregate functions solution
109 ORDER BY
110 ORDER BY exercise
111 ORDER BY solution
112 GROUP BY
113 Using Aliases AS
114 Using Aliases AS exercise
115 Using Aliases AS solution
116 HAVING
117 HAVING exercise
118 HAVING solution
119 WHERE vs HAVING Part I
120 WHERE vs HAVING Part II
121 WHERE vs HAVING Part II exercise
122 WHERE vs HAVING Part II solution
123 LIMIT
124 LIMIT exercise
125 LIMIT solution
SQL INSERT statement
126 The INSERT statement Part I
127 The INSERT statement Part II
128 The INSERT statement exercise 1
129 The INSERT statement solution 1
130 The INSERT statement exercise 2
131 The INSERT statement solution 2
132 Inserting data INTO a new table
133 Inserting Data INTO a New Table exercise
134 Inserting Data INTO a New Table solution
SQL UPDATE Statement
135 TCLs COMMIT and ROLLBACK
136 The UPDATE Statement Part I
137 ROLLBACK ARTICLE
138 The UPDATE Statement Part II
139 The UPDATE Statement Part II exercise
140 The UPDATE Statement Part II solution
SQL DELETE Statement
141 The DELETE Statement Part I
142 The DELETE Statement Part II
143 The DELETE Statement Part II exercise
144 The DELETE Statement Part II solution
145 DROP vs TRUNCATE vs DELETE
MySQL Aggregate functions
146 COUNT
147 COUNT exercise
148 COUNT solution
149 SUM
150 SUM exercise
151 SUM solution
152 MIN and MAX
153 MIN and MAX exercise
154 MIN and MAX solution
155 AVG
156 AVG exercise
157 AVG solution
158 ROUND
159 ROUND exercise
160 ROUND solution
161 COALESCE Preamble
162 IFNULL and COALESCE
163 Another Example of Using COALESCE
164 Another example of using COALESCE exercise 1
165 Another example of using COALESCE solution 1
166 Another example of using COALESCE exercise 2
167 Another example of using COALESCE solution 2
SQL Joins
168 Introduction to JOINs
169 Intro to JOINs exercise 1
170 Intro to JOINs solution 1
171 Intro to JOINs exercise 2
172 INNER JOIN Part I
173 INNER JOIN Part II
174 INNER JOIN Part II exercise
175 INNER JOIN Part II solution
176 A Note on Using Joins
177 Duplicate Records
178 LEFT JOIN Part I
179 LEFT JOIN Part II
180 LEFT JOIN Part II exercise
181 LEFT JOIN Part II solution
182 RIGHT JOIN
183 The new and the old join syntax
184 The new and the old join syntax exercise
185 The new and the old join syntax solution
186 JOIN and WHERE Used Together
187 Important Prevent Error Code 1055
188 JOIN and WHERE Used Together exercise
189 JOIN and WHERE Used Together solution
190 CROSS JOIN
191 CROSS JOIN exercise 1
192 CROSS JOIN solution 1
193 CROSS JOIN exercise 2
194 CROSS JOIN solution 2
195 Using Aggregate Functions with Joins
196 JOIN more than two tables in SQL
197 Join more than two tables in SQL exercise
198 Join more than two tables in SQL solution
199 Tips and tricks for joins
200 Tips and tricks for joins exercise
201 Tips and tricks for joins solution
202 UNION vs UNION ALL
203 UNION vs UNION ALL exercise
204 UNION vs UNION ALL solution
SQL Subqueries
205 SQL Subqueries with IN nested inside WHERE
206 SQL Subqueries with IN nested inside WHERE exercise
207 SQL Subqueries with IN nested inside WHERE solution
208 SQL Subqueries with EXISTSNOT EXISTS nested inside WHERE
209 SQL Subqueries with EXISTSNOT EXISTS nested inside WHERE exercise
210 SQL Subqueries with EXISTSNOT EXISTS nested inside WHERE solution
211 SQL Subqueries nested in SELECT and FROM
212 SQL Subqueries nested in SELECT and FROM exercise 1
213 SQL Subqueries nested in SELECT and FROM solution 1
214 SQL Subqueries nested in SELECT and FROM exercise 2
215 SQL Subqueries nested in SELECT and FROM solution 2
SQL Self Join
216 Self Join
SQL Views
217 Views
218 Views exercise
219 Views solution
Stored routines
220 Introduction to stored routines
221 The MySQL syntax for stored procedures
222 Stored procedures Example Part I
223 Stored procedures Example Part II
224 Stored procedures Example Part II exercise
225 Stored procedures Example Part II solution
226 Another way to create a procedure
227 Stored procedures with an input parameter
228 Stored procedures with an output parameter
229 Stored procedures with an output parameter exercise
230 Stored procedures with an output parameter solution
231 Variables
232 Variables exercise
233 Variables solution
234 Userdefined functions in MySQL
235 Error Code 1418
236 Userdefined functions in MySQL exercise
237 Userdefined functions in MySQL solution
238 Stored routines conclusion
Advanced SQL Topics
239 Types of MySQL Variables Local Variables
240 Session Variables
241 Global Variables
242 UserDefined vs System Variables
243 MySQL Triggers
244 MySQL Triggers
245 MySQL Triggers exercise
246 MySQL Triggers solution
247 MySQL Indexes
248 MySQL Indexes exercise 1
249 MySQL Indexes solution 1
250 MySQL Indexes exercise 2
251 MySQL Indexes solution 2
252 The CASE Statement
253 The CASE Statement exercise 1
254 The CASE Statement solution 1
255 The CASE Statement exercise 2
256 The CASE Statement solution 2
257 The CASE Statement exercise 3
258 The CASE Statement solution 3
SQL Window Functions
259 Introduction to MySQL Window Functions
260 The ROWNUMBER Ranking Window Function and the Relevant MySQL Syntax
261 The ROWNUMBER Ranking Window Function Exercises
262 The ROWNUMBER Ranking Window Function Solution
263 A Note on Using Several Window Functions in a Query
264 A Note on Using Several Window Functions Exercise
265 A Note on Using Several Window Functions Solution
266 MySQL Window Functions Syntax
267 MySQL Window Functions Syntax Exercise
268 MySQL Window Functions Syntax Solution
269 The PARTITION BY Clause VS the GROUP BY Clause
270 The PARTITION BY Clause VS the GROUP BY Clause Exercise
271 The PARTITION BY Clause VS the GROUP BY Clause Solution
272 The MySQL RANK and DENSERANK Window Functions
273 The MySQL RANK and DENSERANK Window Functions Exercise
274 The MySQL RANK and DENSERANK Window Functions Solution
275 Working with MySQL Ranking Window Functions and Joins Together
276 Working with MySQL Ranking Window Functions and Joins Together Exercise
277 Working with MySQL Ranking Window Functions and Joins Together Solution
278 The LAG and LEAD Value Window Functions
279 The LAG and LEAD Value Window Functions Exercise
280 The LAG and LEAD Value Window Functions Solution
281 MySQL Aggregate Functions in the Context of Window Functions Part I
282 MySQL Aggregate Functions in the Context of Window Functions Part IExercise
283 MySQL Aggregate Functions in the Context of Window Functions Part ISolution
284 MySQL Aggregate Functions in the Context of Window Functions Part II
285 MySQL Aggregate Functions in the Context of Window Functions Part IIExercise
286 MySQL Aggregate Functions in the Context of Window Functions Part IISolution
SQL Common Table Expressions CTEs
287 MySQL Common Table Expressions Introduction
288 An Alternative Solution to the Same Task
289 An Alternative Solution to the Same TaskExercise
290 An Alternative Solution to the Same TaskSolution
291 Using Multiple Subclauses in a WITH Clause Part I
292 Using Multiple Subclauses in a WITH Clause Part II
293 Using Multiple Subclauses in a WITH ClauseExercise
294 Using Multiple Subclauses in a WITH ClauseSolution
295 Referring to Common Table Expressions in a WITH Clause
SQL Temporary Tables
296 MySQL Temporary Tables Introduction
297 MySQL Temporary Tables in Action
298 MySQL Temporary Tables in ActionExercise
299 MySQL Temporary Tables in ActionSolution
300 Other Features of MySQL Temporary Tables
301 Other Features of MySQL Temporary TablesExercise
302 Other Features of MySQL Temporary TablesSolution
Combining SQL and Tableau Introduction
303 The Advantages of Software Integration
304 Why Combining SQL and Tableau is useful
305 Installing Tableau Public
306 Loading the employeesmod Database
307 Loading the employeesmod Database
Combining SQL and Tableau Task 1
308 Task 1
309 Task 1 Text
310 Important clarification
311 Task 1 SQL Solution
312 Task 1 SQL Solution Code
313 Transferring a Dataset from SQL to Tableau
314 Chart 1 Visualizing and Analyzing the Solution in Tableau Part I
315 Chart 1 Visualizing and Analyzing the Solution in Tableau Part II
Combining SQL and Tableau Task 2
316 Task 2
317 Task 2 Text
318 Task 2 SQL Solution
319 Task 2 SQL Solution Code
320 Chart 2 Visualizing and Analyzing the Solution in Tableau
Combining SQL and Tableau Task 3
321 Task 3
322 Task 3 Text
323 Task 3 SQL Solution
324 Task 3 SQL Solution Code
325 Chart 3 Visualizing and Analyzing the Solution in Tableau
Combining SQL and Tableau Task 4
326 Task 4
327 Task 4 Text
328 Task 4 Solution in SQL
329 Task 4 Solution in SQL Code
330 Chart 4 Visualizing and Analyzing the Solution in Tableau
Combining SQL and Tableau Task 5
331 Task 5 Organizing Charts 14 into a Beautiful Dashboard using Tableau
Practice SQL 10 Final Query Questions
332 Practice SQL 10 Final Query Questions
BONUS LECTURE
333 Bonus Lecture Next Steps
Resolve the captcha to access the links!