Structured Query a Language (SQL)

RDBMS (Relational Database Management System):

An RDBMS is a type of Database Management System (DBMS) that stores data in the form of related tables (relations). Each table consists of rows (records/tuples) and columns (attributes/fields). The relationships between tables are established using keys (Primary Key and Foreign Key).

Two popular RDBMS software:
1. MySQL
2. Oracle

i) ORDER BY clause:

The `ORDER BY` clause is used to display the result of a SQL query in either ascending (`ASC`) or descending (`DESC`) order with respect to the values of a specified attribute. By default, the order is ascending.

Syntax:
$$\text{SELECT column\_list FROM table\_name ORDER BY column\_name [ASC|DESC];}$$

Example:
```sql
SELECT * FROM Student ORDER BY Name ASC;
```

ii) GROUP BY clause:

The `GROUP BY` clause is used to group rows of a table that contain the same values in a specified column. It is generally used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to produce summary results for each group.

Syntax:
$$\text{SELECT column, aggregate\_function FROM table\_name GROUP BY column;}$$

Example:
```sql
SELECT Category, COUNT(*) FROM MOVIE GROUP BY Category;
```

| Basis | Single Row Functions | Aggregate Functions (Multiple Row Functions) |
|---|---|---|
| Working | Work on a single row at a time and return one result per row. | Work on a set of rows (entire table or a group) and return a single summarised value. |
| Examples | `UPPER()`, `LOWER()`, `LENGTH()`, `ROUND()`, `NOW()` | `COUNT()`, `SUM()`, `AVG()`, `MAX()`, `MIN()` |
| Use with GROUP BY | Not required to use with GROUP BY. | Often used with GROUP BY to produce group-wise results. |
| Output rows | Number of output rows equals number of input rows. | Produces a single output row (or one row per group). |

Cartesian Product:

A Cartesian Product (also called Cross Join) is an operation that combines each row of one table with every row of another table. If Table A has $m$ rows and Table B has $n$ rows, then their Cartesian Product will have $m \times n$ rows.

In SQL, a Cartesian Product is obtained when two tables are listed in the `FROM` clause without any `WHERE` or `JOIN` condition.

Syntax:
```sql
SELECT * FROM Table1, Table2;
```

Example: If TEAM has 4 rows and MATCH_DETAILS has 6 rows, their Cartesian Product will produce $4 \times 6 = 24$ rows.

Cartesian Products are generally avoided in practice as they produce a very large number of meaningless combinations.

i) ALTER vs UPDATE:

| Basis | ALTER | UPDATE |
|---|---|---|
| Type | DDL (Data Definition Language) statement | DML (Data Manipulation Language) statement |
| Purpose | Used to change the structure of a table (add/remove/modify columns, add/drop constraints). | Used to modify existing data (values) in one or more rows of a table. |
| Example | `ALTER TABLE Student ADD Marks INT;` | `UPDATE Student SET Marks = 90 WHERE RollNo = 1;` |

ii) DELETE vs DROP:

| Basis | DELETE | DROP |
|---|---|---|
| Type | DML (Data Manipulation Language) statement | DDL (Data Definition Language) statement |
| Purpose | Used to remove specific rows (or all rows) from a table. The table structure remains intact. | Used to remove the entire table (structure + data) permanently from the database. |
| Reversible | Can be rolled back (in transactions). | Cannot be easily reversed; the table is permanently deleted. |
| Example | `DELETE FROM Student WHERE RollNo = 5;` | `DROP TABLE Student;` |

i) To display the day name (like 'Monday', 'Tuesday') from India's Independence date (15th August 1947):
$$\textbf{DAYNAME()}$$
```sql
SELECT DAYNAME('1947-08-15');
```
Output: `Saturday`

ii) To display a specified number of characters from a particular position of a string:
$$\textbf{MID() or SUBSTR() / SUBSTRING()}$$
```sql
SELECT MID('Informatics', 3, 4);
-- or
SELECT SUBSTR('Informatics', 3, 4);
```

iii) To display the name of the month in which you were born:
$$\textbf{MONTHNAME()}$$
```sql
SELECT MONTHNAME('2005-03-15');
```
Output: `March`

iv) To display your name in capital (uppercase) letters:
$$\textbf{UPPER() or UCASE()}$$
```sql
SELECT UPPER('Rahul');
```
Output: `RAHUL`

Given: `SELECT POW(2,3);`

