What is a database
A database is an organized collection of structured data stored electronically in a computer system.
When the computer was first invented, it was mainly used for scientific researches to perform calculations quickly.
Since the computer was adopted more and more, the requirements were also increased to require the computer to store a larger volume of data for fast retrieval.
Before the database system was invented, the flat file structure was commonly used to store data. For example, here is the comma-separated value (CSV) file that stores employee information:
first name, last name, phone John, Doe, (408)-245-2345 Jane, Doe, (503)-234-2355 ...
The CSV file has three columns which are known as fields and rows which are known as records. When the number of rows in the flat file is increased e.g., million rows, it becomes unmanageable.
In the 1970s, Dr. Ted Codd, a computer scientist, invented the relational model for database management. The relational model deals with many issues caused by the flat file model. According to his model, data is organized in entities and attributes, instead of combining everything in a single structure.
An entity is a person, place, or thing and attributes describe the person, place, and thing. For example, you can use the relational model to organize the employee information into an employee entity with the attributes: first name, last name, and phone:
Each employee may have one or more contacts, you can create a contact entity and relate the employee entity to the contacts entity through a relationship called one-to-many.
By the way, we often refer to the entities as tables, records as rows, and fields as columns.
The relational model is better than the flat file model because it removes the duplicate data e.g. if you put employee and contact information on the same file. The employee, who has more than one contact, will appear in multiple rows.
The Relational Database Management System, or RDBMS in short, manages relational data. Oracle Database is an RDBMS with the largest market share.
Besides the Oracle Database, there are other RDBMS products available. Here are some notable ones:
- Db2 from IBM.
- SQL Server from Microsoft.
- MySQL – the most popular open-source database, also from Oracle.
- PostgreSQL – the most advanced open source database.
Oracle Database features
Oracle Database allows you to quickly and safely store and retrieve data. Here are the integration benefits of the Oracle Database:
- Oracle Database is cross-platform. It can run on various hardware across operating systems including Windows Server, Unix, and various distributions of GNU/Linux.
- Oracle Database has its networking stack that allows application from a different platform to communicate with the Oracle Database smoothly. For example, applications running on Windows can connect to the Oracle Database running on Unix.
- ACID-compliant – Oracle is ACID-compliant Database that helps maintain data integrity and reliability.
- Commitment to open technologies – Oracle is one of the first Database that supported GNU/Linux in the late 1990s before GNU/Linux become a commerce product. It has been supporting this open platform since then.
Oracle Database has several structural features that make it popular:
- Logical data structure – Oracle uses the logical data structure to store data so that you can interact with the database without knowing where the data is stored physically.
- Partitioning – is a high-performance feature that allows you to divide a large table into different pieces and store each piece across storage devices.
- Memory caching – the memory caching architecture allows you to scale up a very large database that still can perform at a high speed.
- Data Dictionary is a set of internal tables and views that support administer Oracle Database more effectively.
- Backup and recovery – ensure the integrity of the data in case of system failure. Oracle includes a powerful tool called Recovery Manager (RMAN) – allows DBA to perform cold, hot, and incremental database backups and point-in-time recoveries.
- Clustering – Oracle Real Application Clusters (RAC) – Oracle enables high availability that enables the system is up and running without interruption of services in case one or more server in a cluster fails.
Oracle Database Editions
Oracle provides three main editions of Oracle Databases as follows:
1) Enterprise Edition (EE) is the common and expensive edition of the Oracle Database. It has the following characteristics:
- No maximum number of CPUs
- No limits on memory or database size
- Include premium features that are not available in other editions.
2) Standard Edition (SE) is a limited edition of the Enterprise Edition that has the following characteristics:
- Limited to four or fewer CPUs
- No limit on memory or database size
- Include many features, but no as many as EE
3) Expression Edition (XE) is a free-to-use version of the Oracle Database that is available on both Windows and GNU/Linux platforms. These are the features of Oracle Database XE 18c:
- Limited to 2 CPUs
- Can use the maximum of 2GB of RAM, and has 12GB of user data.
- Very limited features
Oracle SQL MCQs
- Which of the following is not true regarding LONG values? a) A table can contain only one LONG column b) LONG columns can appear in where clauses c) Stored function cannot return a LONG value d) LONG columns cannot be indexed
Answer:- b) LONG columns can appear in where clauses
- A table can have column of boolean data type. a) TRUE b) FALSE
Answer:- a) TRUE
- Oracle Table can have a boolean data type for its column(s)? a) TRUE b) FALSE
Answer:- b) FALSE
- A long column can store data upto a) 2 Megabytes b) 2 Gigabytes c) 2000 bytes d) None of the above
Answer:- b) 2 Gigabytes
5.BFILE data type can store upto a maximum of
a) 1 GB
b) 2 GB
c) 4 GB
d) None of the above
Answer:- c) 4 GB
- Create table tab_name(col1 long, col2 long); The above statement will create a table tab_name a) TRUE b) FALSE Answer b) FALSE (only one long col can be there)
- CREATE TABLE table_name(column_name CHAR); What will be the length of the column column_name in the table table_name a) 1 b) 2 c) 10 d) 2000
Answer :- a) 1 (default is 1)
- Which all are DATETIME data types that can be used when specifying column definitions? a) TIMESTAMP b) INTERVAL MONTH TO DAY c) DATE d) A & C
Answer:- d) A & C
- CREATE TABLE table_name(column_name CHAR);
The above SQL statement will throw an error as length for CHAR data type is not specified
Answer:- b) FALSE
- Which of the following is not a valid data type in Oracle 10g? a) DATE b) TIMESTAMP(3) c) TIMESTAMP(6) d) TIME
Answer:- d) TIME
- What is the maximum length of a CHAR data type a) 2000 b) 4000 c) 8000 d) No limit
Answer:- a) 2000
- Assume we want to store patient’s image onto the hospital database while creating the patient table which of the following data types should be used for storing image within the database. a) CLOB b) BFILE – store a link to an external binary file (file stored outside of the database) c) BLOB d) NCLOB
Answer:- c) BLOB
- Which of the following is a not Unicode-only datatype? a) CHAR b) NCHAR c) NVARCHAR2 d) All of the above
Answer:- a) CHAR
- How many columns are allowed in Oracle10g table? a) 255 b) 10000 c) 1000 d) there is no limit (infinite)
Answer:- d) there is no limit (infinite)
- What is the maximum length we can give to VARCHAR2( )? a) 2000 b) 4000 c) 1000 d) there is no limit (infinite)
Answer:- b) 4000
- Which data type(s) can hold a null value? a) VARCHAR2( ) b) NUMBER( ) c) DATE d) All of the above
Answer:- d) All of the above
- Which of the following is a Unicode-only datatype? a) Char b) NCHAR c) VARCHAR2 d) None
Answer:- b) NCHAR
- What would be the value stored in database, if 123.89 is stored in a column of datatype NUMBER(6,-2)?
-2 means round to hundreds
Answer:- b) 100
- Which of the following datatype is stored as an external file on the server? a) BLOB b) NCLOB c) CLOB d) BFILE
Answer:- d) BFILE
- Which of the following SQL functions can operate on any datatype? a) MAX b) LOWER c) LPAD d) ADD_MONTHS
Answer:- a) MAX
- Which of the following can be stored in a NUMBER(8,2) datatype? a) 999999.99 b) 999999.999 c) 9999999.99 d) 99999.999 Answer:- c) 9999999.99