CCOG for CIS 276 Fall 2024


Course Number:
CIS 276
Course Title:
Advanced SQL
Credit Hours:
4
Lecture Hours:
30
Lecture/Lab Hours:
0
Lab Hours:
30

Course Description

Focuses on design, development and implementation of SQL programming for all types of relational database applications including client/server and internet databases. Covers the writing of complicated interactive and embedded SQL statements and the implications of multi-user database applications. Recommended: two-term programming language sequence. Audit available. Prerequisites: CIS 275 or instructor permission.

Intended Outcomes for the course

On completion of this course the student should be able to:
 

  • Write complex SQL queries to retrieve information from databases with many tables to support business decision making.
  • Write SQL DDL to create, modify and drop objects within a relational database.
  • Retrieve and store information in a relational database using SQL in a multi-user, web based environment.

Course Activities and Design

  • Prepare SQL queries that use multiple tables.
  • Prepare SQL queries that involve correlated and non correlated subqueries, outer joins, inner joins, self joins.
  • Assess the differences between subqueries and joins.
  • Use built-in functions successfully.
  • Produce user friendly output by using formatting features of SQL.
  • Analyze the effects of various data types and the use of NULLs.
  • Prepare SQL queries for ordering data and grouping data.
  • Prepare SQL queries that involve decision making.
  • Write SQL queries for data definition/manipulation/alteration.
  • Describe and demonstrate issues surrounding data integrity: validity checking, uniqueness constraints, referential integrity problems, cascaded deletes and updates, triggers.
  • Describe and demonstrate issues surrounding transaction processing including locking processes.
  • Identify and describe security concepts including user-ids, levels of privileges, views.
  • Produce programs with embedded SQL including processing stored procedures.

Course Content (Themes, Concepts, Issues and Skills)

  • Prepare SQL queries that use SELECT, FROM, WHERE, IN, BETWEEN, LIKE, EXISTS.
  • Prepare SQL queries that involve correlated and non correlated subqueries, outer joins, inner joins, self joins.
  • Assess the differences between subqueries and joins.
  • Use built-in functions successfully.
  • Produce user friendly output by using formatting features of SQL.
  • Analyze the effects of various data types and the use of NULLs.
  • Prepare SQL queries using ORDER BY, GROUP BY, HAVING, UNION, ANY, ALL.
  • Write SQL queries for data definition/manipulation: CREATE, INSERT, UPDATE, DELETE, ALTER, DROP
  • Describe and demonstrate issues surrounding data integrity: validity checking, uniqueness constraints, referential integrity problems, cascaded deletes and updates, triggers.
  • Describe and demonstrate issues surrounding transaction processing including: use of COMMIT and ROLLBACK, locking processes.
  • Identify and describe security concepts including user-ids, privileges, GRANT and REVOKE.
  • Produce programs with embedded SQL including processing stored procedures.