Concept: `POW(base, exponent)` returns the value of base raised to the power of exponent.

$$POW(2,3) = 2^3 = 8$$

Output:
```
+----------+
| POW(2,3) |
+----------+
| 8 |
+----------+
```

Given: `SELECT ROUND(342.9234, -1);`

Concept: `ROUND(number, decimal_places)` rounds a number to the specified number of decimal places. A negative value for decimal places rounds to the left of the decimal point.

- `ROUND(342.9234, -1)` rounds to the nearest tens place.
- $342.9234 \approx 340$ (since the units digit is 2, which is less than 5, round down)

Output:
```
+----------------------+
| ROUND(342.9234,-1) |
+----------------------+
| 340 |
+----------------------+
```

Given: `SELECT LENGTH("Informatics Practices");`

Concept: `LENGTH(string)` returns the number of characters in the string, including spaces.

Counting characters in `"Informatics Practices"`:
- `Informatics` = 11 characters
- ` ` (space) = 1 character
- `Practices` = 9 characters
- Total = $11 + 1 + 9 = 21$

Output:
```
+---------------------------+
| LENGTH("Informatics Practices") |
+---------------------------+
| 21 |
+---------------------------+
```

Given: `SELECT YEAR("1979/11/26"), MONTH("1979/11/26"), DAY("1979/11/26"), MONTHNAME("1979/11/26");`

Concept:
- `YEAR(date)` → extracts the year part
- `MONTH(date)` → extracts the month as a number
- `DAY(date)` → extracts the day part
- `MONTHNAME(date)` → returns the full name of the month

Working:
- Date = `1979/11/26`
- `YEAR` → `1979`
- `MONTH` → `11`
- `DAY` → `26`
- `MONTHNAME` → `November`

Output:
```
+--------------------+---------------------+-------------------+---------------------------+
| YEAR("1979/11/26") | MONTH("1979/11/26") | DAY("1979/11/26") | MONTHNAME("1979/11/26") |
+--------------------+---------------------+-------------------+---------------------------+
| 1979 | 11 | 26 | November |
+--------------------+---------------------+-------------------+---------------------------+
```

Given: `SELECT LEFT("INDIA",3), RIGHT("Computer Science",4), MID("Informatics",3,4), SUBSTR("Practices",3);`

Concept and Working:

1. `LEFT("INDIA", 3)` → Returns the leftmost 3 characters of `"INDIA"`
- Result: `IND`

2. `RIGHT("Computer Science", 4)` → Returns the rightmost 4 characters of `"Computer Science"`
- `"Computer Science"` → last 4 chars = `ence`
- Result: `ence`

3. `MID("Informatics", 3, 4)` → Returns 4 characters starting from position 3
- `I(1) n(2) f(3) o(4) r(5) m(6) a(7) t(8) i(9) c(10) s(11)`
- Starting at position 3: `f`, `o`, `r`, `m` → `form`
- Result: `form`

4. `SUBSTR("Practices", 3)` → Returns substring starting from position 3 to end
- `P(1) r(2) a(3) c(4) t(5) i(6) c(7) e(8) s(9)`
- Starting at position 3: `actices`
- Result: `actices`

Output:
```
+----------------+-------------------------------+------------------------+----------------------+
| LEFT("INDIA",3)| RIGHT("Computer Science",4) | MID("Informatics",3,4) | SUBSTR("Practices",3)|
+----------------+-------------------------------+------------------------+----------------------+
| IND | ence | form | actices |
+----------------+-------------------------------+------------------------+----------------------+
```

SQL Query:
```sql
SELECT * FROM MOVIE;
```

Explanation: The `SELECT *` statement retrieves all columns and all rows from the MOVIE table. The `*` (asterisk) is a wildcard that represents all columns.

SQL Query:
```sql
SELECT MovieID, MovieName, (ProductionCost + BusinessCost) AS Total_Earning
FROM MOVIE;
```

Explanation:
- We select `MovieID` and `MovieName` directly.
- `Total_Earning` is a computed column calculated as the sum of `ProductionCost` and `BusinessCost`.
- The `AS` keyword is used to give an alias name `Total_Earning` to the computed column.

SQL Query:
```sql
SELECT DISTINCT Category FROM MOVIE;
```

Explanation: The `DISTINCT` keyword eliminates duplicate values and displays each category only once.

Expected Output:
```
Musical
Action
Horror
Adventure
Comedy
```

