Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Databases > db final > Flashcards

db final Flashcards

1
Q

Which of the following Oracle tools can be used to execute SQL statements against a database?

A

SQL Developer
SQL*Plus command line
SQL Live

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

The relational model consists of which of the following. Choose all that are true:

A

Primary and foreign keys

Collection of tables (relations)

Data integrity for accuracy and consistency

Set of operators to act on the relations, called the relational algebra

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What are a primary key’s identifying characteristics? (Select all that apply.)

A

It uniquely identifies each row

it cannot be NULL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Consider the following rows in a table called CUSTOMERS:

CUST_ID FIRST_NAME MIDDLE LAST_NAME
1 Bianca M. Canales
2 Chua A. Nguyen
3 Bianca M. Jackson
4 Maya R. Canales
5 Bianca S. Canales

How many rows of data will be displayed as the result of executing the following statement: (one answer)

SELECT DISTINCT LAST_NAME, FIRST_NAME FROM CUSTOMERS;

A

4

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

The SHIPS table has two columns: SHIP_ID and SHIP_NAME

Which of the following SELECT statements will produce a syntax error? (Choose two.)

A

SELECT FROM ships;

SELECT (ship_id, ship_name) FROM ships;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Which of the following is NOT required to form a syntactically correct SELECT statement?

A

a valid column name

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Consider the following table structure:
Name Type
——————– ———
ENGINE_ID NUMBER (PRIMARY KEY)
ENGINE_NAME VARCHAR2(30)
DISPLACEMENT NUMBER

What will be the result of executing the following SELECT statement?

SELECT ENGINE_NAME FROM ENGINES;

A

it will display the engine name from all of the rows in the ENGINES table, however many there may be (blank space for NULL values)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Given that the columns and table name are all correct, which of the following lines of the SELECT statement contain an error? (line numbers added)

01 SELECT name, contact,
02 “Person to Call”, phone
03 FROM publisher;

A

02

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Which component(s) of the following query is/are a literal (select all that apply):

SELECT order_id || ‘-‘ || line_item_id || ‘ ‘ || quantity “Purchase”
FROM line_item;

A

‘ ‘
‘-’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

You are tasked with creating a SELECT statement to retrieve data from a database table named PORTS. The PORTS table has two columns: PORT_ID, and PORT_NAME. Which of the following is a valid SELECT statement? (Choose all that apply.)

A

SELECT * FROM PORTS;

SELECT PORT_NAME, PORT_ID FROM PORTS;

SELECT ‘Name of the port ‘ || PORT_NAME FROM PORTS;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

To become an Oracle SQL Certified Associate, one must pass one exam, 1Z0-071.

A

True

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Which is the only type of relationship that can be directly implemented by the relational model?

A

1-Many

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

The category of SQL commands used to work with the actual data, such as INSERT, UPDATE, and DELETE, is called _____.

A

Data Manipulation Language (DML)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Which of the following are Oracle Development Environments?

A

SQL Live
SQL*Plus command line
SQL Developer

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

A table consists of (choose the single best answer)

A

Rows and columns, primary key, and possibly foreign keys

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

A primary key may contain NULL values.

A

False

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

The category of SQL commands used to build database objects, such as CREATE, ALTER, and DROP, is called ________.

A

Data Definition Language (DDL)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

The relational model consists of which of the following. Choose all that are true:

A

Collection of tables (relations)

Set of operators to act on the relations, called the relational algebra

Primary and foreign keys

Data integrity for accuracy and consistency

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What is the number one DBMS used worldwide, according to DB-Engines Ranking?

A

Oracle

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

A ______ is the intersection of a row and a column.

A

field

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

The UNIQUE keyword in the SELECT clause will eliminate duplicate values in the result set

A

False

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What will be the result of the executing the following SELECT statement? SELECT ENGINE_NAME FROM ENGINES; SELECT

A

It will display engine names from all of the rows in the ENGINES table … (NULL will display as blank)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

Consider the following statement:

CREATE TABLE PORTS
(port_id NUMBER,
port_name VARCHAR2(20));

Which of the following modifications to the statement is the only valid way to declare that the port_name is required (not optional)?

A

port_name VARCHAR2(20) NOT NULL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

After executing the following SQL statements:

CREATE TABLE invoices (inv_id NUMBER, discount NUMBER));
INSERT INTO invoices VALUES (7, 5);
INSERT INTO invoices VALUES (3, 12);

Which of the following SQL statements will fail? (Choose two.)

A

ALTER TABLE invoices MODIFY discount VARCHAR2(3);

ALTER TABLE invoices MODIFY discount DEFAULT ‘ZERO’;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

Which of the following are valid CREATE TABLE statements? (Choose all that are valid.)

A

CREATE TABLE “Boat Inventory”

(ID NUMBER,

CAPTAIN VARCHAR2(20));

CREATE TABLE Work_Schedule (ID NUMBER);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

