CBSE Class 12 Informatics Practices Querying and SQL Functions MCQs Set A

Question. The COUNT function in SQL returns the number of _____
a) Values
b) Distinct values
c) Group By
d) Columns

Answer : A

Question. What is the purpose of MIN function in SQL Server?
a) It returns the minimum value in the expression
b) It is use for decrementing the integer value
c) MIN is not a SQL server function
d) None of the above

Answer : A

Question. What will be returned by the given query? SELECT ROUND(153.669,2);
a) 153.6
b) 153.66
c) 153.67
d) 153.7

Answer : C

Question. ___ is not a category of SQL command.
a) TCL
b) SCL
c) DCL
d) DDL

Answer : B

Question. _____removes all rows from a table without logging the individual row deletions
a) DELET
b) REMOVE
c) DROP
d) TRUNCATE

Answer : D

Question. If emp_id contain the following set {9, 7, 6, 4, 3, 1, 2}, what will be the output on execution of the following MySQL statement? SELECT emp_id FROM person ORDER BY emp_id;
a) {1, 2, 3, 4, 6, 7, 9}
b) {2, 1, 4, 3, 7, 9, 6}
c) {9, 7, 6, 4, 3, 1, 2}
d) None of the mentioned

Answer : A

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. Which statement is not true?
a) SQL is rational whereas No-SQL is non-rational.
b) SQL follows BASE Model whereas No-SQL follows ACID Model.
c) SQL database are vertically scalable whereas No-SQL database are horizontally scalable.
d) No-SQL databases are preferable to store hierarchical data in comparison SQL databases.

Answer : B

Question. Syntax of the Structured Query Language is ____?
a) Case-sensitive
b) Not case-sensitive
c) Both (a) and (b)
d) None of the above

Answer : B

Question. SQL became the standard of?
a) ASCII
b) ANSI
c) ISO
d) Both B and C

Answer : D

Question. Which of the following is not a valid SQL statement?
a) SELECT MIN(pub_date) FROM books GROUP BY category HAVING pub_id = 4;
b) SELECT MIN(pub_date) FROM books WHERE category = ‘COOKING’;
c) SELECT COUNT(*) FROM orders WHERE customer# = 1005;
d) SELECT MAX(COUNT(customer#)) FROM orders GROUP BY customer#;

Answer : A

Question. What is meant by ‘SQL is an interactive language’?
a) Learning and understanding SQL is easy
b) It can also be used for communicating with the database.
c) In a few seconds, complex queries can also be answered using this language.
d) All of the above

Answer : D

Question. What does the DROP TABLE Statement do?
a) The table, its structure, views, permissions, and triggers will also be deleted or removed with this SQL statement.
b) A new table in SQL is created using this SQL statement.
c) Columns in the SQL database can be created, deleted, or modified with this SQL statement.
d) A new database will be created through this SQL statement.

Answer : A

Question. What does the DROP INDEX Statement do?
a) By running this SQL statement, the transaction will be undone and the operations not yet saved to the SQL database will be undone.
b) The SQL database table’s index is deleted using this SQL statement.
c) An index is created in a SQL database table with this SQL statement.
d) An existing SQL database is selected with this SQL statement. A database must be selected from several existing databases before you can perform operations on the table.

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. Which type of SQL function work with a single-row at a time?
a) Multiple-row functions
b) Single-row functions
c) Both (a) and (b)
d) None of the above

Answer : B

Question. Output returned by the Scalar functions is/are –
a) Single Value
b) Multiple Value
c) Two Values
d) None of the above

Answer : A

Question. Which function is used to extract substrings from the column of the table –
a) ROUND()
b) MID()
c) FORMAT()
d) NOW()

Answer : B

Question. BETWEEN operator is used with which SQL Statements –
a) SELECT
b) DELETE
c) INSERT
d) All of the above

Answer : D

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. A Sub query is an SQL expression that is placed ________ another SQL statement.
a) Before
b) After
c) Inside
d) Outside

Answer : C