SQL Query:
```sql
SELECT MovieID, MovieName, (BusinessCost - ProductionCost) AS NetProfit
FROM MOVIE;
```

Explanation:
- `NetProfit` is a computed column calculated as `BusinessCost - ProductionCost`.
- The `AS` keyword assigns the alias `NetProfit` to the computed expression.

SQL Query:
```sql
SELECT MovieID, MovieName, ProductionCost AS Cost
FROM MOVIE
WHERE ProductionCost > 10000 AND ProductionCost < 100000;
```

Alternative using BETWEEN (note: BETWEEN is inclusive, so we use > and <):
```sql
SELECT MovieID, MovieName, ProductionCost AS Cost
FROM MOVIE
WHERE ProductionCost BETWEEN 10001 AND 99999;
```

Explanation: The `WHERE` clause filters rows where `ProductionCost` is strictly greater than 10,000 and strictly less than 1,00,000.

Expected Output (from given data):
- 002 Tamil_Movie 112000 → excluded (> 1,00,000)
- 004 Bengali_Movie 72000 → included
- 005 Telugu_Movie 100000 → excluded (not less than 1,00,000)
- 006 Punjabi_Movie 30500 → included
- 001 Hindi_Movie 124500 → excluded

SQL Query:
```sql
SELECT * FROM MOVIE
WHERE Category = 'Comedy' OR Category = 'Action';
```

Alternative using IN operator:
```sql
SELECT * FROM MOVIE
WHERE Category IN ('Comedy', 'Action');
```

Explanation: The `IN` operator checks if the value of `Category` matches any value in the given list. Both queries produce the same result — rows for Tamil_Movie (Action), Telugu_Movie (Action), and Punjabi_Movie (Comedy).

SQL Query:
```sql
SELECT * FROM MOVIE
WHERE ReleaseDate IS NULL;
```

Explanation: Movies that have not been released yet will have a `NULL` value in the `ReleaseDate` column. We use `IS NULL` to check for NULL values (we cannot use `= NULL`).

Expected Output: Telugu_Movie (005) and Punjabi_Movie (006) — both have no release date (shown as `-` in the table, meaning NULL).

SQL Query:
```sql
CREATE DATABASE Sports;
USE Sports;
```

Explanation:
- `CREATE DATABASE Sports;` creates a new database named `Sports`.
- `USE Sports;` selects the database so that subsequent SQL statements operate on it.

SQL Query:
```sql
CREATE TABLE TEAM
(
TeamID INT,
TeamName VARCHAR(30)
);
```

Explanation:
- `TeamID` is of type `INT` to store integer values between 1 to 9.
- `TeamName` is of type `VARCHAR(30)` — a variable-length string that can hold team names of length not less than 10 characters (we define maximum as 30 to accommodate longer names).
- The Primary Key constraint is added separately in part (c) as per the question.

SQL Query (with table-level PRIMARY KEY constraint):
```sql
CREATE TABLE TEAM
(
TeamID INT,
TeamName VARCHAR(30),
CONSTRAINT pk_team PRIMARY KEY (TeamID)
);
```

Explanation:
- A table-level constraint is defined separately after all column definitions.
- `CONSTRAINT pk_team PRIMARY KEY (TeamID)` declares `TeamID` as the Primary Key at the table level.
- This ensures that `TeamID` values are unique and not NULL.

SQL Query:
```sql
DESCRIBE TEAM;
```
or
```sql
DESC TEAM;
```

Explanation: The `DESCRIBE` (or `DESC`) statement displays the structure of the table — column names, data types, whether NULL is allowed, key information, default values, and extra information.

Expected Output:
```
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| TeamID | int | NO | PRI | NULL | |
| TeamName | varchar(30) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
```

SQL Queries:
```sql
INSERT INTO TEAM VALUES (1, 'Team Titan');
INSERT INTO TEAM VALUES (2, 'Team Rockers');
INSERT INTO TEAM VALUES (3, 'Team Magnet');
INSERT INTO TEAM VALUES (4, 'Team Hurricane');
```

Explanation: The `INSERT INTO` statement is used to add new rows into the table. Values are provided in the same order as the columns defined in the table structure.

SQL Query:
```sql
SELECT * FROM TEAM;
```

Explanation: `SELECT` is a DML (Data Manipulation Language) statement used to retrieve data from a table. `*` retrieves all columns.

