MTA Database Fundamentals (98-364) Cert Prep: 2 Creating and Manipulating Data

Learn all about getting data in and out of tables and databases, including assigning data types, creating tables, writing queries, and inserting and deleting records.

Introduction

• Welcome
• Preparing for MTA Exam 98-364
• Using the exercise files
• Challenges explained
• What you should know: Restoring your database backup

1. Defining Data Types

• Understanding the built-in SQL Server data types
• Using exact numeric data types
• Using approximate numeric data types
• Storing date and time values
• Storing text as character strings
• Understanding spatial data types
• Working with binary strings and other data types
• Converting data types in calculations

2. Understanding Tables and How to Create Them

• Defining your data needs
• Creating a table in SQL Server Management Studio
• Creating a table using Transact-SQL commands
• Applying data constraints and indexes in T-SQL
• Using SSMS to create reusable scripts
• Challenge: Writing T-SQL commands
• Solution: Writing T-SQL commands

3. Using Queries to Select Data

• Selecting records from the database
• Combining multiple related tables with the JOIN clause
• Combining conditions with AND and OR
• Clarifying ranges with a BETWEEN clause
• Specifying data you don't want with NOT
• Sorting data with ORDER BY
• Combining returns with the UNION clause
• Using the EXCEPT and INTERSECT clauses
• Using aggregate functions
• Challenge: Making selections
• Solution: Making selections

4. Working with Views

• Understanding the role of SQL Server views
• Creating a view in SQL Server Management Studio
• Viewing related data from multiple tables
• Defining a view using Transact-SQL commands
• Challenge: Creating a view
• Solution: Creating a view

5. Creating Stored Procedures and Functions

• Understanding the role of stored procedures
• Creating a stored procedure in SQL Server Management Studio
• Leveraging user-defined parameters
• Introducing user-defined functions
• Creating a user-defined scalar function

6. Inserting and Updating Data in a Table

• Using SSMS to insert data into a table
• Inserting data using data manipulation language
• Updating data in SSMS
• Modifying data using the UPDATE clause

7. Removing Data from the Database

• Deleting records in SSMS
• Using the DELETE statement
• Clearing out a table with TRUNCATE TABLE
• Deleting a table with DROP TABLE
• Understanding transactions

Conclusion

• Next steps