This PHP, MySQL, and SQL Online Test Separates Good From Bad Hires

Consider the following table:

CREATE TABLE users (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL
);

The following query is used to paginate the table:

SELECT * FROM users ORDER BY username LIMIT 5 OFFSET 15;

Select all the true statements about this method of pagination.

You're maintaining the PHP back-end of a Tree Planting website.

The registerForEvent function is called by the server when interested people fill a form to register for a tree planting event.

The register function it calls performs the registration logic.

Consider the code for the registerForEvent function:

function registerForEvent($name, $seedBagsNeeded)
{
    $sanitizedName = filter_var($name, FILTER_SANITIZE_SPECIAL_CHARS);
    $filteredBagsNeeded = filter_var($seedBagsNeeded, FILTER_VALIDATE_INT);
    register($sanitizedName, $filteredBagsNeeded);
}

What is correct about the filtering and sanitization done in the code above?

App usage data are kept in the following table:

TABLE sessions
  id INTEGER PRIMARY KEY,
  userId INTEGER NOT NULL,
  duration DECIMAL NOT NULL

Write a query that selects userId and average session duration for each user who has more than one session.

See the example case for more details.

The following data definition defines an organization's employee hierarchy.

An employee is a manager if any other employee has their managerId set to this employee's id. That means John is a manager if at least one other employee has their managerId set to John's id.

TABLE employees
  id INTEGER NOT NULL PRIMARY KEY
  managerId INTEGER
  name VARCHAR(30) NOT NULL
  FOREIGN KEY (managerId) REFERENCES employees(id)

Write a query that selects only the names of employees who are not managers.

See the example case for more details.

Given the following data definition, write a query that returns the number of students whose first name is John.

TABLE students
   id INTEGER PRIMARY KEY,
   firstName VARCHAR(30) NOT NULL,
   lastName VARCHAR(30) NOT NULL

A table containing the students enrolled in a yearly course has incorrect data in records with ids between 20 and 100 (inclusive).

TABLE enrollments
  id INTEGER NOT NULL PRIMARY KEY
  year INTEGER NOT NULL
  studentId INTEGER NOT NULL

Write a query that updates the field 'year' of every faulty record to 2015.

Information about pets is kept in two separate tables:

TABLE dogs
  id INTEGER NOT NULL PRIMARY KEY,
  name VARCHAR(50) NOT NULL

TABLE cats
  id INTEGER NOT NULL PRIMARY KEY,
  name VARCHAR(50) NOT NULL

Write a query that select all distinct pet names.

See the example case for more details.

A new social network site has the following data tables:

users
id	name	sex
1	Ann	null
2	Steve	m
3	Mary	f
4	Brenda	f

friends
user1	user2
1	2
1	3
2	3

Select data that will be returned by the following SQL query:

SELECT users.name, COUNT(*) as count FROM users
LEFT JOIN friends
ON users.id = friends.user1 OR users.id = friends.user2
WHERE users.sex = 'f'
GROUP BY users.id, users.name;

Fill in the blanks so that the trigger item_delete inserts name from item table to the item_archive table, after a row from the table item is deleted.

CREATE TABLE item (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  quantity INT NOT NULL
);

CREATE TABLE item_archive (
  archive_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);

DELIMITER $$
CREATE ________ item_delete ___________ ON item 
FOR EACH ROW
BEGIN
  INSERT INTO item_archive(name) VALUES (__________);
END;
$$
DELIMITER ;

See the example case for more details.

Consider the following DDL statement:

CREATE TABLE registrations (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  create_date DATE NOT NULL,
  last_activity DATE NOT NULL
);

Fill in the blanks so that the following select returns all registrations that were last active at least 30 days after being created.

SELECT name, create_date, last_activity FROM registrations
  WHERE _____________ >= __________(__________, INTERVAL 30 DAY);

See the example case for more details.

Each item in a web shop belongs to a seller. To ensure service quality, each seller has a rating.

The data are kept in the following two tables:

TABLE sellers
  id INTEGER PRIMARY KEY,
  name VARCHAR(30) NOT NULL,
  rating INTEGER NOT NULL

TABLE items
  id INTEGER PRIMARY KEY,
  name VARCHAR(30) NOT NULL,
  sellerId INTEGER
  FOREIGN KEY (sellerId) REFERENCES sellers(id)

Write a query that selects the item name and the name of its seller for each item that belongs to a seller with a rating greater than 4. The query should return the name of the item as the first column and name of the seller as the second column.

See the example case for more details.

Implement a groupByOwners function that:

• Accepts an associative array containing the file owner name for each file name.
• Returns an associative array containing an array of file names for each owner name, in any order.

For example, for associative array ["Input.txt" => "Randy", "Code.py" => "Stan", "Output.txt" => "Randy"] the groupByOwners function should return ["Randy" => ["Input.txt", "Output.txt"], "Stan" => ["Code.py"]].

Implement the unique_names function. When passed two arrays of names, it will return an array containing the names that appear in either or both arrays. The returned array should have no duplicates.

For example, calling unique_names(['Ava', 'Emma', 'Olivia'], ['Olivia', 'Sophia', 'Emma']) should return ['Emma', 'Olivia', 'Ava', 'Sophia'] in any order.

Implement the function findRoots to find the roots of the quadratic equation: ax² + bx + c = 0. The function should return an array containing both roots in any order. If the equation has only one solution, the function should return that solution as both elements of the array. The equation will always have at least one solution.

The roots of the quadratic equation can be found with the following formula:

