DB Desk

There is a relational operator that is used to perform string-only comparisons, "like" and "not like". We have made queries using relational operators to compare strings.  Examples: we know how to retrieve books whose author is equal to the string "Borges":  select * from books   where author = 'Borges'; The equal operator ("=") allows us to compare strings of characters, but when performing the comparison, it looks for matches of complete strings, it performs an exact search. Let's imagine that we have these 2 books registered:  "El Aleph", "Borges";  "Poetic Anthology", "JL Borges"; If we want to recover all the books of "Borges" and we specify the following condition:  select * from books  where author = 'Borges'; Only the first record will appear, since the string "Borges" is not the same as the string "JL Borges". This happens because the operator "

"In" is used to find out if the value of a field is included in a specified list of values. In the following statement we use "in" to find out if the value of the author field is included in the specified list of values ​​(in this case, 2 strings). Until now, to recover books whose author is 'Paenza' or 'Borges' we used 2 conditions:  select * from books   where author = 'Borges' or author = 'Paenza'; We can use "in" and simplify the query:  select * from books   where author in ('Borges', 'Paenza'); To recover books whose author is not 'Paenza' or 'Borges' we used:  select * from books   where author <> 'Borges' and   author <> 'Paenza'; We can also use "in" by prepending "not":  select * from books   where author not in ('Borges', 'Paenza'); Using "in" we find out if the value of the field is included in the specified

We have seen the relational operators: = (equal), <> (different),> (greater), <(less),> = (greater or equal), <= (less than or equal), is null / is not null ( whether a value is NULL or not). Another relational operator is "between", they work with intervals of values. Until now, to retrieve from the "books" table the books with a price greater than or equal to 20 and less than or equal to 40, we used 2 conditions joined by the logical operator "and":  select * from books   where price> = 20 and   price <= 40; We can use "between" and thus simplify the query:  select * from books   where price between 20 and 40; We find out if the value of a given field (price) is between the specified minimum and maximum values ​​(20 and 40 respectively). "between" means "between". Work with range of values. This operator does not take "null" values ​​into account. If we add the "not" operator befor

 So far, we have learned how to set a "where" condition using relational operators. We can establish more than one condition with the "where" clause, for this we will learn the logical operators. They are as follows: and, means "and", or, means "and / or", not, means "no", invert the result (), parentheses Logical operators are used to combine conditions. If we want to recover all the books whose author is equal to "Borges" and whose price does not exceed 20 pesos, we need 2 conditions:  select * from books   where (author = 'Borges') and   (price <= 20); The records retrieved in a statement that joins two conditions with the "and" operator, meet the 2 conditions. We want to see the books whose author is "Borges" and / or whose publisher is "Planeta":  select * from books   where author = 'Borges' or   editorial = 'Planet'; In the previous statement we use the "or&q

We can order the result of a "select" so that the records are shown ordered by some field, for this we use the "order by" clause. The basic syntax is as follows:  select * from TABLENAME   order by FIELD; For example , we retrieve the records from the "books" table ordered by title: select * from books  order by title; The records are listed alphabetically by the specified field. We can also place the order number of the field by which we want it to be ordered instead of its name, that is, reference the fields by their position in the selection list. For example, we want the result of the "select" ordered by "price":  select title, author, price   from books order by 3; If we put a number greater than the number of fields in the selection list, an error message appears and the sentence is not executed. By default, if we do not clarify in the sentence, it orders them in ascending order (from lowest to highest). We can order them from hig

Oracle has several functions that operate on "date" data types. These are some: - add_months (f, n): adds a number of months to a date. If the second argument is positive, this number of months is added to the sent date; if it is negative, such number of months is subtracted from the sent date. Example: select add_months ('10 / 06/2020 ', 5) from dual; --returns "11/10/20" select add_months ('10 / 06/2020 ', - 5) from dual; --returns "01/10/20" select add_months ('30 / 01/2020 ', 1) from dual; - returns "02/29/20" since it is the last day of that month. - last_day (f): returns the last day of the month of the date sent as an argument.  Example:  select last_day ('10 / 02/2020 ') from dual; - "02/29/20"  select last_day ('10 / 08/2020 ') from dual; - "08/31/20" - months_between (f1, f2): returns the number of months between the dates sent as an argument.  Example: select months_between

 Mathematical functions perform operations with numerical expressions and return a result, they operate with numeric data types. Numeric functions accept numeric input parameters and return numeric values. Oracle has some functions for working with numbers. Here are some of them. - abs (x): returns the absolute value of the argument "x". Example:  select abs (-20) from dual; - returns 20. The dual table is a virtual table that exists in all Oracle databases. - ceil (x): rounds the argument "x" up to an integer. Example:  select ceil (12.34) from dual; - returns 13. - floor (x): rounds the argument "x" down to integer. select floor (12.34) from dual; --12 - mod (x, y): returns the remainder of the x / y division. Example:  select mod (10,3) from dual; - returns 1.  select mod (10,2) from dual; - returns 0. - power (x, y): returns the value of "x" raised to the "y" power.  Example:  select power (2,3) from dual; - returns 8. - round

