Pastebin launched a little side project called VERYVIRAL.com, check it out ;-) Want more features on Pastebin? Sign Up, it's FREE!
Guest

Untitled

By: a guest on Nov 29th, 2010  |  syntax: None  |  size: 26.84 KB  |  views: 32,504  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. est: Mid Term Exam Semester 2 - Part II
  2. Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
  3.         Semester 2 Mid Term Exam Part II covers Sections 5-7 of Database Programming with SQL. 
  4.         Section 5
  5.                                        
  6.                 1.      You use GROUPING function to:   Mark for Review
  7. (1) Points
  8.                                        
  9.                        
  10.         Produce subtotal and cross-tabulated values
  11.        
  12.                        
  13.         Identify the extra row values created by either a ROLLUP or CUBE operation (*)
  14.        
  15.                        
  16.         Aggregate rows using SUM, MIN, MAX, and COUNT
  17.        
  18.                                        
  19.                                
  20. Correct         Correct
  21.        
  22.                                        
  23.                 2.      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?     Mark for Review
  24. (1) Points
  25.                                        
  26.                        
  27.         True (*)
  28.        
  29.                        
  30.         False
  31.        
  32.                                        
  33.                                
  34. Incorrect               Incorrect. Refer to Section 5 Lesson 2.
  35.        
  36.                                        
  37.                 3.      You use GROUPING function to ______ database rows from tabulated rows.  Mark for Review
  38. (1) Points
  39.                                        
  40.                        
  41.         CREATE
  42.        
  43.                        
  44.         DISTINGUISH (*)
  45.        
  46.                        
  47.         COMPUTE
  48.        
  49.                        
  50.         COUNT
  51.        
  52.                                        
  53.                                
  54. Incorrect               Incorrect. Refer to Section 5 Lesson 2.
  55.        
  56.                                        
  57.                 4.      CUBE will cross-reference the columns listed in the ______ clause to create a superset of groups.       Mark for Review
  58. (1) Points
  59.                                        
  60.                        
  61.         GROUP BY (*)
  62.        
  63.                        
  64.         WHERE
  65.        
  66.                        
  67.         SELECT
  68.        
  69.                                        
  70.                                
  71. Correct         Correct
  72.        
  73.                                        
  74.                 5.      Group functions can be nested to a depth of?    Mark for Review
  75. (1) Points
  76.                                        
  77.                        
  78.         Three
  79.        
  80.                        
  81.         Four
  82.        
  83.                        
  84.         Two (*)
  85.        
  86.                        
  87.         Group functions cannot be nested.
  88.        
  89.                                        
  90.                                
  91. Correct         Correct
  92.        
  93.                                        
  94.                 6.      The PRODUCTS table contains these columns:
  95.  
  96. PROD_ID NUMBER(4)
  97. PROD_NAME VARCHAR(20)
  98. PROD_CAT VARCHAR2(15)
  99. PROD_PRICE NUMBER(5)
  100. PROD_QTY NUMBER(4)
  101.  
  102. You need to identify the minimum product price in each product category.
  103. Which statement could you use to accomplish this task?
  104.         Mark for Review
  105. (1) Points
  106.                                        
  107.                        
  108.         SELECT prod_cat, MIN (prod_price)
  109. FROM products
  110. GROUP BY prod_price;
  111.  
  112.        
  113.                        
  114.         SELECT prod_cat, MIN (prod_price)
  115. FROM products
  116. GROUP BY prod_cat;
  117.  
  118. (*)
  119.        
  120.                        
  121.         SELECT MIN (prod_price), prod_cat
  122. FROM products
  123. GROUP BY MIN (prod_price), prod_cat;
  124.  
  125.        
  126.                        
  127.         SELECT prod_price, MIN (prod_cat)
  128. FROM products
  129. GROUP BY prod_cat;
  130.  
  131.        
  132.                                        
  133.                                
  134. Incorrect               Incorrect. Refer to Section 5 Lesson 1.
  135.        
  136.                                        
  137.                 7.      Evaluate this SELECT statement:
  138.  
  139. SELECT COUNT(emp_id), mgr_id, dept_id
  140. FROM employees
  141. WHERE status = 'I'
  142. GROUP BY dept_id
  143. HAVING salary > 30000
  144. ORDER BY 2;
  145.  
  146. Why does this statement return a syntax error?
  147.         Mark for Review
  148. (1) Points
  149.                                        
  150.                        
  151.         MGR_ID must be included in the GROUP BY clause. (*)
  152.        
  153.                        
  154.         The HAVING clause must specify an aggregate function.
  155.        
  156.                        
  157.         A single query cannot contain a WHERE clause and a HAVING clause.
  158.        
  159.                        
  160.         The ORDER BY clause must specify a column name in the EMPLOYEE table.
  161.        
  162.                                        
  163.                                
  164. Incorrect               Incorrect. Refer to Section 5 Lesson 1.
  165.        
  166.                                        
  167.                 8.      Which statement about group functions is true?  Mark for Review
  168. (1) Points
  169.                                        
  170.                        
  171.         Group functions ignore null values. (*)
  172.        
  173.                        
  174.         Group functions can only be used in a SELECT list.
  175.        
  176.                        
  177.         Group functions can be used in a WHERE clause.
  178.        
  179.                        
  180.         A query that includes a group function in the SELECT list must include a GROUP BY clause.
  181.        
  182.                                        
  183.                                
  184. Incorrect               Incorrect. Refer to Section 5 Lesson 1.
  185.        
  186.                                        
  187.                 9.      The EMPLOYEES table contains the following columns:
  188.  
  189. EMPLOYEE_ID NUMBER(10) PRIMARY KEY
  190. LAST_NAME VARCHAR2(20)
  191. FIRST_NAME VARCHAR2(20)
  192. DEPARTMENT VARCHAR2(20)
  193. HIRE_DATE DATE
  194. SALARY NUMBER(10)
  195.  
  196. You want to create a report that includes each employee's last name, employee identification number, date of hire and salary. The report should include only those employees who have been with the company for more than one year and whose salary exceeds $40,000.
  197.  
  198. Which of the following SELECT statements will accomplish this task?
  199.         Mark for Review
  200. (1) Points
  201.                                        
  202.                        
  203.         SELECT employee_id, last_name, salary
  204. FROM employees
  205. WHERE salary > 40000
  206. AND hire_date =
  207.    (SELECT hire_date
  208.     FROM employees
  209.     WHERE (sysdate-hire_date) / 365 > 1);
  210.  
  211.        
  212.                        
  213.         SELECT employee_id, last_name, hire_date, salary
  214. FROM employees
  215. WHERE salary > 40000 AND hire_date =
  216.     (SELECT hire_date
  217.     FROM employees
  218.     WHERE (sysdate-hire_date) / 365 > 1);
  219.  
  220.        
  221.                        
  222.         SELECT employee_id, last_name, hire_date, salary
  223. FROM employees
  224. WHERE salary > 40000
  225. AND (sysdate-hire_date) / 365 > 1;
  226.  
  227. (*)
  228.        
  229.                        
  230.         SELECT employee_id, last_name, salary
  231. FROM employees
  232. WHERE salary > 40000
  233. AND hire_date IN (sysdate-hire_date) / 365 > 1);
  234.        
  235.                                        
  236.                                
  237. Incorrect               Incorrect. Refer to Section 5 Lesson 1.
  238.        
  239.                                        
  240.                 10.     What will the following SQL Statement do?
  241. SELECT job_id, COUNT(*)
  242. FROM employees
  243. GROUP BY job_id;
  244.  
  245.         Mark for Review
  246. (1) Points
  247.                                        
  248.                        
  249.         Displays all the employees and groups them by job.
  250.        
  251.                        
  252.         Displays each job id and the number of people assigned to that job id. (*)
  253.        
  254.                        
  255.         Displays only the number of job_ids.
  256.        
  257.                        
  258.         Displays all the jobs with as many people as there are jobs.
  259.        
  260.                                        
  261.                                
  262. Incorrect               Incorrect. Refer to Section 5 Lesson 1.
  263.  
  264. Section 5
  265.                                        
  266.                 11.     Which statement about the GROUP BY clause is true?      Mark for Review
  267. (1) Points
  268.                                        
  269.                        
  270.         To exclude rows before dividing them into groups using the GROUP BY clause, you should use a WHERE clause. (*)
  271.        
  272.                        
  273.         You can use a column alias in a GROUP BY clause.
  274.        
  275.                        
  276.         By default, rows are not sorted when a GROUP BY clause is used.
  277.        
  278.                        
  279.         You must use the HAVING clause with the GROUP BY clause.
  280.        
  281.                                        
  282.                                
  283. Incorrect               Incorrect. Refer to Section 5 Lesson 1.
  284.        
  285.                                        
  286.                 12.     The PLAYERS and TEAMS tables contain these columns:
  287.  
  288. PLAYERS
  289. PLAYER_ID NUMBER NOT NULL, PRIMARY KEY
  290. LAST_NAME VARCHAR2 (30) NOT NULL
  291. FIRST_NAME VARCHAR2 (25) NOT NULL
  292. TEAM_ID NUMBER
  293. POSITION VARCHAR2 (25)
  294.  
  295. TEAMS
  296. TEAM_ID NUMBER NOT NULL, PRIMARY KEY
  297. TEAM_NAME VARCHAR2 (25)
  298.  
  299. You need to create a report that lists the names of each team with more than three goal keepers.
  300. Which SELECT statement will produce the desired result?
  301.         Mark for Review
  302. (1) Points
  303.                                        
  304.                        
  305.         SELECT t.team_name, COUNT(p.player_id)
  306. FROM players p, teams t
  307. ON (p.team_id = t.team_id)
  308. WHERE UPPER(p.position) = 'GOAL KEEPER'
  309. GROUP BY t.team_name;
  310.  
  311.        
  312.                        
  313.         SELECT t.team_name, COUNT(p.player_id)
  314. FROM players
  315. JOIN teams t ON (p.team_id = t.team_id)
  316. WHERE UPPER(p.position) = 'GOAL KEEPER'
  317. HAVING COUNT(p.player_id) > 3;
  318.  
  319.        
  320.                        
  321.         SELECT t.team_name, COUNT(p.player_id)
  322. FROM players p, teams t
  323. ON (p.team_id = t.team_id)
  324. WHERE UPPER(p.position) = 'GOAL KEEPER'
  325. GROUP BY t.team_name
  326. HAVING COUNT(p.player_id) > 3;
  327.  
  328.        
  329.                        
  330.         SELECT t.team_name, COUNT(p.player_id)
  331. FROM players p
  332. JOIN teams t ON (p.team_id = t.team_id)
  333. WHERE UPPER(p.position) = 'GOAL KEEPER'
  334. GROUP BY t.team_name
  335. HAVING COUNT(p.player_id) > 3;
  336.  
  337. (*)
  338.        
  339.                                        
  340.                                
  341. Incorrect               Incorrect. Refer to Section 5 Lesson 1.
  342.        
  343.                                        
  344.                 13.     The PAYMENT table contains these columns:
  345.  
  346. PAYMENT_ID NUMBER(9) PK
  347. PAYMENT_DATE DATE
  348. CUSTOMER_ID NUMBER(9)
  349.  
  350. Which SELECT statement could you use to display the number of times each customer payment was made between January 1, 2003 and June 30, 2003 ?
  351.         Mark for Review
  352. (1) Points
  353.                                        
  354.                        
  355.         SELECT customer_id, COUNT(payment_id)
  356. FROM payment
  357. WHERE payment_date BETWEEN '01-JAN-2003' AND '30-JUN-2003'
  358. GROUP BY customer_id;
  359.  
  360. (*)
  361.        
  362.                        
  363.         SELECT COUNT(payment_id)
  364. FROM payment
  365. WHERE payment_date BETWEEN '01-JAN-2003' AND '30-JUN-2003';
  366.  
  367.        
  368.                        
  369.         SELECT customer_id, COUNT(payment_id)
  370. FROM payment
  371. WHERE payment_date BETWEEN '01-JAN-2003' AND '30-JUN-2003';
  372.  
  373.        
  374.                        
  375.         SELECT COUNT(payment_id)
  376. FROM payment
  377. WHERE payment_date BETWEEN '01-JAN-2003' AND '30-JUN-2003'
  378. GROUP BY customer_id;
  379.  
  380.        
  381.                                        
  382.                                
  383. Incorrect               Incorrect. Refer to Section 5 Lesson 1.
  384.        
  385.                                        
  386.                 14.     You want to write a report that returns the average salary of all employees in the company, sorted by departments.
  387. The EMPLOYEES table contains the following columns:
  388.  
  389. EMPLOYEES:
  390. EMP_ID NUMBER(10) PRIMARY KEY
  391. LNAME VARCHAR2(20)
  392. FNAME VARCHAR2(20)
  393. DEPT VARCHAR2(20)
  394. HIRE_DATE DATE
  395. SALARY NUMBER(10)
  396.  
  397. Which SELECT statement will return the information that you require?
  398.         Mark for Review
  399. (1) Points
  400.                                        
  401.                        
  402.         SELECT salary (AVG)
  403. FROM employees
  404. GROUP BY dept;
  405.  
  406.        
  407.                        
  408.         SELECT AVG (salary)
  409. FROM employees
  410. GROUP BY dept;
  411.  
  412. (*)
  413.        
  414.                        
  415.         SELECT AVG (salary)
  416. FROM employees
  417. BY dept;
  418.  
  419.        
  420.                        
  421.         SELECT AVG salary
  422. FROM employees
  423. BY dept;
  424.  
  425.        
  426.                                        
  427.                                
  428. Incorrect               Incorrect. Refer to Section 5 Lesson 1.
  429.        
  430.                                        
  431.                 15.     Evaluate this SELECT statement:
  432. SELECT MAX(salary), department_id
  433. FROM employees
  434. GROUP BY department_id;
  435.  
  436. Which values are displayed?
  437.         Mark for Review
  438. (1) Points
  439.                                        
  440.                        
  441.         The highest salary for all employees.
  442.        
  443.                        
  444.         The highest salary in each department. (*)
  445.        
  446.                        
  447.         The employees with the highest salaries.
  448.        
  449.                        
  450.         The employee with the highest salary for each department.
  451.        
  452.                                        
  453.                                
  454. Correct         Correct
  455.        
  456.                                        
  457.                 16.     Evaluate this statement:
  458.  
  459. SELECT department_id, AVG(salary)
  460. FROM employees
  461. WHERE job_id <> 69879
  462. GROUP BY job_id, department_id
  463. HAVING AVG(salary) > 35000
  464. ORDER BY department_id;
  465.  
  466. Which clauses restricts the result? Choose two.
  467.         Mark for Review
  468. (1) Points
  469.                                        
  470.                         (Choose all correct answers)   
  471.                                        
  472.                        
  473.         SELECT department_id, AVG(salary)
  474.        
  475.                        
  476.         WHERE job_id <> 69879 (*)
  477.        
  478.                        
  479.         GROUP BY job_id, department_id
  480.        
  481.                        
  482.         HAVING AVG(salary) > 35000 (*)
  483.        
  484.                                        
  485.                                
  486. Incorrect               Incorrect. Refer to Section 5 Lesson 1.
  487.        
  488.                                        
  489.                 17.     The EMPLOYEES table contains these columns:
  490.  
  491. ID_NUMBER NUMBER Primary Key
  492. NAME VARCHAR2 (30)
  493. DEPARTMENT_ID NUMBER
  494. SALARY NUMBER (7,2)
  495. HIRE_DATE DATE
  496.  
  497. Evaluate this SQL statement:
  498.  
  499. SELECT id_number, name, department_id, SUM(salary)
  500. FROM employees
  501. WHERE salary > 25000
  502. GROUP BY department_id, id_number, name
  503. ORDER BY hire_date;
  504.  
  505. Why will this statement cause an error?
  506.         Mark for Review
  507. (1) Points
  508.                                        
  509.                        
  510.         The HAVING clause is missing.
  511.        
  512.                        
  513.         The WHERE clause contains a syntax error.
  514.        
  515.                        
  516.         The SALARY column is NOT included in the GROUP BY clause.
  517.        
  518.                        
  519.         The HIRE_DATE column is NOT included in the GROUP BY clause. (*)
  520.        
  521.                                        
  522.                                
  523. Incorrect               Incorrect. Refer to Section 5 Lesson 1.
  524.        
  525.                                        
  526.                 18.     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. Mark for Review
  527. (1) Points
  528.                                        
  529.                        
  530.         ONCE; FIRST
  531.        
  532.                        
  533.         TWICE; FIRST
  534.        
  535.                        
  536.         ONCE; LAST (*)
  537.        
  538.                        
  539.         IN ALL; LAST
  540.        
  541.                                        
  542.                                
  543. Incorrect               Incorrect. Refer to Section 5 Lesson 3.
  544.        
  545.                                        
  546.                 19.     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?    Mark for Review
  547. (1) Points
  548.                                        
  549.                        
  550.         True (*)
  551.        
  552.                        
  553.         False
  554.        
  555.                                        
  556.                                
  557. Correct         Correct
  558.        
  559.                                        
  560.                                        
  561.         Section 6
  562.                                        
  563.                 20.     Evaluate this SQL statement:
  564.  
  565. SELECT employee_id, last_name, salary
  566. FROM employees
  567. WHERE department_id IN
  568.    (SELECT department_id
  569.    &nbspFROM employees
  570.    &nbspWHERE salary > 30000 AND salary < 50000);
  571.  
  572. Which values will be displayed?
  573.         Mark for Review
  574. (1) Points
  575.                                        
  576.                        
  577.         Only employees who earn more than $30,000.
  578.        
  579.                        
  580.         Only employees who earn less than $50,000.
  581.        
  582.                        
  583.         All employees who work in a department with employees who earn more than $30,000 and more than $50,000.
  584.        
  585.                        
  586.         All employees who work in a department with employees who earn more than $30,000, but less than $50,000. (*)
  587.        
  588.                                        
  589.                                
  590. Incorrect               Incorrect. Refer to Section 6 Lesson 3.
  591.  
  592. Section 6
  593.                                        
  594.                 21.     You are looking for Executive information using a subquery.
  595. What will the following SQL statement display?
  596.  
  597. SELECT department_id, last_name, job_id
  598. FROM employees
  599. WHERE department_id IN
  600.    (SELECT department_id FROM departments WHERE department_name = 'Executive');
  601.         Mark for Review
  602. (1) Points
  603.                                        
  604.                        
  605.         The department ID, department name and last name for every employee in the Executive department.
  606.        
  607.                        
  608.         The department ID, last name, department name for every Executive in the employees table.
  609.        
  610.                        
  611.         The department ID, last name, job ID from departments for Executive employees.
  612.        
  613.                        
  614.         The department ID, last name, job ID for every employee in the Executive department. (*)
  615.        
  616.                                        
  617.                                
  618. Incorrect               Incorrect. Refer to Section 6 Lesson 3.
  619.        
  620.                                        
  621.                 22.     Evaluate this SELECT statement:
  622.  
  623. SELECT player_id, name
  624. FROM players
  625. WHERE team_id IN
  626.    (SELECT team_id
  627.    &nbspFROM teams
  628.    &nbspWHERE team_id > 300 AND salary_cap > 400000);
  629.  
  630. What would happen if the inner query returned a NULL value?
  631.         Mark for Review
  632. (1) Points
  633.                                        
  634.                        
  635.         No rows would be returned by the outer query. (*)
  636.        
  637.                        
  638.         A syntax error in the outer query would be returned.
  639.        
  640.                        
  641.         A syntax error in the inner query would be returned.
  642.        
  643.                        
  644.         All the rows in the PLAYER table would be returned by the outer query.
  645.        
  646.                                        
  647.                                
  648. Correct         Correct
  649.        
  650.                                        
  651.                 23.     Which statement is false?       Mark for Review
  652. (1) Points
  653.                                        
  654.                        
  655.         The WITH clause retrieves the results of one or more query blocks.
  656.        
  657.                        
  658.         The WITH clause decreases performance. (*)
  659.        
  660.                        
  661.         The WITH clause makes the query simple to read .
  662.        
  663.                        
  664.         Stores the results for the user who runs the query.
  665.        
  666.                                        
  667.                                
  668. Correct         Correct
  669.        
  670.                                        
  671.                 24.     A correlated subquery is evaluated _____ for each row processed by the parent statement.        Mark for Review
  672. (1) Points
  673.                                        
  674.                        
  675.         EVERY TIME
  676.        
  677.                        
  678.         ONCE (*)
  679.        
  680.                        
  681.         COMPLETELY
  682.        
  683.                                        
  684.                                
  685. Incorrect               Incorrect. Refer to Section 6 Lesson 4.
  686.        
  687.                                        
  688.                 25.     Oracle allows you to write named subqueries in one single statement, as long as you start your statement with the keyword WITH. True or False?  Mark for Review
  689. (1) Points
  690.                                        
  691.                        
  692.         True (*)
  693.        
  694.                        
  695.         False
  696.        
  697.                                        
  698.                                
  699. Correct         Correct
  700.        
  701.                                        
  702.                 26.     A correlated subquery will _______ a candidate row from an outer query, _______ the inner query using candidate row value, and _______ values from the inner query to qualify or disqualify the candidate row.  Mark for Review
  703. (1) Points
  704.                                        
  705.                        
  706.         CREATE; EXECUTE; USE
  707.        
  708.                        
  709.         DELETE; UPDATE; INSERT
  710.        
  711.                        
  712.         GET; EXECUTE; USE (*)
  713.        
  714.                        
  715.         ROLLUP; GRANT; DROP
  716.        
  717.                                        
  718.                                
  719. Incorrect               Incorrect. Refer to Section 6 Lesson 4.
  720.        
  721.                                        
  722.                 27.     Which best describes a single-row subquery?     Mark for Review
  723. (1) Points
  724.                                        
  725.                        
  726.         A query that returns only one row from the inner SELECT statement (*)
  727.        
  728.                        
  729.         A query that returns one or more rows from the inner SELECT statement
  730.        
  731.                        
  732.         A query that returns only one column value from the inner SELECT statement
  733.        
  734.                        
  735.         A query that returns one or more column values from the inner SELECT statement
  736.        
  737.                                        
  738.                                
  739. Incorrect               Incorrect. Refer to Section 6 Lesson 2.
  740.        
  741.                                        
  742.                 28.     If a single-row subquery returns a null value and uses the equality comparison operator, what will the outer query return?      Mark for Review
  743. (1) Points
  744.                                        
  745.                        
  746.         No rows (*)
  747.        
  748.                        
  749.         All the rows in the table
  750.        
  751.                        
  752.         A null value
  753.        
  754.                        
  755.         An error
  756.        
  757.                                        
  758.                                
  759. Incorrect               Incorrect. Refer to Section 6 Lesson 2.
  760.        
  761.                                        
  762.                 29.     You need to display all the players whose salaries are greater than or equal to John Brown's salary. Which comparison operator should you use?  Mark for Review
  763. (1) Points
  764.                                        
  765.                        
  766.         =
  767.        
  768.                        
  769.         >
  770.        
  771.                        
  772.         <=
  773.        
  774.                        
  775.         >= (*)
  776.        
  777.                                        
  778.                                
  779. Correct         Correct
  780.        
  781.                                        
  782.                 30.     You need to create a report to display the names of products with a cost value greater than the average cost of all products. Which SELECT statement should you use?    Mark for Review
  783. (1) Points
  784.                                        
  785.                        
  786.         SELECT product_name
  787. FROM products
  788. WHERE cost > (SELECT AVG(cost)
  789. FROM product);
  790.  
  791. (*)
  792.        
  793.                        
  794.         SELECT product_name
  795. FROM products
  796. WHERE cost > AVG(cost);
  797.  
  798.        
  799.                        
  800.         SELECT AVG(cost), product_name
  801. FROM products
  802. WHERE cost > AVG(cost)
  803. GROUP by product_name;
  804.  
  805.        
  806.                        
  807.         SELECT product_name
  808. FROM (SELECT AVG(cost) FROM product)
  809. WHERE cost > AVG(cost);
  810.  
  811.        
  812.                                        
  813.                                
  814. Incorrect               Incorrect. Refer to Section 6 Lesson 1.
  815.  
  816. Section 6
  817.                                        
  818.                 31.     The EMPLOYEES and ORDERS tables contain these columns:
  819.  
  820. EMPLOYEES
  821. EMPLOYEE_ID NUMBER(10) NOT NULL PRIMARY KEY
  822. FIRST_NAME VARCHAR2(30)
  823. LAST_NAME VARCHAR2(30)
  824. ADDRESS VARCHAR2(25)
  825. CITY VARCHAR2(20)
  826. STATE VARCHAR2(2)
  827. ZIP NUMBER(9)
  828. TELEPHONE NUMBER(10)
  829.  
  830. ORDERS
  831.  
  832. ORDER_ID NUMBER(10) NOT NULL PRIMARY KEY
  833. EMPLOYEE_ID NUMBER(10) NOT NULL FOREIGN KEY
  834. ORDER_DATE DATE
  835. TOTAL NUMBER(10)
  836.  
  837. Which SELECT statement will return all orders generated by a sales representative named Franklin during the year 2001?
  838.         Mark for Review
  839. (1) Points
  840.                                        
  841.                        
  842.         SELECT order_id, total
  843. FROM ORDERS (SELECT employee_id
  844. FROM employees
  845. WHERE last_name = 'Franklin')
  846. WHERE order_date BETWEEN '01-jan-01' AND '31-dec-01';
  847.  
  848.        
  849.                        
  850.         SELECT (SELECT employee_id FROM employees WHERE last_name = 'Franklin') AND order_id, total
  851. FROM ORDERS
  852. WHERE order_date BETWEEN '01-jan-01' AND '31-dec-01';
  853.  
  854.        
  855.                        
  856.         SELECT order_id, employee_id, total
  857. FROM ORDERS
  858. WHERE order_date BETWEEN '01-jan-01' AND '31-dec-01' AND emp_id = 'Franklin';
  859.  
  860.        
  861.                        
  862.         SELECT order_id, total
  863. FROM ORDERS
  864. WHERE employee_id = (SELECT employee_id FROM employees WHERE last_name = 'Franklin')
  865. AND order_date BETWEEN '01-jan-01' AND '31-dec-01';
  866.  
  867. (*)
  868.        
  869.                                        
  870.                                
  871. Incorrect               Incorrect. Refer to Section 6 Lesson 1.
  872.        
  873.                                        
  874.                 32.     Which operator can be used with subqueries that return only one row?    Mark for Review
  875. (1) Points
  876.                                        
  877.                        
  878.         LIKE (*)
  879.        
  880.                        
  881.         ANY
  882.        
  883.                        
  884.         ALL
  885.        
  886.                        
  887.         IN
  888.        
  889.                                        
  890.                                
  891. Incorrect               Incorrect. Refer to Section 6 Lesson 1.
  892.        
  893.                                        
  894.                 33.     If you use the equality operator (=) with a subquery, how many values can the subquery return?  Mark for Review
  895. (1) Points
  896.                                        
  897.                        
  898.         Only 1 (*)
  899.        
  900.                        
  901.         Up to 2
  902.        
  903.                        
  904.         Up to 5
  905.        
  906.                        
  907.         Unlimited
  908.        
  909.                                        
  910.                                
  911. Incorrect               Incorrect. Refer to Section 6 Lesson 1.
  912.        
  913.                                        
  914.                 34.     Which operator can be used with a multiple-row subquery?        Mark for Review
  915. (1) Points
  916.                                        
  917.                        
  918.         IN (*)
  919.        
  920.                        
  921.         <>
  922.        
  923.                        
  924.         =
  925.        
  926.                        
  927.         LIKE
  928.        
  929.                                        
  930.                                
  931. Incorrect               Incorrect. Refer to Section 6 Lesson 1.
  932.        
  933.                                        
  934.                 35.     Which of the following is TRUE regarding the order of subquery execution?       Mark for Review
  935. (1) Points
  936.                                        
  937.                        
  938.         The outer query is executed first
  939.        
  940.                        
  941.         The subquery executes once after the main query
  942.        
  943.                        
  944.         The subquery executes once before the main query (*)
  945.        
  946.                        
  947.         The result of the main query is used with the subquery
  948.        
  949.                                        
  950.                                
  951. Incorrect               Incorrect. Refer to Section 6 Lesson 1.
  952.        
  953.                                        
  954.                 36.     Using a subquery in which clause will return a syntax error?    Mark for Review
  955. (1) Points
  956.                                        
  957.                        
  958.         WHERE
  959.        
  960.                        
  961.         FROM
  962.        
  963.                        
  964.         HAVING
  965.        
  966.                        
  967.         You can use subqueries in all of the above clauses. (*)
  968.        
  969.                                        
  970.                                
  971. Incorrect               Incorrect. Refer to Section 6 Lesson 1.
  972.        
  973.                                        
  974.                                        
  975.         Section 7
  976.                                        
  977.                 37.     The PRODUCTS table contains these columns:
  978.  
  979. PRODUCT_ID NUMBER NOT NULL
  980. PRODUCT_NAME VARCHAR2 (25)
  981. SUPPLIER_ID NUMBER NOT NULL
  982. LIST_PRICE NUMBER (7,2)
  983. COST NUMBER (5,2)
  984. QTY_IN_STOCK NUMBER(4)
  985. LAST_ORDER_DT DATE NOT NULL DEFAULT SYSDATE
  986.  
  987. Which INSERT statement will execute successfully?
  988.         Mark for Review
  989. (1) Points
  990.                                        
  991.                        
  992.         INSERT INTO products
  993. VALUES (2958, 'Cable', 8690, 7.09, 4.04, 700);
  994.  
  995. (*)
  996.        
  997.                        
  998.         INSERT INTO products
  999. VALUES (2958, 'Cable', 8690, 7.09, 4.04, SYSDATE);
  1000.  
  1001.        
  1002.                        
  1003.         INSERT INTO products(product_id, product_name)
  1004. VALUES (2958, 'Cable');
  1005.  
  1006.        
  1007.                        
  1008.         INSERT INTO products(product_id, product_name, supplier_id
  1009. VALUES (2958, 'Cable', 8690, SYSDATE);
  1010.  
  1011.        
  1012.                                        
  1013.                                
  1014. Correct         Correct
  1015.        
  1016.                                        
  1017.                 38.     You need to copy rows from the EMPLOYEE table to the EMPLOYEE_HIST table. What could you use in the INSERT statement to accomplish this task?   Mark for Review
  1018. (1) Points
  1019.                                        
  1020.                        
  1021.         An ON clause
  1022.        
  1023.                        
  1024.         A SET clause
  1025.        
  1026.                        
  1027.         A subquery (*)
  1028.        
  1029.                        
  1030.         A function
  1031.        
  1032.                                        
  1033.                                
  1034. Incorrect               Incorrect. Refer to Section 7 Lesson 1.
  1035.        
  1036.                                        
  1037.                 39.     Using the INSERT statement, and assuming that a column can accept null values, how can you implicitly insert a null value in a column?  Mark for Review
  1038. (1) Points
  1039.                                        
  1040.                        
  1041.         Use the NULL keyword.
  1042.        
  1043.                        
  1044.         Use the ON clause
  1045.        
  1046.                        
  1047.         Omit the column in the column list. (*)
  1048.        
  1049.                        
  1050.         It is not possible to implicitly insert a null value in a column.
  1051.        
  1052.                                        
  1053.                                
  1054. Incorrect               Incorrect. Refer to Section 7 Lesson 1.
  1055.        
  1056.                                        
  1057.                 40.     The PRODUCTS table contains these columns:
  1058.  
  1059. PROD_ID NUMBER(4)
  1060. PROD_NAME VARCHAR2(25)
  1061. PROD_PRICE NUMBER(3)
  1062.  
  1063. You want to add the following row data to the PRODUCTS table:
  1064.  
  1065. (1) a NULL value in the PROD_ID column
  1066. (2) "6-foot nylon leash" in the PROD_NAME column
  1067. (3) "10" in the PROD_PRICE column
  1068.  
  1069. You issue this statement:
  1070.  
  1071. INSERT INTO products
  1072. VALUES (null,'6-foot nylon leash', 10);
  1073.  
  1074. What row data did you add to the table?
  1075.         Mark for Review
  1076. (1) Points
  1077.                                        
  1078.                        
  1079.         The row was created with the correct data in all three columns. (*)
  1080.        
  1081.                        
  1082.         The row was created with the correct data in two of three columns.
  1083.        
  1084.                        
  1085.         The row was created with the correct data in one of the three columns.
  1086.        
  1087.                        
  1088.         The row was created completely wrong. No data ended up in the correct columns.
  1089.        
  1090.                                        
  1091.                                
  1092. Correct         Correct
  1093.  
  1094.         Section 7
  1095.                                        
  1096.                 41.     You need to update both the DEPARTMENT_ID and LOCATION_ID columns in the EMPLOYEES table using one UPDATE statement. Which clause should you include in the UPDATE statement to update multiple columns?        Mark for Review
  1097. (1) Points
  1098.                                        
  1099.                        
  1100.         The USING clause
  1101.        
  1102.                        
  1103.         The ON clause
  1104.        
  1105.                        
  1106.         The WHERE clause
  1107.        
  1108.                        
  1109.         The SET clause (*)
  1110.        
  1111.                                        
  1112.                                
  1113. Incorrect               Incorrect. Refer to Section 7 Lesson 2.
  1114.        
  1115.                                        
  1116.                 42.     One of the sales representatives, Janet Roper, has informed you that she was recently married, and she has requested that you update her name in the employee database. Her new last name is Cooper. Janet is the only person with the last name of Roper that is employed by the company. The EMPLOYEES table contains these columns and all data is stored in lowercase:
  1117.  
  1118. EMPLOYEE_ID NUMBER(10) PRIMARY KEY
  1119. LAST_NAME VARCHAR2(20)
  1120. FIRST_NAME VARCHAR2(20)
  1121. DEPARTMENT_ID VARCHAR2 (20)
  1122. HIRE_DATE DATE
  1123. SALARY NUMBER(10)
  1124.  
  1125. Which UPDATE statement will accomplish your objective?
  1126.  
  1127.         Mark for Review
  1128. (1) Points
  1129.                                        
  1130.                        
  1131.         UPDATE employees
  1132. SET last_name = 'cooper'
  1133. WHERE last_name = 'roper';
  1134. (*)
  1135.        
  1136.                        
  1137.         UPDATE employees last_name = 'cooper'
  1138. WHERE last_name = 'roper';
  1139.  
  1140.        
  1141.                        
  1142.         UPDATE employees
  1143. SET last_name = 'roper'
  1144. WHERE last_name = 'cooper';
  1145.  
  1146.        
  1147.                        
  1148.         UPDATE employees
  1149. SET cooper = 'last_name'
  1150. WHERE last_name = 'roper';
  1151.  
  1152.        
  1153.                                        
  1154.                                
  1155. Incorrect               Incorrect. Refer to Section 7 Lesson 2.
  1156.        
  1157.                                        
  1158.                 43.     One of your employees was recently married. Her employee ID is still 189, however, her last name is now Rockefeller. Which SQL statement will allow you to reflect this change?         Mark for Review
  1159. (1) Points
  1160.                                        
  1161.                        
  1162.         INSERT INTO my_employees SET last_name = 'Rockefeller' WHERE employee_ID = 189;
  1163.        
  1164.                        
  1165.         INSERT my_employees SET last_name = 'Rockefeller' WHERE employee_ID = 189;
  1166.        
  1167.                        
  1168.         UPDATE INTO my_employees SET last_name = 'Rockefeller' WHERE employee_ID = 189;
  1169.        
  1170.                        
  1171.         UPDATE my_employees SET last_name = 'Rockefeller' WHERE employee_ID = 189; (*)
  1172.        
  1173.                                        
  1174.                                
  1175. Incorrect               Incorrect. Refer to Section 7 Lesson 2.
  1176.        
  1177.                                        
  1178.                 44.     You need to update the area code of employees that live in Atlanta. Evaluate this partial UPDATE statement:
  1179.  
  1180. UPDATE employee
  1181. SET area_code = 770
  1182.  
  1183. Which of the following should you include in your UPDATE statement to achieve the desired results?
  1184.         Mark for Review
  1185. (1) Points
  1186.                                        
  1187.                        
  1188.         UPDATE city = Atlanta;
  1189.        
  1190.                        
  1191.         SET city = 'Atlanta';
  1192.        
  1193.                        
  1194.         WHERE city = 'Atlanta'; (*)
  1195.        
  1196.                        
  1197.         LIKE 'At%';
  1198.        
  1199.                                        
  1200.                                
  1201. Correct         Correct
  1202.        
  1203.                                        
  1204.                 45.     You need to remove a row from the EMPLOYEES table. Which statement would you use?       Mark for Review
  1205. (1) Points
  1206.                                        
  1207.                        
  1208.         UPDATE with a WHERE clause
  1209.        
  1210.                        
  1211.         INSERT with a WHERE clause
  1212.        
  1213.                        
  1214.         DELETE with a WHERE clause (*)
  1215.        
  1216.                        
  1217.         MERGE with a WHERE clause
  1218.        
  1219.                                        
  1220.                                
  1221. Incorrect               Incorrect. Refer to Section 7 Lesson 2.
  1222.        
  1223.                                        
  1224.                 46.     What keyword in an UPDATE statement speficies the columns you want to change?   Mark for Review
  1225. (1) Points
  1226.                                        
  1227.                        
  1228.         SELECT
  1229.        
  1230.                        
  1231.         WHERE
  1232.        
  1233.                        
  1234.         SET (*)
  1235.        
  1236.                        
  1237.         HAVING
  1238.        
  1239.                                        
  1240.                                
  1241. Incorrect               Incorrect. Refer to Section 7 Lesson 2.
  1242.        
  1243.                                        
  1244.                 47.     What would happen if you issued a DELETE statement without a WHERE clause?      Mark for Review
  1245. (1) Points
  1246.                                        
  1247.                        
  1248.         All the rows in the table would be deleted. (*)
  1249.        
  1250.                        
  1251.         An error message would be returned.
  1252.        
  1253.                        
  1254.         No rows would be deleted.
  1255.        
  1256.                        
  1257.         Only one row would be deleted.
  1258.        
  1259.                                        
  1260.                                
  1261. Incorrect               Incorrect. Refer to Section 7 Lesson 2.
  1262.        
  1263.                                        
  1264.                 48.     The TEACHERS and CLASS_ASSIGNMENTS tables contain these columns:
  1265.  
  1266. TEACHERS
  1267. TEACHER_ID NUMBER(5)
  1268. NAME VARCHAR2(25)
  1269. SUBJECT_ID NUMBER(5)
  1270. HIRE_DATE DATE
  1271. SALARY NUMBER(9,2)
  1272.  
  1273. CLASS_ASSIGNMENTS
  1274. CLASS_ID NUMBER(5)
  1275. TEACHER_ID NUMBER(5)
  1276. START_DATE DATE
  1277. MAX_CAPACITY NUMBER(3)
  1278.  
  1279. Which scenario would require a subquery to return the desired results?
  1280.         Mark for Review
  1281. (1) Points
  1282.                                        
  1283.                        
  1284.         You need to display the start date for each class taught by a given teacher.
  1285.        
  1286.                        
  1287.         You need to create a report to display the teachers who were hired more than five years ago.
  1288.        
  1289.                        
  1290.         You need to display the names of the teachers who teach classes that start within the next week.
  1291.        
  1292.                        
  1293.         You need to create a report to display the teachers who teach more classes than the average number of classes taught by each teacher. (*)
  1294.        
  1295.                                        
  1296.                                
  1297. Incorrect               Incorrect. Refer to Section 7 Lesson 2.
  1298.        
  1299.                                        
  1300.                 49.     When the WHERE clause is missing in a DELETE statement, what is the result?     Mark for Review
  1301. (1) Points
  1302.                                        
  1303.                        
  1304.         All rows are deleted from the table. (*)
  1305.        
  1306.                        
  1307.         The table is removed from the database.
  1308.        
  1309.                        
  1310.         An error message is displayed indicating incorrect syntax.
  1311.        
  1312.                        
  1313.         Nothing. The statement will not execute.
  1314.        
  1315.                                        
  1316.                                
  1317. Incorrect               Incorrect. Refer to Section 7 Lesson 2.
  1318.        
  1319.                                        
  1320.                 50.     Examine the structures of the PLAYERS, MANAGERS, and TEAMS tables:
  1321.  
  1322. PLAYERS
  1323. PLAYER_ID NUMBER Primary Key
  1324. LAST_NAME VARCHAR2 (30)
  1325. FIRST_NAME VARCHAR2 (25)
  1326. TEAM_ID NUMBER
  1327. MGR_ID NUMBER
  1328. SIGNING_BONUS NUMBER(9,2)
  1329. SALARY NUMBER(9,2)
  1330.  
  1331. MANAGERS
  1332. MANAGER_ID NUMBER Primary Key
  1333. LAST_NAME VARCHAR2 (20)
  1334. FIRST_NAME VARCHAR2 (20)
  1335. TEAM_ID NUMBER
  1336.  
  1337. TEAMS
  1338. TEAM_ID NUMBER Primary Key
  1339. TEAM_NAME VARCHAR2 (20)
  1340. OWNER_LAST_NAME VARCHAR2 (20)
  1341. OWNER_FIRST_NAME VARCHAR2 (20)
  1342.  
  1343. Which situation would require a subquery to return the desired result?
  1344.         Mark for Review
  1345. (1) Points
  1346.                                        
  1347.                        
  1348.         To display the names each player on the Lions team
  1349.        
  1350.                        
  1351.         To display the maximum and minimum player salary for each team
  1352.        
  1353.                        
  1354.         To display the names of the managers for all the teams owned by a given owner (*)
  1355.        
  1356.                        
  1357.         To display each player, their manager, and their team name for all teams with a id value greater than 5000
  1358.        
  1359.                                        
  1360.                                
  1361. Incorrect               Incorrect. Refer to Section 7 Lesson 2.
clone this paste RAW Paste Data