SQL & Database Design A-Z™: Learn MS SQL Server + PostgreSQL

Install PostgreSQL and PgAdmin 4 on Windows using the EnterpriseDB installer, select the latest 64-bit release, and configure the password and default port.

Install Microsoft SQL Server on Windows using the Express edition, verify prerequisites such as Windows 8 or 10, then install and launch SQL Server Management Studio to interact with databases.

Upload a consumer complaints dataset to a database, run basic queries to count same-day postings, and filter by New York or California with credit in product and late in issue.

Learn to upload the consumer complaints dataset into PostgreSQL using pgAdmin, create a database and table, import CSV data, fix file permissions, and leverage SQL for efficient data handling.

Explore pgAdmin 4, navigate databases and public schemas, run queries with the query tool, use explain to see how data is connected, and save or load queries.

Explore Microsoft SQL Server Management Studio to navigate Object Explorer, create new queries, execute them, and leverage drag-and-drop and shortcuts for efficient, well-organized work.

Master the basics of SQL with fundamental queries, including select statements, where conditions, and/or clauses, wildcards, and comments, essential for this course and your career.

Understand theory of the select query, the SQL statement with mandatory select and from clauses, and learn to pull specific columns or all data with select star and where clauses.

Learn to write your first PostgreSQL queries in Pgadmin 4, using select * and targeted selects (product name, issue; company, zip code, state) on the consumer complaints table.

Use and/or clauses in PostgreSQL to refine queries by multiple conditions, such as state filters and tags for service member status with timely response in consumer complaints.

Explore using wildcards % and _ in PostgreSQL with like and not like to match data patterns, and apply lower or upper for case-insensitive searches.

Learn how to use comments in PostgreSQL, including single-line -- and multi-line /* */ syntax in pgadmin, to annotate code and aid debugging.

Explore wildcards in MS SQL using the like operator to refine queries with percent and underscore, including case-insensitive behavior, and build precise results from the consumer complaints data.

Learn to use comments in Microsoft SQL Server to debug and annotate code, with block comments (/* ... */) and line comments (--), plus tips for collaboration and clarity.

Analyze a two-table console games and console dates dataset to compute global sales by region and to order titles by platform and year, with data cleaning and missing data handling.

Explore data type conversion in SQL using the cast function to convert integers to character or date, optimize storage, and enable advanced data processing.

Upload two CSV datasets into a new SQL Server database using Microsoft SQL Server Management Studio, importing raw console games and raw console dates into final console_games and console_dates.

Learn to create a global sales column and compute total sales from NA, EU, JP, and other sales; compute North American sales percent with zero-division safeguards.

Explore the varchar data type in MS SQL Server and manipulate text in the console games table. Apply left, right, and reverse functions to extract and inspect game names.

Explore implicit data conversions in SQL Server using cast and convert, such as changing game year from integer to float. Understand temporary query results versus permanent schema changes.

Learn how to handle null values in MS SQL Server by deleting, updating, or filling in null data using practical examples on the console games table.

Explore the fundamentals of database theory and learn about candidate keys, primary keys, foreign keys, and the differences between structured and unstructured data in relational models.

Explore the elements of a database, including tables, schemas, catalogs, and extensions, then examine relations, attributes, tuples, degree, cardinality, domains, and primary, foreign, or super keys.

Learners explore relational keys in SQL, including super keys, candidate keys, primary keys, and foreign keys, with examples using employee ID, first name, last name, and platform name.

Explore how joins connect multiple tables in relational databases to form the full picture. Begin with theoretical tutorials on joins, then apply skills in a practical veterinarian clinic data challenge.

Explore how duplicates in the join column of order numbers affect inner joins between orders and itemized orders, showing how multiple matches replicate rows and reveal paid versus unpaid status.

Learn how joining on multiple fields avoids inflated sales by using order number and store together to link table A and table B, enabling accurate customer totals.

Upload csv files to a database, join pets with owners, map procedures to descriptions, handle nationwide vs clinic-specific datasets, and generate a two-column owners vs procedure prices table.

Upload the pet dataset into a PostgreSQL database using pgadmin 4, create the pets and owners tables, paste the upload code, and verify data with select queries.

Perform a left join to connect pets and their owners via the foreign key owner_id, preserving all pets while selecting precise fields from each table.

Discover practical tips for joins, including using table and column aliases to simplify complex queries, and understand how left and right joins relate for cleaner SQL.

