Docstoc

Praktikum Basis Data 4

Document Sample
Praktikum Basis Data 4 Powered By Docstoc
					Seng Hansun 723/Pra-MIK/122/2009 Kelas C Pra S2 Ilmu Komputer Universitas Gadjah Mada Yogyakarta Tugas 4

Section 1
Enter password: **** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.0.51b-community-nt MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. Membuat database ‘Employee’ mysql> create database Employee; Query OK, 1 row affected (0.00 sec) Memanggil (menggunakan) database ‘Employee’ mysql> use Employee; Database changed Membuat tabel ‘EmployeeAddressTable’ mysql> create table EmployeeAddressTable -> (SSN char(9), -> FirstName char(15), -> LastName char(20), -> Address char(50), -> City char(20), -> State char(20)); Query OK, 0 rows affected (0.41 sec) Melihat struktur tabel dalam ‘EmployeeAddressTable’ mysql> desc EmployeeAddressTable; +----------- +---------- +------ +----- +--------| Field | Type | Null | Key | Default +----------- +---------- +------ +----- +--------| SSN | char(9) | YES | | NULL | FirstName | char(15) | YES | | NULL | LastName | char(20) | YES | | NULL

+-------+ | Extra | +-------+ | | | | | |

| Address | char(50) | YES | City | char(20) | YES | State | char(20) | YES +----------- +---------- +-----6 rows in set (0.00 sec)

| | | +-----

| NULL | NULL | NULL +---------

| | | | | | +-------+

Memasukkan data ke dalam tabel ‘EmployeeAddressTable’ mysql> insert into EmployeeAddressTable values -> ('512687458','Joe','Smith','83 First Street','Howard','Ohio'), -> ('758420012','Mary','Scott','842 Vine Ave.','Losantiville','Ohio'), -> ('102254896','Sam','Jones','33 Elm St.','Paris','New York'), -> ('876512563','Sarah','Ackerman','440 U.S. 110','Upton','Michigan'); Query OK, 4 rows affected (0.36 sec) Records: 4 Duplicates: 0 Warnings: 0 Menampilkan isi kolom FirstName, LastName, Address, City, dan State dari tabel ‘EmployeeAddressTable’ mysql> select FirstName, LastName, Address, City, State -> from EmployeeAddressTable; +----------- +---------- +----------------- +-------------- + ----------+ | FirstName | LastName | Address | City | State +----------- +---------- +----------------- +-------------- + ----------+ | Joe | Smith | 83 First Street | Howard | Ohio | Mary | Scott | 842 Vine Ave. | Losantiville | Ohio | Sam | Jones | 33 Elm St. | Paris | New York | Sarah | Ackerman | 440 U.S. 110 | Upton | Michigan +----------- +---------- +----------------- +-------------- + ----------+ 4 rows in set (0.00 sec) Menampilkan seluruh isi kolom dari ‘EmployeeAddressTable’ mysql> select * from EmployeeAddressTable; +----------- +----------- +---------- +----------------| SSN | FirstName | LastName | Address +----------- +----------- +---------- +----------------| 512687458 | Joe | Smith | 83 First Street | 758420012 | Mary | Scott | 842 Vine Ave. | 102254896 | Sam | Jones | 33 Elm St. | 876512563 | Sarah | Ackerman | 440 U.S. 110 +----------- +----------- +---------- +----------------4 rows in set (0.00 sec) Membuat tabel ‘EmployeeStatisticTable’

| | | | |

+-------------| City +-------------| Howard | Losantiville | Paris | Upton +--------------

+ ----------+ | State + ----------+ | Ohio | Ohio | New York | Michigan + ----------+

| | | | |

mysql> create table EmployeeStatisticsTable -> (EmployeeIDNo char(3), -> Salary integer, -> Benefits integer, -> Position char(20)); Query OK, 0 rows affected (0.08 sec) Melihat struktur tabel dalam ‘EmployeeStatisticTable’ mysql> desc EmployeeStatisticsTable; +-------------+---------- +------ +----| Field | Type | Null | Key +-------------+---------- +------ +----| EmployeeIDNo | char(3) | YES | | Salary | int(11) | YES | | Benefits | int(11) | YES | | Position | char(20) | YES | +-------------+---------- +------ +----4 rows in set (0.00 sec)

