SQL 2024

2-Examine this business rule: Each student can work on multiple projects and each project can have multiple students. You must design an Entity Relationship (ER) model for optimal data storage and allow for generating reports in this format: STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK Which two statements are true? (Choose two.) ¡. PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity. STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the projects entity. An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which is the foreign key linked to the students and projects entities. The ER must have a many-to-many relationship between the STUDENTS and PROJECTS entities that must be resolved into one-to-many relationships. The ER must have a one-to-many relationship between the STUDENTS and PROJECTS entities.

5-Which three statements are true about Oracle synonyms? (Choose three.).  A synonym cannot be created for a PL/SQL package. A synonym can be available to all users.  A SEQUENCE can have a synonym.  Any user can drop a PUBLIC synonym.  A synonym created by one user can refer to an object belonging to another user.

Which two are true about queries using set operators such as UNION? (Choose two).  In a query containing multiple set operators, INTERSECT always takes precedence over UNION and UNION ALL. An expression in the first SELECT list must have a column alias for the expression.  All set operators are valid on columns of all data types.  CHAR columns of different lengths used with a set operator return a VARCHAR2 whose length equals the longest char value.  Queries using set operators do not perform implicit conversion across data type groups (e.g. character, numeric).

12- Examine this SQL statement: Which two are true? (Choose two.). The DELETE statement executes successfully even if the subquery selects multiple rows. The subquery is executed before the DELETE statement is executed. The subquery is not a correlated subquery. All existing rows in the EMPLOYEES table are deleted. The subquery is executed for every row in the EMPLOYEES table.

You only want to display details of rows where START_DATE is within the last 25 months. Which WHERE clause can be used?.  WHERE TO_NUMBER(start_date - SYSDATE) <= 25. WHERE MONTHS_BETWEEN(start_date, SYSDATE) <= 25.  WHERE MONTHS_BETWEEN(SYSDATE, start_date) <= 25.  WHERE ADD_MONTHS(start_date, 25) <= SYSDATE.

A session's NLS_DATE_FORMAT is set to DD Mon YYYY. Which two queries return the value 1 Jan 2019? (Choose two.).  SELECT TO_DATE('2019-01-01') FROM DUAL;.  SELECT DATE '2019-01-01' FROM DUAL;.  SELECT '2019-01-01' FROM DUAL;.  SELECT TO_DATE('2019-01-01', 'YYYY-MM-DD') FROM DUAL;.  SELECT TO_CHAR('2019-01-01') FROM DUAL;.

Which two are true about unused columns? (Choose two.).  A query can return data from unused columns, but no DML is possible on those columns.  Unused columns retain their data until they are dropped.  Once a column has been set to unused, a new column with the same name can be added to the table.  The DESCRIBE command displays unused columns.  A primary key column cannot be set to unused.  A foreign key column cannot be set to unused.

Examine the description of the MEMBERS table: Examine the partial query: SELECT city, last_name AS lname FROM members ...; You want to display all cities that contain the string AN. The cities must be returned in ascending order, with the last names further sorted in descending order. Which two clauses must you add to the query? (Choose two.). ORDER BY 1, 2. ORDER BY 1, lname DESC. WHERE city IN ('%AN%'). WHERE city = '%AN%'. WHERE city LIKE '%AN%'. ORDER BY last_name DESC, city ASC.

Which two statements are true about Oracle databases and SQL? (Choose two.). Updates performed by a database user can be rolled back by another user by using the ROLLBACK command. A query can access only tables within the same schema. The database guarantees read consistency at select level on user-created tables. A user can be the owner of multiple schemas in the same database. When you execute an update statement, the database instance locks each updated row.

Which three actions can you perform on an existing table containing data? (Choose three.). Increase the width of a numeric column. Add a new column as the table's first column. Define a default value that is automatically inserted into a column containing nulls. Change a DATE column containing data to a NUMBER data type. Change the default value of a column. Add a new NOT NULL column with a DEFAULT value.

27- Which three statements are true about the Oracle join and ANSI join syntax? (Choose three.). The Oracle join syntax supports creation of a Cartesian product of two tables. The Oracle join syntax only supports right outer joins. The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables. The Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax. The Oracle join syntax supports natural joins. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax. The SQL:1999 compliant ANSI join syntax supports natural joins.

