Database Query using SQL

A is false but R is true.

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

Both A and R are true but R is not the correct explanation of A.

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

Both A and R are true and R is the correct explanation of A.

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

Both A and R are true and R is the correct explanation of A.

Explanation
Aggregate 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.

Both A and R are true but R is not the correct explanation of A.

Explanation
An 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.

A is true but R is false.

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

Both A and R are true and R is the correct explanation of A.

Explanation
In 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.

A is true but R is false.

Explanation
In 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.

SELECT * FROM emp 
WHERE Sal BETWEEN 1000 AND 2000;

OR

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

Ascending

Reason — By default, ORDER BY clause sorts the result set in ascending order. Since the query does not include DESC, the data will be sorted in ascending order based on the ROLL_NO column.

Null value

Reason — The COUNT() function in SQL counts only non-null values in a column. It does not consider null values while executing the statement. Therefore, null values will not be counted by the COUNT() function in the given SQL statement.

ORDER BY

Reason — The SQL ORDER BY clause is used to sort data in ascending or descending order based on one or more columns.

HAVING

Reason — The HAVING clause is used in SQL queries to place conditions on groups when using the GROUP BY clause.

25, 35, 38

Reason — The DISTINCT keyword in the SQL is used to remove duplicate rows from the results of a SELECT statement. Therefore, the output of the query will include each distinct value from the "Marks" column, which are 25, 35, and 38.

50000

Reason — The DISTINCT keyword in the SUM() function ensures that only unique values of "Salary" are summed. Therefore, the query will sum up the distinct values of salary, which are 10000, 15000, and 25000, resulting in an output of 50000.

SELECT COUNT(*) FROM PRODUCT;

Reason — The SQL statement SELECT COUNT(*) FROM PRODUCT; is used to retrieve the total number of records or rows present in the "PRODUCT" table. The COUNT(*) function counts all rows along with the NULL values in a specified table, and the SELECT keyword is used to fetch data from the database.

SELECT * FROM STUDENTS WHERE LNAME LIKE '_ _ _ _A';

Reason — The SQL statement to display the records of all students whose last name contains 5 letters ending with "A" is SELECT * FROM STUDENTS WHERE LNAME LIKE '_ _ _ _A';. This statement uses the LIKE operator with the pattern '_ _ _ _A', where each underscore represents a single character and the letter "A" specifies the ending condition. This pattern ensures that the last name has exactly 5 letters and ends with "A".

ROUND()

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

MAX()

Reason — The MAX() function is used to find the maximum value of a particular column in a table.

Select SUM(Salary) From Employees;

Reason — The SQL statement used to find out the total salary given to employees is SELECT SUM(Salary) FROM Employees;. This statement utilizes the SUM() function to calculate the total value of the 'Salary' column from the 'Employees' table.

Having

Reason — The HAVING clause is used in SQL queries to place conditions on groups when using the GROUP BY clause.

Date()

Reason — In SQL, the DATE() function is used to extract the date part of a date or date-time expression.

Single row functions operate on a single row to return a single value per row as the output. They can accept one or more arguments but return only one result per row. They are also known as Scalar Functions.

Multiple row functions operate on a group of rows or a set of values and return a single value calculated from all the values present in a column instead of providing the listing of all of the rows. They are also known as aggregate functions.

The GROUP BY clause in SQL can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns. It groups the rows on the basis of the values present in one of the columns and then the aggregate functions are applied on any column of these groups to obtain the result of the query.

The WHERE clause in an SQL SELECT command is used to filter individual rows based on specified conditions, such as column values, before any grouping is performed. On the other hand, the HAVING clause is used for filtering groups of rows after grouping has been applied, with aggregate functions like SUM or AVG.

The Student table is as follows:

SELECT SUM(MARKS) AS Total_Sum, AVG(MARKS) AS Average_Marks, 
MIN(MARKS) AS Lowest_Marks, MAX(MARKS) AS Highest_Marks
FROM Student;

+-----------+---------------+--------------+---------------+
| Total_Sum | Average_Marks | Lowest_Marks | Highest_Marks |
+-----------+---------------+--------------+---------------+
|       742 |       82.4444 |           70 |            98 |
+-----------+---------------+--------------+---------------+

The Student table is as follows:

SELECT STREAM, SUM(MARKS) AS Total_Sum,
AVG(MARKS) AS Average_Marks, MIN(MARKS) AS Lowest_Marks,
MAX(MARKS) AS Highest_Marks
FROM Student
GROUP BY STREAM;

+------------+-----------+---------------+--------------+---------------+
| STREAM     | Total_Sum | Average_Marks | Lowest_Marks | Highest_Marks |
+------------+-----------+---------------+--------------+---------------+
| SCIENCE    |       244 |       81.3333 |           75 |            93 |
| COMMERCE   |       188 |       94.0000 |           90 |            98 |
| HUMANITIES |       158 |       79.0000 |           78 |            80 |
| VOCATIONAL |       152 |       76.0000 |           70 |            82 |
+------------+-----------+---------------+--------------+---------------+

