fork download
copy 
  1. CREATE TABLE Users (
  2.     user_id INT PRIMARY KEY,
  3.     name VARCHAR(255) NOT NULL,
  4.     email VARCHAR(255) UNIQUE NOT NULL,
  5.     join_date DATE NOT NULL,
  6.     subscription_type VARCHAR(20) NOT NULL CHECK (subscription_type IN ('Free', 'Basic', 'Premium'))
  7. );
  8.  
  9. CREATE TABLE Movies (
  10.     movie_id INT PRIMARY KEY,
  11.     title VARCHAR(255) NOT NULL,
  12.     release_year YEAR NOT NULL,
  13.     genre VARCHAR(100) NOT NULL,
  14.     rating DECIMAL(3,1) CHECK (rating BETWEEN 0.0 AND 10.0)
  15. );
  16.  
  17. CREATE TABLE WatchHistory (
  18.     history_id INT PRIMARY KEY,
  19.     user_id INT,
  20.     movie_id INT,
  21.     watch_date DATETIME NOT NULL,
  22.     watched_duration INT NOT NULL CHECK (watched_duration >= 0),
  23.     FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE,
  24.     FOREIGN KEY (movie_id) REFERENCES Movies(movie_id) ON DELETE CASCADE
  25. );
  26.  
  27. -- Insert into Users
  28. INSERT INTO Users (user_id, name, email, join_date, subscription_type) VALUES
  29. (1, 'Alice Johnson', 'alice@example.com', '2023-06-15', 'Premium'),
  30. (2, 'Bob Smith', 'bob@example.com', '2023-07-20', 'Basic'),
  31. (3, 'Charlie Davis', 'charlie@example.com', '2023-08-10', 'Free');
  32.  
  33. -- Insert into Movies
  34. INSERT INTO Movies (movie_id, title, release_year, genre, rating) VALUES
  35. (1, 'Inception', 2010, 'Sci-Fi', 8.8),
  36. (2, 'The Matrix', 1999, 'Action', 8.7),
  37. (3, 'Interstellar', 2014, 'Sci-Fi', 8.6);
  38.  
  39. -- Insert into WatchHistory
  40. INSERT INTO WatchHistory (history_id, user_id, movie_id, watch_date, watched_duration) VALUES
  41. (1, 1, 1, '2024-01-10 15:30:00', 120),
  42. (2, 2, 2, '2024-01-11 18:45:00', 90),
  43. (3, 3, 3, '2024-01-12 20:15:00', 140);
  44. INSERT INTO WatchHistory (history_id, user_id, movie_id, watch_date, watched_duration) VALUES
  45. (4, 1, 1, '2024-01-13 10:00:00', 125), -- Inception
  46. (5, 2, 1, '2024-01-14 12:30:00', 120), -- Inception
  47. (6, 3, 1, '2024-01-15 14:00:00', 130), -- Inception
  48. (7, 1, 1, '2024-01-16 16:45:00', 125), -- Inception
  49. (8, 2, 1, '2024-01-17 19:10:00', 120), -- Inception
  50. (9, 3, 1, '2024-01-18 21:30:00', 128), -- Inception
  51. (10, 1, 1, '2024-01-19 23:15:00', 126), -- Inception
  52. (11, 2, 1, '2024-01-20 08:50:00', 122), -- Inception
  53. (12, 3, 1, '2024-01-21 11:20:00', 124), -- Inception
  54. (13, 1, 1, '2024-01-22 13:45:00', 125), -- Inception
  55. (14, 2, 1, '2024-01-23 15:30:00', 119), -- Inception
  56. (15, 3, 1, '2024-01-24 18:00:00', 123), -- Inception
  57. (16, 1, 2, '2024-01-25 20:10:00', 100), -- The Matrix
  58. (17, 2, 2, '2024-01-26 22:30:00', 110), -- The Matrix
  59. (18, 3, 3, '2024-01-27 09:00:00', 135), -- Interstellar
  60. (19, 1, 3, '2024-01-28 11:45:00', 138), -- Interstellar
  61. (20, 2, 3, '2024-01-29 14:20:00', 140); -- Interstellar
  62.  
  63. SELECT m.title
  64. FROM Movies m
  65. JOIN WatchHistory w ON m.movie_id = w.movie_id
  66. GROUP BY m.movie_id
  67. HAVING COUNT(w.movie_id) >= 10;
  68.  
  69. -- your code goes here
Success #stdin #stdout 0.01s 5292KB
comments (?)
stdin
copy 
Standard input is empty
stdout
copy 
Inception
https://ideone.com/0PdvAE
language:
SQLite (sqlite 3.27.2)
created:
8 days ago
visibility:
public

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!