Deck 3: Using SQL Queries to Insert, update, delete, and View Data

Question 1

When you use the insert command,you must specify column names.

Answer 1

False

Question 2

The default sort order for the ORDER BY clause is ascending.

Answer 2

True

Question 3

The = operator is used by an exact search condition.

Answer 3

True

Question 4

The SYSDATE function supplies the current date from the system.

Question 5

All users can select data from all schemas,but they must be granted privileges to modify data.

Question 6

If you are selecting from a table in another user's schema,you must qualify the table name with the schema name.

Question 7

If you omit the search condition in the delete query no record will be deleted.

Question 8

The operator ^= indicates not equal.

Question 9

MIN is a single-row function.

Question 10

Only one search condition may be listed in a WHERE clause.

Question 11

It is necessary to use the TO_DATE function and a format model to insert "01-JAN-2005" into a date column.

Question 12

If o_date is a DATE column,the expression o_date + 10 will give a value 10 days from o_date.

Question 13

When you add a new row to a database table,it is immediately visible to other users.

Question 14

You can use a format model to add a $ to a currency value when retrieving it from the database.

Question 15

The following search condition would match the column value "Sunday": WHERE value LIKE 'Sun%'

Question 16

Instead of typing "START script-path" at the prompt in SQL*Plus you can use the @ character: @script-path.

Question 17

You can choose to increment a sequence by a number other than 1 when each next value is retrieved.

Question 18

To find all rows with NULL values for the column grade,your where condition would be: WHERE grade = NULL.

Question 19

Duplicate values will automatically be suppressed from query results unless you specify otherwise.

Question 20

When you truncate a table by mistake,you can use the roll back command to retrieve the data.

Question 21

How does Oracle prevent other users from modifying data that has been changed in a pending transaction?

A)The records are locked
B)The records are marked as dirty
C)The records are not available
D)There is nothing to prevent another user from modifying the records

Question 22

A join can only be used to retrieve data from two tables that are related via a foreign key relationship.

Question 23

You must use the GROUP BY clause in a query if you are using a group function.

Question 24

In an outer join,all rows will be returned from the outer table.

Question 25

What format model would you use to display 1237.35 as $1237.35

A)1111.11
B)9999.99
C)$####.##
D)$9999.99

Question 26

The UNION ALL operator excludes duplicate values.

Question 27

You must use a table alias when creating a self-join.

Question 28

Which command is used to give a user permission to alter another user's table?

A)ADD
B)GIVE
C)GRANT
D)ALTER

Question 29

The query INSERT INTO student ____ ('abc','123');inserts a new row into the student table.

A)DATA
B)COLS
C)ROWS
D)VALUES

Question 30

If you are joining three tables you must select a column from each table in the select portion of the statement.

Question 31

What is the difference between deleting all records from a table and truncating the table?

A)Deleting is faster
B)Truncating does not save roll back information while delete does
C)Only a DBA can truncate,anyone can delete
D)Delete does not save roll back information while truncate does

Question 32

All database views are read-only.

Question 33

If you think that you may wish to rollback part of a transaction,but not the whole thing,it is a good idea to create a ____.

A)bookmark
B)savepoint
C)transaction marker
D)buffer

Question 34

When using a nested query,the main query is executed after the subquery.

Question 35

When you drop a view,you delete the data values that appear in the view.

Question 36

How would you select the current value of the sequence s_id_seq?

A)DISPLAY s_id_seq.CURRVAL;
B)SELECT s_id_seq.CURRVAL;
C)SELECT s_id_seq.CURRVAL FROM DUAL;
D)SHOW s_id_seq.CURRVAL

Question 37

Subqueries can be nested to many levels.

Question 38

How does Oracle keep sequence information unique for each user?

A)Only one user has access to a sequence
B)Using user session
C)Each user has a private copy of the sequence
D)It is not always unique - there is a possibility that two users may receive the same sequence number

Question 39

What will happen when the following query is executed: UPDATE student
SET s_class='SR';

A)All records in the student table will be updated
B)No records in the student table will be updated
C)An error will occur
D)The first record in the student table will be updated

Question 40

A search condition begins with the keyword ____.

A)SELECT
B)WHERE
C)COMMIT
D)BEGIN

Question 41

Which character is used to indicate the outer table in an outer join?

A)-
B)+
C)/
D)&

Question 42

What is the result of selecting CONCAT(s_first,s_last)from student where s_first is "Emily" and s_last is "Smith"

A)Emily
B)Smith
C)EmilySmith
D)Emily Smith

Question 43

____ is the default date format.

A)YY-DD-MON
B)DD-MON-YY
C)DD-MON-YYYY
D)MON-DD-YYYY