Expected Output:
```
+--------+----------------+
| TeamID | TeamName |
+--------+----------------+
| 1 | Team Titan |
| 2 | Team Rockers |
| 3 | Team Magnet |
| 4 | Team Hurricane |
+--------+----------------+
```

SQL Query to Create Table:
```sql
CREATE TABLE MATCH_DETAILS
(
MatchID VARCHAR(5),
MatchDate DATE,
FirstTeamID INT,
SecondTeamID INT,
FirstTeamScore INT,
SecondTeamScore INT,
CONSTRAINT pk_match PRIMARY KEY (MatchID),
CONSTRAINT fk_first FOREIGN KEY (FirstTeamID) REFERENCES TEAM(TeamID),
CONSTRAINT fk_second FOREIGN KEY (SecondTeamID) REFERENCES TEAM(TeamID)
);
```

SQL Queries to Insert Data:
```sql
INSERT INTO MATCH_DETAILS VALUES ('M1', '2018-07-17', 1, 2, 90, 86);
INSERT INTO MATCH_DETAILS VALUES ('M2', '2018-07-18', 3, 4, 45, 48);
INSERT INTO MATCH_DETAILS VALUES ('M3', '2018-07-19', 1, 3, 78, 56);
INSERT INTO MATCH_DETAILS VALUES ('M4', '2018-07-19', 2, 4, 56, 67);
INSERT INTO MATCH_DETAILS VALUES ('M5', '2018-07-18', 1, 4, 32, 87);
INSERT INTO MATCH_DETAILS VALUES ('M6', '2018-07-17', 2, 3, 67, 51);
```

Explanation of Data Types:
- `MatchID`: `VARCHAR(5)` — short string identifier like M1, M2.
- `MatchDate`: `DATE` — stores date in YYYY-MM-DD format.
- `FirstTeamID`, `SecondTeamID`: `INT` — integer references to TeamID in TEAM table.
- `FirstTeamScore`, `SecondTeamScore`: `INT` — integer scores.
- Foreign Key constraints ensure referential integrity with the TEAM table.

SQL Query:
```sql
SELECT MatchID
FROM MATCH_DETAILS
WHERE FirstTeamScore > 70 AND SecondTeamScore > 70;
```

Working through the data:
- M1: 90 > 70 AND 86 > 70 → ✓
- M2: 45 > 70 → ✗
- M3: 78 > 70 AND 56 > 70 → ✗ (56 not > 70)
- M4: 56 > 70 → ✗
- M5: 32 > 70 → ✗
- M6: 67 > 70 → ✗

Output:
```
+---------+
| MatchID |
+---------+
| M1 |
+---------+
```

SQL Query:
```sql
SELECT MatchID
FROM MATCH_DETAILS
WHERE FirstTeamScore < 70 AND SecondTeamScore > 70;
```

Working through the data:
- M1: 90 < 70 → ✗
- M2: 45 < 70 AND 48 > 70 → ✗ (48 not > 70)
- M3: 78 < 70 → ✗
- M4: 56 < 70 AND 67 > 70 → ✗ (67 not > 70)
- M5: 32 < 70 AND 87 > 70 → ✓
- M6: 67 < 70 AND 51 > 70 → ✗

Output:
```
+---------+
| MatchID |
+---------+
| M5 |
+---------+
```

SQL Query:
```sql
SELECT MatchID, MatchDate
FROM MATCH_DETAILS
WHERE (FirstTeamID = 1 AND FirstTeamScore > SecondTeamScore)
OR (SecondTeamID = 1 AND SecondTeamScore > FirstTeamScore);
```

Explanation: Team 1 wins when:
- It is the First Team and `FirstTeamScore > SecondTeamScore`, OR
- It is the Second Team and `SecondTeamScore > FirstTeamScore`.

Working through the data:
- M1: FirstTeamID=1, 90 > 86 → ✓ (Team 1 wins)
- M3: FirstTeamID=1, 78 > 56 → ✓ (Team 1 wins)
- M5: FirstTeamID=1, 32 > 87 → ✗ (Team 1 loses)

Output:
```
+---------+------------+
| MatchID | MatchDate |
+---------+------------+
| M1 | 2018-07-17 |
| M3 | 2018-07-19 |
+---------+------------+
```

SQL Query:
```sql
SELECT MatchID
FROM MATCH_DETAILS
WHERE (FirstTeamID = 2 AND FirstTeamScore <= SecondTeamScore)
OR (SecondTeamID = 2 AND SecondTeamScore <= FirstTeamScore);
```

