CBSE Class 12 Computer Science Structured Query Language MCQs Set 02

Question. …… provides statements for creation and deletion of the database tables, views.
(a) DDL
(b) DCL
(c) DML
(d) TCL

Answer : A

Question. Which of the following is/are advantage(s) of SQL?
(a) High speed
(b) Client/server language
(c) Easy to learn
(d) All of the options

Answer : D

Question. Which aggregate function returns the count of all rows in a specified table?
(a) SUM()
(b) DISTINCT()
(c) COUNT()
(d) None of the options

Answer : C

Question. The 'SET' clause is used along with ………….. command.
(a) DELETE
(b) DESCRIBE
(c) CREATE
(d) UPDATE

Answer : D

Question. The ………….. operator is used for pattern matching.
(a) BETWEEN
(b) LIKE
(c) IN
(d) LOOKSLIKE

Answer : B

Question. Which clause is similar to 'HAVING' clause in MySQL?
(a) SELECT
(b) WHERE
(c) FROM
(d) None of the mentioned

Answer : B

Question. ……… is a simple SQL join condition that uses equal sign as a comparison operator.
(a) Equi join
(b) Non-equi join
(c) Both (a) and (b)
(d) None of the options

Answer : A

Question. Which of the following is not an aggregate function?
(a) AVG()
(b) ADD()
(c) MAX()
(d) COUNT()

Answer : B

Question. In which function, NULL values are excluded from the result returned?
(a) SUM()
(b) MAX()
(c) MIN()
(d) All of the options

Answer : D

Question. Which of the following join selects all rows from both the tables as long as the condition satisfies?
(a) Inner Join
(b) Left Join
(c) Right Join
(d) Natural Join

Answer : A

Question. Consider the following statement:
SELECT emp_no, name FROM employee_________desig Which of the following option will be used to display the employee number and names of similar designations together?
(a) FIELD()
(b) GROUP BY
(c) ORDER BY
(d) Both b and c

Answer : B

Question. Which of the following statements is appropriate to display roll Number and names of all students that belongs to any section of class 11?
(a) SELECT roll_no, name FROM student WHERE class LIKE ‘11’
(b) SELECT roll_no, name FROM student WHERE class LIKE ‘11%’
(c) SELECT roll_no, name FROM student WHERE class LIKE ‘11_’
(d) SELECT roll_no, name FROM student WHERE class NOT LIKE ‘11’

Answer : C

Question. The MAX () function finds the
(a) Maximum number of records entered in a table
(b) Maximum number of rows allowed to be entered
(c) Maximum value of the selected column
(d) None of the options

Answer : C

Question. Which SQL function returns the sum of values of a column of numeric type?
(a) TOTAL ( )
(b) ADD ( )
(c) SUM ( )
(d) All of the options

Answer : C

Question. Which operator is used to compare a value to a specified list of values?
(a) ANY
(b) BETWEEN
(c) ALL
(d) IN

Answer : D

Question. Which of the following join gives the intersection of two tables?
(a) Outer join
(b) Inner join
(c) Equi join
(d) None of the options

Answer : B

Question. Which of the following function count all the values except NULL?
(a) COUNT(*)
(b) COUNT(column_name)
(c) COUNT(NOT NULL)
(d) COUNT(NULL)

Answer : A

Question. Which keyword can be used to return only different values in a particular column or a whole table?
(a) WHERE
(b) DISTINCT
(c) ALL
(d) BETWEEN

Answer : B

Question. Which of the following statements is appropriate to display item names and rates of the products having their names ending with ‘soap’?
(a) SELECT item_name, rate FROM product WHERE item_name LIKE ‘%soap’
(b) SELECT item_name, rate FROM product WHERE item_name LIKE ‘soap%’
(c) SELECT item_name, rate FROM product WHERE item_name LIKE ‘_soap’
(d) SELECT item_name, rate FROM product WHERE item_name LIKE ‘soap_’

Answer : A

Question. Which of the following aggregate functions ignore NULL values?
(a) COUNT
(b) MAX
(c) AVERAGE
(d) All of the options

Answer : D

Assertion and Reason Based MCQs

Directions : In the following questions, A statement of Assertion (A) is followed by a statement of
Reason (R). Mark the correct choice as.
(A) Both A and R are true and R is the correct explanation for A.
(B) Both A and R are true and R is not correct explanation for A.
(C) A is true but R is false.
(D) A is false but R is true.

