Sql Fundamentals Chapter 11

Front 1

The most commonly used type of object in a database is the ____.

Back 1

table.

Front 2

T/F The structural rules and the constraint rules together restrict the data that can be inserted into the table.

Back 2

True.

Front 3

T/F All objects have a name, and all objects are owned by someone.

Back 3

True

Front 4

The objects the user owns are their ____.

Back 4

schema

Front 5

What query lists the object types that exist in a database?

Back 5

SQL> select object_type, count (object_type) from dba_objects

This query addresses the view DBA_OBJECTS, which has one row for every object in the database.

Alternate views are USER_OBJECTS, which will show all the objects owned by you, and ALL_OBJECTS, which will show all the objects to which you have been granted access. All users have access to these.

Front 6

The objects of greatest interest to a SQL programmer are those that contain, or give access to, data. These are (list 5)...

Back 6

1. Tables

2. Views

3. Synonyms

4. Indexes

5. Sequences

Front 7

What is a view?

Back 7

It is a stored SELECT statement that can be addressed as though it were a table. It is nothing more than a SELECT statement but, rather than running the statement itself, the user issues a SELECT statement against the view.

Front 8

What is a synonym?

Back 8

It is an alias for a table (or a view). Users can execute SQL statements against the synonym, and the database will map them into statement against the object to which the synonym points.

Front 9

How do indexes improve access time to rows in tables?

Back 9

If a query requires only one row, then rather than scanning the entire table to find the row, an index can give a pointer to the row's exact location. Of course, the index itself must be searched, but this is often faster than scanning the table.

Front 10

What is a sequence?

Back 10

It is a construct that generates unique numbers. There are many cases where unique numbers are needed. Sequences issue numbers in order, on demand: it is absolutely impossible for the same number to be issued twice.

Front 11

Who is a user?

Back 11

Any person who can connect to the database. The user will have a username and a password.

Front 12

A _____ is a container for the objects owned by a user.

Back 12

schema

Front 13

T/F When a user is created, their schema is created too.

Back 13

True.

Initially, it will be empty.

Front 14

T/F A schema must have an object in it.

Back 14

False. Some schemas will always be empty; the user will never create any objects, because they do not need to and (if the user is set up correctly) will not have the necessary privileges anyway.

Front 15

Schema objects are objects with an _____.

Back 15

owner.

Front 16

What is the unique identifier for an object?

Back 16

The unique identifier for an object of a particular type is not its name-- it is its name, prefixed with the name of the schema to which it belongs.

Ex. Thus the table HR.REGIONS is a table called REGIONS, which is owned by user HR.

Front 17

A number of users (and their associated schemas) are created automatically at database creation time. Principal among these are which two users?

Back 17

SYS and SYSTEM.

Front 18

What does the SYS user own?

Back 18

The data dictionary (a set of tables that define the database and its contents). SYS also owns several hundred PL/SQL packages: code that is provided for the use of database administrators and developers.

Front 19

T/F Objects in the SYS schema should never be modified with DML commands.

Back 19

True.

Front 20

What would happen if you were to execute DML against the data dictionary tables?

Back 20

You would run the risk of corrupting the data dictionary, with disastrous results.

Front 21

You update the data dictionary by running _____ commands.

Back 21

DDL (Such as create table). This creates a layer of abstraction between you and the data dictionary itself.

Front 22

What does the SYSTEM schema store?

Back 22

Various additional objects used for administration and monitoring.

Front 23

All schema object names must conform to certain rules. What are they?

Back 23

1. The name may be from 1 to 30 characters long (with the exception of database link names that may be up to 128 characters long)

2. Reserved words (such as SELECT) cannot be used as object names.

3. All names must begin with a letter from "A" through "Z".

