CTP 145 - Relational Databases and SQL

4 credit hours - Four hours of lecture / directed laboratory weekly; one term.
Formerly CSI 172 - Relational Databases and SQL

Learn the fundamental concepts of database systems including design, implementation, and security using Oracle. Learn to create a database, build various database objects, and manipulate data using the structured query language (SQL). Use this universal database programming language to write simple and complex queries to retrieve, insert, update, delete, and filter information in the database. This course is the first in the series of Oracle database administration courses and addresses the core material of both the SQL Fundamentals and the SQL Expert certification exams needed to become an Oracle Certified Associate.

Prerequisite(s): CTP 103 or permission of the CTP department chair.

Note: Typically offered OL; fall, spring and summer terms.

Course Outcomes:

Describe relational database concepts and develop logical database designs
- Define database terms
- Identify the purpose of a database management system (DBMS)
- Explain database design by using entity-relationship models and normalization
- Explain the purpose of a Structured Query Language (SQL)
Construct queries that aggregate, express, and examine data in the database
- Identify keywords, mandatory clauses, and optional clauses in a SELECT statement
- Use a WHERE clause to restrict the rows returned by a query
- Identify and create a variety of joins
- Use single-row and multiple-row functions to manipulate data
- Determine when to use the GROUP BY and HAVING clauses should be used
- Perform enhanced aggregation grouping with GROUPING SETS, CUBE, and ROLLUP
- Use single-row and multiple-row subqueries
- Nest a subquery inside another subquery
- Process multiple DML actions with a MERGE statement
Develop DDL commands to create and maintain tables in the database
- Create a new table with the CREATE TABLE command
- Modify the definition of an existing table
- Rename a table
- Truncate a table
- Drop a table
Develop DML commands to manipulate the data in the database
- Use the INSERT command to add a record to an existing table
- Use the UPDATE command to modify a table’s existing rows
- Delete records
- Manage transactions with the transaction control commands COMMIT, ROLLBACK, and SAVEPOINT
Discuss security issues and employ privileges and views to enhance security
- Explain the concept of data security
- Grant and revoke privileges to a user
- Create a view, using the CREATE VIEW command or the CREATE OR REPLACE VIEW command
- Drop a view
Evaluate and create other database objects to improve the integrity and performance of the database
- Define the purpose of a sequence and explain how it can be used in a database
- Explain the main index structures
- Describe variations on conventional indexes, including a function-based index and an index organized table
- Create and remove a synonym
- Distinguish among PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and NOT NULL constraints and understand the appropriate use for each constraint
Describe various non-traditional database designs
- Define a distributed database management system (DDBMS) and what its components are.
- Define a data warehouse.
- Discuss the importance of NoSQL.
- Develop types to be used in an Object-Oriented database
- Describe the difference between the relational, hierarchical, and network database models
Incorporate SQL editor techniques
- Learn the historical importance of SQL*Plus
- Learn to run SQL statements from SQL Developer