Which three statements are true about single-row functions? (Choose three.). They return a single result row per table. They can be nested to any level. They can accept only one argument. The argument can be a column name, variable, literal or an expression. They can be used only in the WHERE clause of a SELECT statement. The data type returned can be different from the data type of the argument.

31-Examine the description of the EMPLOYEES table: Which statement will fail?. a. b. c. d.

Which three statements are true about a self join? (Choose three.). It must be an equijoin. The ON clause must be used. It must be an inner join. It can be an outer join. The ON clause can be used. The query must use two different aliases for the table.

Which two statements are true about the ORDER BY clause? (Choose two.). In a character sort, the values are case-sensitive. NULLS are not included in the sort operation. Numeric values are displayed in descending order if they have decimal positions. Column aliases can be used in the ORDER BY clause. Only columns that are specified in the SELECT list can be used in the ORDER BY clause.

38-You create a table named 123. Which statement runs successfully?. SELECT * FROM TABLE(123);. SELECT * FROM "123";. SELECT * FROM \'123\';. SELECT * FROM '123';.

Which three are true about scalar subquery expressions? (Choose three.). They can be nested. They cannot be used in the VALUES clause of an INSERT statement. A scalar subquery expression that returns zero rows evaluates to zero. They can be used as default values for columns in a CREATE TABLE statement. A scalar subquery expression that returns zero rows evaluates to NULL. They cannot be used in GROUP BY clauses.

Which two statements are true about an Oracle database? (Choose two.). A table can have multiple primary keys. A column definition can specify multiple data types. A table can have multiple foreign keys. A VARCHAR2 column without data has a NULL value. A NUMBER column without data has a zero value.

Which two statement are true?. Both CASE and DECODE are functions. Neither CASE not DECODE are functions. CASE is a function and DECODE is not. All conditions evaluated using CASE can also be evaluated using DECODE. All condition evaluated using DECODE can also be evaluated using CASE. DECODE is a function and CASE is not.

Which two statements are true about Entity Relationships?. A one-to-one relationship is always a se referencing relationship. A table name can be specified just once when selecting data from a table having a self referencing relationship. A many-to-many relationship can be implemented only by using foreign keys. A relationship can be mandatory for both entities. A one to many relationship in one direction is a one-to-one relationship in the other direction.

6-Examine these statements which execute successfully CREATE USER finance IDENTIFIED BY print CREATE USER fin manager IDENTIFIED BT punge CREATE USER fin clerk IDENTIFIED BY pwclerk GRANT CREATE SESSION TO finance, fin clerks GRANT SELECT ON scott.emp To finance WITH GRANT OPTION CONNECT finance/pwfin GRANT SELECT ON scott.emp To fin_clerk: Which two are true?. Revoking SELECT on SCOTT, EMP from user FINANCE will also revoke the privilege from user FIN_CLERX. Dropping user FINANCE will automatically revoke SELECT on SCOTT.EMP from user FIN_CLERX. User FINANCE is unable to grant ALL on SCOTT.EMP to FIN_MANAGER. User FIN CLERK can grant SELECT on SCOTT EMP to user FIN_MANAGER. User FINANCE can grant CREATE SESSION to user FIN_MANAGER.

Which two statements are true about substitution variables?. A substitution variable used to prompt for a column name must be enclosed in single quotation marks. A substitution variable can be used only in a statement. A substitution variable can be used with any clause in a SELECT statement. A substitution variable prefixed with && prompts only once for a value in a session unless it is set to undefined in the sesión. A substitution variable used to prompt for a column name must be enclosed in double quotation marks. A substitution variable prefixed with & always prompts only once for a value in a sesión.

Which two queries only return CUBE? (Choose two.). A. B. C. D. E.

Examine this statement which returns the name of each employee and their manager SELECT e.last name as emp Allplast Ar FROM employees a JOIN managers a ON manager dmployee ad ORDER BY emp; You want to extend the query to include employees with no manager. What must you add before JOIN to do this?. CROSS. RIGHT OUTER. LEFT OUTER. FULL OUTER.

Examine these statements and the result CREATE SEQUENCE Customers CACHE 10 SELECT customer seq.NEXTVAL FROM DIAL: NEXTVAL 1 Now examine this command: ALTER SEQUENCE customer_seq; What must replace MISSING CLAUSE for CUSTOMER SEQ.NEXTVAL to return 11?. INCREMENT BY 10. CYCLE 11. MINVALUE 11. START WITH 11. MINVALUE 11.

