CSC 150 Database Applications and Design Using SQL Spring 2010 Test 2 Review

Connecticut Community Colleges

Gateway Community College

Student Info System

Contact Instructor

Home Syllabus Term Project

The second test will cover the materials from chapters 6 - 9 in SQL Fundamentals as well as Foreign Keys - http://www.visualcase.com/kbase/database_basics_-_foreign_keys.htm and Primary and Foreign Key Constraints - http://www.databasedev.co.uk/primary_foreign_key_constraints.html You can bring the following handouts to the test to help you, but nothing else is allowed: Select Statement - SelectStatement.doc Wildcards and Punctuation in Access - WildcardCharactersinAccess.doc Creating and Deleting tables, manipulating table data - CreatingTable.doc Create and Alter Statements, Acess Datatypes - CreateStatement.doc Format and Row Functions in Access - RowFunctions.doc

The following topics are fair game for the second test.

• Creating new tables using the create table statement
• Creating new tables and adding constraints concurrently using the create table statement
• Using the insert statement to put data into a new table
• How different datatypes are express in Access
• The difference between fixed length text and variable length text
• Using the alter table command to do the following:
  • add a primary key to a table
  • change a primary key
  • add a new column to a table
  • change the width of a column
  • delete a column from a table
  • add a unique constraint
  • add a foreign key to a table
• When it's okay to have duplicate rows in a table and when it is not okay
• Using the format() function to display dates in different formats
• Using the Access GUI to format fields
• How and why we use the counter/autonumber datatype
• What is an index and we do we use them on tables
• Use the create index statement to put an index on a table
• Using the Access GUI to delete an index from a table
• What is a constraint and why do we use them
How to add the following constraints to a table;
• check
• not null
• unique
• What is Referential Integrity and why do we use it
• What is a foreign key
• How do we create Referential Integrity using the Access GUI
• What constraints does Referential Integrity place on a table
• What will happen when the following options are applied to Referential Integrity;
  • Cascade Update Related Fields
  • Cascade Delete Related Records
• Using more than one field for Referential Integrity
• Using the same table for Referential Integrity
• What are row functions and why do we use them
• Use a row function in all clauses of a select statement
• Show how to define a row function in one query, then use the first query in a second query.
• Show how to use the following types of functions:
  • number functions
  • text functions
  • date functions

Last Updated: 3/30/10