+--------| Default +--------| NULL | NULL | NULL | NULL +---------

+-------+ | Extra | +-------+ | | | | | | | | +-------+

Memasukkan data ke dalam ‘EmployeeStatisticTable’ mysql> insert into EmployeeStatisticsTable values -> ('010',75000,15000,'Manager'), -> ('105',65000,15000,'Manager'), -> ('152',60000,15000,'Manager'), -> ('215',60000,12500,'Manager'), -> ('244',50000,12000,'Staff'), -> ('300',45000,10000,'Staff'), -> ('335',40000,10000,'Staff'), -> ('400',32000,7500,'Entry-Level'), -> ('441',28000,7500,'Entry-Level'); Query OK, 9 rows affected (0.05 sec) Records: 9 Duplicates: 0 Warnings: 0 Menampilkan seluruh isi kolom dari ‘EmployeeStatisticTable’ mysql> select * from EmployeeStatisticsTable -> order by EmployeeIDNo; +-------------+-------- +---------- +-------------+ | EmployeeIDNo | Salary | Benefits | Position | +-------------+-------- +---------- +-------------+ | 010 | 75000 | 15000 | Manager | | 105 | 65000 | 15000 | Manager | | 152 | 60000 | 15000 | Manager | | 215 | 60000 | 12500 | Manager |

| 244 | 50000 | 300 | 45000 | 335 | 40000 | 400 | 32000 | 441 | 28000 +-------------+-------9 rows in set (0.36 sec)

| 12000 | Staff | | 10000 | Staff | | 10000 | Staff | | 7500 | Entry-Level | | 7500 | Entry-Level | +---------- +-------------+

Menampilkan EmployeeIDNo dari ‘EmployeeStatisticTable’ yang memiliki Salary >= 50000 mysql> select EmployeeIDNo -> from EmployeeStatisticsTable -> where Salary >= 50000; +--------------+ | EmployeeIDNo | +--------------+ | 105 | | 152 | | 215 | | 244 | | 010 | +--------------+ 5 rows in set (0.00 sec) Menampilkan EmployeeIDNo dari ‘EmployeeStatisticTable’ yang memiliki Position sebagai Manager mysql> select EmployeeIDNo -> from EmployeeStatisticsTable -> where Position = 'Manager'; +--------------+ | EmployeeIDNo | +--------------+ | 105 | | 152 | | 215 | | 010 | +--------------+ 4 rows in set (0.00 sec) Menampilkan EmployeeIDNo dari ‘EmployeeStatisticTable’ yang memiliki Salary > 40000 dan Position sebagai Staff mysql> select EmployeeIDNo -> from EmployeeStatisticsTable

-> where Salary > 40000 and Position = 'Staff'; +--------------+ | EmployeeIDNo | +--------------+ | 244 | | 300 | +--------------+ 2 rows in set (0.00 sec) Menampilkan EmployeeIDNo dari ‘EmployeeStatisticTable’ yang memiliki Salary < 40000 atau Benefits < 10000 mysql> select EmployeeIDNo -> from EmployeeStatisticsTable -> where Salary < 40000 or Benefits < 10000; +--------------+ | EmployeeIDNo | +--------------+ | 400 | | 441 | +--------------+ 2 rows in set (0.00 sec) Menampilkan EmployeeIDNo dari ‘EmployeeStatisticTable’ yang memiliki Position sebagai Manager dan Salary > 60000 atau Benefits > 12000 mysql> select EmployeeIDNo -> from EmployeeStatisticsTable -> where Position = 'Manager' and Salary > 60000 or Benefits > 12000; +--------------+ | EmployeeIDNo | +--------------+ | 105 | | 152 | | 215 | | 010 | +--------------+ 4 rows in set (0.00 sec) Menampilkan EmployeeIDNo dari ‘EmployeeStatisticTable’ yang memiliki Position sebagai Manager dan (Salary > 50000 atau Benefits > 10000) mysql> select EmployeeIDNo -> from EmployeeStatisticsTable -> where Position = 'Manager' and (Salary > 50000 or Benefits > 10000); +--------------+

