ITCS 2140 - Database Programming Using Microsoft SQL Server

Prerequisite: ITCS-1170

Prerequisite: ITCS-1140 or ITCS-1950

Objectives:

1. Given a set of design specifications student will create tables, constraints, indexes and views, with a minimum of 85% accuracy.
2. Given a set of design specifications students will implement datatypes, with a minimum of 85% accuracy.
3. Given a set a set of base tables, students will alter tables, constraints, indexes and views, with a minimum of 85% accuracy.

Outcome 2: Upon completion of this course, students will be able to create and implement Transaction Structured Query Language (T-SQL) programming objects.

Objectives:

1. Given a set of base tables, students will design, construct implement and test T-SQL stored procedures, with a minimum of 85% accuracy.
2. Given a set of base tables, students will design, construct implement and test T-SQL triggers, with a minimum of 85% accuracy.
3. Given a set of base tables, students will design, construct implement and test T-SQL a set of base tables, students will design, construct implement and test T-SQL functions, with a minimum of 85% accuracy.
4. Given a sent a set of base tables, students will implement transactional control, with a minimum of 85% accuracy.

Outcome 3: Upon completion of this course, students will demonstrate advanced querying techniques utilizing Structured Query Language (SQL) and Transaction Structured Query Language (T-SQL).

Objectives:

1. Given a set of base tables, students will design, construct advanced SELECT statements utilizing aggregate functions, sorting, grouping and built-in functions, with a minimum of 85% accuracy.
2. Given a set of base tables, students will design, construct, implement and test T-SQL triggers, with a minimum of 85% accuracy.
3. Given a set of base tables, students will design, construct, implement and test T-SQL functions, with a minimum of 85% accuracy.
4. Given a set of base tables, students will construct subqueries, with a minimum of 85% accuracy.
5. Given a set of base tables, students will apply ranking functions, with a minimum of 85% accuracy.
6. Given a set of base tables, students will implement common table expressions, with a minimum of 85% accuracy.

Outcome 4: Upon completion of this course, students will be able to monitor and optimize query execution using Structured Query Language (SQL) and Transaction Structured Query Language (T-SQL).

Objectives:

1. Given the Microsoft SQL Server software, students will implement, execute and optimize scripts, with a minimum of 85% accuracy.
2. Given the Microsoft SQL Server software, students will implement Service Broker solutions, with a minimum of 85% accuracy.
3. Given the Microsoft SQL Server software, students will trace data changes, with a minimum of 85% accuracy.

Outcome 5: Upon completion of this course, students will create queries utilizing (Extensible Markup Language) XML data.

Objectives:

1. Given a case scenario, students will generate XML tags for database data, with a minimum of 85%.
2. Given a case scenario, students will transform XML tagged data into database data, with a minimum of 85% accuracy.
3. Given a case scenario, students will query XML data, with a minimum of 85% accuracy.
4. Given a case scenario, students will manage XML data, with a minimum of 85% accuracy.

1. Review of Query
2. Aggregating Functions and Queries
3. Joining tables and datasets
4. Subqueries
5. Common Table Expressions
6. Data Recursion
7. Implementing tables and views
8. Stored Procedures
9. User-Defined Function
10. Triggers
11. Transactions and Concurrency
12. Error Handling
13. Tracking Access and Changes to Data
14. Service Broker
15. XML and XQuery