Q: Question 2Select all records where dept no of both emp and dept table matches.

SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno;

Question 1

Question 1Assertion (A): COUNT() function ignores distinct values.Reasoning (R): Distinct clause ignores duplicate values.Both A and R are true and R is the correct explanation of A.Both A and R are true but R is not the correct explanation of A.A is true but R is false.A is false but R is true.

Accepted Answer

A is false but R is true.ExplanationThe COUNT() function in SQL does not ignore distinct values, it counts all occurrences, including distinct values. On the other hand, the DISTINCT keyword in SQL eliminates duplicate values, ensuring that only distinct values are considered.

Question 2

Question 2Assertion (A): The HAVING clause is used with GROUP BY clause and aggregate functions.Reasoning (R): WHERE clause specifies the condition on individual rows or records.Both A and R are true and R is the correct explanation of A.Both A and R are true but R is not the correct explanation of A.A is true but R is false.A is false but R is true.

Accepted Answer

Both A and R are true but R is not the correct explanation of A.ExplanationThe HAVING clause is used in combination with the GROUP BY clause. It allows aggregate functions to be used along with the specified condition. This is necessary because aggregate functions cannot be used with the WHERE clause, which is evaluated on a single row, whereas aggregate functions are evaluated on a group of rows specified by the GROUP BY clause.

Question 3

Question 3Assertion (A): SQL queries are performed on a table created under a database.Reasoning (R): The database is a folder created by the user in MySQL workbench. Thereafter, the tables are created in a database where various queries can be performed using SQL commands.Both A and R are true and R is the correct explanation of A.Both A and R are true but R is not the correct explanation of A.A is true but R is false.A is false but R is true.

Accepted Answer

Both A and R are true and R is the correct explanation of A.ExplanationThe database is a folder created by the user in MySQL workbench. Within this database, tables are created, and SQL commands are used to perform various queries on these tables, such as altering tables, inserting data, and retrieving data.

Question 4

Question 4Assertion (A): Aggregate functions in SQL operate on multiple set of values and return a single value as the output.Reasoning (R): The aggregate functions are used to perform some fundamental arithmetic operations such as MAX(), MIN(), SUM() etc.Both A and R are true and R is the correct explanation of A.Both A and R are true but R is not the correct explanation of A.A is true but R is false.A is false but R is true.

Accepted Answer

Both A and R are true and R is the correct explanation of A.ExplanationAggregate functions (multiple-row functions) in SQL operate on multiple sets of values and return a single value as the output. Some common aggregate functions are SUM(), MAX(), MIN(), etc., which are used to perform fundamental arithmetic operations on groups of values.

Question 5

Question 5Assertion (A): A Join is a query that combines rows from two or more tables.Reasoning (R): Equi-joins are joins based on equality conditions.Both A and R are true and R is the correct explanation of A.Both A and R are true but R is not the correct explanation of A.A is true but R is false.A is false but R is true.

Accepted Answer

Both A and R are true but R is not the correct explanation of A.ExplanationAn SQL JOIN clause is used to combine rows from two or more tables based on a common field between them. An Equi join is a simple SQL join condition that uses the equal sign (=) as a comparison operator to define a relationship between two tables based on a common field.

Question 6

Question 6Assertion (A): The INTERSECT operator returns all rows that are in both result sets.Reasoning (R): The UNION and INTERSECT yields same output.Both A and R are true and R is the correct explanation of A.Both A and R are true but R is not the correct explanation of A.A is true but R is false.A is false but R is true.

Accepted Answer

A is true but R is false.ExplanationThe INTERSECT operator returns all rows that appear in both interconnected tables, while the UNION operator returns all rows from interconnected tables, eliminating duplicate rows. Therefore, the UNION and INTERSECT operators do not yield the same output.

Question 7

Question 7Assertion (A): GROUP BY clause is always preceded by ORDER BY clause in a SELECT statement.Reasoning (R): ORDER BY clause always follows other clauses.Both A and R are true and R is the correct explanation of A.Both A and R are true but R is not the correct explanation of A.A is true but R is false.A is false but R is true.