(a) SELECT SUM(Price) FROM ITEM;

+------------+
| SUM(Price) |
+------------+
|        370 |
+------------+

(b) SELECT AVG(Price) FROM ITEM;

+------------+
| AVG(Price) |
+------------+
|    74.0000 |
+------------+

(c) SELECT MIN(Price) FROM ITEM;

+------------+
| MIN(Price) |
+------------+
|         20 |
+------------+

(d) SELECT MAX(Price) FROM ITEM;

+------------+
| MAX(Price) |
+------------+
|        150 |
+------------+

(e) SELECT COUNT(Price) FROM ITEM;

+--------------+
| COUNT(Price) |
+--------------+
|            5 |
+--------------+

(f) SELECT DISTINCT Price FROM ITEM;

+-------+
| Price |
+-------+
|    50 |
|   100 |
|   150 |
|    20 |
+-------+

(g) SELECT COUNT(DISTINCT Price) FROM ITEM;

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

(h) SELECT Iname, Price*Quantity FROM ITEM;

+-----------+----------------+
| Iname     | Price*Quantity |
+-----------+----------------+
| SOAP      |           5000 |
| POWDER    |           5000 |
| FACECREAM |           3750 |
| PEN       |          10000 |
| SOAPBOX   |           2000 |
+-----------+----------------+

A function can be defined as a set of predefined commands which, when called, performs certain operations and returns a single value or a result set.

Functions are useful because they allow for code reusability, readability by breaking down complex tasks into smaller units.

SELECT SYSDATE();

+---------------------+
| SYSDATE()           |
+---------------------+
| 2024-06-14 11:56:41 |
+---------------------+

SELECT MONTHNAME(CURDATE());

+----------------------+
| MONTHNAME(CURDATE()) |
+----------------------+
| June                 |
+----------------------+

SELECT DAYOFWEEK('1999-07-21');

+-------------------------+
| DAYOFWEEK('1999-07-21') |
+-------------------------+
|                       4 |
+-------------------------+

The difference between the SYSDATE() and NOW() functions is that the SYSDATE() function returns the exact time at which the function executes. On the other hand, the NOW() function returns the current date and time at the beginning of the statement execution.

The Student table is as follows:

SELECT STREAM, COUNT(*) AS NUMBER_OF_STUDENTS
FROM STUDENT
GROUP BY STREAM;

+------------+--------------------+
| STREAM     | NUMBER_OF_STUDENTS |
+------------+--------------------+
| SCIENCE    |                  4 |
| COMMERCE   |                  2 |
| HUMANITIES |                  2 |
| VOCATIONAL |                  2 |
+------------+--------------------+

(a)

SELECT * FROM FACULTY WHERE SALARY > 12000;

+------+---------+--------+------------+--------+
| F_ID | F_NAME  | L_NAME | HIRE_DATE  | SALARY |
+------+---------+--------+------------+--------+
|  104 | RAKSHIT | SONI   | 2001-05-18 |  14000 |
+------+---------+--------+------------+--------+

(b)

SELECT * FROM FACULTY WHERE SALARY BETWEEN 8000 AND 12000;

+------+---------+------------+------------+--------+
| F_ID | F_NAME  | L_NAME     | HIRE_DATE  | SALARY |
+------+---------+------------+------------+--------+
|  102 | AMIT    | MISHRA     | 1998-10-12 |  10000 |
|  103 | NITIN   | VYAS       | 1994-12-24 |   8000 |
|  105 | RASHMI  | MALHOTRA   | 2004-09-11 |  11000 |
|  106 | SULEKHA | SRIVASTAVA | 2006-06-05 |  10000 |
+------+---------+------------+------------+--------+

(c)

SELECT COUNT(DISTINCT F_ID) FROM FACULTY;

+----------------------+
| COUNT(DISTINCT F_ID) |
+----------------------+
|                    5 |
+----------------------+

(d)

SELECT COUNT(*) FROM FACULTY WHERE SALARY = 10000;

+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+

(e)

 SELECT * FROM FACULTY WHERE F_NAME LIKE 'S%';

+------+---------+------------+------------+--------+
| F_ID | F_NAME  | L_NAME     | HIRE_DATE  | SALARY |
+------+---------+------------+------------+--------+
|  106 | SULEKHA | SRIVASTAVA | 2006-06-05 |  10000 |
+------+---------+------------+------------+--------+

(f)

SELECT * FROM FACULTY ORDER BY HIRE_DATE DESC;

