CST363 Week 8

This week was all about the final project. Our group (BITsoft) built a database system for "Fish and Friends" which is America’s premiere aquarium supply store. Our database keeps track of important
business information as well as general store information like inventory levels and the status of fish tanks in our store.

Most interesting was the SQL menu system:

SET define on
SET verify off
SET heading off
SET echo off

PROMPT 'Welcome to Fish and Friends';
PROMPT
PROMPT '1: Product Lookup           | 2: Employee Lookup';
PROMPT '3: Sales Order Lookup       | 4: Customer Lookup';
PROMPT '5: Vendor Lookup            | 6: Tank Water Alerts';
PROMPT '7: Tank Water Quality Input | 8: Create Invoice';

ACCEPT input PROMPT 'Enter selection:';

col menu new_val menuVar

SET term off
SELECT
   CASE '&input'
      WHEN '1' THEN '@productLookup.sql' 
      WHEN '2' THEN '@employeeLookup.sql'
      WHEN '3' THEN '@salesLookup.sql'
      WHEN '4' THEN '@customerLookup.sql'
      WHEN '5' THEN '@vendorLookup.sql'
      WHEN '6' THEN '@tankstatusLookup.sql'
      WHEN '7' THEN '@tankstatusInput.sql'
      WHEN '8' THEN '@createInvoice.sql'
      ELSE '@queryFishStore.sql'
   END AS menu 
FROM DUAL;
SET term on

@&menuVar

CST363 Week 7

This week we covered sequences (such as used as primary key), alias (temporary name for table or column), text processing (such as concatenation, lower, upper, initcap, ltrim, rtrim, lpad, rpad and substr) and date & time (to_char and sysdate).

For the assignment we built on last weeks lookup script:

set echo off
set verify off
set heading off
set feedback off
accept OtterIDT prompt 'Please enter the OtterID:';
prompt 'Thank you!';
SELECT 'Student OtterID: ', substr(OtterID, 0, 3) || '-' || substr(OtterID, 3, 2) || '-' || substr(OtterID, 5, 4) FROM student WHERE OtterID='&OtterIDT';
SELECT 'Name: ', upper(Name) FROM student WHERE OtterID='&OtterIDT';

CST363 Week 6

We covered additional SQL and SQLPlus commands including grouping and subqueries. The GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups. An example of this command is:

SELECT column1, column2
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2

A subquery is a query within another query and embedded within the WHERE clause. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. An example of this command is:

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
      (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE])

We also touched on SQL I/O. As a side note, it would be helpful if the class covered more troubleshooting commands like DESCRIBE as well.

CST363 Week 5

EDIT: This week we learned about ERD (Entity Relationship Diagrams). This is very useful for designing and documenting databases. The first is the Chen diagram, like so:


The next was the Crows Feet diagram, like so:



Both are obviously useful and I hope to put them into practice soon.

I have been saying that far worse than the possibility of loss of classified information is her blatant attempt to side-step the Freedom of Information Act. An editorial from the Milwaukee Journal Sentinel titled “Clinton’s abysmal record on open government” explains the probable political motive regarding Clinton’s unconventional email practices:
"The issue immediately at hand — and under investigation by the FBI — is Clinton’s use of a private email server for State Department communications. Clinton may have violated national security laws by making top secret documents vulnerable to hackers and available to people without proper security clearance…
In addition, regardless of Clinton’s excuses, the only believable reason for the private server in her basement was to keep her emails out of the public eye by willfully avoiding freedom of information laws. No president, no secretary of state, no public official at any level is above the law. She chose to ignore it, and must face the consequences…
And donations to the foundation from foreign governments have raised conflict of interest questions for Clinton as secretary of state, an office with power over foreign affairs and favors second only to the president’s."