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.

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.

Question 3

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

Question 4

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

Question 5

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

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.

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.

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.

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.

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.

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, NULLs last.

Submit your answer on the comment box.