Database programmin section 18

1. Which SQL statement is used to remove all the changes made by an uncommitted transaction?
ROLLBACK (*)

2. If a database crashes, all uncommitted changes are automatically rolled back. True or False?
True (*)

3. COMMIT saves all outstanding data changes? True or False?
True (*)

4. You need not worry about controlling your transactions. Oracle does it all for you. True or False?
False (*)

5. User BOB's CUSTOMERS table contains 20 rows. BOB inserts two more rows into the table but does not COMMIT his changes. User JANE now executes:
SELECT COUNT(*) FROM bob.customers;
What result will JANE see?
20 (*)

6. A transaction makes several successive changes to a table. If required, you want to be able to rollback the later changes while keeping the earlier changes. What must you include in your code to do this?
A savepoint (*)

7. Steven King's row in the EMPLOYEES table has EMPLOYEE_ID = 100 and SALARY = 24000. A user issues the following statements in the order shown:
UPDATE employees
SET salary = salary * 2
WHERE employee_id = 100;
COMMIT;
UPDATE employees
SET salary = 30000
WHERE employee_id = 100;
The user's database session now ends abnormally. What is now King's salary in the table?
48000 (*)

8. Examine the following statements:
INSERT INTO emps SELECT * FROM employees; -- 107 rows inserted.
SAVEPOINT Ins_Done;
CREATE INDEX emp_lname_idx ON employees(last_name);
UPDATE emps SET last_name = 'Smith';
What happens if you issue a Rollback statement?

The update of last_name is undone, but the insert was committed by the CREATE INDEX statement. (*)

9. If UserB has privileges to see the data in a table, as soon as UserA has entered data into that table, UserB can see that data. True or False?
False (*)

10. If Oracle crashes, your changes are automatically rolled back. True or False?
True (*)

11. Which of the following best describes the term "read consistency"?
It prevents other users from seeing changes to a table until those changes have been committed (*)

12. When you logout of Oracle, your data changes are automatically rolled back. True or False?
False (*)

13. Table MYTAB contains only one column of datatype CHAR(1). A user executes the following statements in the order shown.
INSERT INTO mytab VALUES ('A');
INSERT INTO mytab VALUES ('B');
COMMIT;
INSERT INTO mytab VALUES ('C');
ROLLBACK;
Which rows does the table now contain?
A dan B (*)

14. Examine the following statements:
INSERT INTO emps SELECT * FROM employees; -- 107 rows inserted.
SAVEPOINT Ins_Done;
DELETE employees; -- 107 rows deleted
SAVEPOINT Del_Done;
UPDATE emps SET last_name = 'Smith';
How would you undo the last Update only?
ROLLBACK to SAVEPOINT Del_Done; (*)

15. Examine the following statements:
UPDATE employees SET salary = 15000;
SAVEPOINT upd1_done;
UPDATE employees SET salary = 22000;
SAVEPOINT upd2_done;
DELETE FROM employees;
You want to retain all the employees with a salary of 15000; What statement would you execute next?
ROLLBACK TO SAVEPOINT upd1_done; (*)

Database programming section 17

1. REGULAR EXPRESSIONS can be used as part of a contraint definition. (True or False?)
True (*)

2. Which of these SQL functions used to manipulate strings is NOT a valid regular expression function ?

REGEXP (*)

3. REGULAR EXPRESSIONS can be used on CHAR, CLOB, and VARCHAR2 datatypes? (True or False)
True (*)

4. Which of the following privileges must be assigned to a user account in order for that user to connect to an Oracle database?
CREATE SESSION (*)

5. By Controlling User Access with Oracle Database Security, you can give access to specific Objects in the Database. True or False?
True (*)

6. You want to grant privileges to user CHAN that will allow CHAN to update the data in the EMPLOYEES table. Which type of privileges will you grant to CHAN?
Object privileges (*)

7. Which of the following are object privileges? (Choose two)
(Choose all correct answers)
INSERT (*)
SELECT (*)

8. System privileges are:
Required to gain access to the database. (*)

9. The database administrator wants to allow user Marco to create new tables in his own schema. Which privilege should be granted to Marco?

CREATE TABLE (*)

10. Which statement would you use to add privileges to a role?
GRANT (*)

11. Which keyword would you use to grant an object privilege to all database users?
PUBLIC (*)

12. To join a table in your database to a table on a second (remote) Oracle database, you need to use:

A database link (*)

13. A role can be granted to another role. True or False?
True (*)

14. Scott King owns a table called employees. He issues the following statement:
GRANT select ON employees TO PUBLIC;
Allison Plumb has been granted CREATE SESSION by the DBA. She logs into the database and issues the following statement:
GRANT ﾠselect ON ﾠscott_king.employees TO jennifer_cho;
True or False: Allison's statement will fail.
True (*)

15. What Oracle feature simplifies the process of granting and revoking privileges?
Role (*)

Database programming section 16

1. A gap can occur in a sequence because a user generated a number from the sequence and then rolled back the transaction. True or False?
True (*)

2. CURRVAL is a pseudocolumn used to refer to a sequence number that the current user has just generated by referencing NEXTVAL. True or False?
True (*)

3. Evaluate this statement:
DROP SEQUENCE line_item_id_seq;
What does this statement accomplish?
It removes the sequence from the data dictionary. (*)

4. Nextval and Currval are known as column aliases. True or False?
False (*)

5. In order to be able to generate primary key values that are not likely to contain gaps, which phrase should be included in the sequence creation statement?
NOCACHE (*)

6. Which is the correct syntax for specifying a maximum value in a sequence?

Maxvalue (*)

7. Evaluate this CREATE SEQUENCE statement:
CREATE SEQUENCE order_id_seq NOCYCLE NOCACHE;
Which statement is true?
The sequence will start with 1. (*)

8. When creating a sequence, which keyword or option specifies the minimum sequence value?
MINVALUE (*)

9. Evaluate this statement:
CREATE INDEX sales_idx ON oe.sales (status);
Which statement is true?
The CREATE INDEX statement creates a nonunique index. (*)

10. When creating an index on one or more columns of a table, which of the following statements are true?
(Choose two)
(Choose all correct answers)
You should create an index if one or more columns are frequently used together in a join condition. (*)
You should create an index if the table is large and most queries are expected to retrieve less than 2 to 4 percent of the rows. (*)

11. Indexes can be used to speed up queries. True or False?
True (*)

12. Which of the following is created automatically by Oracle when a UNIQUE integrity constraint is created?

An index (*)

13. You want to create a composite index on the FIRST_NAME and LAST_NAME columns of the EMPLOYEES table. Which SQL statement will accomplish this task?

CREATE INDEX fl_idx
ON employees(first_name,last_name);
(*)

14. What is the correct syntax for creating an index?

CREATE OR REPLACE INDEX index_name ON table_name(column_name);

15. Which of the following statements best describes indexes and their use?
They contain the column value and pointers to the data in the table, but the data is sorted. (*)

Database programming section 15

1. Using the pseudocolumn ROWNUM in a view has no implications on the ability to do DML's through the view. True or False?
False (*)

2. Given the following view, which operations would be allowed on the emp_dept view?
CREATE OR REPLACE VIEW emp_dept
AS SELECT SUBSTR(e.first_name,1,1) ||' '||e.last_name emp_name,
    e.salary,
    e.hire_date,
    d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.department_id >=50;
SELECT, UPDATE of some columns, DELETE (*)

3. Which statement about performing DML operations on a view is true?
You cannot modify data in a view if the view contains a group function. (*)

4. You administer an Oracle database. Jack manages the Sales department. He and his employees often find it necessary to query the database to identify customers and their orders. He has asked you to create a view that will simplify this procedure for himself and his staff. The view should not accept INSERT, UPDATE, or DELETE operations. Which of the following statements should you issue?
CREATE VIEW sales_view
   AS (SELECT c.companyname, c.city, o.orderid, o. orderdate, o.total
   FROM customers c, orders o
   WHERE c.custid = o.custid)
WITH READ ONLY;
(*)

5. Only one type of view exists. True or False?
False (*)

6. You want to create a view based on the SALESREP table. You plan to grant access to this view to members of the Sales department. You want Sales employees to be able to update the SALESREP table through the view, which you plan to name SALESREP_VIEW. What should not be specified in your CREATE VIEW statement?
A GROUP BY clause (*)

7. Which of the following is true about ROWNUM?
It is the number assigned to each row returned from a query as it is read from the table. (*)

