---
description: A Quick SQL example
---

# Quick SQL Example

## Case

{% @mermaid/diagram content="erDiagram 
    TEACHER ||--o{ COURSE : Lecture
    TEACHER {
        integer id 
        string teach_name
        string teach_email
    }
   
    COURSE {
        int id 
        int teacher_id FK
        string course_id
        string course_name
    }

    STUDENT {
        int id 
        string stud_id "學號"
        string stud_name
        string stud_email
    }

    COURSE_STUDENT {
        int id 
        int course_id FK 
        int stud_id FK 
    }
    
    STUDENT ||--o{ COURSE_STUDENT : register

    COURSE ||--o{ COURSE_STUDENT : contain
" %}

## Quick SQL

```
# prefix: "app"
# drop: true

TEACHER /insert 2
    teach_name vc20
    teach_email vc30
    /unique teach_name, teach_email
    
COURSE  /insert 2 -- The text here go into the table comment
    course_name 
    teacher_id /fk teacher  -- The text go inot the column comment
    
STUDENT /insert 10
  stud_id vc20
  stud_name 
  stud_email
  
COURSE_STUDENT /insert 5
  course_id /fk COURSE
  student_id /fk STUDENT
```

## Generated Quick SQL

````sql
-- drop objects
drop table app_teacher cascade constraints;
drop table app_course cascade constraints;
drop table app_student cascade constraints;
drop table app_course_student cascade constraints;

-- create tables
create table app_teacher (
    id                             number generated by default on null as identity 
                                   constraint app_teacher_id_pk primary key,
    teach_name                     varchar2(20 char),
    teach_email                    varchar2(30 char)
)
;

-- unique table constraint
alter table app_teacher add constraint app_teacher_uk unique (teach_name, teach_email);

create table app_course (
    id                             number generated by default on null as identity 
                                   constraint app_course_id_pk primary key,
    teacher_id                     number
                                   constraint app_course_teacher_id_fk
                                   references app_teacher on delete cascade,
    course_name                    varchar2(255 char)
)
;

-- table index
create index app_course_i1 on app_course (teacher_id);

-- comments
comment on table app_course is 'The text here go into the table comment';
comment on column app_course.teacher_id is 'The text go inot the column comment';

create table app_student (
    id                             number generated by default on null as identity 
                                   constraint app_student_id_pk primary key,
    stud_id                        varchar2(20 char),
    stud_name                      varchar2(255 char),
    stud_email                     varchar2(255 char)
)
;

create table app_course_student (
    id                             number generated by default on null as identity 
                                   constraint app_course_student_id_pk primary key,
    course_id                      number
                                   constraint app_course_student_course_i_fk
                                   references app_course on delete cascade,
    student_id                     number
                                   constraint app_course_stud_student_id_fk
                                   references app_student on delete cascade
)
;

-- table index
create index app_course_student_i1 on app_course_student (course_id);
create index app_course_student_i102 on app_course_student (student_id);

-- load data
 
insert into app_teacher (
    id,
    teach_name,
    teach_email
) values (
    1,
    'Compendium GA',
    'gricelda.luebbers@aaab.com'
);

insert into app_teacher (
    id,
    teach_name,
    teach_email
) values (
    2,
    'SOA Upgrade',
    'dean.bollich@aaac.com'
);

commit;

alter table app_teacher
modify id generated by default on null as identity restart start with 3;
 
-- load data
-- load data
 
insert into app_course (
    id,
    course_name,
    teacher_id
) values (
    1,
    'Compendium GA',
    2
);

insert into app_course (
    id,
    course_name,
    teacher_id
) values (
    2,
    'SOA Upgrade',
    2
);

commit;

alter table app_course
modify id generated by default on null as identity restart start with 3;
 
-- load data
 
insert into app_student (
    id,
    stud_id,
    stud_name,
    stud_email
) values (
    1,
    'Diam',
    'Compendium GA',
    'gricelda.luebbers@aaab.com'
);

insert into app_student (
    id,
    stud_id,
    stud_name,
    stud_email
) values (
    2,
    'Risus',
    'SOA Upgrade',
    'dean.bollich@aaac.com'
);

insert into app_student (
    id,
    stud_id,
    stud_name,
    stud_email
) values (
    3,
    'Rhoncuscras',
    'Resiliency Monitoring',
    'milo.manoni@aaad.com'
);

insert into app_student (
    id,
    stud_id,
    stud_name,
    stud_email
) values (
    4,
    'Et',
    'SDK Upgraded for Embedded System',
    'laurice.karl@aaae.com'
);

insert into app_student (
    id,
    stud_id,
    stud_name,
    stud_email
) values (
    5,
    'Nonmolestie',
    'U88 Application',
    'august.rupel@aaaf.com'
);

insert into app_student (
    id,
    stud_id,
    stud_name,
    stud_email
) values (
    6,
    'Eget',
    'Developer Bug Performance Review',
    'salome.guisti@aaag.com'
);

insert into app_student (
    id,
    stud_id,
    stud_name,
    stud_email
) values (
    7,
    'Elit',
    'FAQ Automated Publishing',
    'lovie.ritacco@aaah.com'
);

insert into app_student (
    id,
    stud_id,
    stud_name,
    stud_email
) values (
    8,
    'Elementum',
    'Competitive Tracking',
    'chaya.greczkowski@aaai.com'
);

insert into app_student (
    id,
    stud_id,
    stud_name,
    stud_email
) values (
    9,
    'Rhoncus',
    'Inventory Quality Control Upgrade',
    'twila.coolbeth@aaaj.com'
);

insert into app_student (
    id,
    stud_id,
    stud_name,
    stud_email
) values (
    10,
    'Ultrices',
    'Checklist Management System',
    'carlotta.achenbach@aaak.com'
);

commit;

alter table app_student
modify id generated by default on null as identity restart start with 11;
 
-- load data
 
insert into app_course_student (
    id,
    course_id,
    student_id
) values (
    1,
    1,
    2
);

insert into app_course_student (
    id,
    course_id,
    student_id
) values (
    2,
    2,
    2
);

insert into app_course_student (
    id,
    course_id,
    student_id
) values (
    3,
    2,
    2
);

insert into app_course_student (
    id,
    course_id,
    student_id
) values (
    4,
    2,
    5
);

insert into app_course_student (
    id,
    course_id,
    student_id
) values (
    5,
    2,
    8
);

commit;

alter table app_course_student
modify id generated by default on null as identity restart start with 6;
 
 
-- Generated by Quick SQL Thursday September 14, 2023  05:13:34
 
/*
# prefix: "app"
TEACHER /insert 2
    teach_name vc20
    teach_email vc30
    /unique teach_name, teach_email
    
COURSE  /insert 2 -- The text here go into the table comment
    course_name 
    teacher_id /fk teacher  -- The text go inot the column comment
    
STUDENT /insert 10
  stud_id vc20
  stud_name 
  stud_email
  
COURSE_STUDENT /insert 5
  course_id /fk COURSE
  student_id /fk STUDENT
  
```

# settings = { prefix: "APP", semantics: "CHAR", drop: true, language: "EN", APEX: true }
*/
````