+------+---------+------------+------------+--------+
| F_ID | F_NAME  | L_NAME     | HIRE_DATE  | SALARY |
+------+---------+------------+------------+--------+
|  106 | SULEKHA | SRIVASTAVA | 2006-06-05 |  10000 |
|  105 | RASHMI  | MALHOTRA   | 2004-09-11 |  11000 |
|  104 | RAKSHIT | SONI       | 2001-05-18 |  14000 |
|  102 | AMIT    | MISHRA     | 1998-10-12 |  10000 |
|  103 | NITIN   | VYAS       | 1994-12-24 |   8000 |
+------+---------+------------+------------+--------+

(g)

SELECT MAX(SALARY), MIN(SALARY) FROM FACULTY;

+-------------+-------------+
| MAX(SALARY) | MIN(SALARY) |
+-------------+-------------+
|       14000 |        8000 |
+-------------+-------------+

(h)

+------------------------+
| CONCAT(F_Name, L_Name) |
+------------------------+
| AMITMISHRA             |
| NITINVYAS              |
| RAKSHITSONI            |
| RASHMIMALHOTRA         |
| SULEKHASRIVASTAVA      |
+------------------------+

(i)

+------------------+
| Month(Hire_date) |
+------------------+
|               10 |
|               12 |
|                5 |
|                9 |
|                6 |
+------------------+

(a)

SELECT MIN(Sal) AS MinSalary, MAX(Sal) AS MaxSalary, AVG(Sal) AS AvgSalary
FROM EMP
WHERE Designation = 'MANAGER';

+-----------+-----------+-----------+
| MinSalary | MaxSalary | AvgSalary |
+-----------+-----------+-----------+
|      2985 |      2985 |      2985 |
+-----------+-----------+-----------+

(b)

SELECT COUNT(*) AS ClerkCount
FROM EMP
WHERE Designation = 'CLERK';

+------------+
| ClerkCount |
+------------+
|          1 |
+------------+

(c)

SELECT Designation, EmpName, Sal, DOJ
FROM EMP
ORDER BY Designation;

+-------------+-----------+------+------------+
| Designation | EmpName   | Sal  | DOJ        |
+-------------+-----------+------+------------+
| CLERK       | SMITH     |  800 | 1990-12-18 |
| MANAGER     | MAHADEVAN | 2985 | 1991-04-02 |
| SALESMAN    | ANYA      | 1600 | 1991-02-20 |
| SALESMAN    | SETH      | 1250 | 1991-02-22 |
+-------------+-----------+------+------------+

(d)

SELECT COUNT(*) AS No_comm
FROM EMP
WHERE comm is NULL;

+---------+
| No_comm |
+---------+
|       2 |
+---------+

(e)

SELECT d.DeptID, d.DeptName, AVG(e.Sal) AS Avg_Salary
FROM DEPT d, EMP e
WHERE e.DeptID = d.DeptID AND d.DeptID IN (
  SELECT DeptID
  FROM EMP
  GROUP BY DeptID
  HAVING COUNT(*) > 5
)
GROUP BY d.DeptID, d.DeptName;

(f)

SELECT DeptID, COUNT(*) AS EmpCount
FROM EMP
GROUP BY DeptID;

+--------+----------+
| DeptID | EmpCount |
+--------+----------+
|     20 |        2 |
|     30 |        2 |
+--------+----------+

(g)

SELECT D.DeptName, MAX(E.Sal) AS MaxSalary
FROM EMP E, DEPT D
WHERE E.DeptID = D.DeptID
GROUP BY D.DeptName;

+-----------+-----------+
| DeptName  | MaxSalary |
+-----------+-----------+
| PERSONNEL |      2985 |
| ACCOUNTS  |      1600 |
+-----------+-----------+

(h)

SELECT EMP.EMPNAME, EMP.DESIGNATION, DEPT.DEPTNAME
FROM EMP, DEPT
WHERE EMP.DEPTID = DEPT.DEPTID;

+-----------+-------------+-----------+
| EMPNAME   | DESIGNATION | DEPTNAME  |
+-----------+-------------+-----------+
| SMITH     | CLERK       | PERSONNEL |
| ANYA      | SALESMAN    | ACCOUNTS  |
| SETH      | SALESMAN    | ACCOUNTS  |
| MAHADEVAN | MANAGER     | PERSONNEL |
+-----------+-------------+-----------+

(i)

SELECT COUNT(*) AS NUM_EMP
FROM EMP, DEPT
WHERE EMP.DEPTID = DEPT.DEPTID
AND DEPTNAME = 'ACCOUNTS';

+---------+
| NUM_EMP |
+---------+
|       2 |
+---------+

(a)

SELECT * 
FROM PRODUCT 
WHERE PRICE BETWEEN 40 AND 120;

+------+-------------+--------------+-------+
| P_ID | ProductName | Manufacturer | Price |
+------+-------------+--------------+-------+
| P001 | MOISTURISER | XYZ          |    40 |
| P004 | SHAMPOO     | TAP          |    95 |
+------+-------------+--------------+-------+

