Diademata

 Implement the missing code, denoted by ellipses. You may not modify the pre-existing code.

You've been dreaming about becoming a famous hacker all your life, and now it's time to make your dreams come true! You decided to start by finding a website that has some vulnerability, and you just found a doozy. This particular website has an open database users that contains information about the people using it. What's more, it stores the queries performed on this table on the client side, which makes it super simple to hack them.

The users table contains the following columns:

id - The unique user's ID;

login - The unique user's login;

name - The user's name;

type - The user's role type (which can be "user", "admin", "moderator", etc.).

The query you have access to gathers some information about the users who have the "user" type. You don't want to get caught, so you want to carefully update it so that the query will return the records of all existing types.

Your task is to update the existing query. Note: You should add something to the query, but don't rewrite it.

Your company is planning to expand internationally very soon. You have been tasked with preparing a report on foreign markets and potential competitors.

After some investigation, you've created a database containing a foreignCompetitors table, which has the following structure:

competitor: the name of the competitor;

country: the country in which the competitor is operating.

In your report, you need to include the number of competitors per country and an additional row at the bottom that contains a summary: ("Total:", total_number_of_competitors)

Given the foreignCompetitors table, compose the resulting table with two columns: country and competitors. The first column should contain the country name, and the second column should contain the number of competitors in this country. The table should be sorted by the country names in ascending order. In addition, it should have an extra row at the bottom with the summary, as described above.

You have a table soccer_team that contains information about the players in your favorite soccer team. This table has the following structure:

id: the unique ID of the player;

first_name: the first name of the player;

surname: the last name of the player;

player_number: the number that the player wears (the number is guaranteed to be unique).

Create a semicolon-separated list of all the players, sorted by their numbers, and put this list in a table under a column called players. The information about each player should have the following format: first_name surname #number.

You are an avid traveler and you've visited so many countries that when people ask you where you've been, you can't even remember all of them! Luckily, every time you travel somewhere you write down the trip information in your diary. Now you want to get a list of all the different countries that you have visited using the information in your diary.

The diary is represented as a table diary, which has the following columns:

id: the unique ID of the trip;

travel_date: the date the trip began;

country: the country to which you traveled.

Given this diary table, create a semicolon-separated list of all the distinct countries you've visited, sorted lexicographically, and put the list in a table that has a single countries column.

You want to expand your movie collection, but you don't really have any preferences so you're not sure where to start. After some consideration, you decide that you should start by finding more movies from award-winning directors whose movies you already own and who have shot a movie somewhat recently.

To find the directors whose movies you might want to consider watching in the first place, you've created a database of all the films you already own and stored them in a moviesInfo table, which has the following structure:

title: the title of the movie;

director: the director of this movie;

year: the year the movie was released;

oscars: the number of the Academy Awards this movie received.

Given the moviesInfo table, compose the list of directors you should consider watching more movies from. The resulting table should have a single director column and contain the names of film directors such that:

they shot movies after the year 2000;

the total number of Oscar awards these movies received is more than 2.

The table should be sorted by the directors' names in ascending order.

You are curious about the geographical distribution of CodeSignal users, so you have created a list of countries along with the number of registered users from each. Your task now is to calculate the number of users on each continent.

The information about the countries is stored in a table countries, which has 3 columns:

country: the name of the country;

continent: the name of the continent where the country is located;

users: the number of users registered on CodeSignal in the country.

The answer should be a table with 2 columns, continent and users, sorted by the number of users in decreasing order. 

You recently started working in the IT department of a large store. You were put in charge of the inventory database availableItems, which has the following structure:

id: unique item ID;

item_name: the name of the item;

item_type: the type of the item.

Note that it is possible for items that are of different types to have the same names.

One of the most common operations performed on this database is querying the number of specific items available at the store. Since the database is quite large, queries of this type can take up too much time. You have decided to solve this problem by creating a new table that contains item counts for all available items.

Given the availableItems table, compose a results table that has the following three columns: item_name, item_type and item_count, containing the names of the items, their types, and the amount of those items, respectively. The table should be sorted in ascending order by item type, with items of the same type sorted in ascending order by their names.

Your friend wants to become a professional tour guide and travel all around the world. In pursuit of this dream, she enrolled in tour guide school. The professors in this school turned out to be very demanding, and one of them gave your friend a difficult assignment that she has to finish over the weekend.

Here's the task: Given a list of countries, your friend should calculate the average population and total population of all the countries in the list. To help her, you have decided to write a function that will calculate the required values for any number of countries. The countries table in which the countries are stored has the following structure:

name: the name of the country;

continent: the continent on which the country is situated;

population: the population of the country.

Your task is to return a new table that contains the number of countries in the given list, along with their average and total population, in columns titled number, average and total.

You are managing a small newspaper subscription service. Anyone who uses it can subscribe to a large number of different newspapers for a full year or just a half year.

The information about subscriptions is stored in the full_year and half_year tables, which have the following structures:

full_year:

id: the unique subscription ID;

newspaper: the newspaper's name;

subscriber: the name of the subscriber.

half_year

id: the unique subscription ID;

newspaper: the newspaper's name;

subscriber: the name of the subscriber.

Given the full_year and half_year tables, compose the result as follows: The resulting table should have one column subscriber that contains all the distinct names of anyone who is subscribed to a newspaper with the word Daily in its name. The table should be sorted in ascending order by the subscribers' first names.