Database Exam 1 review
Which of the following are true for 'RR' year designation? 50 can either mean 1950 or 2050 25 means 2025 68 means 1968 45 means 2045
25 means 2025 68 means 1968 45 means 2045
The range for n is 0 - 9. If the value for n is not specified, the default value is ___ 3 4 5 6
6
Which of the following will complete this SELECT statement to return EXACTLY 50% of the resulting rows? SELECT * FROM ORDERS FETCH NEXT 50 ROWS ONLY FETCH NEXT 50% ROWS ONLY FETCH NEXT 50 PERCENT ROWS ONLY FETCH NEXT 50 PERCENT ROWS WITH TIES
FETCH NEXT 50 PERCENT ROWS ONLY
What is the result of: UPDATE cruises SET cruise_name = 'Bahamas', SET start_date = SYSDATE WHERE cruise_id = 1; An error will occur because two columns cannot be updated with one UPDATE For the first record 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 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
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 operator returns records that have no value at all? NVL IS NULL =NULL IS NOT NULL
IS NULL
____ returns the next available number in the sequence. GET_NUM AVAIL_VAL NEXTVAL
NEXTVAL
Which of the following are valid table names that may be used with the CREATE TABLE command? Choose all that apply. $Customers Order_lines "Boat Inventory" Retired#Emps Financial_Categories_No_longer_active
Order_lines "Boat Inventory" Retired#Emps
Which of the following would be an invalid column or table name? Select all that apply. P!nk 21PILOTS BrunoMar$ Lady-Gaga
P!nk 21PILOTS Lady-Gaga
What value would successfully complete the statement (one answer): INSERT INTO employees (emp_id, last_name, hire_date) VALUES (256, 'Monroe' ... '11/2/2015' SYSDATE 'November 2, 2015'
SYSDATE
CLOB and NCLOB are for character data T/F
T
the IN operator means that rows will be restricted to those enumerated in the list. T/F
T
Which two constraints are combined to implement a primary key? UNIQUE NOT NULL FOREIGN KEY
UNIQUE NOT NULL
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; one two none of them all three
all three
A ______ is the intersection of a row and a column. field entity datum
field
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 (port_name) port_name VARCHAR2(20),CONSTRAINT ports_port_name_nn NOT NULL (port_name) port_name VARCHAR2(20) NOT NULL
port_name VARCHAR2(20) NOT NULL
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; ADDRESS will have 1 row with a value of 1 for ZONE. ADDRESS will have 1 row with a value of 2 for ZONE. the ADDRESS table will have no rows.
the ADDRESS table will have no rows.
Which of the following options can be used with the keyword CREATE to form a syntactically correct SQL statement to create a table? the keyword TABLE the keyword CONSTRAINT the keyword SEQUENCE at least one column-definition
the keyword TABLE the keyword CONSTRAINT at least one column-definition
Which of the following are true concerning inserting multiple rows at a time? Select 2 correct answers. the source table must already exist the destination table must be empty This version of the INSERT statement has an implicit COMMIT the columns and datatypes of the source and destination tables must correspond
the source table must already exist the columns and datatypes of the source and destination tables must correspond
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; "Purchase" ' ' '-' ||
' ' '-'
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", phone03 FROM publisher; 01 02 03
02
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)); 01 02 03 04 05
02 03
Which is the only type of relationship that can be directly implemented by the relational model? all of the choices can be directly implemented Many-many 1-1 1-Many
1-Many
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; 1 2 3
3
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? 3.1415 3.142
3.142
Consider the following rows in a table called CUSTOMERS: CUST_ID FIRST_NAME MIDDLE LAST_NAME 1 Bianca M. Canale 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; 3 4 5
4
Consider the following table named "ports": PORT_ID PORT_NAME CAPACITY 1 Galveston 4 2 San Diego 4 3 San Franciso 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? 1 3 4
4
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 DROP TABLE command A TRUNCATE command UPDATE command, setting every column to NULL with no WHERE clause.
A DELETE command with no WHERE clause A TRUNCATE command
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 PRIMARY KEY; ALTER TABLE invoices MODIFY discount VARCHAR2(3); ALTER TABLE invoices MODIFY discount DEFAULT 'ZERO'; ALTER TABLE invoices MODIFY inv_id PRIMARY KEY;
ALTER TABLE invoices MODIFY discount VARCHAR2(3); ALTER TABLE invoices MODIFY discount DEFAULT 'ZERO';
The _______ operator is used to test whether a value falls within a range of two boundary values. BETWEEN <= IN >=
BETWEEN
he data type that would be used for a column that will be storing employee photographs for a large, multi-national company is: CLOB GLOB NCLOB BLOB
BLOB
Which of the following is not a considered to be DML: INSERT DELETE COMMIT UPDATE
COMMIT
Which of the following are considered "Transaction control" (select all that apply) UPDATE COMMIT ROLLBACK DELETE SAVEPOINT
COMMIT ROLLBACK SAVEPOINT
Which of the following are valid CREATE TABLE statements? (Choose all that are valid.) CREATE TABLE WORK_ORDERS (ID NUMBE); CREATE TABLE "Boat Inventory" (ID NUMBER, CAPTAIN VARCHAR2(20)); CREATE TABLE Work_Schedule (ID NUMBER);
CREATE TABLE "Boat Inventory" (ID NUMBER, CAPTAIN VARCHAR2(20)); CREATE TABLE Work_Schedule (ID NUMBER);
What component of SQL is used to build and manage the database objects and define the structure of the database? DLD the schema DML DDL
DDL
A ______ provides a value to be inserted when one is not explicitly given in the INSERT statement. SAVEMARK PLACE HOLDER DEFAULT SAVEPOINT
DEFAULT
Which of the following are NOT part of SQL? Select all that apply. if..then..else selection COMMIT ROLLBACK DO...WHILE loop FOR...NEXT loop
DO...WHILE loop FOR...NEXT loop
The category of SQL commands used to build database objects, such as CREATE, ALTER, and DROP, is called ________. Transaction Control Data Definition Language (DDL) Data Manipulation Language (DML)
Data Definition Language (DDL)
The category of SQL commands used to work with the actual data, such as INSERT, UPDATE, and DELETE, is called _____. Data Definition Language (DDL) Data Manipulation Language (DML) Transaction Control
Data Manipulation Language (DML)
A primary key may contain NULL values. T/F
F
An SQL TRUNCATE statement can be undone. T/F
F
If a WHERE clause returns no rows, an error message is displayed T/F
F
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. T/F
F
The ALTER command is used to make changes to the data in a table. T/F
F
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; NONE OF THESE INSERT STATEMENTS WILL SUCCEED.
INSERT INTO T3 VALUES (3, 6, 7, NULL); INSERT INTO T3 VALUES ('3', '9', '10', '12'); INSERT INTO T3 SELECT * FROM T3;
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 means to find all names that have the word Viking followed by the character '%' It means to find all words that start with the word Viking followed by any characters It represents zero of more characters
It is the Oracle wildcard symbol It means to find all words that start with the word Viking followed by any characters It represents zero of more characters
What is the purpose of this Boolean Expression: Hotel_Name IN ('Hilton', 'Hampton', 'Best Western', 'Marriot') It's not written correctly and will cause an error. It means rows are restricted to those 4 hotels It means to find all rooms in those hotels It means that the user will be prompted to enter a specific hotel
It means rows are restricted to those 4 hotels
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 causes an error because there is no ampersand (&) in front of vDept_ID It displays the text 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:
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 no rows It will produce a syntax error because the WHERE clause does not refer to any columns in the table It will execute and return the ship_id for each row in the table
It will execute and return the ship_id for each row in the table
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 succesfully. What will result from the INSERT statement execution? It will fail because there is no column listing It will fail because the value for STUDENT_PHONE is numeric, and STUDENT_PHONE is a character datatype. It will execute and the table will contain one row of data.
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 can see SALLY's changes but cannot alter them until SALLY commits. MELVIN cannot see SALLY's updates because she has not entered the COMMIT command. SALLY must commit the updates, so that MELVIN can view them, and if necessary roll them back.
MELVIN cannot see SALLY's updates because she has not entered the COMMIT command.
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 not be able to see the rows that SALLY updated because they will be locked. MELVIN will be able to see the updated values. MELVIN will see the old versions of the rows
MELVIN will see the old versions of the rows
Which of the following data type(s) is(are) supported by Oracle? NCLOB NUMBER CHAR STRING TIME ZONE(n) WITH TIMESTAMP
NCLOB NUMBER CHAR
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? DELETE FROM PARTS WHERE STATUS = 'VALID'; DELETE FROM PARTS WHERE PNO = 1; DELETE FROM PARTS SET STATUS = NULL WHERE PNO = 1; None of these answers is correct
None of these answers is correct
If you wanted to sort output by a certain column, you would enter ORDER SORT ORDER BY SORT BY
ORDER BY
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); An error because Home_port_id was not given a value 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. An error because the datatypes of the columns and values are misaligned
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.
What is the number one DBMS used worldwide, according to DB-Engines Ranking? MySQL Oracle IBM Db2
Oracle
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
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
Which of the following job titles will make the following WHERE clause true? (Select all that apply.) WHERE job_title = 'SALES_REP' Sales_Rep None of these--the WHERE clause will cause an error sales_rep SALES_REP
SALES_REP
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 PORT_ID FROM PORTS: SELECT * FROM PORTS; SELECT PORT_NAME, PORT_ID FROM PORTS; SELECT 'Name of the port ' || PORT_NAME FROM PORTS;
SELECT * FROM PORTS; SELECT PORT_NAME, PORT_ID FROM PORTS; SELECT 'Name of the port ' || PORT_NAME FROM PORTS;
Which of the following Oracle tools can be used to execute SQL statements against a database? SQL Developer SQL*Plus command line SQL Language Reference Manual SQL Live
SQL Developer SQL*Plus command line SQL Live
Which of the following are Oracle Development Environments? SQL Developer SQL Language Reference Manual SQL*Plus command line SQL Live
SQL Developer SQL*Plus command line SQL Live
The relational model consists of which of the following. Choose all that are true: Set of operators to act on the relations, called the relational algebra 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 Primary and foreign keys Collection of tables (relations) Data integrity for accuracy and consistency
Which of the following is the recommended constraint name for the SHIPS table having Primary key SHIP_ID? ship_id_PK ships_id_PK PRIMARY KEY(Ship_id) Ships_Ship_ID_PK
Ships_Ship_ID_PK
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. T/F
T
LIKE 'Viking%' would return ship names such as 'Viking III', 'Viking Sojurn', and even the word 'Viking' itself. T/F
T
NOT NULL is different from the other four types of constraints. NOT NULL must be declared "in-line". T/F
T
Non-schema objects are public synonyms, users and roles. Tables, private synonyms, sequences, indexes, views, and constraints are schema objects. T/F
T
TRUNCATE is actually a DDL statement, and therefore has an implicit COMMIT. t/F
T
The UPDATE command without a WHERE clause is the only way to remove values in an entire column. For example: T/F
T
The following statement will remove all rows from the vendors table, but leave the table and index structure intact: TRUNCATE TABLE vendors; T/F
T
The underscore (_) is the single-letter wildcard symbol in Oracle. That is, any single character can be substituted for the underscore in a string. T/F
T
When comparing two character strings using the equal sign, the two strings must match exactly (including case) T/F
T
When used with the LIKE operator, the percent sign "%" is the wildcard symbol, and represents zero or more characters. T/F
T
Which datatype adds fractional seconds to a date/time? DATEPLUS DATESECONDS TIMESTAMP DATESTAMP
TIMESTAMP
The difference between dropping a column from a table with DROP and setting the column to UNUSED is: An UNUSED column may be recovered 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
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
What would happen when the following statement is executed: CREATE TABLE GGC_Student ( StudentID CHAR(6), SName VarChar2(40), gpa Number(5,4) ); An error will occur because a table name cannot contain an underscore The table will be created with StudentID as the Primary key The table will be created without a primary key
The table will be created without a primary key
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 once each time the statement is executed in a session The user will be prompted for the order_date twice, but only the first time the statement is executed in a session The user will be prompted for the order_date twice, each time the statement is executed in a session
The user will be prompted for the order_date twice, each time the statement is executed in a session
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. DELETE COLUMN DROP COLUMN UPDATE without a WHERE clause
UPDATE without a WHERE clause
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' There is no alternate way to write it. WHERE last_name LIKE 'K_ng' WHERE last_name LIKE 'K%ng'
WHERE last_name LIKE 'K_ng'
Which of the following is equivalent to: WHERE salary BETWEEN 3500 and 5000 WHERE salary >= 3500 AND salary <= 5000 WHERE salary <= 3500 AND salary >= 5000
WHERE salary BETWEEN 3500 and 5000
A substitution variable can be used to replace: (select all that apply) a table name a column name a WHERE clause ORDER BY options
a table name a column name a WHERE clause ORDER BY options
The DESCRIBE command, (DESC) can be used to show which of the following? a table's name and the user who created it a brief paragraph describing what the table does the data contained within a table a table's structure, that is, its columns and data types
a table's structure, that is, its columns and data types
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
all are true
An important concept of the relational model is the use of a ______, which is used to implement 1-to-many relationships between two tables. primary key foreign key composite key
foreign key
What are a primary key's identifying characteristics? (Select all that apply.) It uniquely identifies each row it consists of only one column it cannot be NULL it must be numeric
it cannot be NULL It uniquely identifies each row
What are a primary key's identifying characteristics? (Select all that apply.) it cannot be NULL it consists of only one column it must be numeric It uniquely identifies each row
it cannot be NULL It uniquely identifies each row
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) _______. Role database partition schema
schema
CREATE TABLE personnel ( personnel_ID NUMBER (6), division_ID NUMBER (6), CONSTRAINT personnel_pID_PK PRIMARY KEY (personnel_id), CONSTRAINT personnel_dID_PK PRIMARY KEY (division_id) ); What will be the result of an attempt to execute this statement: the statement will fail because you cannot create two primary key constraints on one table the statement will execute successfully to create the table with two primary key columns the statement will execute successfully to create the table with the first primary key but not the second
the statement will fail because you cannot create two primary key constraints on one table
