Gilberto Moreno
Computer System Technology Graduate
&
New York College of Technolgy
- Web Developer
- Front and Back-end
- Content developer
- Database Engineer
Introduction to Databases
ORACLE DATABASE
Practice with Database Editor
ORACLE XE INSTALLATION
Here is the link to Oracle XE:
https://www.oracle.com/database/technologies/xe-downloads.html
Also, make sure that you have the latest install in your machine. Java link:
Here is the link to Oracle XE:
https://www.oracle.com/database/technologies/xe-downloads.html
Also, make sure that you have the latest install in your machine. Java link:
UNIT 1: A QUICK GLIMPSE OF DATABASE
What’s the purpose of a database?
Its purpose is to store, organize, manipulate, or delete an indefinite amount of valuable/collective data. Type of data can range from text, number, images, videos, music, and much more. For instance, schools use databases to keep track their students, staff members, classes, or results from each students.
Now why use a database instead of using an Excel spreadsheet that can also handle the data almost the same way a database?
This is where we start to talk about relational databases and relational database management systems.
What is a relational database?
Before relational databases, the relational database theory and rules was created by Edgar F. Codd in 1970. Data editing was difficult because the data storage resided in different locations. Let us talk first about Relational Database Management System. RDBMS is software that it retrieves, creates, and manipulates data by using a language called Structured Query language (SQL).
Relational Database
A relational database has data independence, the old databases had data stored in different files, and the process of retrieving was complicated. Now, with RDBMS, users can benefit from data consistency and data integrity. What this means, for example, if an employee works for the HR department and he/she can only work in one department, the assumption is that the department records should not be duplicated or contradict the data in the database. Before we can move to the structure of databases, let’s look at how it works.
A database store information every time that you fill a form online or register on a website.
Let us look at what is involved in the transaction. For example, you decide to sign up for an online appointment at your local clinic. Before you can sign for your appointment, you need to create an account. The signup process consists of storing the user their personal information on the new account. Therefore your account would be retrieved from the database server to be accessed by you in order to make appointment in the future.
How databases work in a business environment:
Let’s look at the mechanism of the business sides of databases and how they work.
Every time that you fill a form online, the database stores the information in a database.
Let’s look at how the process is involved in the transaction.
Example:
TABLES AND ENTITIES
In a relational database, tables store the information. Think about tables as manila file folders or containers that store things. The folders or containers have a meaningful relation to each other. For example, before you have computerized software, a business owner, in this example, a Gym used to save their information on file folders.
On these folders, the owner kept information that was relevant to the functioning of the business. That might include the student’s information, billing, and employees.
The easy way to think about tables is to image what folder the business owner needs to keep track of the business. Tables have unique names and are also call Entities. What are tables or Entities? They are things or nouns.
Lets imagine that you are organizing a relational database for a Gym. What would be some information that your client would need to store in the database?
COLUMNS AND ROWS
The organization of the tables consists of columns and rows.
Columns are unique and represent one single attribute about an entity or table.
We establish that Entities are either nouns or things, and column names are attributes for those nouns or things. If you have an Entity call student, the attributes for this Student Entity would be. The student’s first name, student’s last name, student address, student email, student phone number, and more.
Rows represent a single input of information into the database.
For example, after registration is filled and submitted, the information is sent through the network to a database server to be stored. Each row saves an entry.
Columns are vertical and rows horizontal.
DATATYPES
Datatypes identify column names. What are datatypes? Let look at this form.
Each column name on the form requires input from the user. Each input of information is associated with a specific storage format, constraints, and a valid range of values.
For example, to input the value of 001 to the column name studentID, I must format my table to accept a number as a value. Another example is the
Figure 21, column name Photo. Before I can upload an image to my database table, I need to make sure that the column name Photo has a data type that accepts image files.
Most common datatypes.
• NUMBER(p,s) – [(optional precision, [optional scale])] Stores zero, positive, and negative fixed- and floating-point numbers. The allowable values for precision are 1 to 38 and for the scale –84 to 127.
• VARCHAR2 – (size Bytes|Char) Is a variable-length character string with a maximum size of 4,000 characters or bytes. A length must always be specified. (If your database encoding scheme is a single byte, the numbers of bytes and characters are identical. If you use multi-bytes to support specific foreign languages, a character may consist of more than one byte. In this case, consider using NCHAR and NVARCHAR2 datatypes.)
• CHAR [optional size] Holds fixed character length data with a maximum size of 2,000 characters; the default size is 1. When a character is left empty, the data fills the space with a blank.
• LONG Character data type with a maximum storage capacity of 2GB. LONGs are subject to several restrictions, and Oracle recommends that you transform data from this data type to CLOBs.
• CLOB Stores large text objects. Use this instead of the LONG data type. Also useful for storing XML objects.
• DATE Stores date and time, including seconds, from January 1, 4712 B.C., to December 31, 9999 A.D.
• TIMESTAMP [optional seconds precision] Same as DATE but includes fractional seconds precision from 0 to 9 digits. The default is 6.
• TIMESTAMP [optional seconds precision] WITH TIME ZONE Same as TIMESTAMP, including the time zone displacement value.
• BLOB Stores unstructured large binary objects. Graphic images, video clips, and sounds use BLOB.
• BFILE Similar to BLOB, but points to a large binary file stored outside the database. The file can only be read but not modify. Oracle requires an appropriate operating system–level read permissions on the file.
SELECTING THE CORRECT DATATYPE
Let’s look at the Student table.
The column name StudentID holds an input that has a data type set as NUMBER. Now LastName holds an input that accepts only text, and DOB accepts only input that has the format of dates. What happened if your input has number, text, and special characters in the input? Well, then you select the data type VARCHAR to make sure that your input accepts all types of values. Just like Phone has a data type of VARCHAR2(12) and accepts numbers and hyphens 347-987-2345.
Let us start by looking at the column Name StudentID on the Gym registration form. When the database design created this form, he/she selected numbers as datatypes for ID inputs. Additionally, the designer narrows the input to the value of three numbers in which each number can have the minimum value of 0 and the maximum value of 9. As the picture shows, the minimum value for a StudentID can be 001, and the maximum is 999. Continuing, let’s look at the column name State. So, the state holds a data type of CHAR (2) that requires the user to input on characters or text. How many characters? Well, in this case, it only allows us to input two, which makes sense since states have two to letters as a state abbreviation. Also, from the developer side, it prevents the user from making mistakes when inputting the state’s abbreviations.
The last datatype that I would like to focus on is the phone. The phone is a column name that has a data type of VARCHAR2(12). Now, how the designer concluded selecting 12 as the maximum value for special characters? If you think about it, mostly phone number has ten numbers. However, when you input them on a form, usually, you include dashers or hyphen between the numbers. How many hyphens? Well, the standard in the US is at least two — one after the area code and one after the three first numbers of the phone number. You are ending with the value that allows input for 12 characters. As shown below here.
How to select the correct value for your datatype?
One again, let’s look at the Gym registration form.
Let us start by looking at the column Name StudentID on the Gym registration form. When the database design created this form, he/she selected numbers as datatypes for ID inputs. Additionally, the designer narrows the input to the value of three numbers in which each number can have the minimum value of 0 and the maximum value of 9. As the picture shows, the minimum value for a StudentID can be 001, and the maximum is 999. Continuing, let’s look at the column name State. So, the state holds a data type of CHAR (2) that requires the user to input on characters or text. How many characters? Well, in this case, it only allows us to input two, which makes sense since states have two to letters as a state abbreviation. Also, from the developer side, it prevents the user from making mistakes when inputting the state’s abbreviations.
The last datatype that I would like to focus on is the phone. The phone is a column name that has a data type of VARCHAR2(12). Now, how the designer concluded selecting 12 as the maximum value for special characters? If you think about it, mostly phone number has ten numbers. However, when you input them on a form, usually, you include dashers or hyphen between the numbers. How many hyphens? Well, the standard in the US is at least two — one after the area code and one after the three first numbers of the phone number. You are ending with the value that allows input for 12 characters.
UNIT 2: PRIMARY KEYS, FOREIGN KEYS, RELATIONSHIPS KEYS
What are Primary Keys? Primary keys are unique identifiers given to tables to create a unique key that would be representing that entity. It would be used to quickly reference a row within the table. Lets think about what makes a person unique in the US. A last name or a birthday is partly unique but then you’d run into the problem of millions having the same last name or same birthday. In the US, a unique identifier is a Social Security number. Each person in the US has a unique number that cannot be changed. How can we create a unique identifier without using a Social Security? Let’s use an example of a database for a gym. First, you’d have to name the column that holds the unique identifier within the table. Second, select the data type that bests represents the business requirement. A business requirement is having a detailed description of what is the business operation. Lets say you have two tables, INSTRUCTOR and MEMBER. Let’s think about what would suit best of a name for the unique identifier. The first thing that would come to mind if naming it ID, but that would cause problems within your database if you have all of your tables unique identifier as ID. The best option is to give an acronym to each table that would differ from each other. For example, for MEMBER we could use memID, member_ID. For INSTRUCTOR we could use instru_ID, instructorID.
In the interview with the gym owner, he explained that his instructors have different expertise. Some of them teach cardio other boxing and so forth. He would like to have an identifier that shows what type of modality they teach. For example, if you’re teach boxing, your ID show start with B follow numbers. In this case, B001 represents the first boxing instructor in the database. Now, if you have selected values that incorporate numbers and characters, the only data type that is the best suit for this column name is VARCHAR.
FOREIGN KEYS
How is the relationship between the tables created?
First, you need to add a foreign key. A foreign key is necessary to enforce referential integrity on an Oracle database. What is referential integrity means? The values of the primary key in one table must match the value of the foreign key in the other table. The table that has the Primary Key is the parent table, and the table the holds the foreign key is the child table. The foreign key in the child table reference a primary key in the parent table.
What does this mean? Let’s continue with the gym example. For this example, we establish that we need to give each table a unique identifier. Further, ahead we’ll look at normalization. But for now, let us keep it simple. On our database, we added one more table called BILL. The reason for a BILL table is that the business owner would like to know when his students or members are paying their membership. As I point out this before, we will look at normalization down the road. In this case, I have divided that information for the member in two tables. One table “MEMBER” holds all personal information like name, address, Emergency contact, email, phone, picture, and so forth. The other table “BILL” holds the member billing amount, date of payment, payment type, type of membership, discounts, and so forth. Since the bill table belongs to the member, I need to find a way to connect one table to the other.
Therefore, we use the unique key, primary key, and place it on the other table that we want to connect. In this example, we get MemberID that is a Primary Key on the MEMBER table and place the key on the BILL table, turning the Primary Key into a foreign key. The key is the same and holds the same value for both tables. The parent table is the table that has the Primary Key, and the child table is where the Primary Key turns into an Foreign Key. There are other types of keys with different rules, but that is for more advanced SQL.
TABLE RELATIONSHIP
In a relational database, tables have relationships. What this means. Let us look at figure 25. A gym member’s responsibility is to pay for his/her membership. This gym database has a table MEMBER that holds all the personal information about the member, and the BILL table that holds all the information about the member billing. To connect both tables, the member table, and the bill table. The member table placed the primary key on the bill table as a foreign key creating a relation between both tables. There are different types of relationships, they are called Cardinality.
We are going to look at the common types of relationships.
ONE-TO-MANY RELATIONSHIP (1:M)
For example, in a gym, a member can take many classes like yoga, boxing, strength & conditioning. So, it is easy to see that one member can take many classes. The relationship type between MEMBER and CLASSES is ONE to MANY.
ONE-TO-ONE RELATIONSHIP (1:1)
On a database design, the One-to-one relationships happen, not commonly used because the information “data” most of the time can be placed in one table for easiness. Let us look at one example.
For this example, we are simplifying by combining all the attributes in one single PRODUCT.
MANY-TO-MANY RELATIONSHIP (M: M)
When presented with a many-to-many relationship is advised that to create an intersection table, transforming the relationship to (1:M). A typical example is a book and author M: M relationship. In this relationship, a book can have one or more authors, and at the same time, an author can write one or more books — that why this relationship is an M: M relationship. The best practice is to turn this M: M relationship to a 1: M.
What is the purpose of this transformation?
This relationship does not tell me how much each author gets paid for each book. Since more than one author writes each book, I have figured out how much royalty each author gets per book. Thus creating an intersection table
NORMALIZATION
What is normalization?
Tables on databases have structures, as we have seen before, with column names and rows. Normalization is a method used for assessing and fixing table structures to minimize the repetition of information “data redundancies” on the database. Eventually, reducing data anomalies.
Normalization rule of the normal form
- First Normal Form
- Second Normal Form
- Third Normal Form
Unnormalized, Let’s look at this example.
Looking at this example, you can see that the unnormalized table has four almost identical column names called class1, class2, class3, and class4. Indicating that the student registered in four classes. As a result, the data display might not represent a clean and easy database design. Let’s go and look at how this unnormalized form might look on a real site.
1NF – First Normal Form: No Repeating Groups
In the first normal form, state that repeating groups are not allowed. Also, when repeating groups are present, they need to be placed in a separate table. Tables should have only two dimensions on this example since the student might register for more than one class. Classes should have a table of their own. Since one student registered for several classes, these classes should appear in a separate table. Fields Class1, Class2, Class3, and Class4 in the above records are indications of design trouble. Creating a new table called CLASS normalizes the database.
2NF – The second normal form asserts that all “non-key” columns must depend on the entire primary key. It applies only to tables that have composite primary keys. What does this mean?
Figure 31 shows the STUDENT_CLASSES table with both the Student# and Class# as the composite primary key. In this example, Classes# 600-34 and 200-21 have the same student# of 1001. However, each class receives a different amount of book allowances for different classes, although the student is the same.
A composite key or concatenated primary key is when a table has more than one column name as a primary key.
3NF – The third normal form goes more profound than the second normal form. On the 3NF, all the column names that are non-key, meaning they are neither a primary key nor foreign key, must be connected to the table’s primary key. The Professor-Number column is not dependent on the primary key column, Student#, but on the column name Professor. Therefore, the Professor-Number column should not be part of the Student table.
DATABASE DEVELOPMENT TEXT
Let us look at the process starting from the design thoughout the development. The cycle of building a database goes through from the idea, data modeling, logical and physical design until the final system implementation.
- Requirements Analysis
- Conceptual Data Model
- Logical Design
- Physical Design
- Implementation
First, you need to brainstorm on the purpose of the database. We already cover the business needs. In other words, what is that the business needs that the database requires to accomplish. Let us look at a simple example. A business owner of a small karate dojo needs to track payments from his student. Also, he would like to have a database that has information on his students.
The first step is to analyze what are the column names needed in each table. The best method is to write them on a piece of paper and think about what possible relationship that the tables may have.
Once you have established all the tables that you need their column names and relationship, you move to build the Logical data model and the physical data model.
Logical Data Model
Building a logical data model or entity-relationship diagram (ERD) allows you to see all the tables, column names, and their relationship. Also, the ERD may include column names datatypes as well as primary key, and foreign keys.
Physical Data Model
Creating a physical data model allows the programmer and database designer to see the database design implementation. On the physical data model, you can see all the relationships, database normalization, column names, data types, and their values.
CREATE AN ENTITY-RELATIONSHIP DIAGRAM (ERD)
Step One is to see if you have Visio from Microsoft installed on your computer. If you don’t have the software, there are some free options available online and standalone software. The example made is with Visio. However, most of the software’s out there have the same graphical user interface.
Here are some free options.
https://www.lucidchart.com/pages/
Let us start by opening Visio on your pc or mac.
Click New and select Software and Database
Next, select Crow’s Foot Database Notation and click Create
Use the pointer tool to grab an Entity to the canvas. (Drag and Drop)
Use the Pointer tool to select and Drag and Drop.
A Text is to add text to the entity.
Use the connector to create relationships between tables.
First, with the Point Tool selected, grab an Entity located on the left side of the document and drop it on the canvas.
Use the Pointer tool to Drag items to the screen
Next, change the name of the table by clicking on the table name located on the blue tab of your table. Do the same process with all column names. Use the A Text tool located on the top ribbon in the tool part
Notice that there is a tab with a blue square with the letters PK. It would be best if you used this column name for the unique identifier. In this case, since StudentID is the unique identifier, you should place the primary key on this tab.
The next step is adding the Foreign key and then connecting the tables.
To add a foreign key Right-click on the column name, that holds the foreign key and select FK.
The last step is to add a connection or relationship to the tables. Select the connector and drag and drop the connector to one table and then grab the other end of the connector to connect to the other table.
The last step is to add a connection or relationship to the tables. Select the connector and drag and drop the connector to one table and then grab the other end of the connector to connect to the other table. The result should look something like this:
The last step is to add a connection or relationship to the tables. Select the connector and drag and drop the connector to one table and then grab the other end of the connector to connect to the other table.
UNIT 3: CREATING THE DATABASE
As reviewed in the previous unit, start by creating the database ERD. For this example, I provided the ERD. We’ll work with the GAME_StoreDB database. GAME_StoreDB is a collection of game orders, customers, developers, and more. Now let’s start creating our database. It’s important to know that there are many ways to create a database. We’ll be looking at one method that requires notepad and Oracle SQL Plus.
Data Manipulation Language (DML) and commands
But first, let us look at Data Manipulation Language (DML) and commands.
The DML commands are used to query, insert, update, and delete data.
Here are some (not all) SQL commands with their description.
- For Data Manipulation SELECT, INSERT, UPDATE, DELETE
- For Data Definition CREATE, ALTER, DROP
- For Data Control GRANT, REVOKE
- For Transaction Control COMMIT, ROLLBACK
We’ll look at some, but not all.
Create a database with Notepad
Open notepad on your pc. Notepad exists on the Windows accessories folder. Launch notepad. Created a folder for your work. I am creating a folder called GAMES. Once you have opened the notepad, the first thing you need to do before starting to create the database is to save the file. Remember, this is one method.
- On the notepad select File, a save as
- Give the same of the database that you are creating.
- In this case, call GAME_STORE.
- On spaces in databases name or column names.
- Filename: GAME_STORE with the SQL file extension
- your file should be GAME_STORE.sql
- Save as type: All Files (*. *)
- Save the file inside the GAMES folder that you created.
After saving the file, inside your folder, you should have a document that has a little cylinder next to the file.
Ok, if you got this far, you did GREAT!
Let start with the creation of your tables.
Now, your database is empty because we have not added anything to the file yet.
Open the file by right-clicking and selecting open with notepad.
The next steps are a little like following a recipe.
You need SQL commands. In this case, you need the CREATE command.
This command allows you to create the tables. It would help if you looked at your ERD to see the table names, column names, data types, and requirements.
IMPORTANT! Create the tables that don’t have Foreign keys first. It cannot violate constraints, which means how you can have a foreign key on a table if that table does not exist yet
CREATING TABLES
Looking at the ERD, I have four tables that do not have a Foreign key. Customer, Developer, Game_Publisher, and Promotion. Start with the customer table.
SQL Syntax
CREATE TABLE CUSTOMER ();
Commands are upper case as well as the name of the tables. Some database designers use lower case commands and table names. However, get in the habit of doing it correctly. Inside the parentheses, you define your attributes. Commas separate each column name, and the table ends with a closing parenthesis and a semi-colon.
Let’s look at the database table and the ERD side by side.
I look at the ERD specification as a guide to writing the tables. The first attribute or column name is Customer ID. The ERD shows that CustomerID is the primary key or unique identifier. The primary key would be a form of constraint add to the table.
After the Column name comes, the data type with its value the constraint, and the constraint name.
ColumnName | Datatype(p) | CONSTRAINT Key word | PK_NameOfTable | Constraint Name. |
CustomerID | NUMBER (4) | CONSTRAINT | PK_CUSTOMER | PRIMARY KEY |
Table 2
Note there are many types of constraints we would look at PK and FK only.
Ok, if you got this far, you did GREAT!
Creating Tables with FK
Now let’s move to creating tables with a foreign key. Here we can see how constraints affect the table’s data.
Let go back to our ERD. I can see that some tables have one foreign key, and others have a foreign key that is also a primary key. They have a composite key. A composite key or concatenated primary key is when a table has more than one column name as a primary key.
The next set of tables has a foreign key. However, the FK does not act as PK (composite key). The tables GAME_ORDER and GAME have the foreign key, but they do not act as a composite key. The relationship is One to MANY.
After the Column name of the foreign key comes the data type with its value and then the constraint followed by the type of constraint “FK,” name of the child table followed by the name of the parent table and continued by references to the parent table and its primary key.
TABLE GAME_ORDER
Table 3
To finish the database, add the tables with composite keys. Create tables, ITEMS, and GAME_DEVELOPER.
Creating Tables with Composite Keys
To add a composite key to a table, use the same method for the foreign key, and add a second constraint to the table specifying that the new constraint holds two foreign keys that act in combination as primary keys.
Table 4
Your database with all the tables should look like this.
STARTING SQL
You can also used our sql editor
To run your database on SQL Plus, you can use many different methods. The database can run with or without inserts. What are inserts? The command INSERT INTO inputs the information gathered from the user and store in individual separate rows, as shown in figure 10 and figure 20.
Let start by running our database without input.
Login into your SQL Plus
Go to the Start Window and look for the Oracle – OraDB18Home1, expand the folder and look for the SQL Plus icon.——>
Next, sign in with the default username and the password that you gave to the software when you installed it. The password won’t show as you type. However, the SQL Plus is recording your inputs after you finished typing press enter.
The screen should match figure 62.
You are ready to start.
START and @ Commands
START command or the @ “at” symbol allows you to run your database (script).
To use the @ symbol or @@, you need to place the symbol before your database file.
Note you can type the path to your database, or you can drag and drop your file into the SQL Plus editor. The command @ and the file path are continuous. (NO SPACES) Additionally, if your file does not have the SQL extension will not run.
START Command
- Run the script (database) with the command START.
- First, type the command START, followed by the database path.
- Add a space between the START and the path.
- If your database has no errors, you should have all the tables created
Ok, if you got this far, you did GREAT!
INSERT INTO Command
Now let‘s continued by adding information to the database.
There are a few methods to input information into the database. Let’s look at some of them.
Now let‘s continued by adding information to the database.
There are a few methods out there. It would help if you used what is more comfortable for you. Let’s look at some of them.
Create a new notepad SQL document. If you don’t remember, look at figure 53. Call the file Insert with the extension of SQL.
On your new notepad file, add the inserts for all the tables.
YOU MUST FOLLOW THE SAME ORDER AS YOUR DATABASE. Follow the order of tables when you created them. Otherwise, you are risking violating the table constraints.
Two methods – SQL Syntax
INSERT INTO TABLE_NAME (ColumnName, ColumnName, ColumnName, …)
VALUES (value1, value2, value3, value4, …);
Single quotes encapsulate the values of datatypes, VARCHAR, VARCHAR2, CHAR, and DATE. Though NUMBER datatypes don’t have single quotes.
Second Method
In the second method, you can eliminate the parentheses with the column names.
INSERT INTO TABLE_NAME
VALUES (value1, value2, value3, value4, …);
What is a NULL value?
I hold on to explaining null values because it throws students off. When you have a field, it has no information. The user did not provide input for that field. We say that that field is empty or NULL.
Do not confuse Empty with zero. Zero is a value.
In the case of the CUSTOMER table, you see that the column name Referred is empty, NULL, because no one referred the customer.
Running your first INSERT INTO SQL Plus
After I have run the database on SQL Plus with all the tables empty, I start inserting the first value on the CUSTOMER table.
Running your first INSERT INTO SQL Plus
You can also run all the inserts at the same time. I do know recommended if you are starting with SQL. If you run one insert and you have an error, it is easy to find the mistake. Next, I would un a couple of inserts that has errors.
For example.
Now can imagine, if you need to find a mistake in a thousand inserts?
Another method used by experience dba is to put all your inserts in a file and drag and drop them into SQL Plus.
Finish by adding the requirement of each column name. Does the field require an input? Or the field can be left empty if the user wishes. The example shows that all fields require information except for Referred. What does that mean? When you are filling out the form, you might or might not have someone refer the game to you. So, you can input information, or you might leave it empty.
ALTER TABLE
Let us look at the Alter and Drop command. The database uses ALTER and DROP commands for data manipulation. The ALTER and DROP command allows the user to add, modify, or drop/delete columns in a table.
The ALTER command modifies the table. For example, you can add a Foreign Key constraint to your table by using the ALTER TABLE command.
ALTER TABLE Table_Name
ADD Column_Name datatype;
Foreign Key example
ALTER TABLE table_name
ADD CONSTRAINT constraint_name_ParentTableName
FOREIGN KEY (column1, column2, … column_n)
REFERENCES parent_table (column1, column2, … column_n);
DROP TABLE
Another data definition command is the DROP table statement that provides removal and, deletion from the table.
DROP TABLE TABLE_NAME;
For example, the best practice is to add the drop table on top of your script. Therefore, every time run the script on SQL Plus, tables are dropped “delete,” and the new ones update the database.
Figure 75
In Figure 75, the statement contains three other definitions.
- CASCADE CONSTRAINT, all referential integrity constraints are dropped.
- PURGE cleans the table and its dependent objects. In case they appear in the recycle bin. Purge allows for complete deletion.
UNIT 4: QUERIES TYPES, CLAUSES AND COMPARISON OPERATOR
Now, continuing with SQL, we look at how to retrieve information from the database.
To extract information from a database, we use Queries. What are database Queries?
Queries are a type of language that allows for data retrieval. The Queries use Structure Query Language (SQL).
SQL has commands, Clauses, Joins, Operators, Function, Alias, and more.
We’ll look at the basics of SQL.
SELECT Command
The first commands use to retrieve information from a database is the SELECT Command. The SELECT commands allow the dba to retrieve all or some of the information from the database. Let us look at two methods.
Imagine that you need all the information about a table.
First Method syntax.
SELECT * FROM table_name;
The “all” * symbol retrieves all the information from a table; the FROM clause lists tables to be selected.
The Query ends with a semi-colon.
Almost all the statements used the SELECT command.
The second method, the SQL Query, asks for each column name in the table.
Syntax
SELECT columnName, columnName, ColumnName …
FROM table_name;
Column_Names are separated by commas.
WHERE Clause
The WHERE clause works in conjunction with the SELECT, INSERT, UPDATE, or DELETE statement and is used to filter the results.
For example, imagine that you need information about your customers who live in Florida. You can use the WHERE clause to add a filter to your statement.
Comparison Operator
Comparison Operators allow you to filter the records, select specific information needed, and use the WHERE clause.
| Comparison Operator | Description |
= | Equal
Returns all the information of customers that are living in the state of Florida. |
| < > OR != | Not Equal
Returns all the information of customers that do not live in the state of Florida. |
| >, >= | Greater Than / Greater Than or Equal Returns the values that are Greater Than 1019 Return the values that are Greater Than or equal to 1019 |
| <, <= | Less Than / Less Than or Equal Return values that are Less Than 1003 Return the values that are Less Than or Equal to 1003 |
Questions
- List all customers from the state of ‘CA’.
- Find all games that have a price greater than $50.
- Retrieve all customers who are married.
- Show all promotions that ID is greater than 5
UNIT 5: CONDITIONS AND ALIAS
| Conditions | Description |
| AND | Both Values must equal true to return information SELECT column_name, column_name, column_name … FROM table_name WHERE condition1(true) AND condition2(true) AND condition3(true) …; In this query, the LastName ‘MORALES’ must be true, and the state ‘FL’ also needs to true. |
| OR | One or both values must equal true to return information SELECT column_name, column_name, column_name … FROM table_name WHERE condition1 OR condition2 OR condition3 …; Here return the values for either LastName that equal SMITH or the values in which the State equals NY. |
| AND & OR | The first value must be true so you can validate the second values SELECT column_name, column_name, column_name… FROM CUSTOMER WHERE Column_name=’Value’ AND (Column_name=’Value’ OR Column_name=’Value’); Return only the values in which the first condition is true. ColumnName SMITH equals true |
| IN () | Return multiple values SELECT column_name, column_name, column_name… FROM table_name WHERE column_name IN (value1, value2, …); Returns the values within the list inside the parentheses. |
| BETWEEN | Retrieves values within a specific range, and its values can be numbers, text, or dates. SELECT column_name, column_name, column_name… FROM table_name WHERE column_name BETWEEN value1 AND value2; |
| NOT | Opposes a condition and its use in combination with NOT BETWEEN, NOT IN, IS NOT NULL, NOT LIKE ‘%s’; and NOT EXIST. SELECT column_name, column_name, column_name … FROM table_name WHERE NOT condition; |
| IS NULL, IS NOT NULL | The Null operator checks if the field has information or not. If the field is empty, then it has a value of NULL. If the field values it’s not empty has information, then the field is not null. SELECT column_name, Column_name, Column_name… FROM table_name WHERE column_name IS NULL; SELECT column_name, Column_name, Column_name… FROM table_name WHERE column_name IS NOT NULL; |
| LIKE | The LIKE condition allows for pattern matching queries. It searches the database for matching patterns to the query. Its use in combination with Wildcard % and _. % matches a string of any length _ matches one single character SELECT column_name, Column_name, Column_name… FROM table_name WHERE column_name LIKE pattern ‘%String’; or NUMBER or DATE · LastName LIKE ‘a%’ Finds any values that start with “a.” · LastName LIKE ‘%a’ Finds any values that end with “a.” · LastName LIKE ‘%or%’ Finds any values that have “or” in any position · LastName LIKE ‘_r%’ Finds any values that have “r” in the second position · LastName LIKE ‘a__%’ Finds any values that start with “a” and are at least three characters in length · LastName LIKE ‘a%o’ Finds any values that start with “a” and ends with “o.” |
ALIAS
Alias are temporary names created to allow end-users to read the information more naturally.
The “AS” keyword lets us point to the new temporary name encapsulated in double-quotes.
SELECT FN AS “First Name”, LN AS “LastName “
FROM DEVELOPER;
SELECT Column_Name AS ” New Column_Name”
FROM TABLE;
Unit 5 - Questions Conditions
- Find customers from the states ‘CA’, ‘FL’, or ‘NY’.
- Retrieve games with prices between $20 and $50.
- List all customers who are either ‘Single’ or ‘Divorced’.
- Show all promotions that have ID 4, 5, 6.
- Find all customers who do not have an email address.
- Retrieve all games with prices not between $30 and $60.
- List all customers with the last name starting with ‘S’.
- Show customers who are married and live in the ‘WEST’ region.
- Find all customers who are either ‘Married’ and live in ‘SOUTHEAST’ or are ‘Single’ and live in ‘WEST’.
- Retrieve all games with titles that contain the word ‘Adventure’.
- Find customers who have a referral.
- Retrieve the customer IDs and their corresponding email addresses using aliases for both columns.
- List the game titles and their prices, using aliases for both columns.
UNIT 6: CLAUSES
DISTINCT
The Distinct clause allows the query to eliminate duplicate data from the result.
SELECT DISTINCT Column_Name
FROM Table
WHERE Column_Name = ‘Value’;
With the DISTINCT Clause you eliminate duplicates | Without the DISTINCT Clause |
In this case, the GAME_ORDER table has two orders with customerID equal to1007 that lives in the city of Austin. Yet, it returns only one result to the query.
ORDER BY
The ORDER BY clause returns data sorted and organized. The ORDER BY clause is exclusive to SELECT statements.
In this case, we want to return the GameID, Title, and the category of all the GameID that are greater or equal to gameID 5010. Additionally, we’ll like to have the output order by category in ascending order. If you look at the output, the category is in ascending order, but the id is not.
UNIT 7: FUNCTIONS AND SPECIAL CLAUSES
The GROUP BY, HAVING clauses work in combination with Aggregate Functions.
What is a function?
A function is a program that performs tasks. Programing language and data manipulation language(DML) uses these little programs that execute these tasks.
We’ll look at Aggregate functions.
We already define that function performs tasks. What kind of tasks does an aggregate function perform?
So, the aggregate function works by combining grouping, adding to output an individual result. We’ll look at the COUNT, SUM, MIN, MAX, and AVG Function.
COUNT Function
The COUNT Function counts the values.
SELECT COUNT(aggregate_expression)
FROM tables
[WHERE conditions];
SELECT expression1, expression2, … expression_n,
COUNT(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression;
Also, you can add the asterisk (*) wildcard to select all.
SUM Function:
The SUM function returns the added values of an expression.
SELECT SUM(aggregate_expression)
FROM tables
[WHERE conditions];
SELECT expression1, expression2, … expression_n,
SUM(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, … expression_n;
MIN Function
The MIN function outputs the lowest value of the expression.
SELECT MIN(aggregate_expression)
FROM tables
[WHERE conditions];
SELECT expression1, expression2, … expression_n,
MIN(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, … expression_n;
MAX Function
The MAX function outputs the lowest value of the expression.
SELECT MAX(aggregate_expression)
FROM tables
[WHERE conditions];
SELECT expression1, expression2, … expression_n,
MAX(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, … expression_n;
AVG Function
The AVG function returns the average output of an expression.
SELECT AVG (aggregate_expression)
FROM tables
[WHERE conditions];
SELECT expression1, expression2, … expression_n,
AVG (aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, … expression_n;
GROUP BY:
The GROUP BY clause allows gathering data from various records and output the result in groups of one or more columns.
SELECT expression1, expression_n, aggregate_function(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, … expression_n;
HAVING
The HAVING clause acts very similarly to the GROUP BY because the HAVING clause limits the output to groups where the condition matches TRUE.
UNIT 8: JOINS
What are JOINS?
So, what happens is you need to retrieve data from more than one table? Well, JOINS allow you to do just that.
We’ll look at the four types of JOINS. (INNER JOIN, LEFT JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN)
INNER JOIN
You must join the tables thought the Key connecting the tables. The inner join returns values that match both tables.
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
LEFT OUTER JOIN
The Left outer join retrieves information “Data” from the table on the left, Table_1, and also returns the matching records from the table on the right Table_2.
How do you know which table is left or right?
The table on the right is always the table to join. The left table is the one after the FROM.
SELECT column_name, Column_Name, Column_Name
FROM table1 (Left Table)
LEFT JOIN table2 (Right Table)
ON table1.column_nameID = table2.column_nameID;
RIGHT OUTER JOIN
The RIGHT JOIN retrieves all Information “Data” from the table on the Right, Table_2, and also returns the matching records from the table on the left Table_1.
SELECT column_name, Column_Name, Column_Name…
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
UNIT 9: VIEWS AND INDEXES
The view in Oracle creates a query that contains no data itself. It’s a temporary table that allows the DB user to save a query that used very often. (in most cases)
To create a view use the CREATE statement
CREATE VIEW view_name AS
SELECT columns
FROM tables
[WHERE conditions];
Retrieving the VIEW
Syntax
SELECT * FROM View_Name;
Update VIEW
To change or modify our VIEW without the necessity of dropping the View, you can replace the view.
CREATE OR REPLACE VIEW view_name AS
SELECT columns
FROM table
WHERE conditions;
We are changing the view to include the City of Miami and the State of Florida.
Solutions
UNIT 4 – Answers(comparisons operators)
- SELECT * FROM CUSTOMER
WHERE State = ‘CA’; - SELECT * FROM GAME
WHERE Price > 50; - SELECT * FROM CUSTOMER
WHERE MaritalStatus = ‘Married’; - SELECT * FROM PROMOTION
WHERE PromotionID > 5;
UNIT 5 – Answers(conditions & Alias)
- SELECT * FROM CUSTOMER
WHERE State IN (‘CA’, ‘FL’, ‘NY’); - SELECT * FROM GAME
WHERE Price BETWEEN 20 AND 50; - SELECT * FROM CUSTOMER
WHERE MaritalStatus = ‘Single’ OR MaritalStatus = ‘Divorced’; - SELECT * FROM PROMOTION
WHERE DurationDays IN (10, 20); - SELECT * FROM CUSTOMER
WHERE email IS NULL; - SELECT * FROM GAME
WHERE Price NOT BETWEEN 30 AND 60; - SELECT * FROM CUSTOMER
WHERE LastName LIKE ‘S%’; - SELECT * FROM CUSTOMER
WHERE MaritalStatus = ‘Married’ AND Region = ‘WEST’; - SELECT * FROM CUSTOMER
WHERE (MaritalStatus = ‘Married’ AND Region = ‘SOUTHEAST’) OR (MaritalStatus = ‘Single’ AND Region = ‘WEST’); - SELECT * FROM GAME
WHERE Title LIKE ‘%Adventure%’; - SELECT * FROM CUSTOMER
WHERE Referred IS NOT NULL; - SELECT FN AS “First Name”N, LN AS “Last Name”
FROM DEVELOPER; - SELECT CustomerID AS ID, email AS EmailAddress
FROM CUSTOMER; - SELECT Title AS GameTitle, Price AS GamePrice
FROM GAME;
Check your Knowledge Press the button to take a Quiz
Conclusion
Thank you for your participation in this course Introduction to Database. Be advised, if you want to learn more about this course I’d suggest reading the books available below :
Prof. Gonzalez At New York City College of Technologu and Founder of Lecturus.org
Lecturus
Lecturus is a platform that offers training to individuals interested in developing or enhancing their computer skills, as well as a career change or advancement.
Get In Touch
147 Prince St, Brooklyn, NY 11201
- Email: lecturus@outlook.com
- Phone: 929-280-7710
- Hours: Mon-Fri 9 AM - 5 PM
Lecturus
Lecturus is a platform that offers training to individuals interested in developing or enhancing their computer skills, as well as a career change or advancement.
Company
Get In Touch
147 Prince St, Brooklyn, NY 11201
- Email: lecturus@outlook.com
- Phone: 929-280-7710
- Hours: Mon-Fri 9 AM - 5 PM