fork download
copy 
  1. DROP TABLE IF EXISTS Class;
  2. DROP TABLE IF EXISTS Teacher_Phone;
  3. DROP TABLE IF EXISTS Classroom;
  4. DROP TABLE IF EXISTS Teacher;
  5.  
  6.  
  7. CREATE TABLE Teacher (
  8.     teacher_id   INTEGER  PRIMARY KEY,
  9.     first_name   TEXT     NOT NULL,
  10.     last_name    TEXT     NOT NULL
  11. );
  12.  
  13. CREATE TABLE Teacher_Phone (
  14.     teacher_id   INTEGER  NOT NULL,
  15.     phone_number TEXT     NOT NULL,
  16.     PRIMARY KEY (teacher_id, phone_number),
  17.     FOREIGN KEY (teacher_id) REFERENCES Teacher(teacher_id)
  18. );
  19.  
  20. CREATE TABLE Classroom (
  21.     room_id    INTEGER  PRIMARY KEY,
  22.     building   TEXT     NOT NULL,
  23.     capacity   INTEGER  NOT NULL
  24. );
  25.  
  26. CREATE TABLE Class (
  27.     class_id    INTEGER  PRIMARY KEY,
  28.     class_name  TEXT     NOT NULL,
  29.     schedule    TEXT     NOT NULL,
  30.     teacher_id  INTEGER  NOT NULL,
  31.     room_id     INTEGER  UNIQUE,          -- UNIQUE enforces the 1-to-1
  32.     FOREIGN KEY (teacher_id) REFERENCES Teacher(teacher_id),
  33.     FOREIGN KEY (room_id)    REFERENCES Classroom(room_id)
  34. );
  35.  
  36.  
  37. INSERT INTO Teacher VALUES (1, 'Ahmed',  'Hassan');
  38. INSERT INTO Teacher VALUES (2, 'Sara',   'Khalil');
  39.  
  40. INSERT INTO Teacher_Phone VALUES (1, '010-1234-5678');
  41. INSERT INTO Teacher_Phone VALUES (1, '011-9999-0000');  
  42. INSERT INTO Teacher_Phone VALUES (2, '012-5555-3333');
  43.  
  44. INSERT INTO Classroom VALUES (101, 'Building A', 30);
  45. INSERT INTO Classroom VALUES (102, 'Building A', 25);
  46. INSERT INTO Classroom VALUES (103, 'Building B', 40);
  47.  
  48. INSERT INTO Class VALUES (1, 'Algebra I',  'Sun 8:00am',  1, 101);
  49. INSERT INTO Class VALUES (2, 'Algebra II', 'Mon 10:00am', 1, 102);
  50. INSERT INTO Class VALUES (3, 'Biology',    'Tue 9:00am',  2, 103);
  51.  
  52. SELECT * FROM Teacher;
  53. SELECT * FROM Teacher_Phone;
  54. SELECT * FROM Classroom;
  55. SELECT * FROM Class;
  56.  
  57.  
  58.  
  59.  
Success #stdin #stdout 0.01s 5288KB
comments (?)
stdin
copy 
Standard input is empty
stdout
copy 
1|Ahmed|Hassan
2|Sara|Khalil
1|010-1234-5678
1|011-9999-0000
2|012-5555-3333
101|Building A|30
102|Building A|25
103|Building B|40
1|Algebra I|Sun 8:00am|1|101
2|Algebra II|Mon 10:00am|1|102
3|Biology|Tue 9:00am|2|103
https://ideone.com/bc8UGb
language:
SQLite (sqlite 3.27.2)
created:
2 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!