Which of the following would be an invalid column or table name? Select all that apply.

A

P!nk

21PILOTS

Lady-Gaga

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

What would happen when the following statement is executed:

CREATE TABLE GGC_Student

( StudentID CHAR(6),

SName VarChar2(40),

gpa Number(5,4)

);

A

The table will be created without a primary key

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

Which of the following data type(s) is(are) supported by Oracle?

A

NCLOB

NUMBER

CHAR

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

Which two constraints are combined to implement a primary key?

A

UNIQUE

NOT NULL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

The difference between dropping a column from a table with DROP and setting the column to UNUSED is:

A

The UNUSED column and its data are retained within the table’s storage allocation and counts again the total limit on the number of columns that a table is allowed to have, but no longer appears within the table’s description as shown with a DESC or DESCRIBE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

CREATE TABLE NUM_TEST ( A NUMBER(5, 3));
INSERT INTO NUM_TEST (A) VALUES 3.1415;
SELECT A FROM NUM_TEST;

What is the displayed output from the SELECT statement?

A

the statement will fail because you cannot create two primary key constraints on one table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

Which of the following options can be used with the keyword CREATE to form a syntactically correct SQL statement to create a table?

A

at least one column-definition

the keyword CONSTRAINT

the keyword TABLE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

What component of SQL is used to build and manage the database objects and define the structure of the database?

A

DDL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

Which line number illustrates an “in-line constraint”? Choose all that apply.

01 CREATE TABLE PORTS
02 ( Port_ID NUMBER(3) PRIMARY KEY,
03 Port_Name VARCHAR2(30) NOT NULL,
04 Region_ID CHAR(4),
05 CONSTRAINT ports_region_id_FK FOREIGN KEY (Region_ID) REFERENCES Region(Region_ID));

A

02
03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

Which of the following are valid table names that may be used with the CREATE TABLE command? Choose all that apply.

A

Order_lines

“Boat Inventory”

Retired#Emps

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

The data type that would be used for a column that will be storing employee photographs for a large, multi-national company is:

A

BLOB

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

Which of the following is the recommended constraint name for the SHIPS table having Primary key SHIP_ID?

A

Ships_Ship_ID_PK

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

The DESCRIBE command, (DESC) can be used to show which of the following?

A

a table’s structure, that is, its columns and data types

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

A collection of the database objects, such as tables, sequences, views, and indexes, that are owned by a single user account is called a(n) _______.

A

schema

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

Which of the following are true for ‘RR’ year designation?

A

25 means 2025

45 means 2045

68 means 1968

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

The default time for a date is:

A

12:00 a.m.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
42
Q

Consider the ADDRESS table that has the following 3 columns and no rows:
ID NUMBER NOT NULL,
ZONE NUMBER,
ZIP_CODE VARCHAR2(5)

COMMIT;
INSERT INTO ADDRESS VALUES (1, 1, ‘94506’);
SAVEPOINT ZONE_ADDRESS_1;
UPDATE ADDRESS SET ZONE = 2 WHERE ZIP_CODE =’94506’;
ROLLBACK;

A

the ADDRESS table will have no rows.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
43
Q

If a table T3 has four numeric columns (A, B, C, D) and no primary key, which of these statements will succeed? Choose all that apply.

A

INSERT INTO T3 VALUES (3, 6, 7, NULL);

INSERT INTO T3 VALUES (‘3’, ‘9’, ‘10’, ‘12’);

INSERT INTO T3 SELECT * FROM T3;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
44
Q

What is the result of:

UPDATE cruises
SET cruise_name = ‘Bahamas’, SET start_date = SYSDATE
WHERE cruise_id = 1;

A

For the all records in the CRUISES table whose cruise_id is 1, cruise_name will be set to ‘Bahamas’ and start_date will be set to the current date

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
45
Q

Which of these commands will remove every row in a table, but not delete the table itself? Choose one or more answers.

A

A DELETE command with no WHERE clause

A TRUNCATE command

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
46
Q

CREATE TABLE STUDENT_LIST
(STUDENT_ID NUMBER,
STUDENT_NAME VARCHAR2(30),
STUDENT_PHONE VARCHAR2(20));

INSERT INTO STUDENT_LIST VALUES (1, ‘Joe Wookie’, 3185551212);

The table will create successfully. What will result from the INSERT statement execution?

A

It will execute and the table will contain one row of data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
47
Q

A user named SALLY updates some rows, and asks another user MELVIN to login and check the changes before she commits them. Which of the following statements is true about this situation? (Choose the best answer.)

A

MELVIN cannot see SALLY’s updates because she has not entered the COMMIT command.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
48
Q

CREATE TABLE SHIPS (
Ship_ID NUMBER,
Ship_name VARCHAR2(20),
Home_port_id NUMBER(4));

What will be the result of the following DML statement:
INSERT INTO SHIPS(Ship_name, Ship_ID) VALUES (‘Codd Vessel II’, 4001);

