o Suppose we have a ClubMember table created by CREATE TABLE ClubMember ( id INT PRIMARY...

Transcribed Image Text:

o Suppose we have a ClubMember table created by CREATE TABLE ClubMember ( id INT PRIMARY KEY, name VARCHAR(40) NOT NULL, registerTime DATETIME NOT NULL ); INSERT INTO ClubMember VALUES (1, 'David', (2, 'Jessica', (3, 'Andy', (4, 'Jenny', (5, 'Bill', ¹2020-5-28 16:25'), ¹2020-5-28 17:10'), ¹2020-1-1 11:11'), '2019-12-30'), ¹2020-5-27 10:45'); and we would like to ask the following query questions: ■ Who registered with the club within 2 hours as David did? ■ Who registered on the same date as David? Then these two functions DATEADD and CONVERT can come to our assistance as the solutions may be formulated respectively by ■ SELECT id, name, registerTime FROM ClubMember WHERE name <> 'David' AND - exclude 'David' himself DATEADD(HOUR, -2, registerTime) <= '2020-5-28 16:25¹ AND DATEADD(HOUR, 2, registerTime) >= '2020-5-28 16:25¹; where DATEADD(HOUR, 2, register Time) is to add 2 hours to register Time. ■ SELECT 'Who registered on the same date as David did?' [Question 2]; SELECT id, name, registerTime, convert (DATE, registerTime) AS 'register date' FROM ClubMember WHERE name <> 'David' AND -- exclude 'David' himself CONVERT (DATE, registerTime) = CONVERT (DATE, '2020-5-28 16:25'); where CONVERT(DATE, register Time) is to keep only the date part of register Time, not its time part. o Suppose we have a ClubMember table created by CREATE TABLE ClubMember ( id INT PRIMARY KEY, name VARCHAR(40) NOT NULL, registerTime DATETIME NOT NULL ); INSERT INTO ClubMember VALUES (1, 'David', (2, 'Jessica', (3, 'Andy', (4, 'Jenny', (5, 'Bill', ¹2020-5-28 16:25'), ¹2020-5-28 17:10'), ¹2020-1-1 11:11'), '2019-12-30'), ¹2020-5-27 10:45'); and we would like to ask the following query questions: ■ Who registered with the club within 2 hours as David did? ■ Who registered on the same date as David? Then these two functions DATEADD and CONVERT can come to our assistance as the solutions may be formulated respectively by ■ SELECT id, name, registerTime FROM ClubMember WHERE name <> 'David' AND - exclude 'David' himself DATEADD(HOUR, -2, registerTime) <= '2020-5-28 16:25¹ AND DATEADD(HOUR, 2, registerTime) >= '2020-5-28 16:25¹; where DATEADD(HOUR, 2, register Time) is to add 2 hours to register Time. ■ SELECT 'Who registered on the same date as David did?' [Question 2]; SELECT id, name, registerTime, convert (DATE, registerTime) AS 'register date' FROM ClubMember WHERE name <> 'David' AND -- exclude 'David' himself CONVERT (DATE, registerTime) = CONVERT (DATE, '2020-5-28 16:25'); where CONVERT(DATE, register Time) is to keep only the date part of register Time, not its time part.

Answer rating: 100% (QA)

The image displays a SQL scenario involving a database table named ClubMember which contains member ... View the full answer