Oracle Database Flashcards
What does the database do?
A database collects data, store, and organizes data, and retrieves related data.
What is DBMS and what does it do?
Database management System controls the storage, organization, and retrieval of data.
SQL is the
code and language used to retrieve data from the database.
What is RDBMS and what does it do?
Relational database management system is an organized model of subjects and characteristics that have relationships among the subjects.
3 Characteristics of a Relational Database:
■ Structures are objects that store or access data from the database. Tables, views, and indexes are examples of structures in Oracle.
■ Operations are the actions that are used to define the structures or to manipulate data between the structures. SELECT statements and CREATE statements are examples of operations in Oracle.
■ Integrity rules govern what kinds of actions are allowed on data and the database structure. These rules protect the data and the structure of the database. The primary keys and foreign keys are examples of integrity rules in Oracle.
ER model has [Blank], [Blank], and [Blank].
An ER model has entity, relationship, and attributes.
ER model: Entities and Attributes
An entity in a logical model is much like a noun in grammar—a person, place, or thing. The characteristics of an entity are known as its attributes. Attributes
are detailed information about an entity that serves to qualify, identify, classify, or quantify it.
ER model: Relationships and Unique Identifiers
In the ER model, a solid line represents a mandatory relationship, and a crowfoot represents the “many.” But in some departments, there may not be any employees at all. Optional occurrence is represented by a dotted line.
How do you create a physical model?
The physical model is created by taking the logical model and creating a database and database objects to represent the entities and relationships.
In the physical model, each entity becomes a [Blank] and attributes of the entity become columns of the table.
table
What is DML and what is it used for?
Database Manipulation Language is used to access, insert, modify, or delete data in the existing structures of the database.
DML Statements: SELECT, INSERT, UPDATE, DELETE, MERGE, EXPLAIN PLAN, LOCK TABLE,
What is DDL and what is it used for?
Data Definition Language is used to define, alter, or drop database objects and their privileges.
DDL Statements: CREATE, ALTER, DROP, RENAME, TRUNCATE, GRANT, REVOKE, AUDIT, NOAUDIT, COMMENT
How do you write the concatenation operator?
||’Oracle12c’ || ‘Database’ results in ‘Oracle12cDatabase’.
What is a query?
A query is a request for information from the database tables.
Does query modify data? If not, what does it do?
Queries do not modify data; they read data from database tables and views.
A table is used to [Blank] and is [Blank] in rows and columns.
A table is used to store data and is stored in rows and columns.
Keyword to ensure unique rows
DISTINCT
What is the DUAL table and what is it used for?
The DUAL table is a special table available to all users in the database. The DUAL table is mostly used to select system variables or to evaluate an expression.
What is the LIKE keyword?
Using the LIKE operator, you can perform pattern matching.
The pattern-search character % is used to match any character and any number of characters.
The pattern-search character _ is used to match any single character.
What is the ORDER BY keyword?
ORDER BY clause to sort the resulting rows in a specific
order based on the data in the columns.
ASC
DESC
What is the EXIST operator?
The EXISTS operator checks for the existence of a row in the subquery based on the condition.
What is the DESCRIBE command?
list the table columns, the columns are listed in that order.
COMMIT statement
Ends the current transaction, making data changes permanent
and visible to other sessions.
ROLLBACK statement
Undoes all data changes in the current transaction.
What is a constraint?
A constraint can be considered as a rule or policy defined in the database to enforce data integrity
What is a table?
A table is defined with columns, and it stores rows of data. A table should have
at least one column. In Oracle, a table normally refers to a relational table. You can also
create object tables.
What is a view?
A view is a customized representation of data from one or more tables and/or views.
Views are used as windows to show information from tables in a certain way or to restrict
the information. Views are queries stored in the database that select data from one or more
tables. Unlike tables, views do not store data—they can be considered as stored queries. They also provide a way to restrict data from certain users, thereby providing an additional
level of security
What is a schema?
A schema is a collection of database objects owned by a single user. For example, a schema
can have tables, views, triggers, synonyms, and PL/SQL programs such as procedures. A
schema is owned by a database user and has the same name as the user. If the database user
does not own any database objects, then no schema is associated with the user. A schema is
a logical grouping of database objects.
What is PL/SQL Programs?
PL/SQL stored programs include procedures, functions, and packages.
A procedure is a PL/SQL programmatic construct. A function is similar to a procedure but
always returns a value. A package is a grouping of related PL/SQL objects.