4. The characters in a name can only be letters, numbers, an underscore (_), the dollar sign ($), or the hash symbol (#).

5. Lowercase letters will be converted to uppercase.

Front 24

Object names must be no more than ___ characters. The characters can be ___, ___, ___, ___ or ____.

Back 24

30

letters, digits, underscore, dollar, hash

Front 25

T/F Object names are ALWAYS case sensitive.

Back 25

True.

Front 26

These two tables different or the same?

SQL> create table lower (c1 date);

Table created.

SQL> create table "lower" (coll varchar2 (2));

Table created.

Back 26

No. The two tables are completely different

Table_Name

--------------------

lower

LOWER

While it is possible to use lowercase names and nonstandard characters, it is considered bad practice because of the confusion it can cause.

Front 27

What is a namespace?

Back 27

A namespace defines a group of object types, within which all names must be uniquely identified, by schema and name. Objects in different namespaces can share the same name.

Front 28

What object types all share the same namespace?

Back 28

- Tables

- Views

- Sequences

- Private synonyms

Front 29

T/F It is impossible to create a view with the same name as a table.

Back 29

True (at least, it is impossible if they are in the same schema).

Front 30

T/F An index cannot have the same name as a table.

Back 30

False. It is possible, even within the same schema.

Front 31

T/F It is possible to define columns as object.

Back 31

True.

Front 32

Each table exists as a definition in the ___ ___.

Back 32

data dictionary

Front 33

On creation, the table will have been assigned a limited amount of space, known as an _____, within the database. This may be small, perhaps only a few kilobytes or megabytes. As rows are inserted into the table, this ____ will fill.

Back 33

Extent, extent.

Front 34

When an extent is full what will the database do?

Back 34

It will automatically assign another extent to the table. Even if every row is deleted, the extents remain allocated to the table.

Front 35

What happens to the extents if the table is dropped or truncated?

Back 35

They will be freed up and returned to the database for user elsewhere.

Front 36

T/F When creating tables, each column must be assigned a data type, which determines the nature of the values that can be inserted into the column.

Back 36

True.

Front 37

What are the three data types for alphanumeric date?

Back 37

1. VARCHAR2

2. NVARCHAR2

3. CHAR

Front 38

What is VARCHAR2?

Back 38

Variable-length character data, from 1 byte to 4 KB. The data is stored in the database character set.

Front 39

What is NVARCHAR2?

Back 39

Like VARCHAR2, but the data is stored in the alternative national language character set, one of the permitted Unicode character set.

Front 40

What is CHAR?

Back 40

Fixed-length character data, from 1 byte to 2KB, in the database character set. If the data is not the length of the column, then it will be padded with spaces.

Front 41

What is the data type for binary data?

Back 41

RAW

Front 42

What is RAW?

Back 42

Variable-length binary data, from 1 byte to 4KB. Unlike the CHAR and VARCHAR2 data types, RAW data is not converted by Oracle Net from the database's character set to the user process's character set on SELECT or the other way on INSERT.

Front 43

What are the data types for numeric data?

Back 43

1. NUMBER

2. FLOAT

3. INTEGER

Front 44

What is the NUMBER datatype?

Back 44

Numeric data, for which you can specify precision and scale. The precision can range from 1 to 38, the scale can range from -84 to 127.

Front 45

What is the FLOAT datatype?

Back 45

This is an ANSI data type, floating-point number with precision of 126 binary ( or 38 decimal). Oracle also provides BINARY_FLOAT and BINARY_DOUBLE as alternatives.

Front 46

What is the INTEGER datatype?

Back 46

It is equivalent to NUMBER, with scale zero.

Front 47

What are the data types for date and time data?

Back 47

1. DATE

2. TIMESTAMP

3. TIMESTAMP WITH TIMEZONE

4. TIMESTAMP WITH LOCAL TIMEZONE

5. INTERVAL YEAR TO MONTH

6. INTERVAL DAY TO SECOND

Front 48

What is DATE data type?

Back 48

This is either length zero, if the column is empty, or 7 bytes. All DATE data includes century, year, month, day, hour, minute, and second. The valid range is from January 1, 4712 BC to December 31, 9999 AD.