CIS 113 Database Management Systems - 3 UNITS
Provides a solid foundation in database management systems, concentrating on the benefits, structures and views of data. You will analyze the existing database design methodologies, and use Oracle and SQL.
CIS 10 Small Computers for Business, CIS 17 Computer Applications for Business Communica-tions, or consent of the instructor. To take this class, you should be familiar with basic Business Application concepts and Windows Operating Systems. If you have any questions regarding the prerequisites for this course, please see the instructor as soon as possible!
The Practical SQL Handbook, 3rd Edition, by Judith S. Bowman, Sandra L. Emerson, and Marcy Darnovsky, Addison-Weslley Developers Press.
Optional:
Oracle: The complete reference by Koch/Loney, McGrawHill/Oracle Press
Fundamentals of Database Systems, 2nd Edition, by Ramez Elmasri and Shamkant B. Navathe, Addison-Weslley Publishing Company
In this course, students will learn the principles underlying database management systems and Structured Query Language (SQL) programming. Upon completion of this course, students will understand the techniques and methodologies of database management systems; limitations and applications of the various database management systems; and exposure to relational database programming. By successfully completing this course, student should be able to do the following:
Instructor Symon Chang has taught many classes in US, and published many Computer Programming and Internet articles on computer magazines in Taiwan.
Lab 3 -- Select Data from SQL Anywhere
1. Bring up SQL Anywhere
To use the bookbiz database, click on the 'isql' file or icon. (On Windows NT, this will be in the
Sybase SQL Anywhere program group in the Program Manager.)
When the three-panel Interactive SQL window opens, type 'connect' in the Command panel at the bottom of the screen and click the 'Execute' button. (Or F11)
An Interactive SQL Logon window opens.
Fill in the form using the values shown here:
User ID: DBA
Password: SQL
Connection:
Database Name: bookbiz
Database File:
<complete path of bookbiz.db -- for example, E:\sqlany50\bookbiz.db, or G:\sqlany50\win32\bookbiz.db>
Server:
Startline:
Leave Connection, Server, and Startline blank.
Click OK. This will return you to the Interactive SQL Window.
When the 'Execute' button in the Command panel becomes available, erase the 'connect' command by backspacing over it or by picking the New option from the File menu at the top of the window.
2. Select from Database Tables
We will look into four database tables in bookbiz database. They are:
Use select all statement to see columns in each table, and the sample data in each table.
For example, the SQL commands are:
select * from titles;
select * from Authors;
select * from editors;
select * from publishers;
Type in the command, such as:
select * from publishers
and click the 'Execute' button. The results of the query appear in the top Data panel.
Syntax of select statement
SELECT [ ALL | DISTINCT ] <select_list>
<select_list> ::=
{ * | { table_name | table_alias }.* | { column_name | expression } [ [AS] column_alias ] | column_alias = expression } [,...n]
Arguments
ALL
Specifies that duplicate rows can appear in the result set. ALL is the default.
DISTINCT
Specifies that only unique rows can appear in the result set. Null values are considered equal for the purposes of the DISTINCT keyword.
<select_list>
The columns to be selected for the result set. The select list is a series of expressions separated by commas.
*
Specifies that all columns from all tables and views in the FROM clause should be returned. The columns are returned by table or view, as specified in the FROM clause, and in the order in which they exist in the table or view.
table_name | table_alias.*
Limits the scope of the * to the specified table or view.
column_name
Is the name of a column to return. Qualify column_name to prevent an ambiguous reference, such as occurs when two tables in the FROM clause have columns with duplicate names.
Is a column name, constant, function, any combination of column names, constants, and functions connected by an operator(s), or a subquery.
column_alias
Is an alternative name to replace the column name in the query result set.
3. Database Design Questions
Review the output of the select statements, and answer the following questions:
Authors --
Titles --
Publishers --
Editors --
Authors
Titles
Publishers
Editors
Lab 4 -- More Select Statement
Examples:
To select cities where authors live:
select city from authors where state = 'CA'
However, the output will look like this:
city -------------------- Menlo Park Oakland Berkeley San Jose Oakland Berkeley Palo Alto Covelo San Francisco Walnut Creek Oakland Oakland Oakland Palo Alto Vacaville
To remove duplicates, the SQL command should change to:
select DISTINCT city from authors where state = 'CA'
Lab problems:
title ----------------------------------------------------------------------- The Busy Executive's Database Guide Cooking with Computers: Surreptitious Balance Sheets You Can Combat Computer Stress! Straight Talk About Computers
Sample output:
Author -------------------------------------------------------------- Green, Marjorie Straight, Dean Stringer, Dirk MacFeather, Stearns Karsen, Livia
Sample output:
city -------------------- Berkeley Denver Oakland Chicago Boston Rockville
Sample output:
pub_name ---------------------------------------- Algodata Infosystems
For example: In our TIPS application, we want to find out how many hours should we charge to each client in each month. Therefore, we need to get the following information:
Client Name from the client table Client's project from Project table Employee's rate from employee table Hours to charge from hour (rate) table Month information from hour (rate) table
You need to make sure that you have relationship built from all above tables.
This is this TIPS Picture:
[ Back to Home | Previous Page | Next Page | Any Page ]
Symon Chang Symon & Associates, CA. U.S.A symonchang@earthlink.net
Last modified: 11-14-99