---
# System prepended metadata

title: Candidate Assessment
tags: [Interview]

---

# SQL Assessment

### Identifying Vehicles for Disposal

#### Overview

Fresh Cars has an extensive fleet that requires ongoing management. Specifically, when a vehicle's Certificate of Entitlement (COE) expires, or if a vehicle is laid up as per the director's decision, staff members must initiate the disposal procedure. Develop a system that enumerates vehicles poised for disposal, enabling Fresh Cars employees to access this information with precision.

#### Database Schema

**Vehicle**
| Id | CompanyName | VehicleNumber | Make | Model | COEExpiryDate | IsLayup |
|:--:|:-----------:|:-------------:|:----:|:-----:|:-------------:|:-------:|
| 1  | Fresh Cars Pte Ltd | SOE0001X | Toyota | Altis | 2024-06-06 | No |
| 2  | Fresh Cars Pte Ltd | SOE0002X | Honda | Jazz | 2026-06-06 | No |
| 3  | Fresh Cars Pte Ltd | SOE0003X | Mazda | 3 | 2025-06-06 | Yes |
| 4  | Fresh Cars Pte Ltd | SOE0004X | Mazda | 2 | 2028-06-06 | No |

Please write an SQL query to retrieve this data and save it as `dispose.sql`.

### Disposal Task Management

The director requires a system to manage disposal-related tasks, ensuring staff members are informed about the specific tasks to be completed during the disposal process. This module should facilitate tracking of each task's progress, including the staff member's name and the completion date.

#### Database Schema

**DisposeTask**
| Id | TaskName |
|:---:|:---------:|
| 1  | Cancel Insurance |
| 2  | Remove GPS and Camera |
| 3  | Take Pictures |
| 4  | Handover Vehicle |
| 5  | Payment Received from Exporter |

Design a database schema (including the existing Vehicle table) to meet these requirements. Write an SQL query to display the disposal progress for a specific vehicle.

Document your database schema design in a file named `task.txt` / `task.md` (or any other preferred format), and save the SQL query as `task.sql`.

**Sample Output**
| Id | TaskName | Assign To | Remarks | Done Date |
|:--:|:--------:|:---------:|:-------:|:---------:|
| 1 | Cancel Insurance | Alvin | cancel insurance early | 2022-02-03 |
| 2 | Remove GPS and Camera ||||
| 3 | Take Pictures | Alvin | Car pictures taken | 2022-02-04 |
| 4 | Handover Vehicle ||||
| 5 | Payment Received from Exporter ||||
