MCQ Question for Class 12 Computer Science Chapter 4 Structured Query Language (SQL)

Check the below NCERT MCQ Class 12 Computer Science Chapter 4 Structured Query Language (SQL) with Answers available with PDF free download. MCQ Questions for Class 12 Computer Science with Answers were prepared based on the latest syllabus and examination pattern issued by CBSE, NCERT and KVS. Our teachers have provided below Structured Query Language (SQL) Class 12 Computer Science MCQs Questions with answers which will help students to revise and get more marks in exams

Structured Query Language (SQL) Class 12 Computer Science MCQ Questions with Answers

Refer below for MCQ Class 12 Computer Science Chapter 4 Structured Query Language (SQL) with solutions. Solve questions and compare with the answers provided below

Question. The ……… keyword sorts the records in ascending order by default.
(a) LIKE
(b) UPDATE
(c) ORDER
(d) ORDER BY 

Question. What is the meaning of “GROUP BY” clause in MySQL?
(a) Group data by column values
(b) Group data by row values
(c) Group data by column and row values
(d) None of the mentioned

Question. The ………….. character displays all the columns of a table in a SELECT query.
(a) #
(b) @
(c) *
(d) /

Question. The AVG() function in MySQL is an example of
(a) Math function
(b) Text function
(c) Date function
(d) Aggregate function

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

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

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 these

Question. The clause used to check NULL values is
(a) IS NULL
(b) IS NOT NULL
(c) Both (a) and (b)
(d) None of the above

Question. What is true about the following SQL statement?
mysql> SELECT*FROM Student;
(a) Displays contents of table ‘Student’.
(b) Displays column names and contents of table ‘Student’.
(c) Results in error as improper case has been used.
(d) Displays only the column names of table ‘Student’.

Question. Which of the following is a correct syntax to add a column in SQL command?
(a) ALTER TABLE table_name ADD column_name data_type;
(b) ALTER TABLE ADD column_name data_type;
(c) ALTER table_name ADD column_name data_type;
(d) None of the above

Question. The ………… command removes a table completely.
(a) DELETE
(b) REMOVE
(c) DROP
(d) UPDATE

Question. Which clause is used with an “aggregate functions”?
(a) GROUP BY
(b) SELECT
(c) WHERE
(d) Both (a) and (c)

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

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

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

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 above

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

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

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

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)

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

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

Question. Which of the following is the correct order of a SQL statement?
(a) SELECT, GROUP By, WHERE, HAVING
(b) SELECT, WHERE, GROUP BY, HAVING
(c) SELECT, HAVING, WHERE, GROUP BY
(d) SELECT, WHERE, HAVING, GROUP BY

Question. Which of the following command is used to remove the table definition and all data?
(a) CREATE
(b) SELECT
(c) DROP
(d) None of these

Question. To delete all the records from a table “Product” the command will be
(a) DEL FROM Product;
(b) DELETE FROM Product;
(c) REMOVE ALL FROM Product;
(d) DELETE ALL;

Question. Which of the following is not a comparison query?
(A) IN
(B) BETWEEN
(C) LIKE
(D) AND

Question. Which of the following is/are DML commands?
(A) Insert
(B) Delete
(C) Update
(D) All of these

Question. Which of the following is not an aggregate function?
(A) AVG
(B) MAX
(C) JOIN
(D) COUNT

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

Question. Which command is used to change some values in existing rows?
(A) UPDATE
(B) INSERT
(C) ALTER
(D) ORDER

Question. Which function is used with ORDER BY clause to custom sort order.
(A) ASC
(B) DESC
(C) FIELD
(D) None of these

Question. Which of the following function gives the total number of rows in a given column or expression?
(A) SUM()
(B) COUNT()
(C) TOTAL()
(D) Both (a) and (b)

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)

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’

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 these

Question. Which SQL function returns the sum of values of a column of numeric type?
(A) TOTAL ( )
(B) ADD ( )
(C) SUM ( )
(D) All of these