Question. Assertion (A): ORDER BY clause is used to sort the records.
Reason (R): For sorting, the keywords ASC and DESC are used.

Answer : A

Question. Assertion (A): MAX and MIN are aggregate functions
Reason (R): These can work on multiple rows.

Answer : A

Question. Assertion (A): The FIELD function is used by ORDER BY clause.
Reason (R): The FIELD function consider only distinct values of argument expression.

Answer : C

Question. Assertion (A): FLOAT and DOUBLE are data types
Reason (R): Both can hold any number upto 23 digits.

Answer : C

Question. Assertion (A): The PRIMARY KEY constraint is given with column.
Reason (R): NULL values are not allowed to be entered using the NOT NULL constraint.

Answer : B

Case Based MCQs :

Ronita wants to store the data of some products in a table product as follows

She also wants to perform some operations and manipulations on the table . Help her to find the solutions of following questions.

Question. A command that displays the details of all the products will be
(a) SELECT * FROM Product;
(b) SHOW * FROM Product;
(c) DISPLAY * FROM Product;
(d) SELECT ALL details FROM Product;

Answer : A

Question. The default date format in which date has to be stored in MySQL is
(a) DD-MM-YYYY
(b) DD-YY-MM
(c) MM-YY-DD
(d) YYYY-MM-DD

Answer : D

Question. Which command she can use to add a new column to the table?
(a) INSERT
(b) UPDATE
(c) ADD COLUMN
(d) ALTER

Answer : D

Question. Suggest her a proper data type for the 'PName' column.
(a) Varchar
(b) Double
(c) Float
(d) Integer

Answer : A

Question. She is confused whether she has to use the 'COLUMN' clause with the ALTER TABLE command to add a column to the table. What should she do ?
(a) COLUMN clause is must.
(b) COLUMN clause is optional.
(c) COLUMN clause is must for adding integer columns only.
(d) None of the options

Answer : B
 

Write SQL queries for (i) to (iv) and find output for SQL query (v) which are based on the tables.

Question. To display details of all Transactions of TYPE Credit from table TRANSACTION.
(A) SELECT * FROM TRANSACTION WHERE Type = 'Credit';
(B) SELECT AMOUNT FROM TRANSACTION WHERE Type = 'Credit';
(C) SELECT Type FROM TRANSACTION WHERE Type = 'Credit';
(D) None of the options

Answer : A

Question. Identify the primary key of table TRANSACTION.
(A) TRNO
(B) CNO
(C) TYPE
(D) DOT

Answer : A

Question. To display the last date of transmission (DOT) from the table Transaction for the customer having CNO as 103.
(A) SELECT MAX(), DOT FROM TRANSACTION WHERE CNO = '103';
(B) SELECT MAX (DOT) FROM TRANSACTION WHERE CNO = '103';
(C) SELECT MIN (DOT) FROM TRANSACTION WHERE CNO = '103';
(D) None of the options

Answer : B

Question. Find output of
SELECT COUNT (*), AVG (AMOUNT)
FROM TRANSACTION WHERE DOT>='2017-06-01';

Answer : D

Question. Identify the foreign key of table TRANSACTION.
(A) TRNO
(B) CNO
(C) TYPE
(D) DOT

Answer : B

Write SQL queries for (i) to (iii) and find outputs for SQL queries (iv) & (v), which are based on the tables.

Question. To display the all dates of transaction (DOT) from the table TRANSACT for the Accounts having ANO as 103.
(A) SELECT DOT FROM TRANSACT WHERE ANO = 102;
(B) SELECT DOT FROM TRANSACT WHERE ANO = 103;
(C) SELECT DOT FROM TRANSACT WHERE ANO LIKE 103;
(D) None of the options

Answer : B

Question. To display details of all transactions of TYPE Deposit from Table TRANSACT
(A) SELECT * FROM TRANSACT WHERE TYPE = 'Deposit';
(B) SELECT TYPE FROM TRANSACT WHERE Type = 'Deposit';
(C) SELECT * FROM TRANSACT WHERE TYPE = 'withdraw';
(D) None of the options

Answer : A

Question. Identify the primary key of table TRANSACT.
(A) TNO
(B) ANO
(C) TYPE
(D) DOT

Answer : A

Question. Find the output of
SELECT COUNT (*), SUM (AMOUNT) FROM
TRANSACT WHERE DOT<='2017-06-12';(A)

Answer : C

Question. Identify the foreign key of table TRANSACT.
(A) TNO
(B) ANO
(C) TYPE
(D) DOT

Answer : B