1 Pertemuan 1 : Silabus dan aturan perkuliahan
- Baca Sendiri Di Modul
1.1 Silabus dan aturan perkuliahan (materi sendiri)
- Baca Sendiri Di Modul
2 Pertemuan 2 : Skema Relasi & SELECT
2.1 Skema Relasi HR (materi sendiri)
- Baca Sendiri Di Modul
2.2 DP Database Programming with SQL Learner - Indonesian (Oracle Academy)
- Baca Sendiri Di Modul
2.2.1 DP 1-3: Anatomy of a SQL Statement
* Contoh SQL :
Untuk menampilkan kolum berdasankan table
SELECT <column_name> FROM <table_name>;
Untuk menghitung cara langsung aritmatika bisa : * / - +
Jika dia bernilai null maka akan - / null
SELECT last_name, salary, salary + 300 FROM employees
Untuk Membuat alias
SELECT last_name, salary, salary + 300 as "Gaji tambah 300" FROM employees;
Untuk Deskripsi Table Melihat Informasi type Data panjang dll
DESC departments;
Untuk Mengabungkan Kata / Kolumn Dengan Concat
Select department_id ||' ' ||department_name from departments;
SELECT last_name || ' has a monthly
salary of ' || salary || '
dollars.' AS Pay
FROM employees;
Agar tidak terjadi duplikasi maka bisa menggunakan DISTINCT
SELECT DISTINCT department_id
FROM employees;
2.2.2 DP 2-1: Columns, Characters and Rows
3 Pertemuan 3 : Selection & Sorting
3.1 DP Database Programming with SQL Learner - Indonesian (Oracle Academy)
3.1.1 DP 2-2: Limit Rows Selected
Untuk membatasi atau mencari dengan kata kunci tertentu bisa menggunakan :
SELECT employee_id, first_name,
last_name
FROM employees
WHERE employee_id = 101;
Untuk Operator where bisa : = > = < <= <>
3.1.2 DP 2-3: Comparison Operators
Untuk mencari data diantara bisa menggunakan > < atau lebih mudah dengan Between
Select last_name, salary from employees where salary BETWEEN 9000 and 11000;
Untuk Mencari Data Yang Menggunakan OR Bisa menggunakan Opsi Lain Dengan IN
Select city, state_province, country_id from locations where country_id IN('UK', 'CA');
Untuk mencari kata semisal 1 huruf bebas dan dilanjut O dan setelah O bebas maka bisa menggunakan
Select last_name from employees where last_name LIKE '_o%';
Untuk mencari sesuatu tetapi misalkan valuenya terdapat simbol "_" maka bisa menggunakan misalkan mencari _R% maka bisa
Select last_name, job_id from employees where like '%\_R' ESCAPE '\';
Untuk mencari data yang null bisa menggunakan
Select last_name, manager_id, from employees where manager_id IS NULL;
Untuk mencari data yang tidak null bisa menggunakan
Select last_name, manager_id, from employees where manager_id NOT NULL;
3.1.3 DP 3-1: Logical Comparisons and Precedence Rules
Untuk mencari data yang membutuhkan operator perbandingan dan harus terpenuhi keduanya bisa menggunakan AND
Select last_name,hire_date,job_id from employees where hire_date > '01-Jan-1998' AND job_id Like 'SA%';
Untuk mencari data yang membutuhkan operator perbandingan dan salah satunya terpenuhi bisa menggunakan OR
Select last_name,hire_date,job_id from employees where hire_date > '01-Jan-1998' OR job_id Like 'SA%';
Untuk mencari data yang tidak harus memiliki value diantaranya selain bisa menggunakan or bisa menggunakan NOT IN
Select last_name, manager_id, from employees where location_id NOT IN (1700,1800);
3.1.4 DP 3-2: Sorting Rows
Untuk mengurutkan data berdasarkan data terkecil keterbesar bisa dikosongkan secara default atau titambah Order by <column_name> asc;
Select last_name, manager_id, from employees order by hire_date;
Untuk mengurutkan data berdasarkan data terbesar keterkcil
Select last_name, manager_id, from employees order by hire_date desc;
Untuk Multi Penguurutan mengurutkan data berdasarkan data terbesar keterkcil
Select last_name, manager_id, from employees order by hire_date desc, last_name asc;
4 Pertemuan 4 : Function & Single Row Functions
4.1 DP Database Programming with SQL Learner - Indonesian (Oracle Academy)
Dual DUMMY :
SELECT (319/29) + 12
FROM DUAL;
4.1.1 DP 3-3: Introduction to Functions
4.1.2 DP 4-1: Case and Character Manipulation
Case-Manipulation Functions
- LOWER
SELECT last_name FROM employees WHERE LOWER(last_name) = 'abel';
- UPPER
SELECT last_name FROM empl oyees WHERE UPPER(last_name) = 'ABEL';
- INITCAP
SELECT last_name FROM employees WHERE INITCAP(last_name) = 'Abel';
Character-Manipulation Functions
- CONCAT
SELECT CONCAT('Hello', 'World')
FROM DUAL;
SELECT CONCAT(first_name, last_name) FROM employees;
- SUBSTR
SELECT SUBSTR('HelloWorld', 1, 5)
FROM DUAL;
- LENGTH
SELECT LENGTH('HelloWorld')
FROM DUAL;
- INSTR
SELECT INSTR('HelloWorld', 'W')
FROM DUAL;
SELECT last_name, INSTR(last_name, 'a')
FROM employees;
- LPAD | RPAD
SELECT LPAD('HelloWorld', 15, '-')
FROM DUAL;
SELECT LPAD(last_name, 10, '*')
FROM employees;
SELECT RPAD('HelloWorld', 15, '-')
FROM DUAL;
SELECT RPAD(last_name, 10, '*')
FROM employees;
- TRIM
SELECT TRIM(LEADING 'a' FROM 'abcba')
FROM DUAL;
SELECT TRIM(TRAILING 'a' FROM 'abcba')
FROM DUAL;
SELECT TRIM(BOTH 'a' FROM 'abcba')
FROM DUAL;
- REPLACE
SELECT REPLACE('JACK and JUE', 'J', 'BL')
FROM DUAL;
SELECT REPLACE('JACK and JUE', 'J')
FROM DUAL;
SELECT REPLACE(last_name, 'a', '*')
FROM employees;
Input Manual :
SELECT first_name, last_name, salary, department_id
FROM employees
WHERE department_id=:enter_dept_id;
4.1.3 DP 4-2: Number Functions
ROUND(column|expression, decimal places)
TRUNC(column|expression, decimal places)
MOD(column|expression, 2)
4.1.4 DP 4-3: Date Functions
SELECT SYSDATE FROM DUAL;
Beberapa Fungsi Terkait Date
- MONTHS_BETWEEN
- ADD_MONTHS
- NEXT_DAY
- LAST_DAY
- ROUND(tanggal, 'Month/Year')
- TRUNC(tanggal, 'Month/Year);
4.1.5 DP 5-1: Conversion Functions
- TO_NUMBER(character string, 'format model')
SELECT TO_NUMBER('5,320', '9,999')
AS "Number"
FROM dual; - TO_DATE('November 3, 2001', 'Month dd, yyyy')
SELECT TO_DATE('May10,1989', 'fxMonDD,YYYY') AS "Convert"
FROM DUAL;
SELECT TO_DATE('Sep 07, 1965', 'fxMon dd, YYYY') AS "Date"
FROM dual;
07-Sep-1965
SELECT TO_DATE('July312004', 'fxMonthDDYYYY') AS "Date"
FROM DUAL;
31-Jul-2004
SELECT TO_DATE('June 19, 1990','fxMonth dd, YYYY') AS "Date"
FROM DUAL;
19-Jun-1990
SELECT TO_DATE('27-Oct-95','DD-Mon-YY')
AS "Date"
FROM dual; - TO_CHAR (date column name, 'format model you specify')
SELECT TO_CHAR(hire_date, 'Month dd, YYYY')
FROM employees;
June 07, 1994
SELECT TO_CHAR(hire_date, 'fmMonth dd, YYYY')
FROM employees;
June 7, 1994
SELECT TO_CHAR(hire_date, 'fmMonth ddth, YYYY')
FROM employees;
June 7th, 1994
January 3rd, 1990
SELECT TO_CHAR(hire_date, 'fmDay ddth Mon,
YYYY')
FROM employees;
Tuesday 7th Jun, 1994
SELECT TO_CHAR(hire_date, 'fmDay ddthsp
Mon, YYYY')
FROM employees;
Tuesday, seventh Jun,
1994
SELECT TO_CHAR(hire_date, 'fmDay, ddthsp
"of" Month, Year')
FROM employees;
Tuesday, seventh of
June, Nineteen
Ninety-Four
SELECT TO_CHAR(SYSDATE, 'hh:mm')
FROM dual;
02:07
SELECT TO_CHAR(SYSDATE, 'hh:mm pm')
FROM dual;
02:07 am
SELECT TO_CHAR(SYSDATE, 'hh:mm:ss pm')
FROM dual;
02:07:23 am
4.1.6 DP 5-2: NULL Functions
Misalkan terdapat data null dan data null tersebut harus berisi value maka bisa menggunakan :
SELECT country_name, NVL(internet_extension, 'None')
AS "Internet extn"
FROM wf_countries
WHERE location = 'Southern Africa'
ORDER BY internet_extension DESC;
SELECT last_name, salary,
NVL2(commission_pct, salary + (salary * commission_pct),
salary)
AS income
FROM employees
WHERE department_id IN(80,90);
SELECT first_name, LENGTH(first_name) AS "Length FN", last_name,
LENGTH(last_name) AS "Length LN", NULLIF(LENGTH(first_name),
LENGTH(last_name)) AS "Compare Them"
FROM employees;
SELECT last_name,
COALESCE(commission_pct, salary, 10)
AS "Comm"
FROM employees
ORDER BY commission_pct;
4.1.7 DP 5-3: Conditional Expressions
SELECT last_name,
CASE department_id
WHEN 90 THEN 'Management'
WHEN 80 THEN 'Sales'
WHEN 60 THEN 'It'
ELSE 'Other dept.'
END AS "Department"
FROM employees;
SELECT last_name,
DECODE(department_id,
90, 'Management',
80, 'Sales',
60, 'It',
'Other dept.')
AS "Department"
FROM employees;
5 Pertemuan 5 : Multiple Row Functions (Group Functions)
5.1 DP Database Programming with SQL Learner - Indonesian (Oracle Academy)
5.1.1 DP 8-1: Group Functions
Daftar Group Yang Fungsi
- AVG
- COUNT
- MIN
- MAX
- SUM
- VARIANCE
- STDDEV
5.1.2 DP 8-2: COUNT, DISTINCT, NVL
SELECT COUNT(job_id)
FROM employees;
SELECT COUNT(*)
FROM employees
WHERE hire_date < '01-Jan-1996';
SELECT SUM(DISTINCT salary)
FROM employees
WHERE department_id = 90;
5.1.3 DP 9-1: Using Group By and Having Clauses
SELECT COUNT(country_name), region_id
FROM wf_countries
GROUP BY region_id
ORDER BY region_id;
SELECT department_id, job_id,
count(*)
FROM employees
WHERE department_id > 40
GROUP BY department_id, job_id;
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING COUNT(*)>1
ORDER BY department_id;
SELECT region_id,
ROUND(AVG(population))
FROM wf_countries
GROUP BY region_id
HAVING MIN(population)>300000
ORDER BY region_id;
5.1.4 DP 9-2: Using Rollup and Cube Operations and Grouping Sets
Gk tau blm dicoba
6 Pertemuan 6 : Join
6.1 DP Database Programming with SQL Learner - Indonesian (Oracle Academy)
6.1.1 DP 6-1: Cross Joins and Natural Joins
SELECT first_name, last_name, job_id, job_title
FROM employees NATURAL JOIN jobs
WHERE department_id > 80;
SELECT last_name, department_name
FROM employees CROSS JOIN
departments;
6.1.2 DP 6-2: Join Clauses
SELECT first_name, last_name, department_id, department_name
FROM employees JOIN departments USING (department_id);
SELECT first_name, last_name, department_id, department_name
FROM employees JOIN departments USING (department_id)
WHERE last_name = 'Higgins';
SELECT last_name, job_title
FROM employees e JOIN jobs j
ON (e.job_id = j.job_id);
SELECT last_name, salary, grade_level, lowest_sal,
highest_sal
FROM employees JOIN job_grades
ON(salary BETWEEN lowest_sal AND highest_sal);
SELECT last_name, department_name AS "Department", city
FROM employees JOIN departments USING (department_id)
JOIN locations USING (location_id);
6.1.3 DP 6-3: Inner versus Outer Joins
SELECT e.last_name, d.department_id,
d.department_name
FROM employees e LEFT OUTER JOIN
departments d
ON (e.department_id =
d.department_id);
SELECT e.last_name, d.department_id,
d.department_name
FROM employees e RIGHT OUTER JOIN
departments d
ON (e.department_id =
d.department_id);
SELECT e.last_name, d.department_id, d.department_name
FROM employees e FULL OUTER JOIN departments d
ON (e.department_id = d.department_id);
SELECT last_name, e.job_id AS "Job", jh.job_id AS "Old job",
end_date
FROM employees e LEFT OUTER JOIN job_history jh
ON(e.employee_id = jh.employee_id);
6.1.4 DP 6-4: Self-Joins and HIerarchical Queries
SELECT worker.last_name, worker.manager_id, manager.last_name
AS "Manager name"
FROM employees worker JOIN employees manager
ON (worker.manager_id = manager.employee_id);
SELECT employee_id, last_name, job_id, manager_id
FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
SELECT LEVEL, last_name ||
' reports to ' ||
PRIOR last_name
AS "Walk Top Down"
FROM employees
START WITH last_name = 'King'
CONNECT BY PRIOR
employee_id = manager_id;
SELECT LPAD(last_name, LENGTH(last_name)+
(LEVEL*2)-2,'_') AS "Org_Chart"
FROM employees
START WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id;
SELECT LPAD(last_name, LENGTH(last_name) +
(LEVEL*2)-2, '_') AS ORG_CHART
FROM employees
START WITH last_name = 'Grant'
CONNECT BY employee_id = PRIOR manager_id
6.1.5 DP 7-1: Oracle Equijoin and Cartesian Products
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
SELECT employees.last_name, departments.department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;
SELECT last_name, e.job_id, job_title
FROM employees e, jobs j
WHERE e.job_id = j.job_id
AND department_id = 80;
SELECT employees.last_name, departments.department_name
FROM employees, departments;
SELECT employees.last_name, employees.job_id, jobs.job_title
FROM employees, jobs
WHERE employees.job_id = jobs.job_id
AND employees.department_id = 80;
SELECT last_name, city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;
6.1.6 DP 7-2: Oracle Nonequijoins and Outer Joins
SELECT last_name, salary, grade_level, lowest_sal,
highest_sal
FROM employees, job_grades
WHERE (salary BETWEEN lowest_sal AND highest_sal);
SELECT e.last_name,
d.department_id,
d.department_name
FROM employees e, departments d
WHERE e.department_id =
d.department_id(+);
SELECT e.last_name,
d.department_id,
d.department_name
FROM employees e, departments d
WHERE e.department_id(+) =
d.department_id;
7 Pertemuan 7 : Subquery & Set Operators
7.1 DP Database Programming with SQL Learner - Indonesian (Oracle Academy)
7.1.1 DP 10-1: Fundamentals of Subqueries
7.1.2 DP 10-2: Single-Row Subqueries
7.1.3 DP 10-3: Multiple-Row Subqueries
7.1.4 DP 10-4: Correlated Subqueries
7.1.5 DP 9-3: Using Set Operators
--SElECT * FROM countries;
--SELECT country_name FROM countries;
--SELECT employee_id FROM employees where last_name LIKE '%a%';
--select last_name, salary, 12*salary+10 from employees;
--select last_name, job_id, salary, commission_pct, salary*commission_pct as "Mantep" from employees;
--DESC departments;
--Select department_id ||' '|| department_name from departments;
--SELECT last_name || ' has a monthly salary of ' || salary || ' dollars.' AS "as" from employees;
-- Select last_name || ' Hallo '|| salary from employees;
--Select DISTINCT department_id from departments;
--select * from employees where employee_id = 101;
--select * from employees where last_name = 'Taylor';
--select last_name, salary from employees where salary BETWEEN 1000 AND 1000000;
--select city, state_province, country_id from locations where country_id IN('UK', 'CA');
--select last_name from employees where last_name LIKE '__o%';
--select last_name, job_id from employees where job_id like '%\_R%' ESCAPE '\';
--select last_name, job_id from employees where job_id like '_R%';
--Select last_name,salary from employees where salary is not null;
--select last_name,salary,commission_pct from employees where commission_pct is null;
--select last_name,department_id from employees where department_id > 50 AND salary < 12000;
--select last_name, TO_CHAR(hire_date, 'dd-mm-YYYY'), job_id from employees where hire_date > '01-Jan-1998' AND job_id LIKE 'SA%';
--select * from departments where location_id = 2500 OR manager_id =124;
--select * from departments where location_id NOT IN(1700,1800);
--select last_name||' '||salary*1.06 AS "Pegawai dan gaji", department_id, first_name
--from employees where department_id IN(50,80) AND first_name like 'C%' OR last_name like '%s';
--select employee_id, last_name as "test" from employees order by employee_id desc, "test" asc;
--Select (319/29) + 12 from dual;
--select UPPER(last_name), INITCAP(first_name) ||' '|| INITCAP(last_name) as "nama_lengkap" from employees where LOWER(last_name) = 'abel';
--select concat('Hello', 'World') from dual;
--select substr('Aingmaung', 1,5) from dual;
--select substr('Aingmaung', 5) from dual;
--select substr(last_name,1,4) from employees;
--select length(last_name) from employees;
-- instr untuk mencari huruf w
--select instr('HelloWord', 'W') from dual;
--select lpad('HellowWorld',100,'*') from dual;
--trim untuk menghapus
-- LEADING DIDEPAn
-- TRAILING DIBELAKANG
-- BOTH SEMUA
--select trim(BOTH 'L' from UPPER(last_name)) from employees;
-- replace untuk mengganti
--select replace(UPPER(last_name), 'AB', 'JULIANTO') from employees;
--select lower(last_name) || lower(substr(first_name,1,1)) as "User Name" from employees;
--select lower(last_name) || lower(substr(first_name,1,1)) as "User Name" from employees where department_id = 10;
--select lower(last_name) || lower(substr(first_name,1,1)) as "User Name" from employees where department_id =:enter_dept_id
--Select round(45.925,2) from dual; //45.93
--select round(45.925,-1) from dual; //50
-- Turun
--select trunc(45.925,2) from dual; //45.92
--select trunc(45.925,-1) from dual; //40
--select MOD(10,2) from dual;
--select sysdate from dual;
--select sysdate+10 from dual;
--select last_name, (sysdate-hire_date)/7 from employees;
--select employee_id, (end_date-start_date)/365 as "Tenure in last" from job_history;
--cari orang yang telah bekerja lebih dari 240 bulan
--select last_name, hire_date from employees where MONTHS_BETWEEN(SYSDATE,hire_date) > 240;
--select ADD_MONTHs(SYSDATE, 12) from dual
--select NEXT_DAY(SYSDATE,'SATURDAY') As "next Sabtu" from dual;
--select last_day(sysdate) from dual;
--
--select hire_date, round(hire_date,'Month'), trunc(hire_date, 'Month') from employees;
--select employee_id, hire_date,
-- ROUND(MONTHS_BETWEEN(SYSDATE,hire_date)) as "TENURE",
-- ADD_MONTHS(hire_date,6) as REVIEW,
-- NEXT_DAY(hire_date,'FRIDAY'),
-- LAST_DAY(hire_date)
--from employees where months_between(SYSDATE, hire_date) > 36;
--select to_char(hire_date, 'Month dd, YYYY') from employees; //June 07, 1994
--select to_char(hire_date,'fmMonth dd, YYYY') from employees; // June 7, 1994
--select to_char(hire_date, 'fmMonth ddth, YYYY') from employees; // June 7th, 1993
--select to_char(hire_date,'fmDay ddth Mon, YYYY') from employees; // Monday 17th Jun, 2023
--select to_char(hire_date, 'fmDay ddthsp Mon, YYYY') from employees; // Monday seventeenth Jun, 2023
--select to_char(hire_date, 'fmDay, ddthsp "of" Month, Year') from employees; // Monday, seventeen of june, Twenty Thirteen
--select to_char(SYSDATE,'hh:mm'),to_char(SYSDATE,'hh:mm pm'),to_char(SYSDATE,'hh:mm:ss pm') from dual;
--select to_char(salary, '$99,999') as "Gaji", to_char(salary, '99999.99') as "gajubulat" from employees; // $24.000
--select to_number('5,424', '9,999') as "Number" from dual;
--select last_name, to_char(salary,'999,9') from employees where department_id = 80;
--select to_date('November 3, 2001', 'Month dd, yyyy') from dual;
--select to_date('May10, 1989', 'fxMonDD, YYYY') as "Convert" from dual;
--select to_date('Sep 07, 1965', 'fxMon dd, YYYY') as "Date", to_date('July312004', 'fxMonthDDYYYY'), to_date('June 19, 1998', 'fxMonth dd, YYYY') from dual;
--select to_date('27-Oct-95', 'DD-Mon-YYYY') ,to_date('27-Oct-95', 'DD-Mon-RR')from dual;
--select last_name, to_char(hire_date, 'DD-Mon-YY') from employees where hire_date < to_date('01-Jan-90', 'DD-Mon-YY');
--select last_name, nvl(commission_pct,0) from employees;
--select last_name, salary, nvl2(commission_pct, salary +(salary*commission_pct), salary) as "income" from employees;
--select first_name,
-- length(first_name) as "Panajng FN",
-- last_name,
-- nullif(length(first_name),length(last_name)) as"compere"
--from employees;
--select last_name,
-- COALESCE(commission_pct,salary,10)
-- as "Com"
--from employees
--ORDER BY commission_pct;
--select last_name,
-- case department_id
-- when 90 THEN 'Management'
-- when 80 THEN 'Sales'
-- else 'Other'
-- end as "department"
--from employees;
--select last_name,
-- DECODE(department_idselect last_name,
-- case department_id
-- when 90 THEN 'Management'
-- when 80 THEN 'Sales'
-- else 'Other'
-- end as "department"
--from employees;select last_name,
-- case department_id
-- when 90 THEN 'Management'
-- when 80 THEN 'Sales'
-- else 'Other'
-- end as "department"
--from employees;select last_name,
-- case department_id
-- when 90 THEN 'Management'
-- when 80 THEN 'Sales'
-- else 'Other'
-- end as "department"
--from employees;,
-- 90, 'Management',
-- 80, 'Sales',
-- 'Other')
--from employees;
--select department_id, avg(salary) from employees group by department_id order by department_id;employees
--employees
.png)
.png)
