STA261 - Manajemen Data Relational - Praktikum 7

Laily Nissa Atul Mualifah1

Abdul Aziz Nurussadad2

2021-10-03

Sintaks SQL

Silahkan gunakan sintaks SQL berikut ini, untuk menjawab Pertanyaan-pertanyaan di praktikum 7.

DROP TABLE if EXISTS DEPENDENT;
DROP TABLE IF EXISTS Project;
DROP TABLE IF EXISTS Works_On;
DROP TABLE IF EXISTS Dept_Locations;
DROP TABLE IF EXISTS EMPLOYEE;
DROP TABLE if EXISTS Department;

CREATE TABLE DEPARTMENT(
  Dname         VARCHAR(15) NOT NULL        UNIQUE,
  Dnumber   INTEGER     PRIMARY KEY     CHECK (Dnumber <>0),
  Mgr_ssn   CHAR(9)     NOT NULL,
  Mgr_start_date    DATE        DEFAULT CURRENT_DATE);

CREATE TABLE EMPLOYEE( 
  Fname     VARCHAR(10) NOT NULL,
  Minit     CHAR, 
  Lname     VARCHAR(20) NOT NULL,
  Ssn       CHAR(9)     PRIMARY KEY     CHECK (Ssn != 000000000),
  Bdate     DATE,
  Address   VARCHAR(30),
  Sex       CHAR(1),
  Salary    DECIMAL(5),
  Super_ssn     CHAR(9),
  Dno       INT         NOT NULL,
FOREIGN KEY (Dno)       REFERENCES Department (Dnumber) ON UPDATE CASCADE);

CREATE TABLE DEPT_LOCATIONS(
  Dnumber   INT         NOT NULL,
  Dlocation VARCHAR(15) NOT NULL,
FOREIGN KEY     (Dnumber)   REFERENCES DEPARTMENT(Dnumber) ON UPDATE CASCADE ON DELETE CASCADE);

CREATE TABLE PROJECT(
  Pname         VARCHAR(15) NOT NULL,
  Pnumber   INT         PRIMARY KEY,
  Plocation VARCHAR(15),
  Dnum      INT         NOT NULL,
UNIQUE (Pname),
FOREIGN KEY (Dnum) REFERENCES DEPARTMENT(Dnumber)ON DELETE CASCADE ON UPDATE CASCADE);

CREATE TABLE WORKS_ON(
  Essn  CHAR(9)         NOT NULL,
  Pno   INT             NOT NULL,
  Hours DECIMAL(3,1)    NOT NULL,
FOREIGN KEY (Essn)  REFERENCES EMPLOYEE(Ssn) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (Pno)   REFERENCES PROJECT(Pnumber) ON DELETE CASCADE);

CREATE TABLE DEPENDENT(
  Essn          CHAR(9)     NOT NULL,
  Dependent_name    VARCHAR(15),
  Sex           CHAR,
  Bdate         DATE,
  Relationship  VARCHAR(8)  NOT NULL    ON CONFLICT replace DEFAULT 'Family',
PRIMARY KEY (Dependent_name),
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn) ON DELETE CASCADE ON UPDATE CASCADE);

INSERT INTO DEPARTMENT
VALUES ('Research',5,333445555,'1988-05-22'),
       ('Administration',4,999557777,'1995-01-01'),
       ('Headquarters',1,888665555,'1981-06-19'),
       ('Production',3,666228888,'1980-01-31'),
       ('Accounting',6,111223333,'1981-05-01'),
       ('Warehouse',8,444882222,'1990-03-21'),
       ('Purchasing',9,777559999,'2000-04-26'),
       ('Human Resource',10,222773333,'2002-08-17');

INSERT INTO DEPARTMENT(Dname, Dnumber, Mgr_ssn)
VALUES ('Marketing',2,444221111),
       ('Information',7,552224444);

INSERT INTO DEPARTMENT
       VALUES   ('PIC',11, 444772222,?);

INSERT INTO EMPLOYEE
VALUES ('John','B','Smith',123456789,'1965-01-09','731 Fondren, Houston TX','M',30000,333445555,5);