Examine this incomplete query SELECT DATE 2019-01-01 <INTERVAL CLAUSE> FROM DUAL; Which three clauses can replace <INTERVAL CLAUSE>to add 12 hours to the date?. INTERVAL 12:00 HOUR TO SECOND. INTERVAL '12' HOUR. INTERVAL 0 12 DAY TO MOOR. INTERVAL 0.5' DAY. INTERVAL 11:40 HOUR TO MIMITE. INTERVAL 720 MINUTE.

Examine the description of the EMPLOYEES table: Which query is valid?. SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id;. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id;. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id, join_date;. SELECT dept_id, MAX(AVG(salary)) FROM employees GROUP BY dept_id;.

Which two are true about external tables that use the ORACLE DATAPP access driver?. Creating an external table creates a dump the that can be used by an external table in the same or a different database. When creating an external table data can be selected only from a table whose rows are stored in database blocks. Creating an external table creates a dump file that can be used only by an external table in the same database. When creating an external table. data can be selected from another external table or from a table whose rows are stored in database blocks. Creating an external table creates a directory object.

Which two statements are true about the data dictionary?. The data dictionary is accessible when the database is closed. The data dictionary does not store metadata in tables. Views with the prefix ALL_, DBA_ and USER_ are not all available for every type of metadata. Views with the prefix DBA_ display only metadata for objects in the SYS schema. Views with the prefix ALL_ display metadata for objects to which the current user has access.

Which two are SQL features? (Choose two.). providing graphical capabilities. processing sets of data. providing database transaction control. providing update capabilities for data in external files. providing variable definition capabilities.

Examine the description of the ORDERS table: Which three statements execute successfully? (Choose three.). SELECT * FROM orders ORDER BY order_id INTERSECT - SELECT * FROM invoices ORDER BY invoice_id;. (SELECT * FROM orders UNION ALL - SELECT * FROM invoices) ORDER BY order_id;. SELECT order_id, order_date FROM orders UNION ALL - SELECT invoice_id, invoice_date FROM invoices ORDER BY order_id;. SELECT * FROM orders - MINUS - SELECT * FROM invoices ORDER BY 1;. SELECT order_id invoice_id, order_date FROM orders MINUS - SELECT invoice_id, invoice_date FROM invoices ORDER BY invoice_id;. SELECT * FROM orders ORDER BY order_id UNION - SELECT * FROM invoices;. SELECT order_id, order_date FROM orders INTERSECT - SELECT invoice_id, invoice_date FROM invoices ORDER BY invoice_id;.

Which four statements are true about constraints on Oracle tables? (Choose four.). A PRIMARY KEY constraint can be added after a table has been created and populated. A FOREIGN KEY column can contain NULLs. A CHECK constraint can refer to values in other rows. A NOT NULL constraint can be defined at the table level. A UNIQUE constraint can use a pre-existing index on the constrained column or columns. A UNIQUE constraint permits NULLs. A column can have only one CHECK constraint.

Which three statements are true about time zones, date data types, and timestamp data types in an Oracle database? (Choose three.). The CURRENT_TIMESTAMP function returns data without time zone information. A TIMESTAMP WITH LOCAL TIMEZONE data type column is stored in the database using the time zone of the session that inserted the row. A TIMESTAMP data type column contains information about year, month, and day. The DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC). The SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC).

Which two statements are true about date/time functions in a session where NLS_DATE_FORMAT is set to DD-MON-YYYY HH24:MI:SS? (Choose two.). CURRENT_TIMESTAMP returns the same date and time as SYSDATE with additional details of fractional seconds. SYSDATE can be queried only from the DUAL table. CURRENT_DATE returns the current date and time as per the session time zone. SYSDATE can be used in expressions only if the default date format is DD-MON-RR. SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server. CURRENT_TIMESTAMP returns the same date as CURRENT_DATE.

Examine this query and its output: Which two are true about operators that can be used in the WHERE clause? (Choose two.). Using <> ANY will display all the product names except the product named Fork. Using IN will display all the product names. Using NOT IN or <> ANY will give the same result. Using <> ANY will display all the product names. Using NOT IN or <> ANY will give the same result.