Explanation: Team 2 did NOT win when:
- It is the First Team and `FirstTeamScore <= SecondTeamScore` (lost or drew), OR
- It is the Second Team and `SecondTeamScore <= FirstTeamScore` (lost or drew).

Working through the data:
- M1: SecondTeamID=2, SecondTeamScore=86, FirstTeamScore=90 → 86 <= 90 → ✓ (Team 2 lost)
- M4: FirstTeamID=2, FirstTeamScore=56, SecondTeamScore=67 → 56 <= 67 → ✓ (Team 2 lost)
- M6: FirstTeamID=2, FirstTeamScore=67, SecondTeamScore=51 → 67 <= 51 → ✗ (Team 2 won)

Output:
```
+---------+
| MatchID |
+---------+
| M1 |
| M4 |
+---------+
```

Step 1: Rename the table TEAM to T_DATA:
```sql
ALTER TABLE TEAM RENAME TO T_DATA;
```

Step 2: Rename column TeamID to T_ID:
```sql
ALTER TABLE T_DATA CHANGE TeamID T_ID INT;
```

Step 3: Rename column TeamName to T_NAME:
```sql
ALTER TABLE T_DATA CHANGE TeamName T_NAME VARCHAR(30);
```

Explanation:
- `ALTER TABLE ... RENAME TO` is used to rename a table.
- `ALTER TABLE ... CHANGE old_column_name new_column_name datatype` is used to rename a column in MySQL. The data type must be re-specified even if it is not changing.

Step 1: First insert the handkerchief details into the UNIFORM table:
```sql
INSERT INTO UNIFORM (UCode, UName, Colour)
VALUES (7, 'Handkerchief', 'Red');
```

Step 2: Then insert the size and price into the COST table:
```sql
INSERT INTO COST (UCode, Size, Price)
VALUES (7, 'M', 100);
```

Explanation: Since `UniformCode` in COST is a Foreign Key referencing UNIFORM, the entry must first be made in the UNIFORM (parent) table before inserting into COST (child) table. This maintains referential integrity.

SQL Query:
```sql
ALTER TABLE COST
ADD CONSTRAINT fk_ucode
FOREIGN KEY (UCode) REFERENCES UNIFORM(UCode);
```

Explanation:
- A Foreign Key constraint is added on the `UCode` column of the COST table, referencing the `UCode` (primary key) of the UNIFORM table.
- This ensures referential integrity: data can be inserted into COST only if the corresponding `UCode` already exists in the UNIFORM table.
- If someone tries to insert a record in COST with a `UCode` that does not exist in UNIFORM, MySQL will throw an error and reject the insertion.

SQL Query:
```sql
ALTER TABLE UNIFORM
MODIFY UName VARCHAR(30) NOT NULL;
```

Explanation:
- The `NOT NULL` constraint on `UName` ensures that a new `UCode` (row) can only be inserted into the UNIFORM table if a valid (non-null) `UName` is also provided.
- This prevents entries where `UCode` is assigned without a corresponding item name.

SQL Query:
```sql
ALTER TABLE COST
ADD CONSTRAINT chk_price CHECK (Price > 0);
```

Explanation:
- The `CHECK` constraint is added on the `Price` column of the COST table.
- It enforces the business rule that `Price` must always be greater than 0.
- Any attempt to insert or update a row with `Price <= 0` will be rejected by MySQL.

SQL Query:
```sql
CREATE TABLE Product
(
PCode VARCHAR(5) NOT NULL,
PName VARCHAR(30) NOT NULL,
UPrice DECIMAL(10,2) NOT NULL,
Manufacturer VARCHAR(30),
CONSTRAINT pk_product PRIMARY KEY (PCode)
);
```

Explanation of Data Types:
- `PCode`: `VARCHAR(5)` — short product code like P01, P02.
- `PName`: `VARCHAR(30)` — product name string.
- `UPrice`: `DECIMAL(10,2)` — price with decimal precision.
- `Manufacturer`: `VARCHAR(30)` — manufacturer name.
- `PCode` is the Primary Key (unique, not null).

Primary Key: `PCode`