8. Evaluate this SELECT statement:
SELECT ROWNUM "Rank", customer_id, new_balance
FROM (SELECT customer_id, new_balance
     FROM customer_finance
     ORDER BY new_balance DESC)
WHERE ROWNUM <= 25;
Which type of query is this SELECT statement?
A Top-n query (*)

9. A Top-N Analysis is capable of ranking a top or bottom set of results. True or False?
True (*)

10. The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
DEPARTMENT_ID NUMBER
JOB_ID NUMBER
MANAGER_ID NUMBER
SALARY NUMBER(9,2)
COMMISSOIN NUMBER(7,2)
HIRE_DATE DATE
Which SELECT statement could be used to display the 10 lowest paid clerks that belong to department 70?
SELECT ROWNUM "Ranking",last_name||','||first_name "Employee", salary "Salary"
FROM (SELECT last_name, first_name, salary, job_id
     FROM employees
     WHERE job_id LIKE 'CLERK' AND department_id = 70
     ORDER BY salary)
WHERE ROWNUM <=10;
(*)

11. Which statement would you use to alter a view?
CREATE OR REPLACE VIEW (*)

12. Evaluate this view definition:
CREATE OR REPLACE VIEW part_name_v
AS SELECT DISTINCT part_name
FROM parts
WHERE cost >= 45;
Which of the following statements using the PART_NAME_V view will execute successfully?

SELECT *
FROM part_name_v;
(*)

13. Unlike tables, views contain no data of their own. True or False?
True (*)

14. You need to create a view on the SALES table, but the SALES table has not yet been created. Which statement is true?

You can use the FORCE option to create the view before the SALES table has been created. (*)

15. Views must be used to select data from a table. As soon as a view is created on a table, you can no longer select directly from the table. True or False?
False (*)

Database programming section 14

1. A table must have at least one not null constraint and one unique constraint. True or False?
False (*)

2. You need to add a NOT NULL constraint to the COST column in the PART table. Which statement should you use to complete this task?
ALTER TABLE part
MODIFY (cost CONSTRAINT part_cost_nn NOT NULL);
(*)

3. Which statement about constraints is true?
NOT NULL constraints can only be specified at the column level. (*)

4. Which of the following is not a valid Oracle constraint type?
EXTERNAL KEY (*)

5. A column defined as NOT NULL can have a DEFAULT value of NULL. True or False?
False (*)

6. Foreign Key Constraints are also known as:
Referential Integrity Constraints (*)

7. Which of the following pieces of code will successfully create a foreign key in the CDS table that references the SONGS table?
All of the above (*)

8. Which of the following best describes the function of a CHECK constraint?
A CHECK constraint defines restrictions on the values that can be entered in a column or combination of columns. (*)

9. The number of check constraints that can be defined on a column is:
There is no limit (*)

10. The table that contains the Primary Key in a Foreign Key Constraint is known as:
Parent Table (*)

11. All of a user's constraints can be viewed in the Oracle Data Dictionary view called:
USER_CONSTRAINTS (*)

12. Evaluate this statement
ALTER TABLE employees
ENABLE CONSTRAINT emp_id_pk;
For which task would you issue this statement?
To activate the previously disabled constraint on the EMPLOYEE_ID column while creating a PRIMARY KEY index (*)

13. You can view the columns used in a constraint defined for a specific table by looking at which data dictionary table?
USER_CONS_COLUMNS (*)

14. What is the syntax for removing a PRIMARY KEY constraint and all its dependent constraints?
ALTER TABLE table_name
DROP CONSTRAINT constraint_name CASCADE;
(*)

15. Examine the structures of the PRODUCTS and SUPPLIERS tables.
PRODUCTS:
PRODUCT_ID NUMBER NOT NULL, PRIMARY KEY
PRODUCT_NAME VARCHAR2 (25)
SUPPLIER_ID NUMBER FOREIGN KEY to SUPPLIER_ID of the SUPPLIER table
LIST_PRICE NUMBER (7,2)
COST NUMBER (7,2)
QTY_IN_STOCK NUMBER
QTY_ON_ORDER NUMBER
REORDER_LEVEL NUMBER
REORDER_QTY NUMBER
SUPPLIERS:
SUPPLIER_ID NUMBER NOT NULL, PRIMARY KEY
SUPPLIER_NAME VARCHAR2 (25)
ADDRESS VARCHAR2 (30)
CITY VARCHAR2 (25)
REGION VARCHAR2 (10)
POSTAL_CODE VARCHAR2 (11)
Evaluate this statement:
ALTER TABLE suppliers
DISABLE CONSTRAINT supplier_id_pk CASCADE;
For which task would you issue this statement?
To disable any dependent integrity constraints on the SUPPLIER_ID column in the SUPPLIERS table (*)

Database programming section 13

1. You need to store the HIRE_DATE value with a time zone displacement value and allow data to be returned in the user's local session time zone. Which data type should you use?
TIMESTAMP WITH LOCAL TIME ZONE (*)

2. You are designing a table for the Sales department. You need to include a column that contains each sales total. Which data type should you specify for this column?

NUMBER (*)

3. The TIMESTAMP data type allows what?
Time to be stored as a date with fractional seconds. (*)

4. Evaluate this CREATE TABLE statement:
CREATE TABLE sales
( sales_id NUMBER(9),
customer_id NUMBER(9),
employee_id NUMBER(9),
description VARCHAR2(30),
sale_date TIMESTAMP WITH LOCAL TIME ZONE DEFAULT SYSDATE,
sale_amount NUMBER(7,2));
Which business requirement will this statement accomplish?
Today's date should be used if no value is provided for the sale date. (*)

5. A column that will be used to store binary data up to 4 Gigabytes in size should be defined as which datatype?
BLOB (*)

6. You need to remove all the rows from the SALES_HIST table. You want to release the storage space, but do not want to remove the table structure. Which statement should you use?
The TRUNCATE TABLE statement (*)

7. When you use ALTER TABLE to add a column, the new column:
Becomes the last column in the table (*)

8. Examine the structure of the DONATIONS table.
DONATIONS:
PLEDGE_ID NUMBER
DONOR_ID NUMBER
PLEDGE_DT DATE
AMOUNT_PLEDGED NUMBER (7,2)
AMOUNT_PAID NUMBER (7,2)
PAYMENT_DT DATE
You need to reduce the precision of the AMOUNT_PLEDGED column to 5 with a scale of 2 and ensure that when inserting a row into the DONATIONS table without a value for the AMOUNT_PLEDGED column, a price of $10.00 will automatically be inserted. The DONATIONS table currently contains NO records. Which statement is true?
Both changes can be accomplished with one ALTER TABLE statement. (*)

9. Which statement about decreasing the width of a column is true?
When a character column contains data, you can decrease the width of the column if the existing data does not violate the new size. (*)

10. Evaluate the structure of the EMPLOYEE table:
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
DEPARTMENT_ID NUMBER(9)
MANAGER_ID NUMBER(9)
SALARY NUMBER(7,2)
The EMPLOYEE_ID column currently contains 500 employee identification numbers. Business requirements have changed and you need to allow users to include text characters in the identification values. Which statement should you use to change this column's data type?
You CANNOT modify the data type of the EMPLOYEE_ID column, as the table is not empty. (*)

11. Which SQL statement below will correctly create the EMP table based on the structure of the EMPLOYEES table? Include only the EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, and DEPARTMENT_ID columns.
CREATE TABLE emp
AS SELECT employee_id, first_name, last_name, salary, department_id
FROM employees;
(*)

12. You want to create a table named TRAVEL that is a child of the EMPLOYEES table. Which of the following statements should you issue?
CREATE TABLE travel
(destination_id number primary key, departure_date date, return_date date, emp_id number(10) REFERENCES employees (emp_id));
(*)

13. Which statement about table and column names is true?
Table and column names must begin with a letter. (*)

14.  When creating a new table, which of the following naming rules apply. (Choose three)
(Choose all correct answers)

Must contain ONLY A - Z, a - z, 0 - 9, _ (underscore), $, and # (*)
Must begin with a letter (*)
Must be between 1 to 30 characters long (*)

15. DCL, which is the acronym for Data Control Language, allows:

A Database Administrator the ability to grant privileges to users. (*)

Database programming section 12

1. To change an existing row in a table, you can use the UPDATE or INSERT statements. True or False?
False (*)

2. If you are performing an UPDATE statement with a subquery, it MUST be a correlated subquery? (True or False)
False (*)

3. DELETE statements can use correlated subqueries? (True or False)
True (*)

