Syllabus
POS/410 / 7
/ Syllabus
College of Information Systems & Technology
POS/410
SQL for Business

Copyright © 2010, 2009, 2008, 2007, 2006 by University of Phoenix. All rights reserved.

Course Description

This course covers Structured Query Language (SQL) that provides a unified language that lets you query, manipulate, or control data in a business applications environment.

Policies

Faculty and students/learners will be held responsible for understanding and adhering to all policies contained within the following two documents:

·  University policies: You must be logged into the student website to view this document.

·  Instructor policies: This document is posted in the Course Materials forum.

University policies are subject to change. Be sure to read the policies at the beginning of each class. Policies may be slightly different depending on the modality in which you attend class. If you have recently changed modalities, read the policies governing your current class modality.

Course Materials

Vieira, R. (2009). Beginning Microsoft SQL Server 2008 Programming. Indianapolis, IN: Wiley Publishing, Inc.

Software

Microsoft SQL Severer 2008 (Virtual Desktop)

Supplemental Resource

Rob, P., & Coronel, C. (2009). Database systems: Design, implementation, and management (8th ed.). Boston: Course Technology.

Article References

Barnett, R. (2008, October 13). Anatomy of a SQL-injection attack. Network World, 25(40), 30.

Ben-Gan, I. (2009, September). Logged inserts. SQL Server Magazine, 11(9), 21.

Campbell, D., & Nori, A. (2007). The Microsoft data platform. International Conference on Management of Data. Proceedings of the 2007 ACM SIGMOD international conference on Management of data, 1053-1060.

Cerullo, C., & Porta, M. (2007, September). A system for database visual querying and query visualization: Complementing text and graphics to increase expressiveness. 18th International Conference on Database and Expert Systems Applications (DEXA 2007), 109-113.

Nori, A. (2007). Mobile and embedded databases. International Conference on Management of Data. Proceedings of the 2007 ACM SIGMOD International Conference on Management of Data, 1175-1177.

Pathivada, C. S. (2009, June). Avoid referential integrity errors when deleting records from databases. SQL Server Magazine, 11(6), 15.

Raab, D. (2009, October 1). Business intelligence and the new analytical databases; What would BI apps look like if they weren't designed around the constraints of relational databases? Information Management, 19(8), 52.

Sellers, M. (2009, November). Pivoting the dynamic way. SQL Server Magazine, 11(11), 35.

Weis, R., & Johnston, T. (2007, May). Managing time in relational databases, part 1. DM Review, 17(5), 20.

Zhang, C., & Huang, Y. (2007). Cluster by: A new SQL extension for spatial data aggregation. Geographic Information Systems. Proceedings of the 15th Annual ACM International Symposium on Advances in Geographic Information Systems.

All electronic materials are available on the student website.

