Grouping Records, Joins in SQL

(a)

Both Assertion and Reason are true and Reason is the correct explanation of Assertion.

Explanation
The GROUP BY clause combines all those records that have identical values in a particular field or a group of fields. This clause is utilised in SELECT statements to divide the result set into groups based on the values in the specified columns. Grouping can be done by a column name, or with aggregate functions in which case the aggregate produces a value for each group.

(c)

Assertion is true but Reason is false.

Explanation
A GROUP BY query can include functions. This is true because SQL allows the use of aggregate functions like COUNT(), SUM(), etc., within a GROUP BY query to perform calculations on grouped data. However, it's important to note that not all SQL functions can be used within a GROUP BY query. Only aggregate functions or functions that operate on grouped data can be used effectively in this context.

(a)

Both Assertion and Reason are true and Reason is the correct explanation of Assertion.

Explanation
SQL SELECT queries can fetch rows from multiple tables using JOIN operations. This allows retrieving data from related tables simultaneously in a single query. A join in SQL is a query that combines rows from two or more tables based on a specified condition, such as matching values in a common column.

(a)

Both Assertion and Reason are true and Reason is the correct explanation of Assertion.

Explanation
In SQL, a join is a query that combines rows from two or more tables based on a specified condition, such as matching values in a common column. However, this matching condition is not always required; for instance, a Cartesian product is an unrestricted join where all possible combinations of rows from multiple tables are generated without any specific condition. This means that every row from one table is combined with every row from another table.

(c)

Assertion is true but Reason is false.

Explanation
The join, in which columns are compared for equality, is called Equi-join. Join queries in SQL can produce combined rows based on various conditions, not just equality comparisons. For example, join conditions can involve inequality comparisons (<, >, <=, >=), as well as other logical operators and functions.

(a)

Both Assertion and Reason are true and Reason is the correct explanation of Assertion.

Explanation
In the result produced by a join query, there may be multiple identical columns in the final result, but this is not always the case. Depending on the type of join and the columns involved, duplicate columns may or may not appear in the result set. A Natural join is a type of join in SQL where only one of the identical columns (coming from the joined tables) exists in the final result.

(d)

Assertion is false but Reason is true.

Explanation
In order to carry out the join operation, there should be a governing condition is false. This is because a Cartesian join is an example of a join operation that does not require a specific condition; it combines all rows from one table with all rows from another table without any matching condition. A join condition may be based on equality, less than, greater than, or non-equality depending on the requirements of the query.

Join

Reason — A join is a query that combines rows from two or more tables or views. In a join-query, more than one table are listed in FROM clause.

Acts like a WHERE clause but is used for groups rather than rows.

Reason — The HAVING clause places conditions on groups in contrast to WHERE clause that places conditions on individual rows.

Having, where

Reason — Aggregate functions can be used in the select list or the HAVING clause of a select statement. But they cannot be used in a WHERE clause. The reason for this is that the WHERE clause filters rows before any grouping or aggregation occurs, while HAVING clause applies conditions to groups after the data has been grouped using the GROUP BY clause.

Having

Reason — The HAVING clause applies conditions to groups after the data has been grouped using the GROUP BY clause in SQL queries.

GROUP BY, SELECT

Reason — Aggregate functions are used with the GROUP BY clause to perform calculations on groups of rows. However, they are also used in the SELECT clause to display the results of aggregate calculations.

To filter out the summary groups

Reason — The HAVING clause is used to filter out summary groups in a SELECT query that involves aggregate functions and the GROUP BY clause.

False

Reason — The HAVING clause places conditions on groups in contrast to WHERE clause that places conditions on individual rows. While WHERE conditions cannot include aggregate functions, HAVING conditions can include aggregate functions. Hence, WHERE and HAVING clauses cannot be used interchangeably in SELECT queries.

Cartesian product

Reason — In an unrestricted join or Cartesian product of two relations, all possible combinations are formed by pairing each row from the first table with every row from the second table, regardless of whether their attribute values match. This operation returns n1 * n2 rows, where n1 is the number of rows in the first table, and n2 is the number of rows in the second table.

COUNT(*)

Reason — The SQL function COUNT(*) is used to count the number of rows in an SQL query, including duplicates and rows with NULL values.

With

Reason — Aggregate functions in SQL include AVG, COUNT, MAX, MIN, and SUM.

Count(*)

Reason — All aggregate functions, except for COUNT(*), ignore null values in their input collection. COUNT(*) returns all rows, including duplicates and nulls.

AVG

Reason — Aggregate functions in SQL include AVG, COUNT, MAX, MIN, and SUM.

All of the above

Reason — All aggregate functions, except for COUNT(*), ignore null values in their input collection.

Single row

Reason — Single-row functions in SQL work with one row at a time and return a result for every row of a queried table.

Aggregate

Reason — The COUNT() function is an aggregate function in SQL.

