###### 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


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

### ER


### 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


##### 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


※ 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**

### 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**

## Q3. Enhancement WEB Application [Score 30]

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


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