SQL Mock Test II - Tutorialspoint

1 mock TESTSQL mock TESTThis section presents you various set of mock tests related to SQL. You can download thesesample mock tests at your local machine and solve offline at your convenience. Every mock test issupplied with a mock test key to let you verify the final score and grade mock TEST IIISQL mock TEST IIIQ 1 - Which of the following is not true about multiple-row subqueries?A - Multiple row subqueries return multiple rows from the outer SELECT - Multiple row subqueries return multiple rows from the inner SELECT - Multiple row subqueries use multiple-row comparison - All of the 2 - Which of the following comparison operators could be used in a multiple rowquery?A - IN operatorB - ANY operatorC - ALL operatorD - All of the aboveQ 3 - In which of the following cases a DML statement is executed?A - When new rows are added to a - When a table is - When a transaction is - None of the 4 - In which of the following cases a DML statement is not executed?

2 A - When existing rows are modifiedB - When some rows are deletedC - When a table is deletedD - All of the aboveQ 5 - Which of the following is not true about inserting news rows to a table?A - The INSERT INTO statement is used for inserting new rows to a tableB - You cannot insert rows with NULL values to a - You may insert a new row containing values for each - All of the 6 - Which of the following is true about inserting news rows to a table?A - You must list values in the default order of the columns in the - You can also list the columns in the INSERT - You can use the INSERT statement to add rows from one table to - All of the 7 - Which of the following is not true about modifying rows in a table?A - Existing rows in a table are modified using the UPDATE - You can update more than one row at a - All the rows in a table are modified if you omit the WHERE - None of the 8 - Which of the following is true about modifying rows in a table?

3 A - You can update some rows in a table based on values from another - If you try to update a record related to an integrity constraint, it raises an - You can modify multiple - All of the 9 - Which of the following is true about removing rows from a table?A - You remove existing rows from a table using the DELETE statementB - No rows are deleted if you omit the WHERE - You cannot delete rows based on values from another - All of the 10 - Which of the following is not true about removing rows from a table?A - You can use a subquery in a DELETE - Specific rows are deleted based on the WHERE clause - A statement like, DELETE , would cause deletion of the table from the - All of the 11 - Which statement allows conditional update, or insertion of data into a tablesimultaneously?A - INSERT statementB - MERGE statementC - UPDATE statementD - None of the aboveQ 12 - A transaction starts whenA - A COMMIT statement is issuedB - A ROLLBACK statement is issuedC - A CREATE statement is usedD - All of the aboveQ 13 - Which of the following is true about the SQL transaction control statements?

4 A - They ensure data - They allow preview of data changes before making permanent changes in - They group logically related - All are 14 - Which of the following is NOT true about the SQL transaction controlstatements?A - The COMMIT statement ends the current transaction and makes all data changes - The ROLLBACK statement ends the transaction and discards all the pending data - Each DML statement is automatically - All are 15 - Which of the following is not true about the database objects?A - Indexes improve performance of - Views give alternative names to - Sequences are numeric value - Tables are the basic unit of 16 - Which of the following is true about the CREATE TABLE statement?A - This is a DML - This statement also record information in the data - You don t need any privilege to use this - All of the 17 - Which of the following is not true about the ALTER TABLE statement?

5 A - It can add a new - It can add a new - It can modify existing - It can define a default value for the new 18 - Consider the following schema LOCATIONS(subject_code, department_name, location_id, city);Which code snippet will alter the table LOCATIONS and add a column named Address,with datatype VARCHAR2100?A - ALTER TABLE locations ADD addressvarchar2(100);B - ALTER TABLE locations ADD COLUMN addressvarchar2(100);C - MODIFY TABLE locations ADD COLUMN addressvarchar2(100);D - None of the 19 - Consider the following schema LOCATIONS(subject_code, department_name, location_id, city);Which code snippet will alter the table LOCATIONS and change the datatype of thecolumn CITY to varchar230?A - ALTER TABLE locations MODIFY COLUMN cityvarchar2(30);B - MODIFY TABLE locations ADD cityvarchar2(30);C - ALTER TABLE locations MODIFY cityvarchar2(30);D - None of the 20 - Consider the following schema LOCATIONS(subject_code, department_name, location_id, city);Which code snippet will alter the table LOCATIONS and delete the column named CITY?

