Basis Data Modul 11 (Query)

1.
CREATE TABLE DEPT1 (
    id NUMBER (7)
      CONSTRAINT dept1_id_pk PRIMARY KEY
      CONSTRAINT dept1_id_nn NOT NULL
    , name VARCHAR2 (25)
      CONSTRAINT dept1_name_nn NOT NULL);
desc dept1;


2.
INSERT INTO dept1 (id, name)
  SELECT department_id, department_name
  FROM departments;
 
  select *
from dept1;


3.
CREATE TABLE EMP1 (
    id NUMBER (7)
      CONSTRAINT emp1_id_pk PRIMARY KEY
    , last_name VARCHAR2 (25)
    , first_name VARCHAR2 (26)
    , dept_id NUMBER (7)
         CONSTRAINT emp1_dept_fk  REFERENCES
             dept1 (id));            
desc emp1;


4.
ALTER TABLE emp1
MODIFY (first_name VARCHAR2 (50));
desc emp1;


5.
CREATE TABLE EMP2 (
    id NUMBER (6)
      CONSTRAINT e_id_pk PRIMARY KEY
      CONSTRAINT e_id_nn NOT NULL
    , first_name VARCHAR2 (20)
    , last_name VARCHAR2 (25)
    , salary NUMBER (8,2)
    , dept_id NUMBER (4)
         CONSTRAINT e_dept_fk  REFERENCES
             departments (department_id));
desc EMP2;


6.
drop table emp1;
desc emp1;


7.
RENAME emp2 to staff;
desc emp2;
desc staff;


8.
ALTER TABLE staff
drop column first_name;
desc TABLE staff;


9.
CREATE TABLE employees2 (
    employee_id NUMBER (6)
      CONSTRAINT emp2_id_pk  PRIMARY KEY
    , first_name VARCHAR2 (20)
    , last_name VARCHAR2 (25)
      CONSTRAINT emp2_last_name_nn NOT NULL
    , email VARCHAR2 (25)
      CONSTRAINT emp2_email_nn NOT NULL
      CONSTRAINT emp2_email_uk UNIQUE
    , phone_number VARCHAR2 (20)
    , hire_date DATE
      CONSTRAINT emp2_hire_date_nn NOT NULL
    , job_id VARCHAR2 (10)
      CONSTRAINT emp2_job_nn NOT NULL
    , salary NUMBER (8,2)
      CONSTRAINT emp2_salary_ck CHECK (salary>0)
    , commission_pct NUMBER (2,2)
    , manager_id NUMBER (6)
    , department_id NUMBER (4)
      CONSTRAINT emp2_dept_fk REFERENCES
        departments (department_id));
desc employees2;


10.
CREATE TABLE DEPARTMENTS2 (
    department_id NUMBER (10)
      CONSTRAINT d_id_pk PRIMARY KEY
      CONSTRAINT d_id_nn NOT NULL
    , department_name VARCHAR2 (30)
      CONSTRAINT d_name_nn NOT NULL
    , manager_id NUMBER (10)
      CONSTRAINT d_man_id_fk REFERENCES
          employees2 (employee_id)
    , location_id NUMBER (10)
      CONSTRAINT d_loc_id_nn NOT NULL);
desc DEPARTMENTS2;



11.
drop table employees2;
desc employees2;


12.
drop table departments2;
desc departments2;


13.
drop table employees2;
desc employees2;

NB: mungkin Anda bingung akan hasil tampilannya...
ini contoh capture dari salah satu Query di atas...

Basis Data Modul 10 (Query)

create table my_employees (id number(4)
                          constraint emp_id_pk PRIMARY KEY
                          ,first_name Varchar2(20)
                          ,last_name varchar2(25)
                          ,userid varchar2(8)
                          ,salary number(8,2));                         




desc my_employees;


insert into my_employees
values (1, 'Ralph', 'Patel', 'rpatel', 895);


select *
from my_employees;


insert into my_employees
values (2, 'Betty', 'Dance', 'bdance', 860);


select *
from my_employees;