4. The EMPLOYEES table contains the following columns:
EMPLOYEE_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
DEPTARTMENT_ID VARCHAR2(20)
HIRE_DATE DATE
SALARY NUMBER(9,2)
BONUS NUMBER(9,2)
You need to increase the salary for all employees in department 10 by 10 percent. You also need to increase the bonus for all employees in department 10 by 15 percent. Which statement should you use?
UPDATE employees
SET salary = salary * 1.10, bonus = bonus * 1.15
WHERE department_id = 10; (*)

5. Assuming there are no Foreign Keys on the EMPLOYEES table, if the following subquery returns one row, how many rows will be deleted from the EMPLOYEES table?
DELETE FROM employees
WHERE department_id =
     (SELECT department_id
     FROM departments
     WHERE department_name LIKE '%Public%');

All the rows in the EMPLOYEES table with department_ids matching the department_id returned by the subquery. (*)

6. If a default value was set for a null column, Oracle sets the column to the default value. However, if no default value was set when the column was created, Oracle inserts an empty space. True or False?

False (*)

7. Multi-table inserts are used when the same source data should be inserted into _____________ target table.
More than one (*)

8. In developing the Employees table, you create a column called hire_date. You assign the hire_date column a DATE datatype with a DEFAULT value of 0 (zero). A user can come back later and enter the correct hire_date. This is __________.
A bad idea. The default value must match the DATE datatype of the column. (*)

9. Using MERGE accomplishes an __________ and __________ simultaneously.
INSERT; UPDATE (*)

10. Aliases can be used with MERGE statements. True or False?
True (*)

11. The PRODUCTS table contains these columns:
PRODUCT_ID NUMBER NOT NULL
PRODUCT_NAME VARCHAR2 (25)
SUPPLIER_ID NUMBER NOT NULL
LIST_PRICE NUMBER (7,2)
COST NUMBER (5,2)
QTY_IN_STOCK NUMBER(4)
LAST_ORDER_DT DATE DEFAULT SYSDATE NOT NUL
Which INSERT statement will execute successfully?
INSERT INTO products (product_id, product_name, supplier_id, list_price, cost, qty_in_stock)
VALUES(2958, 'Cable', 8690, 7.09, 4.04, 700) (*)

12. You need to add a row to an existing table. Which DML statement should you use?
INSERT (*)

13. Insert statements can be combined with subqueries to create more than one row per statement. True or False?
True (*)

14. DML is an acronym that stands for:
Data Manipulation Language (*)

15. If the employees table has 7 rows, how many rows are inserted into the copy_emps table with the following statement:
INSERT INTO copy_emps (employee_id, first_name, last_name, salary, department_id)
SELECT employee_id, first_name, last_name, salary, department_id
FROM employees
7 rows, as no WHERE-clause restricts the rows returned on the subquery. (*)

Database programming section 10

1. Which statement about the <> operator is true?
The <> operator can be used when a single-row subquery returns only one row. (*)

2. The result of this statement will be:
SELECT last_name, job_id, salary, department_id
FROM employees
WHERE job_id =
     (SELECT job_id
      FROM employees
      WHERE employee_id = 141) AND
    department_id =
     (SELECT department_id
      FROM departments
      WHERE location_id =1500);
Only the employees whose job id matches employee 141 and who work in location 1500 (*)

3. In a non-correlated subquery, the outer query always executes prior to the inner query's execution. True or False?
False (*)

4. When creating a report of all employees earning more than the average salary for their department, a __________ ____________ can be used to first calculate the average salary of each department, and then compare the salary for each employee to the average salary of that employeeﾒs department.
CORRELATED SUBQUERY (*)

5. In a correlated subquery, the outer and inner queries are joined on one or more columns. (True or False?)
True (*)

6. A correlated subquery is evaluated _____ for each row processed by the parent statement.
ONCE (*)

7. You need to create a SELECT statement that contains a multiple-row subquery. Which comparison operator(s) can you use?
IN, ANY, and ALL (*)

8. Which of the following is a valid reason why the query below will not execute successfully?
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id =
    (SELECT department_id FROM employees WHERE last_name like '%u%');
A single, rather than a multiple value operator was used. (*)

9. The salary column of the f_staffs table contains the following values:
4000
5050
6000
11000
23000
Which of the following statements will return the last_name and first_name of those employees who earn more than 5000?

SELECT last_name, first_name
FROM f_staffs
WHERE salary IN (SELECT salary FROM f_staffs WHERE salary > 5000); (*)

10. Group functions can be used in multiple-row subqueries in the HAVING and GROUP BY clauses. True or False?
True (*)

11. Which of the following statements contains a comparison operator that is used to restrict rows based on a list of values returned from an inner query?
All of the above. (*)

12. What would happen if you attempted to use a single-row operator with a multiple-row subquery?
An error would be returned. (*)

13. Which operator can be used with a multiple-row subquery?
IN (*)

14. Using a subquery in which clause will return a syntax error?
You can use subqueries in all of the above clauses. (*)

15. The EMPLOYEES and ORDERS tables contain these columns:
EMPLOYEES
EMPLOYEE_ID NUMBER(10) NOT NULL PRIMARY KEY
FIRST_NAME VARCHAR2(30)
LAST_NAME VARCHAR2(30)
ADDRESS VARCHAR2(25)
CITY VARCHAR2(20)
STATE VARCHAR2(2)
ZIP NUMBER(9)
TELEPHONE NUMBER(10)
ORDERS
ORDER_ID NUMBER(10) NOT NULL PRIMARY KEY
EMPLOYEE_ID NUMBER(10) NOT NULL FOREIGN KEY
ORDER_DATE DATE
TOTAL NUMBER(10)
Which SELECT statement will return all orders generated by a sales representative named Franklin during the year 2001?
SELECT order_id, total
FROM ORDERS
WHERE employee_id = (SELECT employee_id FROM employees WHERE last_name = 'Franklin')
AND order_date BETWEEN '01-Jan-2001' AND '31-Dec-2001';
(*)

Database programming section 9

1. The difference between UNION and UNION ALL is
UNION will remove duplicates; UNION ALL returns all rows from all queries including the duplicates. (*)

2. To control the order of rows returned using SET operators, the ORDER BY clause is used ______ and is placed in the _____ SELECT statement of the query.
ONCE; LAST (*)

3. When using SET operators, the number of columns and the data types of the columns must be identical in all of the SELECT statements used in the query. True or False.
True(*)

4. Evaluate this SELECT statement:
SELECT COUNT(emp_id), mgr_id, dept_id
FROM employees
WHERE status = 'I'
GROUP BY dept_id
HAVING salary > 30000
ORDER BY 2;
Why does this statement return a syntax error?
MGR_ID must be included in the GROUP BY clause. (*)

5. Evaluate this SELECT statement:
SELECT MIN(hire_date), department_id
FROM employees
GROUP BY department_id;
Which values are displayed?

The earliest hire date in each department (*)

6. Which statement about the GROUP BY clause is true?
To exclude rows before dividing them into groups using the GROUP BY clause, you should use a WHERE clause. (*)

7. Evaluate this SELECT statement:
SELECT MAX(salary), department_id
FROM employees
GROUP BY department_id;
Which values are displayed?
The highest salary in each department (*)

8. Group functions can be nested to a depth of?
Two(*)

9. Evaluate this SELECT statement:
SELECT SUM(salary), department_id, department_name
FROM employees
WHERE department_id = 1
GROUP BY department;
Which clause of the SELECT statement contains a syntax error?
GROUP BY (*)

10. You use GROUPING functions to ______ database rows from tabulated rows.
DISTINGUISH(*)

11. Examine the following statement:
SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS(.......);
Select the correct GROUP BY GROUPING SETS clause from the following list:
GROUP BY GROUPING SETS ((department_id, manager_id), (department_id, job_id), (manager_id, job_id)) (*)

12. GROUPING SETS is another extension to the GROUP BY clause and is used to specify multiple groupings of data but provide a single result set. True or False?
Trus (*)

13. Examine the following statement:
SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY ROLLUP(department_id, manager_id)
What extra data will this query generate?
The statement will fail. (*)

14. You use GROUPING functions to:
Identify the extra row values created by either a ROLLUP or CUBE operation (*)

15. If you want to include subtotals and grand totals for all columns mentioned in a GROUP BY clause, you should use which of the following extensions to the GROUP BY clause?
CUBE (*)

Database programming section 8

