azarasi tategotoazarasi

Table: Schools

school_id is the primary key for this table. This table contains information about the capacity of some schools. The capacity is the maximum number of students the school can accept.

Table: Exam

score is the primary key for this table. Each row in this table indicates that there are student_count students that got at least score points in the exam. The data in this table will be logically correct, meaning a row recording a higher score will have the same or smaller student_count compared to a row recording a lower score. More formally, for every two rows i and j in the table, if scorei > scorej then student_counti <= student_countj.

Every year, each school announces a minimum score requirement that a student needs to apply to it. The school chooses the minimum score requirement based on the exam results of all the students:

They want to ensure that even if every student meeting the requirement applies, the school can accept everyone.
They also want to maximize the possible number of students that can apply.
They must use a score that is in the Exam table.

Write an SQL query to report the minimum score requirement for each school. If there are multiple score values satisfying the above conditions, choose the smallest one. If the input data is not enough to determine the score, report -1.

Return the result table in any order.

Table Person:

id is the primary key for this table. Each row of this table contains the name of a person and their phone number. Phone number will be in the form ‘xxx-yyyyyyy’ where xxx is the country code (3 characters) and yyyyyyy is the phone number (7 characters) where x and y are digits. Both can contain leading zeros.

Table Country:

country_code is the primary key for this table. Each row of this table contains the country name and its code. country_code will be in the form ‘xxx’ where x is digits.

Table Calls:

There is no primary key for this table, it may contain duplicates. Each row of this table contains the caller id, callee id and the duration of the call in minutes. caller_id != callee_id

A telecommunications company wants to invest in new countries. The company intends to invest in the countries where the average call duration of the calls in this country is strictly greater than the global average call duration.

Write an SQL query to find the countries where this company can invest.

Return the result table in any order.

The query result format is in the following example.

Table: Sales

(sale_date, fruit) is the primary key for this table. This table contains the sales of “apples” and “oranges” sold each day.

Write an SQL query to report the difference between the number of apples and oranges sold each day.

Return the result table ordered by sale_date.

The query result format is in the following example.

Table: Calls

This table does not have a primary key, it may contain duplicates. This table contains the duration of a phone call between from_id and to_id. from_id != to_id

Write an SQL query to report the number of calls and the total call duration between each pair of distinct persons (person1, person2) where person1 < person2.

Return the result table in any order.

The query result format is in the following example.

Table: Product

product_id is the primary key of this table. Each row of this table indicates the name and the price of each product.

Table: Sales

This table has no primary key, it can have repeated rows. product_id is a foreign key to the Product table. Each row of this table contains some information about one sale.

Write an SQL query that reports the products that were only sold in the spring of 2019. That is, between 2019-01-01 and 2019-03-31 inclusive.

Return the result table in any order.

The query result format is in the following example.

Table: Tree

id is the primary key column for this table. Each row of this table contains information about the id of a node and the id of its parent node in a tree. The given structure is always a valid tree.

Each node in the tree can be one of three types:

• “Leaf”: if the node is a leaf node.
• “Root”: if the node is the root of the tree.
• “Inner”: If the node is neither a leaf node nor a root node.

Write an SQL query to report the type of each node in the tree.

Return the result table ordered by id in ascending order.

The query result format is in the following example.

Table: Products

product_id is the primary key for this table. Each row in this table indicates the product’s price in 3 different stores: store1, store2, and store3. If the product is not available in a store, the price will be null in that store’s column.

Write an SQL query to rearrange the Products table so that each row has (product_id, store, price). If a product is not available in a store, do not include a row with that product_id and store combination in the result table.

Return the result table in any order.

The query result format is in the following example.

Table: Employees

employee_id is the primary key for this table. Each row of this table indicates the name of the employee whose ID is employee_id.

Table: Salaries

employee_id is the primary key for this table. Each row of this table indicates the salary of the employee whose ID is employee_id.

Write an SQL query to report the IDs of all the employees with missing information. The information of an employee is missing if:

• The employee’s name is missing, or
• The employee’s salary is missing.

Return the result table ordered by employee_id in ascending order.

The query result format is in the following example.

Table: Numbers

num is the primary key for this table. Each row of this table shows the frequency of a number in the database.

The median is the value separating the higher half from the lower half of a data sample.

Write an SQL query to report the median of all the numbers in the database after decompressing the Numbers table. Round the median to one decimal point.

(player_id, event_date) is the primary key of this table. This table shows the activity of players of some games. Each row is a record of a player who logged in and played a number of games (possibly 0) before logging out on someday using some device.

Write an SQL query to report for each player and date, how many games played so far by the player. That is, the total number of games played by the player until that date. Check the example for clarity.

Return the result table in any order.

The query result format is in the following example.