Mar 28, 2024  
2018-2019 Course Catalog 
    
2018-2019 Course Catalog [ARCHIVED CATALOG]

Add to Portfolio (opens a new window)

CIS 332 - Database and SQL

Credits: 3
Lecture Hours: 2
Lab Hours: 2
Practicum Hours: 0
Work Experience: 0
Course Type: Voc/Tech
This course is an introduction to SQL as a database programming language to those already familiar with basic relational database concepts. Students will write executable SQL statements to create and maintain database objects.
Prerequisite: CIS 303  
Competencies
  1. Describe the structure of a relational database
    1. Describe the purpose of a database management system (DBMS).
    2. Identify relationships between given entities
    3. Define the terms database, query, tuple, attribute, table, row, column, relation, primary key, and foreign key
    4. Recognize objects in a given database
    5. Describe relational and object relational database concepts
    6. Identify the differences between ANSII standard SQL and application specific SQL statements
  2. Create executable SQL statements to perform single-table queries
    1. List the mandatory clauses of a basic SELECT statement
    2. Reproduce the basic form of an SQL command to retrieve data from a database
    3. Cite the comparison operators used in SQL
    4. Formulate queries containing a simple condition to restrict data retrieved by a query
    5. Formulate queries containing multiple conditions to restrict data retrieved by a query
    6. Name arithmetic operators used in SQL
    7. Create computed columns from existing table data
    8. Use the ORDER BY clause to sort query results
    9. Apply SQL built-in functions to SQL queries
    10. Demonstrate the ability to use the GROUP BY clause
  3. Create executable SQL statements utilizing single-row functions
    1. Describe the various types of functions available in SQL
    2. Use character, number, and data functions in SELECT statements
    3. Explain conversion functions
    4. Demonstrate the use of conversion functions
  4. Create executable SQL statements to perform multiple-table queries
    1. Prepare SQL statements to join tables together
    2. Explain the purpose of comparisons in the WHERE clause of a join statement
    3. Describe the problem with producing Cartesian products in multiple-table queries
    4. Explain what causes a Cartesian product to be formed in a join statement
    5. Identify the different types of joins available in SQL statements (i.e. equijoin, non-equijoin, outer join, inner join, and self join).
    6. Apply the IN, EXISTS and BETWEEN clause to join statements
    7. Demonstrate the use of a subquery to restrict data
    8. Describe the purpose of column and table aliases
    9. Utilize column and tables alias names in queries
    10. Define the set operators union, intersect, and difference
  5. Create executable SQL statements utilizing group functions
    1. Identify the group functions available in SQL (i.e. AVG, COUNT, MAX, MIN, SUM)
    2. Describe the use of group functions
    3. Aggregate data using the GROUP BY clause
    4. Use the HAVING clause to include or exclude grouped rows
    5. Demonstrate the ability to nest group functions in SQL queries
  6. Create executable SQL statements utilizing subqueries
    1. Define subqueries
    2. Describe the types of problems that subqueries can solve
    3. Write single-row and multiple-row and multiple-column subqueries
    4. Determine the effect of a null value returned in a subquery
  7. Create executable SQL statements utilizing correlated subqueries
    1. Describe a correlated subquery
    2. Describe the types of problems that can be solved with a correlated subquery
    3. Use the EXISTS and NOT EXISTS operators in correlated subqueries
    4. Update and delete rows using correlated subqueries
  8. Combine SQL queries with SET operators
    1. Describe the set operators (union, intersect and minus).
    2. Obey the set operators rules and guidelines
    3. Include the order by clause in queries
    4. Recognize the rules that must be followed when using set operators in compound queries
  9. Produce readable output with SQL*Plus
    1. Differentiate between SQL and SQL*Plus
    2. Construct queries that require and input variable to create interactive reports
    3. Utilize the ACCEPT command to read input variables
    4. Customize the SQL*Plus environment
    5. Save customizations in the SQL*Plus environment
    6. Produce neatly formatted, readable output
    7. Format output with SET commands
    8. Add page headers and footer to reports
    9. Demonstrate the use of the COLUMN command to format reports
    10. Demonstrate the use of the BREAK command to format reports
    11. Utilize the COMPUTE command to produce report totals
    12. Construct script files
    13. Execute script files
  10. Create executable SQL statements to create database objects
    1. Describe DDL (data definition language).
    2. Formulate SQL statements to create database objects such as tables, views, sequences, indexes and synonyms
    3. Demonstrate the ability to create new tables from an existing table
    4. Explain the use of naming conventions in SQL
    5. Differentiate between possible data types
    6. Describe how to use the data dictionary to access database object information
  11. Create executable SQL statements to update a database
    1. Describe what is meant by DML (data manipulation language).
    2. Describe each of the DML commands
    3. Demonstrate the ability to load data into a database using SQL
    4. Utilize a sequence to insert values into a table
    5. Prepare SQL statements to change, add, and delete data from a database
    6. Describe the use of nulls when updating column values
    7. Implement changes in a database using the ALTER, CREATE, and DROP statements
    8. Write executable SQL statements to modify the structure of an existing table
    9. Rename an existing table
    10. Insert, Update and Delete data through a view
    11. Alter the definition of a view
    12. Explain the process of updating a view
    13. Construct executable SQL statements to delete database objects from the database
    14. Create synonyms for database objects
  12. Implement and enforce data integrity constraints in a database
    1. Describe what is meant by referential integrity
    2. Describe various types of database constraints
    3. Include integrity constraints on an SQL statement to CREATE a table
    4. Write executable SQL statements to add integrity constraints to an existing table
    5. Produce executable SQL statements to enable and disable integrity constraints
    6. Utilize system tables to view existing integrity constraints
  13. Evaluate special issues involved in managing a database
    1. Describe DCL (data control language)
    2. Define database administration
    3. Investigate SQL features used in database administration
    4. Explain the difference between system security and data security
    5. Explain security mechanisms used in SQL to prevent unauthorized access to a database (such as the GRANT and REVOKE statements)
    6. Define the term ?privileges?
    7. List privileges that can be granted and revoked in a database
    8. Disucss the use of legal values, primary keys, and foreign keys to support data integrity
    9. Recognize the role the SQL catalog plays in obtaining information about a database?s structure
    10. Explain the use of restore/recovery procedures in database administration
    11. Define what constitutes a database transaction
    12. Utilize SQL statements such as COMMIT and ROLLBACK to control database transactions
    13. Describe what is meant by “ready consistency”
    14. Explain why locks are useful in a database.
  14. Write scripts to generate SQL commands
    1. Describe the types of problems that are solved by writing SQL scripts that generate other SQL scripts
    2. Write and execute a script that generates a script of drop table commands
    3. Write and execute a script that generates a script of insert commands
  15. Test SQL statements thoroughly



Add to Portfolio (opens a new window)