PHP coding
Task 1
- 1
- 2
- 3
- divisible by three
- 4
- 5
- divisible by five
- 6
- divisible by three
- 7
- 8
- 9
- divisible by three
- 10
- divisible by five
- 11
- 12
- divisible by three
- 13
- 14
- 15
- divisible by three and five
- 16
- 17
- 18
- divisible by three
- 19
- 20
- divisible by five
- 21
- divisible by three
- 22
- 23
- 24
- divisible by three
- 25
- divisible by five
- 26
- 27
- divisible by three
- 28
- 29
- 30
- divisible by three and five
Task 2
27
Task 3
Array
(
[Learning PHP] => John Smith
[Understanding relational databases] => Mary Little
[Freelancers] => Robin Round
[I love LISP] => Mary Little
[Python for dummies] => John Smith
)
Array
(
[John Smith] => Array
(
[0] => Learning PHP
[1] => Python for dummies
)
[Mary Little] => Array
(
[0] => Understanding relational databases
[1] => I love LISP
)
[Robin Round] => Array
(
[0] => Freelancers
)
)
Task 4
TextInput: 1a0
NumericInput: 10
Task 5
Before
"yabadabadoo" does not contain "yaba"
After
"yabadabadoo" contains "yaba"
Task 6
*
* *
* * *
* * * *
* * * * *
* * * * * *
* * * * *
* * * *
* * *
* *
*
Task 7
Array
(
[0] => 0
[1] => 10
[2] => 80
[3] => 67
[4] => 60
[5] => 89
[6] => 91
[7] => 56
[8] => 45
[9] => 30
[10] => 95
[11] => 83
)
Biggest nr in array: 95
Task 8
| 1 | 2 | 3 | 4 | 5 |
| 1 | 1 | 2 | 3 | 4 | 5 |
| 2 | 2 | 4 | 6 | 8 | 10 |
| 3 | 3 | 6 | 9 | 12 | 15 |
| 4 | 4 | 8 | 12 | 16 | 20 |
| 5 | 5 | 10 | 15 | 20 | 25 |
Task 9
Before
Place 1 - Sue
Place 1 (tie) - Tommy
Place 1 (tie) - John
Place 1 (tie) - Mary
After
Place 1 - Sue (19.333333333333)
Place 2 - Tommy (18.333333333333)
Place 3 - John (17.333333333333)
Place 4 - Mary (15)
MySQL/MariaDB
Task 1
CREATE TABLE `books` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL DEFAULT '',
`total_amount` int(11) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`modified_at` datetime DEFAULT NULL,
`deleted_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
INSERT INTO `books` (`id`, `title`, `total_amount`, `created_at`, `modified_at`, `deleted_at`)
VALUES
(1,'Mysteries of Java',NULL,'2023-01-09 12:27:59','2023-01-09 12:27:59',NULL),
(2,'The Big Rewrite”, “Design patterns',NULL,'2023-01-09 12:27:59','2023-01-09 12:27:59',NULL),
(3,'Design patterns',NULL,'2023-01-09 12:27:59','2023-01-09 12:27:59',NULL),
(4,'Inversion of control',NULL,'2023-01-09 12:27:59','2023-01-09 12:27:59',NULL),
(5,'Why my code smells?',NULL,'2023-01-09 12:27:59','2023-01-09 12:27:59',NULL);
CREATE TABLE `customers` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`firstname` varchar(50) DEFAULT '',
`lastname` varchar(50) NOT NULL DEFAULT '',
`phone` varchar(20) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`modified_at` datetime DEFAULT NULL,
`deleted_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
INSERT INTO `customers` (`id`, `firstname`, `lastname`, `phone`, `created_at`, `modified_at`, `deleted_at`)
VALUES
(1,'Mary','Sue','5012345','2023-01-09 12:27:59','2023-01-09 12:27:59',NULL),
(2,'Alan','Smith','5123456','2023-01-09 12:27:59','2023-01-09 12:27:59',NULL),
(3,'Joe','Goodspeed','5234567','2023-01-09 12:27:59','2023-01-09 12:27:59',NULL),
(4,'Nicky','Jones','5345678','2023-01-09 12:27:59','2023-01-09 12:27:59',NULL);
CREATE TABLE `rentals` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`customer_id` int(11) NOT NULL,
`book_id` int(11) NOT NULL,
`borrowed_at` date NOT NULL,
`returned_at` date DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`modified_at` datetime DEFAULT NULL,
`deleted_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
INSERT INTO `rentals` (`id`, `customer_id`, `book_id`, `borrowed_at`, `returned_at`, `created_at`, `modified_at`, `deleted_at`)
VALUES
(1,1,1,'2020-02-14',NULL,'2023-01-09 12:27:59','2023-01-09 12:27:59',NULL),
(2,1,3,'2020-03-02','2020-03-04','2023-01-09 12:31:35','2023-01-09 12:31:35',NULL),
(3,2,2,'2020-02-17','2020-02-25','2023-01-09 12:32:26','2023-01-09 12:32:26',NULL),
(4,3,3,'2020-03-02',NULL,'2023-01-09 12:33:34','2023-01-09 12:33:34',NULL),
(5,3,4,'2020-03-04',NULL,'2023-01-09 12:33:34','2023-01-09 12:33:34',NULL),
(6,4,5,'2020-03-07',NULL,'2023-01-09 12:33:34','2023-01-09 12:33:34',NULL);
Task 2
1) List the number of jobs available in the employees table
[
{
"job_id": "AC_ACCOUNT",
"count": "1"
},
{
"job_id": "AC_MGR",
"count": "1"
},
{
"job_id": "AD_ASST",
"count": "1"
},
{
"job_id": "AD_PRES",
"count": "1"
},
{
"job_id": "AD_VP",
"count": "2"
},
{
"job_id": "FI_ACCOUNT",
"count": "5"
},
{
"job_id": "FI_MGR",
"count": "1"
},
{
"job_id": "HR_REP",
"count": "1"
},
{
"job_id": "IT_PROG",
"count": "5"
},
{
"job_id": "MK_MAN",
"count": "1"
},
{
"job_id": "MK_REP",
"count": "1"
},
{
"job_id": "PR_REP",
"count": "1"
},
{
"job_id": "PU_CLERK",
"count": "5"
},
{
"job_id": "PU_MAN",
"count": "1"
},
{
"job_id": "SA_MAN",
"count": "5"
},
{
"job_id": "SA_REP",
"count": "30"
},
{
"job_id": "SH_CLERK",
"count": "20"
},
{
"job_id": "ST_CLERK",
"count": "20"
},
{
"job_id": "ST_MAN",
"count": "5"
}
]2) Maximum salary of an employee working as a Programmer (“IT_PROG”)
{
"job_id": "IT_PROG",
"salary": "9000.00"
}3) Average salary and number of employees working the department 90
{
"count": "3",
"salary": "19333.333333"
}4) Number of employees with the same job
[
{
"job_id": "AD_VP",
"count": "2"
},
{
"job_id": "FI_ACCOUNT",
"count": "5"
},
{
"job_id": "IT_PROG",
"count": "5"
},
{
"job_id": "PU_CLERK",
"count": "5"
},
{
"job_id": "SA_MAN",
"count": "5"
},
{
"job_id": "SA_REP",
"count": "30"
},
{
"job_id": "SH_CLERK",
"count": "20"
},
{
"job_id": "ST_CLERK",
"count": "20"
},
{
"job_id": "ST_MAN",
"count": "5"
}
]5) Get the difference between the highest and lowest salaries
[
{
"job_id": "AC_ACCOUNT",
"min_salary": "8300.00",
"max_salary": "8300.00",
"difference_between_salaries": "0.00"
},
{
"job_id": "AC_MGR",
"min_salary": "12000.00",
"max_salary": "12000.00",
"difference_between_salaries": "0.00"
},
{
"job_id": "AD_ASST",
"min_salary": "4400.00",
"max_salary": "4400.00",
"difference_between_salaries": "0.00"
},
{
"job_id": "AD_PRES",
"min_salary": "24000.00",
"max_salary": "24000.00",
"difference_between_salaries": "0.00"
},
{
"job_id": "AD_VP",
"min_salary": "17000.00",
"max_salary": "17000.00",
"difference_between_salaries": "0.00"
},
{
"job_id": "FI_ACCOUNT",
"min_salary": "6900.00",
"max_salary": "9000.00",
"difference_between_salaries": "2100.00"
},
{
"job_id": "FI_MGR",
"min_salary": "12000.00",
"max_salary": "12000.00",
"difference_between_salaries": "0.00"
},
{
"job_id": "HR_REP",
"min_salary": "6500.00",
"max_salary": "6500.00",
"difference_between_salaries": "0.00"
},
{
"job_id": "IT_PROG",
"min_salary": "4200.00",
"max_salary": "9000.00",
"difference_between_salaries": "4800.00"
},
{
"job_id": "MK_MAN",
"min_salary": "13000.00",
"max_salary": "13000.00",
"difference_between_salaries": "0.00"
},
{
"job_id": "MK_REP",
"min_salary": "6000.00",
"max_salary": "6000.00",
"difference_between_salaries": "0.00"
},
{
"job_id": "PR_REP",
"min_salary": "10000.00",
"max_salary": "10000.00",
"difference_between_salaries": "0.00"
},
{
"job_id": "PU_CLERK",
"min_salary": "2500.00",
"max_salary": "3100.00",
"difference_between_salaries": "600.00"
},
{
"job_id": "PU_MAN",
"min_salary": "11000.00",
"max_salary": "11000.00",
"difference_between_salaries": "0.00"
},
{
"job_id": "SA_MAN",
"min_salary": "10500.00",
"max_salary": "14000.00",
"difference_between_salaries": "3500.00"
},
{
"job_id": "SA_REP",
"min_salary": "6100.00",
"max_salary": "11500.00",
"difference_between_salaries": "5400.00"
},
{
"job_id": "SH_CLERK",
"min_salary": "2500.00",
"max_salary": "4200.00",
"difference_between_salaries": "1700.00"
},
{
"job_id": "ST_CLERK",
"min_salary": "2100.00",
"max_salary": "3600.00",
"difference_between_salaries": "1500.00"
},
{
"job_id": "ST_MAN",
"min_salary": "5800.00",
"max_salary": "8200.00",
"difference_between_salaries": "2400.00"
}
]6) Manager ID and the salary of the lowest-paid employee for that manager
[
{
"manager_id": "0",
"min_salary": "24000.00"
},
{
"manager_id": "100",
"min_salary": "5800.00"
},
{
"manager_id": "101",
"min_salary": "4400.00"
},
{
"manager_id": "102",
"min_salary": "9000.00"
},
{
"manager_id": "103",
"min_salary": "4200.00"
},
{
"manager_id": "108",
"min_salary": "6900.00"
},
{
"manager_id": "114",
"min_salary": "2500.00"
},
{
"manager_id": "120",
"min_salary": "2200.00"
},
{
"manager_id": "121",
"min_salary": "2100.00"
},
{
"manager_id": "122",
"min_salary": "2200.00"
},
{
"manager_id": "123",
"min_salary": "2500.00"
},
{
"manager_id": "124",
"min_salary": "2500.00"
},
{
"manager_id": "145",
"min_salary": "7000.00"
},
{
"manager_id": "146",
"min_salary": "7000.00"
},
{
"manager_id": "147",
"min_salary": "6200.00"
},
{
"manager_id": "148",
"min_salary": "6100.00"
},
{
"manager_id": "149",
"min_salary": "6200.00"
},
{
"manager_id": "201",
"min_salary": "6000.00"
},
{
"manager_id": "205",
"min_salary": "8300.00"
}
]7) Department ID and the total salary payable in each department
[
{
"department_id": "0",
"total_salary": "7000.00"
},
{
"department_id": "10",
"total_salary": "4400.00"
},
{
"department_id": "20",
"total_salary": "19000.00"
},
{
"department_id": "30",
"total_salary": "24900.00"
},
{
"department_id": "40",
"total_salary": "6500.00"
},
{
"department_id": "50",
"total_salary": "156400.00"
},
{
"department_id": "60",
"total_salary": "28800.00"
},
{
"department_id": "70",
"total_salary": "10000.00"
},
{
"department_id": "80",
"total_salary": "304500.00"
},
{
"department_id": "90",
"total_salary": "58000.00"
},
{
"department_id": "100",
"total_salary": "51600.00"
},
{
"department_id": "110",
"total_salary": "20300.00"
}
]8) Get the average salary for each job ID excluding programmer
[
{
"job_id": "AC_ACCOUNT",
"average_salary": "8300.000000"
},
{
"job_id": "AC_MGR",
"average_salary": "12000.000000"
},
{
"job_id": "AD_ASST",
"average_salary": "4400.000000"
},
{
"job_id": "AD_PRES",
"average_salary": "24000.000000"
},
{
"job_id": "AD_VP",
"average_salary": "17000.000000"
},
{
"job_id": "FI_ACCOUNT",
"average_salary": "7920.000000"
},
{
"job_id": "FI_MGR",
"average_salary": "12000.000000"
},
{
"job_id": "HR_REP",
"average_salary": "6500.000000"
},
{
"job_id": "MK_MAN",
"average_salary": "13000.000000"
},
{
"job_id": "MK_REP",
"average_salary": "6000.000000"
},
{
"job_id": "PR_REP",
"average_salary": "10000.000000"
},
{
"job_id": "PU_CLERK",
"average_salary": "2780.000000"
},
{
"job_id": "PU_MAN",
"average_salary": "11000.000000"
},
{
"job_id": "SA_MAN",
"average_salary": "12200.000000"
},
{
"job_id": "SA_REP",
"average_salary": "8350.000000"
},
{
"job_id": "SH_CLERK",
"average_salary": "3215.000000"
},
{
"job_id": "ST_CLERK",
"average_salary": "2785.000000"
},
{
"job_id": "ST_MAN",
"average_salary": "7280.000000"
}
]9) Total salary, maximum, minimum, average salary of employees (job ID wise), for department ID 90 only
[
{
"department_id": "90",
"job_id": "AD_PRES",
"min_salary": "24000.00",
"max_salary": "24000.00",
"average_salary": "24000.000000",
"total_salary": "24000.00"
},
{
"department_id": "90",
"job_id": "AD_VP",
"min_salary": "17000.00",
"max_salary": "17000.00",
"average_salary": "17000.000000",
"total_salary": "34000.00"
}
]10) Get the job ID and maximum salary of the employees where maximum salary is greater than or equal to $4000
[
{
"job_id": "AC_ACCOUNT",
"max_salary": "8300.00"
},
{
"job_id": "AC_MGR",
"max_salary": "12000.00"
},
{
"job_id": "AD_ASST",
"max_salary": "4400.00"
},
{
"job_id": "AD_PRES",
"max_salary": "24000.00"
},
{
"job_id": "AD_VP",
"max_salary": "17000.00"
},
{
"job_id": "FI_ACCOUNT",
"max_salary": "9000.00"
},
{
"job_id": "FI_MGR",
"max_salary": "12000.00"
},
{
"job_id": "HR_REP",
"max_salary": "6500.00"
},
{
"job_id": "IT_PROG",
"max_salary": "9000.00"
},
{
"job_id": "MK_MAN",
"max_salary": "13000.00"
},
{
"job_id": "MK_REP",
"max_salary": "6000.00"
},
{
"job_id": "PR_REP",
"max_salary": "10000.00"
},
{
"job_id": "PU_MAN",
"max_salary": "11000.00"
},
{
"job_id": "SA_MAN",
"max_salary": "14000.00"
},
{
"job_id": "SA_REP",
"max_salary": "11500.00"
},
{
"job_id": "SH_CLERK",
"max_salary": "4200.00"
},
{
"job_id": "ST_MAN",
"max_salary": "8200.00"
}
]11) Get the average salary for all departments employing more than 10 employees
[
{
"department_id": "50",
"average_salary": "3475.555556",
"employee_count": "45"
},
{
"department_id": "80",
"average_salary": "8955.882353",
"employee_count": "34"
}
]