This is written using Microsoft SQL Server however a lot of this can carry over just fine to other SQL variants with little change. I am also using SQL Management Studio 2016 when writing this query. This can be found here.
Sub queries come handy when you need to return data from multiple tables but need that data Summed up or some form of calculation done on them. This isn’t the only scenario where these are useful but in this example that’s what I will be doing.
The example below does not require you to create any tables as everything is done in memory.
Creating our tables:
DECLARE @Users TABLE ( UserID INT IDENTITY(1,1) PRIMARY KEY, FirstName VARCHAR(25), LastName VARCHAR(25) ); DECLARE @items TABLE ( ItemID INT IDENTITY(1,1) PRIMARY KEY, ItemName VARCHAR(150), PricePerUnit NUMERIC(5,2) ); DECLARE @sales TABLE ( SaleID INT IDENTITY(1,1) PRIMARY KEY, UserID INT, ItemID INT, AmountSold INT );
The “@” tells SQL Server that we are creating a variable and in this case the data type for that variable is a table.
IDENTITY(1,1) means that this field auto increments. This is great for Primary keys so you do not have to know what the last ID was, it will do that for you and insert it.
Now we want to insert data into these table so we have something to lookup later on.