For example, findRoots(2, 10, 8) should return [-1, -4] or [-4, -1] as the roots of the equation 2x² + 10x + 8 = 0 are -1 and -4.

A company needs a stored procedure that will insert a new user with an appropriate type.

Consider the following tables:

TABLE userTypes
  id INTEGER NOT NULL PRIMARY KEY,
  type VARCHAR(50) NOT NULL

TABLE users
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(50) NOT NULL,
  userTypeId INTEGER NOT NULL,
  FOREIGN KEY (userTypeId) REFERENCES userTypes(id)

Finish the insertUser procedure so that it inserts a user, with these requirements:

• id is auto incremented.
• email is equal to the email parameter.
• userTypeId is the id of the userTypes row whose type attribute is equal to the type parameter.

See the example case for more details.

The user interface contains two types of user input controls: TextInput, which accepts all texts and NumericInput, which accepts only digits.

Implement the class TextInput that contains:

• Public function add($text) - adds the given text to the current value.
• Public function getValue() - returns the current value (string).

Implement the class NumericInput that:

• Inherits from TextInput.
• Overrides the add method so that each non-numeric text is ignored.

For example, the following code should output '10':

$input = new NumericInput();
$input->add('1');
$input->add('a');
$input->add('0');
echo $input->getValue();

As part of a data processing pipeline, complete the implementation of the make_pipeline method:

• The method should accept a variable number of functions, and it should return a new function that accepts one parameter $arg.
• The returned function should call the first function in the make_pipeline with the parameter $arg, and call the second function with the result of the first function.
• The returned function should continue calling each function in the make_pipeline in order, following the same pattern, and return the value from the last function.

For example, make_pipeline(function($x) { return $x * 3; }, function($x) { return $x + 1; }, function($x) { return $x / 2; }) then calling the returned function with 3 should return 5.

A thesaurus contains words and synonyms for each word. Below is an example of a data structure that defines a thesaurus:

array("buy" => array("purchase"), "big" => array("great", "large"))

Implement the function getSynonyms, which accepts a word as a string and returns all synonyms for that word in JSON format, as in the example below.

For example, the call $thesaurus->getSynonyms("big") should return:

'{"word":"big","synonyms":["great", "large"]}'

while a call with a word that doesn't have synonyms, like $thesaurus->getSynonyms("agelast") should return:

'{"word":"agelast","synonyms":[]}'

The following two tables are used to define users and their respective roles:

TABLE users
  id INTEGER NOT NULL PRIMARY KEY,
  userName VARCHAR(50) NOT NULL

TABLE roles
  id INTEGER NOT NULL PRIMARY KEY,
  role VARCHAR(20) NOT NULL

The users_roles table should contain the mapping between each user and their roles. Each user can have many roles, and each role can have many users.

Modify the provided SQL create table statement so that:

• Only users from the users table can exist within users_roles.
• Only roles from the roles table can exist within users_roles.
• A user can only have a specific role once.

See the example case for more details.

The LeagueTable class tracks the score of each player in a league. After each game, the player records their score with the recordResult function. 

The player's rank in the league is calculated using the following logic:

1. The player with the highest score is ranked first (rank 1). The player with the lowest score is ranked last.
2. If two players are tied on score, then the player who has played the fewest games is ranked higher.
3. If two players are tied on score and number of games played, then the player who was first in the list of players is ranked higher.

Implement the playerRank function that returns the player at the given rank.

For example:

$table = new LeagueTable(array('Mike', 'Chris', 'Arnold'));
$table->recordResult('Mike', 2);
$table->recordResult('Mike', 3);
$table->recordResult('Arnold', 5);
$table->recordResult('Chris', 5);
echo $table->playerRank(1);

All players have the same score. However, Arnold and Chris have played fewer games than Mike, and as Chris is before Arnold in the list of players, he is ranked first. Therefore, the code above should display "Chris".

An application for testing programmers contains an initial question version and newer question versions, represented by the following schema:

TABLE questions
  id INTEGER NOT NULL PRIMARY KEY
  name VARCHAR(50) NOT NULL
  childId INTEGER
  FOREIGN KEY (childId) REFERENCES questions(id)

The latest question version has childId set to NULL, other question versions will have childId set to the next version. Each question version can be a child of only one other question version.

Finish the findLatestVersion stored procedure so that it returns the latest question version.

See the example case for more details.

An insurance company maintains records of sales made by its employees. Each employee is assigned to a state. States are grouped under regions. The following tables contain the data:

TABLE regions
  id INTEGER PRIMARY KEY
  name VARCHAR(50) NOT NULL

TABLE states
  id INTEGER PRIMARY KEY
  name VARCHAR(50) NOT NULL
  regionId INTEGER NOT NULL
  FOREIGN KEY (regionId) REFERENCES regions(id)

TABLE employees
  id INTEGER PRIMARY KEY
  name VARCHAR(50) NOT NULL
  stateId INTEGER NOT NULL
  FOREIGN KEY (stateId) REFERENCES states(id)

TABLE sales
  id INTEGER PRIMARY KEY
  amount INTEGER NOT NULL
  employeeId INTEGER NOT NULL
  FOREIGN KEY (employeeId) REFERENCES employees(id)  

Management requires a comparative region sales analysis report.

Write a query that returns:

• The region name.
• Average sales per employee for the region (Average sales = Total sales made for the region / Number of employees in the region).
• The difference between the average sales of the region with the highest average sales, and the average sales per employee for the region (average sales to be calculated as explained above).

Employees can have multiple sales. A region with no sales should be also returned. Use 0 for average sales per employee for such a region when calculating the 2nd and the 3rd column.

See the example case for more details.