The Complete Sql Bootcamp – A Step-By-Step Approach → Entity Relationship Model - Your future starts here – Worldbootcamp.com

Détails

27 Sections
215 Lessons
3 Hours

Expand all sectionsCollapse all sections

---Day1
1
- 1.0
  Day1
Data Definition n(Users, Roles) and Privileges
13
- 1.0
  CREATE USER
- 1.1
  CREATE ROLE
- 1.2
  Data Control Language (DCL)
- 1.3
  GRANT specific privileges to a role
- 1.4
  GRANT ALL PRIVILEGES to a role
- 1.5
  GRANT specific privileges to a user
- 1.6
  GRANT(add members on the existing role)
- 1.7
  REVOKE specific privileges from a role
- 1.8
  REVOKE ALL PRIVILEGES from a role
- 1.9
  REVOKE (remove members on the existing role)
- 1.10
  DROP USERS
- 1.11
  DROP ROLES
- 1.12
  Data Definition(Users, Roles) and Privileges
  10 Minutes0 Questions
Introduction
7
- 2.0
  Introduction
- 2.1
  Database Server
- 2.2
  Installinig PostgreSQL on Windows
- 2.3
  Installing PostgreSQL on MacOS
- 2.4
  Entity Relationship Model
- 2.5
  Data Modelling and Design
- 2.6
  pgAdmin Introduction
Basic Select Statement
15
- 3.0
  Basic Select Statement
- 3.1
  Literal (character, String)
- 3.2
  Literal (constant)
- 3.3
  Non-numeric Literals
- 3.4
  Numeric Literals
- 3.5
  Literal
- 3.6
  Arithmetic Expression Operators
- 3.7
  Mathematical Operator Precedence
- 3.8
  Mathematical Operator Precedence
- 3.9
  Using aliases
- 3.10
  Using aliases
- 3.11
  Escape character sequence
- 3.12
  Concatenation Operator
- 3.13
  Comments Within SQL Statements code
- 3.14
  Basic Select Statement
Data Types
7
- 4.0
  Data Types
- 4.1
  Cast Function(CAST()) and cast operator(::)
- 4.2
  Numeric Data Types
- 4.3
  Character Data Types
- 4.4
  Date Time Data Types
- 4.5
  Boolean Data Type
- 4.6
  Data Types
  3 Days
Selecting from a table
10
- 5.0
  SELECTING columns or expressions FROM a table
- 5.1
  Selecting All Columns
- 5.2
  SELECT all columns
  3 Days
- 5.3
  Selecting Specific Columns or Expressions
- 5.4
  SELECT specific columns
  3 Days
- 5.5
  Using Column Aliases
  3 Days
- 5.6
  Eliminating duplicate rows
- 5.7
  Eliminating duplicate rows
  3 Days
- 5.8
  SELECT from a generated (VALUES Lists)
- 5.9
  Selecting from a table
  3 Days
WHERE clause and ORDER BY clause
19
- 7.0
  Using WHERE Clause
- 7.1
  Using WHERE Clause
  3 Days
- 7.2
  Comparison Operators
- 7.3
  Match any Range search condition( [NOT] BETWEEN )
- 7.4
  Match any Range search condition( [NOT] BETWEEN)
  3 Days
- 7.5
  Match any of a list of values ( [NOT] IN )
- 7.6
  Pattern match search condition ( [NOT] LIKE )
- 7.7
  Using ( IS [NOT] NULL ) Conditions
- 7.8
  Using the AND Operator
- 7.9
  Using the OR Operator
- 7.10
  Rules of Precedence(Order of operation )
- 7.11
  ORDER BY clause
- 7.12
  OFFSET
- 7.13
  FETCH
- 7.14
  LIMIT
- 7.15
  LIMIT
  3 Days
- 7.16
  OFFSET and FETCH
- 7.17
  OFFSET and LIMIT
- 7.18
  WHERE clause and ORDER BY clause
---Day2
1
- 8.0
  Day2
Functions and Operators
16
- 9.0
  Functions and Operators
- 9.1
  String functions and Operators (String Concatenation)
- 9.2
  String functions(Case Conversion)
- 9.3
  Show part of a string(LEFT,RIGHT, SUBSTR)
- 9.4
  Show part of a string(SUBSTR)
  3 Days
- 9.5
  Remove from a String(TRIM,LTRIM,RTRIM)
- 9.6
  Add and Replace characters in a String(LPAD, RPAD, REPLACE)
- 9.7
  Length of string(LENGTH)
- 9.8
  Using MODULUS Function