| EmployeeIDNo | +--------------+ | 105 | | 152 | | 215 | | 010 | +--------------+ 4 rows in set (0.00 sec) Menampilkan EmployeeIDNo dari ‘EmployeeStatisticTable’ yang memiliki Position sebagai Manager atau Staff mysql> select EmployeeIDNo -> from EmployeeStatisticsTable -> where Position in ('Manager','Staff'); +--------------+ | EmployeeIDNo | +--------------+ | 105 | | 152 | | 215 | | 244 | | 300 | | 335 | | 010 | +--------------+ 7 rows in set (0.00 sec) Menampilkan EmployeeIDNo dari ‘EmployeeStatisticTable’ yang memiliki Salary antara 30000 dan 50000 mysql> select EmployeeIDNo -> from EmployeeStatisticsTable -> where Salary between 30000 and 50000; +--------------+ | EmployeeIDNo | +--------------+ | 244 | | 300 | | 335 | | 400 | +--------------+ 4 rows in set (0.00 sec)

Menampilkan EmployeeIDNo dari ‘EmployeeStatisticTable’ yang memiliki Salary tidak di antara 30000 dan 50000 mysql> select EmployeeIDNo -> from EmployeeStatisticsTable -> where Salary not between 30000 and 50000; +--------------+ | EmployeeIDNo | +--------------+ | 105 | | 152 | | 215 | | 441 | | 010 | +--------------+ 5 rows in set (0.00 sec) Menampilkan SSN dari ‘EmployeeAddressTable’ di mana LastName dimulai dari huruf L mysql> select SSN -> from EmployeeAddressTable -> where LastName like 'L%'; Empty set (0.00 sec)

Section 2
Enter password: **** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.51b-community-nt MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. Membuat database ‘Antique’ mysql> create database Antique; Query OK, 1 row affected (0.05 sec) Memanggil (menggunakan) database ‘Antique’ mysql> use Antique; Database changed Membuat tabel ‘AntiqueOwners’ mysql> create table AntiqueOwners

-> (OwnerID char(2), -> OwnerLastName char(15), -> OwnerFirstName char(15)); Query OK, 0 rows affected (0.52 sec) Memasukkan data ke dalam tabel ‘AntiqueOwners’ mysql> insert into AntiqueOwners values -> ('01','Jones','Bill'), -> ('02','Smith','Bob'), -> ('15','Lawson','Patricia'), -> ('21','Akins','Jane'), -> ('50','Fowler','Sam'); Query OK, 5 rows affected (0.08 sec) Records: 5 Duplicates: 0 Warnings: 0 Menampilkan seluruh isi kolom dari tabel ‘AntiqueOwners’ mysql> select * from AntiqueOwners; +--------- +--------------+----------------+ | OwnerID | OwnerLastName | OwnerFirstName | +--------- +--------------+----------------+ | 01 | Jones | Bill | | 02 | Smith | Bob | | 15 | Lawson | Patricia | | 21 | Akins | Jane | | 50 | Fowler | Sam | +--------- +--------------+----------------+ 5 rows in set (0.00 sec) Membuat tabel ‘Orders’ mysql> create table Orders -> (OwnerID char(2), -> ItemDesired char(15)); Query OK, 0 rows affected (0.09 sec) Memasukkan data ke dalam tabel ‘Orders’ mysql> insert into Orders values -> ('02','Table'), -> ('02','Desk'), -> ('21','Chair'), -> ('15','Mirror'); Query OK, 4 rows affected (0.06 sec) Records: 4 Duplicates: 0 Warnings: 0