Justification:
- `PCode` uniquely identifies each product in the table (P01, P02, P03, P04, P05, P06 — all distinct).
- A Primary Key must be unique and NOT NULL.
- `PName` cannot be a primary key as values like 'Toothpaste' and 'Soap' repeat.
- `Manufacturer` also repeats (e.g., Dove appears twice).
- Therefore, `PCode` is the most appropriate Primary Key.

SQL Query:
```sql
SELECT PCode, PName, UPrice
FROM Product
ORDER BY PName DESC, UPrice ASC;
```

Explanation:
- `ORDER BY PName DESC` sorts the result in descending alphabetical order of product name.
- `UPrice ASC` is the secondary sort: when two products have the same name (e.g., both 'Toothpaste' or both 'Soap'), they are sorted in ascending order of price.

Expected Output:
```
+-------+----------------+--------+
| PCode | PName | UPrice |
+-------+----------------+--------+
| P01 | Washing Powder | 120 |
| P02 | Toothpaste | 54 |
| P04 | Toothpaste | 65 |
| P06 | Shampoo | 245 |
| P03 | Soap | 25 |
| P05 | Soap | 38 |
+-------+----------------+--------+
```

SQL Query:
```sql
ALTER TABLE Product
ADD Discount DECIMAL(10,2) DEFAULT 0;
```

Explanation:
- `ALTER TABLE` is used to modify the structure of an existing table.
- `ADD Discount DECIMAL(10,2)` adds a new column named `Discount` with a decimal data type.
- `DEFAULT 0` sets the initial value to 0 for all existing rows.

SQL Query:
```sql
UPDATE Product
SET Discount = CASE
WHEN UPrice > 100 THEN UPrice * 0.10
ELSE 0
END;
```

Alternative using two UPDATE statements:
```sql
UPDATE Product SET Discount = UPrice * 0.10 WHERE UPrice > 100;
UPDATE Product SET Discount = 0 WHERE UPrice <= 100;
```

Explanation:
- For products with `UPrice > 100` (Washing Powder: 120, Shampoo: 245), Discount = 10% of UPrice.
- For all other products, Discount = 0.
- `CASE` expression handles conditional logic within a single `UPDATE` statement.

SQL Query:
```sql
UPDATE Product
SET UPrice = UPrice * 1.12
WHERE Manufacturer = 'Dove';
```

Explanation:
- The `UPDATE` statement modifies existing data.
- `UPrice = UPrice * 1.12` increases the price by 12% (multiplying by 1.12 adds 12% to the original price).
- The `WHERE` clause restricts the update to only those rows where `Manufacturer = 'Dove'` (Soap P05 and Shampoo P06).

SQL Query:
```sql
SELECT Manufacturer, COUNT(*) AS TotalProducts
FROM Product
GROUP BY Manufacturer;
```

Explanation:
- `GROUP BY Manufacturer` groups all rows by manufacturer name.
- `COUNT(*)` counts the number of products in each group.

Expected Output:
```
+--------------+---------------+
| Manufacturer | TotalProducts |
+--------------+---------------+
| Surf | 1 |
| Colgate | 1 |
| Lux | 1 |
| Pepsodent | 1 |
| Dove | 2 |
+--------------+---------------+
```

Given Query: `SELECT PName, avg(UPrice) FROM Product GROUP BY Pname;`

Concept: `GROUP BY PName` groups rows with the same product name, and `AVG(UPrice)` calculates the average price for each group.

Working:
- `Washing Powder`: only P01 → AVG = 120.0000
- `Toothpaste`: P02 (54) and P04 (65) → AVG = $(54+65)/2 = 59.5000$
- `Soap`: P03 (25) and P05 (38) → AVG = $(25+38)/2 = 31.5000$
- `Shampoo`: only P06 → AVG = 245.0000

Output:
```
+----------------+------------+
| PName | avg(UPrice)|
+----------------+------------+
| Washing Powder | 120.0000 |
| Toothpaste | 59.5000 |
| Soap | 31.5000 |
| Shampoo | 245.0000 |
+----------------+------------+
```

Given Query: `SELECT DISTINCT Manufacturer FROM Product;`

Concept: `DISTINCT` eliminates duplicate values and displays each manufacturer only once.

Working: From the table, manufacturers are: Surf, Colgate, Lux, Pepsodent, Dove, Dove → after DISTINCT: Surf, Colgate, Lux, Pepsodent, Dove.

Output:
```
+--------------+
| Manufacturer |
+--------------+
| Surf |
| Colgate |
| Lux |
| Pepsodent |
| Dove |
+--------------+
```