Having

Reason — When using the SUM() function in a condition, it is used with the HAVING clause. The HAVING clause is used to apply conditions to groups of rows after the GROUP BY operation has been performed, making it suitable for conditions involving aggregate functions like SUM().

WHERE

Reason — Aggregate functions cannot be used with the WHERE clause in SQL because the WHERE clause filters rows before any grouping or aggregation occurs.

To filter out the row and column values before creating groups

Reason — The WHERE clause is used to filter rows and columns values before any grouping occurs in a query with a GROUP BY clause.

Equi-join

Reason — An equi-join is a type of join where columns from two or more tables are compared for equality using the "=" operator. In the given SQL query, the WHERE clause specifies the condition CUSTOMER.CUST_ID = ORDER.CUST_ID, which compares the CUST_ID column from the CUSTOMER table with the CUST_ID column from the ORDER table. Therefore, it is an equi-join.

Cartesian product

Reason — This query uses a comma-separated list of table names in the FROM clause without specifying any join condition. In SQL, when we list multiple tables in the FROM clause separated by commas without specifying a join condition, it results in a Cartesian product.

Cartesian product

Reason — When a join query has no join condition specified, it results in a Cartesian product. This means that every row from the first table is combined with every row from the second table.

Equijoins

Reason — An equi-join is a type of join where columns from two or more tables are compared for equality using the "=" operator.

True

Reason — The HAVING clause in SQL is used to filter groups based on specified conditions, while the WHERE clause filters individual rows. This means that the HAVING clause works with grouped data, applying conditions to groups that meet certain criteria, whereas the WHERE clause applies conditions to individual rows before any grouping occurs.

False

Reason — Data Definition Language (DDL) commands are used to define a database, including creating, altering, and dropping tables and establishing constraints.

True

Reason — The SQL keyword GROUP BY clause instructs the DBMS to combine all those rows that have identical values in a particular column or a group of columns.

False

Reason — An equi-join is a type of join where columns from two or more tables are compared for equality using the "=" operator.

True

Reason — When a join query has no join condition specified, it results in a Cartesian product. This means that every row from the first table is combined with every row from the second table.

True

Reason — When we use COUNT(field_name), it counts only the rows where the specified field (field_name) is not null. It does ignore null values for that specific field during counting.

True

Reason — The aggregate functions SUM, AVG, MIN, and MAX are designed to work with numeric columns in SQL. They expect numeric values as arguments and return numeric results.

True

Reason — The SQL keyword GROUP BY instructs the DBMS to combine all those rows that have identical values in a particular column or a group of columns.

False

Reason — The HAVING clause places conditions on groups in contrast to WHERE clause that places conditions on individual rows. While WHERE conditions cannot include aggregate functions, HAVING conditions can include aggregate functions. Hence, WHERE and HAVING clauses cannot be used interchangeably in queries.

False

Reason — The HAVING clause can contain either a simple boolean expression (i.e., an expression or condition that results into true or false) or use aggregate function in the having condition.

True

Reason — The HAVING clause in SQL works with grouped data, applying conditions to groups that meet certain criteria. Therefore, HAVING clause can only be used if the SELECT query has GROUP BY clause.

True

Reason — When using the GROUP BY clause in SQL, the select-list of the SELECT statement can only include the grouping columns (group-fields) and/or aggregate functions. MySQL would not produce any error even if we include a non-group field in the select-list. However, it will return the value from first record of the group for that non-group field.

True

Reason — When a join query has no join condition specified, it results in a Cartesian product. This means that every row from the first table is combined with every row from the second table.

False

Reason — An equi-join is a type of join where columns from two or more tables are compared for equality using the "=" operator.

False

Reason — A join in SQL is a query that combines rows from two or more tables.

The GROUP BY clause in SQL is used to combine all records that have identical values in a particular field or group of fields. This grouping results in one summary record per group if group functions, such as aggregate functions, are used with it.

Aggregate functions in SQL work with data from multiple rows at a time and return a single aggregated value. They are used to perform calculations across multiple rows and return a summary result for that group.

Examples of aggregate functions include SUM(), COUNT(), MAX(), MIN(), AVG() etc.

Aggregate functions are used with the GROUP BY and HAVING clauses to apply conditions on grouped data.

A SQL join is a query that combines rows from two or more tables. Join allows retrieving data from related tables simultaneously in a single query. Joins enable the combination of data from different tables to obtain a comprehensive view of the information.

The most common types of SQL joins are as follows :

1. Equi-Join
2. Non-Equi-Join
3. Natural join

SELECT MAX(PRICE) FROM BOOK_INFORMATION;

