Database Engineering
All-In-One Package 🐘
Database Engineering I : Mastering Database Fundamentals
Module 1: Querying and Filtering
Module 2: Joins and Aggregates
Module 3: Set Operators and Subqueries
Module 4: Modifying Data and Managing Table
Module 5: Data Types and Constraints
Module 6: Conditions and Transactions
Database Engineering II : Database Problem Solving in Action
Module 1: SQL Essential Challenges I
Module 2: SQL Essential Challenges II
Module 3: SQL Mastery Challenges I
Module 4: SQL Mastery Challenges II
Module 5: SQL Expert Challenges I
Module 6: SQL Expert Challenges II
Database Engineering III : Advanced Database Mechanisms
Module 1: Transactions, Fast and Slow Queries
Module 2: Concurrency Control and Isolation Levels
Module 3: MVCC and Performance Optimization
Module 4: Normalization and Functional Dependencies
Module 5: Indexing and Partitioning Strategies
Module 6: Database Systems and NoSQL Database
“InFocus.uz — Life changing education”
Database Engineering I : Mastering Database Fundamentals
Module 1: Querying and Filtering
1- Sample database (15 tables)
2- SELECT
3- ORDER BY
4- SELECT DISTINCT
5- WHERE
6- LIMIT
7- FETCH
8- IN
9- BETWEEN
10- LIKE
11- IS NULL
12-Assignment I
13-Assignment II
Module 2: Joins and Aggregates
1- JOINS
2- INNER JOIN
3- LEFT OUTER JOIN
4- LEFT ONLY
5- RIGHT OUTER JOIN
6- RIGHT ONLY
7- SELF JOIN
8- FULL JOIN
9- FULL OUTER JOIN
10- ONLY OUTER
11- CROSS JOIN
12- NATURAL JOIN
13- GROUP BY
14- HAVING
15- GROUPING SETS
16- ROLL UP
17- CUBE
18- ANALYTIC FUNTIONS
19- ROW_NUMBER, RANK, DENSE_RANK
20- FIRST_VALUE, LAST_VALUE
21- LAG, LEAD
22- Assignment I
23- Assignment II
Module 3: Set Operators and Subqueries
1- UNION
2- UNION ALL
3- INTERSECT
4- EXCEPT
5- SUBQUERY
6- ANY
7- ALL
8- EXISTS
9-Assignment I
10-Assignment II
Module 4: Data Types and Constraints
1- INSERT
2- UPDATE
3- UPDATE JOIN
4- DELETE
5- UPSERT
6- DATA TYPE
7- CREATE TABLE
8- CTAS
9- VIEW
10- Change Table Structure
11- Change Table Name
12- Add Column
13- Remove Column
14- Change Column Datatype
15- Change Column Name
16- Remove Table
17- Temporary Table
18- TRUNCATE
19-Assignment I
20-Assignment II
Module 5: Data Types & Constrains
1- BOOLEAN
2- CHAR, VARCHAR, TEXT
3- NUMERIC
4- SERIAL
5- DATE, TIME, TIMESTAMP
6- PRIMARY KEY
7- FOREIGN KEY
8- CHECK CONSTRAINT
9- UNIQUE CONSTRAINT
10- NOT NULL CONSTRAINT
11-Assignment I
12-Assignment II
Module 6: Conditions and Transactions
1- CASE
2- COALESCE
3- NULLIF
4- CAST
5- WITH
6- SCHEMAS
7- TRIGGERS
8- Assignment I
9- Assignment II
10- Assignment III
11- The Real Problems Start Now
Database Engineering II : Mastering Problem Solving in Action
Module 1: SQL Essential Challenges I
Easy 1- Customers Who Never Order
Easy 2- Delete Duplicate Emails
Easy 3- Game Play Analysis I
Easy 4- Customer Placing the Largest Number of Orders
Easy 5- Actors and Directors Who Cooperated At Least Three Times
Easy 6- User Activity for the Past 30 Days I
Easy 7- Reformat Department Table
Easy 8- Students and Examinations
Easy 9- List the Products Ordered in a Period
Easy 10- Top Travellers
Easy 11- Group Sold Products By The Date
Easy 12- Customer Who Visited but Did Not Make Any Transactions
Easy 13- Average Time of Process per Machine
Easy 14- Fix Names in a Table
Easy 15- Daily Leads and Partners
Easy 16- Biggest Single Number
Easy 17- The Number of Employees Which Report to Each Employee
Easy 18- Find Total Time Spent by Each Employee
Easy 19- Recyclable and Low Fat Products
Easy 20- Primary Department for Each Employee
Easy 21- Assignment I
Easy 22- Assignment II
Easy 23- Assignment III
Easy 24- Assignment VI
Easy 25- Assignment V
Module 2: SQL Essential Challenges II
Easy 1- Game Play Analysis II
Easy 2- Consecutive Available Seats
Easy 3- Shortest Distance in a Line
Easy 4- Friend Requests I: Overall Acceptance Rate
Easy 5- Product Sales Analysis II
Easy 6- Project Employees II
Easy 7- Sales Analysis I
Easy 8- Sales Analysis II
Easy 9- Reported Posts
Easy 10- User Activity for the Past 30 Days II
Easy 11- Number of Comments per Post
Easy 12- Weather Type in Each Country
Easy 13- Find the Team Size
Easy 14- Ads Performance
Easy 15- Students With Invalid Departments
Easy 16- NPV Queries
Easy 17- Create a Session Bar Chart
Easy 18- Friendly Movies Streamed Last Month
Easy 19- Customer Order Frequency
Easy 20- Fix Product Name Format
Easy 21- Assignment I
Easy 22- Assignment II
Easy 23- Assignment III
Easy 24- Assignment VI
Easy 25- Assignment V
Module 3: SQL Mastery Challenges I
Medium 1 - Second Highest Salary
Medium 2- Nth Highest Salary
Medium 3- Rank Scores
Medium 4- Consecutive Numbers
Medium 5- Department Highest Salary
Medium 6- Game Play Analysis IV
Medium 7- Friend Requests II: Who Has the Most Friends
Medium 8- Customers Who Bought All Products
Medium 9- Product Sales Analysis III
Medium 10- Market Analysis I
Medium 11- Product Price at a Given Date
Medium 12- Immediate Food Delivery II
Medium 13- Monthly Transactions I
Medium 14- Restaurant Growth
Medium 15- Movie Rating
Medium 16- Assignment I
Medium 17- Assignment II
Medium 18- Assignment III
Medium 19- Assignment IV
Medium 20- Assignment V
Module 4: SQL Mastery Challenges II
Medium 1- Get Highest Answer Rate Question
Medium 2- Unpopular Books
Medium 3- New Users Daily Count
Medium 4- Highest Grade For Each Student
Medium 5- Active Businesses
Medium 6- Monthly Transactions II
Medium 7- Team Scores in Football Tournament
Medium 8- All People Report to the Given Manager
Medium 9- Find the Start and End Number of Continuous Ranges
Medium 10- Number of Trusted Contacts of a Customer
Medium 11- Customers Who Bought Products A and B but Not C
Medium 12- Evaluate Boolean Expression
Medium 13- Countries You Can Safely Invest In
Medium 14- The Most Recent Three Orders
Medium 15- The Most Frequently Ordered Products for Each Customer
Medium 16- Assignment I
Medium 17- Assignment II
Medium 18- Assignment III
Medium 19- Assignment IV
Medium 20- Assignment V
Module 5: SQL Expert Challenges I
Hard 1- Department Top Three Salaries
Hard 2- Human Traffic of Stadium
Hard 3- Trips and Users
Hard 4- Median Employee Salary
Hard 5- Find Median Given Frequency of Numbers
Hard 6- Find Cumulative Salary of an Employee
Hard 7- Average Salary: Departments VS Company
Hard 8- Students Report By Geography
Hard 9- Assignment I
Hard 10- Assignment II
Module 6: SQL Expert Challenges II
Hard 1- Tournament Winners
Hard 2- Number of Transactions per Visit
Hard 3- Get the Second Most Recent Activity
Hard 4- Total Sales Amount by Year
Hard 5 - Find the Quiet Students in All Exams
Hard 6- Hopper Company Queries I
Hard 7- Hopper Company Queries II
Hard 8- Hopper Company Queries III
Hard 9- Assignment I
Hard 10- Assignment II
Database Engineering III : Advanced Database Mechanisms
Module 1: Transactions, Fast and Slow Queries
1- Transaction Management, TCL
2- Transaction (begin, commit)
3- Transaction (begin, rollback)
4- Transaction (lock)
5- Fast and Slow Query I
6- Fast and Slow Query II
7- ACID Properties (Atomicity)
8- ACID Properties (Consistency)
9- ACID Properties (Isolation)
10- ACID Properties (Durability)
Module 2: Concurrency Control and Isolation Levels
1– Concurrency Control I (Lost updates)
2– Concurrency Control I (Dirty reads)
3– Concurrency Control II (Serializability)
4– Concurrency Control II (Recoverable schedules)
5- Transaction Isolation Levels (Read Uncommitted)
6- Transaction Isolation Levels (Read Committed)
7- Transaction Isolation Levels (Repeatable Read)
8- Transaction Isolation Levels (Serializable)
9- Concurrency Control using Locks (Shared Lock)
10- Concurrency Control using Locks (Exclusive Lock)
11- Concurrency Control using Locks (Two-Phase Locking Protocol)
Module 3: MVCC and Performance Optimization
1- DB MVCC Concept I (Snapshot isolation)
2- DB MVCC Concept I (Versioning)
3- DB MVCC Concept II (Write Skew)
4- DB MVCC Concept II (Optimistic Locking)
5- DB MVCC Concept II (Pessimistic Locking)
6- Database Connection Pool (Connection pooling strategies)
7- Database Connection Pool (Performance impact)
Module 4: Normalization and Functional Dependencies
1- Functional Dependency (Types of dependencies)
2- Functional Dependency (Inference rules)
3- Normalization I (1NF)
4- Normalization I (2NF)
5- Normalization I (3NF)
Module 5: Indexing and Partitioning Strategies
1- Database Indexing (Indexing fundamentals)
2- Database Indexing (Clustered vs non-clustered)
3- B-Tree Indexing I (B-Tree structure)
4- B-Tree Indexing I (B-Tree properties)
5- B-Tree Indexing II (Operation: Insert)
6- B-Tree Indexing II (Operation: Delete)
7- B-Tree Indexing II (Operation: Search)
8- B-Tree Indexing III (Performance considerations)
9- B-Tree Indexing III (Performance trade-offs)
10- Partitioning, Sharding, Replication (Horizontal partitioning)
11- Partitioning, Sharding, Replication (Vertical partitioning)
12- Partitioning, Sharding, Replication (Distributed databases)
Module 6: Database Systems and NoSQL
1- DBMS — PostgreSQL Architecture (Process model)
2- DBMS — PostgreSQL Architecture (Memory structure)
3- System Administration (Performance tuning)
4- System Administration (Security, user roles)
5- Backup and Recovery (Point-in-time recovery)
6- Backup and Recovery (WAL logs)
7- NoSQL — Document Database (JSON-based storage)