(b)

UPDATE PRODUCT 
SET PRICE = PRICE + 20;

(a)

SELECT NAME 
FROM RESULT 
WHERE DIVISION = 'FIRST' 
ORDER BY NAME;

+---------+
| NAME    |
+---------+
| AMAL    |
| ANANDHA |
| VEDANT  |
+---------+

(b)

SELECT NAME, SUBJECT, (STIPEND * 12) AS ANNUAL_STIPEND 
FROM RESULT;

+---------+-------------+----------------+
| NAME    | SUBJECT     | ANNUAL_STIPEND |
+---------+-------------+----------------+
| SHARON  | ENGLISH     |           4800 |
| AMAL    | MATHEMATICS |           8160 |
| VEDANT  | ACCOUNTS    |           6000 |
| SHAKEER | INFORMATICS |           2400 |
| ANANDHA | HISTORY     |           4800 |
| UPASNA  | GEOGRAPHY   |           6600 |
+---------+-------------+----------------+

(c)

SELECT COUNT(*) 
FROM RESULT 
WHERE SUBJECT IN ('ACCOUNTS', 'INFORMATICS');

+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+

(d)

INSERT INTO RESULT 
VALUES(7, "MOHAN", 500, "ENGLISH", 73, "SECOND");

(e)

+--------------+
| AVG(STIPEND) |
+--------------+
|     475.0000 |
+--------------+

(f)

+-------------------------+
| COUNT(DISTINCT Subject) |
+-------------------------+
|                       6 |
+-------------------------+

(g)

+--------------+
| MIN(Average) |
+--------------+
|           38 |
+--------------+

(a)

SELECT * 
FROM SCHOOLBUS 
WHERE Capacity > NoOfStudents 
ORDER BY Rtno;

+------+--------------+----------+--------------+----------+---------------+---------+
| RTNO | AREA_COVERED | CAPACITY | NOOFSTUDENTS | DISTANCE | TRANSPORTER   | CHARGES |
+------+--------------+----------+--------------+----------+---------------+---------+
|    3 | PITAMPURA    |       60 |           55 |       30 | ANAND TRAVELS |   60000 |
|    4 | ROHINI       |      100 |           90 |       35 | ANAND TRAVELS |  100000 |
+------+--------------+----------+--------------+----------+---------------+---------+

(b)

SELECT Area_Covered 
FROM SCHOOLBUS 
WHERE Distance > 20 AND Charges < 80000;

+--------------+
| Area_Covered |
+--------------+
| PITAMPURA    |
+--------------+

(c)

SELECT Transporter, SUM(NoOfStudents) AS Total_Students 
FROM SCHOOLBUS 
GROUP BY Transporter;

+----------------+----------------+
| Transporter    | Total_Students |
+----------------+----------------+
| SHIVAM TRAVELS |            120 |
| ANAND TRAVELS  |            225 |
| BHALLA CO.     |             60 |
| YADAV CO.      |            190 |
| SPEED TRAVELS  |            160 |
| KISAN TOURS    |            100 |
+----------------+----------------+

(d)

SELECT Rtno, Area_Covered, (Charges / NoOfStudents) AS Average_Cost_Per_Student 
FROM SCHOOLBUS;

+------+---------------+--------------------------+
| Rtno | Area_Covered  | Average_Cost_Per_Student |
+------+---------------+--------------------------+
|    1 | VASANT KUNJ   |                 833.3333 |
|    2 | HAUZ KHAS     |                1062.5000 |
|    3 | PITAMPURA     |                1090.9091 |
|    4 | ROHINI        |                1111.1111 |
|    5 | YAMUNA VIHAR  |                 916.6667 |
|    6 | KRISHNA NAGAR |                1000.0000 |
|    7 | VASUNDHARA    |                 909.0909 |
|    8 | PASCHIM VIHAR |                1375.0000 |
|    9 | SAKET         |                 833.3333 |
|   10 | JANAKPURI     |                 950.0000 |
+------+---------------+--------------------------+

(e)

INSERT INTO SCHOOLBUS 
VALUES (11, 'Moti Bagh', 35, 32, 10, 'Kisan Tours', 35000);

(f)

1. SELECT SUM(Distance) FROM SCHOOLBUS WHERE Transporter= "Yadav Co.";

+---------------+
| SUM(Distance) |
+---------------+
|            50 |
+---------------+

2. SELECT MIN(NoOfStudents) FROM SCHOOLBUS;

+-------------------+
| MIN(NoOfStudents) |
+-------------------+
|                40 |
+-------------------+

3. SELECT AVG(Charges) FROM SCHOOLBUS WHERE Transporter = "Anand Travels";

+--------------+
| AVG(Charges) |
+--------------+
|   81666.6667 |
+--------------+