Learn how an inner join links the clinic's local pets to the global procedure history, contrasting it with left and right joins and showing how non-matching rows are discarded.

Join the procedure history and procedure details tables on multiple fields, specifically procedure type and procedure sub code, to reveal complete procedure descriptions and prices.

Explore complex joins across pets, procedure history, and procedure details using inner joins and a left join to reveal which pets had procedures, and show procedure descriptions and costs.

Learn how to clean up joins by removing redundant join columns and selecting only needed fields, using an intermediary table effectively to obtain the final cost data.

Master the cross join in SQL, the Cartesian product of two tables, an advanced topic, and learn how to generate all possible combinations without an on clause.

Upload CSV datasets into Microsoft SQL Server using the Import Data Wizard, creating raw tables and adjusting text qualifiers. Convert raw data into working tables for the pets dataset.

Learn to perform a left join between pets and owners in MS SQL, linking on owner ID to show each pet with its owner.

Learn how to perform an inner join between the pets table and the procedures history table, and compare inner, left, and right joins with practical MS SQL examples.

Full outer join is demonstrated with a pets table and procedures history table, showing unmatched rows left blank and how it compares to left, right, and inner joins.

Join two tables on multiple fields to enrich the procedures history with detailed procedure information. Use a composite key of procedure type and procedure sub code for accuracy.

Learn to perform complex joins in ms sql by linking pets, procedure history, and details to retrieve procedure costs and descriptions.

Clean up joins in MS SQL by trimming extraneous columns, keeping fields from pets, procedures history, and procedures details, and understand the role of intermediary tables in complex joins.

Learn how to create a PostgreSQL database using pgadmin four, including naming, connecting, and verifying schemas and tables. The session explains database versus data operations and shows GUI-generated SQL.

Learn to create a table in PostgreSQL with proper syntax, data types, and constraints, using a movies table example to illustrate primary keys and not null in pgAdmin.

Insert data into existing tables with insert into values for four columns, then update specific rows using where conditions to honor not null constraint and preserve data quality.

Use alter table to set country default USA and add a directors column, then update specific rows with proper constraints and case handling to reflect Christopher Nolan for Interstellar.

Delete specific rows, drop and alter columns, understand drop cascade, and remove databases using pgadmin to manage table structures and constraints.

Create your first database in SQL Server Management Studio by right-clicking databases and selecting new database, then name it and set file properties, owner, and version level.

Learn to create a sql table named movies with columns for movie name, year, country, and genre, enforce not null and a composite primary key on movie name and year.

Insert data into SQL tables using insert into and values to add multiple rows, resolve primary key violations, and preserve data integrity while exploring update and scripting options.

Learn to alter tables in MS SQL Server by adding and dropping a director column in the movies table, then update rows and design robust schemas.

Discover database design fundamentals, including data structure, connections, and joins, then master normalization with first, second, and third normal forms through practical OLTP examples.

Explore how prime and non-prime attributes define candidate keys and normalization, using examples like employee ID, tournament year, and manufacturer model.

Explore the first normal form (1NF) in database design, ensuring no duplicate rows and single-valued cells. See practical examples and a mnemonic to remember its criteria.

Learn to upload a dataset to a PostgreSQL database with PgAdmin, create an OLTP database, import a transactions table from a csv using provided code, and verify with a select.

Explore normalizing a transactions table to first normal form by checking for unique rows and atomic values, and verify there are no duplicates using count and distinct.

Apply third normal form analysis to a two-table database by verifying non-prime attributes are non transitively dependent on candidate keys in customers and transactions, and decide when to split tables.

Apply third normal form normalization to a database by isolating transitive dependencies into an items table, linking via item codes, and ensuring a fully normalized schema.

Upload the dataset into MS SQL Server, create an OLTP database, and import data via the flat file wizard. Then convert raw data to proper types with the provided code.

Inspect a transactions table to verify first normal form by ensuring atomic values and no duplicates, using count and distinct queries.

Explore second normal form by identifying candidate keys and prime attributes in a transactions table. Learn how non-prime attributes must depend on whole key and how to split for 2NF.

Learn how to modify an MS SQL Server database to achieve second normal form by moving customer-specific columns from transactions to a new customers table with a foreign key.

Explore third normal form in practice by analyzing customers and transactions tables, identifying non-prime attributes and transitive dependencies, and learning how to split data to achieve 3NF.

Celebrate your completion of the SQL & database design A-Z course with a travel-inspired finale from Tasmania. Encourage learners to rate and review to help others discover the course quality.