1. What two group functions can be used with any datatype?
MIN, MAX (*)

2. The VENDORS table contains these columns:
VENDOR_ID NUMBER Primary Key
NAME VARCHAR2(30)
LOCATION_ID NUMBER
ORDER_DT DATE
ORDER_AMOUNT NUMBER(8,2)
Which two clauses represent valid uses of aggregate functions for this table?

(Choose all correct answers)
SELECT SUM(order_amount) (*)
SELECT MIN(AVG(order_amount)) (*)

3. Given the following data in the employees table (employee_id, salary, commission_pct)
DATA:     (143, 2600, null
    144, 2500, null
    149, 10500, .2
    174, 11000, .3
    176, 8600, .2
    178, 7000, .15)
What is the result of the following statement:
SELECT AVG(commission_pct)
FROM employees
WHERE employee_id IN( 143,144,149,174,176,178);
0.2125(*)

4. The TRUCKS table contains these columns:
TRUCKS:
TYPE VARCHAR2(30)
YEAR DATE
MODEL VARCHAR2(20)
PRICE NUMBER(10)
Which SELECT statement will return the average price for the 4x4 model?
SELECT AVG(price)
FROM trucks
WHERE model = '4x4'; (*)

5. The CUSTOMER table contains these columns:
CUSTOMER_ID NUMBER(9)
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(30)
CREDIT_LIMIT NUMBER (7,2)
CATEGORY VARCHAR2(20)
You need to calculate the average credit limit for all the customers in each category. The average should be calculated based on all the rows in the table excluding any customers who have not yet been assigned a credit limit value.
Which group function should you use to calculate this value?
AVG (*)

6. Given the following data in the employees table (employee_id, salary, commission_pct)
DATA: (143, 2600, null
144, 2500, null
149, 10500, .2
174, 11000, .3
176, 8600, .2
178, 7000, .15)
What is the result of the following statement:
SELECT SUM(commission_pct), COUNT(salary)
FROM employees
WHERE employee_id IN( 143,144,149,174,176,178);
SUM = .85 and COUNT = 6 (*)

7. You need to calculate the average salary of employees in each department. Which group function will you use?
AVG(*)

8. The PRODUCTS table contains these columns:
PROD_ID NUMBER(4)
PROD_NAME VARCHAR2(30)
PROD_CAT VARCHAR2(30)
PROD_PRICE NUMBER(3)
PROD_QTY NUMBER(4)
The following statement is issued:
SELECT AVG(prod_price, prod_qty)
FROM products;
What happens when this statement is issued?
An error occurs. (*)

9. Evaluate this SQL statement:
SELECT COUNT (amount)
FROM inventory;
What will occur when the statement is issued?
The statement will count the number of rows in the INVENTORY table where the AMOUNT column is not null. (*)

10. Examine the data from the LINE_ITEM table:
LINE_ITEM_ID ORDER_ID PRODUCT_ID PRICE DISCOUNT
890898 847589 848399 8.99 0.10
768385 862459 849869 5.60 0.05
867950 985490 945809 5.60
954039 439203 438925 5.25 0.15
543949 349302 453235 4.50
You query the LINE_ITEM table and a value of 5 is returned. Which SQL statement did you execut
SELECT COUNT(*)
FROM line_item;
(*)

11. What would the following SQL statement return?
SELECT COUNT(DISTINCT salary)
FROM employees;
The number of unique salaries in the employees table (*)

12. Group functions can avoid computations involving duplicate values by including which keyword?
DISTINCT(*)

13. What would the following SQL statement return?
SELECT COUNT(first_name)
FROM employees;
The total number of non-null first names in the employees table (*)

14. Which SELECT statement will calculate the number of rows in the PRODUCTS table?
SELECT COUNT (*) FROM products; (*)

15. Evaluate this SELECT statement:
SELECT COUNT(*)
FROM products;
Which statement is true?
The number of rows in the table is displayed. (*)

Database programming section 7

1. If table A has 10 rows and table B has 5 rows, how many rows will be returned if you perform a cartesian join on those two tables?
50 (*)

2. Evaluate this SQL statement:
SELECT e.employee_id, e.last_name, e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id AND employees.department_id > 5000
ORDER BY 4;
Which clause contains a syntax error?
AND employees.department_id > 5000 (*)

3. You need to create a report that lists all employees in department 10 (Sales) whose salary is not equal to $25,000 per year. Which query should you issue to accomplish this task?
SELECT last_name, first_name, salary
FROM employees
WHERE salary != 25000 AND dept_id = 10; (*)

4. When must column names be prefixed by table names in join syntax?
When the same column name appears in more than one table of the query (*)

5. What happens when you create a Cartesian product?
All rows from one table are joined to all rows of another table (*)

6. What is produced when a join condition is not specified in a multiple-table query using Oracle proprietary Join syntax?
A Cartesian product (*)

7. What is the minimum number of join conditions required to join 5 tables together?
4 (*)

8. Will the following statement work?
SELECT department_name, last_name
FROM employees, departments
WHERE department_id = department_id;
No, Oracle will return a Column Ambiguously Defined error. (*)

9. Which symbol is used to perform an outer join?
(+) (*)

10. Evaluate this SELECT statement:
SELECT p.player_id, m.last_name, m.first_name, t.team_name
FROM player p
LEFT OUTER JOIN player m ON (p.manager_id = m.player_id)
LEFT OUTER JOIN team t ON (p.team_id = t.team_id);
Which join is evaluated first?

The self-join of the player table (*)

Database programming section 6

1. Which statement about a natural join is true?
Columns with the same names must have the same precision and datatype. (*)

2. You need to join two tables that have two columns with the same name, datatype, and precision. Which type of join would you create to join the tables on both of the columns?
Natural join (*)

3. You need to join all the rows in the EMPLOYEES table to all the rows in the EMP_REFERENCE table. Which type of join should you create?

A cross join (*)

4. If you select rows from two tables (employees and departments) using the outer join specified in the example, what will you get?
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);
All employees including those that do not have a departement_id assigned to them (*)

5. You need to display all the rows (both matching and non-matching) from both the EMPLOYEE
and EMPLOYEE_HIST tables. Which type of join would you use?
A full outer join (*)

6. Which syntax would be used to retrieve all rows in both the EMPLOYEES and DEPARTMENTS tables, even when there is no match?
FULL OUTER JOIN (*)

7. EMPLOYEES Table:
Name Null? Type
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
DEPARTMENT_ID NUMBER (4)
DEPARTMENTS Table:
Name Null? Type
DEPARTMENT_ID NOT NULL NUMBER 4
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER (6)
A query is needed to display each department and its manager name from the above tables. However, not all departments have a manager but we want departments returned in all cases. Which of the following SQL: 1999 syntax scripts will accomplish the task?
SELECT d.department_id, e.first_name, e.last_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.employee_id = d.manager_id); (*)

8. Table aliases MUST be used with columns referenced in the JOIN USING clause. True or False?
False(*)

9. The keywords JOIN _____________ should be used to join tables with the same column names but different datatypes.
Using (*)

10. The primary advantage of using JOIN ON is:
It permits columns with different names to be joined. (*)

11. Hierarchical queries MUST use the LEVEL pseudo column. True or False?
False (*)

12. Hierarchical queries can walk both Top-Down and Bottom-Up. True or False?
True (*)

13. Which of the following database design concepts is implemented with a self join?
Recursive Relationship (*)

14. Which SELECT statement implements a self join?

SELECT p.part_id, t.product_id
FROM part p, part t
WHERE p.part_id = t.product_id; (*)

15. Evaluate this SELECT statement:
SELECT *
FROM employee e, employee m
WHERE e.mgr_id = m.emp_id;
Which type of join is created by this SELECT statement?
A self join (*)

Database programming section 5

1. Consider the following data in the Employees table: (last_name, commission_pct, manager_id)
DATA:
King, null, null
Kochhar, null, 100
Vargas, null, 124
Zlotkey, .2, 100
What is the result of the following statement:
SELECT last_name, COALESCE(commission_pct, manager_id, -1) comm
FROM employees ;
King, -1
Kochhar, 100
Vargas, 124
Zlotkey, .2 (*)

2. If quantity is a number datatype, what is the result of this statement?
SELECT NVL(200/quantity, 'zero') FROM inventory;
The statement fails (*)

3. The following statement returns 0 (zero). True or False?
SELECT 121/NULL
FROM dual;
False(*)

4. Which of the following General Functions will return the first non-null expression in the expression list?
COALESCE(*)

