Sql Fundamentals Chapter 1

Front 1

a set of files on disk

Back 1

a database

Front 2

A database exists until its files are

Back 2

deleted deliberately.

Front 3

How do you access the database?

Back 3

Through the (oracle) instance.

Front 4

What is an instance

Back 4

a set of processes and memory structures.

Front 5

Where does the instance exist?

Back 5

on the CPU(s) and in the memory of the server node, and this existence is temporary.

Front 6

T/F An instance can be started and stopped

Back 6

True.

Front 7

What manages all access to the database?

Back 7

the instance

Front 8

T/F In the Oracle environment any user can have direct contact with the database.

Back 8

False. This is impossible.

Front 9

An oracle instance + an Oracle database=

Back 9

an oracle server

Front 10

In a two-tier, client-server model what do the client tier and server tier do?

Back 10

The client tier generates the SQL commands, and the server tier executes them. (LAN between the two sides)

Front 11

The client tier consists of what two components?

Back 11

the users and the user processes.

Front 12

The server tier consists of what three components?

Back 12

The server processes that execute the SQL, the instance, and the database itself.

Front 13

T/F Each user interacts with a user process.

Back 13

True

Front 14

T/F Each user process interacts with a server process.

Back 14

True

Front 15

The server processes interact with the instance, and the instance interacts with the ______.

Back 15

Database

Front 16

What is a session?

Back 16

A user process in communication with a server process. There will usually be one process per user and one server process per user process.

Front 17

The user and server processes that make up sessions are launched on demand by _____ and terminated when no longer required; this is the log-on and log-off cycle.

Back 17

users

Front 18

The instance processes and memory structures are launched by the ________ and persist until the administrator deliberately terminates them; this is the database start-up and shut-down cycle.

Back 18

database administrator

Front 19

What user processes are used extensively in Oracle?

Back 19

SQL*Plus and SQL Developer. These are simple processes provided by Oracle for establishing sessions against an Oracle server and issuing ad hoc SQL.

Front 20

All communication with an oracle server follows what model?

Back 20

client-server

Front 21

What is the simplest form of the database server?

Back 21

One instance connected to one database. (in a more complex environment one database can be opened by many instances. RAC)

Front 22

What are the benefits of RAC?

Back 22

scalability, performance and zero down time.

Front 23

What does an application server do?

Back 23

replaces the client-side software traditionally installed on end-user terminals; it runs applications centrally, presenting them to users in windows displayed locally in web browsers.

Front 24

T/F The Oracle Application Server is a platform for developing, deploying and managing web applications.

Back 24

True

Front 25

What is a web application?

Back 25

A web application can be defined as any application with which users communicate with HTTP.

Front 26

Web applications usually run in at least three tiers. They are..

Back 26

1. a database tier that manages access to the data.

2. the client tier (often implemented as a web browser) handles the local window management for communications with the users.

3. an application tier in the middle executes the program logic that generates the user interface and the SQL calls to the database.

Front 27

In the three-tier environment, there are two types of sessions. What are they?

Back 27

1. end-user sessions: from the client tier to the middle tier

2. database sessions: from the middle tier to the data tier.

Front 28

The end-user sessions with be established with ____.

Back 28

HTTP

Front 29

The database sessions are made up of what?

Back 29

client-server sessions consisting of a user process and a server process.

Front 30

What is connection pooling?

Back 30

With connection pooling, the application server establishes a relatively small number of persistent database sessions and makes them available on demand (queuing requests it necessary) to a relatively large number of end-user sessions against the application server.

Front 31

Oracle Enterprise Manager comes in what three forms?

Back 31

Database Express

Fusion Middleware Control

Cloud Control

Front 32

What is Oracle Enterprise Manager Database Express?

Back 32

It is a graphical tool for managing one database, which may be a RAC clustered database. It consists of a Java process running on the database server machine. Administrators connect to Database Express from a browser, and Database Express then connects to the database server.