Given Query: `SELECT COUNT(DISTINCT PName) FROM Product;`

Concept: `COUNT(DISTINCT PName)` counts the number of distinct (unique) product names.

Working: PNames in the table: Washing Powder, Toothpaste, Soap, Toothpaste, Soap, Shampoo.
Distinct PNames: Washing Powder, Toothpaste, Soap, Shampoo → 4 distinct names.

Output:
```
+----------------------+
| COUNT(DISTINCT PName)|
+----------------------+
| 4 |
+----------------------+
```

Given Query: `SELECT PName, MAX(UPrice), MIN(UPrice) FROM Product GROUP BY PName;`

Concept: `GROUP BY PName` groups rows by product name. `MAX(UPrice)` and `MIN(UPrice)` return the maximum and minimum price within each group.

Working:
- `Washing Powder`: only 120 → MAX=120, MIN=120
- `Toothpaste`: 54, 65 → MAX=65, MIN=54
- `Soap`: 25, 38 → MAX=38, MIN=25
- `Shampoo`: only 245 → MAX=245, MIN=245

Output:
```
+----------------+------------+------------+
| PName | MAX(UPrice)| MIN(UPrice)|
+----------------+------------+------------+
| Washing Powder | 120 | 120 |
| Toothpaste | 65 | 54 |
| Soap | 38 | 25 |
| Shampoo | 245 | 245 |
+----------------+------------+------------+
```

SQL Query:
```sql
ALTER TABLE INVENTORY
ADD Discount DECIMAL(10,2) DEFAULT 0;
```

Explanation:
- `ALTER TABLE INVENTORY` modifies the existing INVENTORY table.
- `ADD Discount DECIMAL(10,2)` adds a new column `Discount` with decimal data type to store discount values.
- `DEFAULT 0` initialises the discount to 0 for all existing rows.

SQL Query:
```sql
UPDATE INVENTORY
SET Discount = CASE
WHEN Model = 'LXI' THEN 0
WHEN Model = 'VXI' THEN Price * 0.10
ELSE Price * 0.12
END;
```

Alternative using separate UPDATE statements:
```sql
-- (i) No discount for LXI
UPDATE INVENTORY SET Discount = 0 WHERE Model = 'LXI';

-- (ii) 10% discount for VXI
UPDATE INVENTORY SET Discount = Price * 0.10 WHERE Model = 'VXI';

-- (iii) 12% discount for all other models
UPDATE INVENTORY SET Discount = Price * 0.12
WHERE Model NOT IN ('LXI', 'VXI');
```

Explanation:
- The `CASE` expression handles all three conditions in a single `UPDATE` statement.
- `Model = 'LXI'` → Discount = 0
- `Model = 'VXI'` → Discount = 10% of Price
- All other models → Discount = 12% of Price

SQL Query:
```sql
SELECT CarName
FROM INVENTORY
WHERE FuelType = 'Petrol' AND Price = (
SELECT MAX(Price)
FROM INVENTORY
WHERE FuelType = 'Petrol'
);
```

Alternative using ORDER BY and LIMIT:
```sql
SELECT CarName
FROM INVENTORY
WHERE FuelType = 'Petrol'
ORDER BY Price DESC
LIMIT 1;
```

Explanation:
- The subquery `SELECT MAX(Price) FROM INVENTORY WHERE FuelType = 'Petrol'` finds the maximum price among all petrol cars.
- The outer query retrieves the `CarName` of the car with that maximum price and fuel type 'Petrol'.

SQL Query:
```sql
SELECT AVG(Discount) AS AverageDiscount,
SUM(Discount) AS TotalDiscount
FROM INVENTORY
WHERE CarName = 'Baleno';
```

Explanation:
- `AVG(Discount)` calculates the average of the Discount column for all Baleno cars.
- `SUM(Discount)` calculates the total (sum) of all discounts for Baleno cars.
- The `WHERE CarName = 'Baleno'` clause filters only Baleno cars.

SQL Query:
```sql
SELECT COUNT(*) AS CarsWithNoDiscount
FROM INVENTORY
WHERE Discount = 0 OR Discount IS NULL;
```

Explanation:
- `COUNT(*)` counts the total number of rows satisfying the condition.
- `Discount = 0` covers cars with explicitly zero discount (LXI model cars).
- `Discount IS NULL` covers any cars where discount has not been set.
- Together, these conditions identify all cars with no discount.