Two-day Microsoft SQL Server 2005/2008 Course

You will learn:

Day 1:

• 1 What is SQL 3
  2 SQL Server 2005/2008 3
  3 Editions of SQL Server 2008 4
  4 Documentation Conventions 4
  5 SQL Server Management Studio 4
  5.1 Object Explorer 5
  5.2 Object Explorer Details Page 5
  5.3 Configuring Startup Options 5
  5.4 Other Windows 5
  5.5 Closing and Hiding Components 5
  5.6 Moving Components 5
  5.7 Dock and undock components 5
  5.8 Document window 6
  6 Using Help - Launching Books Online 6
  7 System Databases 6
  8 Database Files 6
  8.1 Exercise: Create a Database called newDB 6
  9 Detach and Attach Data Files 7
  9.1 Exercise: Detach and Attach Data Files 7
  10 Master database 7
  10.1 Exercise: View the contents of the sysdatabases 7
  11 Table Design 8
  11.1 Fields Properties other than Name and data type 8
  11.2 Primary Key 8
  12 Create a database using SQL Server Management Studio 9
  12.1 Exercise: Create a new database called simpleDB 9
  12.2 Exercise: Create tables 9
  12.3 Data Entry 9
  13 Enter Data using Microsoft SQL Server Management Studio 9
  13.1 Exercise: Enter Data using Microsoft SQL Server Management Studio 9
  14 Create another Database 10
  14.1 SQL (Structured Query Language) and T-SQL 10
  15 Using T-SQL 10
  15.1 T-SQL Comments 11
  15.2 The Select statement 11
  15.3 Exercise: Using Select 11
  15.4 Conditions 11
  15.5 Exercise: Select all the records from the table Employees in Northwind where city is London 12
  16 The NULL Constant 12
  16.1 Exercise: Select all records from Orders where shipregion is null 12
  17 The IS Operator 12
  17.1 Exercise: Using IS operator 12
  18 Operators 12
  18.1 Exercise: Using Select with condition 12
  19 Create Database and tables using T-SQL 13
  19.1 Exercise: Create a database called mySqlDB with a 5 MB data file and 1 MB log file. 13
  20 ALTER DATABASE using TSQL 13
  20.1 Exercise: Add a new data file to the mySqlDB database with a size of 5MB and a Max size of 50MB: 13
  21 Using Multiple Databases 13
  21.1 Exercise: Create a new database using T-SQL statement 14
  21.2 Creating a Table using T-SQL statement 14
  22 Data Entry using T-SQL 15
  22.1 Exercise: Import an Access Database 15
  22.2 Exercise: Adjacent Data entry 15
  22.3 Random Data Entry 15
  22.4 Exercise: Random Data Entry 16
  23 Commonly used T-SQL Queries 16
  23.1 Data Type Syntax 16
  23.2 Exercise: Execute the following T-SQL statements 16
  23.3 T-SQL WHERE Clause 16
  23.4 Using the WHERE statement 16
  23.5 Exercise: Using where (mem database) 17
  24 The LIKE Condition 17
  24.1 Exercise: Using LIKE 17
  25 AND & OR 17
  25.1 Exercise: Using OR 17
  25.2 Exercise: Using AND 17
  26 Using parentheses to form complex expressions 17
  26.1 Exercise: Using parentheses 17
  27 BETWEEN ... AND 18
  27.1 Exercise: Using Between …And 18
  28 NOT operator 18
  28.1 Exercise: Using the NOT operator 18
  29 Select DISTINCT 18
  29.1 Exercise: Using Distinct 18
  30 ORDER BY 18
  30.1 Exercise: Using Order by 18
  31 SQL Update 18
  31.1 Exercise: Using Update 19
  32 DELETE 19
  32.1 Exercise: Using Delete 19
  32.2 Exercise: Count Function 19
  32.3 Exercise: Function AVG(Column) 19
  32.4 Function MAX(column) 19
  32.5 Function SUM(column) 19
  33 Using GROUP BY 19
  33.1 Exercise: Using GROUP BY 20
  34 HAVING 20
  34.1 Exercise: HAVING 20
  35 Working with more than one table 20
  35.1 Exercise: INNER JOIN 20
  35.2 Exercise: LEFT JOIN 20
  35.3 Exercise: RIGHT JOIN 21
  36 Data Import 21
  36.1 Exercise: Import Access Database 21
  37 Delete Database 21
  38 UNIONS 21
  38.1 Exercise: Using Unions 22
  39 The IN Operator 22
  39.1 Exercise: Using the IN operator 22
  40 Using Table and Field Aliases 22
  40.1 Exercise: Using Alias 22
  40.2 Exercise: Using Alias with the pubs database 23
  41 SQL – As 23
  41.1 Exercise: SQL – As 23
  42 SQL - Mathematical Operators 23
  42.1 Exercise: SQL - Mathematical Operators 23
  43 SQL - Incrementing a Value using Update 24
  43.1 Exercise: Incrementing the value of a column 24
  44 SQL Alter Table: Add and Drop a Column 24
  44.1 Exercise: SQL Alter Table: Add a Column 24
  44.2 Exercise: SQL Alter Table: Drop a Column 24
  45 SQL Subquery 24
  45.1 Exercise: SQL Subquery 25
  46 Using the SQL Case Statement 25
  46.1 Exercise: Using the Case statement 25
  47 Using DateAdd 25
  47.1 Exercise: Using DateAdd 26
  48 Using SQL TRUNCATE 26
  48.1 Exercise: SQL TRUNCATE 27
  49 Using Templates 27
  49.1 Exercise: Create a Database using the help of the Template 27
  50 The Query Designer 27
  50.1 Exercise: Build a Query using the Query Designer 27
  1
  

