o Suppose we have a ClubMember table created by CREATE TABLE ClubMember ( id INT PRIMARY...
Fantastic news! We've Found the answer you've been seeking!
Question:
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.
Expert Answer:
Answer rating: 100% (QA)
The image displays a SQL scenario involving a database table named ClubMember which contains member ... View the full answer
Related Book For
Systems analysis and design
ISBN: 978-0136089162
8th Edition
Authors: kenneth e. kendall, julie e. kendall
Posted Date:
Students also viewed these programming questions
-
A company produces very unusual CD's for which the variable cost is $ 11 per CD and the fixed costs are $ 20,000. They will sell the CD's for $ 88 each. Letxbe the number of CD's produced. Write the...
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
The following additional information is available for the Dr. Ivan and Irene Incisor family from Chapters 1-5. Ivan's grandfather died and left a portfolio of municipal bonds. In 2012, they pay Ivan...
-
The following information is available for Marigold's Hot Dogs: Actual production Budgeted production Standard direct labor hours Actual direct labor hours Standard variable overhead rate Actual...
-
Let a = (a1, a2, a3) and b = (b1, b2, b3) be fixed vectors. Show that (x - a) (x - b) = 0 is the equation of a sphere, and find its center and radius.
-
For this assignment, assume you are a CPA researching and making recommendations to your client regarding revenue recognition methods. Choose a specific client, (e.g., manufacturer, retailer,...
-
Determine which of the following numbers are prime: \(\{3,27,77,131,457\}\)
-
The Backwoods American Company in Problem 2-1 produces approximately 20,000 parkas annually. The quality management program the company implemented was able to improve the average percentage of good...
-
Marigold Company manufactures a variety of tools and industrial equipment. The company operates through three divisions. Each division is an investment center. Operating data for the Home Division...
-
Palisade Creek Co. is a merchandising business that uses the perpetual inventory system. The account balances for Palisade Creek as of May 1, 2018 (unless otherwise indicated), are as follows: During...
-
Complete the table below. Suppose 8% of those who take the COVID-19 test actually have the virus. The test is 90% accurate in identifying those with COVID-19 and those without COVID-19. Out of 1000...
-
Polls are to be conducted in two cities to determine the difference in the proportions of residents who believe that the economy will improve over the next year. A 95% confidence interval will be...
-
An unqualified audit report normally states that the financial statements to which the report refers give a true and fair view of the state of the company's affairs at the Statement of Financial...
-
A company borrows \($10,000\) for two years, interest free, when the market interest rate is 10%, compounded annually. At what amount should the liability be valued? How would your answer change if...
-
A company has a \($2\) million operating line of credit at an interest rate of 4% and (general use) long-term loans of \($8\) million at an interest rate of 7%. If general bor rowings are used to...
-
How would the payment of a \($5,000\) upfront administration fee on a \($50,000\), 6%, five-year loan affect subsequent recognition of interest expense?
-
(1 point) Consider the function f(x) = x3 - 12x on the interval [-3, 3]. The Global Maximum is The Global Minimum is
-
Is the modified 5-question approach to ethical decision making superior to the modified moral standards or modified Past in approach?
-
Design an outline of a podcast for someone touring your university, college, or business. What sequence would you place the topics in? How much time would you allow for each campus or building...
-
Which test ensures data accuracy by the incorporation of a number in the code itself?
-
Define what is meant by stratification of samples.
-
\(4 \times 13 \times 25\) Use properties of real numbers and mental math to calculate the expression.
-
\(13 \times 99\) Use properties of real numbers and mental math to calculate the expression.
-
\(23+98+75+12+77\) Use properties of real numbers and mental math to calculate the expression.
Study smarter with the SolutionInn App