SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;


(No column name)


Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) 
	Oct  8 2022 05:58:25 
	Copyright (C) 2022 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE Users 
(
  userId INT IDENTITY (1, 1) NOT NULL,
  forename VARCHAR(50),
  surname VARCHAR(50),
  email VARCHAR(50),
  country VARCHAR(3),
  isPremium BIT DEFAULT 0,
  premiumStart DATETIME,
  premiumEnd DATETIME
);
-- The primary key for the user
ALTER TABLE Users ADD CONSTRAINT PK_Users_Id PRIMARY KEY (userId);
INSERT INTO
    Users (forename, surname, email, country)
VALUES
    (
        'karen',
        'rutherford',
        'karen.rutherford90@hotmail.com',
        'USA'
    ),
    (
        'casper',
        'goodwin',
        'casper.goodwin81@gmail.com',
        'UK'
    ),
    (
        'mariam', 
        'jones', 
        'mariam14@yahoo.com', 
        'USA'
    ),
    (
        'eden',
        'breitenberg',
        'eden_breitenberg@hotmail.com',
        'IRE'
    ),
    (
        'malachi', 
        'morris', 
        'malachi85@yahoo.com', 
        'BEL'
    );
5 rows affected

UPDATE Users SET isPremium = 1 WHERE userID = 3
1 rows affected

-- Select all of the values from this table so we can see what was inserted
SELECT * FROM Users


userId
forename
surname
email
country
isPremium
premiumStart
premiumEnd


1
karen
rutherford
karen.rutherford90@hotmail.com
USA
False
null
null

2
casper
goodwin
casper.goodwin81@gmail.com
UK
False
null
null

3
mariam
jones
mariam14@yahoo.com
USA
True
null
null

4
eden
breitenberg
eden_breitenberg@hotmail.com
IRE
False
null
null

5
malachi
morris
malachi85@yahoo.com
BEL
False
null
null


CREATE TABLE
    Product (
        productId INT IDENTITY (1, 1) NOT NULL,
        description VARCHAR(50),
        color VARCHAR(10),
        cost DECIMAL(19, 4)
    );
--This is the primary key for the products
ALTER TABLE Product ADD CONSTRAINT PK_Product_productId PRIMARY KEY (productId);
-- Here we are just inserting some dummy products into our product table.
INSERT INTO Product (description, color, cost)
VALUES
('Scooter','Red',3.99),
('Bike','Blue',19.99),
('Boat','Yellow',4.99),
('Car','Green',7.99),
('Tank','Brown',2.00)
5 rows affected

-- Select all of the values from this table so we can see what was inserted
SELECT * FROM Product
-- Remember that float value, check it out ----->>>>


productId
description
color
cost


1
Scooter
Red
3.9900

2
Bike
Blue
19.9900

3
Boat
Yellow
4.9900

4
Car
Green
7.9900

5
Tank
Brown
2.0000


CREATE TABLE Basket 
(
  -- This is the ID of the basket, there can be many baskets for many users
  basketID INT IDENTITY(1,1) NOT NULL,
  -- This is the person who owns the basket, users can have more than one basket 
  userID INT,
  createDate DATETIME DEFAULT GETDATE()
)
--This is the primary key constraint for the basketID
ALTER TABLE Basket ADD CONSTRAINT PK_Basket_basketID PRIMARY KEY (basketID);
-- Create some dummy baskets for users, 
-- here we are going to use some of the users we created above
INSERT INTO Basket (UserID)
VALUES
(1),
(2),
(3),
(5)
4 rows affected

-- Select all of the values from this table so we can see what was inserted
SELECT * FROM Basket


basketID
userID
createDate


1
1
2023-11-07 05:08:12.680

2
2
2023-11-07 05:08:12.680

3
3
2023-11-07 05:08:12.680

4
5
2023-11-07 05:08:12.680


CREATE TABLE BasketProduct
(
  -- This is a foreign key, it references the basket table 
  basketID INT NOT NULL,
  -- This is a foreign key, it references the products table because baskets
  -- can have many products 
  ProductID INT NOT NULL  
)
ALTER TABLE BasketProduct ADD CONSTRAINT FK_BasketProducts_basketID FOREIGN KEY (basketID) REFERENCES Basket (basketID);
-- This is the foreign key for the product ID, this references the product table as that
-- is where the source of the product is.
ALTER TABLE BasketProduct ADD CONSTRAINT FK_BasketProduct_ProductID FOREIGN KEY (ProductID) REFERENCES Product (productID);
-- This foreign key ensures that no values are created in the basketproduct that do not 
-- exist in the parent tables, in the base of this constrain that is the Product table.
-- This is the foreign key for the basket ID, this references the basket table as that
-- is where the source of the basket is.
ALTER TABLE BasketProduct ADD CONSTRAINT FK_BasketProduct_basketID FOREIGN KEY (basketID) REFERENCES Basket (basketID);
--This foreign key ensures that no values are created in the basketproduct that do not 
-- exist in the parent tables, in the base of this constrain that is the Basket table.
-- Try and insert a value into Basket Product that doesn't exist in Basket, it will error
-- as it is in violation of the key.
INSERT INTO BasketProduct (basketID, productID)
VALUES
(9,10) -- Both these values don't exist in the basket table or the product table
Msg 547 Level 16 State 0 Line 3
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_BasketProducts_basketID". The conflict occurred in database "fiddle_5ae6dbb3ca6e48e883859fc38d5c81ba", table "dbo.Basket", column 'basketID'.
Msg 3621 Level 0 State 0 Line 3
The statement has been terminated.

