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