4. SELECT DISTINCT Transporter FROM SCHOOLBUS;

+----------------+
| Transporter    |
+----------------+
| SHIVAM TRAVELS |
| ANAND TRAVELS  |
| BHALLA CO.     |
| YADAV CO.      |
| SPEED TRAVELS  |
| KISAN TOURS    |
+----------------+

(a)

SELECT * FROM CUSTOMERS 
ORDER BY NAME;

+----+----------+-----+-----------+--------+
| ID | NAME     | AGE | ADDRESS   | SALARY |
+----+----------+-----+-----------+--------+
|  4 | CHAITALI |  25 | MUMBAI    |   6500 |
|  5 | HARDIK   |  27 | BHOPAL    |   8500 |
|  3 | KAUSHIK  |  23 | KOTA      |   2000 |
|  2 | KHILAN   |  25 | DELHI     |   1500 |
|  6 | KOMAL    |  22 | BENGALURU |   4500 |
|  7 | MUFFY    |  24 | INDORE    |  10000 |
|  1 | RAMESH   |  32 | AHMEDABAD |   2000 |
+----+----------+-----+-----------+--------+

(b)

SELECT * FROM CUSTOMERS 
ORDER BY NAME DESC;

+----+----------+-----+-----------+--------+
| ID | NAME     | AGE | ADDRESS   | SALARY |
+----+----------+-----+-----------+--------+
|  1 | RAMESH   |  32 | AHMEDABAD |   2000 |
|  7 | MUFFY    |  24 | INDORE    |  10000 |
|  6 | KOMAL    |  22 | BENGALURU |   4500 |
|  2 | KHILAN   |  25 | DELHI     |   1500 |
|  3 | KAUSHIK  |  23 | KOTA      |   2000 |
|  5 | HARDIK   |  27 | BHOPAL    |   8500 |
|  4 | CHAITALI |  25 | MUMBAI    |   6500 |
+----+----------+-----+-----------+--------+

(c)

SELECT * 
FROM CUSTOMERS 
ORDER BY NAME ASC, AGE DESC;

+----+----------+-----+-----------+--------+
| ID | NAME     | AGE | ADDRESS   | SALARY |
+----+----------+-----+-----------+--------+
|  4 | CHAITALI |  25 | MUMBAI    |   6500 |
|  5 | HARDIK   |  27 | BHOPAL    |   8500 |
|  3 | KAUSHIK  |  23 | KOTA      |   2000 |
|  2 | KHILAN   |  25 | DELHI     |   1500 |
|  6 | KOMAL    |  22 | BENGALURU |   4500 |
|  7 | MUFFY    |  24 | INDORE    |  10000 |
|  1 | RAMESH   |  32 | AHMEDABAD |   2000 |
+----+----------+-----+-----------+--------+

(d)

SELECT MAX(SALARY) FROM CUSTOMERS;

+-------------+
| MAX(SALARY) |
+-------------+
|       10000 |
+-------------+

(e)

SELECT MIN(SALARY) FROM CUSTOMERS;

+-------------+
| MIN(SALARY) |
+-------------+
|        1500 |
+-------------+

(f)

SELECT COUNT(*) FROM CUSTOMERS;

+----------+
| COUNT(*) |
+----------+
|        7 |
+----------+

(g)

SELECT AVG(SALARY) FROM CUSTOMERS;

+-------------+
| AVG(SALARY) |
+-------------+
|        5000 |
+-------------+

(h)

SELECT SUM(SALARY) FROM CUSTOMERS;

+-------------+
| SUM(SALARY) |
+-------------+
|       35000 |
+-------------+

(i)

SELECT NAME 
FROM CUSTOMERS 
WHERE SALARY > (SELECT AVG(SALARY) FROM CUSTOMERS);

+----------+
| NAME     |
+----------+
| CHAITALI |
| HARDIK   |
| MUFFY    |
+----------+

(j)

SELECT * FROM CUSTOMERS 
WHERE AGE < (SELECT AVG(AGE) FROM CUSTOMERS);

+----+----------+-----+-----------+--------+
| ID | NAME     | AGE | ADDRESS   | SALARY |
+----+----------+-----+-----------+--------+
|  2 | KHILAN   |  25 | DELHI     |   1500 |
|  3 | KAUSHIK  |  23 | KOTA      |   2000 |
|  4 | CHAITALI |  25 | MUMBAI    |   6500 |
|  6 | KOMAL    |  22 | BENGALURU |   4500 |
|  7 | MUFFY    |  24 | INDORE    |  10000 |
+----+----------+-----+-----------+--------+

(a)

SELECT * 
FROM WORKER 
ORDER BY DOB DESC;