5. The STYLES table contains this data:
STYLE_ID STYLE_NAME CATEGORY COST
895840 SANDAL 85940 12.00
968950 SANDAL 85909 10.00
869506 SANDAL 89690 15.00
809090 LOAFER 89098 10.00
890890 LOAFER 89789 14.00
857689 HEEL 85940 11.00
758960 SANDAL 86979
Evaluate this SELECT statement:
SELECT style_id, style_name, category, cost
FROM styles
WHERE style_name LIKE 'SANDAL' AND NVL(cost, 0) < 15.00
ORDER BY category, cost;
Which result will the query provide?
STYLE_ID STYLE_NAME CATEGORY COST
968950 SANDAL 85909 10.00
895840 SANDAL 85940 12.00
758960 SANDAL 86979
(*)

6. When executed, which statement displays a zero if the TUITION_BALANCE value is zero and the HOUSING_BALANCE value is null?
SELECT NVL (tuition_balance + housing_balance, 0) "Balance Due"
FROM student_accounts; (*)

7. Which of the following is a conditional expression used in SQL?
CASE (*)

8. For the given data from Employees (last_name, manager_id) what is the result of the following statement:
DATA:( King, null
Kochhar, 100
De Haan, 100
Hunold, 102
Ernst, 103)
SELECT last_name,
DECODE(manager_id, 100, 'King', 'A N Other') "Works For?"
FROM employees

King, A N Other
Kochhar, King
De Haan, King
Hunold, Kochhar
Ernst, De Haan

9. CASE and DECODE evaluate expressions in a similar way to IF-THEN-ELSE logic. However, DECODE is specific to Oracle syntax. True or False?
True(*)

10.  The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2 (25)
FIRST_NAME VARCHAR2 (25)
HIRE_DATE DATE
You need to display HIRE_DATE values in this format:
January 28, 2000
Which SQL statement could you use?

SELECT TO_CHAR(hire_date, 'Month DD, YYYY')
FROM employees; (*)

11. Which functions allow you to perform explicit data type conversions?
TO_CHAR, TO_DATE, TO_NUMBER (*)

12. A table has the following definition: EMPLOYEES(
EMPLOYEE_ID NUMBER(6) NOT NULL,
NAME VARCHAR2(20) NOT NULL,
MANAGER_ID VARCHAR2(6))

and contains the following rows:

(1001, 'Bob Bevan', '200')
(200, 'Natacha Hansen', null)

Will the folloiwng query work?

SELECT *
FROM employees
WHERE employee_id = manager_id;

Yes, Oracle will perform implicit datatype conversion, but the WHERE clause will not find any matching data. (*)

13. All Human Resources data is stored in a table named EMPLOYEES. You have been asked to create a report that displays each employee's name and salary. Each employee's salary must be displayed in the following format: $000,000.00. Which function should you include in a SELECT statement to achieve the desired result?
TO_CHAR (*)

14. You have been asked to create a report that lists all customers who have placed orders of at least $2,500. The report's date should be displayed using this format:
Day, Date Month, Year (For example, Tuesday, 13 April, 2004 ).
Which statement should you issue?
SELECT companyname, TO_CHAR (sysdate, 'fmDay, dd Month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500; (*)

15. Sysdate is 12-May-2004.
You need to store the following date: 7-Dec-89
Which statement about the date format for this value is true?
The RR date format will interpret the year as 1989, and the YY date format will interpret the year as 2089 (*)

Database programming section 4

1. You query the database with this SQL statement:
SELECT LOWER(SUBSTR(CONCAT(last_name, first_name)), 1, 5) "ID"
FROM employee;
In which order are the functions evaluated?
CONCAT, SUBSTR, LOWER (*)

2. You query the database with this SQL statement:
SELECT CONCAT(last_name, (SUBSTR(LOWER(first_name), 4))) "Default Password"
FROM employees;
Which function will be evaluated first?
LOWER (*)

3. The STYLES table contains this data:
STYLE_IDSTYLE_NAMECATEGORYCOST895840SANDAL8594012.00968950SANDAL8590910.00869506SANDAL8969015.00809090LOAFER8909810.00890890LOAFER8978914.00857689HEEL8594011.00758960SANDAL8697912.00
You query the database and return the value 79. Which script did you use?

SELECT SUBSTR(category, -2,2)
FROM styles
WHERE style_id = 758960;

4. Which query selects the first names of the DJ On Demand clients who have a first name beginning with "A"?
SELECT UPPER(first_name)
FROM d_clients
WHERE LOWER(first_name) LIKE 'a%'
(*)

5. What does the following SQL SELECT statement return?
SELECT UPPER( SUBSTR('Database Programming', INSTR('Database Programming','P'),20))
FROM dual;
PROGRAMMING (*)

6. Which of the following Date Functions will add calendar months to a date?
ADD_MONTHS (*)

7. Which SELECT statement will NOT return a date value?
SELECT (SYSDATE - hire_date) + 10*8
FROM employees;

8. Round and Trunc cannot be used on Date datatypes. True or False?
False(*)

9. Evaluate this SELECT statement:
SELECT SYSDATE + 30
FROM dual;
Which value is returned by the query?

The current date plus 30 days. (*)

10. Which of the following SQL statements will correctly display the last name and the number of weeks employed for all employees in department 90?
SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
FROM employees
WHERE department_id = 90;
(*)

11. Which two functions can be used to manipulate number or date column values, but NOT character column values? (Choose two.)
(Choose all correct answers)
ROUND(*)
TRUNC(*)

12. The answer to the following script is 456. True or False?
SELECT TRUNC(ROUND(456.98))
FROM dual;

False (*)

13. Which number function may be used to determine if a value is odd or even?
MOD(*)

14. Which comparison operator retrieves a list of values?
IN(*)

15. Which script displays '01-May-2004' when the HIRE_DATE value is '20-May-2004'?

SELECT TRUNC(hire_date, 'MONTH')
FROM employees;
(*)



 




 

Database programming section 3

1. A column alias can be specified in an ORDER BY Clause. True or False?
True (*)

2. Evaluate this SELECT statement:
SELECT last_name, first_name, salary
FROM employees;
How will the results of this query be sorted?
The database will display the rows in whatever order it finds it in the database, so no particular order. (*)

3. Evaluate this SELECT statement:
SELECT last_name, first_name, department_id, manager_id
FROM employees;
You need to sort data by manager id values and then alphabetically by employee last name and first name values. Which ORDER BY clause could you use?
ORDER BY manager_id, last_name, first_name (*)

4. You attempt to query the database with this SQL statement:
SELECT product_id "Product Number", category_id "Category", price "Price"
FROM products
WHERE "Category" = 5570
ORDER BY "Product Number";
This statement fails when executed. Which clause contains a syntax error?
WHERE "Category" = 5570 (*)

5. Which SELECT statement should you use to limit the display of product information to those products with a price of less than 50?
SELECT product_id, product_name
FROM products
WHERE price < 50;
(*)

6. The following statement represents a multi-row function. True or False?
SELECT UPPER(last_name)
FROM employees;
False (*)

7. The PLAYERS table contains these columns:
PLAYERS TABLE:
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)
SALARY NUMBER(8,2)
TEAM_ID NUMBER(4)
MANAGER_ID NUMBER(9)
POSITION_ID NUMBER(4)
You must display the player name, team id, and salary for players whose salary is in the range from 25000 through 100000 and whose team id is in the range of 1200 through 1500. The results must be sorted by team id from lowest to highest and then further sorted by salary from highest to lowest. Which statement should you use to display the desired result?
SELECT last_name, first_name, team_id, salary
FROM players
WHERE salary BETWEEN 25000 AND 100000
AND team_id BETWEEN 1200 AND 1500
ORDER BY team_id, salary DESC;
(*)

8. The following statement represents a multi-row function. True or False?
SELECT MAX(salary)
FROM employees
True (*)

9. Evaluate this SQL statement:
SELECT e.employee_id, e.last_name, e.first_name, m.manager_id
FROM employees e, employees m
ORDER BY e.last_name, e.first_name
WHERE e.employee_id = m.manager_id;
This statement fails when executed. Which change will correct the problem?
Reorder the clauses in the query. (*)

10. Will the following statement return one row?
SELECT MAX(salary), MIN(Salary), AVG(SALARY)
FROM employees;

Yes, it will return the highest salary, the lowest salary, and the average salary from all employees. (*)

11. Which of the following is earliest in the rules of precedence?
Arithmetic operator (*)

