SQLize | PHPize | SQLtest

-- ** PROBLEM 1 -- Create a new table named "Device" with these columns: -- Id - required field, auto-generated, primary key -- SerialNumber - needs to be able to hold 10 alphanumeric characters -- Name - the device's name - 50 characters max -- Create another table called "Usage" used to hold usage data -- Id - required field, auto-generated, primary key -- DeviceId - refers to the Id column of the Device table above -- StartTime - the start date and time for this usage -- EndTime - the end date and time for this usage -- Gallons - the number of gallons reported by the device for a given hour create table Device ( Id int identity(1,1) primary key, SerialNumber nvarchar(10) not null, Name nvarchar(50) not null ); create table [Usage] ( Id int identity(1,1) primary key, DeviceId int, StartTime DateTime, EndTime DateTime, Gallons int ); -- ** PROBLEM 2 -- Insert a couple devices into the Device table -- SerialNumber Name -- DVC0000101 Device 1 -- DVC0000102 Device 2 insert into Device (SerialNumber, Name) values ('DVC0000101', 'Device 1'); insert into Device (SerialNumber, Name) values ('DVC0000102', 'Device 2'); -- Also insert a few records for each device into the Usage table. -- Make it so the usage is for hourly time intervals. -- For this problem we want to add few hours of usage for two different calendar days. insert into [Usage] (DeviceId, StartTime, EndTime, Gallons) values (1, '2024-10-01 11:15:00.000', '2024-10-01 12:15:00.000', 100); insert into [Usage] (DeviceId, StartTime, EndTime, Gallons) values (1, '2024-10-01 13:00:00.000', '2024-10-01 14:00:00.000', 200); insert into [Usage] (DeviceId, StartTime, EndTime, Gallons) values (1, '2024-10-02 13:00:00.000', '2024-10-02 14:00:00.000', 150); insert into [Usage] (DeviceId, StartTime, EndTime, Gallons) values (1, '2024-10-03 10:00:00.000', '2024-10-03 11:00:00.000', 300); insert into [Usage] (DeviceId, StartTime, EndTime, Gallons) values (2, '2024-10-01 11:30:00.000', '2024-10-01 12:30:00.000', 100); insert into [Usage] (DeviceId, StartTime, EndTime, Gallons) values (2, '2024-10-02 13:00:00.000', '2024-10-02 14:00:00.000', 170); insert into [Usage] (DeviceId, StartTime, EndTime, Gallons) values (2, '2024-10-02 11:30:00.000', '2024-10-02 12:30:00.000', 350); insert into [Usage] (DeviceId, StartTime, EndTime, Gallons) values (2, '2024-10-03 13:00:00.000', '2024-10-03 14:00:00.000', 200); -- ** PROBLEM 3 -- Write a query to get the total usage for each calendar day per device. -- The output should look something like this: -- SerialNumber | UsageDate | TotalGallons -- DVC0000101 | yyyy-MM-dd | ###.# -- DVC0000101 | yyyy-MM-dd | ###.# -- DVC0000102 | yyyy-MM-dd | ###.# -- DVC0000102 | yyyy-MM-dd | ###.# select CAST(GETDATE() as DATE); -- select DeviceId, Date(StartTime), sum(Gallons) TotalGallons from [Usage] -- group by DeviceId, Date(StartTime);