Type of data in SQL

-

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 situation and the statement is not executed.

For example, if we define a field of type varchar (10) and try to store the string 'Good afternoon' in it, a message appears indicating that the value is too large for the column.


Number (p, s): 

Used to store numerical values ​​with decimals, from 1.0 x10-120 to 9.9 ... (38 places). 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.

It can contain whole numbers or decimals, positive or negative. The parameter "p" indicates the precision, that is, the number of digits in total (counting the decimals) that the number will contain at most. The "s" parameter specifies the scale, that is, the maximum number of decimal digits. For example, a defined field "number (5,2)" can contain any number between 0.00 and 999.99 (positive or negative).

To specify integers, we can omit the "s" parameter or put the value 0 as the "s" parameter. The period (.) Is used as a separator.

If we try to store a larger value outside the allowed range when defining it, such value is not loaded, a message appears indicating such situation and the sentence is not executed.

For example, if we define a field of type number (4,2) and try to save the value 123.45, a message appears indicating that the value is too large for the column. If we enter a value with more decimal places than defined, the value is loaded but with the number of decimal places allowed, the remaining digits are ignored.

Before creating a table, we must think about its fields and choose the appropriate data type for each of them.

For example, if in a field we will store telephone numbers or a document number, we use "varchar2", not "number" because although they are digits, we do not perform mathematical operations with them. If we will save surnames in a field, and we assume that no surname will exceed 20 characters, we define the field "varchar2 (20)". If in a field we will store prices with two decimal places that will not exceed 999.99 pesos, we define a field of type "number (5,2)", that is, 5 digits in total, with 2 decimal places. If in a field we will store integer values ​​of no more than 3 digits, we define a field of type "number (3,0)".

Comments

Post a Comment

Popular posts from this blog

Data Consistency and Inconsistency - What is Difference

-
Image
  Data Consistency: Definition 1: Data consistency means that the changes made to the different occurrences of data should be controlled and managed in such a way that all the occurrences have the same value for any specific data item. Data inconsistency leads to a number of problems, including loss of information and incorrect results. In the database approach, it is controlled because data is shared and consistency is controlled and maintained. Data Consistency Definition 2: Data consistency meaning is the validity, accuracy and usability of related data. It ensures that each user observes a consistent(Same) view of the data, including changes made by the user’s own transactions and transactions of other users. Types of Data Consistency: Point in time consistency Transaction consistency Application consistency Data Inconsistncy: Data Inconsistency Definition: Data inconsistency meaning is that different versions of the same data appear in d...
Read more

Cardinality in DBMS – max/minimum cardinality & examples

-
Image
  Logical Relationship in Tables: Database tables have a major advantage to bridge them with some logical relationship so to filter the records. These relationship allowed us to access one table data using another table records(via columns or rows). In other words logical filtration is called cardinality DBMS. Read Also: Relational Model in DBMS Definitions of Cardinality in DBMS: Definition 1: A number that represents, one instance of first entity(table) is related to how many instances of the second entity, is known as cardinality in dbms. Definition 2: Cardinality is a relationship or join/bridge between rows of  1 table to the rows of another table. via some column (e.g. User Table column {user_id} in another table Posts to filter the posts by user_id). Types of Cardinality in DBMS: There are three types of Cardinality in DBMS. one-to-one one-to-many many-to-one many-to-many Read Also: Degree of Relationship in DBMS ...
Read more

Relational Model in DBMS

-
Image
  Relational Model (RM) represents the database as a collection of relationships. A relationship is nothing more than a table of values. Each row of the table represents a collection of related data values. These rows of the table denote a real-world entity or relationship. The table name and column names are useful for interpreting the meaning of the values in each row. Data are represented as a set of relationships. In the relational model, the data are stored as tables. However, the physical storage of data is independent of how data is organized logically. Some popular relational database management systems are: DB2 and Informix Dynamic Server – IBM Oracle and RDB – Oracle SQL Server and Access – Microsoft Concepts of relational model in DBMS Attribute: Each column in a table. Attributes are the properties that define a relationship. e.g. Student_Rollno, NAME, etc. Tables – In the Relational model the relationships are saved in table format. It ...
Read more