Sql Fundamentals Chapter 2

Front 1

The Oracle server stores information about all tables in a special set of relational tables called the _____.

Back 1

data dictionary

Front 2

T/F The definition of a table includes information like table name, table owner, details about the columns that comprise it, and its physical storage size on disk. This information is also referred to as metadata.

Back 2

True

Front 3

How is the structural metadata of a table obtained?

Back 3

by querying the database for the list of columns that comprise it using the DESCRIBE command.

Front 4

What is the syntax for DESCRIBE?

Back 4

DESC[RIBE].tablename

The DESCRIBE keyword can be shortened to DESC.

If you are describing a table that belongs to the schema to which you have connected, the portion of the command may be omitted.

Front 5

T/F The DESCRIBE keyword can be shortened to DESC.

Back 5

True

Front 6

All tables belong to a ________ or _______.

Back 6

schema, owner

Front 7

DUAL belongs to what schema?

Back 7

the SYS schema

Front 8

Numeric columns are often specified as NUMBER(p,s). What does (p,s) stand for?

Back 8

P= precision

S= scale

Front 9

If a column has a data type of: NUMBER(8,2) what does that mean?

Back 9

This means that the values stored in this column can have at most 8 digits. Of these 8 digits, 2 must be to the right of the decimal point.

Front 10

What do VARCHAR2(length) data type columns store?

Back 10

They store variable length alphanumeric character data, where length determines the maximum number of characters a column can contain.

Front 11

What does it mean when a column has a data type of VARCHAR2(20)?

Back 11

It means that words can be store up to 20 characters. If this column contains no data or its content is less than 20 characters, if will not necessarily use the same space as it would use to store a name that is 20 characters long.

Front 12

What does the CHAR(size) column data type specify?

Back 12

The CHAR(size) column data type specifies fixed length columns where row space is preallocated to contain a fixed number of characters regardless of its contents.

Front 13

T/F CHAR(size) column data type is used more commonly than VARCHAR2.

Back 13

False. CHAR is much less commonly used than VARCHAR2.

Front 14

What does the DATE data type store?

Back 14

DATE stores a moment in time with precision including day, month, year, hours, minutes, and seconds.

Front 15

What does the TIMESTAMP data type store?

Back 15

TIMESTAMP stores the same information as DATE but is also capable of storing fractional seconds.

Front 16

What are Binary Large Objects (BLOBS) used for?

Back 16

BLOBs are used for storing binary data like music or video data.

Front 17

_____ refers to an absence of data.

Back 17

NULL

Front 18

T/F Blank spaces do not count as NULL since they are present in the row and have some length even though they are not visible.

Back 18

True.

Front 19

T/F Any column of data which is restricted by the NOT NULL constraint when the table is created must contain some data.

Back 19

True.

Front 20

In relational theory, relations, or tables, are operated on by a formal language called ______.

Back 20

relational algebra

Front 21

What three concepts from relational theory encompass the capability of the SELECT statement?

Back 21

1. projection

2. selection

3. joining

Front 22

What is projection?

Back 22

Projection refers to the restriction of attributes (columns) selected from a relation or table.

Front 23

What is selection?

Back 23

Selection refers to the restriction of the tuples or rows selected from a relation (table). It is often not desirable to retrieve every row from a table.

Front 24

What is joining?

Back 24

Joining, as a relational concept, refers to the interaction of tables with each other in a query.

Front 25

T/F In its most primitive form, the SELECT statement supports the projection of columns and the creation of arithmetic, character, and date expressions.

Back 25

True

Front 26

What is the basic SELECT statement syntax?

Back 26

SELECT* |{[DISTINCT] column|expression [alias],..}

FROM table

Front 27

T/F When using the commands, the case of the reserved words in your query statement does not matter.

Back 27

True

Front 28

T/F The reserved words (or special keywords) can be used as column names or other database object names.

Back 28

False

Front 29

