SQL Server Database Fundamentals

  1. Understanding Core Database Concepts
  • Understand how data is stored in tables
  • Understanding what a table is
  • How it relates to the data that is stored in the database
  • Columns/fields
  • Rows/records
  • Understand relational database concepts
  • Understanding what a relational database is
  • The need for relational database management systems (RDBMS)
  • How relations are established
  • Basic concepts about database engine
  • About the language SQL
  • What is T-SQL
  • Understand data manipulation language (DML)
  • What is DML
  • How DML works to deal with data in databases
  • Building SELECT statement
  • Displaying result
  • Exporting result
  • Understand data definition language (DDL)
  • Understanding how T-SQL can be used to create database objects
  • DDL statements
  • Creating tables with DDL
  • Creating views with DDL
  1. Creating Database Objects
  • Choose data types
  • Understanding what data types are
  • Why data types are important
  • How data types affect storage requirements
  • Understand tables and how to create them
  • Purpose of tables
  • Determining tables
  • Creating tables in a database by graphical interface
  • Using proper T-SQL statement
  • Create views
  • Understanding when to use views
  • How to create a view using graphical designer
  • Using T-SQL statement
  • Create stored procedures and functions
  • What is a stored procedure
  • What is a function
  • Difference between stored procedure and function
  • Selecting data
  • Inserting, updating and deleting data
  1. Manipulating Data
  • Select data with complicated conditions
  • Utilizing SELECT queries to extract data from one table
  • Extracting data by using joins on multiple tables
  • Combining result sets by using UNION/UINON ALL and INTERSECT
  • Sub-queries
  • Insert data
  • Understanding how data is inserted into a database
  • How to use INSERT statements
  • Multiple insertions
  • Insert with select
  • Update data
  • Understanding how data is updated in a database
  • How to write the updated data to the database
  • Using the appropriate UPDATE statements
  • Update from another table
  • Update with select
  • Delete data
  • Deleting data from single table
  • Deleting data from multiple tables
  • Understanding transaction
  • Ensuring data and referential integrity by using transactions
  • Delete with select
  1. Understanding Data Storage
  • Understand normalization
  • What is normalization
  • Understanding the reasons for normalization
  • The most common levels of normalization
  • How to normalize a database
  • Understand primary, foreign, and composite keys
  • Understanding the reason for keys in a database
  • Choosing appropriate primary keys
  • Selecting appropriate data type for keys
  • Selecting appropriate fields for composite keys
  • Understanding the relationship between foreign and primary keys
  • Understand indexes
  • Understanding indexes
  • Clustered and non-clustered
  • Purpose of having indexes in a database
  • Performance related considerations
  1. Administering a Database
  • Understand database security concepts
  • Understanding the need to secure a database
  • What objects can be secured
  • What objects should be secured
  • User accounts
  • Roles
  • Permissions on database objects
  • Understand database backup and restore
  • Understanding database recovery models and various backup types
  • Things between database models and backup types
  • Full and incremental backups
  • Importance of backups
  • How to backup a database
  • How to restore a database

 

授课教师:Mr. Liu计算机软件专业毕业,MCITP-DBA 2005/2008,拥有多年大型信息系统设计开发经验。曾经在中国主持设计开发超过1000个用户的大型政府部门办公自动化系统,AT&T全美联机系统的Senior Consultant,加拿大著名金融公司、银行及政府部门的Senior SQL Server DBA Consultant。擅长大型系统的设计,复杂数据结构的应用,企业级数据库系统的维护和开发。对于数据库系统理论及应用有精辟及独到的见解,所教授的SQL Server DBA及Access VBA 课程一直受到众多学员的好评。