-- Try and insert a value into Basket Product that doesn't exist in Basket, it will error
-- as it is in violation of the key.
INSERT INTO BasketProduct (basketID, productID)
VALUES
(2,9)  -- The user exists but the product doesn't, still errors because one of the keys was violated
Msg 547 Level 16 State 0 Line 3
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_BasketProduct_ProductID". The conflict occurred in database "fiddle_5ae6dbb3ca6e48e883859fc38d5c81ba", table "dbo.Product", column 'productId'.
Msg 3621 Level 0 State 0 Line 3
The statement has been terminated.

-- Insert some products into the baskets for the users, 
-- we are going to use the basket ids created above
INSERT INTO BasketProduct (basketID, productID)
VALUES
(1,2),
(1,5),
(2,1),
(2,4),
(2,5),
(3,1)
6 rows affected

-- Select all of the values from this table so we can see what was inserted
SELECT * FROM BasketProduct


basketID
ProductID


1
2

1
5

2
1

2
4

2
5

3
1


--This query will get all baskets, their users and the products within the basket
--this uses an inner join so will only return values which on each side of the join, for
--example, user 4 doesn't have any products or a basket

SELECT * FROM Basket B

INNER JOIN BasketProduct BP ON B.BasketID = BP.BasketID

INNER JOIN Users U ON B.UserID = U.UserID

INNER JOIN Product P ON BP.ProductID = P.ProductID


basketID
userID
createDate
basketID
ProductID
userId
forename
surname
email
country
isPremium
premiumStart
premiumEnd
productId
description
color
cost


1
1
2023-11-07 05:08:12.680
1
2
1
karen
rutherford
karen.rutherford90@hotmail.com
USA
False
null
null
2
Bike
Blue
19.9900

1
1
2023-11-07 05:08:12.680
1
5
1
karen
rutherford
karen.rutherford90@hotmail.com
USA
False
null
null
5
Tank
Brown
2.0000

2
2
2023-11-07 05:08:12.680
2
1
2
casper
goodwin
casper.goodwin81@gmail.com
UK
False
null
null
1
Scooter
Red
3.9900

2
2
2023-11-07 05:08:12.680
2
4
2
casper
goodwin
casper.goodwin81@gmail.com
UK
False
null
null
4
Car
Green
7.9900

2
2
2023-11-07 05:08:12.680
2
5
2
casper
goodwin
casper.goodwin81@gmail.com
UK
False
null
null
5
Tank
Brown
2.0000

3
3
2023-11-07 05:08:12.680
3
1
3
mariam
jones
mariam14@yahoo.com
USA
True
null
null
1
Scooter
Red
3.9900


--This query will get all baskets, their users and the products within the basket
--this uses two left joins which will get me all users who have baskets, as you can see
--user 5 has a basket but has no products but does have a basket.

SELECT * FROM Basket B

  INNER JOIN Users U ON B.UserID = U.UserID

  LEFT JOIN BasketProduct BP ON B.BasketID = BP.BasketID
  
  LEFT JOIN Product P ON BP.ProductID = P.ProductID


basketID
userID
createDate
userId
forename
surname
email
country
isPremium
premiumStart
premiumEnd
basketID
ProductID
productId
description
color
cost


1
1
2023-11-07 05:08:12.680
1
karen
rutherford
karen.rutherford90@hotmail.com
USA
False
null
null
1
2
2
Bike
Blue
19.9900

1
1
2023-11-07 05:08:12.680
1
karen
rutherford
karen.rutherford90@hotmail.com
USA
False
null
null
1
5
5
Tank
Brown
2.0000

2
2
2023-11-07 05:08:12.680
2
casper
goodwin
casper.goodwin81@gmail.com
UK
False
null
null
2
1
1
Scooter
Red
3.9900

2
2
2023-11-07 05:08:12.680
2
casper
goodwin
casper.goodwin81@gmail.com
UK
False
null
null
2
4
4
Car
Green
7.9900

2
2
2023-11-07 05:08:12.680
2
casper
goodwin
casper.goodwin81@gmail.com
UK
False
null
null
2
5
5
Tank
Brown
2.0000

3
3
2023-11-07 05:08:12.680
3
mariam
jones
mariam14@yahoo.com
USA
True
null
null
3
1
1
Scooter
Red
3.9900

4
5
2023-11-07 05:08:12.680
5
malachi
morris
malachi85@yahoo.com
BEL
False
null
null
null
null
null
null
null
null


--Select all members and their premium status
SELECT
  forename,
  surname,
  CASE
    WHEN isPremium = 1 THEN 'Premium Member'
    ELSE 'Standard member'
  END AS isPremium
FROM
  Users


forename
surname
isPremium


karen
rutherford
Standard member

casper
goodwin
Standard member

mariam
jones
Premium Member

eden
breitenberg
Standard member

malachi
morris
Standard member
userId	forename	surname	email	country	isPremium	premiumStart	premiumEnd
1	karen	rutherford	karen.rutherford90@hotmail.com	USA	False	null	null
2	casper	goodwin	casper.goodwin81@gmail.com	UK	False	null	null
3	mariam	jones	mariam14@yahoo.com	USA	True	null	null
4	eden	breitenberg	eden_breitenberg@hotmail.com	IRE	False	null	null
5	malachi	morris	malachi85@yahoo.com	BEL	False	null	null
productId	description	color	cost
1	Scooter	Red	3.9900
2	Bike	Blue	19.9900
3	Boat	Yellow	4.9900
4	Car	Green	7.9900
5	Tank	Brown	2.0000
basketID	userID	createDate
1	1	2023-11-07 05:08:12.680
2	2	2023-11-07 05:08:12.680
3	3	2023-11-07 05:08:12.680
4	5	2023-11-07 05:08:12.680
forename	surname	isPremium
karen	rutherford	Standard member
casper	goodwin	Standard member
mariam	jones	Premium Member
eden	breitenberg	Standard member
malachi	morris	Standard member