Basic SQL Queries MCQ Quiz - Objective Question with Answer for Basic SQL Queries - Download Free PDF
Last updated on Apr 20, 2025
Latest Basic SQL Queries MCQ Objective Questions
Basic SQL Queries Question 1:
Which SQL function will return the following output:
mysql > Select ____?_____ (53, 10);
output __ 3
Answer (Detailed Solution Below)
Basic SQL Queries Question 1 Detailed Solution
The correct answer is MOD.
Key Points
- The SQL MOD function is used to find the remainder of a division operation between two numbers.
- In the given example,
MOD(53, 10)
, 53 is divided by 10. - The quotient of this division is 5 (since 53 divided by 10 equals 5 with a remainder).
- The remainder of this division is 3, hence the output of
MOD(53, 10)
is 3.
- In the given example,
Additional Information
- The MOD function is supported by many SQL databases, including MySQL, PostgreSQL, Oracle, and SQL Server.
- The syntax of the MOD function can vary slightly between different SQL databases.
- In some databases, the MOD function might be called REMAINDER.
- Understanding the MOD function is essential for mathematical calculations and data analysis in SQL.
Basic SQL Queries Question 2:
What is the output of the following SQL query?
mysql > SELECT RIGHT("PRAYGRAJ", 3);
Answer (Detailed Solution Below)
Basic SQL Queries Question 2 Detailed Solution
The correct answer is RAJ.
Key Points
- The SQL query used is: SELECT RIGHT("PRAYAGRAJ", 3);
- The RIGHT function in SQL is used to extract a specified number of characters from the right side of a string.
- In this example, the function is asked to return the last 3 characters of the string "PRAYAGRAJ".
- The last 3 characters of "PRAYAGRAJ" are "RAJ".
- Therefore, the output of the SQL query is RAJ.
Additional Information
- The RIGHT function is often used in scenarios where you need to extract a specific portion of a string from the end.
- Other related functions include LEFT (which extracts characters from the left side) and SUBSTRING (which extracts a part of the string based on specified positions).
- Understanding string functions is crucial for data manipulation and querying in SQL.
- Proper usage of string functions can optimize the retrieval of data and enhance the efficiency of SQL queries.
Basic SQL Queries Question 3:
If column "Payment" contains the data set (10000, 15000, 25000, 10000, 15000), what will be the output after the execution of the given query?
SELECT SUM(DISTINCT PAYMENT) FROM EMPLOYEE;
Answer (Detailed Solution Below)
Basic SQL Queries Question 3 Detailed Solution
The correct answer is Option 4.
Key Points
- The query
SELECT SUM(DISTINCT PAYMENT) FROM EMPLOYEE;
calculates the sum of all unique values in the "Payment" column of the "EMPLOYEE" table.- The "Payment" column contains the following data set: (10000, 15000, 25000, 10000, 15000).
- The DISTINCT keyword ensures that only unique values are considered for the sum.
- The unique values in the "Payment" column are: 10000, 15000, and 25000.
- The sum of these unique values is: 10000 + 15000 + 25000 = 50000.
Additional Information
- The DISTINCT keyword is used to remove duplicate values from a result set.
- Using SUM with DISTINCT helps to ensure that the sum is calculated only for unique values.
- This functionality is particularly useful when dealing with data that may have duplicate entries, but only the unique entries are relevant for the calculation.
- In SQL, aggregate functions like SUM, COUNT, AVG, MIN, and MAX can be used with the DISTINCT keyword.
Basic SQL Queries Question 4:
What is the difference between COUNT(*) and COUNT(column_name) in a SQL query?
Answer (Detailed Solution Below)
Basic SQL Queries Question 4 Detailed Solution
The correct answer is COUNT(*) counts all rows, while COUNT(column_name) counts only non-null values in the specified column.
Key Points
- COUNT(*) is used to count the total number of rows in a table. This includes all rows, regardless of whether any columns contain NULL values.
- COUNT(column_name) counts only the rows where the specified column contains non-null values. Rows with NULL values in the specified column are not counted.
- This distinction is useful when you want to count the presence of specific data in a column while ignoring NULL entries.
- For example, if you have a table of students with columns for student ID and student name, COUNT(student_name) will count only those rows where the student name is not NULL, whereas COUNT(*) will count all rows regardless of whether the student name is NULL or not.
- Using COUNT(*) is helpful when you need a total row count to understand the table's size or the number of records it holds.
Additional Information
- Using COUNT(*) is generally faster and more efficient because it does not need to check for NULL values in a specific column.
- On the other hand, COUNT(column_name) can be more informative when dealing with sparse data where many rows may have NULL values in the column of interest.
- Both functions are part of the SQL standard and are widely supported by all relational database management systems (RDBMS).
- Understanding the difference between these two counting methods can help optimize queries and make them more meaningful depending on the context.
Basic SQL Queries Question 5:
Amit wants to be familiar with SQL. One of his friends Anand suggest him to execute following sql commands.
(A) Create Table Student
(B) Use Database DB
(C) Select * from Student
(D) Insert into Student
In which order Amit needs to run above commands.
Answer (Detailed Solution Below)
Basic SQL Queries Question 5 Detailed Solution
The correct answer is Option 3.
Key Points
- To become familiar with SQL, Amit needs to execute the commands in a specific order to ensure they are performed correctly and without errors.
- The command (B) Use Database DB is executed first to select the database in which the operations will be performed.
- The command (A) Create Table Student is executed next to create the table structure within the selected database.
- The command (D) Insert into Student is executed third to populate the table with data.
- The command (C) Select * from Student is executed last to retrieve and display the data from the table.
Additional Information
- Executing the commands in the correct order ensures the database and table are properly set up before attempting to insert or retrieve data.
- Using the
USE
statement ensures that subsequent operations are performed on the correct database. - Creating the table first is essential to define the structure before inserting data.
- Inserting data is necessary before any data retrieval operations can be performed.
Top Basic SQL Queries MCQ Objective Questions
_______ symbol is used to see every column of a table.
Answer (Detailed Solution Below)
Basic SQL Queries Question 6 Detailed Solution
Download Solution PDFThe * symbol is used to see every column of a table.
For Example, Let us consider a table Table1
ID | NAME | AGE | ADDRESS | SALARY |
1 | Akash | 24 | Bijnor | 12000 |
2 | sema | 23 | Delhi | 15000 |
3 | diya | 33 | Banglore | 33000 |
4 | Badal | 29 | Odisha | 40000 |
If you want to fetch only some specific columns from the table, then we can use this query,
Select ID,NAME,AGE from Table1; / Syntax is Select (column_name1, coloumn_name2.....coloum_name) from table_name;
|
If you want to fetch all the fields of the Table1 table, then you should use the following query ,
Select * from Table1; / Syntax is Select * from table_name;
ID | NAME | AGE | ADDRESS | SALARY |
1 | Akash | 24 | Bijnor | 12000 |
2 | sema | 23 | Delhi | 15000 |
3 | diya | 33 | Banglore | 33000 |
4 | Badal | 29 | Odisha | 40000 |
Therefore Option 3 is correct
Which of the following represents the three basic SQL DML commands?
Answer (Detailed Solution Below)
Basic SQL Queries Question 7 Detailed Solution
Download Solution PDFSQL: Structured Query Language, commonly abbreviated to SQL is a language used in Relational Databases. This mainly focuses on creating a database and executing queries against a database.
SQL comprises both data definition and data manipulation language (DML).
Important Points
DML: Data Manipulation Language (DML) is used to modify the instance of the database by inserting, updating, and deleting its data.
- Insert into/ values:- It is used to insert data into a table.
- Update/set/were:- It is used to update existing data within a table.
- Delete from / where:- It is used to delete records from a database table.
Additional Information
Data Definition Language (DDL):- It is used to design and modify the database schema.
- Create: It is used to create new databases, tables, and views from RDBMS.
- Drop: It is used to drop databases, tables, and views.
- Alter: It is used to modify database schema from RDBMS.
What result set is returned from the following SQL query?
A. Select customer_name, telephone
From customers
Where city in ('Jaipur', Delhi', 'Agra');
Answer (Detailed Solution Below)
Basic SQL Queries Question 8 Detailed Solution
Download Solution PDFConcept:
The SQL IN condition allows us to easily test if an expression matches any value in a list of values. It reduces the need for multiple OR conditions in statements.
Explanation:
Given SQL query :
Select customer_name, telephone
From customers
Where city IN ('Jaipur', Delhi', 'Agra') ;
As the operator used is IN so, it will result in the customer name and telephone of all customers living in the city either Jaipur, Delhi or Agra.
SQL command used to modify attribute values of one or more selected type is:
Answer (Detailed Solution Below)
Basic SQL Queries Question 9 Detailed Solution
Download Solution PDFConcepts
SQL command used to modify attribute values of one or more selected types is UPDATE.
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. The ALTER TABLE statement is also used to add and drop various constraints on an existing table.
Syntax:
The UPDATE statement is used to modify the existing records in a table.
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Therefore Option 2 is correct
Additional Information
ALTER TABLE Modify Column command. For Oracle and MySQL, the SQL syntax for ALTER TABLE Modify Column is,
ALTER TABLE "table_name" MODIFY "column_name" "New Data Type";
The INSERT INTO statement is used to insert new records in a table.
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
Which among the following is not a fundamental operation in relational model?
Answer (Detailed Solution Below)
Basic SQL Queries Question 10 Detailed Solution
Download Solution PDFThe correct answer is option 4.
Concept:
A relational algebra and calculus is a procedural language in which each query describes a step-by-step procedure for computing the desired result. i.e relation algebra expression represents query evaluation plan.
It has different operations like,
Selection (σ) or select:
Selection is defined as taking the horizontal subset of rows of a single table that satisfies a particular condition.
Syntax:
σ
Projection (Π) or project:
It displays the columns of a relation or table based on the specified attributes.
Syntax:
π
Union Operation (∪):
It performs binary union between two given relations.
Syntax:
r U s
Set Difference (−):
The result of the set difference operation is tuples, which are present in one relation but are not in the second relation.
Syntax:
r − s
Cartesian Product (Χ):
Combines information of two different relations into one.
Syntax:
r Χ s
Rename Operation (ρ):
The rename operation allows us to rename the output relation. 'rename' operation.
Syntax
ρ x (E)
Where the result of expression E is saved with the name of x.
Additional operations are Set intersection (∩), join (⋈), and Division (÷ ).
SQL Set manipulation operators:
Set intersection, set union, and Except operations are set manipulation operators. Which compare the result of any query with a multiset of rows then it is natural to perform the set operations like,
- Union
- Intersect
- Except.
Example:
(select name from depositor)
union / Intersect / Except
(select name from borrower).
Hence the correct answer is to Set intersection
Which of the following statements are TRUE about an SQL query?
P: An SQL query can contain a HAVING clause even if it does not have a GROUP BY clause
Q: An SQL query can contain a HAVING clause only if it has a GROUP BY clause
R: All attributes used in the GROUP BY clause must appear in the SELECT clause
S: Not all attributes used in the GROUP BY clause need to appear in the SELECT clause
Answer (Detailed Solution Below)
Basic SQL Queries Question 11 Detailed Solution
Download Solution PDFThe correct answer is option 3
EXPLANATION:
GROUP BY clause:
- The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".
- The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.
Syntax:
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
Example:
SELECT COMPANY, COUNT(*)
FROM PRODUCT_MAST
GROUP BY COMPANY
Therefore all attributes used in the GROUP BY clause must appear in the SELECT clause
HAVING clause:
- HAVING clause is used to specify a search condition for a group or an aggregate.
- Having is used in a GROUP BY clause.
- If you are not using GROUP BY clause then you can use HAVING function like a WHERE clause.
Syntax:
Comprehension:
Comprehension: Consider the following table structures related to a university for given questions.
EMPLOYEE
NAME |
VARCHAR (30) |
NOT NULL, |
EID |
VARCHAR (10) |
NOT NULL, |
DEPTNO |
INT (5) |
NOT NULL, |
HODEID |
VARCHAR (10), |
|
SALARY |
INT (10), |
|
PRIMARY KEY (EID),
FOREIGN KEY (HODEID) REFRENCES EMPLOYEE (EID),
FOREIGN KEY (DEPTNO) REFRENCES DEPARTMENT (DID);
DEPARTMENT
DID |
INT (5) |
NOT NULL, |
DNAME |
VARCHAR (30) |
NOT NULL, |
HODID |
VARCHAR (10) |
NOT NULL, |
HODNAME |
VARCHAR (30), |
|
PRIMARY KEY (DID),
UNIQUE (DNAME),
FOREIGN KEY (HODID) REFERENCES EMPLOYEE (EID),
PROJECT WORE:
EMPID |
VARCHAR (10) |
NOT NULL, |
PROJNO |
INT (5) |
NOT NULL, |
PROJECTLOC |
VARCHAR (30) |
NOT NULL, |
PRIMARY KEY (EMPID, PROJNQ),
FOREIGN KEY (EMPID) REFERENCES EMPLOYEE (EID),
In reference to the above given table structures, which of the following query/queries will drop the ‘SALARY’ column from 'EMPLOYEE' table ?
(A) ALTER TABLE EMPLOYEE DROP SALARY CASCADE;
(B) ALTER TABLE EMPLOYEE DROP SALARY RESTRICT;
(C) ALTER EMPLOYEE DROP SALARY:
Choose the correct answer from the options given below:
Answer (Detailed Solution Below)
Basic SQL Queries Question 12 Detailed Solution
Download Solution PDFThe correct answer is option 1.
Key Points
Alter table Syntax:
ALTER TABLE table-Name
DROP [ COLUMN ] column-name [ CASCADE | RESTRICT ]
- The keyword COLUMN, CASCADE, RESTRICT are optional. The default is CASCADE. If you use the RESTRICT option, the column drop will be rejected if it would invalidate a dependent schema object. If CASCADE is defined, the column drop should also remove any other schema objects that have become invalid.
- Since the salary field is not present in any other table, CASCADE | RESTRICT has no impact.
-
(C) ALTER EMPLOYEE DROP SALARY is incorrect syntax.
∴ Hence the correct answer is (A) and (B) only.
Which of the following command is incorrectly matched?
Answer (Detailed Solution Below)
Basic SQL Queries Question 13 Detailed Solution
Download Solution PDFThe correct answer is option 3.
Concept:
Option 1: CREATE USER to create a new user name and password.
True, The CREATE USER statement creates and configures a database user, which is an account that allows you to log in to the database, as well as the means by which Oracle Database allows the user access.
Option 2: GRANT to provide system-level database access permission to users.
True, The SQL Grant command is being used to grant user privileges to database objects. Users can also use this command to grant permissions to other users.
Option 3: PERMIT to provide object-level database access permission to users
False, Use the PERMIT command to keep track of the users and groups who have permission to access a specific resource and it is used to provide system-level database access permission to users like the user IDs and group names authorized to access the resource, as well as the level of access granted to each, are included in the standard access list.
Option 4:REVOKE to cancel the permission to access the database.
True, If any user privileges on database objects have been granted, the Revoke command will remove them. It performs activities in the opposite direction as the Grant command. When a user's privileges are withdrawn, the privileges that user U gave to all other users are also revoked.
Hence the correct answer is PERMIT to provide object-level database access permission to users.
Truncating tables shall ________.
Answer (Detailed Solution Below)
Basic SQL Queries Question 14 Detailed Solution
Download Solution PDFThe correct answer is option 1.
Concept:
TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. To remove the table definition in addition to its data, use the DROP TABLE statement.
SQL Truncate:
The TRUNCATE TABLE command deletes the data inside a table, but not the table itself.
Syntax:
TRUNCATE TABLE table_name;
SQL Drop:
The DROP TABLE command deletes a table in the database.
Syntax:
DROP TABLE table_name;
So truncating tables shall empty a table completely but drop table deletes a table in the database.
Hence the correct answer is empty a table completely.
A clause in SQL allows you to rename a table or a column temporarily by giving another name known as alias. The renaming is a temporary change and the actual table name does not change in the database. Which of the following clauses, allows such option?
Answer (Detailed Solution Below)
Basic SQL Queries Question 15 Detailed Solution
Download Solution PDFThe correct answer is option 3.
Concept:
In SQL, we can temporarily rename a table or a column by giving it another name known as Alias. Table aliases are used to rename a table in a specific SQL statement. The renaming is only a temporary change, and the actual table name in the database remains unchanged.
Syntax: The following is the basic syntax for a table alias.
SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];
Syntax: The following is the basic syntax for a column alias.