Tugas 2 SMBD Kelompok

modul 1 & 2

MODUL TIGA

1.      Mengambil data dengan SELECT
mysql> select*from emloyee;

2.      Mengambil data pada kolom tertentu

mysql> SELECT first_name, last_name, city FROM employee;

3.      Query menggunakan parameter kondisi WHERE

mysql> SELECT first_name, last_name, city FROM employee WHERE city = 'New York';

4.      Query menggunakan beberapa parameter kondisional

mysql> SELECT first_name, last_name, salary, city FROM employee WHERE city = 'New York' AND salary > 4000 ;

mysql> SELECT first_name, last_name, city, description FROM employee WHERE city= 'Toronto' OR description = 'Tester';

5.      Memberikan alias hasil query pada SELECT

mysql> SELECT CONCAT(first_name," ",last_name) AS name, description FROM employee WHERE description = "Detective";

6.      Query data bertipe teks dengan pattern matching

mysql> SELECT CONCAT(first_name," ", last_name) FROM employee WHERE first_name LIKE 'J____';

mysql> SELECT CONCAT(first_name," ", last_name) FROM employee WHERE first_name NOT LIKE '%n';

7.      Query data unik menggunakan DISTINCT

mysql> SELECT description FROM employee;

mysql> SELECT DISTINCT description FROM employee;

8.      Membatasi hasil query dengan LIMIT

mysql> SELECT *FROM employee LIMIT 5;

mysql> SELECT *FROM employee LIMIT 2,3;

mysql> SELECT * FROM employee LIMIT 0,3;

9.      Mengelompokkan hasil query menggunakan GROUP BY

Hasil query dapat kita kelompokkan berdasarkan field/kolom menggunakan statement GROUP BY.

Berikut adalah contohnya:

mysql> SELECT * FROM employee GROUP BY city;

10.     Mendapatkan jumlah anggota setiap kelompok menggunakan COUNT()

mysql> SELECT city, COUNT(*) FROM employee WHERE description = 'Tester' GROUP BY city;

11.     Mendapatkan jumlahanggota setiap kelompok menggunakan COUNT()

mysql> SELECT first_name, last_name, salary FROM employee HAVING salary > 3000;

mysql> SELECT city, COUNT(*), salary FROM employee WHERE salary > 3000 GROUP BY city;

mysql> SELECT city, COUNT(*), salary FROM employee GROUP BY city HAVING salary > 3000;

mysql> SELECT CONCAT(first_name," ",last_name) AS name FROM employee ORDER BY name;

mysql> SELECT first_name, last_name, city FROM employee ORDER BY first_name, city;

mysql> SELECT first_name, last_name, city FROM employee ORDER BY first_name, city DESC;

mysql> SELECT first_name, last_name, city FROM employee ORDER BY city LIMIT 4;

mysql> SELECT first_name, last_name, salary FROM employee WHERE salary BETWEEN 1000 and 3000;

mysql> SELECT first_name, last_name, salary FROM employee WHERE salary BETWEEN 1233.78 and 1235.56;

mysql> SELECT first_name,last_name FROM employee WHERE first_name BETWEEN "Elvis" and "James";

mysql> SELECT first_name,last_name FROM employee WHERE first_name NOT BETWEEN "Elvis" and "James";

mysql> SELECT first_name, last_name, salary FROM employee WHERE salary = (SELECT MAX(salary) FROM employee);

mysql> SELECT first_name, last_name, salary FROM employee WHERE salary > ALL(SELECT salary FROM employee WHERE salary <2000);

mysql> SELECT first_name, last_name, salary FROM employee WHERE salary > ANY(SELECT salary FROM employee WHERE salary <2000);

mysql> SELECT first_name, last_name, salary FROM employee WHERE salary > SOME(SELECT salary FROM employee WHERE salary <2000);

mysql> SELECT first_name, last_name, city FROM employee WHERE EXISTS (SELECT * FROM employee WHERE city = "Toronto");

mysql> SELECT first_name, last_name, city FROM employee WHERE NOT EXISTS (SELECT * FROM employee WHERE city = "Toronto");

mysql> CREATE TABLE job ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(20));

mysql> INSERT into job VALUES (1, 'Tester'), (2, 'Accountant') , (3, 'Programmer'), (4, 'Professor');

mysql> SELECT first_name, last_name, description FROM employee WHERE description IN (SELECT title FROM job );

mysql> CREATE TABLE jobs ( job_id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(20) );

mysql> INSERT INTO jobs VALUES (1, 'Programmer'), (2, 'Tester'), (3, 'Manager'), (4, 'Spy'), (5, 'Detective'), (6, 'Forensics');

mysql> CREATE TABLE employee_join AS (SELECT * FROM employee);
mysql> ALTER TABLE employee_join ADD job_id INT;



mysql> DESC employee_join;

mysql> UPDATE employee_join,jobs SET employee_join.job_id = jobs.job_id WHERE employee_join.description = jobs.title;

mysql> SELECT * FROM employee_join;

mysql> ALTER TABLE employee_join DROP description;
mysql> SELECT * FROM employee_join;

mysql> SELECT * FROM employee_join, jobs WHERE employee_join.job_id = jobs.job_id;

mysql> SELECT * FROM employee_join INNER JOIN jobs ON employee_join.job_id = jobs.job_id;

mysql> SELECT * FROM employee_join INNER JOIN jobs USING (job_id);

mysql> SELECT * FROM employee_join NATURAL JOIN jobs;

mysql> INSERT INTO jobs VALUES (7, 'Developer');
mysql> SELECT * FROM jobs t1 LEFT JOIN employee_join t2 ON t1.job_id = t2.job_id;

mysql> SELECT first_name, last_name, title FROM employee_join INNER JOIN jobs USING(job_id);

mysql> UPDATE employee_join, jobs SET last_name = "Bono" WHERE employee_join.job_id = jobs.job_id AND jobs.title = 'Spy';

mysql> SELECT first_name, last_name, title FROM employee_join INNER JOIN jobs USING(job_id);

mysql> DELETE employee_join FROM employee_join, jobs WHERE employee_join.job_id=jobs.job_id AND jobs.title = 'Spy';

mysql> SELECT * FROM employee_join INNER JOIN jobs USING (job_id);

MODUL EMPAT

Modul 4

mysql> CREATE VIEW view1 AS SELECT CONCAT(first_name, " ", last_name), city FROM employee;

mysql> SELECT * FROM view1;

mysql> CREATE VIEW view2 (name, place) AS SELECT CONCAT(first_name, " ", last_name), city FROM employee;

mysql> SELECT * FROM view2;

mysql> CREATE ALGORITHM = MERGE VIEW v_merge (name, salary) AS SELECT CONCAT(fir

st_name," ",last_name), salary FROM employee WHERE salary > 2000;

mysql> SELECT * FROM v_merge;

mysql> SELECT * FROM v_merge WHERE salary < 5000;

mysql> CREATE ALGORITHM = TEMPTABLE VIEW tempView (city, number_of_employee) AS

SELECT city, count(id) FROM employee GROUP BY city;

mysql> SELECT * FROM tempView;

TUGAS

mysql> select*from employee_join;

mysql> select*from jobs;

1.      Buat view

mysql> select concat(first_name,last_name) as name, salary, city, title as job_desc from employee_join join jobs on employee_join.job_id=jobs.job_id;

selanjutnya modul 5