A

One row will be inserted with ship_ID having a value of 4001, ship_name having value ‘Codd Vessel II’, and Home_port_id will be NULL.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
49
Q

To delete the data values from one entire column in a table (but not remove the column from the table), you would use the ______ command.

A

UPDATE without a WHERE clause

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
50
Q

SALLY updates some rows but does not commit. MELVIN queries the rows that SALLY updated. Which of the following statements is true? (Choose the best answer.)

A

MELVIN will see the old versions of the rows.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
51
Q

Consider the following table called PARTS:
PNO PART_TITLE STATUS
——- —————– ————
1 Processor V1.0 VALID
2 Encasement X770 PENDING
3 Board CPU XER A7 PENDING
Which of the following SQL statements will remove the word VALID from row 1, resulting in that row with a status of NULL and two rows with a status of PENDING?

A

None of these answers is correct

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
52
Q

The following statement will remove all rows from the vendors table, but leave the table and index structure intact: TRUNCATE TABLE vendors;

A

True

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
53
Q

If you create a SEQUENCE, then you must use it to generate primary key values.

A

False

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
54
Q

Which of the following are true concerning inserting multiple rows at a time? Select 2 correct answers.

A

the source table must already exist

the columns and datatypes of the source and destination tables must correspond

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
55
Q

Which of the following are considered “Transaction control” (select all that apply)

A

SAVEPOINT

COMMIT

ROLLBACK

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
56
Q

Which of the following is not a considered to be DML:

A

COMMIT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
57
Q

The ALTER command is used to make changes to the data in a table.

A

FALSE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
58
Q

If all of the records in a table need to be removed, a TRUNCATE is faster than a DELETE, especially if the table is large and contains many rows.

A

TRUE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
59
Q

An SQL TRUNCATE statement can be undone.

A

FALSE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
60
Q

______ returns the next available number in the sequence.

A

NEXTVAL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
61
Q

What value would successfully complete the statement (one answer):

