CCOG for CIS 276 Spring 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.