INSERT INTO EMPLOYEE
VALUES ('Franklin','T','Wong',333445555,NULL,NULL,'M',40000,888665555,3),
       ('Alicia','J','Zelaya',999887777,'1968-01-19','3321 Castle, Spring TX','F',25000,987654321,4);

INSERT INTO EMPLOYEE(Fname, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
VALUES ('Alicia','Kim',944552200,'1962-03-28','3210 Berry, Bellaire TX','F',45000,999872224,10);

INSERT INTO EMPLOYEE (Fname, Minit, Lname,Ssn, Bdate, Dno, Salary, Address, Super_ssn, Sex)
VALUES ('Jennifer','S','Wallace',987654321,'1941-06-20',5,43000,'291 Berry, Bellaire TX',888665555,'F'),
       ('Ramesh','K','Narayan',666884444,'1962-01-15',8,38000,'975 Fire Oak, Humble TX',333445555,'M');

INSERT INTO EMPLOYEE (Fname, Minit, Lname,Ssn, Dno)
VALUES ('Joyce','A','English',453453453,5),
       ('Ahmad','V','Jabbar',987987987,6);  

INSERT INTO EMPLOYEE (Fname, Lname,Ssn, Dno, Salary, Minit, Bdate)
VALUES ('James','Borg',888665555,1,55000,'E','1963-01-10'); 

INSERT INTO EMPLOYEE(Fname, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
VALUES ('Joe','Wang',657332119,'1968-12-02','479 Square, Boston TX','M', 42000,439994666,7);

INSERT INTO PROJECT
VALUES ('ProductX',1,'Bellaire',3),
       ('ProductY',2,'Sugarland',3),
       ('ProductZ',3,'Houston',3);

INSERT INTO PROJECT (Pname, Pnumber,Dnum)
VALUES  ('Entry', 25, 8);

INSERT INTO PROJECT
VALUES ('Computerization',10,'Stafford',7),
       ('Reorganization',20,'Houston',10),
       ('Newbenefits',30,'Stafford',2);

INSERT INTO PROJECT (Pname, Pnumber,Dnum)
VALUES  ('ProductA', 11, 3);

INSERT INTO PROJECT
VALUES ('Audit',13,'Humble',6),
       ('Sampling',32,'Sugarland',5),
       ('Mapping',21,'Humble',5);

INSERT INTO WORKS_ON
VALUES (123456789,1,32.5),
       (657332119,2,7.5),
       (666884444,3,40.0),
       (453453453,1,20.0),
       (453453453,21,20.0),
       (333445555,2,10.0),
       (333445555,3,10.0),
       (944552200,13,10.0),
       (333445555,20,10.0),
       (999887777,30,30.0),
       (999887777,10,10.0),
       (987987987,10,35.0),
       (888665555,30,5.0),
       (987654321,30,20.0),
       (987654321,25,15.0),
       (888665555,20,16.0),
       (657332119,32,5.5),
       (333445555,25,13.5),
       (123456789,21,22.5);

INSERT INTO DEPENDENT
VALUES (333445555,'Alice','F','1986-04-04','Daughter'),
       (333445555,'Theodore','M','1983-10-25','Son'),
       (333445555,'Joy','F','1958-05-03',NULL);

INSERT INTO DEPENDENT (Essn, Dependent_name, Sex,Relationship)
VALUES  (657332119,'English','M','Husband');

INSERT INTO DEPENDENT
VALUES (987654321,'Abner','M',?,'Spouse'),
       (123456789,'Michael','M','1988-01-04','Son');
INSERT INTO DEPENDENT (Essn, Dependent_name, Sex, Bdate)
VALUES (123456789,'Roy','F','1988-12-30'),
       (123456789,'Elizabeth','F',NULL);

INSERT INTO DEPT_LOCATIONS
VALUES (1,'Houston'),
       (4,'Stafford'),
       (5,'Bellaire'),
       (5,'Sugarland'),
       (3,'Houston'),
       (10,'Humble'),
       (2,'Boston'),
       (7,'Stafford'),
       (7,'Houston'),
       (8,'Bellaire'),
       (6,'Humble'),
       (2,'Bellaire');

  1. IPB University,︎↩︎

  2. Badan Informasi Geospasial, ↩︎