BCS 360: Programming in SQL

The second in a two-course sequence applies the knowledge of BCS260 to administer and implement relational database systems. Topics covered may include embedded SQL and other mixed language mechanisms; PL/SQL; advanced/optimized SQL queries; transaction management including concurrency and recovery; schema refinement; higher-level normal forms; integrity; security; and database administration. Credits: 3.00

Prerequisite

BCS 260 with a grade of C or better.

Course Objectives

At the completion of this course, students will

1. Use the SQL database language to write data definition (DDL) statements to create a schema and to modify the tables and indices of an existing schema.
2. Use SQL to write data manipulation (DML) statements to query tables, modify data, and insert data into an existing schema.
3. Write SQL DML statements that access multiple tables using subqueries and/or joins.
4. Write SQL DML statements that use aggregates and groups by collect and summarize data.
5. Differentiate between different types of constraints, and use SQL to write declarative constraints that help provide data integrity within a database management system.
6. Create views to allow DML operations to base tables.
7. Explain the use and implementation of transactions in a relational database, including a discussion of concurrency, consistency and general locking concepts.
8. Create SQL transactions using both commit and rollback statements.
9. Write short stored programs within a DBMS, including programs that create cursors and programs that can be run via a SQL trigger.
10. Use the DBMS security features to provide or restrict access to database objects and articulate how views can be used to restrict user access to data.

Textbooks

• Oracle Database 12c SQL 1st Edition by Jason Price McGraw-Hill Education ISBN-13: 978-0071799355