INSERT INTO employees (emp_id, last_name, hire_date) VALUES (256, ‘Monroe’ …

A

SYSDATE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
62
Q

if a table T3 has four numeric columns (A-D) and no primary key, which of these statements will succeed

A

INSERT INTO T3 VALUES (3, 9, 10, 12);

INSERT INTO T3 VALUES (3,6,7,NULL)

INSERT INTO T3 SELECT * FROM T3;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
63
Q

What will be the result of the following
CREATE TABLE MAILING_LIST … (‘Smith’, ‘Mary’)

A

It will execute sucessfully, create a new table and insert 1 row

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
64
Q

How can you change the primary key value of a row?

A

The UPDATE command may be used, but only if the value being inserted is not a duplicate of any primary key values that already exsist

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
65
Q

Which of the following commands will terminate a transaction

A

COMMIT
ROLLBACK

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
66
Q

A transaction of one or more SQL statements, followed by either a COMMIT or ROLLBACK command

A

True

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
67
Q

What will be the result of executing the following DML statement:

UPDATE employees SET salary = salary +100;

A

Every row in the employees table is updated except for the rows that contain NULL as the salary value

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
68
Q

Consider the following SELECT statement:

SELECT ship_id
FROM ships
WHERE 10 = 5 + 5;

Which of the following is true of this statement?

A

It will execute and return the ship_id for each row in the table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
69
Q

Consider the following table named “ports”:

PORT_ID PORT_NAME CAPACITY
———– ————– ————
1 Galveston 4
2 San Diego 4
3 San Francisco 3
4 Los Angeles 4
5 San Juan 3
6 Grand Cayman 3

Now consider the following SELECT statement:

SELECT *
FROM ports
WHERE port_name LIKE ‘San%’
OR port_name LIKE ‘Grand%’
OR capacity = 3;

How many rows from the data in the table will be returned?

A

4

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
70
Q

ACCT_ID CRUISE_NAME START_DATE END_DATE
1 Hawaii 11-JUL-12 24-JUL-12
2 Hawaii 10-OCT-12 23-OCT-12
3 Mexico 04-OCT-12 17-OCT-12
4 Mexico 06-DEC-12 19-DEC-12

What will be the value of the ACCT_ID for the first row displayed, given the following ORDER BY clause:

ORDER BY cruise_name DESC, start_date;

A

3

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
71
Q

The ACCOUNT table contains these columns:
ACCOUNT_ID NUMBER(12)
NEW_BALANCE NUMBER(7,2)
PREV_BALANCE NUMBER(7,2)
FINANCE_CHARGE NUMBER(7,2)
You need to accomplish these requirements:
1. Display accounts that have a new balance that is less than the previous balance.
2. Display accounts that have a finance charge that is less than $25.00
3. Display accounts have no finance charge.

Which of the 3 requirements will this SELECT statement accomplish?
SELECT account_ID FROM account
WHERE new_balance < prev_balance OR NVL(finance_charge, 0) < 25;

A

All three

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
72
Q

SELECT order_num, ‘&order_date’
FROM Orders
WHERE order_date_placed = ‘&order_date’;

Which statement regarding the execution of this statement is true?

A

The user will be prompted for the order_date twice, each time the statement is executed in a session

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
73
Q

Which of the following are true of the WHERE clause? Select all that apply.

A

The WHERE clause comes after the FROM clause

WHERE identifies which rows are to be included in the result set of the SELECT statement

WHERE may be used by SELECT, UPDATE, and DELETE statements

The WHERE clause is optional

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
74
Q

A substitution variable can be used to replace: (select all that apply)

A

a table name

a column name

a WHERE clause

ORDER BY options

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
75
Q

What does the following command do? Choose all that apply.

ACCEPT vDept_ID PROMPT ‘Enter a department ID code: ‘

A

It pauses until the user enters a value and presses RETURN

It displays the text Enter a department ID code:

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
76
Q

Which of the following will complete this SELECT statement to return EXACTLY 50% of the resulting rows?

SELECT * FROM ORDERS

A

FETCH NEXT 50 PERCENT ROWS ONLY

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
77
Q

Which of the following is equivalent to:

WHERE salary BETWEEN 3500 and 5000

A

WHERE salary >= 3500 AND salary <= 5000

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
78
Q

Which operator returns records that have no value at all?

A

IS NULL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
79
Q

The _______ operator is used to test whether a value falls within a range of two boundary values.

A

BETWEEN

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
80
Q

Which of the following job titles will make the following WHERE clause true? (Select all that apply.)

WHERE job_title = ‘SALES_REP’

A

SALES_REP

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
81
Q

Consider the following WHERE clause:

WHERE ship_name LIKE ‘Viking%’

What is function of the percent sign after the word Viking? Select all that apply.

A

It is the Oracle wildcard symbol

It represents zero of more characters

It means to find all words that start with the word Viking followed by any characters

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
82
Q

If you wanted to sort output by a certain column, you would enter

A

ORDER BY

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
83
Q

If a WHERE clause returns no rows, an error message is displayed.

A

False

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
84
Q

Operators such as AND, OR, and NOT are called _____ operators.

A

Boolean

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
85
Q

What is an alterate way to write the following:

WHERE last_name = ‘Kang’
OR last_name = ‘Kbng’
OR last_name = ‘Kcng’
…
OR last_name = ‘Kzng’

(that is every single letter a - z is inserted into ‘K - ng’)

Select one answer.

A

WHERE last_name LIKE ‘K_ng’

86
Q

THE AND operator connects two conditions, one of which must be satisfied (must be true) in order for the row to be included in the result set.

A

False

87
Q

Single-Row functions may be used in which of the following (Select all that apply):

A

SELECT

WHERE

ORDER BY

88
Q

What would be returned by the following:

SELECT *
FROM countries
WHERE UPPER(country_name) LIKE ‘%U%S%A%’;

A

United States of America

United States

Australia

usa

89
Q

Which one of the following SELECT statements will return 30 as the result?

A

SELECT ROUND(29.01, -1) FROM DUAL;

90
Q

Which of the following functions can be used to determine how many months a book has been available, from the time it is published until today’s date?

A

none of the choices are correct.

91
Q

Which of the following is a valid SQL statement?

A

SELECT TRUNC(ROUND(124.67, 1) ) FROM DUAL;

92
Q

What is the result of:

SELECT INSTR(‘Mississippi’, 3, 2) FROM dual;

A

0

93
Q

SYSDATE = 30-DEC-2012. What is the value returned by the following statement:

SELECT TRUNC(SYSDATE, ‘YEAR’) FROM dual;

A

01-JAN-2012

94
Q

Review this SQL statement:

SELECT MONTHS_BETWEEN(LAST_DAY(‘15-JAN-19’) + 1, ‘01-APR-19’) FROM dual;

What will be the result from the query above?

A

-2

95
Q

What is returned by the following function:

ROUND(‘17-JUN-20’, ‘MM’)

A

01-JUL-20

96
Q

Which of the following function(s) return a number? Choose all that apply.

A

INSTR

MONTHS_BETWEEN

97
Q

What will be returned by INSTR(‘Days worked by employee’, ‘e’, 12, 2)

A

22

98
Q

The result of the following statement is 17:

SELECT TRUNC(174.856, -1)
FROM DUAL;

A

False

99
Q

What is the result of running the following:

SELECT INSTR(‘6^0^5^7’, ‘^’, 3) FROM DUAL;

A

4

100
Q

Which statements regarding single-row functions are true? (choose two)

A

they execute once for each record processed

they may have zero or more input parameters

101
Q

What is returned when the following statement is executed?

SELECT ROUND(15607.329, -1)
FROM DUAL;

A

INSTR-

102
Q

Which of the following will display:

3.14 approximates pi

Select all that apply.

A

SELECT 3.14 || ‘ approximates pi’ FROM DUAL;

SELECT CONCAT(3.14, ‘ approximates pi’) FROM DUAL;

SELECT CONCAT(3 + 0.14, ‘ approximates pi’) FROM DUAL;

103
Q

What is the value of

SELECT LENGTH(‘This is a good day!’) FROM dual;

A

19

104
Q

You are writing a SELECT statement which will be used to print a check. The amount of the check will be displayed as dollars and cents, however, the bank wants the amount to have asterisks (*) filled in to the left of the amount. Which function would you use to accomplish this?

A

LPAD

105
Q

What would be returned by the following:

SELECT *
FROM countries
WHERE UPPER(country_name) LIKE ‘u%s%a%’;

A

no rows returned

106
Q

Choose any statements that are true regarding conversion functions. Choose all that apply.

A

TO_CHAR may convert date items to character items.

TO_DATE may convert character items to date items.

TO_CHAR may convert numbers to character items.

TO_NUMBER may convert character items to numbers.

107
Q

What is returned by the following statement:

SELECT TO_NUMBER(123.56, ‘999.9’) FROM DUAL;

A

An error is returned

108
Q

If today’s date is 12-JULY-19, then what is returned by this statement:

SELECT TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, ‘DD’), ‘DD’), ‘YEAR’)
FROM DUAL;

