MTA 98-364 Database Fundamentals Preparation Course

Learn how normalization improves data integrity and reduces redundancy in relational databases by applying first through third normal forms, splitting tables, and using junction tables for many-to-many relationships.

Explore how normalization reduces data redundancy and ensures data integrity through constraints. Identify data type, primary key, foreign key, unique key, nullability, check, and default constraints for valid data.

Explore the different Sequel Server data types and how they determine storage for table columns. Learn char and varchar, unicode, temporal, numerical, and other categories, plus size and encoding implications.

Learn to install and connect to SQL Server Management Studio, configure authentication, manage logins and databases, restore a North Wind backup, and prepare for querying databases.

Learn how to apply aliases to database objects using the as clause to create correlation names, prevent ambiguous references, and name columns and expressions such as net amount.

Learn how to filter data with the where clause to retrieve only relevant rows, using comparison and range operators, and combining conditions with and, or, and not.

Use the order by clause to sort query results by one or multiple columns. Explore ascending and descending orders, and how the clustered index keys set the stored data order.

Group data using the group by clause and apply aggregate functions. Explore count, sum, avg, max, and min across groups and see net amount calculations.

Explore set operators for combining query results: union, union all, intersect, and except, using distinct to remove duplicates, and ensure compatible data structures across queries.

Learn how to update data using transact sql with update, set, and where clauses to modify single or multiple rows, including hire dates, departments, and salaries.

Delete data with a where clause to remove a single row by its primary key. Compare this with truncate, which resets the identity and removes all rows without per-row logging.

Learn to create a database and tables in SQL Server using both graphical interface and transact sequel script, with primary keys, identity, constraints for a music catalog.

Explore database views as named select statements that act like virtual tables, do not store data, enabling security filtering, data integrity, and simplified queries in SQL Server.

Explore built in and user defined database functions in SQL Server, including scalar, aggregate, and table valued functions, with practical creation, invocation, and alteration examples.

Explore how database triggers automatically enforce business rules, support integrity checks, and enable history and audit trails by responding to DDL and DML events, with after and instead of triggers.

Explain stored procedures by grouping one or more transact-SQL statements into a single database object, boosting performance by reducing calls and improving security via execute permissions and parameterized inputs.

Explore how database indexes boost query performance by using clustered and non clustered indexes, organized as balanced trees, to avoid full table scans and optimize data retrieval.

Learn how to secure a sql server database across server, database, and schema scopes using secure rebels, principals, and permissions; master grant, revoke, and deny, and manage logins with ssms.