- 9.9
  Using MODULUS Function
  3 Days
- 9.10
  Using CEILING and FLOOR Function
- 9.11
  Using CEILING and FLOOR Function
  3 Days
- 9.12
  Using ROUND and TRUNCATE Function
- 9.13
  (Date , Time Functions)
- 9.14
  (Date , Time Functions)
  3 Days
- 9.15
  Functions and Operators
Regular Expression
19
- 10.0
  Introduction
- 10.1
  POSIX Regular Expressions
- 10.2
  Regular Expressions Atoms
- 10.3
  Literal Characters:
- 10.4
  Dot : .
- 10.5
  Regular Expression Constraints (also called Anchors): ^, $
- 10.6
  Regular Expression Quantifiers: { }
- 10.7
  Regular Expression Quantifiers: { }
  3 Days
- 10.8
  Bracket Expressions (Character classes): [ ]
- 10.9
  Escaping characters:
- 10.10
  Escaping characters:
  3 Days
- 10.11
  Groups(Parentheses): ( )
- 10.12
  Alternatives: |
- 10.13
  Alternatives: |
  3 Days
- 10.14
  REGEXP_COUNT
- 10.15
  REGEXP_INSTR
- 10.16
  REGEXP_LIKE
- 10.17
  REGEXP_REPLACE
- 10.18
  REGEXP_SUBSTR
Conditional Expressions
7
- 11.0
  Conditional Expressions
- 11.1
  CASE Statement
- 11.2
  CASE Statement
  3 Days
- 11.3
  NULLIF
- 11.4
  COALESCE
- 11.5
  COALESCE
  3 Days
- 11.6
  LEAST and GREATEST
GROUP BY and HAVING Clauses
10
- 12.0
  Aggregate Functions
- 12.1
  Using Aggregate Functions on the entire column data set
- 12.2
  Using Aggregate Functions on the entire column data set
  3 Days
- 12.3
  Using Aggregate Functions with WHERE clause
- 12.4
  COUNT Aggregate Function
- 12.5
  Handling Null values in Aggregate Functions
- 12.6
  GROUP BY Clause
- 12.7
  GROUP BY Clause(Grouping by Multiple Columns)
- 12.8
  GROUP BY Clause
  3 Days
- 12.9
  HAVING Clause (Filter Groups using Aggregated column)
Generating Reports with (CUBE, ROLLUP, GROUPING SET)
6
- 13.0
  Generating Reports by Grouping Related Data_
- 13.1
  ROLLUP
- 13.2
  CUBE
- 13.3
  GROUPING
- 13.4
  GROUPING SETS
- 13.5
  Generating Reports with (CUBE, ROLLUP, GROUPING SET)
  3 Days
---Day3
1
- 14.0
  Day3
JOINS
9
- 15.0
  Joining Multiple Tables
- 15.1
  Table aliases
- 15.2
  Types of JOINS
- 15.3
  INNER JOIN(ON clause, USING clause)
- 15.4
  LEFT OUTER JOIN(ON clause)
- 15.5
  RIGHT OUTER JOIN(ON clause)
- 15.6
  FULL OUTER JOIN(ON clause)
- 15.7
  CROSS JOIN
- 15.8
  Join
  10 Minutes0 Questions
Window Functions (RANK, DENSE_RANK, ROW_NUMBER)
9
- 16.0
  Windowing
- 16.1
  Windowing calculations(aggregate function)
- 16.2
  Window Functions
- 16.3
  Window Function OVER(ORDER BY order_list ) clause
- 16.4
  Window Function OVER(PARTITION BY ORDER BY ) clause
- 16.5
  Window Function OVER() without “window_definition”
- 16.6
  Window Function OVER(PARTITION BY ) clause
- 16.7
  WINDOW clause
- 16.8
  Window Functions (RANK, DENSE_RANK, ROW_NUMBER)
Subqueries
12
- 17.0
  SUBQUERY
- 17.1
  Scalar subquery
- 17.2
  Row subquery
- 17.3
  Subquery Expressions
- 17.4
  Multi-Row Subquery (IN, SOME/ANY, ALL)
- 17.5
  Multi-Row Subquery ([NOT] IN)
- 17.6
  Multi-Row Subquery ( ANY/SOME)
- 17.7
  Table subquery(Inline View)
- 17.8
  Multi-Row Subquery ( ALL)
- 17.9
  Correlated Subquery
- 17.10
  [NOT] EXISTS Subquery Expressions
- 17.11
  Subqueries
  3 Days
