SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- Table structure
CREATE TABLE [dbo].[Trip](
  [ID] [varchar](15) NOT NULL PRIMARY KEY,
  [DateTimeStart] [datetime] NOT NULL,
  [DateTimeEnd] [datetime] NOT NULL,
  [ID_User] [varchar](8) NOT NULL,
  [ID_Scooter] [varchar](5) NOT NULL,
  [TripDuration] [decimal](10,2) NULL,
  [TripLength] [decimal](10,2) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[User](
  [ID] [varchar](8) NOT NULL PRIMARY KEY,
  [Username] [varchar](100) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Scooter](
  [ID] [varchar](5) NOT NULL PRIMARY KEY,
  [Brand] [varchar](100) NOT NULL,
  [Model] [varchar](100) NOT NULL,
  [ManufacturingYear] INT NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[RepairedScooter](
  [ID] [varchar](5) NOT NULL PRIMARY KEY,
  [Brand] [varchar](100) NOT NULL,
  [Model] [varchar](100) NOT NULL,
  [ManufacturingYear] INT NOT NULL,
  [LastOperatingYear] INT NULL
) ON [PRIMARY]
-- Table data
INSERT INTO [dbo].[Trip]([ID],[DateTimeStart],[DateTimeEnd],[ID_User],[ID_Scooter],[TripDuration],[TripLength])
VALUES ('T20200515000001','2020-05-15 8:35:12.000','2020-05-15 8:47:16.000','U0000001','SR001',724,1448)
INSERT INTO [dbo].[Trip]([ID],[DateTimeStart],[DateTimeEnd],[ID_User],[ID_Scooter],[TripDuration],[TripLength])
VALUES ('T20200515000002','2020-05-15 9:44:0.000','2020-05-15 10:1:17.000','U0000001','SR002',1037,2074)
INSERT INTO [dbo].[Trip]([ID],[DateTimeStart],[DateTimeEnd],[ID_User],[ID_Scooter],[TripDuration],[TripLength])
VALUES ('T20200515000003','2020-05-15 23:1:11.000','2020-05-15 23:38:34.000','U0000003','SR003',2243,2243)
INSERT INTO [dbo].[Trip]([ID],[DateTimeStart],[DateTimeEnd],[ID_User],[ID_Scooter],[TripDuration],[TripLength])
VALUES ('T20200515000004','2020-05-15 1:0:59.000','2020-05-15 1:37:21.000','U0000004','SR002',2182,2182)
INSERT INTO [dbo].[Trip]([ID],[DateTimeStart],[DateTimeEnd],[ID_User],[ID_Scooter],[TripDuration],[TripLength])
VALUES ('T20200515000005','2020-05-15 11:34:34.000','2020-05-15 12:4:20.000','U0000004','SR005',1786,3572)
INSERT INTO [dbo].[Trip]([ID],[DateTimeStart],[DateTimeEnd],[ID_User],[ID_Scooter],[TripDuration],[TripLength])
VALUES ('T20200515000006','2020-05-15 12:12:55.000','2020-05-15 12:16:27.000','U0000004','SR005',212,424)
INSERT INTO [dbo].[Trip]([ID],[DateTimeStart],[DateTimeEnd],[ID_User],[ID_Scooter],[TripDuration],[TripLength])
VALUES ('T20200515000007','2020-05-15 18:0:21.000','2020-05-15 18:8:30.000','U0000004','SR005',489,978)
INSERT INTO [dbo].[Trip]([ID],[DateTimeStart],[DateTimeEnd],[ID_User],[ID_Scooter],[TripDuration],[TripLength])
VALUES ('T20200515000008','2020-05-15 11:19:36.000','2020-05-15 11:50:43.000','U0000006','SR002',1867,3734)
INSERT INTO [dbo].[Trip]([ID],[DateTimeStart],[DateTimeEnd],[ID_User],[ID_Scooter],[TripDuration],[TripLength])
VALUES ('T20200515000009','2020-05-15 7:22:55.000','2020-05-15 7:29:44.000','U0000007','SR005',409,409)
INSERT INTO [dbo].[Trip]([ID],[DateTimeStart],[DateTimeEnd],[ID_User],[ID_Scooter],[TripDuration],[TripLength])
VALUES ('T20200515000010','2020-05-15 8:44:46.000','2020-05-15 8:59:7.000','U0000007','SR001',861,861)
INSERT INTO [dbo].[Trip]([ID],[DateTimeStart],[DateTimeEnd],[ID_User],[ID_Scooter],[TripDuration],[TripLength])
VALUES ('T20200515000011','2020-05-15 10:22:22.000','2020-05-15 10:22:46.000','U0000007','SR001',24,24)
INSERT INTO [dbo].[Trip]([ID],[DateTimeStart],[DateTimeEnd],[ID_User],[ID_Scooter],[TripDuration],[TripLength])
VALUES ('T20200515000012','2020-05-15 17:35:17.000','2020-05-15 18:1:39.000','U0000009','SR001',1582,3164)

INSERT INTO [dbo].[User]([ID],[Username])
VALUES ('U0000001','Andrea')
INSERT INTO [dbo].[User]([ID],[Username])
VALUES ('U0000002','Nat')
INSERT INTO [dbo].[User]([ID],[Username])
VALUES ('U0000003','Donna')
INSERT INTO [dbo].[User]([ID],[Username])
VALUES ('U0000004','Brenda')
INSERT INTO [dbo].[User]([ID],[Username])
VALUES ('U0000005','Jim')
INSERT INTO [dbo].[User]([ID],[Username])
VALUES ('U0000006','Brandon')
INSERT INTO [dbo].[User]([ID],[Username])
VALUES ('U0000007','Dylan')
INSERT INTO [dbo].[User]([ID],[Username])
VALUES ('U0000008','Kelly')

INSERT INTO [dbo].[Scooter] ([ID],[Brand],[Model],[ManufacturingYear])
VALUES ('SR001','Tornado','M01',2008)
INSERT INTO [dbo].[Scooter] ([ID],[Brand],[Model],[ManufacturingYear])
VALUES ('SR002','Tornado','M01',2009)
INSERT INTO [dbo].[Scooter] ([ID],[Brand],[Model],[ManufacturingYear])
VALUES ('SR003','Tornado','M01',2009)
INSERT INTO [dbo].[Scooter] ([ID],[Brand],[Model],[ManufacturingYear])
VALUES ('SR004','Speedy','Gonzales99',2020)
INSERT INTO [dbo].[Scooter] ([ID],[Brand],[Model],[ManufacturingYear])
VALUES ('SR005','Speedy','Gonzales99',2020)

INSERT INTO [dbo].[RepairedScooter] ([ID],[Brand],[Model],[ManufacturingYear],[LastOperatingYear])
VALUES ('SR000','Speedy','Gonzales95',1997, 2018)
INSERT INTO [dbo].[RepairedScooter] ([ID],[Brand],[Model],[ManufacturingYear],[LastOperatingYear])
VALUES ('SR003','Tornado','M01',2009, NULL)
27 rows affected

-- Select
SELECT * FROM [Trip]
SELECT * FROM [User]
SELECT * FROM [Scooter]
SELECT * FROM [RepairedScooter]


ID
DateTimeStart
DateTimeEnd
ID_User
ID_Scooter
TripDuration
TripLength


T20200515000001
2020-05-15 08:35:12.000
2020-05-15 08:47:16.000
U0000001
SR001
724.00
1448.00

T20200515000002
2020-05-15 09:44:00.000
2020-05-15 10:01:17.000
U0000001
SR002
1037.00
2074.00

T20200515000003
2020-05-15 23:01:11.000
2020-05-15 23:38:34.000
U0000003
SR003
2243.00
2243.00

T20200515000004
2020-05-15 01:00:59.000
2020-05-15 01:37:21.000
U0000004
SR002
2182.00
2182.00

T20200515000005
2020-05-15 11:34:34.000
2020-05-15 12:04:20.000
U0000004
SR005
1786.00
3572.00

T20200515000006
2020-05-15 12:12:55.000
2020-05-15 12:16:27.000
U0000004
SR005
212.00
424.00

T20200515000007
2020-05-15 18:00:21.000
2020-05-15 18:08:30.000
U0000004
SR005
489.00
978.00

T20200515000008
2020-05-15 11:19:36.000
2020-05-15 11:50:43.000
U0000006
SR002
1867.00
3734.00

T20200515000009
2020-05-15 07:22:55.000
2020-05-15 07:29:44.000
U0000007
SR005
409.00
409.00

T20200515000010
2020-05-15 08:44:46.000
2020-05-15 08:59:07.000
U0000007
SR001
861.00
861.00

T20200515000011
2020-05-15 10:22:22.000
2020-05-15 10:22:46.000
U0000007
SR001
24.00
24.00

T20200515000012
2020-05-15 17:35:17.000
2020-05-15 18:01:39.000
U0000009
SR001
1582.00
3164.00




ID
Username


U0000001
Andrea

U0000002
Nat

U0000003
Donna

U0000004
Brenda

U0000005
Jim

U0000006
Brandon

U0000007
Dylan

U0000008
Kelly




ID
Brand
Model
ManufacturingYear


SR001
Tornado
M01
2008

SR002
Tornado
M01
2009

SR003
Tornado
M01
2009

SR004
Speedy
Gonzales99
2020

SR005
Speedy
Gonzales99
2020




ID
Brand
Model
ManufacturingYear
LastOperatingYear


SR000
Speedy
Gonzales95
1997
2018

SR003
Tornado
M01
2009
null


-- Query A001
SELECT DISTINCT A.[ID],A.[Username],B.[ID],B.[Brand],B.[Model]
FROM [User] A,[Scooter] B
ORDER BY A.[ID],B.[ID]


ID
Username
ID
Brand
Model


U0000001
Andrea
SR001
Tornado
M01

U0000001
Andrea
SR002
Tornado
M01

U0000001
Andrea
SR003
Tornado
M01

U0000001
Andrea
SR004
Speedy
Gonzales99

U0000001
Andrea
SR005
Speedy
Gonzales99

U0000002
Nat
SR001
Tornado
M01

U0000002
Nat
SR002
Tornado
M01

U0000002
Nat
SR003
Tornado
M01

U0000002
Nat
SR004
Speedy
Gonzales99

U0000002
Nat
SR005
Speedy
Gonzales99

U0000003
Donna
SR001
Tornado
M01

U0000003
Donna
SR002
Tornado
M01

U0000003
Donna
SR003
Tornado
M01

U0000003
Donna
SR004
Speedy
Gonzales99

U0000003
Donna
SR005
Speedy
Gonzales99

U0000004
Brenda
SR001
Tornado
M01

U0000004
Brenda
SR002
Tornado
M01

U0000004
Brenda
SR003
Tornado
M01

U0000004
Brenda
SR004
Speedy
Gonzales99

U0000004
Brenda
SR005
Speedy
Gonzales99

U0000005
Jim
SR001
Tornado
M01

U0000005
Jim
SR002
Tornado
M01

U0000005
Jim
SR003
Tornado
M01

U0000005
Jim
SR004
Speedy
Gonzales99

U0000005
Jim
SR005
Speedy
Gonzales99

U0000006
Brandon
SR001
Tornado
M01

U0000006
Brandon
SR002
Tornado
M01

U0000006
Brandon
SR003
Tornado
M01

U0000006
Brandon
SR004
Speedy
Gonzales99

U0000006
Brandon
SR005
Speedy
Gonzales99

U0000007
Dylan
SR001
Tornado
M01

U0000007
Dylan
SR002
Tornado
M01

U0000007
Dylan
SR003
Tornado
M01

U0000007
Dylan
SR004
Speedy
Gonzales99

U0000007
Dylan
SR005
Speedy
Gonzales99

U0000008
Kelly
SR001
Tornado
M01

U0000008
Kelly
SR002
Tornado
M01

U0000008
Kelly
SR003
Tornado
M01

U0000008
Kelly
SR004
Speedy
Gonzales99

U0000008
Kelly
SR005
Speedy
Gonzales99


-- Query B001
SELECT A.[ID],A.[DateTimeStart],A.[TripDuration],B.[Brand],B.[Model]
FROM [Trip] A
INNER JOIN [Scooter] B
ON A.[ID_Scooter]=B.[ID]


ID
DateTimeStart
TripDuration
Brand
Model


T20200515000001
2020-05-15 08:35:12.000
724.00
Tornado
M01

T20200515000002
2020-05-15 09:44:00.000
1037.00
Tornado
M01

T20200515000003
2020-05-15 23:01:11.000
2243.00
Tornado
M01

T20200515000004
2020-05-15 01:00:59.000
2182.00
Tornado
M01

T20200515000005
2020-05-15 11:34:34.000
1786.00
Speedy
Gonzales99

T20200515000006
2020-05-15 12:12:55.000
212.00
Speedy
Gonzales99

T20200515000007
2020-05-15 18:00:21.000
489.00
Speedy
Gonzales99

T20200515000008
2020-05-15 11:19:36.000
1867.00
Tornado
M01

T20200515000009
2020-05-15 07:22:55.000
409.00
Speedy
Gonzales99

T20200515000010
2020-05-15 08:44:46.000
861.00
Tornado
M01

T20200515000011
2020-05-15 10:22:22.000
24.00
Tornado
M01

T20200515000012
2020-05-15 17:35:17.000
1582.00
Tornado
M01


-- Query B002
SELECT A.[ID],[DateTimeStart],[TripDuration],[Brand],[Model]
FROM [Trip] A
INNER JOIN [Scooter] B
ON [ID_Scooter]=B.[ID]


ID
DateTimeStart
TripDuration
Brand
Model


T20200515000001
2020-05-15 08:35:12.000
724.00
Tornado
M01

T20200515000002
2020-05-15 09:44:00.000
1037.00
Tornado
M01

T20200515000003
2020-05-15 23:01:11.000
2243.00
Tornado
M01

T20200515000004
2020-05-15 01:00:59.000
2182.00
Tornado
M01

T20200515000005
2020-05-15 11:34:34.000
1786.00
Speedy
Gonzales99

T20200515000006
2020-05-15 12:12:55.000
212.00
Speedy
Gonzales99

T20200515000007
2020-05-15 18:00:21.000
489.00
Speedy
Gonzales99

T20200515000008
2020-05-15 11:19:36.000
1867.00
Tornado
M01

T20200515000009
2020-05-15 07:22:55.000
409.00
Speedy
Gonzales99

T20200515000010
2020-05-15 08:44:46.000
861.00
Tornado
M01

T20200515000011
2020-05-15 10:22:22.000
24.00
Tornado
M01

T20200515000012
2020-05-15 17:35:17.000
1582.00
Tornado
M01


-- Query B003
SELECT [Trip].[ID],[Trip].[DateTimeStart],[Trip].[TripDuration],[Scooter].[Brand],[Scooter].[Model]
FROM [Trip]
INNER JOIN [Scooter]
ON [Trip].[ID_Scooter]=[Scooter].[ID]


ID
DateTimeStart
TripDuration
Brand
Model


T20200515000001
2020-05-15 08:35:12.000
724.00
Tornado
M01

T20200515000002
2020-05-15 09:44:00.000
1037.00
Tornado
M01

T20200515000003
2020-05-15 23:01:11.000
2243.00
Tornado
M01

T20200515000004
2020-05-15 01:00:59.000
2182.00
Tornado
M01

T20200515000005
2020-05-15 11:34:34.000
1786.00
Speedy
Gonzales99

T20200515000006
2020-05-15 12:12:55.000
212.00
Speedy
Gonzales99

T20200515000007
2020-05-15 18:00:21.000
489.00
Speedy
Gonzales99

T20200515000008
2020-05-15 11:19:36.000
1867.00
Tornado
M01

T20200515000009
2020-05-15 07:22:55.000
409.00
Speedy
Gonzales99

T20200515000010
2020-05-15 08:44:46.000
861.00
Tornado
M01

T20200515000011
2020-05-15 10:22:22.000
24.00
Tornado
M01

T20200515000012
2020-05-15 17:35:17.000
1582.00
Tornado
M01


-- Query B004
SELECT A.[ID],A.[DateTimeStart],A.[TripDuration],B.[Brand],B.[Model],C.*
FROM [Trip] A
INNER JOIN [Scooter] B
ON A.[ID_Scooter]=B.[ID]
INNER JOIN [User] C
ON A.[ID_User]=C.[ID]


ID
DateTimeStart
TripDuration
Brand
Model
ID
Username


T20200515000001
2020-05-15 08:35:12.000
724.00
Tornado
M01
U0000001
Andrea

T20200515000002
2020-05-15 09:44:00.000
1037.00
Tornado
M01
U0000001
Andrea

T20200515000003
2020-05-15 23:01:11.000
2243.00
Tornado
M01
U0000003
Donna

T20200515000004
2020-05-15 01:00:59.000
2182.00
Tornado
M01
U0000004
Brenda

T20200515000005
2020-05-15 11:34:34.000
1786.00
Speedy
Gonzales99
U0000004
Brenda

T20200515000006
2020-05-15 12:12:55.000
212.00
Speedy
Gonzales99
U0000004
Brenda

T20200515000007
2020-05-15 18:00:21.000
489.00
Speedy
Gonzales99
U0000004
Brenda

T20200515000008
2020-05-15 11:19:36.000
1867.00
Tornado
M01
U0000006
Brandon

T20200515000009
2020-05-15 07:22:55.000
409.00
Speedy
Gonzales99
U0000007
Dylan

T20200515000010
2020-05-15 08:44:46.000
861.00
Tornado
M01
U0000007
Dylan

T20200515000011
2020-05-15 10:22:22.000
24.00
Tornado
M01
U0000007
Dylan


-- Query C001
SELECT * 
FROM [Trip] A
FULL OUTER JOIN [User] B
ON A.[ID_User]=B.[ID]


ID
DateTimeStart
DateTimeEnd
ID_User
ID_Scooter
TripDuration
TripLength
ID
Username


T20200515000001
2020-05-15 08:35:12.000
2020-05-15 08:47:16.000
U0000001
SR001
724.00
1448.00
U0000001
Andrea

T20200515000002
2020-05-15 09:44:00.000
2020-05-15 10:01:17.000
U0000001
SR002
1037.00
2074.00
U0000001
Andrea

T20200515000003
2020-05-15 23:01:11.000
2020-05-15 23:38:34.000
U0000003
SR003
2243.00
2243.00
U0000003
Donna

T20200515000004
2020-05-15 01:00:59.000
2020-05-15 01:37:21.000
U0000004
SR002
2182.00
2182.00
U0000004
Brenda

T20200515000005
2020-05-15 11:34:34.000
2020-05-15 12:04:20.000
U0000004
SR005
1786.00
3572.00
U0000004
Brenda

T20200515000006
2020-05-15 12:12:55.000
2020-05-15 12:16:27.000
U0000004
SR005
212.00
424.00
U0000004
Brenda

T20200515000007
2020-05-15 18:00:21.000
2020-05-15 18:08:30.000
U0000004
SR005
489.00
978.00
U0000004
Brenda

T20200515000008
2020-05-15 11:19:36.000
2020-05-15 11:50:43.000
U0000006
SR002
1867.00
3734.00
U0000006
Brandon

T20200515000009
2020-05-15 07:22:55.000
2020-05-15 07:29:44.000
U0000007
SR005
409.00
409.00
U0000007
Dylan

T20200515000010
2020-05-15 08:44:46.000
2020-05-15 08:59:07.000
U0000007
SR001
861.00
861.00
U0000007
Dylan

T20200515000011
2020-05-15 10:22:22.000
2020-05-15 10:22:46.000
U0000007
SR001
24.00
24.00
U0000007
Dylan

T20200515000012
2020-05-15 17:35:17.000
2020-05-15 18:01:39.000
U0000009
SR001
1582.00
3164.00
null
null

null
null
null
null
null
null
null
U0000002
Nat

null
null
null
null
null
null
null
U0000005
Jim

null
null
null
null
null
null
null
U0000008
Kelly


-- Query D001
SELECT * 
FROM [Trip] A
LEFT JOIN [User] B
ON A.[ID_User]=B.[ID]


ID
DateTimeStart
DateTimeEnd
ID_User
ID_Scooter
TripDuration
TripLength
ID
Username


T20200515000001
2020-05-15 08:35:12.000
2020-05-15 08:47:16.000
U0000001
SR001
724.00
1448.00
U0000001
Andrea

T20200515000002
2020-05-15 09:44:00.000
2020-05-15 10:01:17.000
U0000001
SR002
1037.00
2074.00
U0000001
Andrea

T20200515000003
2020-05-15 23:01:11.000
2020-05-15 23:38:34.000
U0000003
SR003
2243.00
2243.00
U0000003
Donna

T20200515000004
2020-05-15 01:00:59.000
2020-05-15 01:37:21.000
U0000004
SR002
2182.00
2182.00
U0000004
Brenda

T20200515000005
2020-05-15 11:34:34.000
2020-05-15 12:04:20.000
U0000004
SR005
1786.00
3572.00
U0000004
Brenda

T20200515000006
2020-05-15 12:12:55.000
2020-05-15 12:16:27.000
U0000004
SR005
212.00
424.00
U0000004
Brenda

T20200515000007
2020-05-15 18:00:21.000
2020-05-15 18:08:30.000
U0000004
SR005
489.00
978.00
U0000004
Brenda

T20200515000008
2020-05-15 11:19:36.000
2020-05-15 11:50:43.000
U0000006
SR002
1867.00
3734.00
U0000006
Brandon

T20200515000009
2020-05-15 07:22:55.000
2020-05-15 07:29:44.000
U0000007
SR005
409.00
409.00
U0000007
Dylan

T20200515000010
2020-05-15 08:44:46.000
2020-05-15 08:59:07.000
U0000007
SR001
861.00
861.00
U0000007
Dylan

T20200515000011
2020-05-15 10:22:22.000
2020-05-15 10:22:46.000
U0000007
SR001
24.00
24.00
U0000007
Dylan

T20200515000012
2020-05-15 17:35:17.000
2020-05-15 18:01:39.000
U0000009
SR001
1582.00
3164.00
null
null


-- Query D002
SELECT A.* 
FROM [Trip] A
LEFT JOIN [User] B
ON A.[ID_User]=B.[ID]
WHERE B.[ID] IS NULL


ID
DateTimeStart
DateTimeEnd
ID_User
ID_Scooter
TripDuration
TripLength


T20200515000012
2020-05-15 17:35:17.000
2020-05-15 18:01:39.000
U0000009
SR001
1582.00
3164.00


-- Query E001
SELECT * 
FROM [Trip] A
RIGHT JOIN [User] B
ON A.[ID_User]=B.[ID]


ID
DateTimeStart
DateTimeEnd
ID_User
ID_Scooter
TripDuration
TripLength
ID
Username


T20200515000001
2020-05-15 08:35:12.000
2020-05-15 08:47:16.000
U0000001
SR001
724.00
1448.00
U0000001
Andrea

T20200515000002
2020-05-15 09:44:00.000
2020-05-15 10:01:17.000
U0000001
SR002
1037.00
2074.00
U0000001
Andrea

null
null
null
null
null
null
null
U0000002
Nat

T20200515000003
2020-05-15 23:01:11.000
2020-05-15 23:38:34.000
U0000003
SR003
2243.00
2243.00
U0000003
Donna

T20200515000004
2020-05-15 01:00:59.000
2020-05-15 01:37:21.000
U0000004
SR002
2182.00
2182.00
U0000004
Brenda

T20200515000005
2020-05-15 11:34:34.000
2020-05-15 12:04:20.000
U0000004
SR005
1786.00
3572.00
U0000004
Brenda

T20200515000006
2020-05-15 12:12:55.000
2020-05-15 12:16:27.000
U0000004
SR005
212.00
424.00
U0000004
Brenda

T20200515000007
2020-05-15 18:00:21.000
2020-05-15 18:08:30.000
U0000004
SR005
489.00
978.00
U0000004
Brenda

null
null
null
null
null
null
null
U0000005
Jim

T20200515000008
2020-05-15 11:19:36.000
2020-05-15 11:50:43.000
U0000006
SR002
1867.00
3734.00
U0000006
Brandon

T20200515000009
2020-05-15 07:22:55.000
2020-05-15 07:29:44.000
U0000007
SR005
409.00
409.00
U0000007
Dylan

T20200515000010
2020-05-15 08:44:46.000
2020-05-15 08:59:07.000
U0000007
SR001
861.00
861.00
U0000007
Dylan

T20200515000011
2020-05-15 10:22:22.000
2020-05-15 10:22:46.000
U0000007
SR001
24.00
24.00
U0000007
Dylan

null
null
null
null
null
null
null
U0000008
Kelly


-- Query E002
SELECT B.* 
FROM [Trip] A
RIGHT JOIN [User] B
ON A.[ID_User]=B.[ID]
WHERE A.[ID] IS NULL


ID
Username


U0000002
Nat

U0000005
Jim

U0000008
Kelly


-- Query F001
SELECT * 
FROM [Trip] A
INNER JOIN [User] B
ON A.[ID_User]=B.[ID]


ID
DateTimeStart
DateTimeEnd
ID_User
ID_Scooter
TripDuration
TripLength
ID
Username


T20200515000001
2020-05-15 08:35:12.000
2020-05-15 08:47:16.000
U0000001
SR001
724.00
1448.00
U0000001
Andrea

T20200515000002
2020-05-15 09:44:00.000
2020-05-15 10:01:17.000
U0000001
SR002
1037.00
2074.00
U0000001
Andrea

T20200515000003
2020-05-15 23:01:11.000
2020-05-15 23:38:34.000
U0000003
SR003
2243.00
2243.00
U0000003
Donna

T20200515000004
2020-05-15 01:00:59.000
2020-05-15 01:37:21.000
U0000004
SR002
2182.00
2182.00
U0000004
Brenda

T20200515000005
2020-05-15 11:34:34.000
2020-05-15 12:04:20.000
U0000004
SR005
1786.00
3572.00
U0000004
Brenda

T20200515000006
2020-05-15 12:12:55.000
2020-05-15 12:16:27.000
U0000004
SR005
212.00
424.00
U0000004
Brenda

T20200515000007
2020-05-15 18:00:21.000
2020-05-15 18:08:30.000
U0000004
SR005
489.00
978.00
U0000004
Brenda

T20200515000008
2020-05-15 11:19:36.000
2020-05-15 11:50:43.000
U0000006
SR002
1867.00
3734.00
U0000006
Brandon

T20200515000009
2020-05-15 07:22:55.000
2020-05-15 07:29:44.000
U0000007
SR005
409.00
409.00
U0000007
Dylan

T20200515000010
2020-05-15 08:44:46.000
2020-05-15 08:59:07.000
U0000007
SR001
861.00
861.00
U0000007
Dylan

T20200515000011
2020-05-15 10:22:22.000
2020-05-15 10:22:46.000
U0000007
SR001
24.00
24.00
U0000007
Dylan


-- Query G001
SELECT * 
FROM [Trip] A
FULL OUTER JOIN [User] B
ON A.[ID_User]=B.[ID]
WHERE A.[ID] IS NULL OR B.[ID] IS NULL


ID
DateTimeStart
DateTimeEnd
ID_User
ID_Scooter
TripDuration
TripLength
ID
Username


T20200515000012
2020-05-15 17:35:17.000
2020-05-15 18:01:39.000
U0000009
SR001
1582.00
3164.00
null
null

null
null
null
null
null
null
null
U0000002
Nat

null
null
null
null
null
null
null
U0000005
Jim

null
null
null
null
null
null
null
U0000008
Kelly


-- Query H001
SELECT A.*,B.[TripDuration],B.[ID]
FROM [User] A
CROSS APPLY (
   SELECT TOP 1 *
   FROM [Trip] C
   WHERE C.[ID_User]=A.[ID]
   ORDER BY C.[TripDuration] DESC
) B


ID
Username
TripDuration
ID


U0000001
Andrea
1037.00
T20200515000002

U0000003
Donna
2243.00
T20200515000003

U0000004
Brenda
2182.00
T20200515000004

U0000006
Brandon
1867.00
T20200515000008

U0000007
Dylan
861.00
T20200515000010


-- Query I001
SELECT A.*,B.[TripDuration],B.[ID]
FROM [User] A
OUTER APPLY (
   SELECT TOP 1 *
   FROM [Trip] C
   WHERE C.[ID_User]=A.[ID]
   ORDER BY C.[TripDuration] DESC
) B


ID
Username
TripDuration
ID


U0000001
Andrea
1037.00
T20200515000002

U0000002
Nat
null
null

U0000003
Donna
2243.00
T20200515000003

U0000004
Brenda
2182.00
T20200515000004

U0000005
Jim
null
null

U0000006
Brandon
1867.00
T20200515000008

U0000007
Dylan
861.00
T20200515000010

U0000008
Kelly
null
null


-- Query L001
SELECT * 
FROM [Trip] A
LEFT JOIN [User] B
ON A.[ID_User]=B.[ID] 
WHERE B.[Username]='Brenda'


ID
DateTimeStart
DateTimeEnd
ID_User
ID_Scooter
TripDuration
TripLength
ID
Username


T20200515000004
2020-05-15 01:00:59.000
2020-05-15 01:37:21.000
U0000004
SR002
2182.00
2182.00
U0000004
Brenda

T20200515000005
2020-05-15 11:34:34.000
2020-05-15 12:04:20.000
U0000004
SR005
1786.00
3572.00
U0000004
Brenda

T20200515000006
2020-05-15 12:12:55.000
2020-05-15 12:16:27.000
U0000004
SR005
212.00
424.00
U0000004
Brenda

T20200515000007
2020-05-15 18:00:21.000
2020-05-15 18:08:30.000
U0000004
SR005
489.00
978.00
U0000004
Brenda


-- Query L002
SELECT * 
FROM [Trip] A
LEFT JOIN [User] B
ON A.[ID_User]=B.[ID] AND B.[USername]='Brenda'


ID
DateTimeStart
DateTimeEnd
ID_User
ID_Scooter
TripDuration
TripLength
ID
Username


T20200515000001
2020-05-15 08:35:12.000
2020-05-15 08:47:16.000
U0000001
SR001
724.00
1448.00
null
null

T20200515000002
2020-05-15 09:44:00.000
2020-05-15 10:01:17.000
U0000001
SR002
1037.00
2074.00
null
null

T20200515000003
2020-05-15 23:01:11.000
2020-05-15 23:38:34.000
U0000003
SR003
2243.00
2243.00
null
null

T20200515000004
2020-05-15 01:00:59.000
2020-05-15 01:37:21.000
U0000004
SR002
2182.00
2182.00
U0000004
Brenda

T20200515000005
2020-05-15 11:34:34.000
2020-05-15 12:04:20.000
U0000004
SR005
1786.00
3572.00
U0000004
Brenda

T20200515000006
2020-05-15 12:12:55.000
2020-05-15 12:16:27.000
U0000004
SR005
212.00
424.00
U0000004
Brenda

T20200515000007
2020-05-15 18:00:21.000
2020-05-15 18:08:30.000
U0000004
SR005
489.00
978.00
U0000004
Brenda

T20200515000008
2020-05-15 11:19:36.000
2020-05-15 11:50:43.000
U0000006
SR002
1867.00
3734.00
null
null

T20200515000009
2020-05-15 07:22:55.000
2020-05-15 07:29:44.000
U0000007
SR005
409.00
409.00
null
null

T20200515000010
2020-05-15 08:44:46.000
2020-05-15 08:59:07.000
U0000007
SR001
861.00
861.00
null
null

T20200515000011
2020-05-15 10:22:22.000
2020-05-15 10:22:46.000
U0000007
SR001
24.00
24.00
null
null

T20200515000012
2020-05-15 17:35:17.000
2020-05-15 18:01:39.000
U0000009
SR001
1582.00
3164.00
null
null


-- Query L003
SELECT * 
FROM [Trip] A
LEFT JOIN [User] B
ON A.[ID_User]=B.[ID] 
WHERE A.[ID_Scooter]='SR001'


ID
DateTimeStart
DateTimeEnd
ID_User
ID_Scooter
TripDuration
TripLength
ID
Username


T20200515000001
2020-05-15 08:35:12.000
2020-05-15 08:47:16.000
U0000001
SR001
724.00
1448.00
U0000001
Andrea

T20200515000010
2020-05-15 08:44:46.000
2020-05-15 08:59:07.000
U0000007
SR001
861.00
861.00
U0000007
Dylan

T20200515000011
2020-05-15 10:22:22.000
2020-05-15 10:22:46.000
U0000007
SR001
24.00
24.00
U0000007
Dylan

T20200515000012
2020-05-15 17:35:17.000
2020-05-15 18:01:39.000
U0000009
SR001
1582.00
3164.00
null
null


-- Query L004
SELECT * 
FROM [Trip] A
LEFT JOIN [User] B
ON A.[ID_User]=B.[ID] AND A.[ID_Scooter]='SR001'


ID
DateTimeStart
DateTimeEnd
ID_User
ID_Scooter
TripDuration
TripLength
ID
Username


T20200515000001
2020-05-15 08:35:12.000
2020-05-15 08:47:16.000
U0000001
SR001
724.00
1448.00
U0000001
Andrea

T20200515000002
2020-05-15 09:44:00.000
2020-05-15 10:01:17.000
U0000001
SR002
1037.00
2074.00
null
null

T20200515000003
2020-05-15 23:01:11.000
2020-05-15 23:38:34.000
U0000003
SR003
2243.00
2243.00
null
null

T20200515000004
2020-05-15 01:00:59.000
2020-05-15 01:37:21.000
U0000004
SR002
2182.00
2182.00
null
null

T20200515000005
2020-05-15 11:34:34.000
2020-05-15 12:04:20.000
U0000004
SR005
1786.00
3572.00
null
null

T20200515000006
2020-05-15 12:12:55.000
2020-05-15 12:16:27.000
U0000004
SR005
212.00
424.00
null
null

T20200515000007
2020-05-15 18:00:21.000
2020-05-15 18:08:30.000
U0000004
SR005
489.00
978.00
null
null

T20200515000008
2020-05-15 11:19:36.000
2020-05-15 11:50:43.000
U0000006
SR002
1867.00
3734.00
null
null

T20200515000009
2020-05-15 07:22:55.000
2020-05-15 07:29:44.000
U0000007
SR005
409.00
409.00
null
null

T20200515000010
2020-05-15 08:44:46.000
2020-05-15 08:59:07.000
U0000007
SR001
861.00
861.00
U0000007
Dylan

T20200515000011
2020-05-15 10:22:22.000
2020-05-15 10:22:46.000
U0000007
SR001
24.00
24.00
U0000007
Dylan

T20200515000012
2020-05-15 17:35:17.000
2020-05-15 18:01:39.000
U0000009
SR001
1582.00
3164.00
null
null


-- Query M001
SELECT * 
FROM [Scooter]
UNION ALL
SELECT * 
FROM [RepairedScooter]
Msg 205 Level 16 State 1 Line 2
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

-- Query M002
SELECT [ID],[Brand],[Model],[ManufacturingYear]
FROM [Scooter]
UNION ALL
SELECT [ID],[Brand],[Model],[ManufacturingYear]
FROM [RepairedScooter]


ID
Brand
Model
ManufacturingYear


SR001
Tornado
M01
2008

SR002
Tornado
M01
2009

SR003
Tornado
M01
2009

SR004
Speedy
Gonzales99
2020

SR005
Speedy
Gonzales99
2020

SR000
Speedy
Gonzales95
1997

SR003
Tornado
M01
2009


-- Query M003
SELECT [ID],[Brand],[Model],[ManufacturingYear]
FROM [Scooter]
UNION
SELECT [ID],[Brand],[Model],[ManufacturingYear]
FROM [RepairedScooter]


ID
Brand
Model
ManufacturingYear


SR000
Speedy
Gonzales95
1997

SR001
Tornado
M01
2008

SR002
Tornado
M01
2009

SR003
Tornado
M01
2009

SR004
Speedy
Gonzales99
2020

SR005
Speedy
Gonzales99
2020


-- Query N001
SELECT [ID],[Brand],[Model],[ManufacturingYear]
FROM [Scooter]
EXCEPT
SELECT [ID],[Brand],[Model],[ManufacturingYear]
FROM [RepairedScooter]


ID
Brand
Model
ManufacturingYear


SR001
Tornado
M01
2008

SR002
Tornado
M01
2009

SR004
Speedy
Gonzales99
2020

SR005
Speedy
Gonzales99
2020


-- Query O001
SELECT [ID],[Brand],[Model],[ManufacturingYear]
FROM [Scooter]
INTERSECT
SELECT [ID],[Brand],[Model],[ManufacturingYear]
FROM [RepairedScooter]


ID
Brand
Model
ManufacturingYear


SR003
Tornado
M01
2009
ID	DateTimeStart	DateTimeEnd	ID_User	ID_Scooter	TripDuration	TripLength	ID	Username
T20200515000012	2020-05-15 17:35:17.000	2020-05-15 18:01:39.000	U0000009	SR001	1582.00	3164.00	null	null
null	null	null	null	null	null	null	U0000002	Nat
null	null	null	null	null	null	null	U0000005	Jim
null	null	null	null	null	null	null	U0000008	Kelly
ID	DateTimeStart	DateTimeEnd	ID_User	ID_Scooter	TripDuration	TripLength
T20200515000001	2020-05-15 08:35:12.000	2020-05-15 08:47:16.000	U0000001	SR001	724.00	1448.00
T20200515000002	2020-05-15 09:44:00.000	2020-05-15 10:01:17.000	U0000001	SR002	1037.00	2074.00
T20200515000003	2020-05-15 23:01:11.000	2020-05-15 23:38:34.000	U0000003	SR003	2243.00	2243.00
T20200515000004	2020-05-15 01:00:59.000	2020-05-15 01:37:21.000	U0000004	SR002	2182.00	2182.00
T20200515000005	2020-05-15 11:34:34.000	2020-05-15 12:04:20.000	U0000004	SR005	1786.00	3572.00
T20200515000006	2020-05-15 12:12:55.000	2020-05-15 12:16:27.000	U0000004	SR005	212.00	424.00
T20200515000007	2020-05-15 18:00:21.000	2020-05-15 18:08:30.000	U0000004	SR005	489.00	978.00
T20200515000008	2020-05-15 11:19:36.000	2020-05-15 11:50:43.000	U0000006	SR002	1867.00	3734.00
T20200515000009	2020-05-15 07:22:55.000	2020-05-15 07:29:44.000	U0000007	SR005	409.00	409.00
T20200515000010	2020-05-15 08:44:46.000	2020-05-15 08:59:07.000	U0000007	SR001	861.00	861.00
T20200515000011	2020-05-15 10:22:22.000	2020-05-15 10:22:46.000	U0000007	SR001	24.00	24.00
T20200515000012	2020-05-15 17:35:17.000	2020-05-15 18:01:39.000	U0000009	SR001	1582.00	3164.00
ID	Username
U0000001	Andrea
U0000002	Nat
U0000003	Donna
U0000004	Brenda
U0000005	Jim
U0000006	Brandon
U0000007	Dylan
U0000008	Kelly
ID	Brand	Model	ManufacturingYear
SR001	Tornado	M01	2008
SR002	Tornado	M01	2009
SR003	Tornado	M01	2009
SR004	Speedy	Gonzales99	2020
SR005	Speedy	Gonzales99	2020
ID	Brand	Model	ManufacturingYear
SR000	Speedy	Gonzales95	1997
SR001	Tornado	M01	2008
SR002	Tornado	M01	2009
SR003	Tornado	M01	2009
SR004	Speedy	Gonzales99	2020
SR005	Speedy	Gonzales99	2020