The Complete Sql Bootcamp: A Step-By-Step Approach
Published 10/2024
MP4 | Video: h264, 1920x1080 | Audio: AAC, 44.1 KHz
Language: English | Size: 660.91 MB | Duration: 2h 45m
Master SQL
What you'll learn
Generate reports and address business challenges
Write Complex Queries Using Common Table Expressions (CTEs)
Lean about GROUP by clause
Master SQL Joins and Window Functions (RANK, DENSE_RANK, ROW_NUMBER)
Master SQL for Data Analytics and Business Intelligence
Master SQL for Data Migration and Data Manipulation
Create Database Objects
Requirements
No SQL experience is required, and you will learn all that you need.
Description
Brief Introduction:The Complete SQL Bootcamp is a hands-on course designed to take you from the basics to a professional level. This comprehensive course will explore the principles of relational databases and the robust SQL programming language. It will equip developers with vital SQL skills to write queries for single and multiple tables, manipulate data within those tables, and create database objects.Course Overview:This step-by-step SQL course is designed to improve your SQL skills and is ideal for students who want to become proficient in SQL. Led by an experienced SQL Developer Instructor, this course offers a well-organized and engaging learning experience for those who want to master SQL.Learning Objectives:By the end of the course, you will learn how to:Retrieve Data using basic SQL SELECT StatementsRestrict and Sort DataUse Aggregate FunctionsApply Conditional ExpressionsGenerate Reports with Aggregated Data Using Group FunctionsDisplay Data from Multiple Tables Using JoinsUtilize Window FunctionsWork with Common Table Expressions (CTEs) using the WITH clauseManage Data using Subqueries and Advanced QueriesManage Tables using DML StatementsCreate Sequences, Synonyms, Indexes, Views, and Schema ObjectsWho is this course for?This course is designed for learners at all levels, from beginners looking for a solid foundation to SQL professionals striving for mastery. No prior knowledge is necessary. It is suitable for users of any database, including Oracle, MySQL, Microsoft SQL Server, Amazon Redshift, and MySQL.
Overview
Section 1: Introduction
Lecture 1 Introduction
Lecture 2 Database Server
Lecture 3 Installing PostgreSQL on Windows
Lecture 4 Installing PostgreSQL on MacOS
Lecture 5 Entity Relationship Model
Lecture 6 Data Modelling and Design
Lecture 7 pgAdmin Introduction
Section 2: Basic Select Statement
Lecture 8 Basic Select Statement
Lecture 9 Literal(character, String)
Lecture 10 Literal(constant)
Lecture 11 Non-numeric Literals
Lecture 12 Numeric Literals
Lecture 13 Arithmetic Expression Operators
Lecture 14 Mathematical Operator Precedence
Lecture 15 Using aliases
Lecture 16 Escape character sequence
Lecture 17 Concatenation Operator
Lecture 18 Comments Within SQL Statements code
Section 3: Data Types
Lecture 19 Data Types
Lecture 20 Cast Function(CAST()) and cast operator
Lecture 21 Numeric Data Types
Lecture 22 Character Data Types
Lecture 23 Date Time Data Types
Lecture 24 Boolean Data Type
Section 4: Selecting from a table
Lecture 25 SELECTING columns or expressions FROM a table
Lecture 26 Selecting All Columns
Lecture 27 Selecting Specific Columns or Expressions
Lecture 28 Using Column Aliases
Lecture 29 Eliminating duplicate rows
Lecture 30 SELECT from a generated (VALUES Lists)
Section 5: WHERE clause and ORDER BY clause
Lecture 31 Using WHERE Clause
Lecture 32 Comparison Operators
Lecture 33 Match any Range search condition([NOT] BETWEEN )
Lecture 34 Match any of a list of values ([NOT] IN )
Lecture 35 Pattern match search condition ([NOT] LIKE )
Lecture 36 Using ( IS[NOT] NULL ) Conditions
Lecture 37 Using the AND Operator
Lecture 38 Using the OR Operator
Lecture 39 Rules of Precedence(Order of operation )
Lecture 40 ORDER BY clause
Lecture 41 OFFSET
Lecture 42 FETCH
Lecture 43 LIMIT
Lecture 44 OFFSET and FETCH
Lecture 45 OFFSET and LIMIT
Section 6: Functions and Operators
Lecture 46 Functions and Operators
Lecture 47 String functions and Operators (String Concatenation)
Lecture 48 String functions(Case Conversion)
Lecture 49 Show part of a string(LEFT,RIGHT, SUBSTR)
Lecture 50 Remove from a String(TRIM,LTRIM,RTRIM)
Lecture 51 Add and Replace characters in a String(LPAD, RPAD, REPLACE)
Lecture 52 Length of string(LENGTH)
Lecture 53 Using MODULUS Function
Lecture 54 Using CEILING and FLOOR Function
Lecture 55 Using ROUND and TRUNCATE Function
Lecture 56 (Date , Time Functions)
Section 7: Regular Expression
Lecture 57 Introduction
Lecture 58 POSIX Regular Expressions
Lecture 59 Regular Expression Atoms
Lecture 60 Literal Characters
Lecture 61 Dot : .
Lecture 62 Regular Expression Constraints (also called Anchors): ^, $
Lecture 63 Regular Expression Quantifiers: { }
Lecture 64 Bracket Expressions (Character classes):[ ]
Lecture 65 Escaping characters: \
Lecture 66 Groups(Parentheses): ( )
Lecture 67 Alternatives:
Lecture 68 REGEXP_COUNT
Lecture 69 REGEXP_INSTR
Lecture 70 REGEXP_LIKE
Lecture 71 REGEXP_REPLACE
Lecture 72 REGEXP_SUBSTR
Section 8: Conditional Expressions
Lecture 73 Conditional Expressions
Lecture 74 CASE Statement
Lecture 75 NULLIF
Lecture 76 COALESCE
Lecture 77 LEAST and GREATEST
Section 9: GROUP BY and HAVING Clauses
Lecture 78 Aggregate Functions
Lecture 79 Using Aggregate Functions on the entire column data set
Lecture 80 Using Aggregate Functions with WHERE clause
Lecture 81 COUNT Aggregate Function
Lecture 82 Handling Null values in Aggregate Functions
Lecture 83 GROUP BY Clause
Lecture 84 GROUP BY Clause(Grouping by Multiple Columns)
Lecture 85 HAVING Clause (Filter Groups using Aggregated column)
Section 10: Generating Reports with (CUBE, ROLLUP, GROUPING SET)
Lecture 86 Generating Reports by Grouping Related Data_
Lecture 87 ROLLUP
Lecture 88 CUBE
Lecture 89 GROUPING
Lecture 90 GROUPING SETS
Section 11: Joins
Lecture 91 Joining Multiple Tables
Lecture 92 Table aliases
Lecture 93 Types of JOINS
Lecture 94 INNER JOIN(ON clause, USING clause)
Lecture 95 LEFT OUTER JOIN(ON clause)
Lecture 96 RIGHT OUTER JOIN(ON clause)
Lecture 97 FULL OUTER JOIN(ON clause)
Lecture 98 CROSS JOIN
Section 12: Window Functions (RANK, DENSE_RANK, ROW_NUMBER)
Lecture 99 Windowing
Lecture 100 Windowing calculations(aggregate function)
Lecture 101 Window Functions
Lecture 102 Window Function OVER(ORDER BY order_list ) clause
Lecture 103 Window Function OVER(PARTITION BY ORDER BY ) clause
Lecture 104 Window Function OVER() without "window_definition"
Lecture 105 Window Function OVER(PARTITION BY ) clause
Lecture 106 WINDOW clause
Section 13: Common Table Expressions (WITH Clause)
Lecture 107 Using WITH Clause( split complicated queries into smaller parts)
Lecture 108 Purpose and Benefits of the WITH Clause
Lecture 109 Multiple Common Table Expressions (CTEs)
Section 14: Subqueries
Lecture 110 SUBQUERY
Lecture 111 Scalar subquery
Lecture 112 Row subquery
Lecture 113 Subquery Expressions
Lecture 114 Multi-Row Subquery (IN, SOME/ANY, ALL)
Lecture 115 Multi-Row Subquery ([NOT] IN)
Lecture 116 Multi-Row Subquery ( ANY/SOME)
Lecture 117 Table subquery(Inline View)
Lecture 118 Multi-Row Subquery ( ALL)
Lecture 119 Correlated Subquery
Lecture 120[NOT] EXISTS Subquery Expressions
Section 15: Set Operators(UNION, INTERSECT, EXCEPT)
Lecture 121 Introduction
Lecture 122 UNION[ ALL]
Lecture 123 INTERSECT[ ALL ]
Lecture 124 EXCEPT
Section 16: Data Definition(Table)
Lecture 125 Data definition language (DDL)
Lecture 126 CREATE DATABASE
Lecture 127 CREATE TABLE
Lecture 128 CREATE TABLE with AS keyword
Lecture 129 ALTER TABLE (ADD Column)
Lecture 130 ALTER TABLE(DROP COLUMN)
Lecture 131 ALTER ADD (Primary key )
Lecture 132 ALTER TABLE(ADD foreign key)
Lecture 133 RENAME TABLE
Lecture 134 RENAME Column
Lecture 135 RENAME Constraint
Lecture 136 Comment on a TABLE
Lecture 137 ALTER TABLE(DROP COLUMN)
Lecture 138 Remove TABLE comment
Lecture 139 RENAME Column
Lecture 140 TRUNCATE
Lecture 141 DROP
Section 17: Constraints
Lecture 142 Check Constraint (column constraint, explicit constraint name )
Lecture 143 Check Constraint (column constraint, implicit constraint name )
Lecture 144 Check Constraint (table constraint, implicit constraint name )
Lecture 145 Check Constraint (table constraint, implicit constraint name )
Lecture 146 Not-Null Constraints (column constraint explicit)
Lecture 147 Unique Constraints (column constraint, explicit constraint name )
Lecture 148 Unique Constraints (column constraint, implicit constraint name )
Lecture 149 Unique Constraints (table constraint, explicit constraint name )
Lecture 150 Unique Constraints (table constraint, implicit constraint name )
Lecture 151 Primary Keys (column constraint, implicit constraint name )
Lecture 152 Primary Keys (table constraint, implicit constraint name )
Lecture 153 Foreign Keys (table constraint, explicit constraint name )
Lecture 154 Foreign Keys (table constraint, implicit constraint name )
Lecture 155 Default (column constraint only )
Lecture 156 Clean-up
Section 18: Data Manipulation
Lecture 157 Data manipulation language (DML)
Lecture 158 SELECT
Lecture 159 INSERT
Lecture 160 Basic INSERT using literal, expression, and DEFAULT method 1(column name
Lecture 161 Basic INSERT using literal, expression, and DEFAULT method 2(values)
Lecture 162 INSERT using query
Lecture 163 INSERT
Lecture 164 UPDATE
Lecture 165 UPDATE specific values
Lecture 166 UPDATE all values in column
Lecture 167 UPDATE multiple columns
Lecture 168 DELETE
Lecture 169 Delete all rows
Lecture 170 MERGE
Lecture 171 Delete specific rows
Lecture 172 Clean-up
Section 19: Transaction Control
Lecture 173 Disable auto-commit in pgAdmin4 37
Lecture 174 COMMIT
Lecture 175 ROLLBACK
Lecture 176 SAVEPOINT
Section 20: Data Definition(Sequences)
Lecture 177 CREATE SEQUENCE
Lecture 178 Sequence Functions
Lecture 179 NEXTVAL
Lecture 180 CURRVAL
Lecture 181 ALTER Sequence
Lecture 182 RENAME a Sequence
Lecture 183 COMMENT on Sequence
Lecture 184 DROP sequence
Section 21: Data Definition(Views)
Lecture 185 Create a View
Lecture 186 SELECT from a View
Lecture 187 (OR REPLACE) keyword
Lecture 188 ALTER VIEW
Lecture 189 RENAME a View
Lecture 190 COMMENT
Lecture 191 Drop a View
Lecture 192 Cleanup
Section 22: Data Definition(Users, Roles) and Privileges
Lecture 193 CREATE USER
Lecture 194 CREATE ROLE
Lecture 195 Data Control Language (DCL)
Lecture 196 GRANT specific privileges to a role
Lecture 197 GRANT ALL PRIVILEGES to a role
Lecture 198 GRANT specific privileges to a user
Lecture 199 GRANT(add members on the existing role)
Lecture 200 REVOKE specific privileges from a role
Lecture 201 REVOKE ALL PRIVILEGES from a role
Lecture 202 REVOKE (remove members on the existing role)
Lecture 203 DROP USERS
Lecture 204 DROP ROLES)
Managers,Data Analytics and Business Intelligence,Data Scientists and Data Engineers,Data Migrations Specialist,Other Database Users
Screenshots
Code:
Bitte
Anmelden
oder
Registrieren
um Code Inhalt zu sehen!
Code:
Bitte
Anmelden
oder
Registrieren
um Code Inhalt zu sehen!
