CIS 332 - Database and SQL

1. Create executable SQL statements to perform single-table queries
   1. Identify the mandatory and optional clauses of an SQL SELECT statement.
   2. Select and view data in a database
   3. Assign a column alias in the result set
   4. Generate computed columns
   5. Remove duplicates from a result set
   6. Use concatenation to combine fields, literals and other data
   7. Use comparison operators and search conditions to restrict results
   8. Sort query results with an ORDER BY clause
2. Create executable data definition language (DDL) commands
   1. Describe the purpose of DDL commands
   2. Differentiate between possible data types
   3. Formulate DDL statements to create database objects such as tables, sequences, indexes and synonyms
   4. Demonstrate the ability to create new tables or views from an existing table
   5. Alter or modify existing database objects
   6. Rename objects in the database
   7. Remove objects from a database
   8. Determine a sequence’s current value and next value
3. Implement and enforce data integrity constraints in a database
   1. Describe types of database constraints such as primary keys, foreign keys, not null, check, unique and default values
   2. Include integrity constraints on a CREATE a table command
   3. Add integrity constraints to an existing table
   4. Manage foreign keys when parent rows are deleted
   5. Enable and disable integrity constraints
   6. Utilize system tables to view existing integrity constraints
4. Create executable data manipulation language (DML) commands
   1. Describe DML commands such as insert, update, delete
   2. Prepare DML statements to change, add, or delete data in a database
   3. Utilize a sequence to generate and insert values into a table
   4. Construct SQL statements that require user input / a substitution variable
   5. Resolve integrity constraint errors when manipulating data in a database
5. Perform transaction control statements
   1. Execute COMMIT and ROLLBACK commands
   2. Create a SAVEPOINT
   3. Demonstrate the use of table locks
6. Evaluate database security issues
   1. Understand security mechanisms such as the GRANT and REVOKE statements
   2. List privileges that can be granted and revoked in a database
   3. Explain the use of roles in database management
7. Construct executable SQL statements to perform multiple-table queries
   1. Identify the various types of joins available
   2. Explain what causes and the problems associated with a Cartesian product
   3. Prepare SQL statements that utilize an equality join
   4. Utilize a non-equality join in an SQL statement
   5. Formulate queries containing a self-join
   6. Demonstrate the use of an outer join
   7. Utilize table aliases to simply the process of qualifying columns
   8. Employ set operators such as union, intersect and minus to combine the results of two or more SELECT statements
8. Produce executable SQL statements utilizing single-row functions
   1. Describe the various types of functions available in SQL
   2. Demonstrate the use of case conversion functions
   3. Create a query utilizing character manipulation functions
   4. Formulate SQL statements utilizing number functions
   5. Demonstrate the use of date functions
   6. Test the use of single-row functions by referencing the DUAL table
9. Develop executable SQL statements utilizing group functions
   1. Identify the group functions available in SQL
   2. Aggregate data using the GROUP BY clause
   3. Use the HAVING clause to include or exclude grouped rows
   4. Demonstrate the ability to nest group functions in SQL queries
10. Formulate executable SQL statements utilizing subqueries
    1. Identify the types of problems that subqueries can solve
    2. Execute a single-row subquery in a SELECT clause, a WHERE clause, and a HAVING clause
    3. Utilize multiple-row operators in a multiple-row subquery
    4. Formulate a multiple-column subquery in a FROM clause and in a WHERE clause
    5. Demonstrate the ability to successfully handle NULL values in a subquery
    6. Utilize nested subqueries
11. Demonstrate code readability standards, testing and effective communication to other developers
    1. Use a development tool and explore it’s features
    2. Comply with the use of standard readability and naming conventions
    3. Execute and modify script files
    4. Demonstrate the use of comments and good code layout
    5. Access database information using the data dictionary
    6. Develop strategies for testing SQL statements and verify the results