12. Which statement about the logical operators is true?
The order of operator precedence is NOT, AND, and OR. (*)

13. Which comparison condition means "Less Than or Equal To"?

"<=" (*)

14. What will be the results of the following selection?
SELECT *
FROM employees
WHERE last_name NOT LIKE 'A%' AND last_name NOT LIKE 'B%'
All last names that do not begin with A or B (*)

15. Which of the following statements best describes the rules of precedence when using SQL?
The order in which the expressions are evaluated and calculated (*)

Database programming section 2

1. The EMPLOYEES table contains these columns:
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
EMAIL VARCHAR2(50)
You are writing a SELECT statement to retrieve the names of employees that have an email address.
SELECT last_name||', '||first_name "Employee Name"
FROM employees;
Which WHERE clause should you use to complete this statement?
Mark for Review (1) Point
WHERE email IS NULL; WHERE email IS NOT NULL;(*)
WHERE email != NULL; WHERE email = NULL;

2. If you write queries using the BETWEEN operator, it does not matter in what order you enter the values, i.e. BETWEEN low value AND high value will give the same result as BETWEEN high value and low value. True or False?
Mark for Review (1) Point
True
False(*)

3. When using the "LIKE" operator, the % and _ symbols can be used to do a pattern-matching, wild card search. True or False?

Mark for Review (1) Point
True(*)
False


4. Which of the following are examples of comparison operators used in the WHERE clause?
Mark for Review (1) Point
=, >, <, <=, >=, <> in (..,..,.. )
between ___ and ___ like
is null All of the above(*)

5. The EMPLOYEES table includes these columns:
EMPLOYEE_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(15) NOT NULL
FIRST_NAME VARCHAR2(10) NOT NULL
HIRE_DATE DATE NOT NULL
You want to produce a report that provides the last names, first names, and hire dates of those employees who were hired between March 1, 2000, and August 30, 2000. Which statements can you issue to accomplish this task?
 Mark for Review (1) Point

SELECT last_name, first_name, hire_date
FROM employees
WHERE hire_date BETWEEN '01-Mar-2000' AND '30-Aug-2000';
(*)

6. Which comparison operator searches for a specified character pattern?
Mark for Review (1) Point
BETWEEN… AND… LIKE(*)
IS NULL IN

7. Which of the following is NOT BEING DONE in this SQL statement?
SELECT first_name || ' ' || last_name "Name"
FROM employees;
Mark for Review (1) Point
Concatenating first name, middle name and last name (*)
Putting a space between first name and last name
Selecting columns from the employees table
Using a column alias

8. You need to display all the rows in the EMPLOYEES table that contain a null value in the DEPARTMENT_ID column. Which comparison operator should you use?
Mark for Review (1) Point
“=NULL”
IS NULL
ISNULL
NULL!

9. The Concatenation Operator does which of the following?

Mark for Review (1) Point
Links rows of data together inside the database.
Separates columns.
Links two or more columns or literals to form a single output column (*)
Is represented by the asterisk (*) symbol

10. You need to display only unique combinations of the LAST_NAME and MANAGER_ID columns in the EMPLOYEES table. Which keyword should you include in the SELECT clause?
Mark for Review (1) Point
UNIQUEONE
DISTINCTROW
DISTINCT
ONLY

11. You need to display all the employees whose last names (of any length) start with the letters 'Sm' . Which WHERE clause should you use?

Mark for Review (1) Point

WHERE last_name LIKE '_Sm'
WHERE last_name LIKE ‘Sm_'
WHERE last_name LIKE 'Sm%'(*)

WHERE last_name LIKE '%Sm'

12. How can you write "not equal to" in the WHERE-clause?
Mark for Review (1) Point
!=
^=
<>
All of the above(*)

13. Which of the following are true? (Choose Two)

(Choose all correct answers)
Mark for Review (1) Point

Date values are enclosed in single quotation marks (*)
Date values are format-sensitive (*)
Character values are not case-sensitive
Character strings are enclosed in double quotation marks

14. Which of the following is true?
Mark for Review (1) Point
Character strings must be enclosed in double quotation marks
Date values are enclosed in single quotation marks (*)
Date values are not format-sensitive
Character values are not case-sensitive

15. You want to retrieve a list of customers whose last names begin with the letters 'Fr' . Which keyword should you include in the WHERE clause of your SELECT statement to achieve the desired result?
Mark for Review (1) Point
LIKE(*)
AND
BETWEEN
IN

Database Programming section 1

1. The _______ clause can be added to a SELECT statement to return a subset of the data.
Mark for Review (1) Point

WHICH
EVERY
WHERE(*)
ANYWHERE

2. All computers in the world speak the same languages, so you only need to learn one programming language - Oracle SQL. True or False?
Mark for Review (1) Point
True
False (*)

3. You cannot use computers unless you completely understand exactly how they work. True or False?
Mark for Review (1) Point
True
False (*)

4. The DESCRIBE command returns all rows from a table. True or False?
Mark for Review (1) Point
True
False (*)

5. What command will return data from the database to you?
Mark for Review (1) Point
RETURN
SELECT(*)
GET
FETCH

6. If you want to see just a subset of the columns in a table, you use what symbol?
Mark for Review (1) Point
&
%
*
None of the above; instead of using a symbol, you name the columns for which you want to see data. (*)

7. In which clause of a SELECT statement would you specify the name of the table or tables being queried?
Mark for Review (1) Point
The FROM clause (*)

The SELECT clause
The WHERE clause
Any of the above options; you can list tables wherever you want in a SELECT statement.

8. Which statement best describes how arithmetic expressions are handled?
Mark for Review (1) Point
Multiplication and subtraction operations are handled before any other operations.
Addition operations are handled before any other operations.
Division and multiplication operations are handled before subtraction and addition operations. (*)

Multiplication and addition operations are handled before subtraction and division operations.

9. Which SQL keyword specifies that an alias will be substituted for a column name in the output of a SQL query?
Mark for Review (1) Point
AS (*)
AND
SUBSTITUTE
OR

10. In a SQL statement, which clause specifies one or more columns to be returned by the query?
Mark for Review (1) Point

SELECT (*)
FROM
WHERE
Any of the above options; you can list columns wherever you want to in a SELECT statement.

11. Every row in a relational database table is unique.
Mark for Review (1) Point
True(*)
False

12. A Relational Database generally contains two or more tables. True or False?
Mark for Review (1) Point
True(*)
False

13. Every time you shop online, it is likely you will be accessing a database. True or False?
Mark for Review (1) Point
True(*)
False

14. Most of the well know Internet search engines use databases to store data. True or False?
Mark for Review (1) Point
True(*)
False

15. The basic storage structure in a Relational Database is a _________:
Mark for Review (1) Point
ROW
KEY
TABLE(*)
FIELD

Java fundamental midterm

Section 2

1. In Alice, objects inherit the characteristics of their:
Class (*)

2. A textual storyboard helps the reader understand the actions that will take place during the animation. True or false?
True (*)

3. In Alice, if a procedure is declared for a clownFish class, which classes can use the procedure?
ClownFish class (*)

4. Alice uses built-in math operators. They are:
All of the above (*)

5. In Alice, which of the following programming statements moves the alien backward the distance to the asteroid, minus 2 meters?
this.Alien move backward {this.Alien getDistanceTo this.Asteroid -2} (*)

6. You have a Class representing Cat. Each Cat can meow, purr, catch mice, and so on. When you create a new cat, what is it called?
An instance (*)

7. Which of the following statements about methods is false?
Classes must be defined directly within a method definition. (*)

8. From your Alice lessons, built-in functions provide precise property details for the following areas:
Proximity, size, spatial relation, and point of view. (*)

9. From your Alice lessons, what is the purpose of nesting?
To add visual structure to your program. (*)

10. Debugging is the process of finding bugs in a software program. True or false?
True (*)

11. A loop can be infinite (continue forever) or conditional (stops upon a condition). True or false?
True (*)

12. When you import a class from another file you have to import the entire class. True or false?
False (*)

13. To save a class to the myClasses directory you do so at the ________ level.
Class (*)

14. Which handle style would be used to rotate an object's sub-part about the x, y, and z axes?
Rotation (*)

15. Which is an example of the Boolean variable type?
True or False (*)

16. In Java code the { } brackets are used to represent what statements?
(Choose all correct answers)
Begin(*)
End(*)

17. In Alice, which of the following is not a control statement?
Move (*)

18. In Alice, a computer program requires functions to tell it how to perform the procedure. True or false?
False (*)