Question. What does the DESCRIBE Statement do?
a) In this SQL statement, the data or records are inserted into an existing database table. One query statement can insert multiple records simultaneously using this statement.
b) By executing this SQL statement, all records in the SQL database will be deleted.
c) Specify the columns of the table in this SQL statement to return distinct values.
d) The data specified in this table or view is reported in this SQL statement.

Answer : D

Question. Determine the correct SQL command?
a) CREATE
b) UPDATE
c) DELETE
d) All of the above

Answer : D

Question. What does the SELECT Statement do?
a) Data is read from the SQL database by this statement and displayed to the database user.
b) The stored data in the SQL database is changed or modified by this SQL statement.
c) By deleting the stored data, this SQL statement deletes the database.
d) A new table in SQL is created using this SQL statement.

Answer : A

Question. Write the output of the following SQL command. SELECT ROUND (47.89);
a) 47.88
b) 47.8
c) 48.0
d) 50

Answer : C

Question. SQL has the advantage of?
a) SQL require a lot of programming.
b) SQL provides High-Speed Query Processing.
c) SQL follows the standard languages of ANSI and ISO.
d) SQL is easily portable.

Answer : A

Question. Which of the following clause cannot be used in SQL sub queries?
a) GROUP BY
b) ORDER BY
c) DELETE
d) FROM

Answer : B

Question. Using the DELETE Query from the CRUD Operator, we can delete –
a) Only one row
b) All the rows
c) Only two rows
d) None of the above

Answer : B

Question: Which operator perform pattern matching ?
a) BETWEN
b) LIKE
c) IN
d) NOT

Answer : B

Question: Which operator tests a column for the absence of data(i.e. NULL value) ?
a) Exist Operator
b) NOT Operator
c) IS Operator
d) None of these

Answer : C

Question: Consider the following query
SELECT name FROM class WHERE Subject LIKE ‘___ Informatics Practices’;
Which one of the following has to be added into the blank space to select the subject which has informatics practices as its ending string?
a) $
b) _
c) ||(
d) %

Answer : D

Question: Consider the following table namely Employee :
Which of the names will not be displayed by the below given query ?
SELECT name from Employee WHERE employee_id>1009;
a) Misha, Khushi
b) Khushi, Japneet
c) Japneet
d)Misha, Japneet

Answer : A

Question: Which clause is used to sort the query result ?
a) Order By
b) Sort By
c) Group By
d) Arrange By

Answer : A

Question: By default ORDER BY clause list the result in ________ order.
a) Descending
b) Any
c) Same
d) Ascending

Answer : D

Question: Consider the following query
SELECT * FROM employee ORDER BY salary ________, name ______;
To display the salary from greater to smaller and name in alphabetical order which of the following options should be used ?
a) Ascending, Descending
b) Asc, Desc
c) Desc, Asc
d) Descending, Ascending

Answer : C

Question: What is the meaning of Remark LIKE “%5%5%”;
a) Column Remark begin with two 5s
b) Column Remark ends with two 5s
c) Column Remark has more than two 5s
d) Column Remark has two 5s in it, at any position

Answer : D

Question: In SQL, which command(s) is/are used to change a table’s structure/characteristics?
a) ALTER TABLE
b) MODIFY TABLE
c) CHANGE TABLE
d) All of these

Answer : A

Question: A Table can have __________
a) Many primary keys and many unique keys.
b) One primary key and one unique key
c) One primary key and many unique keys.
d) Many primary keys and one unique key.

Answer : C

Question: Which of the following is/are the DDL Statement ?
a) Create
b) Drop
c) Alter
d) All of these

Answer : D

Question: Consider the following SQL Statement. What type of statement is this ?
INSERT INTO instructor VALUES (10211, ‘SHREYA’ , ‘BIOLOGY’, 69000);
a) Procedure
b) DML
c) DCL
d) DDL

Answer : B

Question: Which of the following types of table constraints will prevent the entry of duplicate rows?
a) Unique
b) Distinct
c) Primary Key
d) Null

Answer : A

Question: Which of the following query will drop a column from a table ?
a) DELETE COLUMN column_name;
b) DROP COLUMN column_name;
c) ALTER TABLE table_name DROP COLUMN column_name;
d) None of these