Menampilkan seluruh isi kolom dari tabel ‘Orders’ mysql> select * from Orders; +--------+-------------+ | OwnerID | ItemDesired | +--------+-------------+ | 02 | Table | | 02 | Desk | | 21 | Chair | | 15 | Mirror | +--------+-------------+ 4 rows in set (0.00 sec) Membuat tabel ‘Antiques’ mysql> create table Antiques -> (SellerID char(2), -> BuyerID char(2), -> Item char(20)); Query OK, 0 rows affected (0.06 sec) Memasukkan data ke dalam tabel ‘Antiques’ mysql> insert into Antiques values -> ('01','50','Bed'), -> ('02','15','Table'), -> ('15','02','Chair'), -> ('21','50','Mirror'), -> ('50','01','Desk'), -> ('01','21','Cabinet'), -> ('02','21','Coffee Table'), -> ('15','50','Chair'), -> ('01','15','Jewelry Box'), -> ('02','21','Pottery'), -> ('21','02','Bookcase'), -> ('50','01','Plant Stand'); Query OK, 12 rows affected (0.38 sec) Records: 12 Duplicates: 0 Warnings: 0 Menampilkan seluruh isi kolom dari tabel ‘Antiques’ mysql> select * from Antiques; +---------- +--------- +--------------+ | SellerID | BuyerID | Item | +---------- +--------- +--------------+

| 01 | 50 | Bed | | 02 | 15 | Table | | 15 | 02 | Chair | | 21 | 50 | Mirror | | 50 | 01 | Desk | | 01 | 21 | Cabinet | | 02 | 21 | Coffee Table | | 15 | 50 | Chair | | 01 | 15 | Jewelry Box | | 02 | 21 | Pottery | | 21 | 02 | Bookcase | | 50 | 01 | Plant Stand | +---------- +--------- +--------------+ 12 rows in set (0.36 sec) Mengubah struktur tabel, yakni membuat Primary Key pada kolom OwnerID mysql> alter table AntiqueOwners -> modify OwnerID char(2) primary key; Query OK, 5 rows affected (0.34 sec) Records: 5 Duplicates: 0 Warnings: 0 Melihat struktur tabel mysql> desc AntiqueOwners; +---------------+---------- +-----| Field | Type | Null +---------------+---------- +-----| OwnerID | char(2) | NO | OwnerLastName char(15) | YES | OwnerFirstName | char(15) | YES +---------------+---------- +-----3 rows in set (0.02 sec)

+----- +--------| Key | Default +----- +--------| PRI | NULL | | NULL | | NULL +----- +---------

+-------+ | Extra | +-------+ | | | | | | +-------+

Menampilkan OwnerLastName, OwnerFirstName dari tabel AntiqueOwners dan Antiques, di mana BuyerID = OwnerID dan Item = ‘Chair’ mysql> select ownerlastname, ownerfirstname -> from antiqueowners, antiques -> where buyerid=ownerid and item='chair'; +--------------+----------------+ | ownerlastname | ownerfirstname | +--------------+----------------+ | Smith | Bob | | Fowler | Sam | +--------------+----------------+

2 rows in set (0.00 sec) atau mysql> select AntiqueOwners.OwnerLastName, AntiqueOwners.OwnerFirstName -> from AntiqueOwners, Antiques -> where Antiques.BuyerID=AntiqueOwners.OwnerID and Antiques.Item='Chair'; +--------------+----------------+ | OwnerLastName | OwnerFirstName | +--------------+----------------+ | Smith | Bob | | Fowler | Sam | +--------------+----------------+ 2 rows in set (0.00 sec) Menampilkan secara unik SellerID, OwnerLastName, OwnerFirstName dari tabel Antiques dan AntiqueOwners, di mana SellerID = OwnerID dan dikelompokkan berdasarkan OwnerLastName, OwnerFirstName, dan terakhir OwnerID mysql> select distinct SellerID, OwnerLastName, OwnerFirstName -> from Antiques, AntiqueOwners -> where SellerID=OwnerID -> order by OwnerLastName, OwnerFirstName, OwnerID; +---------- +--------------+----------------+ | SellerID | OwnerLastName | OwnerFirstName | +---------- +--------------+----------------+ | 21 | Akins | Jane | | 50 | Fowler | Sam | | 01 | Jones | Bill | | 15 | Lawson | Patricia | | 02 | Smith | Bob | +---------- +--------------+----------------+ 5 rows in set (0.01 sec) Menampilkan OwnerLastName sebagai Last_Name, ItemDesired sebagai Item_Ordered dari tabel Orders (alias ‘Ord’) dan AntiqueOwners (alias ‘Own’), di mana OwnerID (tabel Orders) = OwnerID (tabel AntiqueOwners) dan ItemDesired diperoleh dari kolom Item dari tabel Antiques mysql> select Own.OwnerLastName Last_Name, Ord.ItemDesired Item_Ordered -> from Orders Ord, AntiqueOwners Own -> where Ord.OwnerID=Own.OwnerID and -> Ord.ItemDesired in -> (select Item -> from Antiques); +----------+--------------+