A

TWO THOUSAND NINETEEN

109
Q

Consider the following data in the LINE_ITEMS table:
LINE_ITEM PRICE
100 4.12
210
184 7.07
What is true of the query: SELECT NVL(price, 10) FROM LINE_ITEMS;

A

It will return three rows, but it will not change the price for line items 100 and 184.

110
Q

What is the result of the execution of the following query:

SELECT NVL(SUBSTR(‘abc’, 4), ‘FLEUR’) FROM DUAL;

A

FLEUR

111
Q

01 SELECT NVL(SHIP_NAME, ‘None’),
02 CASE CAPACITY WHERE 234 THEN 0
03 WHERE 999 THEN 1
04 END
05 FROM SHIPS;

Which of the following statements is true of this SELECT statement?

A

The statement will fail because of syntax errors on lines 2 and 3.

112
Q

Which of the following will display the current time, in hours, minutes, and seconds?

A

SELECT TO_CHAR(SYSDATE, ‘HH:MI:SS’) FROM DUAL;

113
Q

Consider the following statement:

SELECT customer#, state,
DECODE(state, ‘CA’, .08, ‘FL’, .07, .05) “Sales Tax Rate”
FROM Customers
WHERE state IN (‘CA’, ‘FL’, ‘GA’, ‘TX’);

What will be the statement show as the sales tax rate in Georgia?

A

0.05

114
Q

You need to display the day of the week, such as ‘Monday’ or ‘Tuesday’ for a particular date. Which function will provide this information?

A

TO_CHAR

115
Q

Which of these completes the statement correctly:

Conversion functions …

A

Change a value’s data type in an equation to tell SQL to treat the value as that specified data type.

116
Q

Which of the following may be used on character data? (Choose two)

A

COUNT
MIN

117
Q

Review the following columns that are contained in a tabled named Cruise_Orders:

Cruise_Order_ID NUMBER (PRIMARY KEY)
Cruise_Date DATE

What can be said of this SQL statement:

SELECT AVG(Cruise_Order_ID), MIN(Cruise_date)
FROM Cruise_Orders;

A

There is nothing wrong with the statement. It will execute and perform as intended.

118
Q

Review the following data listing from a table SCORES:

SCORE_ID TEST_SCORE
———— ——————
1 95
2
3 85

Now consider the following query:

SELECT TO_CHAR(AVERAGE(TEST_SCORE), ‘999,999.99’)
FROM SCORES;

A

It will result in a syntax error.

119
Q

An aggregate function can be called from: (Choose all that apply.)

A

The ORDER BY clause

the select list

the HAVING clause

120
Q

Which of the following SELECT statements lists the highest retail price of all books in the Family category?

A

SELECT MAX(retail)
FROM books
WHERE category = ‘Family’;

121
Q

Which of the following statements are true about HAVING? (Choose two.)

A

It can be used only in the SELECT statement

It must occur after the WHERE clause.

122
Q

Consider this scheme for the PROJECTS table:

PROJECT_ID NUMBER
SHIP_ID NUMBER
PURPOSE VARCHAR2(30)
PROJECT_NAME VARCHAR2(30)
PROJECT_COST NUMBER
DAYS NUMBER

Your task is to define a SELECT statement that queries the PROJECTS table, to show the average project cost for each PURPOSE. There are only two values for PURPOSE in the table: ‘Upgrade’ or ‘Maintenance’. You want to restrict output to those rows where the DAYS are greater than 3. Which of the following will perform this task?

A

SELECT PURPOSE, AVG(PROJECT_COST)
FROM PROJECTS
WHERE DAYS > 3
GROUP BY PURPOSE;