1. SELECT BOOK_ID, BOOK_TITLE, MAX(PRICE) FROM BOOK_INFORMATION; — This query selects the BOOK_ID, BOOK_TITLE, and maximum PRICE from the BOOK_INFORMATION table. However, the requirement is to find the highest price only.
2. SELECT MAX(PRICE) FROM BOOK_INFORMATION; — This query selects the maximum PRICE from the BOOK_INFORMATION table using the MAX() aggregate function. This option is correct because it directly retrieves the highest price among all the books listed in the BOOK_INFORMATION table, which is what the question asks for.
3. SELECT MAXIMUM(PRICE) FROM BOOK_INFORMATION; — There is no MAXIMUM() function in SQL.
4. SELECT PRICE FROM BOOK_INFORMATION ORDER BY PRICE DESC; — This query selects all prices from the BOOK_INFORMATION table and orders them in descending order using ORDER BY PRICE DESC but it doesn't directly give the highest price.

SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID;

1. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES; — This statement selects the store_ID and calculates the total sales amount using SUM() aggregate function from the SALES table. It does not group the results by store ID, so it will return a single row with the total sales amount across all stores.
2. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES ORDER BY STORE_ID; — This statement selects the store_ID and calculates the total sales amount using SUM() aggregate function from the SALES table and uses an ORDER BY clause to sort the results by store ID. However, it still doesn't group the results by store_ID.
3. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID; — This statement selects the store_ID and calculates the total sales amount using SUM() aggregate function from the SALES table and uses the GROUP BY clause to group the results by store ID. It calculates the total sales amount for each store ID separately. As a result, it calculates the total sales amount for each unique store ID separately, providing a breakdown of sales amounts for each store in the dataset.
4. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES HAVING UNIQUE STORE_ID; — This statement is incorrect because the HAVING clause is used for filtering grouped data based on a condition, not for identifying unique values. Also, "UNIQUE STORE_ID" is not a valid condition in SQL.

SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES
GROUP BY STORE_ID HAVING SUM(SALES_AMOUNT) > 5000;

1. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID HAVING SUM(SALES_AMOUNT) > 5000; — This statement selects the store ID and calculates the total sales amount for each store using the SUM() aggregate function. The GROUP BY STORE_ID clause ensures that the results are grouped by store ID. The HAVING SUM(SALES_AMOUNT) > 5000 condition then filters the grouped data, selecting only those stores whose total sales amount is over 5000.
2. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID HAVING SALES_AMOUNT > 5000; — This option is incorrect because the HAVING clause cannot directly reference SALES_AMOUNT without an aggregate function like SUM() since SUM(SALES_AMOUNT) is used in the SELECT statement.
3. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES WHERE SUM(SALES_AMOUNT) > 5000 GROUP BY STORE_ID; — This option is incorrect because aggregate functions like SUM(SALES_AMOUNT) cannot be used directly in the WHERE clause. The WHERE clause is used for filtering individual rows before grouping.
4. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES WHERE SALES_AMOUNT > 5000 GROUP BY STORE_ID; — This option is incorrect because it tries to filter individual sales amounts (SALES_AMOUNT) directly without using the SUM() aggregate function to calculate the total sales amount for each store. The GROUP BY STORE_ID clause is used for grouping after the filtering, which is not the correct approach for filtering based on the total sales amount.

SELECT COUNT(DISTINCT STORE_ID) FROM SALES;

1. SELECT COUNT(STORE_ID) FROM SALES; — This query uses the COUNT() aggregate function with the STORE_ID column in the SELECT statement. It counts the number of non-null values in the STORE_ID column, and this count includes duplicates.
2. SELECT COUNT(DISTINCT STORE_ID) FROM SALES; — This option uses COUNT(DISTINCT STORE_ID) to count the number of unique store IDs in the SALES table. The DISTINCT keyword ensures that only distinct (unique) values are counted, avoiding overcounting due to duplicates.
3. SELECT DISTINCT STORE_ID FROM SALES; — This option selects distinct (unique) store IDs from the SALES table but does not count or provide the total number of stores.
4. SELECT COUNT(STORE_ID) FROM SALES GROUP BY STORE_ID; — This option attempts to count the number of occurrences of each store ID by using COUNT(STORE_ID) and grouping by store ID with GROUP BY STORE_ID. However, this results in a count for each unique store ID separately, not the total number of stores in the table.

SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES   
WHERE STORE_ID IN (25, 45) GROUP BY STORE_ID;

1. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES WHERE STORE_ID IN (25, 45) GROUP BY STORE_ID; — This query uses the IN operator to filter rows where the STORE_ID is either 25 or 45. It then calculates the total sales amount for each store ID using SUM(SALES_AMOUNT) and groups the results by STORE_ID. This query correctly finds the total sales amount for Store ID 25 and Store ID 45 separately.
2. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID HAVING STORE_ID IN (25, 45); — This query will also give the required output but it is inefficient because it first retrieves all rows from the "SALES" table, then groups the results by store ID, and finally filters the result set to include only store IDs 25 and 45. The inefficiency arises from the fact that it processes all rows in the "SALES" table before filtering out the unnecessary data. This means that it processes more data than necessary, which can be wasteful in terms of computational resources and time. A more efficient approach would be to select only the rows having store IDs 25 and 45 first (using WHERE clause), and then perform the aggregation.
3. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES WHERE STORE_ID IN (25, 45); — This query filters rows where the STORE_ID is either 25 or 45 and calculates the total sales amount for these store IDs using SUM(SALES_AMOUNT). However, it doesn't include a GROUP BY clause, so it would return a single row with the total sales amount for both Store ID 25 and Store ID 45 combined.
4. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES WHERE STORE_ID = 25 AND STORE_ID = 45 GROUP BY STORE_ID; — This query filter rows where the STORE_ID is both 25 and 45 simultaneously using STORE_ID = 25 AND STORE_ID = 45. However, this condition is impossible to satisfy because a single value cannot be both 25 and 45 at the same time. Therefore, this query would not return any results.

SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY EXAM_ID HAVING EXAM_ID = 1;

+-----------------+
| AVG(EXAM_SCORE) |
+-----------------+
|         83.2500 |
+-----------------+

1. SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS; — This statement calculates the average exam score across all exam IDs in the EXAM_RESULTS table.
2. SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY EXAM_ID WHERE EXAM_ID = 1; — This statement is incorrect because the WHERE clause should come before the GROUP BY clause. Additionally, grouping by EXAM_ID and then trying to filter by EXAM_ID = 1 within the GROUP BY clause will result in an error because grouping should be done before filtering.
3. SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY EXAM_ID HAVING EXAM_ID = 1; — This query groups the exam results by EXAM_ID and then calculates the average exam score for each group. The HAVING clause filters the groups and returns only those where the EXAM_ID is equal to 1, giving us the average exam score for the exam with EXAM_ID equal to 1.
4. SELECT COUNT(EXAM_SCORE) FROM EXAM_RESULTS WHERE EXAM_ID = 1; — This statement calculates the count of exam scores for EXAM_ID = 1, but it doesn't calculate the average score.

