RDBMS & SQL: Fundamentals for Backend Developers

Examine SQL datatypes like int, decimal, char, varchar, date, time, float, binary, blob, enum, xml, json, and uuid, and map them to your rdbms while choosing natural or synthetic keys.

Explore data definition language in sql to define schemas, create and alter databases and tables, manage columns and data types, and understand primary keys and drop operations.

Walks through designing a simple students table from requirements to a create statement, highlighting a primary key with auto increment, varchar fields, date enrollment, and a single-table design.

Create and explore a new database called studentDB, then define a students table, describe its fields and metadata, including types, nullability, and primary key with auto-increment.

Perform hands-on data manipulation language exercises with insert, update, delete, and select on a sample student table, using auto-incremented ids, aliases, limit, and where filtering.

Understand how null represents missing or unknown values in SQL, why null cannot be compared with equals, and how to use is null and is not null to query nulls.

Explore the key query clauses of a typical select statement, including select, from, where, group by, having, order by, and limit with offset for pagination.

Follow how the from clause starts the evaluation. Then where, group by, having, select (including star), order by, and finally limit or offset determine the results.

Explore permanent, derived, temporary, and virtual tables and views in relational databases, and learn how queries yield result sets and create on-the-fly or stored tables.

Create and query views as virtual tables to simplify complex SQL, expose up-to-date results, and selectively hide columns while weighing performance versus temporary tables.

Learn how to group by country code and aggregate population using sum, apply having filters, and order results by total population with optional alias use.

Explore data control language basics by learning grant and revoke commands to manage user permissions on database objects, ensuring minimal necessary access and regular privilege reviews.

Learn how foreign keys link customers and orders in a relational database by using the primary key to cross-reference data and enforce integrity, with nullable options.

Learn how to join multiple tables using inner joins, avoiding cartesian products, by using on clauses to map city to country codes and retrieve related data efficiently.

Master inner and outer joins in SQL, including left, right, and full joins. Learn how these joins return matches, nulls, and nonmatching rows, and how MySQL lacks full join support.

Explore joining with a derived query to compute the number of orders per customer. Use group by with count, alias the result, and join back to the customer table.

Discover subqueries and nested queries, including scalar, row, column, and table subqueries, and see how inner queries serve outer queries via select, from, and where.

Indexes act as a lookup table to speed retrieval and avoid full table scans; primary keys auto-indexed, and composite indexes optimize multi-column queries while trade-offs include slower writes and storage.

Explore SQL triggers and their use in enforcing data integrity and audit trails. See how triggers can automatically respond to insert, update, or delete events, reducing application code.

Learn to verify your sql query performance with explain, interpret execution plans, identify bottlenecks and full table scans, and apply indexing and filtering before consulting a database administrator.

Develop secure database connections by using parameterized queries or prepared statements and input sanitization to prevent SQL injection and cross-site scripting, and store passwords with hashes.

Learn why database transactions are essential for safely transferring money between accounts, ensuring all-or-nothing debits and credits, with start, commit, rollback, and locking.

Explore lock granularities in databases—table, page, and row locks—and how read-only and write-only locks optimize concurrency while maintaining acid guarantees. Learn about transaction save points for rollback within long transactions.

Consolidate foundational relational database and sql knowledge for backend developers, highlighting direct sql usage, orm mapping, and the value of practicing queries.