123
Q

Which of the following functions ignore NULL values in its calculations (Select all that apply.)

A

COUNT

MAX

SUM

AVG

124
Q

Which of the following statement(s) is/are true about group functions? (Select all that apply)

A

The AVG function can be used only with numeric data.

125
Q

Consider the BOOKS table, with columns Retail_Cost and Category. Which one of the following is a valid SELECT statement?

A

None of the choices are correct.

126
Q

What is the result of executing the following

SELECT COUNT() FROM dual;

A

Throws exception “ORA-00909: invalid number of arguments”

127
Q

A Table t_count has one column that contains 12 values: 1, 2, 3, 32, 15, 6, NULL, 8, 12, NULL, 77, NULL. What is the output of:

SELECT COUNT(*) FROM t_count;

A

12

128
Q

All aggregate functions ignore NULL values except

A

COUNT(*)

129
Q

Count used with a column name, returns the number of non-null values in that column (that satisfy the WHERE clause of the SELECT statement).

A

True

130
Q

Which of the following is true about aggregate functions in SQL? (Choose two)

A

They return one value for each group of rows specified, except for the RANK functions

they are also called group functions

131
Q

Which of the following functions can be used on character data?

A

COUNT
MIN

132
Q

Which of the following functions will would return the following data:

Name Score Place
——– ——— ——-
Sally 100 1
Alex 95 2
Maria 95 2
Oscar 90 3

A

DENSE_RANK

133
Q

What group function can be used with any datatype?

A

COUNT

134
Q

Which of the following will provide a number indicating where the value occurs in the list (1st, 2nd, 3rd, etc) of a value as it is ordered by a particular column?

A

RANK

135
Q

Since the name of the function, e.g. MAX, MIN, AVG, etc. appear in the column heading, it is all right not to provide a column alias when that function is used on a column.

A

False

136
Q

Review the following SQL statment (line numbers added):

01 SELECT vendor_id, invoice_date, total_price
02 FROM vendors JOIN invoices
03 USING (vendor_id);

Which of the following is true for the statement?

A

It will execute successfully, if vendor_id is a column in both tables.

137
Q

The syntax diagram for the JOIN USING clause is:

SELECT table1.column, table2.column, .

A

FROM table1 JOIN table2
USING (join_column);

138
Q

Select which clauses below are categorized as “Natural Joins” (Choose all that apply):

A

NATURAL JOIN

JOIN…USING

JOIN…ON

139
Q

The EMPLOYEES and DEPARTMENTS tables have two identically named columns, department_id and manager_id. Which clause(s) join these tables based on both column values? (Choose all that apply.)

A

(A, B, C)
A. SELECT * FROM EMPLOYEES NATURAL JOIN DEPARTMENTS;

B. SELECT * FROM EMPLOYEES JOIN DEPARTMENTS USING (DEPARTMENT_
ID,MANAGER_ID);

C. SELECT * FROM EMPLOYEES E JOIN DEPARTMENTS D ON E.DEPARTMENT_
ID=D.DEPARTMENT_ID AND

140
Q

How many rows will be returned by this SQL query on the Regions table?

SELECT * FROM Regions R1 JOIN Regions R2 ON (R1.Region_ID = LENGTH(R2.Region_name)/2);

Region_ID Region_Name
———— —————
1 Europe
2 Americas
3 Asia
4 Middle East

A

3

141
Q

Qualifying a column reference using dot notation, such as employee.last_name or department.dept_name, have no performance benefit in a query.

A

False

142
Q

Which of the following statements accurately describe the SQL statement below? (Choose two.)

SELECT A.EMPLOYEE_ID, B.POSITION
FROM PAY_HISTORY A JOIN POSITIONS B
ON A.SALARY <= B.MAX_SALARY;

A

It is an inner join.

It is a non-equijoin.

143
Q

What can be said about the following statement (line numbers added):
1 SELECT P.PORT_NAME, S.SHIP_NAME, SC.ROOM_NUMBER
2 FROM PORTS P JOIN SHIPS S
3 ON P.PORT_ID = S.PORT_ID
4 FROM SHIP_CABINS SC
5 ON S.SHIP_ID = SC.SHIP_ID;

A

There is a syntax error on line 4.

144
Q

Joins can connect two, three or more tables.

A

True

145
Q

The general syntax for the NATURAL JOIN clause is as follows:

SELECT table1.column, table2.column
FROM table1
NATURAL JOIN table2;

What condition must be true in order for a natural join to execute without any errors?

A

table1 and table 2 must have at least one column with: (1) the same name and (2) these two columns must have compatible datatypes

146
Q

What must be true in order for a join to happen between two tables?

A

they must be related

147
Q

One of the categories of JOINS is OUTER join.

A

TRUE

148
Q

Table aliases are necessary to eliminate ambiguity in referring to columns in different tables in a join operation; and will help the query run faster.

A

TRUE