Which two are true about rollbacks?. Data consistency is not guaranteed after a rollback. The ROLLBACK statement does not release locks resulting from table updates. If the ROLLBACK statement is used without TO SAVE POINT, then all savepoints in the transaction are deleted. A transaction Interrupted by a system failure is automatically rolled back. Data Control Language (DCL) statements, such as GRANT and REVOKE, can be rolled back.

Which statement will return a comma-separated list of employee names in alphabetical order for each department in the EMP table?. SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP (GROUP BY deptno) AS employee_list FROM emp ORDER BY ename;. SELECT deptno,LISTAGG(ename, ' , ') WITHIN GROUP AS employee_list FROM emp GROUP BY deptno;. SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY ename) AS employee_list FROM emp GROUP BY deptno;. SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP AS employee_list FROM emp GROUP BY deptno ORDER BY ename;.

hich two are true about the MERGE statement? (Choose two.). The WHEN NOT MATCHED clause can be used to specify the deletions to be performed. The WHEN NOT MATCHED clause can be used to specify the updates to be performed. The WHEN NOT MATCHED clause can be used to specify the inserts to be performed. The WHEN WATCHED clause can be used to specify the inserts to be performed. The WHEN WATCHED clause can be used to specify the updates to be performed.

Which three statements are true about defining relations between tables in a relational database?. Primary key columns allow null values. Foreign key columns allow null values. Unique key columns allow null values. Every foreign key value must refer to a matching primary or unique key value. Every primary or unique key value must refer to a matching foreign key value.

Table ORDER_ITEMS contains columns ORDER_ID, UNIT_PRICE and QUANTITY, of data type NUMBER. Examine these SQL statements: Statement 1: SELECT MAX(unit_price * quantity) `Maximum Order` FROM order_items; Statement 2: SELECT MAX(unit_price * quantity) `Maximum Order` FROM order_items - GROUP BY order_id; Which two statements are true?. Statement 1 returns only one row of output. Statement 2 returns only one row of output. Both statements will return NULL if either UNIT_PRICE or QUANTITY contains NULL. Both the statements give the same output. Statement 2 may return multiple rows of output.

Examine these statements which execute successfully: Both statements display departments ordered by their average salaries. Which two are true? (Choose two.). Both statements will execute successfully if you add E.AVG_SAL to the select list. Both statements will display departments with no employees. Only the second statement will execute successfully if you add E.AVG_SAL to the select list. Only the first statement will display departments with no employees. Only the second statement will display departments with no employees. Only the first statement will execute successfully if you add E.AVG_SAL to the select list.

Which two queries execute successfully? (Choose two.). SELECT NULLIF(NULL, 100) FROM DUAL;. SELECT NULLIF(100, 'A') FROM DUAL;. SELECT COALESCE(100, 'A') FROM DUAL;. SELECT COALESCE(100, NULL, 200) FROM DUAL;. SELECT NULLIF(100, 100) FROM DUAL;.

47-Examine this description of the TRANSACTIONS table: Which two SQL statements execute successfully? (Choose two.). SELECT customer_id AS ג€CUSTOMER-IDג€, transaction_date AS DATE, amount + 100 ג€DUESג€ FROM transactions;. SELECT customer_id AS ג€CUSTOMER-IDג€, transaction_date AS ג€DATEג€, amount + 100 DUES FROM transactions. SELECT customer_id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount + 100 ג€DUES AMOUNTג€ FROM transactions;. SELECT customer_id CUSTID, transaction_date TRANS_DATE, amount + 100 DUES FROM transactions;. SELECT customer_id AS 'CUSTOMER-ID', transaction_date AS DATE, amount + 100 'DUES AMOUNT' FROM transactions;.

Examine this description of the EMP table: only departments where the total salary is greater than 3000, returned in no particular order. only departments where the total salary is greater than 3000, ordered by department. all departments and a sum of the salaries of employees with a salary greater than 3000. an error.

The ORDERS table has a primary key constraint on the ORDER_ID column. The ORDER_ITEMS table has a foreign key constraint on the ORDER_ID column, referencing the primary key of the ORDERS table. The constraint is defined with ON DELETE CASCADE. There are rows in the ORDERS table with an ORDER_TOTAL of less than 1000. Which three DELETE statements execute successfully? (Choose three.). DELETE order_id FROM orders WHERE order_total < 1000;. DELETE orders WHERE order_total < 1000. DELETE * FROM orders WHERE order_total < 1000;. DELETE FROM orders;. DELETE FROM orders WHERE order_total < 1000;.

