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

  1. Truncate
  2. ROUND
  3. MOD
  4. POW

Answer (Detailed Solution Below)

Option 3 : MOD

Basic SQL Queries Question 1 Detailed Solution

The correct answer is MOD.

key-point-image 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.

additional-information-image 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);

  1. PRA
  2. PRAYAG
  3. RAJ
  4. PRAYAGRAJ

Answer (Detailed Solution Below)

Option 3 : RAJ

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;

  1. 75000
  2. 25000
  3. 10000
  4. 50000

Answer (Detailed Solution Below)

Option 4 : 50000

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?

  1. COUNT(*) counts all rows, while COUNT(column_name) counts only non-null values in the specified column
  2. COUNT(*) counts only distinct values, while COUNT(column_name) counts all duplicate values.
  3. COUNT(*) is faster than COUNT(column_name).
  4. There is no difference; they both do the same thing.

Answer (Detailed Solution Below)

Option 1 : COUNT(*) counts all rows, while COUNT(column_name) counts only non-null values in the specified column

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.

  1. (A), (B), (C), (D)
  2. (A), (B), (D), (C)
  3. (B), (A), (D), (C) 
  4. (C), (B), (D), (A)

Answer (Detailed Solution Below)

Option 3 : (B), (A), (D), (C) 

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.

  1. /
  2. _ _
  3. *
  4. !

Answer (Detailed Solution Below)

Option 3 : *

Basic SQL Queries Question 6 Detailed Solution

Download Solution PDF

The * 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;

ID NAME AGE
1 Akash 24
2 sema 23
3 diya 33
4 Badal 29

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?

  1. Drop, Update, Delete
  2. Insert, Alter, Delete
  3. Insert, Update, Create
  4. Insert, Update, Delete

Answer (Detailed Solution Below)

Option 4 : Insert, Update, Delete

Basic SQL Queries Question 7 Detailed Solution

Download Solution PDF

SQL: 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');

  1. The customer_name and telephone of all customers. 
  2. The customer_name and telephone of all customers Living in Jaipur, Delhi and Agra. 
  3. The customer_name and telephone of all customers Living in either Jaipur Delhi or Agra.  
  4. The customer_name and telephone of all customers who are not living in Jaipur, Delhi or Agra. 

Answer (Detailed Solution Below)

Option 3 : The customer_name and telephone of all customers Living in either Jaipur Delhi or Agra.  

Basic SQL Queries Question 8 Detailed Solution

Download Solution PDF

Concept:

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:

  1. MODIFY
  2. UPDATE
  3. INSERT
  4. ALTER

Answer (Detailed Solution Below)

Option 2 : UPDATE

Basic SQL Queries Question 9 Detailed Solution

Download Solution PDF

Concepts

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?

  1. Project
  2. Cartesian product
  3. Union
  4. Set intersection

Answer (Detailed Solution Below)

Option 4 : Set intersection

Basic SQL Queries Question 10 Detailed Solution

Download Solution PDF

The 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:

σ (R)

Projection (Π) or project:

It displays the columns of a relation or table based on the specified attributes.

Syntax:

π (R)

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

  1. P and R
  2. P and S
  3. Q and R
  4. Q and S

Answer (Detailed Solution Below)

Option 3 : Q and R

Basic SQL Queries Question 11 Detailed Solution

Download Solution PDF

The 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:

SELECT column1, column2   
FROM table_name  
WHERE conditions   
GROUP BY column1, column2   
HAVING conditions  
ORDER BY column1, column2;  
 
Example:
SELECT COMPANY, COUNT(*)  
FROM PRODUCT_MAST   
GROUP BY COMPANY  
HAVING COUNT(*)>2; 
Therefore an SQL query can contain a HAVING clause only if it has a GROUP BY clause.
Confusion Points
The answer is as per standard SQL

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:

  1. (A) and (B) only
  2. (A) and (C) only
  3. (B) and (C) only
  4. (A) only

Answer (Detailed Solution Below)

Option 1 : (A) and (B) only

Basic SQL Queries Question 12 Detailed Solution

Download Solution PDF

The 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?

  1. CREATE USER ­ to create a new user name and password
  2. GRANT ­ to provide system level database access permission to users
  3. PERMIT ­ to provide object level database access permission to users
  4. REVOKE ­ to cancel the permission to access database

Answer (Detailed Solution Below)

Option 3 : PERMIT ­ to provide object level database access permission to users

Basic SQL Queries Question 13 Detailed Solution

Download Solution PDF

The 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 ________.

  1. empty a table completely
  2. delete the selected rows only
  3. delete the selected columns only
  4. add one table with another

Answer (Detailed Solution Below)

Option 1 : empty a table completely

Basic SQL Queries Question 14 Detailed Solution

Download Solution PDF

The 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?

  1. REN
  2. RENAME TEMP
  3. AS
  4. AS TEMP

Answer (Detailed Solution Below)

Option 3 : AS

Basic SQL Queries Question 15 Detailed Solution

Download Solution PDF

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

SELECT column_name AS alias_name
FROM table_name
WHERE [condition];
Hence the correct answer is AS.
Get Free Access Now
Hot Links: teen patti all games teen patti party teen patti lucky teen patti master downloadable content