149
Q

Oracle imposes a rule stating that the join columns in two distinct tables must have a primary key - foreign key relationship.

A

FALSE

150
Q

Which is the most flexible way of performing a natural join regardless of the column names?

A

JOIN…ON

151
Q

WHich of the following are NATURAL JOIN formats? Select all that apply.

A

JOIN…ON

JOIN…USING

NATURAL JOIN

152
Q

A table alias: (Choose three)

A

Makes for simpler and cleaner code

Exists only for the SQL statement that declared it.

Is a short-hand way of referring to a table

153
Q

Which keyword is optional when doing NATURAL JOIN or JOIN… ON?

A

INNER

154
Q

What operator would you choose to prevent this Oracle error message:

ORA-01427: single-row subquery returns more than one row

A

IN

155
Q

Which comparison operator(s) can be used with multiple-row subqueries (select all that apply):

A

IN ANY ALL

156
Q

A subquery must be placed in the outer query’s HAVING clause if:

A

The value returned by the inner query is to be compared to grouped data in the outer query.

157
Q

Which query identifies customer(s) living in the same state as the customer named LISA SMITH?

A

SELECT customer# FROM customers
WHERE state IN (SELECT state
FROM customers
WHERE firstname = ‘LISA’
AND lastname = ‘SMITH’
);

158
Q

When will the subquery be executed in the following statement:

SELECT o.customer_id, o.customer_Name
FROM customers o
WHERE o.credit_limit > (SELECT AVG(i.credit_limit)
FROM customers i
WHERE o.category = i.category);

A

It will be executed once for every candidate row in the main query

159
Q

Which type of subquery will execute once for each value in the result set of the outer (main) query?

A

correlated subquery

160
Q

Which one of the following operators is considered a single-row operation?

A

<>

161
Q

What is wrong with the following statement:

SELECT last_name, employee_id
FROM employees
WHERE salary = (SELECT salary FROM employees);

A

The statement will fail if the subquery returns more than one value.

162
Q

A subquery that includes references back to the parent (or main) query, and thus cannot execute as a standalone query is a ____ subquery.

A

correlated

163
Q

What is the purpose of the following query?

SELECT title FROM books
WHERE (pub_id, category) IN
(SELECT pub_id, category
FROM books
WHERE title LIKE ‘%ORACLE%’);

A

It will be executed prior to the main query

164
Q

What will happen when you execute the following?

SELECT title, retail
FROM books
WHERE retail = (SELECT MAX(retail) FROM books);

A

It will run and select any books that have the highest retail price.

165
Q

A subquery may appear in which of the following SQL statements? Select all that apply.

A

CREATE VIEW

INSERT

166
Q

A subquery may be nested within which of the following clauses of an SQL SELECT statement? Select all that apply.

A

FROM

HAVING

167
Q

When a connection exists between the main query and the subquery, it is referrer to as a _____ subquery.

A

correlated

168
Q

Which one of the subqueries can typically be used with one of the comparison operators, such as =. <=, etc.

A

none of the above

169
Q

How do you know when you need a subquery?

A

When you reach a point where you need to look up something manually in a table

170
Q

A subquery must be enclosed in parenthesis or you will get a “missing expression” error when you try to run the outer query.

A

TRUE

171
Q

The following query has no errors:

SELECT last_name, job_id, salary
FROM employees
WHERE salary >= (SELECT salary
FROM employees
WHERE employee_id = 150
ORDER BY 1)
ORDER BY last_name;

A

FALSE

172
Q

A subquery may return exactly one row of data or a set of data values.

A

TRUE

173
Q

Which of the following is(are) database object(s)?

A

VIEW, SEQUENCE, INDEX

174
Q

Which of the following statements are true concerning Oracle indexes. Select all that apply.

A

The purpose of an index on a column is to speed up a query that uses that column

An index is stored in a file separate from the table it indexes

175
Q

A sequence is

A

none of the above

176
Q

Which of these is a characteristic of a complex view, rather than a simple view? (Choose all that apply.)

A

MAX, MIN, SUM, or COUNT in the SELECT clause

A subquery within the SELECT statement

Join of two or more tables

177
Q

Which of the following are legitimate reasons for using views in a database application? (Choose all that are legitimate.)

A

To provide an additional level of security.

To simplify complex queries.

To prevent access to sensitive data by some users.

178
Q

For which of the following, will Oracle automatically create an index?

A

primary key column

column defined as UNIQUE

179
Q

Which of the following statements will define a new index on the Op_key column of the CodeOps table?

A

CREATE INDEX CodeOps_Op_key_idx ON CodeOps(Op_Key);

180
Q

The term meta-data means

A

data about data

181
Q

One place to get a master list of all of the views that form the data dictionary is:

A

DICTIONARY

182
Q

Which of the following will not cause the contents of the data dictionary to be changed in some way?

A

none of the above

183
Q

How do you retrieve data from a view named OrderlineView?