Answer : C

Question. GROUP BY clause is placed before which clause in SQL?
a) HAVING
b) WHERE
c) ORDER BY
d) FROM

Answer : C

Question. Select the correct type(s) of SQL Comments.
a) Inline Comments
b) Single Line Comments
c) Multi-line Comments
d) All of the above

Answer : D

Question. In SQL, which command(s) is are used to change a table’s storage characteristics
a) ALTER TABLE
b) MODIFY TABLE
c) CHANGE TABLE
d) All of the above

Answer : A

Question. We can create View in SQL using –
a) CREATE statement
b) CREATE VIEW statement
c) VIEW CREATE statement
d) SQL VIEW statement

Answer : B

Case Study Based Questions

Rachana Mittal runs a beauty parlor. She uses a database management system(DBMS) to store the information that she needs to manage her business. This information includes customer contact details, staff names, the treatments that the parlor offer (for example, ‘’Hair Massage’) and appointment that customers have made for treatments. A separate appointment must be made for each treatment.
The details are stored in a database using the following four relations:
Customer: (CustomerID, FirstName, LastName, TelephoneNumber, EmailAddress)
Staff :(StaffID, FirstName,LastName, IsQualified)
Treatment: (TreatmentName,Price,TimeTaken,NeedsQualification)
Appointment : (CustomerID,TreatmentName,ApDate,ApTime)
• The IsQualifiedattribute for a member of staff stores one of the value True or False, to indicate if the member of staff is fully qualified or not.
• The NeedsQualifictionattribute for a treatment stores True or False to indicate if the treatment can only be given by a qualified member of staff.
• The TimeTakenattribute for a treatment is the number of minutes (a whole number) that the treatment takes.

Question: Write a SQL statement to create the table staff.
Answer : Create Table Staff
( StaffID Number(4,0) NOT NULL PRIMARY KEY,
FirstName Varchar(20) NOT NULL,
LastNameVarchar(20),
ISQualifiedChar(4) Check (IsQualified IN(‘True’, ‘False’)));

Question: Which table’s records can be deleted without affecting any other table?
(i) Customer (ii) Staff (iii) Treatment (iv) Appointment
Answer : (ii) Staff table’s records can be deleted without affecting any other table as of now, because this table is not linked with any other table yet.

Question: Write a query to Insert a record in the table Staff with following data ;
(2009, ‘Sheril’, ‘Mark’, ‘True’)
Answer : INSERT INTO Staff Values(2009, ‘Sheril’, ‘Mark’, ‘True’);

Question: Rachana wants to send e-mail advertisement to all the customers who had a ‘RF Facial’ treatmentin 2020. To send the email, the customer’s email address, firstname and lastname are needed.
Write a SQL query to retrieve the email address, firstname and lastname of each customer to whom email should be sent.
Answer : Select EmailAddress, FirstName,LastName
From Customer C, Appointment A
Where C.CustomerID=A.CustomerID
AND TreatmentName= ‘RF Facial’;

Question: Write a query to Modify table Appointment to add a new column StaffID, which should hold a legal Staff ID value from the staff table.
Answer : Alter Table Appointment Add StaffIDNumber(4,0) NOT NULL Reference Staff(StaffID);

Fill in the Blanks

Question: _______ _______ command removes a table from a database permanently.
Answer : drop table

Question: to remove table data as well table structure, use command ______ ________
Answer : drop table

Question: use _____ _______ command to add new columns in an existing table.
Answer : alter table

Question: a column added via alter table command initially contains _____ value for all rows.
Answer : null

Question: issue ______ command to make changes to table permanent.
Answer : commit

Question: the ______ _______ clause is used to divide result of select query in groups.
Answer : group by

Question: to specify condition with a group by clause, _______ clause is used.
Answer : having

Question: only _______ functions are used with group by clause.
Answer : aggregate

Question: nested grouping can be done by providing ________ ______ in the group by expression.
Answer : multiple fields

Question: the _________ clause is used in select queries to specify filtering condition for groups.
Answer : having