SQLize Online / PHPize Online  /  SQLtest Online

-- ** 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 -- ** PROBLEM 2 -- Insert a couple devices into the Device table -- SerialNumber Name -- DVC0000101 Device 1 -- 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. -- ** 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 1