Basic SQL Queries MCQ Quiz - Objective Question with Answer for Basic SQL Queries - Download Free PDF

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.

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.

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.

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.

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.

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.

The * symbol is used to see every column of a table.

 For Example, Let us consider a table Table1

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;

Therefore Option 3 is correct

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.

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.

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, ...);

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

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

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.

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.

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.

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.