Question. Consider the following statement:
SELECT * FROM product ORDER BY rate ________, item_name ______.
Which of the following option should be used to display the ‘rate’ from greater to smaller and ‘name’ in alphabetical order?
(A) ASC, DESC
(B) DESC, ASC
(C) Descending, Ascending
(D) Ascending, Descending

Question. The __________ clause places condition with aggregate functions.
(A) HAVING
(B) WHERE
(C) IN
(D) BETWEEN

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_’

Question. Which of the following aggregate functions ignore NULL values?
(A) COUNT
(B) MAX
(C) AVERAGE
(D) All of these

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.

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

Question. Assertion (A): The UNIQUE keyword ensures no duplicate values in table.
Reason (R): DISTINCT is similar to UNIQUE

Question. Assertion (A): The HAVING clause is used with aggregate functions.
Reason (R): WHERE clause places condition on individual rows.

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

Question. Assertion (A): LIKE is a logical operator used with WHERE clause
Reason (R): Wildcard characters are used with LIKE operator

Question. Assertion (A): SQL SELECT provides clauses for summarizing results.
Reason (R): The GROUP BY clause allows to create summarized results.

Question. Assertion (A): COUNT function ignores DISTINCT.
Reason (R): DISTINCT ignores the duplicate values.

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

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.

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;

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

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

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

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 above

Basis on above table information, answer the following questions.

Question. Which SQL statement lets you find the total number of stores in the SALES table?
(a) SELECT COUNT(Store_ID) FROM Sales;
(b) SELECT COUNT(DISTINCT Store_ID) FROM Sales;
(c) SELECT DISTINCT Store_ID FROM Sales;
(d) SELECT COUNT(Store_ID) FROM Sales GROUP BY Store_ID;

Question. Which SQL statement allows you to find the highest price from the table Book_Information?
(a) SELECT Book_ID,Book_Title,MAX(Price) FROM Book_Information;
(b) SELECT MAX(Price) FROM Book_Information;
(c) SELECT MAXIMUM(Price) FROM Book_Information;
(d) SELECT Price FROM Book_Information ORDER BY Price DESC;

Question. Which SQL statement lets you to list all store name whose total sales amount is over 5000 ?
(a) SELECT Store_ID, SUM(Sales_Amount) FROM Sales GROUP BY Store_ID HAVING SUM(Sales_Amount) > 5000;
(b) SELECT Store_ID, SUM(Sales_Amount) FROM Sales GROUP BY Store_ID HAVING Sales_Amount > 5000;
(c) SELECT Store_ID, SUM(Sales_Amount) FROM Sales WHERE SUM(Sales_Amount) > 5000 GROUP BY Store_ID;
(d) SELECT Store_ID, SUM(Sales_Amount) FROM Sales WHERE Sales_Amount > 5000 GROUP BY Store_ID;

Question. Which SQL statement allows you to find the total sales amount for Store_ID 25 and the total sales amount for Store_ID 45?
(a) SELECT Store_ID, SUM(Sales_Amount) FROM Sales WHERE Store_ID IN ( 25, 45) GROUP BY Store_ID;
(b) SELECT Store_ID, SUM(Sales_Amount) FROM Sales GROUP BY Store_ID HAVING Store_ID IN ( 25, 45);
(c) SELECT Store_ID, SUM(Sales_Amount) FROM Sales WHERE Store_ID IN (25,45);
(d) SELECT Store_ID, SUM(Sales_Amount) FROM Sales WHERE Store_ID = 25 AND Store_ID =45 GROUP BY Store_ID;

Question. Which SQL statement allows you to find sales amount for each store?
(a) SELECT Store_ID, SUM(Sales_Amount) FROM Sales;
(b) SELECT Store_ID, SUM(Sales_Amount) FROM Sales ORDER BY Store_ID;
(c) SELECT Store_ID, SUM(Sales_Amount) FROM Sales GROUP BY Store_ID;
(d) SELECT Store_ID, SUM(Sales_Amount) FROM Sales HAVING UNIQUE Store_ID;

