Kata SQL

SQL Basics: Simple UNION ALL

For this challenge you need to create a UNION statement, there are two tables ussales and eusales the parent company tracks each sale at its respective location in each table, you must all filter the sale price so it only returns rows with a sale greater than 50.00. You have been tasked with combining that data for future analysis. Order by location (US before EU), then by id.

(us/eu)sales table schema

  • id
  • name
  • price
  • card_name
  • card_number
  • transaction_date

resultant table schema

  • location (EU for eusales and US for ussales)
  • id
  • name
  • price (greater than 50.00)
  • card_name
  • card_number
  • transaction_date

NOTE: Your solution should use pure SQL. Ruby is used within the test cases to do the actual testing.

Hint: The syntax for alter table is “ALTER TABLE ADD COLUMN”. Also, be careful to use ‘’ instead of “”.

Solution:

ALTER TABLE ussales
ADD COLUMN location varchar(255)
DEFAULT 'US';

ALTER TABLE eusales
ADD COLUMN location varchar(255)
DEFAULT 'EU';
SELECT * FROM ussalesWHERE price > 50.00
UNION ALL
SELECT * FROM eusales
WHERE price > 50.00
ORDER BY location desc, id;

Link

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Isabelle

Isabelle

In love with telling stories with data