The book my mentor recommended to me.
(Less pretty than that other book)
Table of contents of what I read.
First of all, there are many different types of databases, some of which are more commonly used than others, depending on the system that is being used. Types of databases are Flat-file Databases, Hierarchical Databases, Object-Oriented Databases, Relational Databases, and many others. My mentor, who is in charge of the FRS system, uses the Oracle Database and is a Relational Database. In order to access the Oracle database, she uses the PL/SQL Developer Tool. To have access to the FRS data records from the database in the PL/SQL Developer, she uses SQL code. SQL is an acronym for Structured Query Language and it is the programming language used to retrieve, add, update, and delete data records from the tables in Oracle databases. My mentor walked me through how to use SQL code to retrieve FRS records from a specific table as well as the concepts of the Relational Database.
In the Relational Database, each table consists of one or more records, or rows, that contain the data for a single entry. The tables have primary keys which uniquely identify each row. The tables can also be defined by one or more indexes which provide an efficient way to access data from a table based on the values in specific columns. Relational Database tables can relate to each other between primary keys and foreign keys, the keys that refer to the primary key of another table. When a row in a table relates to one or more rows in another table based on the keys, it is called a one-to-many relationship. When two tables are related via a foreign key, the table with the foreign key is referred to as the foreign key table and the table with the primary key is referred as the primary key table. There are also one-to-one relationships and many-to-many relationships.
Each column in a database needs to be defined a data type to determine how the computer reads each row in the column. The data type also determines what can be stored into that column, as well as the length, precision, and scale of the data. It is also possible to make the columns “nullable” in that it allows a null value to be stored in a field. A null value is like an “empty” space. It is a value that represents that something is occupying that space, but the value itself is unknown. Default values can also be assigned to each column. When new rows are added to this column, the row will contain the specified default value. Identity columns can also be made. New rows that are added to this column will contain a numeric value that is generated automatically. To restrict values that a column can hold, you can define check constraints. Check constraints can be defined at either the column level or the table level.
One of the major portions of information that my mentor gave me is the actual use of SQL code to obtain data records from the database. There are four SQL statements I learned that retrieve, add, delete, and update data. They are the Select, Insert, Update, and Delete statements.
The Select command retrieves and sorts selected rows and columns from a specified table. An example is as follows:
Select ProtuctID, Name, UnitPrice
From Products
Where CategoryID = ‘props’
Order By UnitPrice
This will result in a table being displayed, consisting of all the selected columns (ProtuctID, Name, and UnitPrice) from the specified table (Products) that meet the condition where the CategoryID is equal to ‘props’. The table will then be ordered by the specified column (UnitPrice) in alphabetical order or least to greatest if it is a numeric value. The column names can be replaced with * which specifies all of the columns in that table.
The Insert command adds a row into a table with a certain value. An example of this is as follows:
Insert Into Categories (CategoryID, ShortName, LongName)
Values (‘food’, ‘Spooky Food’, ‘The very best in Halloween cuisine’)
The values specified (‘food’, ‘Spooky Food’, and ‘The very best in Halloween cuisine’) will be inserted into new rows in the corresponding columns (CategoryID, ShortName, and LongName) in the specified table (Categories).
The Delete command is similar; it removes a certain row in the table. It is coded slightly differently:
Delete From Categories
Where CategoryID = ‘food’
The command will delete the row(s) of the specified table (Categories) that meets the condition where CategoryID = ‘food’.
The Update command consists of setting the value of a row equal to be equal to a new value.
Update Categories
Set ShortName = ‘Halloween cuisine’
The value of a certain column (ShortName) in the specified table (Categories) will be set to a new value (‘Halloween cuisine’) if the condition is met (Where CategoryIN = ‘food’).
The Relational Database can contain other database objects like Views. They act like tables, but aren’t technically tables themselves. Views are predefined queries that are stored in a database and are accessed by issuing a Select statement that refers to the view. The View creates what is known as a virtual table, which is a temporary table that’s created on the server. Views consist of simple SQL statements.
The Relational Database can also contain Stored Procedures which can contain more complex and generally more SQL code than views. Stored procedures can use the insert, delete, and stored commands.
The values specified (‘food’, ‘Spooky Food’, and ‘The very best in Halloween cuisine’) will be inserted into new rows in the corresponding columns (CategoryID, ShortName, and LongName) in the specified table (Categories).
The Delete command is similar; it removes a certain row in the table. It is coded slightly differently:
Delete From Categories
Where CategoryID = ‘food’
The command will delete the row(s) of the specified table (Categories) that meets the condition where CategoryID = ‘food’.
The Update command consists of setting the value of a row equal to be equal to a new value.
Update Categories
Set ShortName = ‘Halloween cuisine’
Where CategoryIN = ‘food’
The value of a certain column (ShortName) in the specified table (Categories) will be set to a new value (‘Halloween cuisine’) if the condition is met (Where CategoryIN = ‘food’).
The Relational Database can contain other database objects like Views. They act like tables, but aren’t technically tables themselves. Views are predefined queries that are stored in a database and are accessed by issuing a Select statement that refers to the view. The View creates what is known as a virtual table, which is a temporary table that’s created on the server. Views consist of simple SQL statements.
The Relational Database can also contain Stored Procedures which can contain more complex and generally more SQL code than views. Stored procedures can use the insert, delete, and stored commands.
.png)
.jpg)
.png)
.jpg)
.png)
.png)
.png)