MTA Database Fundamentals (98-364) Cert Prep: 1 Core Concepts

Introduction

• An introduction to database foundations
• What you should know
• Using the exercise files

1. Database Core Concepts

• What is a database?
• Storing data efficiently
• Relational database management systems
• The client-server model

2. Set Up a Database Playground

• Purpose of the playground
• Install Docker
• Database server containers
• Local installation alternatives to Docker
• RDBMS command-line interfaces
• Challenge: Create a server container
• Solution: Create a server container

3. Graphical Interfaces

• Management interfaces
• Install Azure Data Studio
• Connect to a database server
• Explore the server’s contents
• Create a database
• Challenge: Create a database
• Solution: Create a database

4. Create Table Objects

• Structured Query Language
• Organize a database with schemas
• Table columns
• Create tables
• Alter tables
• Reserved keywords
• Challenge: Create a table
• Solution: Create a table

5. Add Data to a Table

• Table rows
• Add records
• Update and delete records
• Saving and using SQL scripts
• Challenge: Add data to a table
• Solution: Add data to a table

6. Retrieve Information

• Querying data
• Return data with SELECT and FROM
• Filter rows with WHERE
• Sort values with ORDER BY
• Combine data with JOIN
• Limiting the number of rows returned

7. Manipulate Data

• Display column aliases with AS
• Mathematical operations
• Use built-in functions
• Aggregate data with GROUP BY
• Filtering groups with HAVING
• Challenge: Query data
• Solution: Query data

Conclusion

• Next steps