Front 33

What is Oracle Enterprise Manager Fusion Middleware Control?

Back 33

It is a graphical tool for managing a Fusion Middleware deployment. These deployments typically include Oracle WebLogic Server, an industry-leading application server which provides the container Java virtual machines (JVMs) that host Oracle or custom Java applications.

Front 34

What is Oracle Enterprise Manager Cloud Control?

Back 34

It globalizes the management environment. It can also manage the nodes, or machines, on which the servers run, as well as (through plug-ins) a wide range of third party products.

Front 35

Critical to the concept of cloud computing is ____ ___.

Back 35

service virtualization

Front 36

What does service virtualization mean?

Back 36

At all levels there is a layer of abstraction between what is requested and what is provided. End users ask for an application service and let the cloud work out which clustered J2EE application server can best provide it.

Front 37

Within the database it is possible to user what three languages?

Back 37

1. SQL

2. PL/SQL

3. Java

Front 38

What is SQL used for?

Back 38

data access, but it cannot be used for developing complete applications. It has no real facilities for developing user interfaces, and it also lacks the procedural structures needed for manipulating rows individually.

Front 39

What is PL/SQL?

Back 39

PL/SQL is a third-generation language (3GL) proprietary to Oracle.

It has the usual procedural constructs (such as if-then-else and looping) and facilities for user interface design.

In PL/SQL code, one can embed calls to SQL. Thus, a PL/SQL application might use SQL to retrieve one or more rows from the database, then perform various actions based on their content, and then issue more SQL to write rows back to the database.

Front 40

T/F Any Java programmer should be able to write code that will work with an Oracle database.

Back 40

True.

Front 41

The relational paradigm models data as ...

Back 41

two dimensional tables.

Front 42

A table consists of..

Back 42

a number of rows, each consisting of a set of columns.

Front 43

T/F Within a table, all the rows have the same column structure, though it is possible that in some rows some columns may have nothing in them.

Back 43

True

Front 44

A table may also be referred to as what?

Back 44

A relation or an entity

Front 45

A row may also be referred to as what?

Back 45

records or tuples

Front 46

A column may also be referred to as what?

Back 46

an attribute or field.

Front 47

What is the cardinality of typles?

Back 47

the number of rows in a table.

Front 48

The process of modeling data into relational tables is known as what?

Back 48

normalization

Front 49

In most cases, data stored in a relational database and accessed with SQL should be normalized to the _____ form.

Back 49

third

Front 50

The first normal is to

Back 50

remove the repeating groups

Front 51

What is second normal form?

Back 51

Removes columns from the table that are not dependent on the primary key.

Front 52

What is the third normal form?

Back 52

removes all columns that are interdependent

Front 53

What is a primary key?

Back 53

The unique identifier of a row in a table, either one column or a concatenation of several columns (known as a composite key)

Front 54

Is it possible to define tables without a primary key?

Back 54

Yes, though it is usually not a good idea.

Front 55

What is a foreign key?

Back 55

It is a column (or a concatenation of several columns) that can be used to identify a related row in another table. A foreign key in one table will match a primary key in another table.

Front 56

List the Data Manipulation Language (DML) commands.

Back 56

1. Select

2. Insert

3. Update

4. Delete

5. Merge

Front 57

List the Data Definition Language (DDL) commands.

Back 57

1. Create

2. Alter

3. Drop

4. Rename

5. Truncate

6. Comment

Front 58

List the Data Control Language (DCL) commands.

Back 58

1. Grant

2. Revoke

Front 59

List the Transaction Control Language (TCL) commands.

Back 59

1. Commit

2. Rollback

3. Savepoint

Front 60

What are the two most basic tools user to connect to an Oracle database?

Back 60

SQLPlus and SQL Developer

Front 61

_____ is a client-server tool for connecting to a database and issuing ad hoc SQL commands. It can also be used for creating PL/SQL code and has facilities for formatting results.

