Skip to content

J
jdbc-e001
Switch branch/tag
Find file
Normal viewHistoryPermalink
scriptDB1.sql 8.47 KB
Newer Older
ejercicios completados
b9e6e4e0
 
Matias Ferreira committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 
CREATE TABLE "students"(
  "student_iid" serial primary key,
  "fullname" varchar(256) not null,
  "birth_date" date
);

create table "courses" (
  "course_iid" serial primary key,
  "code" varchar(32) not null,
  "name" varchar(128) not null,
  "start_date" date not null,
  "end_date" date not null
);

create table "enrollments"(
  "enrollment_iid" serial primary key,
  "course_iid" integer not null,
  "student_iid" integer not null,
  "enrollment_date" date not null,
  "payment_date" date not null,
  "paid" boolean not null,
  foreign key ("course_iid") references "courses"("course_iid"),
  foreign key ("student_iid") references "students"("student_iid")
);



insert into "students"("fullname", "birth_date")
  values('juan perez', '10-11-2000'),
        ('pepa pig', '13-10-2001'),
        ('jimmy neutron', '03-05-2002'),
        ('james rodriguez', '08-09-2000'),
        ('ana frank', '04-07-2000'),
        ('jorge nitales', '02-03-2004'),
        ('andrea chipreto', '04-01-2005'),
        ('natalia monges', '30-11-1995'),
        ('patricia ramirez', '14-06-1994'),
        ('luhana gonzales', '07-08-1999');


insert into "courses" ("code", "name", "start_date", "end_date")
  values('code001', 'Matematicas', '02-02-2020', '03-05-2022'),
        ('code002', 'Ingles', '03-02-2020', '03-08-2020'),
        ('code003', 'Programacion', '07-02-2020', '07-05-2020'),
        ('code004', 'Quimica', '08-02-2020', '08-05-2020'),
        ('code005', 'Fisica', '09-02-2020', '09-05-2020');


insert into "enrollments"("course_iid", "student_iid", "enrollment_date", "payment_date", "paid")
  values( (select "course_iid" from "courses" where "course_iid"=1),
    (select "student_iid" from "students" where "student_iid"=1),
    '06-01-2020', '02-02-2020', 'false');
insert into "enrollments"("course_iid", "student_iid", "enrollment_date", "payment_date", "paid")
  values( (select "course_iid" from "courses" where "course_iid"=1),
    (select "student_iid" from "students" where "student_iid"=2),
    '06-01-2020', '02-02-2020', 'true');
insert into "enrollments"("course_iid", "student_iid", "enrollment_date", "payment_date", "paid")
  values( (select "course_iid" from "courses" where "course_iid"=1),
    (select "student_iid" from "students" where "student_iid"=3),
    '06-01-2020', '02-02-2020', 'false');
insert into "enrollments"("course_iid", "student_iid", "enrollment_date", "payment_date", "paid")
  values( (select "course_iid" from "courses" where "course_iid"=1),
    (select "student_iid" from "students" where "student_iid"=4),
    '06-01-2020', '02-02-2020', 'false');
insert into "enrollments"("course_iid", "student_iid", "enrollment_date", "payment_date", "paid")
  values( (select "course_iid" from "courses" where "course_iid"=1),
    (select "student_iid" from "students" where "student_iid"=5),
    '06-01-2020', '02-02-2020', 'true');
insert into "enrollments"("course_iid", "student_iid", "enrollment_date", "payment_date", "paid")
  values( (select "course_iid" from "courses" where "course_iid"=2),
    (select "student_iid" from "students" where "student_iid"=1),
    '06-01-2020', '03-02-2020', 'false');
insert into "enrollments"("course_iid", "student_iid", "enrollment_date", "payment_date", "paid")
  values( (select "course_iid" from "courses" where "course_iid"=2),
    (select "student_iid" from "students" where "student_iid"=2),
    '06-01-2020', '03-02-2020', 'true');
insert into "enrollments"("course_iid", "student_iid", "enrollment_date", "payment_date", "paid")
  values( (select "course_iid" from "courses" where "course_iid"=2),
    (select "student_iid" from "students" where "student_iid"=3),
    '06-01-2020', '03-02-2020', 'true');
insert into "enrollments"("course_iid", "student_iid", "enrollment_date", "payment_date", "paid")
  values( (select "course_iid" from "courses" where "course_iid"=2),
    (select "student_iid" from "students" where "student_iid"=10),
    '06-01-2020', '03-02-2020', 'true');
insert into "enrollments"("course_iid", "student_iid", "enrollment_date", "payment_date", "paid")
  values( (select "course_iid" from "courses" where "course_iid"=2),
    (select "student_iid" from "students" where "student_iid"=7),
    '06-01-2020', '03-02-2020', 'true');
