Complete SQL and Databases Bootcamp

Understand that a database is a collection of data with tools to access and manipulate it, and learn to install software to turn any computer into a database.

Learn how databases are managed with a database management system and relational databases, explore CRUD operations, and understand the structured query language (SQL) as the language to interact with data.

Explore how databases replace spreadsheets by using sql statements to manage customers, orders, and products in an amazon-like store, with an interactive playground to build intuition.

Install a database management system like PostgreSQL and migrate data into customers, products, and orders tables with unique IDs to support relational links and scalable data management.

Explore the five data models—relational, document, key-value, graph, and wide-columnar databases—through examples like PostgreSQL, MongoDB, Redis, Neo4j, and Cassandra, and discuss when to use each.

Learn to interact with databases, store and query data, and decide when to use a key-value store, Mongo DB, or PostgreSQL, then install database software to begin adding data.

Explore how sql is a language to talk to databases and retrieve data via queries. Understand why large systems need databases for structured data storage beyond notepad and Excel sheets.

Explain what happens versus how it happens in declarative and imperative programming, using examples like select star from users and a ham sandwich. Highlight declarative simplicity and productivity.

Explore the history of sql, from its original name sequel to structured english query language, and how ibm engineers Edgar Chod, Donald Chamberland, and Raymond Boyce created its first version.

Explore how SQL standards create consistency across databases through standardization and how PostgreSQL, Microsoft, and Oracle add extras on top, tracing the history from the 1980s to 2011.

Trace the shift from file processing systems to databases, a structured model using columns and rows that links data with a shared schema, reducing redundancy and update gaps.

Adopt a database oriented approach that unifies data through a database management software and a relational model, using structured query language to store and relate information.

Discover how the relational model replaces parent-child structures with table-based data, using unique identifiers and linking tables to model relationships, all managed by database management software.

Explore the relational model for storing data, and learn the core terms—relation, relation schema, attribute, degree, cardinality, tuple, column, key, domain, tables, and relation instance—and how databases enforce these rules.

Understand tables in the relational model. Build a named table with columns and rows that store data, including id, first name, last name, gender, and date of birth.

Explore how columns define a table in a database, covering attributes, degree of the relation, and domain constraints that govern data types like date of birth and sex.

learn how rows, or tuples, populate a table by following column constraints and data types, while understanding cardinality and the relational model.

Learn how the relational model uses primary keys to uniquely identify data and foreign keys to link tables, creating solid relationships across a database.

Classify databases as OLTP or OLAP in a hands-on exercise, identifying transactional databases for daily operations, analytical platforms for future insights, and databases used for executive reporting and user tracking.

Explore why PostgreSQL is widely adopted, using Valentina DBE as a cross-platform open-source UI to manage PostgreSQL, and note its widespread use in cloud services.

Set up postgres on Mac by choosing an installer or a drag‑and‑drop postgres app and initializing with your Mac username and password. Manage databases with Vallentine studio.

Learn to set up Linux for PostgreSQL, including Ubuntu repository steps, sudo usage, and installing PostgreSQL 11. Then install Valentine Studio, connect to local PostgreSQL, and prepare databases for import.

Transform imposter syndrome into momentum by embracing hard work, practicing, and gradually gaining experience, then solidify your SQL and database skills by teaching others on Discord.

Explore the SQL categories—DDL, DML, and DCL—and learn how the SELECT statement retrieves data to view and query your databases.

rename columns in a select query to present data more readably using aliases like birthday and first name for clear subset views.

Explore aggregate functions such as count, min, max, average, and sum on data to reveal insights. See how the employees table in Valentina Studio computes highest salary and total salaries.

Identify and fix common SQL mistakes in SQL queries, including misspellings, incorrect punctuation, and misused quotes, to improve reliability and productivity.

Master data filtering by using the where clause and layering multiple criteria to extract precise subsets, from simple gender queries to complex age and salary ranges.

Filter data from the state of Oregon and the state of New York to count female customers, using where clauses, boolean logic, and parentheses for correct operation order.

Discover how to use the not keyword to exclude results and build powerful filters with and, or, and parentheses, including range queries.

Explore how comparison operators power range filtering in SQL, using equal to, not equal to, greater than, less than, and their inclusive variants to craft precise conditions.

Explore how logical operators and comparison operators filter data, and learn the order of operations that govern select, from, and where.

