DBMS > Structural Query Language (SQL) > Write SQL to create the database and tables.

The following is the schemma diagram of a databse.

DDL for the above diagram are as follows:

create table program(
  program_id int(3) primary key,
  program_name varchar(30)
);

create table call_type(
  call_id int(3) primary key,
  call_name varchar(30)
);

create table present_type(
  present_type_id int(3) primary key,
  present_type_name varchar(30)
);
create table students(
  student_id int(5) primary key,
  student_name varchar(30),
  program_id int(3),
  foreign key (program_id) references program(program_id)
);

create table presents(
  present_id int(10) primary key,
  student_id int(5),
  call_id int(3),
  present_type_id int(3),
  present_date date,
  foreign key (student_id )        references students(student_id),
  foreign key (call_id)               references call_type(call_id),
  foreign key (present_type_id) references present_type(present_type_id)
);

Now we can insert some example data using DML language:

insert into program values  (1, 'Diploma in ICT'),
                                        (2, 'Diploma in Web'),
                                        (3, 'Diploma in Hotel'),
                                        (4, 'Diploma in Tourism');
insert into present_type values (1, 'Present'),
                                             (2, 'Late'),
                                             (3, 'Absent with Permission'),
                                             (4, 'Absent without Permission');
insert into call_type values      (1, 'In the Morning at Playground'),
                                             (2, 'In the first class'),
                                             (3, 'In the Last class'),
                                             (4, 'At night at Dorm');
insert into students values       (1, 'Amena Islam', 1),
                                             (2, 'Asadur Rahman',2),
                                             (3, 'Jesmin Fariha', 3),
                                             (4, 'Shakhawat Hossain',4);

Prev

Some Questions

Next

Aggregate Functions

Feedback

Submit