A

SELECT * FROM OrderlineView;

184
Q

What is true of the following code to make a new view?

A

There is an error on line 02 because the 2nd column must have an alias

185
Q

How many tables can be part of a simple view?

A

1

186
Q

Which of the following are characteristics of a complex view. Select all that apply.

A

Subqueries

Group functions

JOINS

187
Q

All database data is stored in:

A

TABLES

188
Q

It is always possible to update the underlying table(s) through every view, whether it be a simple view or a complex view.

A

FALSE

189
Q

One advantage of using views is that different views of the same data can be presented to different users.

A

TRUE

190
Q

A view is a SELECT statement that is stored in the database and has a name, and is accessible as though it were a table.

A

TRUE

191
Q

What is true of the following code to make a new view?

01 SELECT OR REPLACE employee_view_phone AS
02 SELECT employee_id, last_name, phone_number
03 FROM employees;

A

there is an error on line 01 because the keyword SELECT should be CREATE

192
Q

You are tasked with cleaning up a database application. There are two tables in the database: ORDERS contains completed orders, and ORDER_RETURNS contains duplicate information for any orders that were returned. Your goal is to find out if there are any rows in ORDER_RETURNS that are not in the ORDERS table. Which of the following set operators should you use?

A

minus

193
Q

When combining two SELECT statements, which of the following set operators will produce a different result, depending on which SELECT statement precedes or follows the operator? (Choose two.)

A

MINUS, UNION ALL

194
Q

How many rows will results from the following query:

SELECT NUM, PRODUCT FROM STORE_INVENTORY
INTERSECT
SELECT CAT#, ITEM_NAME FROM FURNISHINGS;

A

0

195
Q

How many rows will results from the following query:

SELECT TO_CHAR(LAST_ORDER, ‘Month’), AISLE
FROM STORE_INVENTORY
UNION ALL
SELECT ‘–’, SECTION
FROM FURNISHINGS
WHERE CAT# NOT IN (1, 2);

A

3

196
Q

What will result from the following query:

SELECT LAST_ORDER
FROM STORE_INVENTORY
ORDER BY 1
UNION
SELECT ADDED
FROM FURNISHINGS;

A

It will fail with a syntax error because you cannot use an ORDER BY clause in this context.

197
Q

(SELECT PRODUCT FROM STORE_INVENTORY
UNION ALL SELECT ITEM_NAME FROM FURNISHINGS)
INTERSECT
(SELECT ITEM_NAME FROM FURNISHINGS WHERE Cat# = 3
UNION ALL SELECT PRODUCT FROM STORE_INVENTORY WHERE num = 78);

A

This statement will return 1 row.

198
Q

What is true about 2 queries that are UNION-COMPATIBLE. Select all that apply.

A

The number of columns in each query must be the same

the datatypes of corresponding columns must be compatible

199
Q

Which is the correct syntax for doing a UNION?

A

None of the choices are correct

200
Q

The MERGE statement includes a USING clause. Which of the following statements is not true of the USING clause?

A

it is optional

201
Q

CREATE ROLE MANAGER;
GRANT DELETE ON FURNISHINGS TO MANAGER;
GRANT MANAGER TO LEBLANC;

Which of the following will change the privileges so that LEBLANC no longer can execute DELETE statements on the FURNISHINGS table?

A

REVOKE DELETE ON FURNISHINGS FROM LEBLANC;

202
Q

Object privileges correspond to DDL and DML statements that are relevant to existing objects.

A

TRUE

203
Q

Which of these operators will remove duplicate rows from the final result? (choose all that apply.)

A

UNION

MINUS

INTERSECT

204
Q

Which set operator returns the rows from the first query that do not exist in the second query?

A

MINUS

205
Q

It is possible to use ORDER BY in the individual queries that make a compound query, i.e. one that contains UNION, INTERSECT, or MINUS.

A

FALSE

206
Q

Which of the following set operators can be used to return the rows that are common to two queries?

A

INTERSECT

207
Q

The two queries in a UNION query must return the same number of rows.

A

False

208
Q

Set operators combine two SELECT statements, each of which may contain:

A

joins

subqueries

WHERE clause

209
Q

There is no need to put column aliases on the second SELECT in a compound query; the column headings will be taken from the first SELECT statement.

A

TRUE

210
Q

Which of the following operators will not remove duplicate rows?

A

UNION ALL

211
Q

What are the purpose of the words ‘Member’ and ‘Officer’ in the following:

SELECT m_name, m_address, m_city, ‘Member’
FROM member
UNION
SELECT o_lastname, o_street_address, o_city, ‘Officer’
FROM officer;

A

They will identify the type of each person

212
Q

The following two SELECT statements are union-compatible:

SELECT m_name, m_address, m_city, ‘Member’
FROM member;

SELECT o_lastname, o_street_address, o_city, o_state, ‘Officer’
FROM officer;

A

FALSE

Databases (1 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions