It Includes the latest DBMS MCQ Questions and Answer.SQL Online test questions and answers.
1.Evaluate the SQL statement:
SELECT ROUND (TRUNC (MOD (1600, 10),-1), 2) FROM dual;
What will be displayed?
d) D.an error statement
2.Evaluate the SQL statement:
TRUNCATE TABLE DEPT;
Which three are true about the SQL statement?
A. It releases the storage space used by the table.
B. It does not release the storage space used by the table.
C. You can roll back the deletion of rows after the statement executes.
D. You can NOT roll back the deletion of rows after the statement executes.
E. You must be the owner of the table or have DELETE ANY TABLE system privileges to truncate the DEPT table
F. An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement executes will display an error.
3. Which three statements are true regarding subqueries?
A. Multiple columns or expressions can be compared between the main query and subquery.
B. Main query and subquery can get data from different tables
C.Subqueries can contain GROUP BY and ORDER BY clauses
D. Main query and subquery must get data from the same tables
E.Subqueries can contain ORDER BY but not the GROUP BY clause
F. Only one column or expression can be compared between the main query and subquery
4. View the Exhibit and examine the structure of the PRODUCTS, SALES, and SALE_SUMMARY tables.
SALE_VW is a view created using the following command:
SQL>CREATE VIEW sale_vw AS SELECT prod_id, SUM(quantity_sold) QTY_SOLD FROM sales GROUP BY prod_id;
You issue the following command to add a row to the SALE_SUMMARY table:
SQL>INSERT INTO sale_summary (SELECT prod_id, prod_name, qty_sold FROM sale_vw JOIN products USING (prod_id) WHERE prod_id = 16);
What is the outcome?
a)It executes successfully.
b)It gives an error because a complex view cannot be used to add data to the SALE_SUMMARY table.
c)It gives an error because the column names in the subquery and the SALE_SUMMARY table do not match.
d)It gives an error because the number of columns to be inserted does not match with the number of columns in the SALE_SUMMARY table.
5. Which statements are true regarding the FOR UPDATE clause in a SELECT statement?
A. It locks only the columns specified in the SELECT list.
B. It locks the rows that satisfy the condition in the SELECT statement.
C. It can be used only in SELECT statements that are based on a single table.
D. It can be used in SELECT statements that are based on single or multiple tables.
E. After it is enforced by a SELECT statement, no other query can access the same rows until a COMMIT or ROLLBACK is issued.
a)a, b, c
b)b, d, e
e)a, b, c, d, e
Answer: B, D
6. View the Exhibits and examine the structures of the COSTS and PROMOTIONS tables.
Evaluate the following SQL statement:
SQL> SELECT prod_id FROM costs WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_cost < ALL (SELECT MAX(promo_cost) FROM promotions GROUP BY (promo_end_datepromo_begin_date)));
What would be the outcome of the above SQL statement?
a)It displays prod IDs in the promo with the lowest cost.
b)It displays prod IDs in the promos with the lowest cost in the same time interval.
c)It displays prod IDs in the promos with the highest cost in the same time interval.
d)It displays prod IDs in the promos with cost less than the highest cost in the same time interval.
7. Evaluate the following SQL statement:
SQL> SELECT cust_id, cust_last_name
FROM customers WHERE cust_credit_limit IN (select cust_credit_limit FROM customers WHERE cust_city =’Singapore’);
Which statement is true regarding the above query if one of the values generated by the subquery is NULL?
a)It produces an error.
b)It executes but returns no rows.
c)It generates output for NULL as well as the other values produced by the subquery.
d)It ignores the NULL value and generates output for the other values produced by the subquery.
8. Which of the following is true about the PL/SQL data structure VARRAY?
a)It also has a maximum size that cannot be changed.
b)A VARRAY type is created with the CREATE VARRAY statement, at the schema level.
c)The maximum size of a VARRAY can be changed using the ALTER TYPE statement.
d)The maximum size of a VARRAY can be changed using the ALTER VARRAY statement.
9. Which of the following statements is true about implicit cursors?
a)Implicit cursors are used for SQL statements that are not named.
b)Developers should use implicit cursors with great care.
c)Implicit cursors are used in cursor for loops to handle data processing.
d)Implicit cursors are no longer a feature in Oracle.
9. What is the value of customer_id within the nested block in the example below?
/* Start main block /
customer_id NUMBER(9) := 678;
credit_limit NUMBER(10,2) := 10000;
/ Start nested block */
customer_id VARCHAR2(9) := ‘AP56’;
current_balance NUMBER(10,2) := 467.87;
— what is the value of customer_id at this point?
Please select the best answer.
10. You can add a row using SQL in a database with which of the following?
11.Which of the following is the correct order of keywords for SQL SELECT statements?
a)SELECT, FROM, WHERE
b)FROM, WHERE, SELECT
c)WHERE, FROM, SELECT
d)SELECT, WHERE, FROM
12. The result of a SQL statement is a(n)_.
13. Which of the following do you need to consider when you make a table in SQL?
d)All of the above
14. The view of total database content is
15. ODBC stands for
a)Object Database Connectivity.
b)Oral Database Connectivity.
c)Oracle Database Connectivity.
d)Open Database Connectivity.
16. An entity set that does not have sufficient attributes to form a primary key is a
a) strong entity set.
b) weak entity set.
c) simple entity set.
d) primary entity set.
17. In the case of entity integrity, the primary key maybe
c)both Null & not Null.
18. The database environment has all of the following components except:
19. A logical schema
A. is the entire database.
B. is a standard way of organizing information into accessible parts.
C. describes how data is actually stored on disk.
D. both (A) and (C)
20. The property/properties of a database is/are:
A. It is an integrated collection of logically related records.
B. It consolidates separate files into a common pool of data records.
C. Data stored in a database is independent of the application programs using it.
D. All of the above.
21. Key to represent the relationship between tables is called
A. Primary key
B. Secondary Key
C. Foreign Key
D. None of these
22. Select invalid variable types
23. Which of the following is used to declare a record?
C. Both A & B
D. None of the above
24. In a PL/SQL block, a variable is declared as NUMBER without an initial value. What will its value be when it is first used in the executable section of the PL/SQL block?
C. Results in a compilation error
D. An exception will be raised
25. Which of the following is true about PL/SQL programs?
A.PL/SQL programs can exist with or without any SQL statements.
B.PL/SQL programs can exist only with any SQL statements.
C.PL/SQL programs can exist only without any SQL statements
D.SQL programs can exist only with PL/SQL statements
26. Which of the following is not true about keywords?
A. Reserved words and keywords are identifiers that have special meaning in PL/SQL.
B. The difference between reserved words and keywords is that reserved words cannot be used as identifiers.
C. Keywords can be used as identifiers, but it is not recommended
D. Reserved keywords can be used as ordinary user-defined identifiers.
27. Which of the following keyword is used with Data Control Language (DCL) statements?
28. A type of query that is placed within a WHERE or HAVING clause of another query is called
A. Master query
C. Super query
30. A command that lets you change one or more fields in a record is
D. All of the Mentioned
31. Which of the following is a Data Model?
A. Entity-Relationship model
B. Relational data model
C. Object-Based data model
D. All of the above
32. The column header is referred to as
33. Which data manipulation command is used to combines the records from one or more tables?
34. SQL permits attribute names to be repeated in the same relation. (True or False).
35. Which of the following operations requires the relations to be union compatible?
D. ALL OF THESE
36. Which of the following is a legal expression in SQL?
A. SELECT NULL FROM SALES;
B. SELECT NAME FROM SALES;
C. SELECT * FROM SALES WHEN PRICE = NULL;
D. SELECT # FROM SALES;
37. The COUNT function in SQL returns the number of __
B. Distinct values
C. Group By
38. The virtual table that it’s created by data from the result of an SQL ‘Select’ statement is called _
40. What is the other name of INNER JOIN?
a) Equi Join
b) In Join
c) Out Join
d) All of the above
41. List the types of Inner join?
a) Out, In, Equi
b) Left, In, Cross
c) Equi, Natural
d) None of the above
42. What is the purpose of the index in the SQL server?
a) It leads to enhance query performance.
b) It leads to provide an index to a record.
c) It leads to perform fast searches.
d) All of the above.
Read the more interesting article: click here
Read the more interesting article in python: click here