Extra Blog; Structured Query Language (SQL)

On April 5th, me and my mentor met to learn about creating tables in the Oracle database and using SQL to retrieve, update, add, and delete records from the database. We also briefly went over how to convert my JIIS HTML pages to ColdFusion Pages and how the Financial Reporting System for my mentor's work uses the database. The following is the work assignment that lists out what we did:

First, we went over creating a table in the PL/SQL Developer. Creating a table was pretty straight forward. All we needed to do was right click on the "Tables" Object from the PL/SQL Developer and select "New..." Doing this displays a popup window that lets us define the name of the table, the columns/fields of the table, the datatypes for each column/field, and the primary key for the table. After doing so, the table that we created was listed in the Tables Object in the PL/SQL Developer.

Since there was no data within the table we had just created, we used an existing table to see how to retrieve, add, update, and delete records. We right clicked on a table named "Departments" and selected "Query data." Doing so displayed all the data within the Departments table, as well as showed us the SQL code that retrieved the data. My mentor pointed out that there was a little blue arrow that pointed downwards at the top of the table display. She said that this meant that there was more records than what was currently being displayed on the screen. Upon clicking it, the rest of the data was displayed (after scrolling  a long way down).

The SQL code that displayed was:

select * from Departments

This code retrieved data from the table "Departments" and displayed it on the screen. The select command retrieves and displays the data from the specified column(s). In this case, the columns specified was an asterisk (*). This mean that the indicated column to select was "all" the columns in the table. To indicate which table to select 'from,' the command from, followed by a table name allows the SQL code to know which table to retrieve all the columns from. In this case, the table is "Departments". 

My mentor showed me how, instead of selecting all columns, it is possible to just indicate specific columns from the Departments table to select from. This was done by typing each columns' name in place of the asterisk in the order that we wished to display them, each separated by a comma. Doing so would only display the specified column(s), once the code is executed by clicking the "Execute" button at the upper left corner of the PL/SQL Developer.

Following  the from command, there was more code that could be written. These were the where and the order by commands. First, the where command filtered what was being retrieved. It created a statement 'where' if the condition specified was true for a record, the record would be selected. If a record did not meet the condition, that record would not be retrieved and, therefore, would not be displayed. For example:

select * from Departments

where ID = '1234'

Doing this will only display the record(s) with a value of '1234' in the ID column/field. To specify multiple conditions, the word and can be placed directly after a condition. For example:

select * from Departments

where CITY = 'Pomona' and STATE = 'California' 

The second additional piece of code was the order by command. This ordered the records by a certain column in ascending order. The first records to be displayed were the records that had no value (null) in the field. Then, the records with a numbers and letters in the specified field would be displayed (ex: 1, 13, 1G, 2, 24, A, A3, AB, B45, BC, BW, C, ect.). For example, the SQL code

order by CITY

displays the records ordered by the CITY field.

After showing me the different pieces of SQL code for retrieving data from a table, my mentor then showed me two different ways to add and delete data from a table. The first way was right clicking on a table, selecting "Edit data," and editing the records from there.

The second way was selecting "Query data" and typing in for update after the initial select command. After running it, the displayed table had a plus and minus button in the menu bar, as well as a "Post changes" button. Clicking the plus button would create a new record row in the table, while selecting a record row and clicking minus would result in deleting the selected record row. Once changes have been made, it is required to click the "Post changes" button to save the changes. To confirm the changes were made successfully, my mentor executes the SQL code again. To delete all the records in a table, we can type the SQL code:

delete from Departments

This delete command can also contain a where command.

In addition to showing me SQL code in the PL/SQL Developer, my mentor also showed me that, for my current HTML programs, I can open them in Adobe Dreamweaver where I would be able to convert them to ColdFusion programs. This is done by changing the file extension from .html to .cfm.

Afterward, my mentor taught me how the FRS Web Application uses a database. She showed me FRS tables, views, and stored procedures. The tables were organized similar to the Department table; they just had different values pertaining to different things. She then showed me existing FRS View Objects, which contained SQL code that contain combinations of tables to be used for specific inquiries. She then showed me Stored Procedures and explained that they were like sub-programs to save multiple SQL statements in a single procedure and to use parameters in conjunction with the SQL statements.