insert into "enrollments"("course_iid", "student_iid", "enrollment_date", "payment_date", "paid")
  values( (select "course_iid" from "courses" where "course_iid"=3),
    (select "student_iid" from "students" where "student_iid"=1),
    '06-01-2020', '07-02-2020', 'true');
insert into "enrollments"("course_iid", "student_iid", "enrollment_date", "payment_date", "paid")
  values( (select "course_iid" from "courses" where "course_iid"=3),
    (select "student_iid" from "students" where "student_iid"=2),
    '06-01-2020', '07-02-2020', 'false');
insert into "enrollments"("course_iid", "student_iid", "enrollment_date", "payment_date", "paid")
  values( (select "course_iid" from "courses" where "course_iid"=3),
    (select "student_iid" from "students" where "student_iid"=10),
    '06-01-2020', '07-02-2020', 'true');
insert into "enrollments"("course_iid", "student_iid", "enrollment_date", "payment_date", "paid")
  values( (select "course_iid" from "courses" where "course_iid"=3),
    (select "student_iid" from "students" where "student_iid"=4),
    '06-01-2020', '07-02-2020', 'true');
insert into "enrollments"("course_iid", "student_iid", "enrollment_date", "payment_date", "paid")
  values( (select "course_iid" from "courses" where "course_iid"=3),
    (select "student_iid" from "students" where "student_iid"=10),
    '06-01-2020', '07-02-2020', 'true');
insert into "enrollments"("course_iid", "student_iid", "enrollment_date", "payment_date", "paid")
  values( (select "course_iid" from "courses" where "course_iid"=4),
    (select "student_iid" from "students" where "student_iid"=1),
    '06-01-2020', '08-02-2020', 'true');
insert into "enrollments"("course_iid", "student_iid", "enrollment_date", "payment_date", "paid")
  values( (select "course_iid" from "courses" where "course_iid"=4),
    (select "student_iid" from "students" where "student_iid"=8),
    '06-01-2020', '08-02-2020', 'true');
insert into "enrollments"("course_iid", "student_iid", "enrollment_date", "payment_date", "paid")
  values( (select "course_iid" from "courses" where "course_iid"=4),
    (select "student_iid" from "students" where "student_iid"=6),
    '06-01-2020', '08-02-2020', 'true');
insert into "enrollments"("course_iid", "student_iid", "enrollment_date", "payment_date", "paid")
  values( (select "course_iid" from "courses" where "course_iid"=4),
    (select "student_iid" from "students" where "student_iid"=4),
    '06-01-2020', '08-02-2020', 'true');
insert into "enrollments"("course_iid", "student_iid", "enrollment_date", "payment_date", "paid")
  values( (select "course_iid" from "courses" where "course_iid"=4),
    (select "student_iid" from "students" where "student_iid"=5),
    '06-01-2020', '08-02-2020', 'true');
insert into "enrollments"("course_iid", "student_iid", "enrollment_date", "payment_date", "paid")
  values( (select "course_iid" from "courses" where "course_iid"=5),
    (select "student_iid" from "students" where "student_iid"=1),
    '06-01-2020', '09-02-2020', 'true');
insert into "enrollments"("course_iid", "student_iid", "enrollment_date", "payment_date", "paid")
  values( (select "course_iid" from "courses" where "course_iid"=5),
    (select "student_iid" from "students" where "student_iid"=2),
    '06-01-2020', '09-02-2020', 'true');
insert into "enrollments"("course_iid", "student_iid", "enrollment_date", "payment_date", "paid")
  values( (select "course_iid" from "courses" where "course_iid"=5),
    (select "student_iid" from "students" where "student_iid"=9),
    '06-01-2020', '09-02-2020', 'true');
-- insert into "enrollments"("course_iid", "student_iid", "enrollment_date", "payment_date", "paid")
--   values( select "course_iid" from "courses" where "course_iid"=5,
--     select "student_iid" from "students" where "student_iid"=4,
--     '06-01-2020', '09-02-2020', 'true');
-- insert into "enrollments"("course_iid", "student_iid", "enrollment_date", "payment_date", "paid")
--   values( (select "course_iid" from "courses" where "course_iid"=2),
--     (select "student_iid" from "students" where "student_iid"=10),
--     '07-02-2020', '07-02-2020', 'true');
-- insert into "enrollments"("course_iid", "student_iid", "enrollment_date", "payment_date", "paid")
--   values( (select "course_iid" from "courses" where "course_iid"=3),
--     (select "student_iid" from "students" where "student_iid"=8),
--     '06-01-2020', '07-02-2020', 'false');