--	1. Give me all invoices for user x and which city he is from - easy
declare @userid int = 1324;
select TInvoice.nInvoiceId, TCity.cName
from TInvoice
inner join TCity on TCity.nCityId=(select TUser.nCityId from TUser where TUser.nUserId=@userid)
where TInvoice.nUserId=@userid;


--	2. Give me all the products and how many products user x has bought in those invoices mentioned above  and their average ratings
select TProduct.cName, sum(TInVoiceLine.nQuantity), TProduct.nAverageRating
from TInvoice
inner join TInVoiceLine on TInvoice.nInvoiceId=TInVoiceLine.nInvoiceId
inner join TProduct on TProduct.nProductId=TInVoiceLine.nProductId
where TInvoice.nUserId=@userid
group by TProduct.cName, TProduct.nAverageRating;
--  Why do we need group by here but not above?


--  3. What is the average price of those products user x bought
select AVG(TInVoiceLine.nUnitPrice)
from TInVoiceLine
where TInVoiceLine.nInvoiceId in (select TInvoice.nInvoiceId from TInvoice where TInvoice.nUserId=@userid);


--	4. What is the rating of each product user x bought
Select TRating.nStar, TRating.cComment, TRating.nProductId
from TRating
where TRating.nProductId in (select TInVoiceLine.nProductId from TInVoiceLine where TInVoiceLine.nInvoiceId in (select TInvoice.nInvoiceId from TInvoice where TInvoice.nUserId=@userid));


--	5. Which creditcards did user x use
select TCreditCard.cCardNumber
from TCreditCard
where TCreditCard.nUserId=@userid and TCreditCard.nTotalPurchase > 0;


--  6. Give me the dates for which user x bought every product
select TInvoice.dInvoiceDate
from TInvoice
where TInvoice.nUserId=@userid;


--	7. Give me, if any, the ratings and comments user x has made
Select TRating.nStar, TRating.cComment, TRating.nProductId
from TRating
where TRating.nUserId=@userid;


--  8. Change the last name of user x
update TUser
set cSurname='lastname123'
where nUserId=@userid;
-- proof
select TUser.cName, TUser.cSurname
from TUser where TUser.nUserId=@userid;


--  9. Change a comment for a rating user x has made
select TRating.cComment, TRating.nUserId, TRating.nProductId
from TRating;

update TRating
set cComment = 'ændret'
where TRating.nUserId=2 and nProductId=3;


--  10. Delete a comment user x has made
delete from TRating
where nUserId=2 and nProductId=3;


--  11. Show me the changes
select * from RatingHistory where nUserId=2 and nProductId=3;


--  12. Create a stored procedure to get by last names