Oracle Mock Test 1
Mock test for 10g oracle
- 1.
Evaluate the SQL statement: SELECT ROUND(TRUNC(MOD(1600,10),-1),2) FROM dual; What will be displayed?
- A.
0
- B.
1
- C.
0.00
- D.
An error statement
Correct Answer
A. 0Explanation
The SQL statement is using the functions ROUND, TRUNC, and MOD to perform calculations on the number 1600. The MOD function calculates the remainder when 1600 is divided by 10, which is 0. The TRUNC function then truncates this result to the nearest multiple of 10, which is also 0. Finally, the ROUND function rounds this result to 2 decimal places, which is still 0. Therefore, the output of the SQL statement will be 0.Rate this question:
-
- 2.
You want to display the titles of books that meet these criteria: 1. Purchased before January 21, 2001 2. Price is less then $500 or greater than $900 You want to sort the results by their data of purchase, starting with the most recently bought book. Which statement should you use?
- A.
SELECT book_title FROM books WHERE price between 500 and 900 AND purchase_date < ’21-JAN-2001’ ORDER BY purchase_date;
- B.
SELECT book_title FROM books WHERE price IN (500,900) AND purchase_date < ’21-JAN-2001’ ORDER BY purchase date ASC;
- C.
. SELECT book_title FROM books WHERE price < 500 or > 900 AND purchase_date < ’21-JAN-2001’ ORDER BY purchase date DESC;
- D.
SELECT book_title FROM books WHERE (price < 500 OR price > 900) AND purchase_date < ’21-JAN-2001’ ORDER BY purchase date DESC;
Correct Answer
D. SELECT book_title FROM books WHERE (price < 500 OR price > 900) AND purchase_date < ’21-JAN-2001’ ORDER BY purchase date DESC;Explanation
The correct answer is the last option because it correctly includes the criteria of purchasing before January 21, 2001 and having a price less than $500 or greater than $900. The ORDER BY clause is used to sort the results by the purchase date in descending order, starting with the most recently bought book.Rate this question:
-
- 3.
In which case would you use FULL OUTER JOIN?
- A.
You want all unmatched data from one table.
- B.
You want all matched data from both tables.
- C.
You want all unmatched data from both tables
- D.
One of the tables has more data than other.
Correct Answer
C. You want all unmatched data from both tablesExplanation
A FULL OUTER JOIN is used when you want to retrieve all unmatched data from both tables. This means that you want to include all records from both tables, even if they do not have a matching record in the other table. This is useful when you want to combine data from two tables and include all the information available, regardless of whether it has a match or not.Rate this question:
-
- 4.
Which of the following stores data externally?
- A.
BFILE
- B.
BLOB
- C.
CLOB
- D.
LONG RAW
Correct Answer
A. BFILEExplanation
BFILE is the correct answer because it is used to store large binary files outside of the database, such as images, audio, or video files. BLOB, CLOB, and LONG RAW are all types of data that can be stored internally within the database.Rate this question:
-
- 5.
Which operator can be used with a multiple row subquery?
- A.
=
- B.
LIKE
- C.
NOT IN
- D.
BETWEEN
- E.
IS
Correct Answer
C. NOT INExplanation
The NOT IN operator can be used with a multiple row subquery to exclude values that are present in the subquery result from the main query result. It returns all rows from the main query where the value is not found in the subquery result.Rate this question:
-
- 6.
Which is a major problem with SQL?
- A.
SQL cannot support object-orientation
- B.
The same query can be written in many ways, each with vastly different execution plans.
- C.
SQL syntax is too difficult for non-computer professionals to use
- D.
SQL creates excessive locks within the Oracle database
Correct Answer
A. SQL cannot support object-orientationExplanation
SQL is a relational database language that is not designed to support object-oriented programming concepts. Object-orientation allows for the encapsulation of data and behavior into objects, which SQL lacks. SQL is primarily focused on managing and manipulating structured data in a relational database management system. Therefore, it is not suitable for implementing object-oriented concepts like inheritance, polymorphism, and encapsulation.Rate this question:
-
- 7.
You need to display the last names of those employees who have the letter "A" as the second character in their names. Which SQL statement displays the required results?
- A.
SELECT last_name FROM EMP WHERE last_name LIKE'_A%;
- B.
SELECT last_name FROM EMP WHERE last name='*A%
- C.
SELECT last_name FROM EMP WHERE last name ='* _A%;
- D.
SELECT last_name FROM EMP WHERE last name LIKE '* a%
Correct Answer
A. SELECT last_name FROM EMP WHERE last_name LIKE'_A%;Explanation
The correct answer is "SELECT last_name FROM EMP WHERE last_name LIKE '_A%;". This SQL statement uses the LIKE operator with a wildcard character (_) to match any single character and the letter "A" as the second character in the last name. This will retrieve the last names of employees who have the letter "A" as the second character in their names.Rate this question:
-
- 8.
Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) Which three statements inserts a row into the table? (Choose three)
- A.
INSERT INTO employees VALUES ( NULL, ‘John’,‘Smith’);
- B.
INSERT INTO employees( first_name, last_name) VALUES(‘John’,‘Smith’);
- C.
INSERT INTO employees VALUES (‘1000’,‘John’,NULL);
- D.
INSERT INTO employees(first_name,last_name, employee_id) VALUES ( 1000, ‘John’,‘Smith’);
- E.
. INSERT INTO employees (employee_id) VALUES (1000);
- F.
INSERT INTO employees (employee_id, first_name, last_name) VALUES ( 1000, ‘John’,‘’);
Correct Answer(s)
C. INSERT INTO employees VALUES (‘1000’,‘John’,NULL);
E. . INSERT INTO employees (employee_id) VALUES (1000);
F. INSERT INTO employees (employee_id, first_name, last_name) VALUES ( 1000, ‘John’,‘’);Explanation
The first statement inserts a row into the table by providing values for all columns, including a NULL value for the EMPLOYEE_ID column. The second statement inserts a row by providing values for the FIRST_NAME and LAST_NAME columns. The third statement inserts a row by providing a value for the EMPLOYEE_ID column and NULL values for the FIRST_NAME and LAST_NAME columns.Rate this question:
-
- 9.
Mark for review View the image below and examine the data in the EMPLOYEES and DEPARTMENTS tables. You want to retrieve all employees' last names, along with their managers' last names and their department names. Which query would you use?
- A.
SELECT last_name, manager_id, department_name FROM employees e FULL OUTER JOIN departments d ON (e.department_id = d.department_id);
- B.
SELECT e.last_name, m.last_name, department_name FROM employees e LEFT OUTER JOIN employees m on ( e.manager_id = m.employee_id) LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);
- C.
SELECT e.last_name, m.last_name, department_name FROM employees e RIGHT OUTER JOIN employees m on ( e.manager_id = m.employee_id) LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);
- D.
SELECT e.last_name, m.last_name, department_name FROM employees e LEFT OUTER JOIN employees m on ( e.manager_id = m.employee_id) RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id);
- E.
SELECT e.last_name, m.last_name, department_name FROM employees e RIGHT OUTER JOIN employees m on ( e.manager_id = m.employee_id) RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id);
- F.
SELECT last_name, manager_id, department_name FROM employees e JOIN departments d ON (e.department_id = d.department_id) ;
Correct Answer
B. SELECT e.last_name, m.last_name, department_name FROM employees e LEFT OUTER JOIN employees m on ( e.manager_id = m.employee_id) LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);Explanation
The correct query to retrieve all employees' last names, along with their managers' last names and their department names, is the one that uses a LEFT OUTER JOIN to join the employees table with itself on the manager_id column, and then uses another LEFT OUTER JOIN to join the result with the departments table on the department_id column. This query ensures that all employees are included in the result, even if they do not have a manager or department.Rate this question:
-
- 10.
DML operations be performed on our table through a complex view. True or false
- A.
True
- B.
False
Correct Answer
B. FalseExplanation
DML operations cannot be performed on a complex view. A complex view is a view that is created using multiple tables or subqueries, and it may involve joins, aggregations, or other complex operations. While DML operations like INSERT, UPDATE, and DELETE can be performed on simple views (views created from a single table), they are not allowed on complex views. Therefore, the statement is false.Rate this question:
-
- 11.
Select TCL statements (Multiple answers)
- A.
DROP
- B.
COMMIT
- C.
ROLLBACK
- D.
DELETE
- E.
SAVEPOINT
Correct Answer(s)
B. COMMIT
C. ROLLBACK
E. SAVEPOINTExplanation
The given question is asking to select the TCL (Transaction Control Language) statements. TCL statements are used to manage transactions in a database. The correct answers are COMMIT, ROLLBACK, and SAVEPOINT. COMMIT is used to permanently save the changes made in a transaction, ROLLBACK is used to undo the changes made in a transaction, and SAVEPOINT is used to set a point in a transaction from where the changes can be rolled back.Rate this question:
-
- 12.
The CUSTOMERS table has these columns: CUSTOMER_ID NUMBER (4) NOT NULL CUSTOMER_NAME VARCHAR2 (100) NOT NULL CUSTOMER_ADDRESS VARCHAR2 (150) CUSTOMER_PHONE VARCHAR2 (20) You need to produce output that states "Dear Customer customer_name”. The customer_name data values come from the CUSTOMER_NAME column in the CUSTOMERS table. Which statement produces this output?
- A.
SELECT dear customer, customer_name, FROM customers;
- B.
SELECT "Dear Customer", customer_name || ',' FROM customers;
- C.
SELECT 'Dear Customer ' || customer_name ',' FROM customers;
- D.
SELECT 'Dear Customer ' || customer_name || ',' || FROM customers;
- E.
SELECT 'Dear Customer ' || customer_name || ',' || FROM customers;
- F.
SELECT 'Dear Customer ' || customer_name || ',' FROM customers;
Correct Answer
F. SELECT 'Dear Customer ' || customer_name || ',' FROM customers;Explanation
The correct answer is "SELECT 'Dear Customer ' || customer_name || ',' FROM customers;". This statement concatenates the string 'Dear Customer ' with the values from the customer_name column in the CUSTOMERS table, followed by a comma. This will produce the desired output of "Dear Customer customer_name" for each row in the table.Rate this question:
-
- 13.
What is a TRUNCATE statement in Oracle?
- A.
A DDL command and can be rolled back
- B.
A DDL command and cannot be rolled back
- C.
A TCL command and can be rolled back
- D.
A DML command and cannot be rolled back
Correct Answer
B. A DDL command and cannot be rolled backExplanation
The TRUNCATE statement in Oracle is a Data Definition Language (DDL) command that is used to remove all data from a table. Unlike other DDL commands like DROP or ALTER, the TRUNCATE statement cannot be rolled back. Once the TRUNCATE statement is executed, the data is permanently deleted from the table and cannot be recovered. Therefore, it is important to use the TRUNCATE statement with caution as it cannot be undone.Rate this question:
-
- 14.
Examine the data in the EMPLOYEES table: LAST_NAME DEPARTMENT_ID SALARY Getz 10 3000 Davis 20 1500 King 20 2200 Davis 30 5000 … Which three subqueries work? (Choose three)
- A.
SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary)
- B.
SELECT last_name FROM employees Where salary > ANY (SELECT MAX(salary) FROM employees GROUP BY department_id);
- C.
SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);
- D.
SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id);
- E.
SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);
- F.
SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY department.id);
Correct Answer(s)
B. SELECT last_name FROM employees Where salary > ANY (SELECT MAX(salary) FROM employees GROUP BY department_id);
C. SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);
E. SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);Explanation
The first subquery "SELECT last_name FROM employees Where salary > ANY (SELECT MAX(salary) FROM employees GROUP BY department_id)" works because it returns the last names of employees whose salary is greater than the maximum salary in their respective departments.
The second subquery "SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id)" works because it returns the department IDs of departments where all employees have a salary greater than the average salary of their department.
The third subquery "SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id)" works because it returns the distinct department IDs where at least one employee has a salary greater than the average salary of their department.Rate this question:
-
- 15.
How would you add a foreign key constraint on the dept_no column in the EMP table. Referring to the ID column in the DEPT table?
- A.
Use the ALTER TABLE command with the ADD clause on the EMP table.
- B.
Use the ALTER TABLE command with the ADD clause in the DEPT table.
- C.
Use the ALTER TABLE command with the MODIFY clause on the DEPT table.
- D.
Use the ALTER TABLE command with the MODIFY clause on the EMP table.
- E.
This task cannot be accomplished
Correct Answer
A. Use the ALTER TABLE command with the ADD clause on the EMP table.Explanation
To add a foreign key constraint on the dept_no column in the EMP table, the correct approach is to use the ALTER TABLE command with the ADD clause on the EMP table. This allows us to specify the foreign key constraint and reference the ID column in the DEPT table. By using the ADD clause on the EMP table, we can define the foreign key relationship between the two tables and ensure data integrity by enforcing referential integrity rules.Rate this question:
-
- 16.
Mark for review Evaluate the SQL statement: SELECT ROUND (45.953, -1), TRUNC (45.936, 2) FROM dual; Which values are displayed?
- A.
46 and 45.93
- B.
50 and 45.93
- C.
50 and 45.9
- D.
46 and 45
- E.
45.95 and 46
Correct Answer
B. 50 and 45.93Explanation
The SQL statement is using the ROUND and TRUNC functions on two numbers: 45.953 and 45.936. The ROUND function with a second argument of -1 rounds the first number to the nearest tens place, resulting in 50. The TRUNC function with a second argument of 2 truncates the second number to two decimal places, resulting in 45.93. Therefore, the values displayed are 50 and 45.93.Rate this question:
-
- 17.
What is the syntax for removing a PRIMARY KEY constraint and all its dependent constraints?
- A.
ALTER TABLE table_name DROP PRIMARY KEY CASCADE;
- B.
ALTER TABLE table_name DISABLE CONSTRAINT PRIMARY KEY CASCADE;
- C.
ALTER TABLE table_name REMOVE CONSTRAINT PRIMARY KEY CASCADE;
- D.
A PRIMARY KEY constraint CANNOT be removed if it has dependent constraints.
Correct Answer
A. ALTER TABLE table_name DROP PRIMARY KEY CASCADE;Explanation
The correct answer is "ALTER TABLE table_name DROP PRIMARY KEY CASCADE." This syntax is used to remove a PRIMARY KEY constraint and all its dependent constraints. The "DROP PRIMARY KEY" command removes the primary key constraint from the specified table, and the "CASCADE" keyword ensures that any dependent constraints are also removed.Rate this question:
-
- 18.
A subquery can be used to _________.
- A.
Create groups of data
- B.
Sort data in a specific order
- C.
Retrieve data based on an unknown condition
- D.
Convert data to a different format
Correct Answer
C. Retrieve data based on an unknown conditionExplanation
A subquery can be used to retrieve data based on an unknown condition. This means that the subquery can be used to fetch data from a table based on a condition that is not explicitly known or specified. The subquery can be used to dynamically determine the condition or filter criteria for retrieving the data, allowing for more flexibility in querying the database.Rate this question:
-
- 19.
You are granted the CREATE VIEW privilege. What does this allow you to do?
- A.
Create a view in any scheme
- B.
Create a view in your schema
- C.
Create a view only if it is based on tables that you created
- D.
Create a view that is accessible by everyone
- E.
Create a table view
Correct Answer
B. Create a view in your schemaExplanation
The CREATE VIEW privilege allows the user to create a view in their own schema. This means that they can create a view using tables and data that they have access to within their own schema. They do not have the ability to create a view in any other schema or create a view that is accessible by everyone. Additionally, the option of creating a table view is not mentioned as a possibility with the CREATE VIEW privilege.Rate this question:
-
- 20.
In a query with a GROUP BY clause, the columns in the SELECT clause
- A.
Must be aggregate functions only
- B.
Must also appear in the GROUP BY clause
- C.
Must also appear in the GROUP BY and HAVING clauses
Correct Answer
A. Must be aggregate functions onlyExplanation
In a query with a GROUP BY clause, the columns in the SELECT clause must be aggregate functions only. This means that when using GROUP BY, the SELECT clause can only include aggregate functions such as SUM, COUNT, AVG, etc. It is not possible to include individual columns in the SELECT clause without applying an aggregate function to them. The purpose of the GROUP BY clause is to group the data based on certain columns, and the SELECT clause should only display the aggregated results of those groups.Rate this question:
-
- 21.
Mark for review which two tasks can you perform using only the TO_CHAR function? (Choose two.)
- A.
Convert 10 to 'TEN'
- B.
Convert 10 to '10'
- C.
Convert '10' to 10
- D.
Convert a date to a character expression
- E.
Convert a character expression to a date
Correct Answer(s)
B. Convert 10 to '10'
D. Convert a date to a character expressionExplanation
Using the TO_CHAR function, you can convert a number (such as 10) to a character expression ('10') and you can also convert a date to a character expression.Rate this question:
-
- 22.
Evaluate the SQL statement: 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; What is the result of the statement?
- A.
The statement produces an error at line 1.
- B.
The statement produces an error at line 3.
- C.
The statement produces an error at line 6.
- D.
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.
- E.
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 then the maximum salary paid in the company
Correct Answer
D. 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.Explanation
The given SQL statement uses a subquery to find the maximum salary for each department and then joins it with the employees table. It selects the employee name, salary, department ID, and maximum salary for all employees whose salary is less than the maximum salary in their department. Therefore, 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.Rate this question:
-
- 23.
The ORDERS table has these columns ORDER_ID NUMBER (4) NOT NULL CUSTOMER_ID NUMBER (12) NOT NULL ORDER_TOTAL NUMBER (10, 2) The ORDERS table tracks the Order number, the order total and the customer to whom the Order belongs. Which two statements retrieve orders with an inclusive total that ranges between 100.00 and 200.00 dollars? (Choose Two).
- A.
SELECT customer_id, order_id, order_total FROM orders HAVING order total BETWEEN 100 and 2000
- B.
SELECT customer_id, order_id, order_total FROM orders WHERE order_total BETWEEN 100 and 2000
- C.
SELECT customer_id, order_id, order _total FROM orders WHERE order_total>= 100 and order_total
- D.
SELECT customer_id, order_id, order_total FROM orders WHERE order_total >= 100 and
Correct Answer(s)
B. SELECT customer_id, order_id, order_total FROM orders WHERE order_total BETWEEN 100 and 2000
C. SELECT customer_id, order_id, order _total FROM orders WHERE order_total>= 100 and order_totalExplanation
The first statement is incorrect because it uses the wrong range for the order total (100 to 2000 instead of 100 to 200). The second statement is correct because it uses the correct range for the order total (100 to 200). The third statement is incorrect because it uses the wrong syntax for the comparison operators (missing the second operand for the second comparison). The fourth statement is incorrect because it uses the wrong syntax for the comparison operators (missing the second operand for the second comparison).Rate this question:
-
- 24.
Mark for review. Which four are types of functions available in SQL? (Choose 4)
- A.
String
- B.
Character
- C.
Integer
- D.
Date
- E.
Translation
- F.
Conversion
- G.
Calendar
- H.
Numeric
Correct Answer(s)
B. Character
D. Date
F. Conversion
H. NumericExplanation
The four types of functions available in SQL are character functions, date functions, conversion functions, and numeric functions. Character functions are used to manipulate and analyze character data, such as extracting substrings or changing the case of letters. Date functions are used to perform calculations and manipulations on date and time values. Conversion functions are used to convert data types from one format to another. Numeric functions are used to perform calculations and manipulations on numeric data, such as finding the square root or rounding numbers.Rate this question:
-
- 25.
Evaluate the SQL statement DROP TABLE DEPT: Which four statements are true of the SQL statement? (Choose two)
- A.
All data in the table is deleted, and the table structure is also deleted.
- B.
All data in the table is deleted, but the structure of the table is retained.
- C.
All indexes based on the DEPT table are dropped.
- D.
All synonyms based on the DEPT table are deleted
- E.
All views based on the DEPT table are deleted.
Correct Answer(s)
A. All data in the table is deleted, and the table structure is also deleted.
C. All indexes based on the DEPT table are dropped.Explanation
The SQL statement DROP TABLE DEPT will delete all data in the table and also delete the table structure. Additionally, all indexes based on the DEPT table will be dropped.Rate this question:
-
- 26.
Which of the following queries can you use to search for employees with the pattern 'A_B' in their names?
- A.
SELECT last_name FROM employees WHERE last_name LIKE '%A\_B%' ESCAPE '\';
- B.
SELECT last_name FROM employees WHERE last_name LIKE 'A_B%' ESCAPE '%';
- C.
SELECT last_name FROM employees WHERE last_name LIKE '%A_B%' ESCAPE ;
- D.
SELECT last_name FROM employees WHERE last_name LIKE '%A\_B%' ESCAPE '\\';
Correct Answer
A. SELECT last_name FROM employees WHERE last_name LIKE '%A\_B%' ESCAPE '\';Explanation
The correct answer is "SELECT last_name FROM employees WHERE last_name LIKE '%A\_B%' ESCAPE '\';". This query uses the LIKE operator with the pattern '%A\_B%', where the underscore (_) is a wildcard representing any single character. The ESCAPE '\' clause is used to escape the underscore character so that it is treated as a literal character instead of a wildcard. This query will search for employees with names that have the pattern 'A_B', where the underscore represents any single character.Rate this question:
-
- 27.
Examine the structure of student table: Name Null Type STU ID NOT NULL NUMBER(3) NAME VARCHER2(25) ADDRESS VARCHER2(50) GRADUATION DATE Currently the table is empty. You have decided that null values should not be allowed for the NAME column. Which statement restricts NULL values from being entered into column?
- A.
ALTER TABLE student ADD CONSTRAINT name(NOT NULL);
- B.
ALTER TABLE student ADD CONSTRAINT NOT NULL (name);
- C.
ALTER TABLE student MODIFY CONSTRAINT name(NOT NULL);
- D.
ALTER TABLE student MODIFY(name varcher2(25) NOT NULL);
Correct Answer
D. ALTER TABLE student MODIFY(name varcher2(25) NOT NULL);Explanation
The correct answer is "ALTER TABLE student MODIFY(name varcher2(25) NOT NULL)". This statement modifies the structure of the table by adding a constraint to the NAME column, specifying that it cannot contain NULL values.Rate this question:
-
- 28.
Which of the following is not a number function
- A.
To_numbert()
- B.
Sinh()
- C.
Sqrt()
- D.
Round()
Correct Answer
A. To_numbert()Explanation
The function to_numbert() is not a valid number function. This can be inferred from the fact that the other options - sinh(), sqrt(), and round() - are commonly known mathematical functions that operate on numbers. However, to_numbert() does not follow the same pattern and is therefore not a number function.Rate this question:
-
- 29.
You want of display the details or all employees whose last names is Smith. But you are not sure in which case last names are stored. Which statement will list all the employees whose last name is Smith?
- A.
Select last name, first name. FROM emp WHERE last name= ‘smith’;
- B.
Select last name, first name. FROM emp WHERE UPPER (last name)= ‘smith’;
- C.
Select last name, first name. FROM emp WHERE last name=UPPER (‘smith’);
- D.
Select last name, first name. FROM emp WHERE LOWER (last name)= ‘smith’;
Correct Answer
D. Select last name, first name. FROM emp WHERE LOWER (last name)= ‘smith’;Explanation
The correct answer is to use the statement "Select last name, first name. FROM emp WHERE LOWER (last name)= 'smith';". This statement will list all the employees whose last name is "Smith" regardless of the case in which the last names are stored. By using the LOWER function, the last name will be converted to lowercase, allowing for a case-insensitive comparison with the string 'smith'.Rate this question:
-
- 30.
Which statement explicitly names a constraint?
- A.
ALTER TABLE student_grades ADD CONSTRAINT NAME=student_id_fk FOREIGN KEY (student_id) REFERENCES student(student_id);
- B.
ALTER TABLE student_grades ADD FOREIGN KEY (student_id) REFERENCES students (student_id);
- C.
ALTER TABLE student grades ADD NAMED CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students (student_id);
- D.
ALTER TABLE student_grades ADD CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students (student_id);
Correct Answer
D. ALTER TABLE student_grades ADD CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students (student_id);Explanation
The correct answer is the fourth option, "ALTER TABLE student_grades ADD CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students (student_id)." This statement explicitly names a constraint by using the keyword "CONSTRAINT" followed by the name "student_id_fk." It also specifies the foreign key constraint by using the keyword "FOREIGN KEY" and references the table "students" and the column "student_id."Rate this question:
-
Quiz Review Timeline +
Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.
-
Current Version
-
Mar 21, 2023Quiz Edited by
ProProfs Editorial Team -
Nov 20, 2011Quiz Created by
311570