CSC 150 Database Applications and Design Using SQL

Spring 2010

CRN: 1723 - Thursdays - 5:00pm to 9:50pm - LW Room 219

Instructor: Susan E Traudt
[email protected]
Connecticut Community Colleges Gateway Community College Student Info System Contact Instructor

Syllabus

Assignments

Term Project

Announcements

Top 10 Largest Databases in the World - http://www.focus.com/fyi/operations/10-largest-databases-in-the-world/


Final Exam Review


Take Home Final

Take Home Final - older word version if you cannot download the exam from above link

Note: Some queries may produce errors for one record. As long as the query properly processes the majority of the records in a table, the query is correct.

Note: #7 : Supplier_Code should be supplier_id


Recipe Database - What you final project should look like

What your final project should contain:

  1. A cover sheet with DB name, course name, and your name
  2. A table of contents
  3. A list of tables (See Recipe database for example)
  4. A data dictionary (See Recipe database for example
  5. At least 2 SQL statements from each chapter - 2 - 4, 6 - 13

    For each SQL statement:

    • Chapter the query is from
    • Description of what the SQL statement will do
    • A before screen capture if necessary
    • The SQL statement
    • A screen capture of the results of the SQL Statement
    • (Try to put each statement on it's own page)


Web site for Oracle for Windows - http://www.oracle.com/technology/products/database/xe/index.html?origref=http://www.oracle.com/us/products/database/index.htm


Answers to Questions about Final Exams from class

I'm not really sure when null will impact results. Can you provide some tips?

In row functions, a null parameter will produce null results. In a column function, null values will be ignored. A null value in the column being used as part of an inner join will cause the row containing the null to be excluded from the resulting table.

What do we need to know about the row, null, and date functions? What column functions do we need to know in depths?

You should know overall that row functions either create new data from fields with in a row, or the row functions (prince + price_increase_ will in some way change the data contained in a field Cint(number).
Column functions look at all of the data values in one column. You should know column functions are also called aggregate functions. The column functions you should know are min(), max(), count(), sum, and avg().

Are you trying kill us with these Query statements for the take home final?

Not kill you, but put on a bit of pressure. Pressure turns coal into diamonds.

For the take home final questions, are the answers like what we have done in chapters 10 - 13?

The answers will be similar to what we have covered in the last four chapters, but you may have to think and apply things a bit differently than the class examples.

Can online Access help assist with finding the answers on the take home final?

Possibly.

How many points will each question on the take home and the in class final be worth?

All questions will be worth 10 points. All questions can receive partial credit. Both tests will have an extra 10 pointa available for extra credit.

How do you properly do a union and a join?

Don't worry about unions, only joins will be on the test.

Explain in more detail inner joins.

An inner join allows information from two separate tables to be joined into one row of a results table. Joining is done on a column in both tables that contains the same information, for example the dept_code that is found in both the employees table and the department table. What happens when in a join is the resulting row will contain information from table a and information from table b where what ever the value of the joining field is equal. For example: One row in the employee table contains Joe Smith shp and the matching row in the department table contains shp Shipping. By joining the two table, the resulting row would be Joe Smith Shipping.
Basically, fields from a row in table a are selected to be placed in the result table, then the row in table b with a matching value in the join field is found and place in the resulting row with the values from table a.

What is the difference between summary data and detail data when using a grouping clause?

Detail data cannot by used in a select statement when using a grouping clause. Any column select from a table must either be used to divide all the rows in a table into groups, or as part of an aggregate/column function.

Groups formed on two or more columns - "Drill Down"

The author of your textbook calls using more columns for grouping "drilling down". Basically, grouping divides all of the rows of a table into groups. The more columns selected, the fewer the number of rows in each group will be. For example, grouping on the dept_code field will divide the rows in the employee table in smaller groups based on the value of the dept_code. All records in a group will have the same value in the dept_code field. If we then add another column, manager_id, the groups of records will be even further broken down first grouped by matching values in the dept_code field, then into groups with matching values in both the dept_code and the manager_id fields. The more fields in the group by statement, the fewer the number of records in each group.

What kind of things will I have to study for the test in the class?

The final is covering chapters 10-13 in the textbook. The in class test will focus on the why. The take home test focuses on the how.

What chapters will help me with the take home test?

All of them, but the focus is on chapters 10 - 13.


Assignments for class this week

Reading: None - study for final
Written: Term Project
Take home part of final exam

SQL Class Examples - Chapter13SQLExamples.txt


Course Description

Presents relational database concepts and organization. Students will learn to use SQL to query and change these databases and generate the output needed. Furthermore, students will design their own databases using one or more of the dominant relational databases, such as ACCESS or ORACLE. Three hours of lecture / two hours of laboratory.


Course Reference Material


Stumped??

Please remember that I am available 24/7 via e-mail - [email protected]

If you wish to talked to me directly, please e-mail me your phone number and the times you may be reached and I will call you.


Last Updated: 5/10/10