+-------+--------------+------------+--------+------------+------------+
| ECODE | NAME         | DESIG      | PLEVEL | DOJ        | DOB        |
+-------+--------------+------------+--------+------------+------------+
|    12 | CHANDER NATH | OPERATOR   | P003   | 2010-02-22 | 1987-07-12 |
|    11 | SACHIN PATEL | SUPERVISOR | P001   | 2004-09-13 | 1985-08-23 |
|    15 | AMEEN AHMED  | MECHANIC   | P002   | 2006-08-21 | 1984-03-13 |
|    13 | FIZZA        | OPERATOR   | P003   | 2009-06-14 | 1983-10-14 |
|    18 | SANYA        | CLERK      | P002   | 2005-12-19 | 1983-06-09 |
+-------+--------------+------------+--------+------------+------------+

(b)

SELECT PLEVEL, COUNT(*) 
FROM WORKER 
GROUP BY PLEVEL;

+--------+----------+
| PLEVEL | COUNT(*) |
+--------+----------+
| P001   |        1 |
| P003   |        2 |
| P002   |        2 |
+--------+----------+

(c)

SELECT PL.PLEVEL, COUNT(*)   
FROM WORKER W, PAYLEVEL PL  
WHERE W.PLEVEL = PL.PLEVEL AND PL.PAY > 15000   
GROUP BY PL.PLEVEL;

+--------+----------+
| PLEVEL | COUNT(*) |
+--------+----------+
| P001   |        1 |
| P002   |        2 |
+--------+----------+

(d)

SELECT NAME, DESIG 
FROM WORKER 
WHERE PLEVEL IN ('P001', 'P002');

+--------------+------------+
| NAME         | DESIG      |
+--------------+------------+
| SACHIN PATEL | SUPERVISOR |
| AMEEN AHMED  | MECHANIC   |
| SANYA        | CLERK      |
+--------------+------------+

(e)

1. SELECT COUNT(PLEVEL), PLEVEL FROM WORKER GROUP BY PLEVEL;

+---------------+--------+
| COUNT(PLEVEL) | PLEVEL |
+---------------+--------+
|             1 | P001   |
|             2 | P003   |
|             2 | P002   |
+---------------+--------+

2. SELECT MAX(DOB), MIN(DOJ) FROM WORKER;

+------------+------------+
| MAX(DOB)   | MIN(DOJ)   |
+------------+------------+
| 1987-07-12 | 2004-09-13 |
+------------+------------+

(a) In the EVENT table, the primary key is EventId, and in the CELEBRITY table, the primary key is CelebrityID. The foreign key in the EVENT table is CelebrityID, which references the CelebrityID in the CELEBRITY table.

NumPerformers cannot be set as the primary key because primary keys must uniquely identify each row in the table. NumPerformers can have duplicate values for different events, as multiple events can have the same number of performers.

(b) The EVENT table has 4 rows and the CELEBRITY table has 4 rows. So, the Cartesian join will have: 4 × 4 = 16 rows.

(c)

1.

SELECT EVENTID, EVENT, CELEBRITYID 
FROM EVENT 
WHERE NUMPERFORMERS > 10;

+---------+-----------------+-------------+
| EVENTID | EVENT           | CELEBRITYID |
+---------+-----------------+-------------+
|     102 | PROMOTION PARTY | C103        |
|     103 | ENGAGEMENT      | C102        |
|     104 | WEDDING         | C104        |
+---------+-----------------+-------------+

2.

SELECT CELEBRITYID, CELEBRITYNAME  
FROM CELEBRITY  
WHERE CELEBRITYNAME LIKE '%Khan%';

+-------------+-------------------+
| CELEBRITYID | CELEBRITYNAME     |
+-------------+-------------------+
| C101        | FAIZ KHAN         |
| C103        | NEERA KHAN KAPOOR |
+-------------+-------------------+

3.

SELECT CELEBRITYNAME, FEECHARGED 
FROM CELEBRITY 
WHERE FEECHARGED > 200000;

+-------------------+------------+
| CELEBRITYNAME     | FEECHARGED |
+-------------------+------------+
| SANJAY KUMAR      |     250000 |
| NEERA KHAN KAPOOR |     300000 |
+-------------------+------------+

(a)

SELECT Name FROM STUDENT 
WHERE Stream = 'Science';

(b)

SELECT COUNT(*) FROM STUDENT 
WHERE Stream = 'Commerce';

(c)

SELECT Designation, COUNT(*) AS TeacherCount FROM TEACHER 
GROUP BY Designation;

(d)

SELECT MAX(Pay) FROM TEACHER 
WHERE Subject = 'English';

(e)

SELECT S.Name 
FROM STUDENT S, TEACHER T 
WHERE S.TeacherID = T.TeacherID AND T.TName = 'Anand Mathur';

(f)

SELECT T.TName, T.Designation 
FROM STUDENT S, TEACHER T 
WHERE S.TeacherID = T.TeacherID AND S.Name = 'Amit';

