Getting the number of orders & total costs

In this exercise, you will JOIN two tables to get the total number of orders for each person and the sum of prices of all orders. You have to join the Person and History tables on the primary and foreign keys to get all required information.

Questo esercizio fa parte del corso

Hierarchical and Recursive Queries in SQL Server

Visualizza il corso

Istruzioni dell'esercizio

COUNT() the number of orders and alias it as Orders.
SUM() the total price of all orders and alias it as Costs.
Join the Person and History tables.
Aggregate the information on ID using GROUP BY.

Esercizio pratico interattivo

Prova a risolvere questo esercizio completando il codice di esempio.

INSERT INTO Person  
VALUES (1, 'Andrew', 'Anderson','Union Ave 10','New York','1986-12-30');
INSERT INTO Person 
VALUES (2, 'Sam', 'Smith','Flushing Ave 342','New York','1986-12-30');

INSERT INTO History VALUES ( 1, 'IPhone XS', 1000, 1);
INSERT INTO History VALUES ( 2, 'MacBook Pro', 1800, 1);
INSERT INTO History VALUES ( 5, 'IPhone XR', 600, 2);
INSERT INTO History VALUES ( 6, 'IWatch 4', 400, 1);

SELECT 
    Person.ID,
    -- Count the number of orders
    ___(___) as ___,
    -- Add the total price of all orders
    ___(___) as ___
FROM Person
	-- Join the tables Person and History on their IDs
	___ History 
	ON Person.___ = History.___
-- Aggregate the information on the ID
___ ___ Person.___;

Modifica ed esegui il codice