sql-week-challenge.sql

create table Products(
ID int not null Primary key identity, 
ProdName nvarchar(40) not null,
Price decimal not null, 

)

create table Orders(
ID int not null Primary Key identity, 
ProductId int not null foreign key references Products(ID) on update cascade on delete cascade,
CustomerId int unique,

)

create table Customers(
CustomerRefID int Primary key identity,
ID int foreign Key references Orders(CustomerId) on update cascade on delete cascade, 


FirstName nvarchar(30) not null, 
LastName nvarchar(30) not null, 
CardNumber nvarchar (16) not null,


)

insert into Products values ('lamp', 30.00); 
insert into Products values ('tissues', 5.50);
insert into Products values ('orange', 7.50);

insert into Orders values ( 2, 1)
insert into Orders values ( 1, 2)
insert into Orders values ( 3, 3)

insert into Customers values (1 ,'Jack', 'Pflug', 123424235)
insert into Customers values (2 ,'John', 'Smith', 123254235)
insert into Customers values (3 ,'Judy', 'Smith', 123456235)

select * from Products;
select * from Orders;
Select * from Customers;
--inserting iphone
insert into Products values('IPhone', 200);

--adding tina
--insert into Customers values ( null,'Tina', 'Smith', 97520973);

--add Iphone order 
	insert into Orders values (4, 4); 
	insert into Customers values (4, 'Tina', 'Smith', 98135490); 

	-- get all of tinas orders
	Select Customers.FirstName from Orders inner join Customers on Orders.CustomerId = Customers.CustomerId where CustomerId = 4; 


	--revenue generated by iphone 
	With totalrevenue as(Select Price
			-- had I not ran out of time I would've used the CTE to create a variable that store the product price of the iphone and then ran a query to see the sum of the revenue generated 
			--by orders where the product id =4

			b