| Last_Name | Item_Ordered | +----------+--------------+ | Smith | Table | | Smith | Desk | | Lawson | Mirror | | Akins | Chair | +----------+--------------+ 4 rows in set (0.34 sec)

Section 3
Memanggil (menggunakan) database ‘Employee’ mysql> use Employee; Database changed Menampilkan jumlah dan rata-rata Salary dari ‘EmployeeStatisticsTable’ mysql> select sum(Salary), avg(Salary) -> from EmployeeStatisticsTable; +------------+-------------+ | sum(Salary) | avg(Salary) | +------------+-------------+ | 455000 | 50555.5556 | +------------+-------------+ 1 row in set (0.39 sec) Menampilkan nilai minimum Benefits dari ‘EmployeeStatisticsTable’ yang memiliki Position sebagai Manager mysql> select min(Benefits) -> from EmployeeStatisticsTable -> where Position = 'Manager'; +---------------+ | min(Benefits) | +---------------+ | 12500 | +---------------+ 1 row in set (0.00 sec) Menghitung cacah data dari ‘EmployeeStatisticsTable’ yang memiliki Position sebagai Staff mysql> select count(*) -> from EmployeeStatisticsTable -> where Position = 'Staff'; +----------+

| count(*) | +----------+ | 3 | +----------+ 1 row in set (0.00 sec) Memanggil (menggunakan) database ‘Antique’ mysql> use Antique; Database changed Membuat view dengan nama ‘AntView’ dengan data dari kolom ItemDesired dari tabel ‘Orders’ mysql> create view AntView as select ItemDesired from Orders; Query OK, 0 rows affected (0.47 sec) Menampilkan SellerID dari Antiques dan AntView di mana ItemDesired = Item mysql> select SellerID -> from Antiques, AntView -> where ItemDesired = Item; +----------+ | SellerID | +----------+ | 02 | | 15 | | 21 | | 50 | | 15 | +----------+ 5 rows in set (0.00 sec) Membuat tabel Orders mysql> create table Orders -> (OwnerID integer not null, -> ItemDesired char(40) not null); ERROR 1050 (42S01): Table 'orders' already exists Nb: Muncul ERROR karena di database ‘Antique’ telah ada tabel dengan nama yang sama Menampilkan tabel-tabel yang ada di dalam database ‘Antique’ mysql> show tables; +-------------------+ | Tables_in_antique |

+-------------------+ | antiqueowners | | antiques | | antview | | orders | +-------------------+ 4 rows in set (0.00 sec) Mengubah struktur tabel ‘Antique’ dengan menambah kolom Price mysql> alter table Antiques -> add (Price decimal(8,2) null); Query OK, 12 rows affected (0.61 sec) Records: 12 Duplicates: 0 Warnings: 0 Memasukkan data baru ke dalam tabel ‘Antiques’ secara lengkap (seluruh field terisi) mysql> insert into Antiques values -> (21,01,'Ottoman',200.00); Query OK, 1 row affected (0.05 sec) mysql> select * from antiques; +---------- +--------- +-------------| SellerID | BuyerID | Item +---------- +--------- +-------------| 01 | 50 | Bed | 02 | 15 | Table | 15 | 02 | Chair | 21 | 50 | Mirror | 50 | 01 | Desk | 01 | 21 | Cabinet | 02 | 21 | Coffee Table | 15 | 50 | Chair | 01 | 15 | Jewelry Box | 02 | 21 | Pottery | 21 | 02 | Bookcase | 50 | 01 | Plant Stand | 21 |1 | Ottoman +---------- +--------- +-------------13 rows in set (0.00 sec)