(g)

SELECT TName 
FROM TEACHER 
WHERE Pay = (SELECT MAX(Pay) FROM TEACHER);

(h)

SELECT DISTINCT City 
FROM TEACHER 
WHERE Subject = 'Maths';

(i)

SELECT TName 
FROM TEACHER 
WHERE Designation = 'PGT' AND 
Pay = (SELECT MIN(Pay) FROM TEACHER WHERE Designation = 'PGT');

(j)

SELECT S.Name 
FROM STUDENT S, TEACHER T 
WHERE S.TeacherID = T.TeacherID  AND T.Designation = 'PGT';

(a)

SELECT * 
FROM TEACHER
WHERE CATEGORY = 'PGT' ;

+----+---------------+------------+------------+----------+--------+--------+
| ID | NAME          | DEPARTMENT | HIREDATE   | CATEGORY | GENDER | SALARY |
+----+---------------+------------+------------+----------+--------+--------+
|  3 | NANDITA ARORA | ENGLISH    | 1980-05-16 | PGT      | F      |  30000 |
+----+---------------+------------+------------+----------+--------+--------+

(b)

SELECT Name 
FROM TEACHER 
WHERE Department = 'Hindi' AND Gender = 'F';

+---------------+
| Name          |
+---------------+
| JASPREET KAUR |
+---------------+

(c)

SELECT NAME, DEPARTMENT, HIREDATE 
FROM TEACHER
ORDER BY HIREDATE;

+------------------+---------------+------------+
| NAME             | DEPARTMENT    | HIREDATE   |
+------------------+---------------+------------+
| DISHA SEHGAL     | MATH          | 1980-03-17 |
| NANDITA ARORA    | ENGLISH       | 1980-05-16 |
| SONALI MUKHERJEE | MATH          | 1980-11-17 |
| JAMES JACOB      | ENGLISH       | 1989-10-16 |
| SAURABH SHARMA   | ART           | 1990-02-12 |
| JASPREET KAUR    | HINDI         | 1990-08-01 |
| TANYA NANDA      | SOCIALSTUDIES | 1994-03-17 |
+------------------+---------------+------------+

(d)

SELECT COUNT(*) 
FROM TEACHER
WHERE DEPARTMENT = 'ENGLISH';

+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+

(e)

SELECT DEPARTMENT, HIREDATE 
FROM TEACHER
WHERE GENDER = 'F' AND SALARY > 25000;

+------------+------------+
| DEPARTMENT | HIREDATE   |
+------------+------------+
| ENGLISH    | 1980-05-16 |
+------------+------------+

(f)

SELECT Name 
FROM TEACHER  
WHERE Name LIKE 'J%';

+---------------+
| Name          |
+---------------+
| JAMES JACOB   |
| JASPREET KAUR |
+---------------+

(g)

+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+

(h)

+-------------+
| AVG(Salary) |
+-------------+
|  24500.0000 |
|  22500.0000 |
+-------------+

(a)

SELECT NAME FROM SPORTS 
WHERE GRADE1 = 'A' OR GRADE2 = 'A';

+---------+
| NAME    |
+---------+
| SAMEER  |
| SUJIT   |
| VEENA   |
| ARCHANA |
| ARPIT   |
+---------+

(b)

SELECT COUNT(*) FROM SPORTS 
WHERE GAME1 = 'CRICKET' OR GAME2 = 'CRICKET' ;

+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+

(c)

SELECT NAME FROM SPORTS 
WHERE GAME1 = GAME2;

+-------+
| NAME  |
+-------+
| VEENA |
+-------+

(d)

SELECT NAME, GAME1, GAME2 
FROM SPORTS 
WHERE NAME LIKE 'A%';

+---------+------------+-----------+
| NAME    | GAME1      | GAME2     |
+---------+------------+-----------+
| ARCHANA | BASKETBALL | CRICKET   |
| ARPIT   | CRICKET    | ATHLETICS |
+---------+------------+-----------+

(e)

1. SELECT COUNT(*) FROM SPORTS;

+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+

2. SELECT DISTINCT CLASS FROM SPORTS;

+-------+
| CLASS |
+-------+
|     7 |
|     8 |
|     9 |
|    10 |
+-------+

3. SELECT MAX(Class) FROM SPORTS;

+------------+
| MAX(Class) |
+------------+
|         10 |
+------------+

4. SELECT COUNT(*) FROM SPORTS GROUP BY Game1;

+----------+
| COUNT(*) |
+----------+
|        2 |
|        2 |
|        1 |
|        1 |
+----------+

(a)

SELECT * FROM ITEM;