Back 61

SQL*Plus

Front 62

In terms of architecture, SQL*Plus is a user process written in ___.

Back 62

C

Front 63

SQL*Plus establishes a session against an instance and a database over the ___ ___ ___.

Back 63

Oracle Net protocol

Front 64

The ORACLE_HOME variable points to the Oracle Home. What is an Oracle Home?

Back 64

It is the software installation: the set of files and directories containing the executable code and some of the configuration files.

Front 65

The PATH must include the ___ directory in the Oracle Home.

Back 65

bin

Front 66

The LD_LIBRARY_PATH should include the ___ directory in the Oracle Home, but in practice you may get away without setting this.

Back 66

lib

Front 67

What command can you use to check that ORACLE_HOME, PATH, and LD_LIBRARY_PATH variables have been set up correctly?

Back 67

~]$ echo $ORACLE_HOME

~]$ echo $PATH

~]$ echo $LD_LIBRARY_PATH

Front 68

What is the format of the login string for SQL*Plus?

Back 68

Database username followed by a forward slash character as a delimiter, then a password followed by an @ symbol as a delimiter, and finally an Oracle Net connect identifier.

system/oracle@orc1

Username: system

Password: oracle

Database: orc1

Front 69

______ does not have any way of storing database connection details. Each time a user wishes to connect to a database, the user must tell _____ who they are and where the database is.

Back 69

SQL*Plus, SQL*Plus

Front 70

_________ is a tool for connecting to an Oracle database and issuing ad hoc SQL commands. It can also manage PL/SQL objects.

Back 70

SQL Developer

Front 71

SQL Developer is written in ____ and requires a ______ ____ ___ (JRE) to run.

Back 71

Java, Java Runtime Environment

Front 72

In Oracle parlance, a database user is a person who can do what?

Back 72

log on to database

Front 73

What is a database schema?

Back 73

All the objects in the database owned by one user.

Front 74

T/F Users and Schemas can be used interchangeably as there is a one-to-one relationship between the two.

Back 74

True.

Front 75

A ______ is initially created empty, when a user is created with the ____ ___ command.

Back 75

schema, create user

Front 76

_____ are used for storing objects. These may be data objects such as tables or programmatic objects such as PL/SQL stored procedures.

Back 76

Schemas

Front 77

T/F It is impossible for a data object to exist independently of a schema.

Back 77

True. All tables must have an owner. The owner is the user in whose schema the table resides.

Front 78

What is the unique identifier for a table or any other schema object?

Back 78

The username, followed by the object name.

Front 79

_____ is not an application development language, but is invoked by such languages when they need to access data.

Back 79

SQL

Front 80

The Oracle ____ ____ provide a platform for developing and deploying such applications.

Back 80

server technologies.

Front 81

The combination of the Oracle server technologies and SQL result in an environment conforming to the relational database paradigm that is an enabling technology for ___ ___.

Back 81

Grid computing

Front 82

______ is a command-line utility installed into the Oracle Home.

Back 82

SQL*Plus

Front 83

___ ____ is a graphical tool installed into its own directory.

Back 83

SQL Developer

Front 84

T/F SQL Developer relies on an LDAP directory for name resolution.

Back 84

False. LDAP is only one of several techniques for name resolution.

Front 85

How can you move a schema from one user to another?

Back 85

You cannot move a schema from one user to another. A schema and user are inseparable.

Front 86

Oracle Database 12c has evolved from a RDBMS

to an ________ RDBMS supporting the organziation of virtually any type of information with no practical limit on the volume of data that may be stored.

Back 86

Object

Front 87

The _______ is the repository for data and the engine that manages access to it.

Back 87

database

Front 88

What does the Oracle WebLogic Server do?

Back 88

It runs software that generates the web user interfaces that submit calls for data retrieval and modification to the database for execution.

Front 89