Accepted Answer

Both A and R are true and R is the correct explanation of A.ExplanationIn SQL syntax, the GROUP BY clause comes before the ORDER BY clause. The standard order of clauses is SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY. On the other hand, it is true that the ORDER BY clause always follows other clauses, as it comes after the other clauses in the statement.

Question 8

Question 8Assertion (A): All aggregate functions except count(*) ignore null values in their input collection.Reasoning (R): SUM, AVG, MIN, and MAX can only be used with numeric columns.Both A and R are true and R is the correct explanation of A.Both A and R are true but R is not the correct explanation of A.A is true but R is false.A is false but R is true.

Accepted Answer

A is true but R is false.ExplanationIn SQL, aggregate functions like SUM, AVG, MIN, and MAX ignore NULL values in their calculations. The function COUNT(*), however, counts all rows along with NULL values in the table. The SUM and AVG functions can take arguments of only integer type, and can be used with numeric columns. While MAX and MIN functions can be used with various data types, including numeric, string, and datetime columns.

Question 9

Question 1ABC Associates has over 2000 employees on its roll and deals with customer support services. Help the company to perform associated operations for calculating the salaries (stored in Sal column) of their employees and to perform relevant analysis on the fetched data from the SQL database/table.How can we retrieve records where salary is between 1000 and 2000?

Accepted Answer

SELECT * FROM emp 
WHERE Sal BETWEEN 1000 AND 2000;ORSELECT * FROM emp 
WHERE Sal >= 1000 AND Sal <= 2000;

Question 10

Question 2Select all records where dept no of both emp and dept table matches.

Accepted Answer

SELECT * 
FROM emp e, dept d 
WHERE e.deptno = d.deptno;

Question 11

Question 3If there are two tables emp and emp1, and both have common records, how can we fetch all the records but common records only once?

Accepted Answer

SELECT * FROM emp 
UNION 
SELECT * FROM emp1;

Question 12

Question 4How can we fetch only common records from two tables emp and emp1?

Accepted Answer

SELECT * FROM emp 
INTERSECT 
SELECT * FROM emp1;

Question 13

Question 5How can we retrieve all records of emp that are not present in emp1?

Accepted Answer

SELECT * FROM emp 
LEFT JOIN
SELECT * FROM emp1;

Question 14

Question 6Count the total salary deptno wise where more than 2 employees exist.

Accepted Answer

SELECT deptno, SUM(sal) AS TOTALSAL
FROM emp
GROUP BY deptno
HAVING COUNT(empno) > 2;

Question 15

Question 7Suppose there is annual salary information provided by emp table. How can we fetch monthly salary of each and every employee?

Accepted Answer

SELECT ename, Sal / 12 AS MonthlySalary 
FROM emp;

Question 16

Question 8Select all records from emp table where deptno = 10 or 40.

Accepted Answer

SELECT * FROM emp 
WHERE deptno = 10 OR deptno = 40;

Question 17

Question 9Select all records from emp table where deptno = 30 and sal > 1500.

Accepted Answer

SELECT * FROM emp 
WHERE deptno = 30 AND Sal > 1500;

Question 18

Question 10Count number of managers (MGR column) and their salary in emp table.

Accepted Answer

SELECT COUNT(MGR), COUNT(Sal) 
FROM emp;

Question 19

Question 11How can we display all the employees grouped together on the basis of deptno and sal in descending order?

Accepted Answer

SELECT ename, deptno, sal 
FROM emp
GROUP BY deptno 
ORDER BY Sal DESC;

Question 20

Question 1The Group/Aggregate function works with data of multiple rows at a time and returns aggregated value.

Accepted Answer

Please refer to the full solution above.

Database Query using SQL

Assertions And Reasons

Casesource Based Questions

Fill In The Blanks

Multiple Choice Questions

Solutions To Unsolved Questions

State True Or False