+--------+ | Price | +--------+ | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | 200.00 | +--------+

Memasukkan data baru ke dalam tabel ‘Antiques’ pada sebagian field (kolom) mysql> insert into Antiques (BuyerID, SellerID, Item) values -> (21,01,'Ottoman'); Query OK, 1 row affected (0.38 sec)

Menghapus data dari ‘Antiques’ di mana Item = ‘Ottoman’ mysql> delete from Antiques -> where Item='Ottoman'; Query OK, 2 rows affected (0.06 sec) Menghapus data dari ‘Antiques’ di mana Item = ‘Ottoman’ dan BuyerID = 01 serta SellerID = 21 mysql> delete from Antiques -> where Item='Ottoman' and BuyerID=01 and SellerID=21; Query OK, 0 rows affected (0.00 sec) Meng-update data dalam tabel ‘Antiques’ dengan men-set Price = 500 dari Item yang berupa Chair mysql> update Antiques -> set Price = 500 -> where Item = 'Chair'; Query OK, 2 rows affected (0.03 sec) Rows matched: 2 Changed: 2 Warnings: 0 Menampilkan seluruh isi data dari tabel ‘Antiques’ mysql> select * from Antiques; +---------- +--------- +-------------| SellerID | BuyerID | Item +---------- +--------- +-------------| 01 | 50 | Bed | 02 | 15 | Table | 15 | 02 | Chair | 21 | 50 | Mirror | 50 | 01 | Desk | 01 | 21 | Cabinet | 02 | 21 | Coffee Table | 15 | 50 | Chair | 01 | 15 | Jewelry Box | 02 | 21 | Pottery | 21 | 02 | Bookcase | 50 | 01 | Plant Stand +---------- +--------- +-------------12 rows in set (0.00 sec)

+--------+ | Price | +--------+ | NULL | | NULL | | 500.00 | | NULL | | NULL | | NULL | | NULL | | 500.00 | | NULL | | NULL | | NULL | | NULL | +--------+

