Bootcamp Details & Notes

• Details of the bootcamp:

  • Bootcamp starts on 03-Jun-2023 and ends on 06-Aug-2023.

  • Total 20 Live sessions of 1.5 hrs each.

  • Classes are held only on weekends (Saturday & Sunday).

  • The cost for the entire Bootcamp is USD $30 (~INR 2400).

  • Certification of course completion will be provided at the end of the Bootcamp.

  • All sessions will be recorded. The recording will be available for upto 1 year from the date of the last session

  • Timing:

    • For Indians: 07:30 pm IST (Indian time) - Every Saturday & Sunday.

    • For US learners: 10:00 am EST (Eastern time) - Every Saturday & Sunday.

    • For European learners: 04:00 pm CET (Central European time) - Every Saturday & Sunday.

  • You can register for the course using the below link or by directly going to Lighthall.co and searching for my class under teacher “Thoufiq”

    https://www.lighthall.co/class/242f82c8-8220-40e8-9674-ed6b3f596f09

• Format of the Bootcamp sessions:

  • These are LIVE sessions where students can watch and hear me teach. Students can communicate with me through live chat. There is no option for students to enable their video or audio. Students can post their questions or doubts live during the class through chat only

  • The last 15 mins of every session will be allocated to answer questions raised during the session.

  • If a student misses a live session, then they can watch the recording of that session which will be available within 24hrs after the end of the session.

  • Any questions related to the class can also be posted on the Lighthall group chat. I will try to answer the questions either in group chat or in the next live session

• Additional details:

  • The bootcamp will be conducted on https://www.lighthall.co/

  • Lighthall offers group chat feature so everyone part of the bootcamp will be able to communicate with each other through this group chat itself. There will be no separate Telegram group created for this purpose.

  • Anyone facing payment issues can send an email to lighthall support team support@lighthall.co . If the issue is not solved then you can directly ping me on LinkedIn or discord.

  • All the course material used during the bootcamp such as datasets, SQL scripts, documents etc will be shared after each session. The materials will be available in Lighthall under the “Additional Content” section of the class page.

  • All the sessions will be recorded. Recordings will be available within 24 hrs after the end of each session. Recordings will be available in Lighthall within the class page itself.

  • The validity of session recordings will be 1 year. Recordings will be available on Lighthall upto 1 year from the date of the last session.

  • The concepts will be mainly covered using PostgreSQL database and PgAdmin IDE but we shall also look at Oracle, MySQL and Microsoft SQL Server whenever required. This bootcamp will be applicable to all popular RDBMS (or relation database) such as MySQL, MSSQL, Oracle, PostgreSQL etc.

Course Contents

Introduction & Pre-Requisites

• Introduction to data, database, schema and SQL.

• Understand why do we use SQL and how data is stored in database

• How to choose a database and IDE

• Installing a database and tools required to get started

Basic SQL

• SQL Commands

  • DDL commands

    • CREATE, ALTER, DROP, TRUNCATE

  • DML commands

    • INSERT, UPDATE, DELETE, MERGE

  • TCL commands

    • COMMIT, ROLLBACK and SAVEPOINT

  • DCL commands

    • GRANT and REVOKE

  • DQL commands

    • SELECT statement

• Data Types

  • String data type like VARCHAR, TEXT etc

  • Integer data type like INT, NUMBER etc.

  • DATE

  • FLOAT / DECIMAL

  • BOOLEAN

  • IDENTITY column (Auto Increment column)

• Constraints

  • PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL and DEFAULT constraints

  • Understanding how foreign key and primary is used to build relationship between tables.

• Normalization in SQL

  • Different normal forms like 1NF, 2NF, 3NF, BCNF

  • Understand ER (Entity Relationship) diagram

• Operators

  • Arithmetic operator

  • Logical operator

  • Comparison operator

  • UNION, UNION ALL operator

• CASE statement

  • Simple case statement as well nested case statement.

• Important SQL clause

  • DISTINCT clause

  • Order by clause

  • Limit / Top clause

• INNER join

  • How to fetch data from multiple tables.

Intermediate SQL

• Group By and Having clause

• Aggregate functions

  • MIN, MAX, AVG, SUM, COUNT

• Order of Execution in SQL Queries

• Sub-Queries

  • Different type of subqueries like scalar subquery, multirow subquery and correlated subqueries

• CTE table / WITH clause

• All type of Joins

  • LEFT Join, RIGHT Join, FULL OUTER Join

  • CROSS Join, SELF Join

• In-built functions

  • String functions like Substring, Position, Coalesce etc.

  • Date functions like Extract, To_Date etc.

• Window functions

  • RANK, DENSE_RANK, ROW_NUMBER, LEAD, LAG

  • FIRST_VALUE, LAST_VALUE, NTH_VALUE, NTILE

• Views in SQL

Advanced SQL

• Recursive SQL Queries

• PIVOT table / CROSSTAB function

• Materialized Views

• Stored Procedure

• User Defined Functions

SQL Case study

• You will be given a scenario with sample dataset

• Understand the scenario and analyze the given data

• Solve multiple SQL problems for the given problem statement