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.

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 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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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 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 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 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.

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.

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

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.

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.

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.

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.

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.

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.

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 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.

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.

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.

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

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.

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 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.

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.

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.

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