Examine the data in the CUST_NAME column of the CUSTOMERS table: CUST_NAME --------------------- Renske Ladwig Jason Mallin Samuel McCain Allan MCEwen Irene Mikkilineni Julia Nayer You want to display the CUST_NAME values where the last name starts with Mc or MC. Which two WHERE clauses give the required result?
Correct Answer: A,E
To find customer names where the last name starts with Mc or MC, the correct queries are: A . WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name, ' ') + 1)) LIKE 'MC%' This query converts the substring of cust_name that comes after the first space (which should correspond to the last name) to uppercase and checks if it starts with 'MC', which will match both 'Mc' and 'MC'. E . WHERE SUBSTR(cust_name, INSTR(cust_name, ' ') + 1) LIKE 'Mc%' This query checks if the substring of cust_name after the first space starts with 'Mc'. However, this query will only match last names that start with 'Mc' and not 'MC' unless the database is using a case-insensitive collation. Options B, C, and D are incorrect: B is incorrect because the syntax is not valid in Oracle SQL; the LIKE clause cannot be used to match multiple patterns in that way. C is incorrect because INITCAP would not only capitalize the first letter of 'mc' or 'Mc' but would lowercase all other letters, which is not the intended action. D is incorrect because, like option C, INITCAP is not the appropriate function for this use case and it will not correctly identify names that start with 'MC'.
1z0-071 Exam Question 32
Examine the structure of the INVOICEtable. Which two SQL statements would execute successfully? SELECT inv_no, NVL2(inv_date, 'Pending', 'Incomplete')
Correct Answer: A,B
1z0-071 Exam Question 33
Examine this business rule: Each student can work on multiple projects and earth project can have multiple students. You must decide an Entity Relationship (ER) model for optional data storage and allow generating reports in this format: STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK Which two statements are true?
Correct Answer: A,B
For the described business rule, the relationship between the students and projects entities is a many-to-many relationship, meaning that each student can be involved in multiple projects and each project can have multiple students. * Statement A is true because to implement a many-to-many relationship in a relational database, an associative (junction) table is typically used. This table will contain the primary keys from both related entities as foreign keys in the associative table, making a composite primary key consisting of STUDENT_ID and PROJECT_ID. This setup allows the database to effectively manage the relationships between students and projects. * Statement B is true as it accurately describes the solution to managing a many-to-many relationship. A direct many-to-many relationship cannot be physically implemented in a relational database. It is instead resolved into two 1-to-many relationships using an associative table as described in A. This is a fundamental relational database design principle aimed at normalizing the database and avoiding redundancy. * Statements C, D, and E are incorrect as they misrepresent the nature of the relationships and key constraints necessary for this scenario. Specifically, C and E incorrectly suggest that one entity's primary key should serve as a foreign key in another, which does not align with the many-to-many relationship requirement described. Statement D incorrectly suggests a 1-to-many relationship directly between students and projects, which does not meet the business rule requirement.
1z0-071 Exam Question 34
Which three statements are true regarding subqueries?