A SELECT statement always comprises _____ or more clauses.

Back 29

two

Front 30

What are the two mandatory clauses in the SELECT statement?

Back 30

SELECT and FROM

Front 31

SELECT * means what?

Back 31

Select all possible columns

Front 32

What does the FROM clause specify?

Back 32

Which table to query to fetch the columns requested in the SELECT clause.

Front 33

What is an alias?

Back 33

An alias is an alternative name for referencing a column or expression.

Front 34

What are alias typically used for?

Back 34

Displaying output in a user-friendly manner. They also serve as shorthand when referring to columns or expressions to reduce typing.

Front 35

What does DISTINCT do?

Back 35

DISTINCT eliminates all duplicate rows from the results set.

An important feature of the DISTINCT keyword is the elimination of duplicate values from combinations of columns.

Front 36

T/F It is a matter of personal taste about the case in which SQL statements are submitted to the database.

Back 36

True. You can write your SQL statements in upper or lower case.

Front 37

T/F SQL reserved words must be specified in uppercase.

Back 37

FALSE. This is a common misconception.

Front 38

What is the one caveat regarding case sensitivity?

Back 38

When interacting with literal values, case does matter.

Front 39

Metadata about different database objects is stored by default in ______case in the data dictionary.

Back 39

Upper

Front 40

_________are generally used as SQL statement terminators.

Back 40

Semicolons

Front 41

Individual statements in SQL scripts are commonly terminated by a ____ _____ (or carriage return) and a forward slash on the next line, instead of a semicolon.

Back 41

line break

Front 42

_______ always requires a statement terminator, and usually a semicolon is used.

Back 42

SQL*Plus

Front 43

________ does not require a statement terminator if only a single statement is present, but it will not object if one is used.

Back 43

SQL Developer

Front 44

T/F It is entirely acceptable for a SQL statement to either be written on one line or to span multiple lines as long as no words in the statement span multiple lines.

Back 44

True.

Front 45

You want to construct and execute queries against tables stored in an Oracle database. Are you confined to using SQL*Plus or SQL Developer?

Back 45

No. Oracle provides SQL*Plus and SQL Developer as free tools to create and execute queries. There are numerous tools available from Oracle and other third-party vendors that provide an interface to the tables stored in an Oracle database.

Front 46

To explore your database environment further, you would like a list of tables, owned by your current schema, available for you to query. How do you interrogate the database dictionary to provide this metadata?

Back 46

The data dictionary is a set of tables and views of other tables that can be queried via SQL. The statement SELECT TABLE_NAME from USER_TABLES; queries the database dictionary for a list of table names that belong to the current user.

Front 47

When querying the jobs table for every row containing just the JOB_ID and MAX_SALARY columns, is a projection, selection, or join being performed?

Back 47

A projection is performed since the columns in the JOBS table have been restricted to the JOB_ID and MAX_SALARY columns.

Front 48

An __________ is usually made up of an operation being performed on one or more column values.

Back 48

expression.

Front 49

The operators that can act upon column values to form an expression depend on what?

Back 49

The data type of the column.

Front 50

What are the operators that can act upon column values to form an expression?

Back 50

1. The 4 cardinal arithmetic operators (addition, subtraction, multiplication, and division) for numeric columns.

2. The concatenation operator for character or string columns.

3. The addition and subtraction operators for date and timestamp columns.

Front 51

What is the operator precedence when more than one operator occurs in an expression?

Back 51

- Round brackets have the highest precedence.

- Division and multiplication operations are nest in the hierarchy

- Addition and subtraction have the lowest precedence.

Front 52

Operations with the same level of precedence are evaluated from ____ to ____.

Back 52

Left to right

Front 53

_________ is an alternate name for a column or an expression.

Back 53

An alias

Front 54

The double pipe symbols || represent the _____. q

Back 54

Character concatenation operator.

Front 55

What does the character concatenation operator do?

