SQL Quiz By "The Cloud 9"
- 1.
With SQL, how can you delete the records where the "FirstName" is "Peter" in the Persons Table?
- A.
1. DELETE ROW FirstName='Peter' FROM Persons
- B.
2. DELETE FirstName='Peter' FROM Persons
- C.
3. DELETE FROM Persons WHERE FirstName = 'Peter'
- D.
4. None
Correct Answer
C. 3. DELETE FROM Persons WHERE FirstName = 'Peter'Explanation
The correct answer is option 3 because it uses the correct syntax to delete records from the Persons table where the FirstName is 'Peter'. The DELETE statement is followed by the FROM keyword to specify the table, and the WHERE clause is used to specify the condition for deletion, which is FirstName = 'Peter' in this case. Option 1 is incorrect because it uses an incorrect syntax for the DELETE statement. Option 2 is also incorrect because it does not specify the table to delete from. Option 4 is incorrect because there is a valid way to delete the records in this scenario.Rate this question:
-
- 2.
For which two types of constraints will a unique index be automatically created??
- A.
Unique
- B.
Primary key
- C.
Not null
- D.
Foreign key
- E.
Check
Correct Answer(s)
B. Primary key
D. Foreign keyExplanation
A unique index is automatically created for the primary key constraint because it ensures that each value in the column is unique, which helps in identifying each row uniquely. Similarly, a unique index is also automatically created for the foreign key constraint because it ensures the referential integrity between two tables, where the values in the foreign key column must match the values in the primary key column of the referenced table.Rate this question:
-
- 3.
OCI stands for
- A.
1. Open Call Interface
- B.
2. Oracle Call Interface
- C.
3. Oracle Communication Interface
- D.
4. Oracle Connection Interface
Correct Answer
B. 2. Oracle Call InterfaceExplanation
OCI stands for Oracle Call Interface. It is a programming interface that allows applications to interact with Oracle databases. It provides a set of functions that enable applications to perform tasks such as connecting to a database, executing SQL statements, and retrieving results. OCI is commonly used by developers to build high-performance and scalable applications that interact with Oracle databases.Rate this question:
-
- 4.
What best describes the relationship between indexes and SQL performance?
- A.
1. Indexes are only used in special cases
- B.
2. Indexes are used to make table storage more efficient
- C.
3. Indexes rarely make a difference in SQL performance
- D.
4. Indexes exist solely to improve query speed.
Correct Answer
D. 4. Indexes exist solely to improve query speed.Explanation
Indexes in SQL are data structures that are created on columns in a database table to improve the performance of queries. They allow the database to quickly locate and retrieve specific data, thereby improving the speed of query execution. Indexes can significantly enhance the performance of SQL queries by reducing the number of disk reads required to retrieve the desired data. Therefore, option 4, "Indexes exist solely to improve query speed," accurately describes the relationship between indexes and SQL performance.Rate this question:
-
- 5.
Consider a table TAB with a single row NAME CHARRIS Then, the query SELECT SUBSTR(Name,-3) FROM TAB gives
- A.
1. RIS
- B.
2. CHA
- C.
3. ARRIS
- D.
4. ARR
Correct Answer
A. 1. RISExplanation
The query SELECT SUBSTR(Name,-3) from TAB retrieves the substring of the "Name" column starting from the third character from the end. In this case, the value in the "Name" column is "CHARRIS", so the substring starting from the third character from the end would be "RIS". Therefore, the correct answer is 1. RIS.Rate this question:
-
- 6.
What is a TRUNCATE statement in Oracle?
- A.
1. A DDL command and can be rolled back
- B.
2. A DML command and cannot be rolled back
- C.
3. A DDL command and cannot be rolled back.
- D.
4. A TCL command and can be rolled back
Correct Answer
C. 3. A DDL command and cannot be rolled back.Explanation
The TRUNCATE statement in Oracle is a Data Definition Language (DDL) command used to quickly delete all rows from a table. It is not possible to rollback a TRUNCATE statement because it is a DDL command, not a Data Manipulation Language (DML) command. DDL commands are used to define or modify the structure of database objects, while DML commands are used to manipulate data within the database.Rate this question:
-
- 7.
Examine the data in the EMPLOYEES and DEPARTMENTS tables. EMPLOYEES LAST_NAME DEPARTMENT_ID SALARY Getz 10 3000 Davis 20 1500 King 20 2200 Davis 30 5000 Kochhar 5000 DEPARTMENTS DEPARTMENT_ID DEPARTMENT_NAME 10 Sales 20 Marketing 30 Accounts 40 Administration You want to retrieve all employees, whether or not they have matching departments in the departments table. Which query would you use?
- A.
1. SELECT last_name, department_name FROM employees , departments(+);
- B.
2. SELECT last_name, department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);
- C.
3. SELECT last_name, department_name FROM employees(+) e JOIN departments d ON (e.department_id = d.department_id);
- D.
4. SELECT last_name, department_name FROM employees(+) , departments ON (e.department_id = d.department_id);
Correct Answer
B. 2. SELECT last_name, department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);Explanation
The correct query to retrieve all employees, whether or not they have matching departments in the departments table is option 2. This query uses a LEFT OUTER JOIN to join the EMPLOYEES table with the DEPARTMENTS table on the department_id column. The "e" and "d" are aliases for the EMPLOYEES and DEPARTMENTS tables, respectively. The LEFT OUTER JOIN ensures that all rows from the EMPLOYEES table are included in the result, even if there is no matching row in the DEPARTMENTS table. The result will include the last_name column from the EMPLOYEES table and the department_name column from the DEPARTMENTS table.Rate this question:
-
- 8.
The OR operator displays a record if ANY conditions listed are true. The AND operator displays a record if ALL of the conditions listed are true
- A.
True
- B.
False
- C.
Bath correct
- D.
None
Correct Answer
A. TrueExplanation
The given statement explains the functionality of the OR and AND operators in displaying records. The OR operator will display a record if any of the conditions listed are true, while the AND operator will only display a record if all of the conditions listed are true. Therefore, the answer "true" is correct as it accurately describes the behavior of these operators.Rate this question:
-
- 9.
How can you change "Hansen" into "Nilsen" in the "LastName" column in the Persons table?
- A.
1. MODIFY Persons SET LastName='Nilsen' WHERE LastName='Hansen'
- B.
2. UPDATE Persons SET LastName='Hansen' INTO LastName='Nilsen'
- C.
3. UPDATE Persons SET LastName='Nilsen' WHERE LastName='Hansen'
- D.
4. MODIFY Persons SET LastName='Hansen' INTO LastName='Nilsen
Correct Answer
C. 3. UPDATE Persons SET LastName='Nilsen' WHERE LastName='Hansen'Explanation
The correct answer is 3. UPDATE Persons SET LastName='Nilsen' WHERE LastName='Hansen'. This query updates the value of the LastName column from 'Hansen' to 'Nilsen' in the Persons table. The WHERE clause ensures that only the rows where the LastName is 'Hansen' are updated.Rate this question:
-
- 10.
You need to give the MANAGER role the ability to select from, insert into, and modify existing rows in the STUDENT_GRADES table. Anyone given this MANAGER role should be able to pass those privileges on to others. Which statement accomplishes this?
- A.
GRANT select, insert, update ON student_grades TO manager;
- B.
GRANT select, insert, update ON student_grades TO ROLE manager;
- C.
GRANT select, insert, modify ON student_grades TO manager WITH GRANT OPTION;
- D.
GRANT select, insert, update ON student_grades TO manager WITH GRANT OPTION;
- E.
GRANT select, insert, update ON student_grades TO ROLE manager WITH GRANT OPTION;
Correct Answer
D. GRANT select, insert, update ON student_grades TO manager WITH GRANT OPTION;Explanation
The correct answer is "GRANT select, insert, update ON student_grades TO manager WITH GRANT OPTION." This statement grants the MANAGER role the ability to select from, insert into, and modify existing rows in the STUDENT_GRADES table. Additionally, the WITH GRANT OPTION allows anyone given the MANAGER role to pass on these privileges to others.Rate this question:
-
- 11.
The database administrator of your company created a public synonym called HR for the HUMAN_RESOURCES table of the GENERAL schema, because many users frequently use this table. As a user of the database, you created a table called HR in your schema. What happens when you execute this query? SELECT * FROM HR;
- A.
You obtain the results retrieved from the public synonym HR created by the database administrator.
- B.
You obtain the results retrieved from the HR table that belongs to your schema.
- C.
You get an error message because you cannot retrieve from a table that has the same name as a public synonym.
- D.
You obtain the results retrieved from both the public synonym HR and the HR table that belongs to your schema, as a Cartesian product.
Correct Answer
B. You obtain the results retrieved from the HR table that belongs to your schema.Explanation
When you execute the query SELECT * FROM HR, you obtain the results retrieved from the HR table that belongs to your schema. This is because when you create a table in your schema with the same name as a public synonym, the table in your schema takes precedence over the public synonym. Therefore, the query will retrieve the data from your table rather than the public synonym.Rate this question:
-
- 12.
Which statement about views is true?
- A.
A view can be created as read only.
- B.
A view can be created as a join on two or more tables.
- C.
A view must have aliases defined for the column names in the SELECT statement.
- D.
A view cannot be created with a GROUP BY clause in the SELECT statement.
Correct Answer
B. A view can be created as a join on two or more tables.Explanation
A view can be created as a join on two or more tables. This means that a view can be created by combining data from multiple tables into a single virtual table. This allows for easier data retrieval and analysis, as the view can be treated like a regular table. By joining tables in a view, it is possible to access and manipulate data from multiple sources in a more efficient and organized manner.Rate this question:
-
- 13.
Examine the description of the EMPLOYEES table: EMP_ID NUMBER(4) NOT NULL LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(30) DEPT_ID NUMBER(2) JOB_CAT VARCHARD2(30) SALARY NUMBER(8,2) Which statement shows the maximum salary paid in each job category of each department?
- A.
SELECT dept_id, job_cat, MAX(salary) FROM employees WHERE salary > MAX(salary);
- B.
SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id, job_cat;
- C.
SELECT dept_id, job_cat, MAX(salary) FROM employees;
- D.
SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id;
Correct Answer
B. SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id, job_cat;Explanation
The correct answer is "SELECT dept_id, job_cat, MAX(salary)
FROM employees
GROUP BY dept_id, job_cat;"
This statement uses the GROUP BY clause to group the records by department and job category. The MAX(salary) function is used to find the maximum salary within each group. This query will return the maximum salary paid in each job category of each department.Rate this question:
-
- 14.
Management has asked you to calculate the value 12*salary* commission_pct for all the employees in the EMP table. The EMP table contains these columns: LAST NAME VARCHAR2(35) NOT NULL SALARY NUMBER(9,2) NOT NULL COMMISION_PCT NUMBER(4,2) Which statement ensures that a value is displayed in the calculated columns for all employees?
- A.
SELECT last_name, 12*salary* commission_pct FROM emp;
- B.
SELECT last_name, 12*salary* (commission_pct,0) FROM emp;
- C.
SELECT last_name, 12*salary*(nvl(commission_pct,0)) FROM emp;
- D.
SELECT last_name, 12*salary*(decode(commission_pct,0)) FROM emp;
Correct Answer
C. SELECT last_name, 12*salary*(nvl(commission_pct,0)) FROM emp;Explanation
The correct answer is SELECT last_name, 12*salary*(nvl(commission_pct,0))
FROM emp. This statement uses the NVL function to replace any NULL values in the commission_pct column with 0. This ensures that a value is displayed in the calculated columns for all employees, even if they do not have a commission_pct value.Rate this question:
-
- 15.
Which syntax turns an existing constraint on?
- A.
ALTER TABLE table_name ENABLE constraint_name;
- B.
ALTER TABLE table_name STATUS = ENABLE CONSTRAINT constraint_name;
- C.
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;
- D.
ALTER TABLE table_name STATUS ENABLE CONSTRAINT constraint_name;
- E.
ALTER TABLE table_name TURN ON CONSTRAINT constraint_name;
Correct Answer
C. ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;Explanation
The correct answer is "ALTER TABLE table_name ENABLE CONSTRAINT constraint_name." This syntax is used to turn on or enable an existing constraint on a table. By executing this command, the specified constraint will be activated and enforced for future data modifications on the table.Rate this question:
-
- 16.
Examine the description of the STUDENTS table: STD_ID NUMBER(4) COURSE_ID VARCHARD2(10) START_DATE DATE END_DATE DATE Which two aggregate functions are valid on the START_DATE column? (Choose two)
- A.
SUM(start_date)
- B.
AVG(start_date)
- C.
COUNT(start_date)
- D.
AVG(start_date, end_date)
- E.
MIN(start_date)
- F.
MAXIMUM(start_date)
Correct Answer(s)
C. COUNT(start_date)
E. MIN(start_date) -
- 17.
You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty. Which statement accomplishes this task?
- A.
ALTER TABLE students ADD PRIMARY KEY student_id;
- B.
ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);
- C.
ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;
- D.
ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
- E.
ALTER TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
Correct Answer
D. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);Explanation
The correct statement to add a primary key on the STUDENT_ID column in the STUDENTS table is "ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);". This statement adds a constraint named "stud_id_pk" to the table, specifying that the student_id column should be the primary key.Rate this question:
-
- 18.
Which three are DATETIME data types that can be used when specifying column definitions? (Choose three.)
- A.
TIMESTAMP
- B.
INTERVAL MONTH TO DAY
- C.
INTERVAL DAY TO SECOND
- D.
INTERVAL YEAR TO MONTH
- E.
TIMESTAMP WITH DATABASE TIMEZONE
Correct Answer(s)
A. TIMESTAMP
C. INTERVAL DAY TO SECOND
D. INTERVAL YEAR TO MONTHExplanation
The three DATETIME data types that can be used when specifying column definitions are TIMESTAMP, INTERVAL DAY TO SECOND, and INTERVAL YEAR TO MONTH. These data types allow for the storage and manipulation of date and time values. TIMESTAMP represents a point in time, while INTERVAL DAY TO SECOND represents a duration in terms of days, hours, minutes, and seconds. INTERVAL YEAR TO MONTH represents a duration in terms of years and months. These data types are commonly used in database systems to handle date and time-related operations and calculations.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 -
Oct 24, 2011Quiz Created by
Gaurav.4086
Back to top