> For the complete documentation index, see [llms.txt](https://dailyjournal.gitbook.io/solutions/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://dailyjournal.gitbook.io/solutions/hackerrank-solutions/certify/sql-basic.md).

# SQL (Basic)

## #1 SQL: Merit Rewards

On the basis of merit, a company decides to promote some of its employees in its HR division at the end of the quarter because of their high performance. Write a query to find the employee IDs along with the names of all its employees who work in the HR department who earned a bonus of 5000 dollars or more in the last quarter.

There are two tables in the database: *`employee_information`*and *`last_quarter_bonus`.* Their primary keys are *`employee_ID`.*

#### Schema

There are 2 tables: **`employee_information`**, **`last_quarter_bonus`**.

#### employee\_information

<table><thead><tr><th width="151.33333333333331">Name</th><th width="111">Type</th><th>Description</th></tr></thead><tbody><tr><td>employee_ID</td><td>INTEGER</td><td>The employee ID of the employee. This is the primary key.</td></tr><tr><td>name</td><td>STRING</td><td>The name of the employee.</td></tr><tr><td>division</td><td>STRING</td><td>The division in which the employee works.</td></tr></tbody></table>

#### last\_quarter\_bonus

<table><thead><tr><th width="154.33333333333331">Name</th><th width="115">Type</th><th>Description</th></tr></thead><tbody><tr><td>employee_ID</td><td>INTEGER</td><td>The employee ID of the employee. This is the primary key.</td></tr><tr><td>bonus</td><td>INTEGER</td><td>The bonus earned by the employee in last quarter (in dollars).</td></tr></tbody></table>

**Note:** Both tables contain data about all employees working in the compan&#x79;*.*

{% tabs %}
{% tab title="SQL Server" %}

```sql
SELECT ei.employee_ID, ei.name
FROM employee_information ei JOIN last_quarter_bonus lqbonus 
ON ei.employee_ID = lqbonus.employee_ID
WHERE ei.division = 'HR'
AND lqbonus.bonus >= 5000;
```

{% endtab %}
{% endtabs %}

## #2 SQL: Profitable Stocks

A stock is considered profitable if the predicted price is strictly greater than the current price. Write a query to find the stock\_codes of all the stocks which are profitable based on this definition. Sort the output in ascending order.

There are two tables in the database: *`price_today`*&#x61;nd *`price_tomorrow`.* Their primary keys are *`stock_code`*.

#### Schema

There are 2 tables: **`price_today`**, **`price_tomorrow`**.

#### **price\_today**

<table><thead><tr><th width="142.33333333333331">Name</th><th width="116">Type</th><th>Description</th></tr></thead><tbody><tr><td>stock_code</td><td>STRING</td><td>The stock code for this stock. This is the primary key.</td></tr><tr><td>price</td><td>INTEGER</td><td>Today's price of the stock.</td></tr></tbody></table>

#### price\_tomorrow

<table><thead><tr><th width="147.33333333333331">Name</th><th width="131">Type</th><th>Description</th></tr></thead><tbody><tr><td>stock_code</td><td>STRING</td><td>The stock code for this stock. This is the primary key.</td></tr><tr><td>price</td><td>INTEGER</td><td>Predicted Price tomorrow for the stock</td></tr></tbody></table>

**Note:** Both tables contain all stocks listed on the stock exchange.

{% tabs %}
{% tab title="SQL Server" %}

```sql
SELECT price_today.stock_code
FROM price_today JOIN price_tomorrow
ON price_today.stock_code = price_tomorrow.stock_code
WHERE price_tomorrow.price > price_today.price;
```

{% endtab %}
{% endtabs %}