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 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 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 (*)