+--------+-----------+-------+----------+
| ITEMNO | INAME     | PRICE | QUANTITY |
+--------+-----------+-------+----------+
|    101 | SOAP      |    50 |      100 |
|    102 | POWDER    |   100 |       50 |
|    103 | FACECREAM |   150 |       25 |
|    104 | PEN       |    50 |      200 |
|    105 | SOAPBOX   |    20 |      100 |
+--------+-----------+-------+----------+

(b)

SELECT INAME, PRICE 
FROM ITEM;

+-----------+-------+
| INAME     | PRICE |
+-----------+-------+
| SOAP      |    50 |
| POWDER    |   100 |
| FACECREAM |   150 |
| PEN       |    50 |
| SOAPBOX   |    20 |
+-----------+-------+

(c)

SELECT  * FROM ITEM 
WHERE INAME = 'SOAP';

+--------+-------+-------+----------+
| ITEMNO | INAME | PRICE | QUANTITY |
+--------+-------+-------+----------+
|    101 | SOAP  |    50 |      100 |
+--------+-------+-------+----------+

(d)

SELECT * FROM ITEM 
WHERE INAME LIKE 'S%';

+--------+---------+-------+----------+
| ITEMNO | INAME   | PRICE | QUANTITY |
+--------+---------+-------+----------+
|    101 | SOAP    |    50 |      100 |
|    105 | SOAPBOX |    20 |      100 |
+--------+---------+-------+----------+

(e)

SELECT ITEMNO, INAME, (PRICE * QUANTITY) AS Total_Price 
FROM ITEM;

+--------+-----------+-------------+
| ITEMNO | INAME     | Total_Price |
+--------+-----------+-------------+
|    101 | SOAP      |        5000 |
|    102 | POWDER    |        5000 |
|    103 | FACECREAM |        3750 |
|    104 | PEN       |       10000 |
|    105 | SOAPBOX   |        2000 |
+--------+-----------+-------------+

(f)

+-------+
| PRICE |
+-------+
|    50 |
|   100 |
|   150 |
|    20 |
+-------+

(g)

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

(a)

+-----------+
| POW(2, 3) |
+-----------+
|         8 |
+-----------+

(b)

+--------------------+--------------------+
| ROUND(123.2345, 2) | ROUND(342.9234,-1) |
+--------------------+--------------------+
|             123.23 |                340 |
+--------------------+--------------------+

(c)

+---------------------------------+
| LENGTH("Informatics Practices") |
+---------------------------------+
|                              21 |
+---------------------------------+

(d)

+--------------------+---------------------+-------------------------+-------------------+
| YEAR("1979-11-26") | MONTH("1979-11-26") | MONTHNAME("1979-11-26") | DAY("1979-11-26") |
+--------------------+---------------------+-------------------------+-------------------+
|               1979 |                  11 | November                |                26 |
+--------------------+---------------------+-------------------------+-------------------+

(e)

+------------------+------------------------------+
| LEFT("INDIA", 3) | RIGHT("Computer Science", 4) |
+------------------+------------------------------+
| IND              | ence                         |
+------------------+------------------------------+

(f)

+-------------------------+------------------------+
| MID("Informatics", 3,4) | SUBSTR("Practices", 3) |
+-------------------------+------------------------+
| form                    | actices                |
+-------------------------+------------------------+

(g)

+----------------------------------------------+
| CONCAT("You Scored", LENGTH("123") , "rank") |
+----------------------------------------------+
| You Scored3rank                              |
+----------------------------------------------+

(h)

+-------------+
| ABS(-67.89) |
+-------------+
|       67.89 |
+-------------+

(i)

+--------------------------------+
| SQRT(625) + ROUND(1234.89, -3) |
+--------------------------------+
|                           1025 |
+--------------------------------+

(j)

+------------+
| MOD(56, 8) |
+------------+
|          0 |
+------------+

False

Reason — The SQL ORDER BY clause is used to sort data in ascending or descending order based on one or more columns. To group records with identical values, the GROUP BY clause is used.

False

Reason — The HAVING clause is used to specify filtering conditions for groups.

True

Reason — The DISTINCT keyword, when used with group functions (such as COUNT, SUM, AVG), ensures that only unique values of the specified column or expression are considered in the calculation, thereby eliminating duplicates from the result set.

False

Reason — By default, ORDER BY clause sorts the result set in ascending order.

True

Reason — The COUNT() function is used to count the number of non-null values in a column. It ignores any null values present in the column.

False

Reason — The MAX() function returns the largest value in a set of values, which can be numeric, date, or time. Therefore, the return value of the MAX() function is not always a numeric value.

False

Reason — Single row function is also known as scalar function.

False

Reason — The COUNT() function is used to count the total number of records in a table, while the SUM() function is used to find the total value of a specific column.

False

Reason — The AVG() function always takes arguments of integer type only.

True

Reason — Group functions, also known as aggregate functions in SQL, can be applied to numeric values, some text types, and DATE values. These functions perform calculations across multiple rows and return a single result.