SQL Programming #16

Oracle Database Programming with SQL Section 16 1.         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 =…

SQL Programming #15

Oracle Database Programming with SQL Section 15 1.         Regular expressions used as check constraints are another way to ensure data is formatted correctly prior to being written into the database table. True or False? ♦True (*) ♦False 2.         Regular expressions are a method of describing both simple and complex patterns for searching and manipulating. True…

SQL Programming #14

Oracle Database Programming with SQL Section 14 1.         Sequences can be used to: (Choose three) (Choose all correct answers) ♦Generate a range of numbers and optionally cycle through them again (*) ♦Set a fixed interval between successively generated numbers. (*) ♦Ensure primary key values will be unique and consecutive ♦Guarantee that no primary key values…

SQL Programming #13

Oracle Database Programming with SQL Section 13 1.            Evaluate this CREATE VIEW statement: CREATE VIEW emp_view AS SELECT SUM(salary) FROM employees; Which statement is true? ♦You cannot update data in the EMPLOYEES table using the EMP_VIEW view. (*) ♦You can update any data in the EMPLOYEES table using the EMP_VIEW view. ♦You can update only…

SQL Programming #12

Oracle Database Programming with SQL Section 12 1.         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…

SQL Programming #11

Oracle Database Programming with SQL Section 11 1.         To store time with fractions of seconds, which datatype should be used for a table column? ♦DATE ♦INTERVAL DAY TO SECOND ♦INTERVAL YEAR TO MONTH ♦TIMESTAMP (*) 2.         The ELEMENTS column is defined as: NUMBER(6,4) How many digits to the right of the decimal point are allowed…

SQL Programming #9

Oracle Database Programming with SQL Section 9 1.         A multiple-row operator expects how many values? ♦One or more (*) ♦Only one ♦Two or more ♦None 2.         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…

SQL Programming #8

Oracle Database Programming with SQL Section 8 1.         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? ♦HAVING ♦ROLLUP ♦CUBE (*) ♦GROUP BY ALL COLUMNS 2.         Examine the following statement: SELECT…

SQL Programming #7

Oracle Database Programming with SQL Section 7 1. The AVG, SUM, VARIANCE, and STDDEV functions can be used with which of the following? ♦All except numeric ♦Only numeric data types (*) ♦Integers only ♦Any data type 2. Which aggregate function can be used on a column of the DATE data type? ♦MAX (*) ♦STDDEV ♦AVG…

SQL Programming #6

Oracle Database Programming with SQL Section 6 1.         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? ♦FROM employees e, departments d ♦SELECT e.employee_id, e.last_name, e.first_name, d.department_name ♦ORDER BY 4; ♦WHERE e.department_id…

SQL Programming #5

Oracle Database Programming with SQL Section 5 1.         Given the following descriptions of the employees and jobs tables, which of the following scripts will display each employee?s possible minimum and maximum salaries based on their job title? EMPLOYEES Table: Name   Null?    Type EMPLOYEE_ID          NOT NULL     NUMBER (6) FIRST_NAME             VARCHAR2 (20) LAST_NAME  NOT NULL     VARCHAR2…

SQL Programming #4

Oracle Database Programming with SQL Section 4 1.            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 (*) ♦False 2.            For the given data from Employees (last_name, manager_id) what is the result of the following statement: DATA:( King, null…