6 A - MODIFY TABLE locations DROP cityvarchar2(30);B - ALTER TABLE locations DROP COLUMN city;C - ALTER TABLE locations DROP city;D - None of the 21 - Which of the following code will successfully delete the table LOCATIONS fromthe database?A - DROP TABLE locations;B - DELETE TABLE locations;C - TRUNCATE TABLE locations;D - None of the 22 - Which of the following is true about deleting a table from the database?A - All the data in the table are deletedB - The table structure is removedC - The indexes in the table are deletedD - All of the 23 - Which of the following code will remove all the rows from the tableLOCATIONS?A - DROP TABLE locations;B - DELETE TABLE locations;C - TRUNCATE TABLE locations;D - None of the 24 - Which of the following is not true about constraints?A - A NOT NULL constraint specifies that the column cannot have a null - A UNIQUE constraint specifies that a column or a combination of column must have uniquevalues for all - A PRIMARY KEY is same as - A FOREIGN KEY enforces a foreign key relationship between a column and a referenced 25 - Which of the following is not true about creating constraints?

7 A - Constraints are defined using the CREATE CONSTRAINT - They are created at the same time when the table is - They could be created after the table is - All the constraints are stored in data 26 - Which of the following is not true about a FOREIGN KEY constraint?A - It is a referential integrity - It establishes a relationship between a primary key or a unique key in the same table or adifferent - A foreign key value cannot be - A foreign key value must match an existing value in the parent 27 - Which of the following is not true about use of a database view?A - It restricts data - It makes queries - It provides data - It prevents different views of same 28 - Which of the following is not true about simple views?A - They derive data from one - They contain no functions or - You cannot perform DML operations through a simple - All of the above are 29 - Which of the following is not true about complex views?

8 A - They derive data from more than one - They contain no functions or - You cannot perform DML operations through a complex - All of the above are 30 - Consider the following schema STUDENTS(student_code, first_name, last_name, email, phone_no, date_of_birth, honours_subject, percentage_of_marks);Which of the following code will create a simple view named all_marks_english thatcontains the names and percentage of marks of the students in the honours_subject Eng01 ?A - create view all_marks_english as select first_name, last_name, percentage_of_marks fromstudents where honours_subject = Eng01 ;B - create view all_marks_english as firstname,lastname,percentageofmarksfrom studentswherehonourssubject= Eng01 ;C - select view all_marks_english as select first_name, last_name, percentage_of_marks fromstudents where honours_subject = Eng01 ;D - None of the 31 - Which of the following code will retrieve data from the view all_marks_english,created in the previous question?

9 A - select view all_marks_english;B - select from all_marks_english;C - retrieve from all_marks_english;D - select * from all_marks_english;Q 32 - Which of the following code will delete a view named all_marks_english?A - delete view all_marks_english;B - drop view all_marks_english;C - delete all_marks_english;D - drop all_marks_english;Q 33 - Which of the following is not true about a sequence?A - They are used for generating sequential - Sequences are created by the CREATE SEQUENCE - You cannot modify a - None of the 34 - Which of the following minimal code would create a sequence named loc_seq asa primary key of the LOCATIONS table?A - create sequence loc_seq;B - create sequence loc_seq on locations;C - create loc_seq;D - None of the 35 - Which of the following statement is used to modify a sequence?A - ALTER SEQUENCEB - SELECT SEQUENCEC - DROP SEQUENCED - None of the 36 - Which of the following is not true about the Pseudocolumns that return thesequence values?

10 A - NEXTVAL returns the next available sequence - CURRVAL gets the current sequence - PREVVAL gets the previous sequence - None of the 37 - Which of the following code will delete a sequence named loc_seq?A - delete sequence loc-seq;B - drop sequence loc_seq;C - delete primary key loc_sec;D - drop primary key loc_sec;Q 38 - Which of the following is true about database indexes?A - You can create an index on one or more - Indexes always slow down the speed of query - Indexes are always created - Indexes should ideally be created on small 39 - Which of the following code will create an index named stu_marks_ind on thecolumns student_code and percentage_of_marks of the STUDENTS table?A - It s not possible to create an index on two - create index stu_marks_ind from studentsstudentcode,percentageofmarks;C - create index stu_marks_ind on studentsstudentcode,percentageofmarks;D - create index stu_marks_ind studentcode,percentageofmarks on students;Q 40 - Which of the following code will create an index namedstu_marks_ind on the columns student_code and percentage_of_marks of theSTUDENTS which will delete the index of the following code will delete an index stu_marks_ind on the columnsstudent_code and percentage_of_marks of the STUDENTS table?