Question

Write suitable SQL query for the following:

I. To display the value of 17².

II. To display the day part of the dates in the Hire_Date column of the Employee table.

III. To display the maximum value of the Bill_Amount column in the Bill table.

IV. To remove leading and trailing spaces from the Location column in the Branch table.

V. To display total salary from tot_sal column in Employees table.

Hint:

Use POW() for the power, DAY() for the day part, MAX() for the largest value, TRIM() to remove spaces and SUM() for the total.

Answer 1

Step 1: Understanding the Concept:
Each part asks for one SQL query. MySQL has built-in functions for power, date parts, maximum, trimming and totals. Each query is a SELECT statement that calls one function.

Step 2: Key Formula or Approach:
1. POW(x, y) or POWER(x, y) returns x raised to the power y.
2. DAY(date) returns the day of the month from a date.
3. MAX(column) is an aggregate function that returns the largest value of a column.
4. TRIM(string) removes spaces from both the start and the end of a string.
5. SUM(column) is an aggregate function that adds all values of a column.

Step 3: Part I - value of \( 17^2 \):
The power function takes the base first and the exponent second. We need 17 as the base and 2 as the exponent. No table is needed.

SELECT POW(17, 2);

The output is 289, because \( 17 \times 17 = 289 \).

Step 4: Part II - day part of Hire_Date:
The DAY function works on a date column and gives only the day number. We apply it to every row of the Employee table.

SELECT DAY(Hire_Date) FROM Employee;

For a hire date of 2024-03-15 the output is 15.

Step 5: Part III - maximum Bill_Amount:
MAX looks at all rows of the column and returns one value, the largest. The table here is Bill.

SELECT MAX(Bill_Amount) FROM Bill;

Step 6: Part IV - remove leading and trailing spaces:
TRIM cuts spaces on both ends of the text. It does not touch spaces in the middle of the text. We apply it to Location in the Branch table.

SELECT TRIM(Location) FROM Branch;

For the value ' Delhi ' the output is 'Delhi'.

Step 7: Part V - total salary:
SUM adds the values of the tot_sal column over all rows of the Employees table. The result is a single number.

SELECT SUM(tot_sal) FROM Employees;

Final Answer:
The five queries are:
I. SELECT POW(17, 2);
II. SELECT DAY(Hire_Date) FROM Employee;
III. SELECT MAX(Bill_Amount) FROM Bill;
IV. SELECT TRIM(Location) FROM Branch;
V. SELECT SUM(tot_sal) FROM Employees;