insert into my_employees
values (3, 'Ben', 'Biri', 'bbiri', 1100);


insert into my_employees
values (4, 'Chad', 'Newman', 'cnewman', 750);


insert into my_employees
values (5, 'Audrey', 'Ropeburn', 'aropebur', 1550);


select *
from my_employees;


UPDATE my_employees
set last_name = 'Drexler'
where id = 3;


select *
from my_employees;


UPDATE my_employees
set salary = 1000
where salary < 900;


select *
from my_employees;


DELETE from my_employees
WHERE first_name = 'Betty';


SELECT *
from my_employees;

nb: jika anda memakai SQL Express, maka harus di setting terlebih dahulu...!!

Basis Data Modul 9 (Query)

select last_name, hire_date
from employees
where department_id = (select department_id
                       from employees
                       where last_name = 'Zlotkey')
and last_name <> 'Zlotkey'
order by last_name;


select employee_id, last_name, salary
from employees
where salary > (select avg(salary) FROM employees)
order by salary asc;


select employee_id, last_name
from employees
where department_id in (select department_id
                       from employees
                       where last_name like '%u%');


select e.last_name, e.department_id, e.job_id
from employees e
where e.department_id in (select department_id
                          FROM departments
                          where location_id = 1700);


select e.last_name, e.salary
from employees e
where employee_id in (select manager_id
                    from employees
                    where last_name = 'King');


select department_id, last_name, job_id
from employees
where department_id in (select department_id
                        from departments
                        where department_name = 'Executive');

Basis Data Modul 7 (Query)

select location_id, street_address, city, state_province, country_name
from locations
natural join countries;

select e.last_name, department_id, d.department_name
from employees e JOIN departments d
using(department_id);

select e.last_name, e.department_id, d.department_id,
d.department_name
from employees e JOIN departments d
ON(e.department_id = d.department_id);

select last_name, job_id, department_id, department_name
from employees
natural join departments
where location_id = 1800;

select e.last_name, e.job_id, department_id, d.department_name
from employees e join departments d
using(department_id)
where location_id = 1800;

select e.last_name, e.job_id, e.department_id, d.department_id,
d.department_name
from employees e join departments d
on(e.department_id = d.department_id)
where location_id = 1800;

select e.last_name, e.employee_id EMP#, m.last_name,
m.manager_id Mgr#
from employees e join employees m
on(e.manager_id = m.employee_id);

select e.last_name, d.department_name, d.location_id,
l.location_id, l.city
from departments d
join employees e
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id
where commission_pct is not null;

Basis Data Modul 8 (Query)

select max (salary), min(salary), sum(salary), round(avg(salary))
from employees;

select job_id, max (salary), min(salary), sum(salary), round(avg(salary))
from employees
group by job_id;

select job_id, count(last_name)
from employees
group by job_id;

select count(DISTINCT(manager_id))"Jumlah Manager"
from employees;

select max (salary)- min (salary) "Perbedaan Gaji"
from employees;

select manager_id, min (salary)
from employees
group by manager_id
having min(salary)>6000
order by min(salary)asc;

select count(employee_id)"total",
sum(decode(to_char(hire_date , 'yyyy'),1995,1,0)) "1995",
sum(decode(to_char(hire_date , 'yyyy'),1996,1,0)) "1996",
sum(decode(to_char(hire_date , 'yyyy'),1997,1,0)) "1997",
sum(decode(to_char(hire_date , 'yyyy'),1998,1,0)) "1998"
from employees
where to_char (hire_date, 'yyyy') in('1995','1996','1997','1998');

select job_id,
nvl (to_char(sum(decode(department_id ,20, salary))),' ') "Dept 20",
nvl (to_char(sum(decode(department_id ,50, salary))),' ') "Dept 50",
nvl (to_char(sum(decode(department_id ,80, salary))),' ') "Dept 80",
nvl (to_char(sum(decode(department_id ,90, salary))),' ') "Dept 90",
sum(salary)"Total"
from employees
group by job_id
order by job_id asc;