Tables

Create table

When creating a table, we specify the fields that the table should hold, the type of data stored by these fields, and the properties of the table:

 

During creation, we can override all constrain  such as primary key or foreign key. We can also set a default value if no value is entered for a given field when the table is filled. This is done using the DEFAULT clause.

 

Create a table based on a query

We can also create a table based on the query. The resulting table will contain all the data that will be returned to us as a result of the query. If we want to create only a structure, we can add a condition after the WHERE clause that can never happen.

 

Deleting a table

To delete a table we use the drop:

 

 

Rename table

To change the name of the table we use  RENAME :

 

Add columns

To add columns to the table we use  ALTER TABLE. We can use all the parameters we use when creating a table (parameters for a column).

 

 

Deleting columns

We use DROP COLUMN:

 

 

Change the properties of the columns

To change the column type use the MODIFY command. You can change the column type if the data contained in this column can be converted to a new type. For example, we can change the length of a column, we can change the number type to a text type, but we can not change the text type to numbers if the data contained in the numeric column is not numerical.

 

 

Integrity bonds

Due to integrity constraints, data cannot be modified so that it is consistent. They are a set of rules imposed on the tables in the database.

Primary Key

Provides unique value in the column. It usually assumes a column that holds data uniquely identifying a single line. Only one primary key can be present in the table. Provides no NULL value in the column to which it is superimposed.

Unique

Provides unique values in a column, but unlike the PRIMARY KEY, such keys can be more than one, and allow NULL values.

NOT NULL

Prevents inserting NULL values into a column.

Check

Ensures that the value inserted in the column satisfies the required logical condition. You can not use references to other tables, aggregate functions, SYSDATE.

Foreign key

This is a foreign key. Defines relationships between tables. Ensures that the record in the child table will always have its counterpart in the parent table. The foreign key must refer to the column (s) in the parent table where UNIQUE  is installed or the primary key.

Creating Constraints

There are two possible ways of establishing ties. Create a table and apply it to existing one

tables. Here’s an example of how to create constraints when creating a table:

 

 

Example of applying constrainings to an existing table:

 

 

An exception to the rule is NOT NULL. Set the NULL / NOT NULL property by changing the state:

 

Enabling and disabling constraints

Constraints can be turned on or off. This ability becomes useful when we want to perform an action that would prevent us from establishing a constraint. For example, we remove the FOREIGN KEY from the table if we supplement it with data that does not have its counterpart in the parent table.

 

 

We can enable constraint after scheduled tasks, provided that the condition is fulfilled (eg we will complete the entries in the parent table for the FOREIGN KEY). We can also enable constraint without validating existing data. The constraint will only apply to data entered later:

 

 

Removing Constraints

To remove from the constraint table we have to use the drop command giving the key name.

 

 

Views

If we often make a query to the database, such as using multiple tables or just long, we can define a view. Using it will be much more comfortable. We can retrieve data from it as from a regular table. Suddenly a long query with many parameters to which we often have to add something we can turn into something like this:

SELECT * FROM VIEW NAME

Views are completely dynamic structures, ie the query that defines them at the time of definition is only validated for syntactic and semantic accuracy, but not executed. This query is executed when referring to the view.

Views also apply in the case of delegation. We may want to share only part of the data or some of the columns in the table. In this case, we create a view of the given properties, and the user allows access to the view and not the table.

Create views

CREATE [OR REPLACE] [FORCE] VIEW AS_VIDEO NAME

REQUEST [WITH READ ONLY] [WITH CHECK OPTION]

To create a view, you must have permissions for all objects to which the view refers.

OR REPLACE

Thanks to this clause if a view with that name already exists, it will be overwritten by the currently created.

FORCE

Forces the creation of a view even if the underlying query is invalid (eg, refers to a table that does not already exist).

WITH READ ONLY

In some cases, you can use DML statements (such as INSERT) in views, this statement even if formally possible, this prevents it.

WITH CHECK OPTION

This option makes it impossible for you to upload data or change it so that it will not be visible in this view (if possible for that view).

Performing DML operations on views

For queries, the views function as tables. Depending on the DML operation, the views apply to different properties. In none of the following cases, we can not perform an operation if the view was created with the WITH READ ONLY clause.

Update

• The view can not be based on multiple tables

• Must not contain a DISTINCT clause

• Can not contain GROUP BY or group functions

• Must not contain correlated queries

• Expressions in the column

Delete

• The view can not be based on multiple tables

• Must not contain a DISTINCT clause

• Can not contain GROUP BY or group functions

• Must not contain correlated queries

Insert

• The view can not be based on multiple tables

 

• Must not contain a DISTINCT clause

• Can not contain GROUP BY or group functions

• Must not contain correlated queries

• Expressions in the column

• The table on which the view is based has a NOT NULL column without a default value, and the column is not mapped to the view.

