Scott cannot create any synonym for Mary's view. Scott cannot create a synonym because synonyms can be created only for tables.į. Scott can create a synomym for the EMP_DEPT_LOC_VU by using the command CRETE LOCAL SYNONYM EDL_VU ON mary(EMP_DEPT_LOC_VU) then he can prefix the columns with this synonymĮ. Scott can create a synoym for the EMP_DEPT_LOC_VU by using the command CREATE LOCAL SYNONYM EDL_VU FOR mary.emp dept_LOC_uv then he can prefix the columns with the synonym.ĭ. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command CREATE SYNONYM EDL_VU FOR mary.EMP DEPT_LOC_VU then he can prefix the columns with this synonym.Ĭ. Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command CREATE PRIVATE SYNONYM EDL_VU FOR mary.EMP DEPT_LOC_VU then he can prefix the columns with this synonymī. Which option enables Scott to eliminate the need to qualify the view with the name MARY.EMP_DEPT_LOC_VU each time the view is referenced?Ī. She granted SELECT privilege to Scott on this view. Mary has a view called EMP_DEPT_LOC_VU that was created based on the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables. The statement fails because there are no columns specified in the DELETE clause of the DELETE statement. Also all the rows in the EMPLOYEES table are deleted.į. The row with department ID 40 is deleted in the DEPARTMENTS table. Also the rows with employee IDs 106 and 110 and the employees working under employee 110 are deleted from the EMPLOYEES table.Į. Also the rows with employee IDs 110 and 106 are deleted from the EMPLOYEES table.ĭ. The statement fails because there are child records in the EMPLOYEES table with department ID 40.Ĭ. Only the row with department ID 40 is deleted in the DEPARTMENTS table.ī. Examine this DELETE statement: DELETE FROM departments WHERE department id=40 What happens when you execute the DELETE statement?Ī. On the DEPARTMENTS table, DEPARTMENT_ID is the primary key. On the EMPLOYEES table, EMPLOYEE_ID is the primary key MGR_ID is the ID of mangers and refers to the EMPLOYEE_ID DEPT_ID is foreign key to DEPARTMENT_ID column of the DEPARTMENTS table (EMPLOEE_ID NUMBER PRIMARY KEY, EMP_NAME VARCHAR2(20),ĭEPT_ID NUMBER REFERENCES departments (department_id) MGR_ID NUMBER REFERENCES employees(employee id), JOB_ID VARCHAR2(15). (department_id NUMBER PRIMARY KEY, department_name VARCHAR2(30)) Click the Exhibit button and examine the data in the EMPLOYEES and DEPARTMENTS tables.ĮMP_ID - EMP_NAME - DEPT_ID - MGR_ID - JOB_ID - SALARYġ08 - Jennifer - 30 - 110 - HR_DIR - 6500Īlso examine the SQL statements that create the EMPLOYEES and DEPARTMENTS tables: The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department. The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all departments that pay less salary than the maximm salary aid in the company.Į. The statement produces an error at line6.ĭ. The statement produces an error at line3.Ĭ. The statement produces an error at line1.ī. SELECT ord_id, cust_id, ord_total FROM orders WHERE cust_id IN (SELECT cust_id FROM customers WHERE cust name = 'Martin')ġ SELECT a.emp_name, a.sal, a.dept_id, b.maxsalĦ WHERE a.dept_id = b.dept_id 7 AND a.salĪ. SELECT ord_id, cust_id, ord_total FROM orders WHERE ord_date IN (SELECT ord_date FROM orders, customers WHERE cst_name='Martin') ĭ. SELECT ord_id, cust_id, ord_total FROM orders WHERE ord_date IN (SELECT ord_date FROM orders WHERE cust_id=(SELECT cust_id FROMĬ. SELECT ord_id, cust_id, ord_total FROM orders, customers WHERE cust_name='Martin' AND ord_date IN ('1' 2') ī. Which SQL statement retrieves the order ID, customer ID, and order total for the orders that are placed on the same day that Martin paced his orders?Ī. Click the Exhibit button and examine the data from the ORDERS and CUSTOMERS tables. SELECT last_name, (salary*12)* NVL(commission_pct,0) FROM EMPLOYEES SELECT last_name, (salary*12)* NVL2(commission_pct,0) FROM EMPLOYEES ĭ. SELECT last_name, (salary*12)* IFNULL(commission_pct,0) FROM EMPLOYEES Ĭ. SELECT last_name, (salary*12)* commission_Pct FROM EMPLOYEES ī. Which SQL statement displays the desired results?Ī. For records that have a NULL commission_pct, a zero must be displayed against the calculated column. You want todisplay the name and annual salary multiplied by the commission_pct for all employees.