contoh dalam pengerjaan SQL

Disini terdapat contoh perintah-perintah dalam SQL beserta pengerjaannya

Tampilkan semua data emp

Mysql  > Select* from emp;

Output

empno	empname	job	hiredate	sal	comm	deptno
7369	Smith	Clerk	1980-12-17	1000	0	20
7499	Allen	Salesman	1981-02-20	1600	300	30
7521	Wards	Salesman	1981-02-22	1250	500	30
7566	Jones	Manager	1981-04-02	2975	0	20
7654	Martin	Salesman	1981-09-28	1250	1400	30
7698	Blake	Manager	1981-03-01	2850	0	30
7782	Scott	Manager	1981-06-09	2450	0	30
7788	Clark	Analyzt	1981-04-19	3000	0	20
7839	king	President	1981-10-17	5000	0	10
7844	Turner	Salesman	1981-09-08	1500	0	30

Tampilkan semua data emp yang bekerja (job) sebagai manager

Mysql > select* from emp where job=’manager’;

empno	empname	job	hiredate	sal	comm	deptno
7566	Jones	Manager	1981-04-02	2975	0	20
7698	Blake	Manager	1981-03-01	2850	0	30
7782	Scott	Manager	1981-06-09	2450	0	30

Mengurutkan data dari bawah ke atas berdasarkan “empname”

Mysql > select* from emp order by empname desc;

empno	empname	job	hiredate	sal	comm	deptno
7521	Wards	Salesman	1981-02-22	1250	500	30
7844	Turner	Salesman	1981-09-08	1500	0	30
7369	Smith	Clerk	1980-12-17	1000	0	20
7782	Scott	Manager	1981-06-09	2450	0	30
7654	Martin	Salesman	1981-09-28	1250	1400	30
7839	king	President	1981-10-17	5000	0	10
7566	Jones	Manager	1981-04-02	2975	0	20
7788	Clark	Analyzt	1981-04-19	3000	0	20
7698	Blake	Manager	1981-03-01	2850	0	30
7499	Allen	Salesman	1981-02-20	1600	300	30

Mengurutkan dari atas ke bawah berdasarkan “empname”

Mysql > select *from emp order by empname asc;

empno	empname	job	hiredate	sal	comm	deptno
7499	Allen	Salesman	1981-02-20	1600	300	30
7698	Blake	Manager	1981-03-01	2850	0	30
7788	Clark	Analyzt	1981-04-19	3000	0	20
7566	Jones	Manager	1981-04-02	2975	0	20
7839	king	President	1981-10-17	5000	0	10
7654	Martin	Salesman	1981-09-28	1250	1400	30
7782	Scott	Manager	1981-06-09	2450	0	30
7369	Smith	Clerk	1980-12-17	1000	0	20
7844	Turner	Salesman	1981-09-08	1500	0	30
7521	Wards	Salesman	1981-02-22	1250	500	30

Mengurutkan dari bawah ke atas berdasarkan “empno”

Mysql> select* from emp order by empno desc;

empno	empname	job	hiredate	sal	comm	deptno
7844	Turner	Salesman	1981-09-08	1500	0	30
7839	king	President	1981-10-17	5000	0	10
7788	Clark	Analyzt	1981-04-19	3000	0	20
7782	Scott	Manager	1981-06-09	2450	0	30
7698	Blake	Manager	1981-03-01	2850	0	30
7654	Martin	Salesman	1981-09-28	1250	1400	30
7566	Jones	Manager	1981-04-02	2975	0	20
7521	Wards	Salesman	1981-02-22	1250	500	30
7499	Allen	Salesman	1981-02-20	1600	300	30
7369	Smith	Clerk	1980-12-17	1000	0	20

Mengurutkan dari atas ke bawah berdasarkan “empno”

Mysql> select*from emp order by empno asc;

empno	empname	job	hiredate	sal	comm	deptno
7369	Smith	Clerk	1980-12-17	1000	0	20
7499	Allen	Salesman	1981-02-20	1600	300	30
7521	Wards	Salesman	1981-02-22	1250	500	30
7566	Jones	Manager	1981-04-02	2975	0	20
7654	Martin	Salesman	1981-09-28	1250	1400	30
7698	Blake	Manager	1981-03-01	2850	0	30
7782	Scott	Manager	1981-06-09	2450	0	30
7788	Clark	Analyzt	1981-04-19	3000	0	20
7839	king	President	1981-10-17	5000	0	10
7844	Turner	Salesman	1981-09-08	1500	0	30

Menampilkan “job” dimana “job” yang sama hanya ditampilkan satu kali saja

Mysql> select distinct job from emp;

job

Clerk

Salesman

Manager

Analyzt

President

Menampilkan job dan jumlah emp yang mempunyai job tersebut

Mysql> select job,count(*) from emp group by job ;

job	Count (*)
Analyst	1
Clerk	1
Manager	3
president	1
salesman	4

Menampilkan rata-rata sal

Mysql> select avg(sal) from emp;

Avg (sal)

2287,5

Menampilkan hiredate terkecil

Mysql> select min(hiredate) from emp;

Min (hiredate)

1980-12-17

Menampilkan hiredate terbesar

Mysql> select max(hiredate) from emp;

Max (hiredate)

1981-10-17

Menampilkan empname,job,hiredate dengan urutan dari atas ke bawah berdasarkan hiredate

Mysql> select empname,job,hiredate from emp order by hiredate asc;

empname	job	hiredate
Smith	Clerk	1980-12-17
Allen	Salesman	1981-02-20
Wards	Salesman	1981-02-22
Blake	Manager	1981-03-01
Jones	Manager	1981-04-02
Clark	Analyzt	1981-04-19
Scott	Manager	1981-06-09
Turner	Salesman	1981-09-08
Martin	Salesman	1981-09-28
king	President	1981-10-17

Menampilkan emp dimana empno lebih dari sama dengan 7745

Mysql > select * from emp where empno>=’7745’

empno	empname	job	hiredate	sal	comm	deptno
7782	Scott	Manager	1981-06-09	2450	0	30
7788	Clark	Analyzt	1981-04-19	3000	0	20
7839	king	President	1981-10-17	5000	0	10
7844	Turner	Salesman	1981-09-08	1500	0	30

Menampilkan job dan jumlah emp yang bekerja sebagai “job”tersebut yang jumlahnya lebih dari satu

Mysql> select job,count(*) from emp group by job having count (*)>1;

job	Count(*)
Manager	3
salesman	4

Menampilkan nama yang diawali dengan huruf “S”

Mysql> select empname from emp where empname like ‘S%’;

Empname

Smith

scott

Menampilkan data dari emp dimana depno sama dengan 30

Mysql> select*  from emp where deptno=’30’;

empno	empname	job	hiredate	sal	comm	deptno
7499	Allen	Salesman	1981-02-20	1600	300	30
7521	Wards	Salesman	1981-02-22	1250	500	30
7654	Martin	Salesman	1981-09-28	1250	1400	30
7698	Blake	Manager	1981-03-01	2850	0	30
7782	Scott	Manager	1981-06-09	2450	0	30
7844	Turner	Salesman	1981-09-08	1500	0	30