membuat database dan menampilkan data tertentu pada my sql!!
persiapan !!
1)install program XAMPP sampai selesai
2)buka command prompt
3)ketikan tulisan seperti di bawah untuk menampilkan hasil dibawahnya pada command prompt
Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
(1)
C:\Users\4nz0n>d:
D:\>cd instalan xampp\xampp\mysql\bin
D:\instalan xampp\xampp\mysql\bin>mysql -u root -h localhost -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.33-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
(2)
mysql> create database 8017805TKJ;
Query OK, 1 row affected (0.03 sec)
(3)
mysql> use 8017805TKJ;
Database changed
mysql> create table TabelSiswa(
-> nis varchar(10) not null primary key,
-> nnsiswa varchar(50),
-> tgl_lahir date not null,
-> n_teori decimal(6,2),
-> n_praktek decimal(6,2));
Query OK, 0 rows affected (0.11 sec)
mysql> show tables;
+----------------------+
| Tables_in_8017805tkj |
+----------------------+
| tabelsiswa |
+----------------------+
1 row in set (0.00 sec)
mysql> desc tabelSiswa;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| nis | varchar(10) | NO | PRI | NULL | |
| nnsiswa | varchar(50) | YES | | NULL | |
| tgl_lahir | date | NO | | NULL | |
| n_teori | decimal(6,2) | YES | | NULL | |
| n_praktek | decimal(6,2) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
(4)
mysql> alter table tabelSiswa add gender enum('L','P') not null default 'P' afte
r tgl_lahir;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tabelSiswa;
+-----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| nis | varchar(10) | NO | PRI | NULL | |
| nnsiswa | varchar(50) | YES | | NULL | |
| tgl_lahir | date | NO | | NULL | |
| gender | enum('L','P') | NO | | P | |
| n_teori | decimal(6,2) | YES | | NULL | |
| n_praktek | decimal(6,2) | YES | | NULL | |
+-----------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
(5)
mysql> alter table tabelSiswa change tgl_lahir TanggalLahir date not null defaul
t 0;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tabelSiswa;
+--------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| nis | varchar(10) | NO | PRI | NULL | |
| nnsiswa | varchar(50) | YES | | NULL | |
| TanggalLahir | date | NO | | NULL | |
| gender | enum('L','P') | NO | | P | |
| n_teori | decimal(6,2) | YES | | NULL | |
| n_praktek | decimal(6,2) | YES | | NULL | |
+--------------+---------------+------+-----+---------+-------+
6 rows in set (0.02 sec)
(6)
mysql> rename table tabelSiswa to tblSiswa;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+----------------------+
| Tables_in_8017805tkj |
+----------------------+
| tblsiswa |
+----------------------+
1 row in set (0.00 sec)
(7)
mysql> insert into tblSiswa value
-> ('10801','Burhanuddin','1990-11-01','L','80.00','85.00'),
-> ('10802','Fransiska','1989-10-08','P','100.00','90.00'),
-> ('10803','Wawan','1990-04-11','L','95.75','80.00'),
-> ('10804','Ernawati','1990-05-12','P','75.25','86.00'),
-> ('10805','Handi','1989-04-01','L','85.00','95.00');
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from tblSiswa;
+-------+-------------+--------------+--------+---------+-----------+
| nis | nnsiswa | TanggalLahir | gender | n_teori | n_praktek |
+-------+-------------+--------------+--------+---------+-----------+
| 10801 | Burhanuddin | 1990-11-01 | L | 80.00 | 85.00 |
| 10802 | Fransiska | 1989-10-08 | P | 100.00 | 90.00 |
| 10803 | Wawan | 1990-04-11 | L | 95.75 | 80.00 |
| 10804 | Ernawati | 1990-05-12 | P | 75.25 | 86.00 |
| 10805 | Handi | 1989-04-01 | L | 85.00 | 95.00 |
+-------+-------------+--------------+--------+---------+-----------+
5 rows in set (0.02 sec)
(8)
mysql> update tblSiswa set n_teori='90',n_praktek='85' where nis='10802';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from tblSiswa;
+-------+-------------+--------------+--------+---------+-----------+
| nis | nnsiswa | TanggalLahir | gender | n_teori | n_praktek |
+-------+-------------+--------------+--------+---------+-----------+
| 10801 | Burhanuddin | 1990-11-01 | L | 80.00 | 85.00 |
| 10802 | Fransiska | 1989-10-08 | P | 90.00 | 85.00 |
| 10803 | Wawan | 1990-04-11 | L | 95.75 | 80.00 |
| 10804 | Ernawati | 1990-05-12 | P | 75.25 | 86.00 |
| 10805 | Handi | 1989-04-01 | L | 85.00 | 95.00 |
+-------+-------------+--------------+--------+---------+-----------+
5 rows in set (0.00 sec)
(9)
mysql> alter table tblSiswa add n_ulangan decimal(6,2) after n_praktek;
Query OK, 5 rows affected (0.13 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> desc tblSiswa;
+--------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| nis | varchar(10) | NO | PRI | NULL | |
| nnsiswa | varchar(50) | YES | | NULL | |
| TanggalLahir | date | NO | | NULL | |
| gender | enum('L','P') | NO | | P | |
| n_teori | decimal(6,2) | YES | | NULL | |
| n_praktek | decimal(6,2) | YES | | NULL | |
| n_ulangan | decimal(6,2) | YES | | NULL | |
+--------------+---------------+------+-----+---------+-------+
7 rows in set (0.01 sec)
(10)
mysql> update tblSiswa set n_ulangan =87.00 where nis ='10801';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update tblSiswa set n_ulangan =90.00 where nis ='10802';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update tblSiswa set n_ulangan =78.00 where nis ='10803';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update tblSiswa set n_ulangan =85.00 where nis ='10804';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update tblSiswa set n_ulangan =93.00 where nis ='10805';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from tblSiswa;
+-------+-------------+--------------+--------+---------+-----------+-----------
+
| nis | nnsiswa | TanggalLahir | gender | n_teori | n_praktek | n_ulangan
|
+-------+-------------+--------------+--------+---------+-----------+-----------
+
| 10801 | Burhanuddin | 1990-11-01 | L | 80.00 | 85.00 | 87.00
|
| 10802 | Fransiska | 1989-10-08 | P | 90.00 | 85.00 | 90.00
|
| 10803 | Wawan | 1990-04-11 | L | 95.75 | 80.00 | 78.00
|
| 10804 | Ernawati | 1990-05-12 | P | 75.25 | 86.00 | 85.00
|
| 10805 | Handi | 1989-04-01 | L | 85.00 | 95.00 | 93.00
|
+-------+-------------+--------------+--------+---------+-----------+-----------
+
5 rows in set (0.00 sec)
(11)
mysql> select nis,nnsiswa,gender from tblSiswa nis order by TanggalLahir;
+-------+-------------+--------+
| nis | nnsiswa | gender |
+-------+-------------+--------+
| 10805 | Handi | L |
| 10802 | Fransiska | P |
| 10803 | Wawan | L |
| 10804 | Ernawati | P |
| 10801 | Burhanuddin | L |
+-------+-------------+--------+
5 rows in set (0.00 sec)
mysql> select nis,nnsiswa,gender,TanggalLahir from tblSiswa nis order by Tanggal
Lahir;
+-------+-------------+--------+--------------+
| nis | nnsiswa | gender | TanggalLahir |
+-------+-------------+--------+--------------+
| 10805 | Handi | L | 1989-04-01 |
| 10802 | Fransiska | P | 1989-10-08 |
| 10803 | Wawan | L | 1990-04-11 |
| 10804 | Ernawati | P | 1990-05-12 |
| 10801 | Burhanuddin | L | 1990-11-01 |
+-------+-------------+--------+--------------+
5 rows in set (0.00 sec)
mysql> select * from tblSiswa nis order by TanggalLahir;
+-------+-------------+--------------+--------+---------+-----------+-----------
+
| nis | nnsiswa | TanggalLahir | gender | n_teori | n_praktek | n_ulangan
|
+-------+-------------+--------------+--------+---------+-----------+-----------
+
| 10805 | Handi | 1989-04-01 | L | 85.00 | 95.00 | 93.00
|
| 10802 | Fransiska | 1989-10-08 | P | 90.00 | 85.00 | 90.00
|
| 10803 | Wawan | 1990-04-11 | L | 95.75 | 80.00 | 78.00
|
| 10804 | Ernawati | 1990-05-12 | P | 75.25 | 86.00 | 85.00
|
| 10801 | Burhanuddin | 1990-11-01 | L | 80.00 | 85.00 | 87.00
|
+-------+-------------+--------------+--------+---------+-----------+-----------
+
5 rows in set (0.00 sec)
(12)
mysql> select * from tblSiswa where(gender='L') order by n_ulangan;
+-------+-------------+--------------+--------+---------+-----------+-----------
+
| nis | nnsiswa | TanggalLahir | gender | n_teori | n_praktek | n_ulangan
|
+-------+-------------+--------------+--------+---------+-----------+-----------
+
| 10803 | Wawan | 1990-04-11 | L | 95.75 | 80.00 | 78.00
|
| 10801 | Burhanuddin | 1990-11-01 | L | 80.00 | 85.00 | 87.00
|
| 10805 | Handi | 1989-04-01 | L | 85.00 | 95.00 | 93.00
|
+-------+-------------+--------------+--------+---------+-----------+-----------
+
3 rows in set (0.00 sec)
(13)
mysql> select * from tblSiswa where(gender='P'and n_ulangan <90);
+-------+----------+--------------+--------+---------+-----------+-----------+
| nis | nnsiswa | TanggalLahir | gender | n_teori | n_praktek | n_ulangan |
+-------+----------+--------------+--------+---------+-----------+-----------+
| 10804 | Ernawati | 1990-05-12 | P | 75.25 | 86.00 | 85.00 |
+-------+----------+--------------+--------+---------+-----------+-----------+
1 row in set (0.00 sec)
(14)
mysql> select * from tblSiswa where(gender='L'and n_ulangan > 85 );
+-------+-------------+--------------+--------+---------+-----------+-----------
+
| nis | nnsiswa | TanggalLahir | gender | n_teori | n_praktek | n_ulangan
|
+-------+-------------+--------------+--------+---------+-----------+-----------
+
| 10801 | Burhanuddin | 1990-11-01 | L | 80.00 | 85.00 | 87.00
|
| 10805 | Handi | 1989-04-01 | L | 85.00 | 95.00 | 93.00
|
+-------+-------------+--------------+--------+---------+-----------+-----------
+
2 rows in set (0.00 sec)
(15)
mysql> mysql> select * from tblSiswa where TanggalLahir between '1990-01-01' and '1990-
10-01';
+-------+----------+--------------+--------+---------+-----------+-----------+
| nis | nnsiswa | TanggalLahir | gender | n_teori | n_praktek | n_ulangan |
+-------+----------+--------------+--------+---------+-----------+-----------+
| 10803 | Wawan | 1990-04-11 | L | 95.75 | 80.00 | 78.00 |
| 10804 | Ernawati | 1990-05-12 | P | 75.25 | 86.00 | 85.00 |
+-------+----------+--------------+--------+---------+-----------+-----------+
2 rows in set (0.11 sec)
mysql> select nis,nnsiswa,gender from tblSiswa where TanggalLahir between '1990-
01-01' and '1990-10-01';
+-------+----------+--------+
| nis | nnsiswa | gender |
+-------+----------+--------+
| 10803 | Wawan | L |
| 10804 | Ernawati | P |
+-------+----------+--------+
2 rows in set (0.00 sec)
(16)
mysql> select * from tblSiswa where(nnsiswa like 'e%' and nnsiswa like '%i');
+-------+----------+--------------+--------+---------+-----------+-----------+
| nis | nnsiswa | TanggalLahir | gender | n_teori | n_praktek | n_ulangan |
+-------+----------+--------------+--------+---------+-----------+-----------+
| 10804 | Ernawati | 1990-05-12 | P | 75.25 | 86.00 | 85.00 |
+-------+----------+--------------+--------+---------+-----------+-----------+
1 row in set (0.00 sec)
(17)
mysql> select * from tblSiswa where(n_teori between 70 and 85 and nnsiswa like '
%i%');
+-------+-------------+--------------+--------+---------+-----------+-----------
+
| nis | nnsiswa | TanggalLahir | gender | n_teori | n_praktek | n_ulangan
|
+-------+-------------+--------------+--------+---------+-----------+-----------
+
| 10801 | Burhanuddin | 1990-11-01 | L | 80.00 | 85.00 | 87.00
|
| 10804 | Ernawati | 1990-05-12 | P | 75.25 | 86.00 | 85.00
|
| 10805 | Handi | 1989-04-01 | L | 85.00 | 95.00 | 93.00
|
+-------+-------------+--------------+--------+---------+-----------+-----------
+
3 rows in set (0.01 sec)
(18)
mysql> select nis,nnsiswa,TanggalLahir,gender from tblSiswa where month(TanggalL
ahir)='04';
+-------+---------+--------------+--------+
| nis | nnsiswa | TanggalLahir | gender |
+-------+---------+--------------+--------+
| 10803 | Wawan | 1990-04-11 | L |
| 10805 | Handi | 1989-04-01 | L |
+-------+---------+--------------+--------+
2 rows in set (0.01 sec)
(19)
mysql> select nis,nnsiswa,TanggalLahir,gender from tblSiswa where month(TanggalL
ahir)='04' or month(TanggalLahir)='05' and year(TanggalLahir)='1990';
+-------+----------+--------------+--------+
| nis | nnsiswa | TanggalLahir | gender |
+-------+----------+--------------+--------+
| 10803 | Wawan | 1990-04-11 | L |
| 10804 | Ernawati | 1990-05-12 | P |
| 10805 | Handi | 1989-04-01 | L |
+-------+----------+--------------+--------+
3 rows in set (0.00 sec)
(20)
mysql> select nis,nnsiswa,TanggalLahir,gender from tblSiswa where dayofmonth(Tan
ggalLahir)='01';
+-------+-------------+--------------+--------+
| nis | nnsiswa | TanggalLahir | gender |
+-------+-------------+--------------+--------+
| 10801 | Burhanuddin | 1990-11-01 | L |
| 10805 | Handi | 1989-04-01 | L |
+-------+-------------+--------------+--------+
2 rows in set (0.00 sec)
(21)
mysql> select nis,nnsiswa,n_teori,n_praktek,n_ulangan from tblSiswa where (gende
r='L') and(TanggalLahir >'1990-04-01');
+-------+-------------+---------+-----------+-----------+
| nis | nnsiswa | n_teori | n_praktek | n_ulangan |
+-------+-------------+---------+-----------+-----------+
| 10801 | Burhanuddin | 80.00 | 85.00 | 87.00 |
| 10803 | Wawan | 95.75 | 80.00 | 78.00 |
+-------+-------------+---------+-----------+-----------+
2 rows in set (0.00 sec)
(22)
mysql> select nis,nnsiswa,n_teori,n_praktek,n_ulangan,((n_teori+n_praktek+n_ulan
gan)/3) as' n_rata' from tblSiswa order by 'n_rata';
+-------+-------------+---------+-----------+-----------+-----------+
| nis | nnsiswa | n_teori | n_praktek | n_ulangan | n_rata |
+-------+-------------+---------+-----------+-----------+-----------+
| 10801 | Burhanuddin | 80.00 | 85.00 | 87.00 | 84.000000 |
| 10802 | Fransiska | 90.00 | 85.00 | 90.00 | 88.333333 |
| 10803 | Wawan | 95.75 | 80.00 | 78.00 | 84.583333 |
| 10804 | Ernawati | 75.25 | 86.00 | 85.00 | 82.083333 |
| 10805 | Handi | 85.00 | 95.00 | 93.00 | 91.000000 |
+-------+-------------+---------+-----------+-----------+-----------+
5 rows in set, 1 warning (0.00 sec)
(23)
mysql> create index nama on tblSiswa(nnsiswa);
Query OK, 5 rows affected (0.13 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> create index nilaiteori on tblSiswa(n_teori);
Query OK, 5 rows affected (0.09 sec)
Records: 5 Duplicates: 0 Warnings: 0
(24)
mysql> create view view1 as select * from tblSiswa order by n_ulangan;
Query OK, 0 rows affected (0.05 sec)
mysql> select * from view1;
+-------+-------------+--------------+--------+---------+-----------+-----------
+
| nis | nnsiswa | TanggalLahir | gender | n_teori | n_praktek | n_ulangan
|
+-------+-------------+--------------+--------+---------+-----------+-----------
+
| 10803 | Wawan | 1990-04-11 | L | 95.75 | 80.00 | 78.00
|
| 10804 | Ernawati | 1990-05-12 | P | 75.25 | 86.00 | 85.00
|
| 10801 | Burhanuddin | 1990-11-01 | L | 80.00 | 85.00 | 87.00
|
| 10802 | Fransiska | 1989-10-08 | P | 90.00 | 85.00 | 90.00
|
| 10805 | Handi | 1989-04-01 | L | 85.00 | 95.00 | 93.00
|
+-------+-------------+--------------+--------+---------+-----------+-----------
+
5 rows in set (0.01 sec)
(25)
mysql> drop index nilaiteori on tblSiswa;
Query OK, 5 rows affected (0.08 sec)
Records: 5 Duplicates: 0 Warnings: 0
(26)
mysql> create view view2 as select nis,nnsiswa,n_praktek from tblSiswa where (ge
nder='P') and (n_praktek > 80);
Query OK, 0 rows affected (0.11 sec)
mysql> select * from view2;
+-------+-----------+-----------+
| nis | nnsiswa | n_praktek |
+-------+-----------+-----------+
| 10802 | Fransiska | 85.00 |
| 10804 | Ernawati | 86.00 |
+-------+-----------+-----------+
2 rows in set (0.00 sec)
(27)
mysql> create view view3 as select nis,nnsiswa,n_teori,n_praktek,n_ulangan,((n_t
eori+n_praktek+n_ulangan)/3) as n_rata from tblSiswa;
Query OK, 0 rows affected (0.05 sec)
mysql> select * from view3;
+-------+-------------+---------+-----------+-----------+-----------+
| nis | nnsiswa | n_teori | n_praktek | n_ulangan | n_rata |
+-------+-------------+---------+-----------+-----------+-----------+
| 10801 | Burhanuddin | 80.00 | 85.00 | 87.00 | 84.000000 |
| 10802 | Fransiska | 90.00 | 85.00 | 90.00 | 88.333333 |
| 10803 | Wawan | 95.75 | 80.00 | 78.00 | 84.583333 |
| 10804 | Ernawati | 75.25 | 86.00 | 85.00 | 82.083333 |
| 10805 | Handi | 85.00 | 95.00 | 93.00 | 91.000000 |
+-------+-------------+---------+-----------+-----------+-----------+
5 rows in set (0.01 sec)
(28)
mysql> drop view view2;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+----------------------+
| Tables_in_8017805tkj |
+----------------------+
| tblsiswa |
| view1 |
| view3 |
+----------------------+
3 rows in set (0.01 sec)
download soal studi kasus sql disini !!
http://www.indowebster.com/download/files/sql2
Tidak ada komentar:
Posting Komentar