What is a comprehensive administration tool for monitoring, managing, and tuning the Oracle processes and also (through plug-ins) third-party products.

Back 89

Oracle Enterprise Manager

Front 90

What is the cloud?

Back 90

The cloud is an approach to the delivery of IT services that maximizes the cost efficiency of the whole environment by delivering computing power from a pool of available resources to wherever it is needed, on demand.

Front 91

The user process can be any client-side software that is capable of connecting to an ___ ___ ___.

Back 91

Oracle server process

Ex of user processes: SQL*Plus and SQL Developer

Front 92

T/F Applications running in the Oracle WebLogic Server environment can't connect to any database for which there are Java-compliant drivers.

Back 92

False. The can connect to any db for which there are Java-compliant drivers. It is not necessary to use an Oracle db.

Front 93

The simplest processing model of web applications is three-tier. What are the tiers?

Back 93

1. A Client Tier (manages user interface)

2. A Middle Tier (generates the interface and issues SQL statements to the data tier)

3. A Data Tier (manages the data itself)

Front 94

What are examples of the three tiers in the Oracle environment?

Back 94

Client tier = browser

Middle tier = Oracle Weblogic Server running software (probably written in Java

Data Tier = an Oracle server (instance + database)

Front 95

T/F The cloud is not exclusive to Oracle. At the physical level, some Operating Systems and hardware vendors are providing cloud-like capabilities.

Back 95

True. These include the ability to partition servers into virtual machines and dynamically add or remove CPU(s) and RAM from the virtual machines according to demand.

Front 96

T/F It should be possible to design a cloud environment with no single point of failure, thus achieving the goal of 100 percent uptime that is being demanded by many users.

Back 96

True.

Front 97

A ____ ____ is conceived when high-level constructs called entities, comprising various attributes and their relationships, are typically represented together in a diagram.

Back 97

Logical Model

Front 98

Attributes that uniquely identify an instance of an entity are designated as ____ ____ and are sometimes denoted by "#*".

Back 98

primary keys

Front 99

The logical model is then turned into a relational model by translating the entities into _____, commonly referred to as tables.

Back 99

relations

The idea here is that sets of instances of the entities are collectively modeled as a table. The attributes are turned into table columns.

Front 100

Each instance of an entity is reflect as a ___ or row of data, each having values for its different attributes or columns.

Back 100

tuple

Front 101

The number of "___ ___ ___ ___" is the "cardinality of the tuples."

Back 101

rows in the table

Front 102

Usually the attributes that are unique for each row are called unique keys, and typically a unique key, is chosen to be a _____ key.

Back 102

primary

Front 103

T/F Multiples row can share the same primary key value.

Back 103

False

Front 104

T/F SELECT is a DML statement. In practice, no one includes it when they refer to DML--they talk about it as though it were a language in its own right.

Back 104

True

Front 105

____ is a set-oriented language capable of nothing other than data access. For application development, one will therefore need a procedural language that can invoke SQL calls.

Back 105

SQL

Front 106

ORA-12154: TNS: could not resolve the connect identifier specified

What does this error mean?

Back 106

This error is because the connect identifier given cannot be resolved into database connection details by the TNS layer of Oracle NET.

Front 107

ORA-12541: TNS: no listener

What does this error mean?

Back 107

This indicates that the connect identifier has resolved correctly into the address of a database listener, but that the listener is not actually running.

Front 108

ORA-12514: TNS: listener does not currently know of service requested in connect descriptor.

What does this error indicate?

Back 108

This error is generated by the database listener. SQL*Plus has found the listener with no problems, but the listener cannot make the onward connection to the database service. The most likely reason for this is that the database instance has not been started, so the user should ask the database administrator to start it and then try again.

Front 109

ORA-01017: invalid username/password

What does this error indicate?

Back 109

To receive this message, the user must have contacted the database. The user has got through all the possible network problems, the database instance is running, and the database itself has been opened by the instance. The user just has the password or username wrong.