SQLize
Online
/
PHPize Online
/
SQLtest Online
A
A
A
Share
Donate
Blog
Popular
Donate
A
A
A
Share
Blog
Popular
SQLize.online is a free online SQL environment for quickly running, experimenting with and sharing code.
You can run your SQL code on top of the most popular RDBMS including MySQL, MariaDB, SQLite, PostgreSQL, Oracle and Microsoft SQL Server.
SQL code:
Upload
Copy
Format
Clear
-- ** 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 DeviceId, cast(StartTime as date) StartDate, sum(Gallons) TotalGallons from [Usage] group by DeviceId, cast(StartTime as date);
SQL
Server:
MariaDB 11.4
MariaDB 11.5
MariaDB 10
MariaDB 10 Sakila (ReadOnly)
MySQL 5.7
MySQL 5.7 Sakila (ReadOnly)
MySQL 8.0
MySQL 8.0 Sakila (ReadOnly)
SQLite 3
PostgreSQL 10 Bookings (ReadOnly)
PostgreSQL 11
PostgreSQL 12
PostgreSQL 13
PostgreSQL 14
PostgreSQL 15
MS SQL Server 2017
MS SQL Server 2019
MS SQL Server 2022
MS SQL Server 2022 AdventureWorks (ReadOnly)
Firebird 4.0
Firebird 4.0 (Employee)
Oracle Database 19c (HR)
Oracle Database 21c
Oracle Database 23c Free
SOQOL
Version
ER Diagram
Preserve result
Stuck with a problem?
Got Error?
Ask ChatGPT!
Result:
Copy
Clear