Day 2:

• Create Stored Procedures
• Create Stored Procedures to create a Database, to create a table
• Input Records with a stored procedure
• Delete a Stored Procedure
• Define stored procedure with input parameters
• Calling Stored Procedures
• Using the GUI to execute a stored Procedure
• What is a Trigger
• Create a Trigger for INSERT
• Test the Trigger for INSERT
• Create a Trigger for UPDATE
• Test the Trigger for UPDATE
• Create a Trigger for DELETE
• Test the Trigger for DELETE
• Using Views
• Select data from three base tables to present a virtual table of commonly needed data
• Use of Built-in functions
• Calling the Extended Stored Procedure
• Some Useful Extended Stored Procedures 12
  9.1 Exercise: Using xp_fileexist 12
  9.2 Exercise: Using xp_regread 12
  9.3 Exercise: Using xp_repl_encrypt 12
  9.4 Exercise: Using xp_dirtree 13
  9.5 Exercise: Using xp_ availablemedia 13
  9.6 Exercise: Using xp_ availablemedia 13
  10 User-defined function 13
  10.1 Statements that you can include in the body of a UDF 13
  10.2 Three Types of user-defined functions 14
  10.3 Scalar UDF 14
  10.4 Exercise: Create a Scalar UDF under the testDB Database 14
  10.5 Exercise: Create another Scalar UDF 14
  10.6 Inline-table valued UDF 15
  10.7 Exercise: Create and test inline-table valued UDF 15
  10.8 Multi-statement table valued UDF 16
  10.9 Exercise: multi-statement table valued UDF 16
  11 SQL Server 2005/2008 Administration 16
  11.1 Adding SQL Server Groups 17
  11.2 Editing Server Groups 17
  11.3 Adding SQL Server Registration 17
  11.4 Editing Server Registrations 17
  12 Logins 17
  12.1 Using sp_addlogin 17
  12.2 Exercise: Create login 18
  12.3 Exercise: Create another login ID 18
  12.4 Exercise: Login 18
  13 Users 18
  13.1 Exercise: Grant the user ‘kerry’ all the rights to the database mem 18
  13.2 Exercise: Login as the user ‘kerry’ and see if you can access database mem 19
  13.3 Why a new user (e.g. Kitty or Kerry) that you create in SQL Server when login can read and edit tables in those built-in databases (e.g. Northwind, Pubs, Masters etc) but not the ones you created (e.g. mem) 19
  14 Roles 19
  15 View a SQL Server login 20
  15.1 Change the default database of a login 20
  15.2 How to remove a SQL Server login 20
  15.3 To view the roles defined in the current database 20
  15.4 To remove a user account from a database role 20
  15.5 How to add a database role 21
  15.6 How to remove a database role 21
  15.7 How to view a database user properties 21
  15.8 How to allow access database objects by granting permissions 21
  16 Import and Export Data 21
  17 Using the SQL server Import and Export Wizard 22
  17.1 Exercise: Export a database to an Access file 22
  18 bulk copy command-line utility, bcp.exe 22
  18.1 Exercise: Using bcp.exe to export data from a table to a text file 22
  18.2 Exercise: Using bcp.exe to export the result of a query in native binary format 22
  18.3 Another Exercise: Using Bcp.exe to export and import 22
  19 Implementing Jobs 23
  19.1 Jobs 23
  19.2 Exercise: Create a job to backup the mem database 23
  20 Deterministic and Nondeterministic Functions 23
  21 Aggregate Functions 24
  21.1 Exercise: Using SUM 24
  21.2 Exercise: Using MAX 24
  22 Scalar functions 24
  22.1 Using DATEADD 24
  22.2 Using YEAR 24
  22.3 Using ASCII 24
  22.4 Exercise: Using @@LANGUAGE 25
  22.5 Exercise: Using @@MAX_CONNECTIONS 25
  23 Database Backups 25
  23.1 Database Backup Types 25
  23.2 Exercise: Backup 25
  23.3 To restore the Database 26
  24 Database Schema 26
  24.1 Exercise: Creating a Database Schema 26
  1
  
• Create login (no password or default database)
• Create a login ID and default database
• Grant a user rights to a database
• Roles
• Change the default database of a login
• Remove a SQL Server login
• Roles defined in the current database
• Rremove a user account from a database role
• Remove a SQL Server role
• View a database user
• Allow access by granting permissions
• Import and Export Data
• Implementing Jobs
• Attach databases
• Database Backups
• Restore Databases
• Displaying and Manipulating Data from SQL Server 2005 using ASP.NET
• Building applications using VB.NET to manipulate SQL Server 2005 database

Date: Thursday 20 October and Friday 21 October 2011

Cost: $660 (Australian dollars) including morning tea, lunch, afternoon tea, notes and lots of hands on exercises

Time: 9 am - 5pm

Place: 600 Collins Street or at a place close to the Monash freeway about 25 minutes drive from Melbourne city in Australia

Enquiries or Enrolment:

Email: melbourne@expert.com.hk

We also offer on-site training courses at your place anywhere in Australia, email to us for details: melbourne@expert.com.hk