CBSE Class 12 Computer Science Structured Query Language MCQs Set A

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

Answer : B

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 the options

Answer : C

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;

Answer : B

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 options

Answer : A

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

Answer : B

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 options

Answer : A

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

Answer : C

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

Answer : A

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

Answer : D

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

Answer : D

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

Answer : A

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

Answer : C

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

Answer : B

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

Answer : A

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

Answer : C

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

Answer : D

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

Answer : B

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

Answer : A

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

Answer : D

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

Answer : C

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): LIKE is a logical operator used with WHERE clause
Reason (R): Wildcard characters are used with LIKE operator

Answer : A

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

Answer : A

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

Answer : D

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

Answer : A

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

Answer : B

Case Based MCQs :

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;

Answer : D

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;

Answer : B

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;

Answer : A

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;

Answer : B

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;

Answer : C
 

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;

Answer : A

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;

Answer : A

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;

Answer : A

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

Answer : A

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

Answer : C