Case-based MCQs

Attempt any 4 sub-parts from each question. Each subpart carries 1 mark.
I. 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 these

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

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 these

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

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

II. 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 these

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 these

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

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

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

III. In a Database, there are two tables with the instances given below:

Question. Identify the statement to delete a column phone from the table students.
(A) ALTER TABLE STUDENTS DROP PHONE;
(B) DROP PHONE;
(C) UPDATE DROP PHONE;
(D) DELETE FROM STUDENTS WHERE DROP PHONE;

Question. Choose the command to display name and game of those students whose address is available in students’ table.
(A) SELECT NAME, GAME FROM STUDENTS, SPORTS WHERE STUDENTS. ADMNO = SPORTS.ADMNO AND ADDRESS IS NOT NULL;
(B) SELECT NAME, GAME FROM STUDENTS, SPORTS WHERE STUDENTS. ADMNO = SPORTS.ADMNO AND ADDRESS IS NULL;
(C) SELECT NAME, GAME FROM STUDNETS, SPORTS WHERE STUDENTS. ADMNO = SPORTS.ADMNO, ADDRESS IS NULL;
(D) SELECT NAME, GAME FROM STUDENTS, SPORTS WHERE STUDENTS. ADMNO = SPORTS.ADMNO NOT ADDRESS IS NULL;

Question. Choose the command to display Name of the students who are studying in class 12 and their corresponding Coach names
(A) SELECT NAME, COACHNAME FROM STUDENTS, SPORTS WHERE CLASS LIKE “12%” AND STUDENTS.ADMNO = SPORTS. ADMNO;
(B) SELECT NAME, COACHNAME FROM STUDENTS, SPORTS WHERE CLASS LIKE “%12” AND STUDENTS.ADMNO = SPORTS.ADMNO;
(C) SELECT NAME, COACHNAME FROM STUDENTS, SPORTS WHERE CLASS LIKE “12%” AND ADMNO.STUDENTS = ADMNO. SPORTS;
(D) SELECT NAME, COACHNAME FROM STUDENTS, SPORTS WHERE CLASS LIKE = “12%” AND STUDENTS.ADMNO = SPORTS. ADMNO;

Question. Choose the command to count the number of students who play volleyball
(A) SELECT COUNT(*) FROM STUDENTS, SPORTS WHERE GAME = ”VOLLEYBALL” AND STUDENTS.ADMNO = SPORTS.ADMNO;
(B) SELECT COUNT(GAME) FROM STUDENTS, SPORTS WHERE GAME = ”VOLLEYBALL” AND STUDENTS.ADMNO = SPORTS.ADMNO;
(C) SELECT COUNT(*) FROM STUDENTS, SPORTS WHERE GAME = ”VOLLEYBALL”;
(D) SELECT COUNT(*) FROM STUDENTS, SPORTS WHERE SPORTS = ”VOLLEYBALL” AND STUDENTS. ADMNO = SPORTS.ADMNO

Question. Which two select queries will give the same output
(A) SELECT NAME, GRADE FROM STUDENTS, SPORTS WHERE ADDRESS IS NULL AND STUDENTS.ADMNO = SPORTS.ADMNO;
(B) SELECT NAME, GRADE FROM STUDENTS, SPORTS WHERE ADDRESS IS NOT NULL AND STUDENTS.ADMNO = SPORTS.ADMNO;
(C) SELECT NAME, GRADE FROM STUDENTS, SPORTS WHERE ADDRESS IS NULL OR STUDENTS. ADMNO = SPORTS.ADMNO;
(D) SELECT ST.NAME, SP.GRADE FROM STUDENTS ST, SPORTS SP WHERE ADDRESS IS NULL AND ST.ADMNO = SP.ADMNO;
(i) A AND B
(ii) B AND D
(iii) A AND D
(iv) C AND D