Using the table INVENTORY from CARSHOWROOM database, write sql queries for the following:
a) Convert the CarMake to uppercase if its value starts with the letter ‘B’.
b) If the length of the car’s model is greater than 4 then fetch the substring starting from position 3 till the

Question

Using the table INVENTORY from CARSHOWROOM database, write sql queries for the following:
a) Convert the CarMake to uppercase if its value starts with the letter ‘B’.
b) If the length of the car’s model is greater than 4 then fetch the substring starting from position 3 till the end from attribute Model.

Accepted Answer

> [!TLDR]
> Use `IF()` (or `CASE`) with `UPPER()` and `SUBSTRING()`/`LENGTH()` to transform column values conditionally, applied to the real `INVENTORY` table (Table 9.9) of the `CARSHOWROOM` database — the real name column is `CarName` (there is no `CarMake` column in this schema).

## The real INVENTORY table (Table 9.9)

| CarId | CarName | Model |
|---|---|---|
| D001 | Dzire | LXI |
| D002 | Dzire | VXI |
| B001 | Baleno | Sigma1.2 |
| B002 | Baleno | Delta1.2 |
| E001 | EECO | 5 STR STD |
| E002 | EECO | CARE |
| S001 | SWIFT | LXI |
| S002 | SWIFT | VXI |

---

## **(a)** Convert the CarName to uppercase if its value starts with the letter 'B'

```sql
SELECT
    IF(CarName LIKE 'B%', UPPER(CarName), CarName) AS CarName
FROM INVENTORY;
```

**Key logic:** `CarName LIKE 'B%'` tests whether the name starts with 'B'. Only **Baleno** (B001, B002) starts with 'B' among the real car names (Dzire, Baleno, EECO, SWIFT); the other three names are left unchanged.

**Output**

| CarId | CarName |
|---|---|
| D001 | Dzire |
| D002 | Dzire |
| B001 | BALENO |
| B002 | BALENO |
| E001 | EECO |
| E002 | EECO |
| S001 | SWIFT |
| S002 | SWIFT |

---

## **(b)** If the length of the car's model is greater than 4 then fetch the substring starting from position 3 till the end from attribute Model

```sql
SELECT
    CarId,
    IF(LENGTH(Model) > 4, SUBSTRING(Model, 3), Model) AS Model
FROM INVENTORY;
```

**Key logic:** `LENGTH(Model)` is checked against 4. `LXI`, `VXI` (length 3) and `CARE` (length 4, not *greater than* 4) stay unchanged. `Sigma1.2`, `Delta1.2` (length 8) and `5 STR STD` (length 9, counting the two spaces) all exceed 4 and get truncated from position 3 onward.

**Output** …

Q.Using the table INVENTORY from CARSHOWROOM database, write sql queries for the following:

Concept understanding — Conditional String Functions

Conditional String Functions – A First Look

The everyday intuition

What it does precisely

Why it matters

A concrete example (no numbers) …

The real INVENTORY table (Table 9.9)

(a) Convert the CarName to uppercase if its value starts with the letter 'B'

(b) If the length of the car's model is greater than 4 then fetch the substring starting from position 3 till the end from attribute Model

The real INVENTORY table (Table 9.9)

(a) Convert the CarName to uppercase if its value starts with the letter 'B'

(b) If the length of the car's model is greater than 4 then fetch the substring starting from position 3 till the end from attribute Model

CarId	CarName	Model
D001	Dzire	LXI
D002	Dzire	VXI
B001	Baleno	Sigma1.2
B002	Baleno	Delta1.2
E001	EECO	5 STR STD
E002	EECO	CARE
S001	SWIFT	LXI
S002	SWIFT	VXI

Q.Using the table INVENTORY from CARSHOWROOM database, write sql queries for the following:

The real INVENTORY table (Table 9.9)

(a) Convert the CarName to uppercase if its value starts with the letter 'B'

(b) If the length of the car's model is greater than 4 then fetch the substring starting from position 3 till the end from attribute Model

🎓Unlock everything free for 14 days

Unlock everything free for 14 days