Write a part1.sql script that creates the school database.
Also, add procedures to the script that allow you to import and export data for each table from/to a file with a .csv extension.
The csv file separator is specified as a parameter of each procedure.
In each of the tables, enter at least 5 records. As you progress through the task, you will need new data to test all of your choices. This new data needs to be added to this script as well.
If csv files were used to add data to the tables, they must also be uploaded to the GIT repository.
Create a part2.sql script, in which, in addition to what is described below, add test queries/calls for each item.
Parameters: nickname of the person being checked, checker's nickname, task name, P2P check status, time.
If the status is "start", add a record in the Checks table (use today's date).
Add a record in the P2P table.
If the status is "start", specify the record just added as a check, otherwise specify the check with the unfinished P2P step.
Parameters: nickname of the person being checked, task name, Verter check status, time.
Add a record to the Verter table (as a check specify the check of the corresponding task with the latest (by time) successful P2P step)
3) Write a trigger: after adding a record with the "start" status to the P2P table, change the corresponding record in the TransferredPoints table
The record is considered correct if:
- The number of XP does not exceed the maximum available for the task being checked
- The Check field refers to a successful check If the record does not pass the check, do not add it to the table.
Create a part3.sql script, in which you should include the following procedures and functions (consider as procedures all tasks that do not specify that they are functions).
Peer's nickname 1, Peer's nickname 2, number of transferred peer points.
The number is negative if peer 2 received more points from peer 1.
Output example:
Peer1 | Peer2 | PointsAmount |
---|---|---|
Aboba | Amogus | 5 |
Amogus | Sus | -2 |
Sus | Aboba | 0 |
2) Write a function that returns a table of the following form: user name, name of the checked task, number of XP received
Include in the table only tasks that have successfully passed the check (according to the Checks table).
One task can be completed successfully several times. In this case, include all successful checks in the table.
Output example:
Peer | Task | XP |
---|---|---|
Aboba | C8 | 800 |
Aboba | CPP3 | 750 |
Amogus | DO5 | 175 |
Sus | A4 | 325 |
Function parameters: day, for example 12.05.2022.
The function returns only a list of peers.
Output the result sorted by the change in the number of points.
Output format: peer's nickname, change in the number of peer points
Output example:
Peer | PointsChange |
---|---|
Aboba | 8 |
Amogus | 1 |
Sus | -3 |
5) Calculate the change in the number of peer points of each peer using the table returned by the first function from Part 3
Output the result sorted by the change in the number of points.
Output format: peer's nickname, change in the number of peer points
Output example:
Peer | PointsChange |
---|---|
Aboba | 8 |
Amogus | 1 |
Sus | -3 |
If there is the same number of checks for some tasks in a certain day, output all of them.
Output format: day, task name
Output example:
Day | Task |
---|---|
12.05.2022 | A1 |
17.04.2022 | CPP3 |
23.12.2021 | C5 |
7) Find all peers who have completed the whole given block of tasks and the completion date of the last task
Procedure parameters: name of the block, for example “CPP”.
The result is sorted by the date of completion.
Output format: peer's name, date of completion of the block (i.e. the last completed task from that block)
Output example:
Peer | Day |
---|---|
Sus | 23.06.2022 |
Amogus | 17.05.2022 |
Aboba | 12.05.2022 |
You should determine it according to the recommendations of the peer's friends, i.e. you need to find the peer with the greatest number of friends who recommend to be checked by him.
Output format: peer's nickname, nickname of the checker found
Output example:
Peer | RecommendedPeer |
---|---|
Aboba | Sus |
Amogus | Aboba |
Sus | Aboba |
- Started only block 1
- Started only block 2
- Started both
- Have not started any of them
A peer is considered to have started a block if he has at least one check of any task from this block (according to the Checks table)
Procedure parameters: name of block 1, for example SQL, name of block 2, for example A.
Output format: percentage of those who started only the first block, percentage of those who started only the second block, percentage of those who started both blocks, percentage of those who did not started any of them
Output example:
StartedBlock1 | StartedBlock2 | StartedBothBlocks | DidntStartAnyBlock |
---|---|---|---|
20 | 20 | 5 | 55 |
Also determine the percentage of peers who have ever failed a check on their birthday.
Output format: percentage of peers who have ever successfully passed a check on their birthday, percentage of peers who have ever failed a check on their birthday
Output example:
SuccessfulChecks | UnsuccessfulChecks |
---|---|
60 | 40 |
Procedure parameters: names of tasks 1, 2 and 3.
Output format: list of peers
I. e. How many tasks have to be done, based on entry conditions, to get access to the current one.
Output format: task name, number of preceding tasks
Output example:
Task | PrevCount |
---|---|
CPP3 | 7 |
A1 | 9 |
C5 | 1 |
13) Find "lucky" days for checks. A day is considered "lucky" if it has at least N consecutive successful checks
Parameters of the procedure: the N number of consecutive successful checks .
The time of the check is the start time of the P2P step.
Successful consecutive checks are the checks with no unsuccessful checks in between.
The amount of XP for each of these checks must be at least 80% of the maximum.
Output format: list of days
Output format: peer's nickname, amount of XP
Output example:
Peer | XP |
---|---|
Amogus | 15000 |
Procedure parameters: time, N number of times .
Output format: list of peers
Procedure parameters: N number of days , M number of times .
Output format: list of peers
For each month, count how many times people born in that month came to campus during the whole time (we'll call this the total number of entries).
For each month, count the number of times people born in that month have come to campus before 12:00 in all time (we'll call this the number of early entries).
For each month, count the percentage of early entries to campus relative to the total number of entries.
Output format: month, percentage of early entries
Output example:
Month | EarlyEntries |
---|---|
January | 15 |
February | 35 |
March | 45 |
For this part of the task, you need to create a separate database, in which to create the tables, functions, procedures, and triggers needed to test the procedures.
Add the creation and filling of this database, as well as the written procedures, to the part4.sql file.