---
# System prepended metadata

title: Developer Exams 2021/6
tags: [bbo]

---

###### tags: `bbo`

# Developer Exams 2021/6

## How to submit the your answer.

At first you check out from this repository, and then make your branch by your name (ex. nabepero)
https://github.com/smart-study-inc/dev-exam-202106

Create PR from that branch, you need to include Q1, Q2 and Q3 answers.

## Q1. Create WEB Application [Score 50] 

- Implement web system refring the specification.
- Requirement for exam
    - The code should be PSR-12compliant
    - Should make unit test code
    - Should make validation each field
    - No need to use bootstrap.

### Screen transition diagram

![](https://i.imgur.com/x2PGVt9.png)

### UI Mock
https://ss-exams-202106.bss.design/students/index.html

### ER

![](https://i.imgur.com/iA12vgz.png)

### Definition of DB table 

#### students

| column | type        | not null |
| ------ | ----------- | -------- |
| id     | integer     | YES      |
| name   | varchar(20) | YES      |

#### scores

| column     | type    | not null |
| ---------- | ------- | -------- |
| id         | integer | YES      |
| student_id | integer | YES      |
| subject_id | integer | YES      |
| score      | integer | YES      |

#### subjects

| column | type        | not null |
| ------ | ----------- | -------- |
| id     | integer     | YES      |
| name   | varchar(20) | YES      |

##### seeder of subjects

| ID  | Name |
| --- | ---- |
| 1   | 算数 |
| 2   | 国語 |
| 3   | 英語 |

※ Need to make seeder file


### Specification

#### Student List

![](https://i.imgur.com/F71Xi0E.png)

##### Table

| Field        | Table    | How to edit                                             |
| ------------ | -------- | ------------------------------------------------------- |
| ID           | students | id                                                      |
| Student Name | students | name                                                    |
| Total Score  | scores   | Calculate total score of all subjects from scores table |

※ sort by studnet id

##### Events
| Event               | CBehaivor                   |
| ------------------- | --------------------------- |
| Click Create button | Jump to Create Student page |
| Click Edit button   | Jump to Edit Student page   |

#### Create / Edit student

![](https://i.imgur.com/zodwHj6.png)

※ Create dropbox from subjects table.
※ Subject fields are fixed 3 items (It does not need add new fields and remove)

##### Validation Rule

| Field        | Required | Rule                    |
| ------------ | -------- | ----------------------- |
| Student name | YES      | length range is 1 〜 20 |
| Subject      | YES      |                         |
| Score        | YES      | 0〜100                  |

##### Evnets

| Events              | Behaivor |
| ------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Click Submit button | At first, validate field. if it has an error, it will be shown error messages. If no error, store/update a record ,and then jump to Student list page (it will be show `Success` as flash message.) |
| Click Back link     | Jump to Student list page |


## Q2. SQL

Create q2-1.txt, q2-2.txt and q2-3.txt, and then includes those files to your PR.

### 1. Write the SQL to create the table below [5]

Table name is `schools` 

| Column  | Type         | Not null |                |
| ------- | ------------ | -------- | -------------- |
| id      | integer      | YES      | auto increment |
| name    | varchar(20)  | YES      |                |
| address | varchar(250) |          |                |
| tel     | varchar(20)  |          |                |
| type    | integer      |          | default 1      |

※You should submit sql (file name is q2-1.txt).

### 2. Extract records with math scores of 80 or more [5]
※Use database when created Q1.
※You should submit sql (file name is q2-2.txt).

**Sample reuslt**
![](https://i.imgur.com/KHFmRIq.png)


### 3. Min, Max and Average score for each subject(need to get subject name from subjects table) [10]
※Use database when created Q1.
※You should submit sql (file name is q2-3.txt).

**Sample reuslt**
![](https://i.imgur.com/5i8GVGT.png)


## Q3. Enhancement WEB Application  [Score 30]

If you have a time, you can challenge extra questions.
But you need to clear Q1 and Q2

![](https://i.imgur.com/DNWe1vn.png)

- Add Login feature
- Add Subject List/Create/Edit page