19. In Alice, the setVehicle procedure will associate one object to another. True or false?
True (*)

20. In Alice, where are arithmetic operators available?
(Choose all correct answers)
Amount argument (*)
Duration argument (*)
Get Distance functions (*)



22. What is the purpose of a function in Alice?

To compute and answer a question about an object. (*)

23. Which of the following elements of the Alice animation should be tested before the animation is considered complete?
All of the above. (*)

24. The Alice animation should be tested throughout development, not just at the end of the animation's development. True or false?
True (*)

Section 3

25. To execute a method in your Greenfoot game, where is it called from?
The act method (*)

26. Which one of the following can be used to detect when 2 actors collide?
isTouching() (*)

27. The list below displays components of the Greenfoot source code editor except one. Which one
should be removed?
Instance creator (*)

28. From your Greenfoot lessons, where do you review a class's inherited methods?
Documentation (*)

29. In Greenfoot, you must first create an instance before you create a class. True or false?
False (*)

30. Which of the following are examples of default superclasses that are present in a new Greenfoot scenario?
(Choose all correct answers)
World (*)
Actor (*)

31. Using the Greenfoot IDE, when is a constructor automatically executed?
When a new instance of the class is created. (*)

32. In Greenfoot, actor constructors can be used to create images or values and assign them to the variables. True or false?
True (*)

33. In Greenfoot, which method is used to add a new instance to a scenario when the world is initialized?
addObject (*)

34. When a Greenfoot code segment is executed in an if-statement, each line of code is executed in sequential order. True or false?
True (*)

35. From your Greenfoot lessons, which of the following comparison operators represents "greater than"?
>(*)

36. From your Greenfoot lessons, which axes define an object's position in a world?
(Choose all correct answers)
Y(*)
X(*)

37. Which of the following comparison operators represents "greater than or equal"?
>= (*)

38. Which of the following features of Greenfoot will locate syntax errors in your program?
Compilation (*)

39. When designing a game in Greenfoot, it helps to define the actions that will take place in a textual storyboard. True or false?
True (*)

40. In Greenfoot, a local variable is declared at the beginning of a class. True or false?
False (*)

41. Which of the following is an example of string concatenation?
Instead of entering ".png" after each image file name, add + ".png" after the imageName value in the programming statement. (*)

42. In Greenfoot, when is a local variable most often used?
Within loop constructs (*)

43. An array is an object that holds multiple methods. True or false?
False (*)

44. In Greenfoot, you can only interact with the scenario using a keyboard.
False (*)

45. Greenfoot has tools to record sound. True or false?
True (*)

46. In Java what is casting?
When you take an Object of one particular type and turn it into another Object type. (*)

47. Using the Greenfoot IDE, only five instances can be added to a scenario. True or false?
False (*)

48. In Greenfoot, which of the following options are not possible when associating an image file with an instance?
Add a video (*)

49. In Greenfoot, methods can be called in the act method. When the Act button is clicked in the environment, the methods in the method body of the act method are executed. True or false?
True (*)

50. In the Greenfoot IDE, an instance's position is on the x and y coordinates. True or false?
True (*)

Section 2

1. In Alice, when using the getDistanceTo function what menu option would you use to subtract a set value from the distance?
Math (*)

2. From your Alice lessons, if you examined a science process that had many steps, which of the following is a way that you could apply functional decomposition to this process?

1. Identify the high level steps for the science concept.
2. Further refine and define the tasks needed for each high level step.
3. Present the problem as an animation. (*)

3. When presenting your Alice animation, it is not important to give the audience a reason to listen to the presentation. True or false?
False (*)

4. Identify an example of an Alice expression.
12 + 15 = 27 (*)

5. Alice uses built-in math operators; they are:
All of the above (*)

6. From your Alice lessons, what does the Count control statement do?
Executes statements a specific number of times. (*)

7. Which of the following is not a relational operator?
//(*)

8. In Java, which symbol is used to assign one value to another?
==(*)

9. When you import a class from another file you have to import the entire class. True or false?
False(*)

10. An event is any action initiated by the user that is designed to influence the programﾒs execution during play.
True(*)

11. What do lines 9 and 11 do in the following code
Accept user input and store them in the variables num1 and num2. (*)

12. Main is an example of what in the following code?

public static void main (String[] args) {
System.out.println{"Hello World!");
}
A method (*)

13. A flowchart is a useful way to illustrate how your Alice animation's characters will look. True or false?
False (*)

14. From your Alice lessons, a flowchart could be created in a software program, or documented in a journal. True or false?
True(*)

15. From your Alice lessons, what does inheritance mean?
Each subclass inherits the methods and properties of its superclass. (*)

16. What do moving objects provide to your scene?
The action (*)

17. A conditional loop is a loop that will continue forever. True or false?
False (*)

18. Which of the following actions would require a control statement to control animation timing?
(Choose all correct answers)
A bird flying. (*)
A fish swimming. (*)
 biped object walking. (*)

19. In Alice, which of the following is not a control statement?
Move (*)

20. In Alice, which control statement is used to invoke simultaneous movement?
Do Together (*)

Java Fundamental Section 7

1. Static classes can't return instances of the parent class when the parent class uses a private constructor. True or false?

False (*)

2. Static methods can't act like "setter" methods. True or false?

False (*)

3. Static methods can read static variables. True or false?

True (*)

4. What is Polymorphism?

The concept that a variable or reference can hold multiple types of objects. (*)

5. Would this code be correct if a Dog is a HousePet? Why or Why not? 

HousePet Scooby = new Dog();

Yes, because polymorphism allows this since Dog is a subclass of HousePet. (*)

6. Why would a programmer use polymorphism rather than sticking to a standard array?

Because arrays only work using the same object type and polymorphism provides a way around this. (*)

7. The constructor of a class has the same name as the class. True or false?

True (*)

8. Which of the following may be part of a class definition?

all of the above (*)

9. What operator do you use to call an object's constructor method and create a new object?

New (*)

10. An access modifier is a keyword that allows subclasses to access methods, data, and constructors from their parent class. True or false?

True (*)

11. Which of the following is the correct way to call an overriden method needOil() of a super class Robot in a subclass SqueakyRobot?

super.needOil(); (*)

12. What is the Java Applet?

(Choose all correct answers)

A web-based Java program that is embedded into a web browser. (*)

A graphic visual included in Java. (*)

13. Which of the following is a possible way to overload constructors?

 

14. Which of the following could be a reason to return an object?

The method makes changes to the object and you wish to continue to use the updated object outside of the method. (*)

15. Consider the following:

There is a method A that calls method B. Method B is a variable argument method. 

With this, which of the following are true?

(Choose all correct answers)

Method A can invoke method B twice, each time with a different number of arguments. (*)

When invoked, method B creates an array to store some or all of the arguments passed to it from method A. (*)

1. It is possible to inherit from an abstract class. True or false?

True (*)

2. If a class is immutable then it must be abstract. True or false?

False (*)

3. Which of the following would be most beneficial for this scenario?

Joe is a college student who has a tendency to lose his books. Replacing them is getting costly. In an attempt to get organized, Joe wants to create a program that will store his textbooks in one group of books, but he wants to make each book type the subject of the book (i.e. MathBook is a book). How could he store these different subject books into a single array?

Using polymorphism. (*)

4. Static classes can exist as stand alone classes. True or false?

False (*)

5. Public static variables can't have their value reset by other classes. True or false?

False (*)

6. A static variable is always publicly available. True or false?

False (*)

7. Which of the following correctly defines overloading?

Having more than one constructor with the same name but different arguments. (*)

8. A team is working on a coding project. They desire that all portions of their code should have access to the classes that they write. What access modifier should be used for each class?

Public (*)

9. It is possible to overload a method that is not a constructor. True or False?

True (*)

 

10. What value will be returned when the setValue method is called?

37(*)

11. A class can only have one constructor. True or false?

False (*)

12. The following statement compiles and executes. What do you know for certain? 

tree.grows(numFeet);

grows must be the name of a method. (*)

13. Why is it not possible to extend more than one class at a time in an inheritance hierarchy chain?

It is not necessary considering all public content is passed from super class to subclass and further to their subclass and that subclass' subclass and so on. (*)

14. Which of the following correctly describes an "is-a" relationship?

A helpful term used to conceptualize the relationships among nodes or leaves in an inheritance hierarchy. (*)

15. It is possible for a subclass to be a superclass. True or false?

True (*)

