1. buat store procedure untuk menampilkan name (gabungan dari firstname, lastname), email, city dari karyawan(employee)
prosedur memiliki parameter untuk penyaringan berdasarkan
Jawab :
DELIMITER /
CREATE PROCEDURE karyawan(IN city varchar(50))
BEGIN
SELECT CONCAT(firstName,' ',lastName) as Name, email, city
from employees, offices WHERE city=('
END
/
Memanggil PROCEDURE karyawan :
CALL karyawan('
/
2. buat store procedure untuk menghapus data produk berdasarkan productCode
Jawab:
CREATE PROCEDURE produk(IN productCode_param varchar(15))
BEGIN
DELETE FROM products WHERE productCode = productCode_param;
END;
/
Memanggil PROCEDURE produk :
CALL produk('S72_3212')
/
3. buat store procedure untuk memasukkan data offices
Jawab :
CREATE PROCEDURE tambahData(IN officeCode varchar(10),city varchar(50),phone varchar(50),addressLine1 varchar(50),addressLine2 varchar(50),state varchar(50),country varchar(50),postalCode varchar(15),territory varchar(10))
BEGIN
INSERT INTO offices values(officeCode,city,phone,addressline1,addressline2,state,country,postalCode,territory);
END;
/
Memanggil tambahData :
CALL tambahData(9,'bantul',2483,'jogja','sleman','jawa','ina',234,'dunia');
/
4.buat store procedure untuk mencari jumlah pembayaran terkecil, terbesar dan rata2 (payments)
Jawab :
CREATE PROCEDURE pembayaran(IN customerNumber int(11))
BEGIN
SELECT MIN(amount)AS Pembayaran_Terkecil,MAX(amount)AS Pembayaran_Terbesar,AVG(amount)AS Pembayaran_Rata_Rata from payments;
END
/
Memanggil pembayaran :
CALL pembayaran('');
/
Mode 2
Nomor 1
DELIMITER //
CREATE PROCEDURE nama()
BEGIN
SELECT CONCAT(firstName,' ',lastName) "Nama",email,city
FROM employees INNER JOIN offices USING (officeCode) ORDER BY (city);
END //
DELIMITER ;
CALL nama();
Nomor 2
DELIMITER //
CREATE PROCEDURE hapusproduk(IN Code VARCHAR(15))
BEGIN
DELETE FROM products WHERE productCode=Code;
END //
DELIMITER ;
CALL hapusproduk('S10_1678');
Nomor 3
DELIMITER //
CREATE PROCEDURE inputOffices(IN inputOfficeCode VARCHAR(15),
inputCity VARCHAR(50),
inputPhone varchar(50),
inputAddressLine1 varchar(50),
inputAddressLine2 varchar(50),
inputState varchar(50),
inputCountry varchar(50),
inputPostalCode varchar(15),
inputTerritory varchar(10)
)
BEGIN
INSERT INTO `offices`(`officeCode`,`city`,`phone`,`addressLine1`,`addressLine2`,`state`,`country`,`postalCode`,`territory`)
VALUES (inputOfficeCode,inputCity,inputPhone,inputAddressLine1,inputAddressLine2,inputState,inputCountry,inputPostalCode,inputTerritory);
END //
DELIMITER ;
CALL inputOffices('x','xx','xxx','xxxx','xxxxx','xxxxxx','xxxxxx','xxxxxxx','xxxxxxxx');
4. buat store procedure untuk mencari jumlah pembayaran terkecil, terbesar
dan rata-rata (payments)
mysql> DELIMITER //
mysql> CREATE PROCEDURE GetPayment()
-> BEGIN
-> SELECT MAX(amount) AS Terbesar, MIN(amount) AS Terkecil, AVG(amount) AS Rata FROM payments;
-> END //
Query OK, 0 rows affected (0.00 sec)
mysql> CALL GetPayment();
-> //
+-----------+----------+-----------------+
| Terbesar | Terkecil | Rata |
+-----------+----------+-----------------+
| 120166.58 | 615.45 | 32431.645531136 |
+-----------+----------+-----------------+
1 row in set (0.00 sec)