Section 4
Membuat index OID_IDX pada kolom OwnerID dari tabel ‘AntiqueOwners’ mysql> create index OID_IDX on AntiqueOwners (OwnerID); Query OK, 5 rows affected (0.69 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from AntiqueOwners; +--------- +--------------+----------------+ | OwnerID | OwnerLastName | OwnerFirstName +--------- +--------------+----------------+ | 01 | Jones | Bill | 02 | Smith | Bob | 15 | Lawson | Patricia | 21 | Akins | Jane | 50 | Fowler | Sam +--------- +--------------+----------------+ 5 rows in set (0.01 sec)

| | | | | |

Membuat index Name_IDX pada kolom OwnerLastName dan OwnerFirstName dari tabel ‘AntiqueOwners’ mysql> create index Name_IDX on AntiqueOwners (OwnerLastName, OwnerFirstName); Query OK, 5 rows affected (0.55 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from AntiqueOwners; +--------- +--------------+----------------+ | OwnerID | OwnerLastName | OwnerFirstName +--------- +--------------+----------------+ | 21 | Akins | Jane | 50 | Fowler | Sam | 01 | Jones | Bill | 15 | Lawson | Patricia | 02 | Smith | Bob +--------- +--------------+----------------+ 5 rows in set (0.00 sec) Menghapus index OID_IDX mysql> drop index OID_IDX; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 Nb: muncul pesan ERROR

| | | | | |

Membuat unique index OID_IDX pada OwnerID dari tabel ‘AntiqueOwners’ mysql> create Unique index OID_IDX on AntiqueOwners (OwnerID); ERROR 1061 (42000): Duplicate key name 'OID_IDX' Nb: muncul pesan ERROR karena terjadi duplikasi nama kunci OID_IDX Menampilkan BuyerID dan max(Price) dari tabel ‘Antiques’ dikelompokkan berdasarkan BuyerID mysql> select BuyerID, max(Price) -> from Antiques -> group by BuyerID; +--------- +------------+ | BuyerID | max(Price) | +--------- +------------+ | 01 | NULL | | 02 | 500.00 | | 15 | NULL | | 21 | NULL | | 50 | 500.00 | +--------- +------------+ 5 rows in set (0.03 sec) Menampilkan BuyerID dan max(Price) dari tabel ‘Antiques’ dikelompokkan berdasarkan BuyerID dan memiliki Price di atas 1000

mysql> select BuyerID, max(Price) -> from Antiques -> group by BuyerID -> having Price > 1000; ERROR 1054 (42S22): Unknown column 'Price' in 'having clause' Nb: muncul pesan ERROR Menampilkan BuyerID dari tabel ‘Antiques’ di mana Price lebih besar dari (rerata + 100) dari tabel ‘Antiques’ mysql> select BuyerID -> from Antiques -> where Price > -> (select avg(Price) + 100 -> from Antiques); Empty set (0.00 sec) Menampilkan OwnerLastName dari tabel ‘AntiqueOwners’ di mana OwnerID berada

dalam BuyerID dari tabel ‘Antiques’ secara unik mysql> select OwnerLastName -> from AntiqueOwners -> where OwnerID in -> (select distinct BuyerID -> from Antiques); +---------------+ | OwnerLastName | +---------------+ | Akins | | Fowler | | Jones | | Lawson | | Smith | +---------------+ 5 rows in set (0.00 sec)

mysql> update AntiqueOwners -> set OwnerFirstName = 'John' -> where OwnerID = -> (select BuyerID -> from Antiques -> where Item = 'Bookcase'); Query OK, 1 row affected (0.41 sec) Rows matched: 1 Changed: 1 Warnings: 0 Menampilkan OwnerFirstName, OwnerLastName dari tabel ‘AntiqueOwners’ di mana ada Item = Chair dari seluruh data di dalam tabel ‘Antiques’ mysql> select OwnerFirstName, OwnerLastName -> from AntiqueOwners -> where exists -> (select * -> from Antiques -> where Item = 'Chair'); +---------------+---------------+ | OwnerFirstName | OwnerLastName | +---------------+---------------+ | Jane | Akins | | Sam | Fowler | | Bill | Jones | | Patricia | Lawson | | John | Smith | +---------------+---------------+

5 rows in set (0.00 sec) Menampilkan BuyerID dan Item dari tabel ‘Antiques’ di mana Price lebih besar sama dengan semua Price lainnya dari tabel ‘Antiques’ mysql> select BuyerID, Item -> from Antiques -> where Price >= all -> (select Price -> from Antiques); +--------- +-------+ | BuyerID | Item | +--------- +-------+ | 02 | Chair | | 50 | Chair | +--------- +-------+ 2 rows in set (0.00 sec) Menampilkan BuyerID dari tabel ‘Antiques’ union (gabungan) OwnerID dari tabel ‘Orders’ mysql> select BuyerID -> from Antiques -> union -> select OwnerID -> from Orders; +---------+ | BuyerID | +---------+ | 50 | | 15 | | 02 | | 01 | | 21 | +---------+ 5 rows in set (0.00 sec) Menampilkan OwnerID dan string ‘is in both Orders & Antiques’ dari tabel ‘Orders’ dan ‘Antiques’ di mana OwnerID = BuyerID union BuyerID dan string ‘is in Antiques only’ dari tabel ‘Antiques’ di mana BuyerID tidak ada dalam OwnerID dari tabel ‘Orders’ mysql> select OwnerID, 'is in both Orders & Antiques' -> from Orders, Antiques -> where OwnerID = BuyerID -> union -> select BuyerID, 'is in Antiques only'

-> from Antiques -> where BuyerID not in -> (select OwnerID -> from Orders); +--------+------------------------------+ | OwnerID | is in both Orders & Antiques +--------+------------------------------+ | 15 | is in both Orders & Antiques | 02 | is in both Orders & Antiques | 21 | is in both Orders & Antiques | 50 | is in Antiques only | 01 | is in Antiques only +--------+------------------------------+ 5 rows in set (0.00 sec)

| | | | | |


				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:115
posted:12/19/2009
language:Indonesian
pages:20