This command is used to select the distinct rows.
For Example: If we want to select all distinct department names from employee table, the query would be:
SELECT DISTINCT dept FROM employee;
SQL MCQ Questions and answers
Learn competitive MySQL mcq questions and answers with easy and logical explanations Page-2 section-1
MAX function is used to get the maximum value from a column. To get the maximum salary drawn by an employee, the query would be:
SELECT MAX (salary) FROM employee;
A VIEW is a virtual table, through which a selective portion of the data from one or more tables can be seen. A view do not contain data of their own. They are used to restrict access to the database or to hide data complexity. A view is stored as a SELECT statement in the database. DML operations on a view like INSERT, UPDATE, DELETE affects the data in the original table upon which the view is based.
The Syntax to create a sql view is:
CREATE VIEW view_name
AS
SELECT column_list
FROM table_name [WHERE condition];
- view_name is the name of the VIEW.
- The SELECT statement is used to define the columns and rows that you want to display in the view.
SELECT. Here's a brief explanation of each option:Option A:
DELETEThe
DELETE command is used to remove data from a database table, not to retrieve it.Option B:
INSERTThe
INSERT command is used to add new records or data into a database table, not to retrieve existing data.Option C:
SELECTThe
SELECT command is specifically designed for retrieving data from one or more tables in a database. It is used to perform queries and retrieve records that match specified criteria.Option D:
JOINThe
JOIN clause is used in conjunction with the SELECT statement to combine data from multiple tables. While it is a crucial part of querying data, it is not used by itself to retrieve data; it is used to retrieve data from multiple related tables.So, the correct SQL command to retrieve data is
Option C: SELECT. SQL has several arithmetic functions to do math on the numbers, such as summing up, taking average, retrieved from the column. They are:
AVG():- Average of the column
COUNT():- Number of records
MAX():- maximum of the column
MIN():- minimum of the column
SUM():- Sum of the column
UPDATE. Here's a brief explanation of each option:Option A:
SAVEThere is no standard SQL command called
SAVE for updating data in a database.Option B:
UPDATEThe
UPDATE command is used to modify existing records in a database table. It allows you to change the values of specific columns in one or more rows based on specified conditions.Option C:
SAVE ASSAVE AS is not a standard SQL command used for updating data. It's not typically used in SQL for data modification.Option D:
MODIFYMODIFY is not a standard SQL command for updating data. It may be used in some specific database systems, but it's not a commonly recognized SQL command for data updates.So, the correct SQL statement to update data in a database is
Option B: UPDATE. DELETE. Here's a brief explanation of each option:Option A:
COLLAPSEThere is no standard SQL command called
COLLAPSE for deleting data from a database.Option B:
REMOVEREMOVE is not a standard SQL command used for deleting data. It's not typically used in SQL for data deletion.Option C:
ALTERThe
ALTER command is used to modify the structure of a database, such as adding, modifying, or deleting columns. It is not primarily used for data deletion.Option D:
DELETEThe
DELETE command is specifically designed to remove rows or records from a database table based on specified conditions.So, the correct SQL statement to delete data from a database is
Option D: DELETE.
ORDER BY. Here's a brief explanation of each option:Option A:
SORT BYThere is no standard SQL keyword called
SORT BY for sorting result-sets.Option B:
ORDERThe
ORDER keyword, by itself, is not used to specify the sorting of a result-set. It's typically used in combination with BY as ORDER BY.Option C:
ORDER BYThe
ORDER BY clause is used to specify the sorting of the result-set based on one or more columns. You can use it to sort the result-set in ascending or descending order.Option D:
SORTSORT is not a standard SQL keyword used for sorting result-sets. The correct keyword is ORDER BY.So, the correct SQL keyword to sort the result-set is
Option C: ORDER BY. SELECT SUBSTR('123456789', INSTR('abcabcabc', 'b'), 4) FROM DUAL;
INSTR Function:- The INSTR function in SQL is used to find the starting location of a pattern in a string. The syntax for the INSTR function is as follows:
INSTR (str, pattern): Find the starting location of pattern in string str.
SUBSTR Function:- The Substring function in SQL is used to grab a portion of the stored data. The syntax for the SUBSTR function is as follows:
SUBSTR(str,pos,len): Starting with the position pos in string str select the characters upto the length len.
In the above query,
INSTR('abcabcabc', 'b') outputs 2 as the starting location of pattern
MAX, COUNT, and SUM, have the ability to ignore NULL values when processing data within their respective functions. Here's a brief explanation of each option:Option A:
MAXThe
MAX function is used to find the maximum (largest) value in a set of values. It can operate on a column and will return the maximum value, ignoring NULL values in that column.Option B:
COUNTThe
COUNT function is used to count the number of rows in a result-set or the number of non-NULL values in a specific column. It ignores NULL values when counting.Option C:
SUMThe
SUM function is used to calculate the sum of all values in a numeric column. It adds up all the non-NULL values and produces a result, ignoring NULL values.Option D:
All of the aboveThis option is correct because all of the mentioned group functions (
MAX, COUNT, and SUM) have the capability to ignore NULL values when performing their respective operations.So, the correct answer is
Option D: All of the above.