SELECT EXAM_ID, COUNT(DISTINCT STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID;

+---------+------------------------+
| EXAM_ID | COUNT(DISTINCT STU_ID) |
+---------+------------------------+
|       1 |                      4 |
|       2 |                      5 |
+---------+------------------------+

1. SELECT COUNT(DISTINCT STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID; — It groups the EXAM_RESULTS table by EXAM_ID and uses the COUNT(DISTINCT STU_ID) function to count the number of distinct student IDs for each exam. However, the result set does not include EXAM_ID.
2. SELECT EXAM_ID, MAX(STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID; — This query groups the results by EXAM_ID and then selects the maximum STU_ID for each exam. However, this doesn't provide the count of students who took each exam, as it gives the maximum student ID instead of counting the distinct student IDs.
3. SELECT EXAM_ID, COUNT(DISTINCT STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID; — It groups the EXAM_RESULTS table by EXAM_ID and uses the COUNT(DISTINCT STU_ID) function to count the number of distinct student IDs for each exam. The result set includes the EXAM_ID and the count of students who took each exam.
4. SELECT EXAM_ID, MIN(STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID; — This query groups the results by EXAM_ID and selects the minimum STU_ID for each exam. It does not provide information about the number of students who took each exam.

SELECT * FROM EXAM_RESULTS WHERE LNAME LIKE 'L%' ;

+--------+-------+-------+---------+------------+
| stu_id | fname | lname | exam_id | exam_score |
+--------+-------+-------+---------+------------+
|     10 | LAURA | LYNCH |       1 |         90 |
|     10 | LAURA | LYNCH |       2 |         85 |
+--------+-------+-------+---------+------------+

1. SELECT * FROM EXAM_RESULTS WHERE LNAME LIKE 'L%'; — The LIKE operator is used for pattern matching in SQL. '%' is a wildcard character that matches zero or more characters. 'L%' specifies that the last name (LNAME) should start with 'L' followed by zero or more characters. The SELECT * statement retrieves all columns from the EXAM_RESULTS table for the matching records.
2. SELECT * FROM EXAM_RESULTS WHERE LNAME LIKE 'L'; — This query attempts to select all columns (*) from the EXAM_RESULTS table where the last name (LNAME) is exactly equal to 'L'. However, when using the LIKE operator in SQL for pattern matching, we use wildcard characters (%) to represent unknown parts of a string.
3. SELECT * FROM EXAM_RESULTS WHERE LNAME 'L'; — This statement contains a syntax error. In SQL, when using the WHERE clause to filter records based on a specific condition, we need to use comparison operators or functions to define the condition properly.
4. SELECT * FROM EXAM_RESULTS WHERE LNAME <> 'L'; — This query retrieves records where the last name is not equal to 'L'. It does not specifically look for last names starting with 'L', so it's not the correct option for the given requirement.

+-----------------+
| MAX(EXAM_SCORE) |
+-----------------+
|              95 |
+-----------------+

The above SQL query calculates the maximum exam score for EXAM_ID 1 from the EXAM_RESULTS table, by grouping results based on EXAM_ID and filtering using HAVING.

1.

+------------------------+
| COUNT(DISTINCT SPORTS) |
+------------------------+
|                      4 |
+------------------------+

The SQL query SELECT COUNT(DISTINCT SPORTS) FROM Club ; calculates the count of unique values in the 'SPORTS' column of the 'Club' table. This query helps us to get information about the number of sports offered by the club.

2.

+----------+
| MIN(Age) |
+----------+
|       34 |
+----------+

The SQL query SELECT MIN(Age) FROM CLUB WHERE Sex = 'F' ; retrieves the minimum Age from the 'CLUB' table where the 'Sex' column has the value 'F'. This query gives us the age of the youngest female coach in the club.

3.

+-----------+
| AVG(Pay)  |
+-----------+
| 1100.0000 |
+-----------+

The SQL query SELECT AVG(Pay) FROM CLUB WHERE Sports = 'KARATE' ; calculates the average value of the 'Pay' column from the 'CLUB' table where the 'Sports' column has the value 'KARATE'. This query helps us to get information about the average pay of karate coaches in the club.

4.

+----------+
| SUM(Pay) |
+----------+
|     7800 |
+----------+

The SQL query SELECT SUM(Pay) FROM CLUB WHERE Dateofapp > '1998-01-31'; calculates the sum of the 'Pay' column from the 'CLUB' table where the 'Dateofapp' column has a date value greater than '1998-01-31'. This query gives us the total pay of all the coaches who joined after 31/01/1998.

1.

SELECT EMPLOYEE.EMPLOYEEID, EMPLOYEE.NAME, EMPLOYEE.JOBID, JOB.JOBTITLE
FROM EMPLOYEE, JOB
WHERE EMPLOYEE.JOBID = JOB.JOBID;

+------------+-------------------+-------+--------------------------+
| EMPLOYEEID | NAME              | JOBID | JOBTITLE                 |
+------------+-------------------+-------+--------------------------+
| E1         | SUMIT SINHA       |   102 | VICE PRESIDENT           |
| E2         | VIJAY SINGH TOMAR |   101 | PRESIDENT                |
| E3         | AJAY RAJPAL       |   103 | ADMINISTARTION ASSISTANT |
| E4         | MOHIT RAMNANI     |   102 | VICE PRESIDENT           |
| E5         | SHAILJA SINGH     |   103 | ADMINISTARTION ASSISTANT |
+------------+-------------------+-------+--------------------------+

2.

SELECT EMPLOYEE.NAME, EMPLOYEE.SALES, JOB.JOBTITLE
FROM EMPLOYEE, JOB 
WHERE EMPLOYEE.JOBID = JOB.JOBID
AND EMPLOYEE.SALES > 1300000;

+---------------+---------+--------------------------+
| NAME          | SALES   | JOBTITLE                 |
+---------------+---------+--------------------------+
| AJAY RAJPAL   | 1400000 | ADMINISTARTION ASSISTANT |
| SHAILJA SINGH | 1450000 | ADMINISTARTION ASSISTANT |
+---------------+---------+--------------------------+

3.

SELECT EMPLOYEE.NAME, JOB.JOBTITLE
FROM EMPLOYEE, JOB 
WHERE EMPLOYEE.JOBID = JOB.JOBID
AND EMPLOYEE.NAME LIKE '%SINGH%';

+-------------------+--------------------------+
| NAME              | JOBTITLE                 |
+-------------------+--------------------------+
| VIJAY SINGH TOMAR | PRESIDENT                |
| SHAILJA SINGH     | ADMINISTARTION ASSISTANT |
+-------------------+--------------------------+

4. In the given tables, EMPLOYEE and JOB, the JOBID column in the EMPLOYEE table is a foreign key referencing the JOBID column in the JOB table.

5.

UPDATE EMPLOYEE
SET JOBID = 104
WHERE EMPLOYEEID = 'E4';

SELECT * FROM EMPLOYEE ;

+------------+-------------------+---------+-------+
| EMPLOYEEID | NAME              | SALES   | JOBID |
+------------+-------------------+---------+-------+
| E1         | SUMIT AINHA       | 1100000 |   102 |
| E2         | VIJAY SINGH TOMAR | 1300000 |   101 |
| E3         | AJAY RAJPAL       | 1400000 |   103 |
| E4         | MOHIT RAMNANI     | 1250000 |   104 |
| E5         | SHAILJA SINGH     | 1450000 |   103 |
+------------+-------------------+---------+-------+

1.

SELECT Depid, COUNT(*) AS Frequency 
FROM Employee 
GROUP BY Depid;

2.

SELECT Name
FROM Employee
WHERE Name LIKE 'H%';

3.

ALTER TABLE Salary
ADD COLUMN Total_Sal FLOAT;

4.

UPDATE Salary
SET Total_Sal = Basic + `D.A.` + HRA + Bonus;

5.

SELECT MAX(Basic)
FROM Salary
WHERE Bonus > 40;

+------------+
| MAX(Basic) |
+------------+
|      10000 |
+------------+

6.

SELECT Sec as sex, COUNT(*) AS Count 
FROM Employee 
GROUP BY Sec ;

+-----+-------+
| sex | Count |
+-----+-------+
| F   |     2 |
| M   |     4 |
+-----+-------+

7.

SELECT DISTINCT Depid FROM Employee ;

+-------+
| Depid |
+-------+
|   101 |
|   102 |
|   103 |
+-------+

(a)

SELECT P.EMPNO, P.NAME, J.Salary 
FROM PERSONAL P, JOB J
WHERE P.EMPNO = J.Sno AND P.Hobby = 'Sports';

+-------+--------+--------+
| EMPNO | NAME   | Salary |
+-------+--------+--------+
|   125 | Vinod  |   8500 |
|   129 | Ramesh |   7000 |
+-------+--------+--------+

(b)

SELECT name
FROM personal
WHERE dobirth = (
    SELECT MIN(dobirth)
    FROM personal
);

+------+
| name |
+------+
| Amit |
+------+

(c)

SELECT Area, COUNT(Sno) AS Employee_Count
FROM Job
GROUP BY Area;

+-----------+----------------+
| Area      | Employee_Count |
+-----------+----------------+
| Agra      |              2 |
| Delhi     |              1 |
| Mathura   |              1 |
| Pune      |              1 |
| Bangalore |              1 |
+-----------+----------------+

(d)

SELECT Name, `Native-place`, dobirth  
FROM personal  
WHERE dobirth = (SELECT MAX(dobirth)         
FROM personal p2          
WHERE personal.`Native-place` = p2.`Native-place` ) ;

+--------+--------------+------------+
| Name   | Native-place | dobirth    |
+--------+--------------+------------+
| Abhai  | Allahabad    | 1975-08-11 |
| Vinod  | Delhi        | 1977-04-04 |
| Manoj  | Mumbai       | 1976-12-12 |
| Ramesh | Pune         | 1981-10-28 |
+--------+--------------+------------+

(e)

SELECT SNO, NAME, HOBBY, SALARY 
FROM PERSONAL, JOB 
WHERE PERSONAL.EMPNO = JOB.SNO 
ORDER BY SALARY DESC;

+-----+--------+-----------+--------+
| SNO | NAME   | HOBBY     | SALARY |
+-----+--------+-----------+--------+
| 125 | Vinod  | Sports    |   8500 |
| 128 | Abhay  | Gardening |   7500 |
| 129 | Ramesh | Sports    |   7000 |
| 127 | Manoj  | Writing   |   6000 |
| 124 | Abhai  | Music     |   5500 |
| 123 | Amit   | Music     |   5000 |
+-----+--------+-----------+--------+

(f)

SELECT HOBBY 
FROM PERSONAL 
WHERE Name = 'abhay' or Name = 'abhai' ;

+-----------+
| HOBBY     |
+-----------+
| Music     |
| Gardening |
+-----------+

(g)

SELECT App_date, nativeplace 
FROM personal, job 
WHERE personal.empno = job.sno 
AND (Name LIKE 'A%' OR Name LIKE '%d') ;

+------------+--------------+
| App_date   | native-place |
+------------+--------------+
| 2006-01-25 | Delhi        |
| 2007-08-19 | Allahabad    |
| 2004-04-14 | Delhi        |
| 2008-03-13 | Mumbai       |
+------------+--------------+

(h)

SELECT Salary AS "Salary Expense" 
FROM Job
WHERE `Retd_date` > '2006-01-20';

+----------------+
| Salary Expense |
+----------------+
|           5000 |
|           5500 |
|           8500 |
|           6000 |
|           7500 |
|           7000 |
+----------------+

(i)

SELECT SUM(Salary * 0.1) AS "Additional Burden" 
FROM PERSONAL, JOB
WHERE PERSONAL.EMPNO = JOB.SNO AND HOBBY = 'SPORTS' ;

+-------------------+
| Additional Burden |
+-------------------+
|            1550.0 |
+-------------------+

(j)

SELECT Hobby 
FROM PERSONAL 
GROUP BY Hobby 
HAVING COUNT(*) >= 2;

+--------+
| Hobby  |
+--------+
| Music  |
| Sports |
+--------+

(k)

SELECT COUNT(*)  
FROM Job
WHERE DATEDIFF(CURDATE(), App_date) >= 20 * 365;

+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

(l)

SELECT P.Name, P.Dobirth 
FROM Personal P, Job J 
WHERE P.Empno = J.Sno AND J.Retd_date > CURDATE() AND DATEDIFF(CURDATE(), J.App_date) > 17 * 365;

+-------+------------+
| Name  | Dobirth    |
+-------+------------+
| Amit  | 1965-01-23 |
| Manoj | 1976-12-12 |
+-------+------------+

(m)

SELECT Name 
FROM Personal, job
where personal.Empno = job.Sno 
and job.Salary > ( select max(salary) 
from job
where dept = 'sales');

There will be no output because there are no employees whose salary is greater than the highest salary in the Sales department.

(n)

UPDATE Job J, Personal P
SET J.Salary = (J.Salary * 0.05 ) + J.Salary
WHERE J.Sno = P.Empno
AND (P.Hobby = 'Music' 
OR DATEDIFF(CURDATE(), J.App_date) >= 3 * 365);

+-----+-----------+------------+--------+------------+-----------+
| sno | area      | app_date   | salary | retd_date  | dept      |
+-----+-----------+------------+--------+------------+-----------+
| 123 | Agra      | 2006-01-25 |   5250 | 2026-01-25 | Marketing |
| 124 | Agra      | 2007-08-19 |   5775 | 2027-08-19 | Marketing |
| 125 | Delhi     | 2004-04-14 |   8925 | 2018-04-14 | Sales     |
| 127 | Mathura   | 2006-12-22 |   6300 | 2026-12-22 | Finance   |
| 128 | Pune      | 2008-03-13 |   7875 | 2028-03-13 | Sales     |
| 129 | Bangalore | 2003-07-21 |   7350 | 2023-07-21 | Finance   |
+-----+-----------+------------+--------+------------+-----------+

(o)

1.

Select distinct hobby from personal ; 

+-----------+
| hobby     |
+-----------+
| Music     |
| Sports    |
| Writing   |
| Gardening |
+-----------+

2.

Select avg(salary) from personal, job where Personal.Empno = Job.Sno and Area in ('Agra','Delhi') ;  

+-------------+
| AVG(SALARY) |
+-------------+
|   6650.0000 |
+-------------+

3.

Select count(distinct Native_place) from personal;

+--------------------------------+
| COUNT(DISTINCT `NATIVE-PLACE`) |
+--------------------------------+
|                              4 |
+--------------------------------+

4.

Select name, max(salary) 
from Personal, Job where Personal.Empno = Job.Sno ;

+------+-------------+
| name | max(salary) |
+------+-------------+
| Amit |        8500 |
+------+-------------+

The given query retrieves the maximum salary from the 'Job' table and pairs it with the corresponding employee name from the 'Personal' table based on the matching Empno and Sno values. However, including a non-group field like 'name' in the select-list means it will return the value from the first record of the group for the 'name' field. Therefore, 'Amit' is the first record in the 'Personal' table, the query returns 'Amit' as the value for the 'name' field.

(p)

INSERT INTO Personal (Empno, Name, Dobirth, `Native-place`, Hobby)
VALUES (130, 'Amruta', '1990-05-15', 'Chennai', 'Music');

select * from personal;

+-------+--------+------------+--------------+-----------+
| empno | name   | dobirth    | native-place | hobby     |
+-------+--------+------------+--------------+-----------+
|   123 | Amit   | 1965-01-23 | Delhi        | Music     |
|   124 | Abhai  | 1975-08-11 | Allahabad    | Music     |
|   125 | Vinod  | 1977-04-04 | Delhi        | Sports    |
|   127 | Manoj  | 1976-12-12 | Mumbai       | Writing   |
|   128 | Abhay  | 1974-03-10 | Mumbai       | Gardening |
|   129 | Ramesh | 1981-10-28 | Pune         | Sports    |
|   130 | Amruta | 1990-05-15 | Chennai      | Music     |
+-------+--------+------------+--------------+-----------+

(q)

ALTER TABLE Job
ADD COLUMN Email VARCHAR(55); 

(r)

insert into empdetails(empno, name, hobby)
select empno, name, hobby
from personal ;

select * from empdetails ;

+-------+--------+-----------+
| Empno | Name   | Hobby     |
+-------+--------+-----------+
|   123 | Amit   | Music     |
|   124 | Abhai  | Music     |
|   125 | Vinod  | Sports    |
|   127 | Manoj  | Writing   |
|   128 | Abhay  | Gardening |
|   129 | Ramesh | Sports    |
|   130 | Amruta | Music     |
+-------+--------+-----------+

(s)

CREATE VIEW LessThan15YearsView AS
SELECT * FROM Personal p, Job j   
WHERE p.Empno = j.Sno AND 
DATEDIFF(CURDATE(), J.App_date) < 15 * 365;

(t)

DELETE j
FROM Job j, Personal p 
WHERE j.Sno = p.Empno AND p.Hobby <> 'Sports';

(u)

DROP TAbLE Personal;

(i)

SELECT Firstname, Lastname, Address, City
FROM Employees
WHERE City = 'Paris';

+-----------+----------+-------------+-------+
| Firstname | Lastname | Address     | City  |
+-----------+----------+-------------+-------+
| SAM       | TONES    | 33 ELM ST.  | PARIS |
| PETER     | THOMPSON | 11 RED ROAD | PARIS |
+-----------+----------+-------------+-------+

(ii)

SELECT *
FROM Employees
ORDER BY Firstname DESC;

+-------+-----------+----------+-------------------+------------+
| empid | FIRSTNAME | LASTNAME | ADDRESS           | CITY       |
+-------+-----------+----------+-------------------+------------+
|   215 | SARAH     | ACKERMAN | 440 U.S. 110      | UPTON      |
|   152 | SAM       | TONES    | 33 ELM ST.        | PARIS      |
|   300 | ROBERT    | SAMUEL   | 9 FIFTH CROSS     | WASHINGTON |
|   335 | RITU      | TONDON   | SHASTRI NAGAR     | GZB        |
|    10 | RAVI      | KUMAR    | RAJ NAGAR         | GZB        |
|   400 | RACHEL    | LEE      | 121 HARRISON ST.  | NEW YORK   |
|   441 | PETER     | THOMPSON | 11 RED ROAD       | PARIS      |
|   244 | MANILA    | SENGUPTA | 24 FRIENDS STREET | NEW DELHI  |
|   105 | HARRY     | WALTOR   | GANDHI NAGAR      | GZB        |
+-------+-----------+----------+-------------------+------------+

(iii)

SELECT e.Firstname, e.Lastname, 
(s.Salary + s.Benefits) AS TotalSalary
FROM Employees e, EmpSalary s  
WHERE e.Empid = s.Empid AND s.Designation = 'Manager';

+-----------+----------+-------------+
| Firstname | Lastname | TotalSalary |
+-----------+----------+-------------+
| RAVI      | KUMAR    |       90000 |
| HARRY     | WALTOR   |       80000 |
| SARAH     | ACKERMAN |       87500 |
+-----------+----------+-------------+

(iv)

SELECT Designation, MAX(Salary) 
FROM EmpSalary 
WHERE Designation IN ('Manager', 'Clerk') 
group by designation;

+-------------+-------------+
| Designation | MAX(Salary) |
+-------------+-------------+
| MANAGER     |       75000 |
| CLERK       |       50000 |
+-------------+-------------+

(i)

+-----------+--------+
| FIRSTNAME | SALARY |
+-----------+--------+
| RACHEL    |  32000 |
| PETER     |  28000 |
+-----------+--------+

(ii)

+-----------------------------+
| COUNT(DISTINCT DESIGNATION) |
+-----------------------------+
|                           4 |
+-----------------------------+

(iii)

+-------------+-------------+
| DESIGNATION | SUM(SALARY) |
+-------------+-------------+
| MANAGER     |      215000 |
| CLERK       |      135000 |
+-------------+-------------+

(iv)

+---------------+
| SUM(BENEFITS) |
+---------------+
|         32000 |
+---------------+

SELECT job, AVG(Sal) AS AvgSalary 
FROM Empl 
GROUP BY job HAVING COUNT(*) > 3;

+----------+-----------+
| job      | AvgSalary |
+----------+-----------+
| CLERK    |    1037.5 |
| SALESMAN |      1400 |
+----------+-----------+

SELECT Job 
FROM Empl 
GROUP BY Job HAVING MAX(Sal) >= 3000;

+-----------+
| Job       |
+-----------+
| PRESIDENT |
| ANALYST   |
+-----------+

SELECT COUNT(*) AS NumManagers
FROM EmpSalary
WHERE Designation = 'Manager';

+-------------+
| NumManagers |
+-------------+
|           3 |
+-------------+

SELECT deptno, COUNT(*) AS employee_count
FROM EMPL
GROUP BY deptno;

+--------+----------------+
| deptno | employee_count |
+--------+----------------+
|     20 |              5 |
|     30 |              6 |
|     10 |              3 |
+--------+----------------+

SELECT deptno, SUM(sal) AS total_salary
FROM EMPL
GROUP BY deptno;

+--------+--------------+
| deptno | total_salary |
+--------+--------------+
|     20 |        10885 |
|     30 |         9400 |
|     10 |         8750 |
+--------+--------------+

SELECT deptno, MAX(sal) AS max_salary
FROM EMPL
GROUP BY deptno;

+--------+------------+
| deptno | max_salary |
+--------+------------+
|     20 |       3000 |
|     30 |       2850 |
|     10 |       5000 |
+--------+------------+