Web Programming

Web Programming Test #2, October 24, 2000

I have not violated the Honor Code in completing this test.

Signed: ___________________________________________

READ THESE NOTES
Sign the pledge and initial each sheet. SHOW ALL YOUR WORK.
Do as many questions as you are able.
DO NOT spend an excessive amount of time on any one question since all questions count the same.
You may OMIT any ONE question.
You MUST indicate which question is to be omitted by circling its number and writing the word OMIT next to it.

A Data Definition Language is used to ____
A. retrieve data from a database.
B. define Tables, Keys, Domains and other internal database components.
C. sort records in a database.
D. modify data in a database.
E. make complex objects accessible to the user.

Tri-State Bank (TSB) is licensed to serve an area that covers parts of three states in the southwest Virginia area. TSB utilizes a small database application whose tables are given below.

STATES	CUSTOMERS	ACCOUNTS	ACCOUNTTRANSACTIONS
StateCode	SSN	CustNum	CustNum
StateName	CustNum	AcctSpecifier	AcctSpecifier
	Name	AcctType	DocNum
	Address	JointNum	TransType
	City	CurrBal	Amount
	State	BalanceDate	Date
	ZIP
	Phone

NOTES:
The STATES table is simply a list of the three states that TSB's customers may reside in. It is used for referential integrity for the State field of the CUSTOMERS table.
CustNum is a customer number used as the primary key of the CUSTOMER table.
AcctSpecifier in the ACCOUNTS table is an integer used to identify one of a customer's accounts since a customer may have more than one account.
CustNum and AcctSpecifier taken together define the primary key of ACCOUNTS. The key may be referred to as AcctNum (the account number).
AcctType in ACCOUNTS lists the valid types of accounts; C for Checking, S for Savings and O for Other.
CurrBal is the current balance in an account as of the BalanceDate.
JointNum in the ACCOUNTS table is the Customer Number of a customer who is the joint holder (along with the cusomer number in the CustNum field) of the account. If the account is not a joint account then JointNum is empty.
DocNum in ACCOUNTTRANSACTIONS is an identifier for a transaction document; for example, a check number.
TransType in ACCOUNTTRANSACTIONS defines the types of account transactions; D for Deposit, W for Withdrawal.

Give an SQL query that will display the Name and SSN, sorted by Name, of all Virginia customers.
Give an SQL query that will display the Name and Customer Number of all customers who live in a zip code that begins with 229.
Give an SQL query that will return the sum of all the money in all customer accounts (i.e., the bank's entire holdings).
Give an SQL query that will return the Customer Number and the sum of all deposits made during the month of October, 2000. The query should return one record per customer.
Hint: You will need to use the SUM and GROUP BY verbs.
TSB offers special Christmas Club Savings Accounts that have an Account Type (AcctType) code of K. These accounts do not pay monthly interest but on November 1 the balance (CurrBal) in all such accounts is increased by 3%. Give an SQL statement that will do this.
Give an SQL query that will return the Customer Number of each customer who has had a transaction during the year 2000. The recordset should have one record per customer - no duplicates.
TSB does not normally return cancelled checks to its customers. Instead it offers a service whereby customers may access online a form/report that resembles a check and contains most of the data entered on customers' checks. The data displayed includes the customer's name, the account number, the check number, the amount and the date.
Give an SQL query that returns the above data for all checks for a particular account number.
Hint: Your query will contain two joins.
Tri-State Bank (TSB) is to take over One State Bank (OSB). Both banks have the identical format for their CUSTOMERS tables. Give an SQL statement that will combine the CUSTOMERS tables of the two banks while preserving the bank of origin for each record by creating a new field called BankOfOrigin that will contain either 'TSB' or 'OSB'.
A web page is to display a photo of a zebra (zebra.gif) on the left side of the screen. Immediately to the right of the zebra photo is a few lines of text telling who took the photo and where and when it was taken. Immediately beneath the zebra photo is a brief description of the location of the country of Chad in Africa. The zebra image is larger than either block of text.
Give HTML code to create this.
Name one disadvantage of using a header tag (Hn) instead of using a font tag to create larger, bold type.
Create a hyperlink that points to the Lynchburg College Technology Classroom web pages so that the words "Lynchburg College Technology Classroom" may be clicked on to follow the link.
Create a hyperlink that enables one to click on an image called LC.jpg to connect to the main web page for Lynchburg College.
Create a table with a thin border that has 2 rows and 12 columns. This first row contains the names of the 12 months of the year and the next row contains the number of days in each month. (Don't worry about leap years).
Write HTML code whereby someone viewing the web page can click on your name and invoke a mail program to send mail to you.

Create an HTML table that displays the following field names from the CUSTOMERS table of TSB as follows.

CustomerID                Customer SSN

Customer Name
Customer Address          Customer Phone
City State ZIP

Explain why a font like Courier should be used when displaying columns of numbers?
For example:
```
 125.60
  32.13
   7.22
 ------
 164.95
```
What does the IP address 127.0.0.1 correspond to?
Why should one never use a drive letter in a hyperlink reference?
A web manager wants to store her server-side scripts in a folder called Scripts just below her main (root) folder. What must she do to enable server-side scripts located in this folder to execute?

Top of this page Top of page

Home page