1Z0-051 Exam Dumps | You need to write a SQL statement that returns employee name, salary, department ID, and maximum salary

<< Prev Question Next Question >>

Question 109/290

You need to write a SQL statement that returns 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.
Which statement accomplishes this task?

A. SELECT a.emp_name, a.sal, b.dept_id, MAX(sal) FROM employees a, departments b WHERE a.dept_id = b.dept_id AND a.sal < MAX(sal) GROUP BY b.dept_id;

B. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) b WHERE a.dept_id = b.dept_id AND a.sal < b.maxsal;

C. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a WHERE a.sal < (SELECT MAX (sal) maxsal FROM employees b GROUP BY dept_id);

D. SELECT emp_name, sal, dept_id, maxsal FROM employees, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) WHERE a.sal < maxsal;

Question List (290q): Question 1: Which three are true? (Choose three.)...; Question 2: You need to create a table for a banking application. One of...; Question 3: Evaluate the following two queries: Exhibit: (Exhibit) Exhib...; Question 4: What is true about the WITH GRANT OPTION clause?...; Question 5: The DBA issues this SQL command: CREATE USER Scott IDENTIFIE...; Question 6: In which four clauses can a sub query be used? (Choose four....; Question 7: View the exhibit and examine the description for the SALES a...; Question 8: Which SQL statements would display the value 1890.55 as $1,8...; Question 9: You need to display the first names of all customers from th...; Question 10: Which is a valid CREATE TABLE statement?...; Question 11: View the Exhibit and examine the structure of the CUSTOMERS ...; Question 12: Which four are types of functions available in SQL? (Choose ...; Question 13: View the Exhibit and examine the data in the PRODUCTS table....; Question 14: In the CUSTOMERS table, the CUST_CITY column contains the va...; Question 15: View the Exhibit and examine the structure of the EMPLOYEES ...; Question 16: The COMMISSION column shows the monthly commission earned by...; Question 17: You need to display the date 11-Oct-2007 in words as 'Eleven...; Question 18: View the Exhibit and examine the structure of CUSTOMERS tabl...; Question 19: Which three statements/commands would cause a transaction to...; Question 20: Which are /SQL*Plus commands? (Choose all that apply.)...; Question 21: View the Exhibit and examine the structure of the CUSTOMERS ...; Question 22: Examine the structure of the PROMOS table: (Exhibit) You wan...; Question 23: View the Exhibit and examine the description for the CUSTOME...; Question 24: You issue the following command to drop the PRODUCTS table: ...; Question 25: Examine the data in the ORD_ITEMS table: (Exhibit) Evaluate ...; Question 26: Examine the structure of the ORDERS table: (Exhibit) You wan...; Question 27: View the Exhibit and examine the structure of ORDERS and CUS...; Question 28: Which two statements are true regarding views? (Choose two.)...; Question 29: View the Exhibit and evaluate structures of the SALES, PRODU...; Question 30: Which two statements are true regarding sub queries? (Choose...; Question 31: The DBA issues this SQL command: CREATE USER scott IDENTIFIE...; Question 32: Evaluate the following query: SELECT INTERVAL '300' MONTH, I...; Question 33: Which substitution variable would you use if you want to reu...; Question 34: Which statements are true regarding the FOR UPDATE clause in...; Question 35: You need to calculate the number of days from 1st January 20...; Question 36: SLS is a private synonym for the SH.SALES table. The user SH...; Question 37: View the Exhibit and examine the structure of the CUSTOMERS ...; Question 38: Examine the structure of the EMPLOYEES table: (Exhibit) Whic...; Question 39: Which statement is true regarding the INTERSECT operator?...; Question 40: Which describes the default behavior when you create a table...; Question 41: What is true about updates through a view?...; Question 42: Which two statements are true regarding constraints? (Choose...; Question 43: Evaluate the following SQL statements: Exhibit: (Exhibit) Yo...; Question 44: Which SQL statement would you use to remove a view called EM...; Question 45: Which two statements are true regarding views? (Choose two.)...; Question 46: Which tasks can be performed using SQL functions that are bu...; Question 47: Examine these statements: CREATE ROLE registrar; GRANT UPDAT...; Question 48: Evaluate these two SQL statements: SELECT last_name, salary,...; Question 49: Using the CUSTOMERS table, you need to generate a report tha...; Question 50: Which two statements are true regarding constraints? (Choose...; Question 51: Examine the structure of the PRODUCTS table: (Exhibit) You w...; Question 52: Which are iSQL*Plus commands? (Choose all that apply.)...; Question 53: Which two statements are true regarding the COUNT function? ...; Question 54: Which constraint can be defined only at the column level?...; Question 55: Which three tasks can be performed using SQL functions built...; Question 56: View the Exhibit and examine the structure of the PROMOTIONS...; Question 57: View the Exhibit and examine the structure of the CUSTOMERS ...; Question 58: You created an ORDERS table with the following description: ...; Question 59: Examine the structure of the EMP_DEPT_VU view: (Exhibit) Whi...; Question 60: View the Exhibit and examine the data in the PROMO_NAME and ...; Question 61: Which constraint can be defined only at the column level?...; Question 62: You own a table called EMPLOYEES with this table structure: ...; Question 63: You need to generate a list of all customer last names with ...; Question 64: Which three statements are true regarding views? (Choose thr...; Question 65: View the Exhibit and examine the structure of the PRODUCTS t...; Question 66: User Mary has a view called EMP_DEPT_LOC_VU that was created...; Question 67: Evaluate the following CREATE SEQUENCE statement: CREATE SEQ...; Question 68: You issue the following query: SQL> SELECT AVG(MAX(qty)) ...; Question 69: The PRODUCTS table has the following structure: (Exhibit) Ev...; Question 70: Here is the structure and data of the CUST_TRANS table: Exhi...; Question 71: The user Sue issues this SQL statement: GRANT SELECT ON sue....; Question 72: Examine the structure and data in the PRIC E_LIST table: Nam...; Question 73: Examine the data in the CUST_NAME column of the CUSTOMERS ta...; Question 74: For which action can you use the TO_DATE function?...; Question 75: Top N analysis requires _____ and _____. (Choose two.)...; Question 76: You work as a database administrator at ABC.com. You study t...; Question 77: Examine the structure of the SHIPMENTS table: (Exhibit) You ...; Question 78: Examine the structure of the TRANSACTIONS table: Name Null T...; Question 79: Examine the structure proposed for the TRANSACTIONS table: (...; Question 80: View the Exhibits and examine the structures of the PROMOTIO...; Question 81: View the Exhibit and examine the data in the EMPLOYEES table...; Question 82: The SQL statements executed in a user session as follows: Ex...; Question 83: Which statement is true regarding sub queries?...; Question 84: Examine the description of the EMPLOYEES table: EMP_ID NUMBE...; Question 85: Evaluate the following SQL query; (Exhibit) What would be th...; Question 86: See the Exhibit and examine the structure of ORD table: Exhi...; Question 87: Examine the statement: GRANT select, insert, update ON stude...; Question 88: You need to extract details of those products in the SALES t...; Question 89: Which three statements are true regarding the data types in ...; Question 90: View the Exhibit and examine the structure of the PROMOTIONS...; Question 91: View the Exhibit and examine the structure of the PRODUCTS t...; Question 92: Which two statements are true about sequences created in a s...; Question 93: You are currently located in Singapore and have connected to...; Question 94: View the Exhibit and examine the structure and data in the I...; Question 95: View the Exhibit and examine the data in the PROMO_CATEGORY ...; Question 96: You want to create an ORD_DETAIL table to store details for ...; Question 97: Examine the following SQL commands: (Exhibit) Which statemen...; Question 98: Examine the structure of the EMPLOYEES table: (Exhibit) Whic...; Question 99: Which statement is true regarding the UNION operator?...; Question 100: Evaluate the SQL statement: SELECT LPAD (salary,10,'*') FROM...; Question 101: See the Exhibit and examine the structure of the PROMOTIONS ...; Question 102: View the Exhibit to examine the description for the SALES ta...; Question 103: Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NU...; Question 104: See the Exhibit and examine the structure of the SALES, CUST...; Question 105: You need to display the date ll-oct-2007 in words as "Eleven...; Question 106: Evaluate the following SQL statement: (Exhibit) Which statem...; Question 107: Which two statements are true regarding working with dates? ...; Question 108: View the Exhibit and examine the structure of ORD and ORD_IT...; Question 109: You need to write a SQL statement that returns employee name...; Question 110: Which two statements are true regarding constraints? (Choose...; Question 111: Examine the statement: Create synonym emp for hr.employees; ...; Question 112: Examine the data in the CUSTOMERS table: (Exhibit) You want ...; Question 113: Which two statements are true regarding savepoints? (Choose ...; Question 114: Which two statements are true regarding the USING clause in ...; Question 115: View the Exhibit and examine the structure of the CUSTOMERS ...; Question 116: Examine the structure of the EMPLOYEES table: (Exhibit) You ...; Question 117: Which object privileges can be granted on a view?...; Question 118: Which is an iSQL*Plus command?; Question 119: See the Exhibit and examine the structure of the PROMOSTIONS...; Question 120: Which two statements are true regarding the ORDER BY clause?...; Question 121: View the Exhibit and examine the structure of the PRODUCTS t...; Question 122: Which SQL statement displays the date March 19, 2001 in a fo...; Question 123: The user Alice wants to grant all users query privileges on ...; Question 124: Examine the structure of the PROMOTIONS table: (Exhibit) The...; Question 125: Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tab...; Question 126: Which four are valid Oracle constraint types? (Choose four.)...; Question 127: What are two reasons to create synonyms? (Choose two.)...; Question 128: Which three statements are true regarding subqueries? (Choos...; Question 129: Which arithmetic operations can be performed on a column by ...; Question 130: You need to create a table with the following column specifi...; Question 131: View the Exhibit and examine the structure of the PRODUCT, C...; Question 132: View the Exhibit and examine the structure of CUSTOMERS and ...; Question 133: Which one is a system privilege?...; Question 134: Which statement is true regarding transactions? (Choose all ...; Question 135: Which three SQL statements would display the value 1890.55 a...; Question 136: You need to modify the STUDENTS table to add a primary key o...; Question 137: Examine this statement: SELECT student_id, gpa FROM student_...; Question 138: The CUSTOMERS table has these columns: (Exhibit) The CUSTOME...; Question 139: See the Exhibit and Examine the structure of the CUSTOMERS t...; Question 140: You work as a database administrator at ABC.com. You study t...; Question 141: Which statement is true regarding the default behavior of th...; Question 142: View the Exhibit and examine the structure of the PROMOTIONS...; Question 143: View the Exhibit and examine the structure of the PRODUCTS t...; Question 144: See the exhibit and examine the structure of the CUSTOMERS a...; Question 145: The ORDERS TABLE belongs to the user OE. OE has granted the ...; Question 146: The STUDENT_GRADES table has these columns: (Exhibit) Which ...; Question 147: Examine the structure and data of the CUSTJTRANS table: CUST...; Question 148: You are the DBA for an academic database. You need to create...; Question 149: Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NU...; Question 150: The STUDENT_GRADES table has these columns: STUDENT_IDNUMBER...; Question 151: Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tab...; Question 152: Which three statements are true regarding sub queries? (Choo...; Question 153: Which statement correctly describes SQL and /SQL*Plus?...; Question 154: You need to produce a report where each customer's credit li...; Question 155: EMPLOYEES and DEPARTMENTS data: EMPLOYEES (Exhibit) DEPARTME...; Question 156: Which best describes an inline view?...; Question 157: Examine the data in the CUST_NAME column of the CUSTOMERS ta...; Question 158: See the Exhibit and examine the structure and data in the IN...; Question 159: What is true about sequences?; Question 160: View the Exhibit and examine the data in the EMPLOYEES table...; Question 161: Which statements are true regarding single row functions? (C...; Question 162: You need to design a student registration database that cont...; Question 163: View the Exhibit and examine the structure of the PROMOTIONS...; Question 164: Which SQL statement returns a numeric value?...; Question 165: The EMPLOYEES table contains these columns: EMPLOYEE_ID NUMB...; Question 166: View the Exhibit; e xamine the structure of the PROMOTIONS t...; Question 167: Evaluate the following SQL statement: SQL> SELECT cust_id...; Question 168: View the Exhibit and examine the structure of the CUSTOMERS ...; Question 169: The STUDENT_GRADES table has these columns: STUDENT_ID NUMBE...; Question 170: When does a transaction complete? (Choose all that apply.)...; Question 171: Which two statements are true about constraints? (Choose two...; Question 172: Examine the structure of the INVOICE table: Exhibit: (Exhibi...; Question 173: Which is an iSQL*Plus command?; Question 174: View the Exhibit for the structure of the STUDENT and FACULT...; Question 175: Evaluate the following query: (Exhibit) What would be the ou...; Question 176: Which two statements are true regarding tables? (Choose two....; Question 177: See the Exhibit and Examine the structure of SALES and PROMO...; Question 178: Examine the structure of the TRANSACTIONS table: (Exhibit) Y...; Question 179: In which two cases would you use an outer join? (Choose two....; Question 180: Which statement adds a constraint that ensures the CUSTOMER_...; Question 181: What is true about sequences?; Question 182: You are currently located in Singapore and have connected to...; Question 183: Which three statements are true about multiple-row subquerie...; Question 184: Which statements are true regarding the WHERE and HAVING cla...; Question 185: Which two statements about sub queries are true? (Choose two...; Question 186: Which two statements about creating constraints are true? (C...; Question 187: You want to display the date for the first Monday of the nex...; Question 188: Evaluate this SQL statement: SELECT e.emp_name, d.dept_name ...; Question 189: Which four are attributes of single row functions? (Choose f...; Question 190: The PART_CODE column in the SPARES table contains the follow...; Question 191: Evaluate this SQL statement: SELECT ename, sal, 12*sal+100 F...; Question 192: Evaluate the following SQL statements: DELETE FROM sales; Th...; Question 193: You need to calculate the number of days from 1st Jan 2007 t...; Question 194: Evaluate the SQL statement: TRUNCATE TABLE DEPT; Which three...; Question 195: You need to produce a report for mailing labels for all cust...; Question 196: Which statement is true regarding synonyms?...; Question 197: View the Exhibit and examine the structure of the CUSTOMERS ...; Question 198: View the Exhibit and examine the structure of ORDERS and CUS...; Question 199: View the Exhibit and examine the description for the CUSTOME...; Question 200: See the structure of the PROGRAMS table: (Exhibit) Which two...; Question 201: View the Exhibit and examine the structure of the CUSTOMERS ...; Question 202: You work as a database administrator at ABC.com. You study t...; Question 203: Which SQL statement accepts user input for the columns to be...; Question 204: Examine the structure of the PROMOS table: (Exhibit) You wan...; Question 205: What is true of using group functions on columns that contai...; Question 206: View the Exhibit and examine the structure of the PRODUCTS, ...; Question 207: Evaluate the following SQL statements: Exhibit: (Exhibit) Wh...; Question 208: View the Exhibit and examine the structure of the CUSTOMERS ...; Question 209: Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tab...; Question 210: Which two statements are true regarding single row functions...; Question 211: View the Exhibit and examine the structure of the SALES tabl...; Question 212: Which two statements are true regarding the USING and ON cla...; Question 213: View the exhibit and examine the structure of the PROMOTIONS...; Question 214: A SELECT statement can be used to perform these three functi...; Question 215: Which CREATE TABLE statement is valid?...; Question 216: Which two statements complete a transaction? (Choose two)...; Question 217: The EMPLOYEES table contains these columns: EMPLOYEE_ID NUMB...; Question 218: View the Exhibit and examine the structures of the EMPLOYEES...; Question 219: Which statement describes the ROWID data type?...; Question 220: View the Exhibit and examine the structure of the PROMOTIONS...; Question 221: Which two statements are true regarding subqueries? (Choose ...; Question 222: Examine the description of the CUSTOMERS table: (Exhibit) Th...; Question 223: Which two statements are true regarding the DELETE and TRUNC...; Question 224: The STUDENT_GRADES table has these columns: STUDENT_ID NUMBE...; Question 225: View the Exhibits and examine the structures of the PRODUCTS...; Question 226: You need to create a table named ORDERS that contain four co...; Question 227: Which is the valid CREATE [TABLE statement?...; Question 228: The CUSTOMERS table has the following structure: Exhibit: (E...; Question 229: Examine the data in the LIST_PRICE and MIN_PRICE columns of ...; Question 230: View the Exhibit and examine the structure of the PROMOTIONS...; Question 231: Examine the SQL statement that creates ORDERS table: CREATE ...; Question 232: Examine the structure of the STUDENTS table: (Exhibit) You n...; Question 233: Where can sub queries be used? (Choose all that apply)...; Question 234: Examine the structure of the CUSTOMERS table: (Exhibit) CUST...; Question 235: See the Exhibit and examine the structure and data in the IN...; Question 236: View the Exhibit and examine the structure of the PRODUCTS t...; Question 237: View the Exhibits and examine the structures of the COSTS an...; Question 238: View the Exhibit and examine the structure of the CUSTOMERS ...; Question 239: The CUSTOMERS table has these columns: (Exhibit) A promotion...; Question 240: View the Exhibit and examine the structure of the ORDERS and...; Question 241: View the Exhibit and examine the structure of the SALES and ...; Question 242: Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tab...; Question 243: Examine the structure of the EMPLOYEES and DEPARTMENTS table...; Question 244: The PRODUCTS table has these columns: PRODUCT_ID NUMBER(4) P...; Question 245: You want to find out if there is any item in the table for w...; Question 246: View the Exhibit and examine the structure and data in the I...; Question 247: Evaluate the following SQL statement: SQL> SELECT cust_id...; Question 248: The EMPLOYEES table has these columns: LAST NAMEVARCHAR2(35)...; Question 249: Evaluate the SQL statement: SELECT ROUND(45.953, -1), TRUNC(...; Question 250: Which object privileges can be granted on a view?...; Question 251: You are granted the CREATE VIEW privilege. What does this al...; Question 252: See the Exhibits and examine the structures of PRODUCTS, SAL...; Question 253: What does the FORCE option for creating a view do?...; Question 254: View the Exhibits and examine PRODUCTS and SALES tables. (Ex...; Question 255: You work as a database administrator at ABC.com. You study t...; Question 256: View the Exhibit and examine the structure of the PROMOTIONS...; Question 257: View the Exhibit and examine the data in the PROMOTIONS tabl...; Question 258: Examine the data in the PROMO_BEGIN_DATE column of the PROMO...; Question 259: Which statements are correct regarding indexes? (Choose all ...; Question 260: Evaluate the following SQL commands: (Exhibit) The command t...; Question 261: Which statement is true regarding the COALESCE function?...; Question 262: Which two are true about aggregate functions? (Choose two.)...; Question 263: You work as a database administrator at ABC.com. You study t...; Question 264: View the Exhibit and examine the data in the PROJ_TASK_DETAI...; Question 265: The CUSTOMERS table has these columns: CUSTOMER_ID NUMBER(4)...; Question 266: Examine the structure of the MARKS table: Exhibit: (Exhibit)...; Question 267: Examine the structure and data in the PRICE_LIST table: Name...; Question 268: Examine the structure proposed for the TRANSACTIONS table: (...; Question 269: Exhibit contains the structure of PRODUCTS table: (Exhibit) ...; Question 270: Which two statements are true about WHERE and HAVING clauses...; Question 271: View the Exhibit and examine the structure of the PROMOTIONS...; Question 272: A data manipulation language statement _____....; Question 273: View the Exhibit and examine the structure of the CUSTOMERS ...; Question 274: View the Exhibits and examine the structures of the PRODUCTS...; Question 275: View the Exhibits and examine the structures of the CUSTOMER...; Question 276: You work as a database administrator at ABC.com. You study t...; Question 277: Examine the description of the EMP_DETAILS table given below...; Question 278: Evaluate the following SQL statement: SQL> SELECT cust_id...; Question 279: Which two statements are true regarding indexes? (Choose two...; Question 280: What is true regarding sub queries?...; Question 281: Evaluate the following SQL statements: Exhibit: (Exhibit) Ex...; Question 282: You need to perform these tasks: - Create and assign a MANAG...; Question 283: View the Exhibit and examine the structure of the PROMOTIONS...; Question 284: View the Exhibit and examine the description for the PRODUCT...; Question 285: View the Exhibit and examine the data in the COSTS table. (E...; Question 286: View the Exhibit and examine the structure of the PRODUCTS t...; Question 287: Evaluate the SQL statement DROP TABLE DEPT: Which four state...; Question 288: The following data exists in the PRODUCTS table: PROD_ID PRO...; Question 289: Which view should a user query to display the columns associ...; Question 290: View the Exhibit and examine the data in the PROMOTIONS tabl...

Question 109/290

LEAVE A REPLY

Download PDF File