The alphanumeric character handling functions accept arguments of type character and return characters or numeric values. The following are some of the functions that Oracle offers to work with character strings: - chr (x): returns a character equivalent to the code sent as argument "x". Example:  select chr (65) from dual; - returns 'A'.  select chr (100) from dual; - returns 'd'. Annotations The DUAL table is a special single column table present by default in all Oracle database installations. It is used when we want to make a statement that does not need to consult tables. The table has a single column VARCHAR2 (1) called DUMMY that has a value of 'X' - concat (string1, string2): concatenate two character strings; is equivalent to the || operator. Example:  select concat ('Good afternoon', 'afternoon') from dual; - returns 'Good afternoon'. - initcap (string): returns the string sent as an argument with the first letter (

One way to make the result of a query more understandable is to change the column headings. For example, we have the table "books" with a field "quantity" (among others) in which the quantity of books in stock is stored; We want the text "stock" to appear as the header of the "quantity" field when displaying the information of said table, for this we place an alias as follows:  select title, quantity as stock,  price   from books; To replace the name of one header field with another, place the keyword "as" followed by the header text. If the alias consists of a single string, the quotes are not necessary, but if it contains more than one word, it is necessary to place it in double quotes:  select title,   quantity as "available stock",   price   from books; You can also create an alias for calculated columns. For example:  select title, price,   price * 0.1 as discount,   price- (price * 0.1) as "final price"   from

 We learned that operators are symbols that allow you to perform different types of operations. We said that Oracle has 4 types of operators :  relational or comparison (we saw them), arithmetic,  concatenation and  logical. Arithmetic operators allow you to perform calculations with numeric values. They are: multiplication (*), division (/), addition (+) and subtraction (-). It is possible to obtain outputs in which a column is the result of a calculation and not a field of a table. If we want to see the titles, price and quantity of each book we write the following sentence:  select title, price, quantity   from books; If we want to know the total amount in money of a title we can multiply the price by the amount for each title, but we can also have Oracle perform the calculation and include it in an extra column in the output:  select title, price, quantity,   price * quantity   from books; If we want to know the price of each book with a 10% discount, we can include the following c

We have seen that if when inserting records a value is not specified for a field that admits null values, "null" is automatically entered. This value is called the default or default value. A default value is inserted when it is not present when entering a record. For fields of any type not declared "not null", that is, that admit null values, the default value is "null". For fields declared "not null", there is no default value, unless explicitly declared with the "default" clause. We can set default values ​​for the fields when we create the table. For this we use "default" when defining the field. For example, we want the default value of the "author" field of the "books" table to be "Unknown" and the default value of the "quantity" field to be "0":  create table books (   title varchar2 (40) not null,   author varchar2 (30) default 'Unknown' not null,    editorial v

We already explained that when creating a table we must choose the appropriate structure, that is, define the fields and their most precise types, as appropriate. Numeric values ​​are not enclosed in quotes. The point is used as a decimal point. To store NUMERIC values ​​Oracle has two types of data: #1 . number (t, d): to store integer or decimal values, positive or negative. Its range is from 1.0 x 10-130 to 9,999 ... (38 nines). We define fields of this type when we want to store numerical values ​​with which we will then perform mathematical operations, for example, quantities, prices, etc. The parameter "t" indicates the total number of digits (counting the decimals) that the maximum number will contain (it is the precision). Its range is from 1 to 38. The parameter "d" indicates the maximum number of decimal digits (scale). The scale can go from -84 to 127. To define integers, you can omit the "d" parameter or put a 0. A defined field "number (5

We already explained that when creating a table we must choose the appropriate structure, that is, define the fields and their most precise types, as appropriate. To store alphanumeric values ​​(text) we use character strings. Strings are enclosed in single quotes. We can store letters, symbols and digits with which mathematical operations are not performed, for example, identification codes, document numbers, telephone numbers. We have the following types: #1 .  char (x): defines a fixed-length character string determined by the "x" argument. If the argument is omitted, it defaults to 1. "char" stands for character, which means character in English. Its range is from 1 to 2000 characters. That it is a fixed length string means that, if we define a field as "char (10)" and store the value "hello" (4 characters), Oracle will fill the remaining 6 positions with spaces, that is, it will occupy the 10 positions; therefore, if the length is invariable

We learned that to delete all records from a table , use "delete" without a "where" condition. Truncate Table in SQL: We can also remove all the records from a table with "truncate table". Syntax:  truncate table TABLENAME; For example, we want to empty the table "books", we use: truncate table books; The "truncate table" statement empties the table (removes all records) and preserves the structure of the table. The difference with "drop table" is that this statement drops the table, not only the records, "truncate table" empties it of records. The difference with "delete" is the following, when using "delete", Oracle saves a copy of the deleted records and they are recoverable, with "truncate table" recovery is not possible because all the disk space occupied by the table is freed ; therefore "truncate table" is faster than "delete" (noticeable when the number of rec

A primary key is a field (or more) that identifies a single record (row) in a table. For a key field value there is only one record. Let's see an example, if we have a table with people data, the document number can be set as a primary key, it is a non-repeating value; There may be people with the same surname and first name, even the same address (father and son for example), but their document will always be different. If we have the table "users", the name of each user can be established as a primary key, it is a value that is not repeated; There may be users with the same password, but their username will always be different. We can set a field to be the primary key when the table is created or after it has been created. We are going to learn how to set it when creating the table. There is no single way to do it, for now we will see the simplest syntax. We have our table "users" defined with 2 fields ("name" and "password"). The basic and

To retrieve the records that contain the value "null" in some field, we cannot use the relational operators seen above: = (equal) and <> (different); we must use the operators "is null" (it is equal to null) and "is not null" (it is not null). With the following sentence we recover the books that contain null value in the "publisher" field:  select * from books   where editorial is null; Remember that null values ​​are not displayed, the field appears empty. The following statements will have a different output:  select * from books where publisher is null;  select * from books where publisher = ''; With the first sentence we will see the books whose publisher stores the value "null" (unknown); with the second, the books whose publisher keeps a string of 3 blank spaces. To get the records that do not contain "null", you can use "is not null", this will show the records with known values. To see the book

"null 'means" unknown data "or" non-existent value ". Sometimes, the data corresponding to a field of a record may be unknown or not exist. In these cases we say that the field can contain null values. For example, in our table of books, we can have null values ​​in the "price" field because it is possible that for some books we have not set the price for sale. In contrast, we have fields that can never be empty. Let's look at an example. We have our table "books". The "title" field should never be empty, likewise the "author" field. To do this, when creating the table, we must specify that such fields do not allow null values:  create table books (   title varchar2 (30) not null,   author varchar2 (20) not null,   editorial varchar2 (15) null,   price number (5.2)   ); To specify that a field does NOT allow null values, we must put "not null" after the field definition. In the above example, the "p

To clarify some instructions, we sometimes need to add comments. It is possible to enter comments on the command line, that is, a text that is not executed; For this, two hyphens (-) are used:  select * from books; - show the book records  in the previous line, everything after the hyphens (to the right) is not executed. To add multiple lines of comments, a slash followed by an asterisk (/ *) is placed at the beginning of the comment block and at the end of it, an asterisk followed by a slash (* /)  select title, author   / * we show titles and  authors' names * /  from books; everything between the "/ *" and "* /" symbols is not executed.

We say that we update a record when we modify any of its values. To modify one or more data in one or more records we use "update". Basic syntax of update records in SQL:  update TABLE NAME set FIELD = NEWVALUE; We use "update" together with the name of the table and "set" together with the field to modify and its new value. The change will affect all records. For example , in our "users" table, we want to change the values ​​of all keys, to "RealMadrid": update users set key = 'RealMadrid'; We can modify some records, for this we must establish selection conditions with "where". For example, we want to change the value corresponding to the key of our user called "Federicolopez", we want as a new key "Boca", we need a condition "where" that affects only this record:  update users set password = 'Mouth'   where name = 'Federicolopez'; If Oracle does not find records that mee

To delete the records from a table we use the "delete" command. Basic syntax of delete in SQL:  delete from TABLENAME; The delete command is placed followed by the keyword "from" and the name of the table from which we want to delete the records. The following example removes the records from the "users" table: delete from users; Then a message indicates the number of records that have been deleted. If we do not want to delete all the records, but only some, we must indicate which or which ones; For this we use the "delete" command together with the "where" clause with which we establish the condition that the records to be deleted must meet. For example, we want to delete that record whose username is "Marcelo":  delete from users  where name = 'Marcelo'; If we request the deletion of a record that does not exist, that is, no record meets the specified condition, a message will appear indicating that no record was rem

Operators are symbols that allow you to perform mathematical operations, concatenate strings, make comparisons. Types of Relational Operators - Oracle SQL Oracle database recognizes 4 types of relational operators. Relational (or comparison) Arithmetic Concatenation Logical For now we will only see the first ones. Comparison in SQL Relational (or comparison) operators allow us to compare two expressions, which can be variables, field values, etc. We have learned to specify equal conditions to select records from a table; for example:  select * from books   where author = 'Borges'; We use the relational equality operator. Relational operators bind a field to a value so that Oracle compares each record (the specified field) to the given value. The relational operators are as follows: = equal <> different > major <minor > = greater than or equal <= less than or equal We can select the records whose author is different from "Borges", for this we use the

 We have learned how to select some fields from a table. It is also possible to retrieve some records. There is a clause, "where" with which we can specify conditions for a "select" query. That is, we can retrieve some records, only those that meet certain conditions indicated with the "where" clause. For example, we want to see the user whose name is "Marcelo", for this we use "where" and after it, the condition:  select name, key   from users   where name = 'Marcelo'; The basic and general syntax is as follows:  select FIELDNAME1, ..., FIELDNAMEn   from TABLE NAME   where CONDITION; Relational operators are used for conditions (a topic that we will deal with later in detail). The equal sign (=) is a relational operator. For the following selection of records we specify a condition that requests users whose password is equal to "River":  select name, key   from users   where key = 'River'; If no record meets t

 We have learned how to view all the records in a table, using the "select" statement. The basic and general syntax is as follows:  select * from TABLENAME; The asterisk (*) indicates that all the fields in the table are selected. We can specify the name of the fields that we want to see, separating them by commas:  select title, author from books;  The list of fields after the "select" selects the data corresponding to the named fields. In the previous example we select the "title" and "author" fields from the "books" table, showing all the records.

We already explained that when creating a table we must determine what fields (columns) it will have and what type of data each of them will store, that is, their structure. The data type specifies the type of information that a field can store: characters, numbers, etc. These are some basic Oracle data types (we will see others later and in more detail): varchar2: Used to store character strings. A string is a sequence of characters. It is enclosed in single quotes; example: 'Hello', 'Juan Perez', 'Colon 123'. This type of data defines a variable length string in which we determine the maximum number of characters between parentheses. You can save up to xxx characters. For example, to store strings of up to 30 characters, we define a field of type varchar2 (30), that is, in parentheses, next to the name of the field we put the length. If we try to store a character string longer than the one defined, the string is not loaded, a message appears indicating this s

There are several database objects: tables, constraints, views, sequences, indexes, clusters, triggers, snapshots, procedures, functions, packages, synonyms, users, profiles, privileges, roles, etc. Tables in SQL - Oracle A database stores its information in tables, which is the basic unit of storage. A table is a data structure that organizes data in columns and rows; each column is a field (or attribute) and each row, a record. The intersection of a column with a row contains a specific data, a single value. Each record contains one data for each column in the table. Each field (column) must have a name. The field name refers to the information it will store. Each field (column) must also define the type of data it will store. Tables are part of a database. We will work with the database already created. To see the existing tables we type:  select * from all_tables; A table appears that shows us in each row, the data of a specific table; in the column "TABLE_NAME" appears t

For this course we will use the Oracle Database Express Edition (XE) version. To get the Oracle Database version 18c Express edition you need to visit the Oracle website: Oracle XE We select the version " Oracle Database 18c Express Edition for Windows x64 " To allow it to be downloaded from the site we select with the mouse the " Accept License Agreement " Radio control. The Oracle site requires us to register. We must select " sign up now " and then " Create your Oracle account now ", that is, create an Oracle account. After we have registered we can download the Oracle database engine . The next step is to install the database manager itself. We execute the file that we have just downloaded: ' setup.exe '.   We must press the "next" button in the installation wizard, except when it asks us to enter the password for the database, it is important not to forget this password:   After a few minutes we already have the Oracle data