Week One: SQL Tables
Details / Due / Points
Objectives / 1.1  Create tables using SQL.
Course Preparation / Read the course description and objectives.
Make sure you have access to the required software:
·  Microsoft SQL Severer 2008 (Virtual Desktop)
·  NOTE: The Microsoft SQL Severer 2008 Virtual Desktop can be accessed on the left hand side of the Course Materials Page.
Review the Learning Team Toolkit.
Readings / Read Ch. 1, “RDBMS Basics: What Makes Up a SQL Server Database?” of Beginning Microsoft SQL Server 2008 Programming.
Read Ch. 2, “Tools of the Trade,” of Beginning Microsoft SQL Server 2008 Programming.
Read Ch. 5, “Creating and Altering Tables,” of Beginning Microsoft SQL Server 2008 Programming.
Read the Week One Read Me First.
Read this week’s Electronic Reserve Readings.
Participation / Participate in class discussion. 4 days, 2 substantive messages / day. / 4 days / 3
Discussion Questions / Respond to weekly discussion questions. / Thursday
Saturday / 2
Learning Team
Instructions / Create a Learning Team Charter.
The Learning Team assignment for this class, due in Week Five, is based upon Service Request SR-kf-009, “SQL Accounting Database.” A link to the Virtual Organizations can be found on the Course Materials page.
Week Two: Table Queries
Details / Due / Points
Objectives / 2 
2.1  Apply single-table queries.
2.2  Apply multiple-table queries.
Readings / Read Ch. 3, “The Foundation Statements of T-SQL,” of Beginning Microsoft SQL Server 2008 Programming.
Read Ch. 4, “JOINs,” of Beginning Microsoft SQL Server 2008 Programming.
Read Ch. 7, “Adding More to Our Queries,” of Beginning Microsoft SQL Server 2008 Programming.
Read the Week Two Read Me First.
Read this week’s Electronic Reserve Readings.
Participation / Participate in class discussion. 4 days, 2 substantive messages / day. / 4 days / 3
Discussion Questions / Respond to weekly discussion questions. / Thursday
Saturday / 2
Learning Team Instructions / Begin work on Service Request SR-kf-009, “SQL Accounting Database.”
·  Develop a project plan defining all tasks and work assignments for each team member.
·  Analyze the current Chart of Accounts spreadsheet and come up with a preliminary list of tables needed.
·  Submit the project plan for review by faculty. / Monday / 1
Individual
Table Queries / Create an SQL server database for the Kudler Fine Foods Virtual Organization.
Create the following tables using fields listed below.
Note. Supply SQL server data types when creating tables. In both tables, create an identity numeric Id field that auto-generates a unique number for each employee and job title, designating the fields as Primary Key fields. The primary key from the Job Title table appears as the foreign key in the Employee table.
Both the Age and salary fields are of numeric type.
·  Employee
o  Id
o  LastName
o  FirstName
o  Address
o  City
o  State
o  Phone
o  HireDate
o  Salary
o  Gender
o  Age
o  JobTitleId
·  JobTitle
o  Id
o  EEO1Classification
o  JobTitle
o  JobDescription
o  ExemptStatus
Use the SQL INSERT statement to go to the human resources department in the Kudler Fine Foods Virtual Organization. Using information found in the employee files for the La Jolla and Encinitas stores, enter records into the Employee table for the following employees:
·  Glenn Edelman
·  Eric McMullen
·  Raj Slentz
·  Erin Broun
·  Donald Carpenter
·  David Esquivez
·  Nancy Sharp
Use the information from the job classifications and descriptions to enter records into the Job Title table for the following titles:
·  Accounting clerk
·  Assistant manager
·  Bagger
·  Cashier
·  Computer support specialist
·  Director of finance and accounting
·  Retail assistant bakery and pastry
·  Retail assistant butchers and seafood specialists
·  Stocker
Check results by selecting all columns from both tables. Non-exempt employees at Kudler Fine Foods are paid an hourly wage and must track their working hours. / Monday / 16
Week Three: Data Changes
Details / Due / Points
Objectives / 3 
3.1  Apply changes to data.
3.2  Apply changes to tables.
Readings / Read Ch. 5, “Creating and Altering Tables,” of Beginning Microsoft SQL Server 2008 Programming.
Read Ch. 6, “Constraints,” of Beginning Microsoft SQL Server 2008 Programming.
Read Ch. 9, “SQL Server Storage and Index Structures,” of Beginning Microsoft SQL Server 2008 Programming.
Read Ch. 10, “Views,” of Beginning Microsoft SQL Server 2008 Programming.
Read the Week Three Read Me First.
Read this week’s Electronic Reserve Readings.
Participation / Participate in class discussion. 4 days, 2 substantive messages / day. / 4 days / 3
Discussion Questions / Respond to weekly discussion questions. / Thursday
Saturday / 2
Learning Team Instructions / Continue work on the Learning Team Project.
·  Define the SQL Svr database and its tables.
·  Load the SQL Svr database with data from the chart of accounts spreadsheet.
·  Prepare test data and load it into the SQL database.
·  Submit a script file showing preliminary SQL DB definition. / Monday / 1
Individual
Data Changes and SQL Statements / Use the database and tables from last week’s assignment to write SQL statements and enter records into the Employee table for workers identified in the employee files for the administrative offices and the Del Mar store. Check results by selecting all columns from both tables.
Use the database and tables from last week’s assignment to write SQL queries using Between, Like, and Union. Write SQL queries that do the following:
·  Join the two tables in the example database and use BETWEEN to restrict record selection. Use salary to restrict data.
·  Join the two tables in the example database and use BETWEEN to restrict record selection. Use hire dates to restrict data.
·  Join the two tables in the example database and use LIKE to restrict record selection. Use telephone numbers to restrict data.
·  Join the two tables in the example database and use IN to restrict record selection. Use age to restrict data.
·  Use the UNION of the two tables to select the Id, the first and last name and Age fields from the Employee table, Id and JobDescription fields from the JobTitle table. In the Employee SELECT, the first, last and Age fields are to be returned in one single alphabetical field, with a space separating first from last and a comma character separating last from age.
Use the updated database to write the following queries, using the SQL GROUP statement:
·  Select employees’ last names and group them by EEO-1 classification.
·  Select employees’ last names and group them by salary.
·  Select employees’ first, last name, city and job title fields from the Employee table using a subquery. Hint: A JOIN should not be used; A subquery should be used instead.
Print out the SQL queries in a script or text file. / Monday / 16
Week Four: Reports
Details / Due / Points
Objectives / 4 
4.1  Apply reporting in SQL.
Readings / Read Ch. 11, “Writing Scripts and Batches,” of Beginning Microsoft SQL Server 2008 Programming.
Read Ch. 12, “Stored Procedures,” of Beginning Microsoft SQL Server 2008 Programming.
Read Ch. 17, “Reporting for Duty, Sir!: A Look At Reporting Services,” of Beginning Microsoft SQL Server 2008 Programming.
Read the Week Four Read Me First.
Read this week’s Electronic Reserve Readings.
Participation / Participate in class discussion. 4 days, 2 substantive messages / day. / 4 days / 3
Discussion Questions / Respond to weekly discussion questions. / Thursday
Saturday / 2
Learning Team Instructions / Continue working on the Learning Team Project.
·  Create account number query in a stored procedure.
·  Create a stored proc with input params and one output param that inserts a record in one of your tables containing an auto-generated numeric PK field. The out param returns the newly created identity auto-generated PK value.
·  Test your query and insert sp’s.
·  Submit a preliminary script or text file with both sp’s. / Monday / 1
Individual
SQL Reports / Use the database and tables from last week’s assignment to write the SQL statements:
·  Increase all employees’ salaries with an EEO-1 classification of your choice by 10 percent.
·  Choose an employee from the Employee table and delete that employee.
·  Write a SQL statement to create a stored procedure having one int input parameter. In the stored procedure, select the JobTitleId field and the total of all salary field values for each group of JobTitleId's from the Employee table. Only rows with their salary total, or sum, greater than the value specified in the input parameter should be returned. Hint: Each returned record has a unique JobTitleID value, since we are dealing with groups.
Use the database and tables from last week’s assignment to write SQL queries that do the following:
·  Calculate the average salary for all employees.
·  Calculate the maximum salaries for exempt and non-exempt employees.
·  Calculate the minimum salaries for exempt and non-exempt employees.
Print out the SQL statements in a text or script file. / Monday / 18
Week Five: Embedded SQL
Details / Due / Points
Objectives / 5 
5.1  Explain embedded SQL.
5.2  Apply SQL to a business application.
Readings / Read Ch. 13, “User-Defined Functions,” of Beginning Microsoft SQL Server 2008 Programming.
Read Ch. 15, “Triggers,” of Beginning Microsoft SQL Server 2008 Programming.
Read the Week Five Read Me First.
Read this week’s Electronic Reserve Readings.
Learning Team
Project / Complete work on the Learning Team Project.
·  Create a report in SQL Svr showing the Chart of Accounts.
·  Create an Update trigger on any of your team’s tables that contains a numeric field, which raises an error string if the numeric field of the updated row has a higher value than the value prior to the Update. In other words, the new numeric field value cannot be larger than the older value. If the trigger fires and after the error string is displayed, the Update operation should be rolled back. The exact text of the error string is up to your team to define.
·  Submit one paper that shows the screen shot of the team DB schema diagram showing all tables, fields and relationships, final sp’s of previous weeks, trigger and screen shot of the Chart of Accounts report. / Monday / 27

Copyright

University of Phoenix® is a registered trademark of Apollo Group, Inc. in the United States and/or other countries.

Microsoft®, Windows®, and Windows NT® are registered trademarks of Microsoft Corporation in the United States and/or other countries. All other company and product names are trademarks or registered trademarks of their respective companies. Use of these marks is not intended to imply endorsement, sponsorship, or affiliation.

Edited in accordance with University of Phoenix® editorial standards and practices.