Explain operator precedence in SQL, showing how parentheses, arithmetic, and boolean operators in where clauses determine evaluation order and associativity.

Learn how to detect and distinguish null values from empty strings, understand the implications of nullable fields, and deliberately manage nulls to avoid query bugs.

Learn how to use the is operator to filter null values, distinguish optional from required data, and write defensive SQL queries that safely handle nulls.

Learn to handle null values with the coalesce function, substituting missing data with a fallback. See how coalescing across columns and aliasing keeps names intact for clean calculations.

Use between and as a shorthand for inclusive range filtering across dates and numbers, improving readability. Between depends on argument order and is the same as using >= and <=.

Learn how to filter with the in keyword to match multiple values, such as employee numbers, by using where column in (values) to build efficient SQL queries.

Explore why date handling is hard, distinguish GMT from UTC, and learn to store dates in UTC while using session-level time zone settings in PostgreSQL.

Learn how to set the time zone for your PostgreSQL user to UTC, using alter user, show time zone, and reconnecting sessions to ensure consistent date and time views.

Understand how timestamps store date, time, and time zone in Postgres, including ISO 8601 and UTC. Learn when to use timestamp with time zone versus timestamp without time zone.

Explore date and time operations in PostgreSQL, using now and current date, plus to_char formatting with modifiers to extract year, month, day, and week numbers.

Learn to calculate date differences and cast strings to dates in PostgreSQL, generating intervals and formatted dates with to_date and date casting in ISO 8601 format.

Explore calculating age in SQL with the age function, casting inputs to date to yield years, months, and days, and measuring the interval between two dates.

Extract the day, month, and year from a date using the extract keyword with parentheses, and use date trunk (truncate) to round dates to year, month, or day for queries.

Master interval syntax to talk about time readably, add or subtract dates with plus or minus interval, and extract components like years, months, days, hours, and minutes.

Explore the distinct keyword that removes duplicates by evaluating one or more columns, keeping one row per unique combination, and note outcomes vary with multiple columns.

Explore how to sort data with the order by clause, applying ascending or descending order to single or multiple columns, including expressions like the length of a name.

Learn how to perform multi-table selects by linking employees and salaries with primary and foreign keys to create meaningful joins.

Learn to perform a self join by joining a table to itself. Use a foreign key that references the same primary key to display each employee's supervisor name.

Explore outer joins in SQL, including left and right outer joins, contrast with inner joins, and handle nulls with coalesce while counting non-managers and tracking salary raises.

Explore using the using keyword for simple joins in sql, compare it with on, and learn when matching primary keys to foreign keys across tables like employees and departments.

Group by splits data into logical buckets, enabling per-group aggregates like counts by department or salaries by department. Apply the split-apply-combine approach and correct clause order for department-level insights.

Explore the having keyword after group by to filter groups using aggregates, compare it with where for row-level filtering, and build complex queries with joins and counts.

Sort grouped data using order by after group by, sorting by department name or by count of employees, and apply order by to analytical functions for ranking results.

Explore the group by mental model, grouping logic, and max aggregation; learn how groups structure data like salary by employee, and recognize the limitations of single queries.

Explore how grouping sets combine multiple groupings in one query, replacing unions for totals and per-group sums across products and order lines.

Explore advanced grouping with group by, having, grouping sets, and rollups, then learn to compare each salary to its department average using window functions.

Apply a window function over a subset of data with the over keyword. See max salaries shown beside each row and how filters versus limits affect the window.

Learn how to use partition by with window functions to compute the average salary by department, applying the over clause across partitions and observing department-level insights.

Discover how order by in window functions alters the frame, producing cumulative counts that differ from partitioned results, with practical framing examples.

Explore the last_value window function with partition by category and a frame of unbounded preceding to unbounded following to find the highest price in each category.

Learn how to compute a customer's cumulative spend by partitioning by customer id and ordering by order id to produce a running total of net amounts from orders.

Master the ROW_NUMBER window function to rank products by price within each category, using partition by category and order by price, and learn that framing does not affect numbering.

Learn how the null if function returns null when two values match and otherwise returns the left-hand side, enabling simple null handling and avoiding divide by zero.

Learn how views store and query results of queries, including non materialized views that rerun on demand and materialized views that store data on disk and update with table changes.

Learn to use views to simplify complex salary queries, including creating a last salary change view and querying it like a table, comparing nonmaterialized and materialized views with joins.

