About db<>fiddle - db<>fiddle

TopAnswers db<>fiddle

Meta

Databases

TeX

Code Golf

APL

C++

.net

db<>fiddle

Java

*nix

PHP

PowerShell

Python

Rust

टेक्-मराठी

Typst

Web Client Dev

Web Server Dev

About db<>fiddle

Documentation

add tag

Community

[db<>fiddle](https://dbfiddle.uk) is similar to other tools like [SQL Fiddle](http://sqlfiddle.com) and [rextester](https://rextester.com) but specifically designed to be:

* friendly for markdown-based Q&A sites like Stack Overflow, TopAnswers and [Codidact](https://codidact.org)
* readable for longer, multi-step, code

---

Supported engines are:

engine|versions
------|--------
Db2 Developer-C|11.1 [^1]
Firebird|3.0, 4.0
MariaDB|~~10.2~~, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
MySQL|5.5, 5.6, 5.7, 8.0
Node.js|18
Oracle|11.2, 18, 21, 23
Postgres|~~8.4~~, 9.3, 9.4, 9.5, 9.6, 10, 11, 12, 13, 14, 15, 16, 17
SQL Server|~~2012~~, 2014, 2016, 2017, 2017 Linux, 2019, 2019 Linux, 2022
SQLite|~~3.8~~, ~~3.16~~[^2], 3.27, 3.39
YugabyteDB|2.6, 2.8, 2.18

Upcoming features and work in progress:

* Other Node.js versions
* Other programming language engines

[^1]: with lots of thanks to [@mustaccio](https://dba.meta.stackexchange.com/users/23721/mustaccio)
[^2]: currently offline for [security reasons](https://security-tracker.debian.org/tracker/CVE-2018-20346)

Create hospital fact table and four dimension tables (Doctor, Patient, Medical Records, and Admin) and Insert 5 values in each table HospitalVisits( VisitID, DoctorID, PatientID, MedicalRecordID, AdminID, VisitDate, Duration, Cost) Doctor(DoctorID, DoctorName, Specialty, Gender, Experience) Patient(PatientID, PatientName, Age, Gender, MedicalHistory) Medical Records(MedicalRecordID, Diagnosis, Treatment, Medication, PatientID, DoctorID) Admin(AdminID, AdminName, Role, Gender, Department ) Solve following Queries: 1. How many hospital visits were recorded in the fact table? 2. What is the total cost of all visits recorded in the fact table? 3. How many unique patients visited the hospital? 4. List all the doctors along with their specialties. 5. What is the average duration of hospital visits? 6. Show the total cost of visits for each doctor's specialty, with a grand total for all specialties combined

USE storedb; CREATE TABLE Categories ( categoryID int PRIMARY KEY AUTO_INCREMENT, categoryName varchar(255) not null, description text ); CREATE TABLE Suppliers ( supplierID int PRIMARY KEY AUTO_INCREMENT, supplierName varchar(255) not null, phone varchar(20) not null, address text not null ); CREATE TABLE Products ( productID int PRIMARY KEY AUTO_INCREMENT, categoryID int, supplierID int, productName varchar(255) not null, price DECIMAL(20, 2) not null, description text, FOREIGN KEY (categoryID) REFERENCES Categories(categoryID), FOREIGN KEY (supplierID) REFERENCES Suppliers(supplierID) ); CREATE TABLE ImageProduct ( imageID int PRIMARY KEY AUTO_INCREMENT, productID int, imageUrl text, FOREIGN KEY (productID) REFERENCES Products(productID) ); CREATE TABLE Customers ( customerID int PRIMARY KEY AUTO_INCREMENT, name varchar(150) NOT NULL, phone varchar(10) NOT NULL, email varchar(150) NOT NULL, birthday datetime NOT NULL ); CREATE TABLE Accounts ( accountID int PRIMARY KEY AUTO_INCREMENT, customerID int unique, username VARCHAR(255) NOT NULL, hashed_password VARCHAR(255) NOT NULL, salt VARCHAR(255) NOT NULL, FOREIGN KEY (customerID) REFERENCES Customers(customerID) ); CREATE TABLE InfoDelivery ( infoDeliveryID int PRIMARY KEY AUTO_INCREMENT, accountID int, name varchar(50) not null, phone varchar(10) not null, city varchar(100) not null, district varchar(100) not null, ward varchar(100) not null, specificAddress varchar(100) not null, description text, dateCreate datetime not null, FOREIGN KEY (accountID) REFERENCES Accounts(accountID) ); CREATE TABLE Orders ( orderID int PRIMARY KEY AUTO_INCREMENT, customerID int, dateOrder datetime not null, dateShipping datetime, infoDeliveryID int, FOREIGN KEY (customerID) REFERENCES Customers(customerID), FOREIGN KEY (infoDeliveryID) REFERENCES InfoDelivery(infoDeliveryID) ); CREATE TABLE OrderDetails ( orderID int PRIMARY KEY, productID int PRIMARY KEY, price DECIMAL(20, 2) not null, quantity int NOT NULL, FOREIGN KEY (orderID) REFERENCES Orders(orderID), FOREIGN KEY (productID) REFERENCES Products(productID) ); CREATE TABLE CustomerCarts ( cartID int PRIMARY KEY AUTO_INCREMENT, accountID int unique, FOREIGN KEY (accountID) REFERENCES Accounts(accountID) ); CREATE TABLE CartDetails ( cartID int, productID int, quantity int not null, FOREIGN KEY (cartID) REFERENCES CustomerCarts(cartID), FOREIGN KEY (productID) REFERENCES Products(productID) );

0 Answers