Back 55

It is used to join character expressions or columns together to create a larger character expression. Columns of a table may be linked to each other or to strings of literal characters to create one resultant character expression.

Front 56

What are literal values?

Back 56

These values refer to numeric, character, or date and time values found in SELECT clauses that do not originate from any database object.

Front 57

The ____ ____ allows literal expressions to be selected from it for processing and returns the expression results in its single row.

Back 57

dual table

Front 58

Regarding literals, How are words that contain single quotation marks dealt with?

Back 58

Two ways:

1. The most popular way is to add an additional single quotation mark next to each naturally occurring single quotation mark in the character string.

2. The alternative quote (q) operator

Front 59

What is the alternative quote (q) operator?

Back 59

- It enables you to choose from a set of possible pairs of wrapping symbols for character literals as alternatives to the single quote symbols.

Front 60

What are the options for the alternative quote operator?

Back 60

The options are any single-byte or multibyte character or the four brackets: (round brackets), {curly brackets}, [square brackets], or <angle brackets>.

Front 61

How is the number zero and a blank space different from null?

Back 61

Both the number zero and a blank space are different from null since they occupy space.

Front 62

______ is formally defined as a value that is unavailable, unassigned, unknown, or inapplicable.

Back 62

NULL

Front 63

_________ is a term sometimes used to describe a column that is allowed to store null values.

Back 63

Nullable

Front 64

Any arithmetic calculation with a NULL value will always return ______.

Back 64

NULL

Front 65

T/F The character concatenation operators ignore null, whilst the arithmetic operations involving null values always result in null.

Back 65

True.

Front 66

T/F Oracle insists that any column that is a primary key is implicitly constrained to be mandatory (cannot contain nulls).

Back 66

True

Front 67

An alias provides a mechanism to rename a column or an expression. Under what conditions should you enclose an alias in double quotes?

Back 67

If an alias contains more than one word or if the case of an alias must be preserved, then it should be enclosed in double quotation marks. Failure to double quote a multiworded alias will raise an Oracle error. Failure to double quote a single-word alias will result in the alias being returned in uppercase.

Front 68

When working with character literal values that include single quotation marks, how should you specify these literals in the SELECT clause without raising an error?

Back 68

There are two mechanisms available. The more common approach is to replace each naturally occurring single quote with two single quotes. The other approach is to make use of the alternate quote operator to specify an alternate pair of characters with which to enclose character literals.

Front 69

__________ expose a vista of data manipulation possibilities through the interaction or arithmetic and character operators with column or literal data, or a combination of the two.

Back 69

Expressions

Front 70

An unlimited combination of ____, _____, and _____ provides the language to extract the relational data required.

Back 70

projections, selections, and joins

Front 71

A structural definition of a table can be obtained using the _________ command.

Back 71

DESCRIBE

Front 72

Columns in tables store different types of data using various data types, the most common of which are _____, _____, ______, and ______.

Back 72

NUMBER, VARCHAR2, DATE, and TIMESTAMP

Front 73

What does the data type NUMBER(x,y) imply?

Back 73

It implies that numeric information store in this column can have at most x digits, but at least y of these digits must appear on the right hand side of the decimal point.

Front 74

The DESCRIBE command lists:

Back 74

the names, data types, and nullable status of all columns in a table.

Front 75

Mandatory columns are also referred to as what?

Back 75

NOT NULL columns

Front 76

The FROM clause specifies what?

Back 76

The source table or tables from which items are selected.

Front 77

T/F If an alias contains multiple words or the case of the alias is important, it must be enclosed in double quotation marks.

Back 77

True.

Front 78

The ______ _______ is a single column and single row table that is often used to evaluate expressions that do not refer to specific columns or tables.

Back 78

DUAL table

Front 79

T/F Columns which are not governed by a NOT NULL constraint have the potential to store null values and are sometimes referred to as nullable columns.

Back 79

True