• If a view is created with the WITH CHECK OPTION clause, no line can be inserted into the view that would not be visible in that view.

Deleting views

DROP VIEW VIEW NAME;

sequences

Sequence creation

A sequence is an object that gives subsequent values according to set criteria. We often use sequences to create key tables. Why do we need to manually enter the next number after which line we will identify? It is easy to confuse and insert a number that already exists, and besides, this involves a rather cumbersome check of the last number. Let’s do it for us!

 

 

Based on the above example:

create sequence

After that, the name of the sequence is given. The sequence name cannot be a space.

MINVALUE

The Minimal value that can take a sequence.

maxvalue

The maximum value that a sequence can take.

Start with

Specifies the value from which to start the sequence.

Increment by

Specifies the value to change the current state of the sequence after each data retrieval.

As a parameter of this command, we can also specify a negative number. The sequence value will then decrease.

Cycle / nocycle

This parameter determines whether the sequence can be twisted (like the mileage in the car) and start calculating from the beginning.

Cache / nocache

The CACHE clause involves performing pre-allocations of sequence numbers and storing them in memory, which results in an increase in the rate of generation of consecutive numbers. NOCACHE excludes this option. CACHE 20 is accepted by default. The value given in CACHE must be less than MAXVALUE – MINVALUE.

Order / noorder

The ORDER clause guarantees that subsequent numbers will be generated in the order they were received by the command system of their generation. NOORDER will exclude this property.

Retrieve values from a sequence

Two sequences of currval and nextval are used to retrieve data from the sequence.

nextval

Used to retrieve the next value from the sequence.

currval

Used to retrieve the current sequence value. Use currval at least once per nextval. In the absence of this initiation, both currval and nextval will have the same value corresponding to the start with parameter for the sequence. There are two ways to get values from a sequence. Use this select in the table (eg dual) or in the DML statements. About this second method we will speak a little later. Here are examples of how to get currval and nextval values and the differences between them.

 

 

 

Use of sequences in DML statements

When adding data to a table, it is often necessary to generate a unique id for each row for each entry. This is required because of the assumption on the primary key table. In this situation, it is best to use the sequence. Sequences can only produce numerical values.

 

Deleting sequences

To delete a created sequence, use the drop command with the following structure:

DROP SEQUENCE name of the sequence;

as follows:

 

 

Sequence modification

To change or assign a parameter to an existing sequence, we use the construct:

ALTER SEQUENCE NAME_SEQUENCE PARAMETER NAME {VALUE};

We can not just change the START WITH parameter, as you see with examples of the proper use of ALTER SEQUENCE in the image below:

 

Indices

Indexes allow you to speed up the searching of rows in tables.

In the absence of an index, finding a row that fulfills a condition would require looking at the row after the row of the entire table. For large and very large tables, such a search could take a very long time. Due to the tree structure of the index, you can quickly find the position of the searched lines. This makes search always short.

Create an index

Generic index command construction:

CREATE INDEX NAME_INFORMATION ON NAME (COLUMNS);

Indexes can be set in more than one column. Just add them to the decimal point.

Indexes should be set on those columns that often occur in queries as a condition in the row filtration or the column we are grouping. Indexes speed up data retrieval but slow down their addition/change. This is because new information must be added not only to the table but also to the index.

Deleting an index

DROP INDEX NAME_INDATE;

Database links

Database links are references to another database, which may even reside on a remote (physical) server. They simplify work on multiple databases because we can use other databases in a similar way to other users’ schemas. Here’s how to create a database link.

CREATE DATABASE LINK NAME

CONNECT IS THE NAME OF THE USER IDENTIFIED BY PASSWORD

USING ALIAS_BAZY

Application examples: &

 

 

As you can see we have created a database link referring to the database specified in the tnsnames.ora file as XE. This file contains detailed information about the car connection. With the entry in this file, the system knows what database to reference.

We can also specify the IP address of another Oracle server, as shown in the following illustration:

 

 

The remote Oracle server based on authorization data redirects us to the correct schema. From the moment we create the database link, we can use the objects in the remote database as in the local database, we only need to specify “@index_link” after the object name in the database.

exercises

1.  Create a primary key table, one column with unique values, and one to which you always have

You will need to enter data

2.  Create a view that will display the employees (employees table) of the department that you specified

name (tab. departments)

3.  Create a view that will allow you to add rows to the regions table

4.  Create a database link to your friend’s hr schema. Check if there are any differences between

You and its employees table

5. Create a “City” table containing: id, city name, foreign key to the regions table

6. Insert the master key into the “City” table id field.

7. Create a unique key on the field with the name of the city.

8. Add to the table “Cities” field “district”

9. Insert the index into the “district”

10.Create a sequence that starts with 1, with a maximum value of 9999 progressing by 1

11. Create a sequence that starts from 9999, with a minimum value of 1 progressing by 4.

12.Check the value of the sequence without changing its value.