On which two columns of the table will an index be created automatically? (Choose two.). ORDER_ID. ORDER_TOTAL. ORDER_DATE. PRODUCT_ID. STATUS. SERIAL_NO.

Which two statements are true about single row functions? (Choose two.). MOD : returns the quotient of a division operation. FLOOR : returns the smallest integer greater than or equal to a specified number. TRUNC : can be used with NUMBER and DATE values. CONCAT : can be used to combine any number of values. CEIL : can be used for positive and negative numbers.

Examine this statement: What is returned upon execution?. an error. 2 rows. 0 rows. 1 rows.

Examine these two queries and their output: SELECT deptno, dname FROM dept; How many rows will be displayed?. 64. 6. 3. 12.

Examine the description of the BOOKS_TRANSACTIONS table: Examine this partial SQL statement: SELECT * FROM books_transactions Which two WHERE conditions give the same result? (Choose two.). WHERE borrowed_date = SYSDATE AND (transaction_type = 'RM' AND member_id = 'A101' OR member_id = 'A102');. WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' OR member_id IN ('A101', 'A102');. WHERE borrowed_date = SYSDATE AND (transaction_type = 'RM' AND (member_id = 'A101' OR member_id = 'A102'));. WHERE borrowed_date = SYSDATE AND (transaction_type = 'RM' OR menber_id IN ('A101', 'A102'));. WHERE (borrowed_date = SYSDATE AND transaction_type - 'RM') OR member_id IN ('A101', VA102');.

Which two are true about multitable INSERT statements?. The conditional INSERT FIRST statement always inserts a row into a single table. The unconditional INSERT ALL statement must have the same number of columns in both the source and target tables. They can transform a row from a source table into multiple rows in a target table. The conditional INSERT ALL statement inserts rows into a singe table by aggregating source rows. They always use subqueries.

Which two are true about global temporary tables? (Choose two.). Indexes can be created on them. Backup and recovery operations are available for these tables. Their data is always stored in the default temporary tablespace of the user who created them. If the ON COMMIT clause is transaction-specific, all rows in the table are deleted after each COMMIT OR ROLLBACK. They can be created only by a user with the DBA role, but can be accessed by all users who can create a session. If the ON COMMIT clause is session-specific, the table is dropped when the session is terminated.

Examine the data in the ORDERS table: 2. 1. 3. 5 01-MAR-2019. 3 01-JAN-2015.

Which statement is true about TRUNCATE and DELETE?. For tables with multiple indexes and triggers, DELETE is faster than TRUNCATE. You can never TRUNCATE a table if foreign key constraints would be violated. You can DELETE rows from a table with referential integrity constraints. For large tables, DELETE is faster than TRUNCATE.

Examine the description of the EMPLOYEES table: Which statement will execute successfully, returning distinct employees with non-null first names?. SELECT first_name, DISTINCT last_name FROM employees WHERE first_name <> NULL;. SELECT first_name, DISTINCT last_name FROM employees WHERE first_name IS NOT NULL;. SELECT DISTINCT * FROM employees WHERE first_name IS NOT NULL;. SELECT DISTINCT * FROM employees WHERE first_name <> NULL;.

Examine the description of the EMPLOYEES table: A. B. C. D.

You execute these commands: Which two, used independently, can replace so the query returns 1?. ROLLBACK;. ROLLBACK TO SAVEPOINT post_insert;. ROLLBACK TO post_insert;. COMMIT;. COMMIT TO SAVEPOINT post_insert;.

Examine this description of the PRODUCTS table: Rows exist in this table with data in all the columns. You put the PRODUCTS table in read-only mode. Which three commands execute successfully on PRODUCTS? (Choose three.). CREATE INDEX price_idx ON products (price);. ALTER TABLE products SET UNUSED (expiry_date);. DROP TABLE products;. ALTER TABLE products DROP COLUMN expiry_date;. ALTER TABLE products DROP COLUMN expiry_date;. ALTER TABLE products DROP UNUSED COLUMNS;.