Question 44

If you wish to grant a privilege to all users,what user name is used in the grant command?

A)ALL
B)EVERYONE
C)PUBLIC
D)ALLUSERS

Question 45

To create a locator for a LOB column,use the ____ function.

A)make_blob()
B)init_blob()
C)new_blob()
D)empty_blob()

Question 46

A(n)____ is an alternate name for a query column.

A)record
B)alias
C)row
D)clause

Question 47

Which of the following does not match the string '_T%' when used with the LIKE operator?

A)MT
B)MTW
C)T
D)MTWRF

Question 48

What keyword is added to the select command to retrieve only unique data?

A)UNIQUE
B)NODUP
C)DISTINCT
D)INTERSECT

Question 49

Which operator is used with a subquery that returns multiple values?

A)=
B)<>
C)IN
D)ON

Question 50

Which query is executed first in a nested subquery?

A)The innermost query
B)The outermost query
C)The main query
D)The fastest query

Question 51

The ____ command is used to run a script.

A)begin
B)open
C)start
D)newscript

Question 52

To add data to the database,use the ____ command.

A)OPEN
B)ADD
C)ADD_DATA
D)INSERT

Question 53

What character is used to retrieve all columns in a table?

A)%
B)&
C)*
D)#

Question 54

Which statement would be used to select all students with more than one grade?

A)WHERE COUNT(grade)> 1
B)WHERE COUNT(grade)>= 1
C)HAVING COUNT(grade)> 1
D)HAVING COUNT(grade)>= 1

Question 55

To modify the SQL*Plus display settings click ____ on the menu bar,and then click Environment.

A)Tools
B)Display
C)Settings
D)Options

Question 56

What part of the query contains the join condition when joining two tables?

A)SELECT
B)FROM
C)WHERE
D)GROUP BY

Question 57

To format a number value with a comma between the thousands and hundreds,use the format model ____.

A)9999
B)99,999
C)xxxx
D)xx,xxx

Question 58

What is the most likely cause when a multiple-table query returns more records than you expect?

A)Missing join condition
B)Too much data
C)Used an outer join when you should have used an inner join
D)DBMS made a mistake

Question 59

What is the name of the underlying query used to create a view?

A)View query
B)Source query
C)Select query
D)Source table

Question 60

To retrieve a list of all values that appear in one table and not in another,which operator would be used?

A)MINUS
B)SUBTRACT
C)UNION
D)INTERSECT

Question 61

When you use the ____ logical operator to create a complex search condition,only one of the conditions must be true for the complex search condition to be true.

A)OR
B)AND
C)XOR
D)NOT

Question 62

DML commands that insert,update,or delete database data are called ____________________.

Question 63

In a SQL query,the search condition begins with ____.

A)WHERE
B)FROM
C)BY
D)COND

Question 64

A series of action queries which represents a logical unit of work is known as a(n)____________________.

Question 65

To get the current value of a sequence you would select the ____________________ pseudocolumn.

Question 66

A(n)____________________ is a structure that contains information that identifies the LOB data type and points to the alternate memory location.

Question 67

The format model to display a four digit year (e.g. ,2005)is ____________________.

Question 68

To match a null value use ____.

A)<> NULL
B)!= NULL
C)IS NULL
D)IS 0

Question 69

If you do not wish to save the changes made in a transaction,use the ____________________ command to undo them.

Question 70

To delete table data without saving rollback information,use the ____________________ command.

Question 71

To suppress duplicate values from a query result use the ____________________ qualifier.

Question 72

Use a(n)____________________ to specify a non-default input or output format for NUMBERs and DATEs.

Question 73

ROUND is a ____ function.

A)group
B)single-row
C)financial
D)format

Question 74

The ____________________ logical operator can be used to combine two search conditions.The result will evaluate to true if both conditions are true.

Question 75

Use ____ to save a transaction.

A)COMMIT
B)ROLLBACK
C)SAVE
D)MODIFY

Question 76

A query that uses the ____ set operator joins the output of two unrelated queries into a single output result.

A)INTERSECT
B)DIFFERENCE
C)MERGE
D)UNION

Question 77

The more common name for a natural join is a(n)____.

A)inner join
B)outer join
C)multi-join
D)bi-join

Question 78

A view derives its data from database tables called ____ tables.

A)view
B)source
C)display
D)object

Question 79

When using the SQL*Plus interpreter,the ____ property specifies how many lines appear on a SQL*Plus page.

A)linesize
B)pagelines
C)pagesize
D)numline

Question 80

To remove privileges from a user,use the ____ command.

A)UNDO
B)LIMIT
C)REMOVE
D)REVOKE