SQL SELECT STATEMENT – PRACTICE SET 1

WHAT WE LEARN

In this video, we discuss about:

1. Select * vs Select specific column names
2. Alias (AS)
3. Computed columns
4. ORDER BY
5. Execution Order

Challenge question

Question 1:

Select only the product_name, unit_price, and category from the products table. Rename the columns to Product, Price, and Type.

Answer

SELECT product_name AS product, unit_price AS price , category AS type from products;

Question 2

Show every employee’s full name as one column called full_name and their job title as position. Only from the Engineering department.

Answer

SELECT 
CONCAT(first_name,' ', last_name) AS full_name,
job_title AS position
FROM employees
WHERE department="engineering";

Question 3

From the products table, show the product name, unit price, and calculate the price after a 15% discount. Call it discounted_price.

Answer

SELECT product_name, 
unit_price,
unit_price*0.15 AS discounted_price
FROM products;

Question 4

From the customers table, show first_name as First, last_name as Last, city as Location, and signup_date as Member Since.

Answer

SELECT
first_name AS first,
last_name AS last,
city AS Location,
signup_date AS member_since
FROM customers;

Question 5

Show every employee’s full name, salary, and their annual salary. Order by annual salary from highest to lowest.

Answer

SELECT  
CONCAT_WS(" ", first_name, last_name) AS full_name,
salary,
salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC


Note: concat_ws means concat with separtor: concat_ws(separator, expression 1,expression 2..)

Question 6

From products, show the product name, unit price, cost price, and calculate the total cost per product. Call it line_total. Order by line_total descending.

Answer

SELECT product_name,
unit_price,
cost_price,
unit_price * cost_price AS line_total
FROM products
ORDER BY line_total desc;

Question 7

From order_items, show the item_id, quantity, unit_price, the total line value as line_total, and build a label column that reads item_id: line total — for example 5: 299.99. Call it summary.

Answer

SELECT 
item_id,
quantity,
unit_price,
unit_price* quantity as line_total,
CONCAT(item_id, ': ', unit_price * quantity) AS summary
 FROM order_items;

Question 8

From employees, select full name, department, and monthly salary. Add a column tax_estimate which is 20% of salary. Order by tax_estimate descending — using the alias, not the expression.

Answer

SELECT 
CONCAT_WS(" ", first_name, last_name) AS full_name,
department,
salary AS monthly_salary,
salary*0.20 as tax_estimate
FROM employees
ORDER BY tax_estimate DESC
;

Question 9

A junior dev wrote SELECT * FROM products and pushed it to production. Write the corrected query that shows only product_id, product_name, category, unit_price, and stock_qty — keeping cost_price out of the result.

SELECT 
product_id,
product_name,
category,
unit_price
stock_qty
 FROM products;

Question 10

From order_items, show item_id, unit_price, quantity, the line total, the line total including 20% VAT as line_total_vat, and the VAT amount alone as vat_amount.

Answer

SELECT 
item_id,
unit_price,
quantity,
unit_price * quantity as line_total,
unit_price*quantity * 1.20 as line_total_vat,
unit_price * quantity * 0.20 as vat_amount
FROM order_items;

Question 11

Write a single SELECT from employees that shows: full name, department, job title, annual salary, annual salary after a 10% raise as new_salary, and the raise amount alone as raise_amount. Order by raise_amount descending,

Answer

SELECT
concat_ws(" ",first_name, last_name) as full_name,
department,
job_title,
salary as annual_salary,
salary* 1.10 as new_salary,
salary* 0.10 as raise_amount
FROM employees
order by raise_amount desc

Submit your answer on the comment box.