The Complete Sql Bootcamp: A Step-By-Step Approach - Printable Version

+- Softwarez.Info - Software's World! (https://softwarez.info)
+-- Forum: Library Zone (https://softwarez.info/Forum-Library-Zone)
+--- Forum: Video Tutorials (https://softwarez.info/Forum-Video-Tutorials)
+--- Thread: The Complete Sql Bootcamp: A Step-By-Step Approach (/Thread-The-Complete-Sql-Bootcamp-A-Step-By-Step-Approach--673350)

The Complete Sql Bootcamp: A Step-By-Step Approach - AD-TEAM - 11-17-2024

[Image: 1cb7c14acf654e580c50a31804e119df.jpg]

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

[b]What you'll learn[/b]

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

[b]Requirements[/b]

No SQL experience is required, and you will learn all that you need.

[b]Description[/b]

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(: Smile

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

[Image: o4RcJxbw_o.jpg]

[To see links please register or login]

[To see links please register or login]

[To see links please register or login]