SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE Customers( customer_id int PRIMARY KEY NOT NULL, first_name varchar(50) NULL, last_name varchar(50) NULL, city varchar(50) NULL ) ; CREATE TABLE Sales ( sales_id INT PRIMARY KEY NOT NULL, sales_date DATE, customer_id INT REFERENCES Customers (customer_id), product_name VARCHAR (50), quantity INT CHECK (quantity > 0), price DECIMAL (7, 2) ); insert into Customers values(1,'marco','rossi','milan'); insert into Customers values(2,'john','doe','london'); insert into Customers values(3,'mark','smith','liverpool'); insert into Customers values(4,'mary','jane','london'); insert into Sales values(1,'2018-09-01',1,'xyz',5,1.5); insert into Sales values(2,'2018-09-01',2,'abc',2,2); insert into Sales values(3,'2018-09-02',3,'xyz',10,1.5); insert into Sales values(4,'2018-09-02',4,'abc',4,2); insert into Sales values(5,'2018-09-03',1,'abc',2,2.2); insert into Sales values(6,'2018-09-03',2,'xyz',3,1.5); select sales_date, year(sales_date) as 'Year of Sale', UPPER(product_name) as PRODUCTS, price, quantity, price*int(quantity) as revenue, from Sales order by PRODUCTS asc