db final Flashcards
Which of the following Oracle tools can be used to execute SQL statements against a database?
SQL Developer
SQL*Plus command line
SQL Live
The relational model consists of which of the following. Choose all that are true:
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
What are a primary key’s identifying characteristics? (Select all that apply.)
It uniquely identifies each row
it cannot be NULL
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;
4
The SHIPS table has two columns: SHIP_ID and SHIP_NAME
Which of the following SELECT statements will produce a syntax error? (Choose two.)
SELECT FROM ships;
SELECT (ship_id, ship_name) FROM ships;
Which of the following is NOT required to form a syntactically correct SELECT statement?
a valid column name
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;
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)
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;
02
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;
‘ ‘
‘-’
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.)
SELECT * FROM PORTS;
SELECT PORT_NAME, PORT_ID FROM PORTS;
SELECT ‘Name of the port ‘ || PORT_NAME FROM PORTS;
To become an Oracle SQL Certified Associate, one must pass one exam, 1Z0-071.
True
Which is the only type of relationship that can be directly implemented by the relational model?
1-Many
The category of SQL commands used to work with the actual data, such as INSERT, UPDATE, and DELETE, is called _____.
Data Manipulation Language (DML)
Which of the following are Oracle Development Environments?
SQL Live
SQL*Plus command line
SQL Developer
A table consists of (choose the single best answer)
Rows and columns, primary key, and possibly foreign keys
A primary key may contain NULL values.
False
The category of SQL commands used to build database objects, such as CREATE, ALTER, and DROP, is called ________.
Data Definition Language (DDL)
The relational model consists of which of the following. Choose all that are true:
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
What is the number one DBMS used worldwide, according to DB-Engines Ranking?
Oracle
A ______ is the intersection of a row and a column.
field
The UNIQUE keyword in the SELECT clause will eliminate duplicate values in the result set
False
What will be the result of the executing the following SELECT statement? SELECT ENGINE_NAME FROM ENGINES; SELECT
It will display engine names from all of the rows in the ENGINES table … (NULL will display as blank)
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)?
port_name VARCHAR2(20) NOT NULL
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.)
ALTER TABLE invoices MODIFY discount VARCHAR2(3);
ALTER TABLE invoices MODIFY discount DEFAULT ‘ZERO’;
Which of the following are valid CREATE TABLE statements? (Choose all that are valid.)
CREATE TABLE “Boat Inventory”
(ID NUMBER,
CAPTAIN VARCHAR2(20));
CREATE TABLE Work_Schedule (ID NUMBER);
Which of the following would be an invalid column or table name? Select all that apply.
P!nk
21PILOTS
Lady-Gaga
What would happen when the following statement is executed:
CREATE TABLE GGC_Student
( StudentID CHAR(6),
SName VarChar2(40),
gpa Number(5,4)
);
The table will be created without a primary key
Which of the following data type(s) is(are) supported by Oracle?
NCLOB
NUMBER
CHAR
Which two constraints are combined to implement a primary key?
UNIQUE
NOT NULL
The difference between dropping a column from a table with DROP and setting the column to UNUSED is:
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
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?
the statement will fail because you cannot create two primary key constraints on one table
Which of the following options can be used with the keyword CREATE to form a syntactically correct SQL statement to create a table?
at least one column-definition
the keyword CONSTRAINT
the keyword TABLE
What component of SQL is used to build and manage the database objects and define the structure of the database?
DDL
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));
02
03
Which of the following are valid table names that may be used with the CREATE TABLE command? Choose all that apply.
Order_lines
“Boat Inventory”
Retired#Emps
The data type that would be used for a column that will be storing employee photographs for a large, multi-national company is:
BLOB
Which of the following is the recommended constraint name for the SHIPS table having Primary key SHIP_ID?
Ships_Ship_ID_PK
The DESCRIBE command, (DESC) can be used to show which of the following?
a table’s structure, that is, its columns and data types
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) _______.
schema
Which of the following are true for ‘RR’ year designation?
25 means 2025
45 means 2045
68 means 1968
The default time for a date is:
12:00 a.m.
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;
the ADDRESS table will have no rows.
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.
INSERT INTO T3 VALUES (3, 6, 7, NULL);
INSERT INTO T3 VALUES (‘3’, ‘9’, ‘10’, ‘12’);
INSERT INTO T3 SELECT * FROM T3;
What is the result of:
UPDATE cruises
SET cruise_name = ‘Bahamas’, SET start_date = SYSDATE
WHERE cruise_id = 1;
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
Which of these commands will remove every row in a table, but not delete the table itself? Choose one or more answers.
A DELETE command with no WHERE clause
A TRUNCATE command
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?
It will execute and the table will contain one row of data.
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.)
MELVIN cannot see SALLY’s updates because she has not entered the COMMIT command.
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);
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.
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.
UPDATE without a WHERE clause
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.)
MELVIN will see the old versions of the rows.
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?
None of these answers is correct
The following statement will remove all rows from the vendors table, but leave the table and index structure intact: TRUNCATE TABLE vendors;
True
If you create a SEQUENCE, then you must use it to generate primary key values.
False
Which of the following are true concerning inserting multiple rows at a time? Select 2 correct answers.
the source table must already exist
the columns and datatypes of the source and destination tables must correspond
Which of the following are considered “Transaction control” (select all that apply)
SAVEPOINT
COMMIT
ROLLBACK
Which of the following is not a considered to be DML:
COMMIT
The ALTER command is used to make changes to the data in a table.
FALSE
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.
TRUE
An SQL TRUNCATE statement can be undone.
FALSE
______ returns the next available number in the sequence.
NEXTVAL
What value would successfully complete the statement (one answer):
INSERT INTO employees (emp_id, last_name, hire_date) VALUES (256, ‘Monroe’ …
SYSDATE
if a table T3 has four numeric columns (A-D) and no primary key, which of these statements will succeed
INSERT INTO T3 VALUES (3, 9, 10, 12);
INSERT INTO T3 VALUES (3,6,7,NULL)
INSERT INTO T3 SELECT * FROM T3;
What will be the result of the following
CREATE TABLE MAILING_LIST … (‘Smith’, ‘Mary’)
It will execute sucessfully, create a new table and insert 1 row
How can you change the primary key value of a row?
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
Which of the following commands will terminate a transaction
COMMIT
ROLLBACK
A transaction of one or more SQL statements, followed by either a COMMIT or ROLLBACK command
True
What will be the result of executing the following DML statement:
UPDATE employees SET salary = salary +100;
Every row in the employees table is updated except for the rows that contain NULL as the salary value
Consider the following SELECT statement:
SELECT ship_id
FROM ships
WHERE 10 = 5 + 5;
Which of the following is true of this statement?
It will execute and return the ship_id for each row in the table
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?
4
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;
3
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;
All three
SELECT order_num, ‘&order_date’
FROM Orders
WHERE order_date_placed = ‘&order_date’;
Which statement regarding the execution of this statement is true?
The user will be prompted for the order_date twice, each time the statement is executed in a session
Which of the following are true of the WHERE clause? Select all that apply.
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
A substitution variable can be used to replace: (select all that apply)
a table name
a column name
a WHERE clause
ORDER BY options
What does the following command do? Choose all that apply.
ACCEPT vDept_ID PROMPT ‘Enter a department ID code: ‘
It pauses until the user enters a value and presses RETURN
It displays the text Enter a department ID code:
Which of the following will complete this SELECT statement to return EXACTLY 50% of the resulting rows?
SELECT * FROM ORDERS
FETCH NEXT 50 PERCENT ROWS ONLY
Which of the following is equivalent to:
WHERE salary BETWEEN 3500 and 5000
WHERE salary >= 3500 AND salary <= 5000
Which operator returns records that have no value at all?
IS NULL
The _______ operator is used to test whether a value falls within a range of two boundary values.
BETWEEN
Which of the following job titles will make the following WHERE clause true? (Select all that apply.)
WHERE job_title = ‘SALES_REP’
SALES_REP
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.
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
If you wanted to sort output by a certain column, you would enter
ORDER BY
If a WHERE clause returns no rows, an error message is displayed.
False
Operators such as AND, OR, and NOT are called _____ operators.
Boolean
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.
WHERE last_name LIKE ‘K_ng’
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.
False
Single-Row functions may be used in which of the following (Select all that apply):
SELECT
WHERE
ORDER BY
What would be returned by the following:
SELECT *
FROM countries
WHERE UPPER(country_name) LIKE ‘%U%S%A%’;
United States of America
United States
Australia
usa
Which one of the following SELECT statements will return 30 as the result?
SELECT ROUND(29.01, -1) FROM DUAL;
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?
none of the choices are correct.
Which of the following is a valid SQL statement?
SELECT TRUNC(ROUND(124.67, 1) ) FROM DUAL;
What is the result of:
SELECT INSTR(‘Mississippi’, 3, 2) FROM dual;
0
SYSDATE = 30-DEC-2012. What is the value returned by the following statement:
SELECT TRUNC(SYSDATE, ‘YEAR’) FROM dual;
01-JAN-2012
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?
-2
What is returned by the following function:
ROUND(‘17-JUN-20’, ‘MM’)
01-JUL-20
Which of the following function(s) return a number? Choose all that apply.
INSTR
MONTHS_BETWEEN
What will be returned by INSTR(‘Days worked by employee’, ‘e’, 12, 2)
22
The result of the following statement is 17:
SELECT TRUNC(174.856, -1)
FROM DUAL;
False
What is the result of running the following:
SELECT INSTR(‘6^0^5^7’, ‘^’, 3) FROM DUAL;
4
Which statements regarding single-row functions are true? (choose two)
they execute once for each record processed
they may have zero or more input parameters
What is returned when the following statement is executed?
SELECT ROUND(15607.329, -1)
FROM DUAL;
INSTR-
Which of the following will display:
3.14 approximates pi
Select all that apply.
SELECT 3.14 || ‘ approximates pi’ FROM DUAL;
SELECT CONCAT(3.14, ‘ approximates pi’) FROM DUAL;
SELECT CONCAT(3 + 0.14, ‘ approximates pi’) FROM DUAL;
What is the value of
SELECT LENGTH(‘This is a good day!’) FROM dual;
19
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?
LPAD
What would be returned by the following:
SELECT *
FROM countries
WHERE UPPER(country_name) LIKE ‘u%s%a%’;
no rows returned
Choose any statements that are true regarding conversion functions. Choose all that apply.
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.
What is returned by the following statement:
SELECT TO_NUMBER(123.56, ‘999.9’) FROM DUAL;
An error is returned
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;
TWO THOUSAND NINETEEN
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;
It will return three rows, but it will not change the price for line items 100 and 184.
What is the result of the execution of the following query:
SELECT NVL(SUBSTR(‘abc’, 4), ‘FLEUR’) FROM DUAL;
FLEUR
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?
The statement will fail because of syntax errors on lines 2 and 3.
Which of the following will display the current time, in hours, minutes, and seconds?
SELECT TO_CHAR(SYSDATE, ‘HH:MI:SS’) FROM DUAL;
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?
0.05
You need to display the day of the week, such as ‘Monday’ or ‘Tuesday’ for a particular date. Which function will provide this information?
TO_CHAR
Which of these completes the statement correctly:
Conversion functions …
Change a value’s data type in an equation to tell SQL to treat the value as that specified data type.
Which of the following may be used on character data? (Choose two)
COUNT
MIN
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;
There is nothing wrong with the statement. It will execute and perform as intended.
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;
It will result in a syntax error.
An aggregate function can be called from: (Choose all that apply.)
The ORDER BY clause
the select list
the HAVING clause
Which of the following SELECT statements lists the highest retail price of all books in the Family category?
SELECT MAX(retail)
FROM books
WHERE category = ‘Family’;
Which of the following statements are true about HAVING? (Choose two.)
It can be used only in the SELECT statement
It must occur after the WHERE clause.
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?
SELECT PURPOSE, AVG(PROJECT_COST)
FROM PROJECTS
WHERE DAYS > 3
GROUP BY PURPOSE;
Which of the following functions ignore NULL values in its calculations (Select all that apply.)
COUNT
MAX
SUM
AVG
Which of the following statement(s) is/are true about group functions? (Select all that apply)
The AVG function can be used only with numeric data.
Consider the BOOKS table, with columns Retail_Cost and Category. Which one of the following is a valid SELECT statement?
None of the choices are correct.
What is the result of executing the following
SELECT COUNT() FROM dual;
Throws exception “ORA-00909: invalid number of arguments”
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;
12
All aggregate functions ignore NULL values except
COUNT(*)
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).
True
Which of the following is true about aggregate functions in SQL? (Choose two)
They return one value for each group of rows specified, except for the RANK functions
they are also called group functions
Which of the following functions can be used on character data?
COUNT
MIN
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
DENSE_RANK
What group function can be used with any datatype?
COUNT
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?
RANK
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.
False
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?
It will execute successfully, if vendor_id is a column in both tables.
The syntax diagram for the JOIN USING clause is:
SELECT table1.column, table2.column, .
FROM table1 JOIN table2
USING (join_column);
Select which clauses below are categorized as “Natural Joins” (Choose all that apply):
NATURAL JOIN
JOIN…USING
JOIN…ON
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, 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
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
3
Qualifying a column reference using dot notation, such as employee.last_name or department.dept_name, have no performance benefit in a query.
False
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;
It is an inner join.
It is a non-equijoin.
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;
There is a syntax error on line 4.
Joins can connect two, three or more tables.
True
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?
table1 and table 2 must have at least one column with: (1) the same name and (2) these two columns must have compatible datatypes
What must be true in order for a join to happen between two tables?
they must be related
One of the categories of JOINS is OUTER join.
TRUE
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.
TRUE
Oracle imposes a rule stating that the join columns in two distinct tables must have a primary key - foreign key relationship.
FALSE
Which is the most flexible way of performing a natural join regardless of the column names?
JOIN…ON
WHich of the following are NATURAL JOIN formats? Select all that apply.
JOIN…ON
JOIN…USING
NATURAL JOIN
A table alias: (Choose three)
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
Which keyword is optional when doing NATURAL JOIN or JOIN… ON?
INNER
What operator would you choose to prevent this Oracle error message:
ORA-01427: single-row subquery returns more than one row
IN
Which comparison operator(s) can be used with multiple-row subqueries (select all that apply):
IN ANY ALL
A subquery must be placed in the outer query’s HAVING clause if:
The value returned by the inner query is to be compared to grouped data in the outer query.
Which query identifies customer(s) living in the same state as the customer named LISA SMITH?
SELECT customer# FROM customers
WHERE state IN (SELECT state
FROM customers
WHERE firstname = ‘LISA’
AND lastname = ‘SMITH’
);
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);
It will be executed once for every candidate row in the main query
Which type of subquery will execute once for each value in the result set of the outer (main) query?
correlated subquery
Which one of the following operators is considered a single-row operation?
<>
What is wrong with the following statement:
SELECT last_name, employee_id
FROM employees
WHERE salary = (SELECT salary FROM employees);
The statement will fail if the subquery returns more than one value.
A subquery that includes references back to the parent (or main) query, and thus cannot execute as a standalone query is a ____ subquery.
correlated
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%’);
It will be executed prior to the main query
What will happen when you execute the following?
SELECT title, retail
FROM books
WHERE retail = (SELECT MAX(retail) FROM books);
It will run and select any books that have the highest retail price.
A subquery may appear in which of the following SQL statements? Select all that apply.
CREATE VIEW
INSERT
A subquery may be nested within which of the following clauses of an SQL SELECT statement? Select all that apply.
FROM
HAVING
When a connection exists between the main query and the subquery, it is referrer to as a _____ subquery.
correlated
Which one of the subqueries can typically be used with one of the comparison operators, such as =. <=, etc.
none of the above
How do you know when you need a subquery?
When you reach a point where you need to look up something manually in a table
A subquery must be enclosed in parenthesis or you will get a “missing expression” error when you try to run the outer query.
TRUE
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;
FALSE
A subquery may return exactly one row of data or a set of data values.
TRUE
Which of the following is(are) database object(s)?
VIEW, SEQUENCE, INDEX
Which of the following statements are true concerning Oracle indexes. Select all that apply.
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
A sequence is
none of the above
Which of these is a characteristic of a complex view, rather than a simple view? (Choose all that apply.)
MAX, MIN, SUM, or COUNT in the SELECT clause
A subquery within the SELECT statement
Join of two or more tables
Which of the following are legitimate reasons for using views in a database application? (Choose all that are legitimate.)
To provide an additional level of security.
To simplify complex queries.
To prevent access to sensitive data by some users.
For which of the following, will Oracle automatically create an index?
primary key column
column defined as UNIQUE
Which of the following statements will define a new index on the Op_key column of the CodeOps table?
CREATE INDEX CodeOps_Op_key_idx ON CodeOps(Op_Key);
The term meta-data means
data about data
One place to get a master list of all of the views that form the data dictionary is:
DICTIONARY
Which of the following will not cause the contents of the data dictionary to be changed in some way?
none of the above
How do you retrieve data from a view named OrderlineView?
SELECT * FROM OrderlineView;
What is true of the following code to make a new view?
There is an error on line 02 because the 2nd column must have an alias
How many tables can be part of a simple view?
1
Which of the following are characteristics of a complex view. Select all that apply.
Subqueries
Group functions
JOINS
All database data is stored in:
TABLES
It is always possible to update the underlying table(s) through every view, whether it be a simple view or a complex view.
FALSE
One advantage of using views is that different views of the same data can be presented to different users.
TRUE
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.
TRUE
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;
there is an error on line 01 because the keyword SELECT should be CREATE
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?
minus
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.)
MINUS, UNION ALL
How many rows will results from the following query:
SELECT NUM, PRODUCT FROM STORE_INVENTORY
INTERSECT
SELECT CAT#, ITEM_NAME FROM FURNISHINGS;
0
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);
3
What will result from the following query:
SELECT LAST_ORDER
FROM STORE_INVENTORY
ORDER BY 1
UNION
SELECT ADDED
FROM FURNISHINGS;
It will fail with a syntax error because you cannot use an ORDER BY clause in this context.
(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);
This statement will return 1 row.
What is true about 2 queries that are UNION-COMPATIBLE. Select all that apply.
The number of columns in each query must be the same
the datatypes of corresponding columns must be compatible
Which is the correct syntax for doing a UNION?
None of the choices are correct
The MERGE statement includes a USING clause. Which of the following statements is not true of the USING clause?
it is optional
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?
REVOKE DELETE ON FURNISHINGS FROM LEBLANC;
Object privileges correspond to DDL and DML statements that are relevant to existing objects.
TRUE
Which of these operators will remove duplicate rows from the final result? (choose all that apply.)
UNION
MINUS
INTERSECT
Which set operator returns the rows from the first query that do not exist in the second query?
MINUS
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.
FALSE
Which of the following set operators can be used to return the rows that are common to two queries?
INTERSECT
The two queries in a UNION query must return the same number of rows.
False
Set operators combine two SELECT statements, each of which may contain:
joins
subqueries
WHERE clause
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.
TRUE
Which of the following operators will not remove duplicate rows?
UNION ALL
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;
They will identify the type of each person
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;
FALSE