Java fundamental section 6

1. The following array declaration is valid. True or false?

int[] y = new int[5];
True (*)

2. double array[] = new double[8];

After execution of this statement, which of the following are true?
array.length is 8 (*)

3. The following array declaration is valid:

int[] y = new int[5];
True (*)

4. The following creates a reference in memory named z that can refer to seven different doubles via an index. True or false?

double z[] = new double[7];
True (*)

5. The following segment of code initializes a 2 dimensional array of primitive data types. True or false?

double[][] a=new double[4][5];
True (*)

6. Which of the following statements print every element of the one dimensional array prices to the screen?
for(int i=0; i < prices.length; i++){System.out.println(prices[i]);} (*)

7. The following segment of code initializes a 2 dimensional array of references. True or false?

String[][] array={{"a", "b", "C"},{"a", "b", "c"}};
True (*)

8. Which of the following statements adds 5 to every element of the one dimensional array prices and then prints it to the screen?

for(int i=0;i<prices.length;i++)
System.out.println(prices[i]+5); (*)

9. What is the output of the following segment of code if the command line arguments are "a b c d e f"?

6 (*)

10. The following array declaration is valid. True or false?
int k[] = new int[10];
True (*)

11. If an exception is thrown by a method, where can the catch for the exception be?
The catch can be in the method that threw the exception or in any other method that called the method that threw the exception. (*)

12. Which of the following could be a reason to throw an exception?
To eliminate exceptions from disrupting your program. (*)

13. Choose the best response to this statement: An error can be handled by throwing it and catching it just like an exception.
False. An error is much more severe than an exception and cannot be dealt with adequately in a program. (*)

14. A computer company has one million dollars to give as a bonus to the employees, and they wish to distribute it evenly amongst them.

The company writes a program to calculate the amount each employee receives, given the number of employees.

Unfortunately, the employees all went on strike before they heard about the bonus. This means that the company has zero employees.

What will happen to the program if the company enters 0 into the employment number?
(Choose all correct answers)

An exception will occur because it is not possible to divide by zero. (*)

15.  What are exceptions used for in Java?
Correcting mistakes made in your code and handling extraordinary cases. (*)


1.  Which line of code shows the correct way to throw an exception?
throw new Exception("Array index is out of bounds"); (*)

2. It is possible to throw and catch a second exception inside a catch block of code. True or false?
True (*)

3. What exception message indicates that a variable may have been mispelled somewhere in the program?
variableName cannot be resolved to a variable (*)

4. Choose the best response to this statement: An error can be handled by throwing it and catching it just like an exception.
False. An error is much more severe than an exception and cannot be dealt with adequately in a program. (*)

5. A computer company has one million dollars to give as a bonus to the employees, and they wish to distribute it evenly amongst them.

The company writes a program to calculate the amount each employee receives, given the number of employees.

Unfortunately, the employees all went on strike before they heard about the bonus. This means that the company has zero employees.

What will happen to the program if the company enters 0 into the employment number?
(Choose all correct answers)
An exception will occur because it is not possible to divide by zero. (*)

6. Which of the following declares and initializes a two dimensional array that can hold 6 Object reference types?
Object[][] array=new Object[2][3]; (*)

7. What is the Output of the following segment of code?

666666 (*)

8. The following creates a reference in memory named k that can refer to six different integers via an index. True or false?

int k[]= int[6];
False (*)

9. Which of the following declares a one dimensional array name scores of type int that can hold 14 values?

int[] scores=new int[14]; (*)

10. What is the output of the following segment of code?

int num[]={9,8,7,6,5,4,3,2,1};
for(int i=0;i<9;i=i+3)
System.out.print(num[i]);
963 (*)

11. The following creates a reference in memory named z that can refer to seven different doubles via an index. True or false?

double z[] = new double[7];
True (*)

12. The following array declaration is valid:

int[] y = new int[5];
True (*)

13. Which of the following declares and initializes a one dimensional array that can hold 5 Object reference types?
Object array=new Object[5]; (*)

Java Fundamental Section 5

1.  What will print if the following Java code is executed?

if ((5.1 > 4.3 && 6.2 < 8.4) && !(7.2 < 3.5 || 1.2 == 2.1 || 2.2 != 2.25))
System.out.print("TRUE");
else
System.out.print("FALSE");
False (*)

2.  What is the difference between the symbols = and == ?
The = is use to assign values to variables and the == compares values. (*)

3.  Consider that a Scanner has beeLabel
n initialized such that:

Scanner in = new Scanner(System.in);

Which of the following lines of code reads in the userﾒs input and sets it equal to a new String called input?
String input = in.next(); (*)

4.  In Java, each case seqment of a switch statement requires the keyword break to avoid "falling through".
True (*)

5.  Which of the following could be a reason to use a switch statement in a Java program?
Because it allows the program to run certain segments of code and neglect to run others based on the input given. (*)

6.  Which of the following correctly matches the switch statement keyword to its function?
(Choose all correct answers)
default: signals what code to execute if the input does not match any of the cases (*)

case: signals what code is executed if the user input matches the specified element (*)
switch: identifies what element will be compared to the element of the case statements to find a possible match (*)

7.  The three logic operators in Java are:

&&, ||, ! (*)

8.  The following code fragment properly implements the switch statement. True or false?

default(input)
switch '+':
answer+=num;
break;
case '-':
answer-=num;
break;
!default
System.out.println("Invalid input");
False (*)

9.  What is a loop?
A set of logic that is repeatedly executed until a certain condition is met. (*)

10.  One advantage to using a while loop over a for loop is that a while loop always has a counter. True or false?
False (*)

11.  The syntax below represents a valid initialization of a for loop counter. True or False?

public class ForLoop {
 public static void main (String args[])
 {
  for (int i=10; i <20; i++)
  {System.out.println("i: "+i); }
 }
}
True (*)

12.  What is one significant difference between a while loop and a do-while loop?
A DO-WHILE loop will always execute the code at least once, even if the conditional statement for the WHILE is never true. A WHILE loop is only executed if the conditional statement is true. (*)

13.  Identify which situation could be an example of a while loop.
All of the above. (*)

14.  Which of the following are types of loops in Java?

(Choose all correct answers)
while (*)
for (*)
do-while (*)

15.  In a for loop, the counter is automatically incremented after each loop iteration. True or False?
False (*)

1.  In an if-else construct, the condition to be evaluated must be contained within parentheses. True or False?
True (*)

2.  Which of the following are relational operators in Java?

(Choose all correct answers)
< (*)
<= (*)
!= (*)

3.  Which of the following correctly initializes an instance of Scanner, called "in", that reads input from the console screen?

Scanner in = new Scanner(System.in); (*)

4.  Which of the following correctly matches the switch statement keyword to its function?
(Choose all correct answers)
default: signals what code to execute if the input does not match any of the cases (*)
switch: identifies what element will be compared to the element of the case statements to find a possible match (*)
case: signals what code is executed if the user input matches the specified element (*)

5.  Which of the two diagrams below illustratethe correct syntax
for variables used in an if-else statement?

Example A(*)

6.  How would you use the ternary operator to rewrite this if statement?

if (gender == "male")
System.out.print("Mr.");
else
System.out.print("Ms.");
System.out.print( (gender == "male") ? "Mr." : "Ms." ); (*)

7.  In an if-else construct the condition to be evaluated must end with a semi-colon. True or false?
False (*)

8.  This keyword is used to instruct specific code when the input for a switch statement that does not match any of the cases.
Default(*)

9.  What is the output of the following code segment?

int num = 7;
while(num >= 0)
{
num -= 3;
}
System.out.println(num);
-2 (*)

10.  What should replace the comment "//your answer here" in the code below if the code is meant to take no action when i % 2 is 0 (in other words when i is even)?

for(int i = 0; i < 10; i++){
if(i%2 == 0)
//your answer here
else
k+=3;
}
Continue (*)

11.  What is one significant difference between a while loop and a do-while loop?
A DO-WHILE loop will always execute the code at least once, even if the conditional statement for the WHILE is never true. A WHILE loop is only executed if the conditional statement is true. (*)

12.  A counter used in a for loop cannot be initialized within the For loop statement. True or False?
False (*)

13.  In a for loop the counter is not automatically incremented after each loop iteration. Code must be written to increment the counter. True or false?
True (*)

14.  One advantage to using a while loop over a for loop is that a while loop always has a counter. True or false?
False (*)

15.  When the for loop condition statement is met the construct is exited. True or false?
False (*)