Common Table Expressions (WITH Clause)
4
- 18.0
  Using WITH Clause (split complicated queries into smaller parts
- 18.1
  Purpose and Benefits of the WITH Clause
- 18.2
  Multiple Common Table Expressions (CTEs)
- 18.3
  Common Table Expressions (WITH Clause)
  3 Days
Set Operators (UNION, INTERSECT, EXCEPT)
5
- 19.0
  Introduction
- 19.1
  UNION [ ALL]
- 19.2
  INTERSECT [ ALL ]
- 19.3
  EXCEPT
- 19.4
  Set Operators(UNION, INTERSECT, EXCEPT)
  3 Days
---Day4
1
- 20.0
  Day4
Data Definition (Table)
18
- 21.0
  Data definition language (DDL)
- 21.1
  CREATE DATABASE
- 21.2
  CREATE TABLE
- 21.3
  CREATE TABLE with AS keyword
- 21.4
  ALTER TABLE (ADD Column)
- 21.5
  ALTER TABLE(DROP COLUMN)
- 21.6
  ALTER ADD (Primary key )
- 21.7
  ALTER TABLE(ADD foreign key)
- 21.8
  RENAME TABLE
- 21.9
  RENAME Column
- 21.10
  RENAME Constraint
- 21.11
  Comment on a TABLE
- 21.12
  ALTER TABLE(DROP COLUMN)
- 21.13
  Remove TABLE comment
- 21.14
  RENAME Column
- 21.15
  TRUNCATE
- 21.16
  DROP
- 21.17
  Data Definition(Table)
  3 Days
Constraints
16
- 22.0
  Check Constraint (column constraint, explicit constraint name )
- 22.1
  Check Constraint (column constraint, implicit constraint name )
- 22.2
  Check Constraint (table constraint, implicit constraint name )
- 22.3
  Check Constraint (table constraint, implicit constraint name )
- 22.4
  Not-Null Constraints (column constraint explicit)
- 22.5
  Unique Constraints (column constraint, explicit constraint name )
- 22.6
  Unique Constraints (column constraint, implicit constraint name )
- 22.7
  Unique Constraints (table constraint, explicit constraint name )
- 22.8
  Unique Constraints (table constraint, implicit constraint name )
- 22.9
  Primary Keys (column constraint, implicit constraint name )
- 22.10
  Primary Keys (table constraint, implicit constraint name )
- 22.11
  Foreign Keys (table constraint, explicit constraint name )
- 22.12
  Foreign Keys (table constraint, implicit constraint name )
- 22.13
  Default (column constraint only )
- 22.14
  Clean-up
- 22.15
  Constraints
  3 Days
Data Manipulation
17
- 23.0
  Data manipulation language (DML)
- 23.1
  SELECT
- 23.2
  INSERT
- 23.3
  Basic INSERT using literal, expression, and DEFAULT method1 (column name)
- 23.4
  Basic INSERT using literal, expression, and DEFAULT method 2(values)
- 23.5
  INSERT using query
- 23.6
  INSERT
- 23.7
  UPDATE
- 23.8
  UPDATE specific values
- 23.9
  UPDATE all values in column
- 23.10
  UPDATE multiple columns
- 23.11
  DELETE
- 23.12
  Delete specific rows
- 23.13
  Delete all rows
- 23.14
  MERGE
- 23.15
  Clean-up
- 23.16
  Data Manipulation
  10 Minutes0 Questions
Transaction Control
6
- 24.0
  Disable auto-commit in pgAdmin4
- 24.1
  COMMIT
- 24.2
  ROLLBACK
- 24.3
  SAVEPOINT
- 24.4
  Cleanup
- 24.5
  Transaction Control
  10 Minutes0 Questions
---Day5
1
- 25.0
  Day5
Data Definition (Sequences)
9
- 26.0
  CREATE SEQUENCE
- 26.1
  Sequence Functions
- 26.2
  NEXTVAL
- 26.3
  CURRVAL
- 26.4
  ALTER Sequence
- 26.5
  RENAME a Sequence
- 26.6
  COMMENT on Sequence
- 26.7
  DROP sequence
- 26.8
  Data Definition(Sequences)
  10 Minutes0 Questions
Data Definition (Views)
8
- 27.0
  Create a View
- 27.1
  SELECT from a View
- 27.2
  (OR REPLACE) keyword
- 27.3
  ALTER VIEW
- 27.4
  RENAME a View
- 27.5
  COMMENT
- 27.6
  Drop a View
- 27.7
  Data Definition(Views)
  10 Minutes0 Questions

Enable Notifications OK No thanks