Learn how indexes speed queries and how to choose between single or multi-column, unique, and partial indexes. Discover when to create or drop indexes to balance performance and update costs.

Explore when to use different index algorithms in PostgreSQL, including default, hash, gin, and gist, and measure their impact on query performance with explain analyze.

Explore subqueries, i.e., inner queries, used to build complex filters and calculations in SQL, especially in where, from, and having clauses, with single-column or single-record results and joins comparison.

Compare subqueries and joins: subqueries can stand alone and may return a single result or a rowset, while joins always return a rowset and require a matching condition.

Apply subquery guidelines: enclose in parentheses and place on the right of the comparison operator, and explore single row, multiple row, multiple column, correlated, and nested types.

Explore practical subqueries in sql: compare each employee's age to the average, use correlated subqueries for titles by date, and compare with joins for the most recent salaries.

Learn how correlated subqueries and joins affect performance when querying salaries, using where clauses, from clauses, and views, and compare execution plans with explain analyze.

Explore the types of databases in a DBMS, contrasting regular databases with template databases, and learn how templates blueprint database creation and influence PostgreSQL setup behind the scenes.

Explore how template zero acts as a safety backup for template one, the default blueprint used to create new databases, and learn how template choices affect database creation.

Create a database using the create database command, with options for owner, template, encoding, and connection limit, and learn defaults like template zero one, utf eight, and 100 connections.

Organize database objects with PostgreSQL schemas to separate tables, views, and indexes into logical boxes such as sales, payroll, and HR, while defaulting to the public schema.

Explore how role attributes shape privileges in sql databases, from login and super user rights to create database and role permissions, and learn password encryption and object access controls.

Learn to create users and roles, configure login and password encryption, and adjust PostgreSQL authentication settings via pg_hba.conf and postgresql.conf for secure local and interactive access.

Adopt best practices for role management by applying the principle of least privilege, creating granular roles, and avoiding default super user access to secure data.

Learn how PostgreSQL data types act as constraints on fields, ensuring data integrity through core types like boolean, with true, false, or null values and smart conversions.

Model a database with an entity relationship diagram before building tables, outlining entities like courses, students, and enrollment; apply crow's feet notation and define primary and foreign keys.

Create the student table from the data model, define uid primary key with a default uid generate v4, and enforce not null constraints, while applying consistent naming conventions and extensions.

Learn how to define table constraints to constrain multiple columns, including multi-column primary keys, checks and foreign keys, and know when to apply column vs table level constraints.

Explore universally unique identifiers uid and the uid extension that generates unique primary keys, and weigh pros and cons versus auto-incrementing numbers for growing databases.

Create a full relational schema by building students, enrollments, subjects, teachers, and courses, applying primary keys, foreign keys, and a custom domain type to enforce constraints.

Learn how to insert data into a prepared database model—students, teachers, courses, subjects, enrollment, and feedback—while exploring user journeys and design flaws.

Create a course by defining a subject, linking a teacher, and setting a description. Learn to enforce data integrity with constraints, perform migrations, and manage updates.

Enroll a student into a course using student ID, course ID, and enrollment date, then append feedback to the course’s feedback array, noting potential integrity issues.

Establish a robust backup and disaster recovery plan with tested procedures. Choose backup types—full, incremental, differential, and transaction logs—and set storage and retention policies.

Explore how the way you generate a dump influences restoration, compare graphical user interfaces to command-line scripts, and diagnose syntax errors when restoring a Postgres database.

Understand how transactions coordinate concurrent database access to maintain consistency. See how begin, commit, rollback, and locking enforce acid properties (atomicity, consistency, isolation, durability) for durable updates.

Investigate sql and databases by solving a mystery using an employee database and a security camera database to narrow down a data breach to a specific period.

Analyze on-site data access at Kako Corp.'s offices and how cars captured on security footage help identify drivers or passengers as culprits.

Analyze vehicle location histories near the office to identify rides on the breach day, then use a suspected rights view and joins to narrow to 89 drivers and owners.

Investigate clues by comparing employee data with drivers and riders to uncover an inside-job collaboration between two people and reveal common links.

Solve a data theft mystery by interrogating suspects and revealing the motive of a disgruntled employee. Recover stolen data and learn how SQL and database skills streamline investigations.

Move from chaos to structure by mastering system design in phase three of software development lifecycle. Translate stakeholder goals into high-level diagrams and compare top-down and bottom-up database design approaches.

