Inheritance Create Table Examples
drop table staff;
drop table faculty;
drop table employee;
drop table student;
drop table person;
------------------------set the default date format-------------------------
alter session set nls_date_format = 'YYYY-MM-DD';
------------------------- person table -------------------------
create table person(
person_id int generated always as identity start with 100 primary key,
first_name varchar(20),
last_name varchar(30) not null
);
insert into person (first_name, last_name) values('Gove', 'Allen'); --100
insert into person (first_name, last_name) values('Joseph', 'Xiong'); --101
insert into person (first_name, last_name) values('Madeleine', 'Johnson');--102
insert into person (first_name, last_name) values('Skyler ', 'Woolf'); --103
insert into person (first_name, last_name) values('Annie', 'Johnson'); --104
insert into person (first_name, last_name) values('Madeleine', 'Rawson'); --105
insert into person (first_name, last_name) values('Fiona', 'O''Connor'); --106
insert into person (first_name, last_name) values('Degan', 'Kettles'); --107
insert into person (first_name, last_name) values('Ashlyn', 'Lewis'); --108
insert into person (first_name, last_name) values('Spencer', 'Hilton'); --109
insert into person (first_name, last_name) values('Zach', 'Irvine'); --110
select * from person;
------------------------- student table -------------------------
create table student(
student_id int references person primary key,
gpa number(3,2),
date_admitted date
);
insert into student values(101, 3.95, '2022-03-14');
insert into student values(102, 3.97, '2021-02-05');
insert into student values(103, 3.92, '2020-12-09');
insert into student values(104, 3.99, '2021-04-04');
insert into student values(105, 3.90, '2021-01-29');
insert into student values(106, 2.85, '2019-02-05');
insert into student values(110, 4.0, '2020-03-25');
select * from student;
------ Student with full attributes ------
select student_id, first_name, last_name, date_admitted, gpa
from student
join person
on person_id=student_id;
------------------------- employee table -------------------------
create table employee(
employee_id int references person primary key,
date_hired date
);
insert into employee values(100, '2007-01-01');
insert into employee values(106, '1998-07-01');
insert into employee values(107, '2017-09-15');
insert into employee values(108, '2018-07-01');
insert into employee values(109, '2019-07-01');
insert into employee values(110, '2023-09-09');
select * from employee;
------ employee with full attributes ------
select employee_id, first_name, last_name, date_hired
from employee
join person
on person_id=employee_id;
------------------------- faculty table -------------------------
create table faculty(
faculty_id int references employee primary key,
terminal_degree char(5),
institution varchar(50)
);
insert into faculty values(100, 'PhD', 'University of Minnesota');
insert into faculty values(107, 'PhD', 'Arizona State University');
insert into faculty values(109, 'MBA', 'Weber State University');
select * from faculty;
------ faculty with full attributes ------
select faculty_id, first_name, last_name, date_hired, terminal_degree, institution
from person
join employee
on person_id=employee_id
join faculty
on employee_id=faculty_id;
------------------------- staff table -------------------------
create table staff(
employee_id int references employee primary key,
job_title varchar(50)
);
insert into staff values(106, 'Event Coordinator');
insert into staff values(108, 'Department Secretary');
insert into staff values(110, 'Teaching Assistant');
select * from staff;
------ staff with full attributes ------
select staff.employee_id, first_name, last_name, date_hired, job_title
from person
join employee
on person_id=employee.employee_id
join staff
on employee.employee_id=staff.employee_id ;
------ staff who are students with full attributes ------
select staff.employee_id, first_name, last_name, date_hired, job_title, gpa, date_admitted
from person
join employee
on person_id=employee.employee_id
join staff
on employee.employee_id=staff.employee_id
join student
on student_id = person_id ;