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_informationand last_quarter_bonus. Their primary keys are employee_ID.

Schema

There are 2 tables: employee_information, last_quarter_bonus.

employee_information

Name	Type	Description
employee_ID	INTEGER	The employee ID of the employee. This is the primary key.
name	STRING	The name of the employee.
division	STRING	The division in which the employee works.

last_quarter_bonus

Name	Type	Description
employee_ID	INTEGER	The employee ID of the employee. This is the primary key.
bonus	INTEGER	The bonus earned by the employee in last quarter (in dollars).

Note: Both tables contain data about all employees working in the company.

SQL Server

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;

#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_todayand price_tomorrow. Their primary keys are stock_code.

Schema

There are 2 tables: price_today, price_tomorrow.

price_today

Name	Type	Description
stock_code	STRING	The stock code for this stock. This is the primary key.
price	INTEGER	Today's price of the stock.

price_tomorrow

Name	Type	Description
stock_code	STRING	The stock code for this stock. This is the primary key.
price	INTEGER	Predicted Price tomorrow for the stock

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

SQL Server

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;