Explore entity relationship modeling to design a top-down ER diagram, defining entities and relationships, and learn the steps to conceptualize Drive Me Academy’s database.

Determine entities to transform chaos into a solid database model by following steps from entity relationship diagrams, ensuring singular, identifiable objects with identifiers.

Explore diagramming tooling and the UML standard with Lucidchart, comparing browser-based options and cross-platform apps for creating diagrams.

Explore attributes as atomic, single-value properties of entities, corresponding to table columns; distinguish atomic from single vs multi-valued fields, and review primary and foreign keys within relational models.

Extend the relational model by revisiting relational schema, relation instance, and the various keys beyond primary and foreign keys, linking theory to practical database design.

Learn how super keys and candidate keys identify rows, then select a single primary key to simplify data modeling, and implement foreign keys to link related tables for one-to-one relationships.

Learn the SQL key taxonomy, from super keys and primary keys to foreign keys, compound keys, composite keys, surrogate keys, and alternate keys.

Explore how to determine entity attributes in a relational model, balance attributes and relationships, define keys and foreign keys, and apply naming conventions for students, instructors, lessons, and exams.

Explore how to define entities, attributes, primary keys, and foreign keys, and visualize one-to-one, one-to-many, and many-to-many relationships using crow's feet diagrams to build robust relational databases.

Define concrete one-to-many and one-to-one relationships for Drive Me Academy, linking schools, instructors, students, lessons, exams, vehicles, and maintenance, with soft constraints to guide rules.

Understand why many to many relationships complicate relational schemas and learn to resolve them with intermediate entity types to avoid insert, update, and delete anomalies, using book author and enrolment.

Learn to apply top-down design by using subject areas to divide entities into logical groups, enabling distributed work, iterative refinement, and complete system requirements in database design.

Apply top down design with the five steps to build a painting reservations system, solving entities, relationships, and a many-to-many through a reservation detail intermediary.

Identify and prevent modification anomalies, including update, delete, and insert, by using normalization, separating branches from customers, and applying bottom-up and top-down design with entity-relationship models.

Apply normalization to design a database with minimal anomalies and redundancy by analyzing functional dependencies, normal forms, and how bottom-up and top-down methods interlink with entity relationship diagrams.

Explore functional dependencies in database design, showing how a determinant uniquely determines another attribute, with examples like employee number and birthdate, and the role of primary and composite keys.

Explore functional dependencies with salary, employee number, and project idea, and see how their combination supports normalization to avoid redundancy and anomalies.

Explore normalization from bottom up to three point five, linking functional dependencies to entity design and reducing redundancy and anomalies, while noting four and five and the sixth normal form.

Learn how to move from zero normal form to first normal form by removing repeating groups and non-atomic attributes, isolating entities, and recognizing primary keys to reduce redundancy and anomalies.

Move from the first to the second normal form by ensuring all non-key attributes are fully functionally dependent on the primary key, using a book and author example.

Move from 2NF to 3NF by identifying and removing transitive dependencies and applying functional dependencies, while recognizing when to adopt boyscout normal form in practical databases.

Develop a strategic plan to improve database performance, security, backups, and access control, and guidance on selecting the right database model for business needs.

Learn how replication duplicates data across multiple machines in different locations to prevent failures, achieve eventual or synchronous vs asynchronous consistency, and enable horizontal scalability.

Differentiate backups from replication, noting real time replication versus nightly dumps stored in multiple locations. Build a backup strategy to protect against disasters and enable reverting to a previous state.

Apply database security best practices by enforcing least privilege, restricting access to authorized users, and monitoring logs to prevent malware, data loss, and injections.

Explore injections and sql injection, see how untrusted input can drop tables or bypass logins, and learn to sanitize input, use prepared statements, and parameterized queries to prevent attacks.

Explore data management best practices, including encrypted backups, selective encryption of sensitive user data, and secure password storage via hashing, comparison, and salt rounds in a PostgreSQL context.

Compare relational and NoSQL databases, focusing on PostgreSQL, MongoDB, and MySQL, with guidance on when to use each. Highlight data integrity, normalization, and ACID trade-offs for schema design.

Discover the top databases you’ll encounter, led by PostgreSQL as the main relational choice, alongside MongoDB, Firebase, ElasticSearch, Redis, DynamoDB, DocumentDB, and S3.