LAB MYSQL
1.Membuat tabel pengguna
Sintaks SQL:
CREATE TABLE `tb_pengguna` (
`Userid` VARCHAR(50) NOT NULL,
`Nama` VARCHAR(50) NOT NULL,
`Email` VARCHAR(50) NOT NULL,
`No_telp` bigint NOT NULL DEFAULT 0,
PRIMARY KEY (`Userid`),
UNIQUE KEY `idx_uniq_tb_pengguna` (`Email`),
UNIQUE KEY `idx_uniq_tb_pengguna` (`Nama`,`No_telp`);
Hasil output:
2.Added Unique Into Table
Contoh sintaks SQL:
ALTER TABLE `tb_pengguna` ADD UNIQUE INDEX unique_email (Email);
3.Added Index Into Table
Contoh sintaks SQL:
ALTER TABLE `tb_pengguna` ADD INDEX idx_nm_nama_notelp (`Nama,`No_telp`);
Hasil output:
4.Edit Field Data Type
Contoh sintaks SQL:
alter table tb_pengguna modify email VARCHAR(100);
Hasil output
5.Insert Data Into Table tb_pengguna
Contoh sintaks SQL:
INSERT INTO tb_pengguna VALUES ('dyka.permana','Andyka Putra Permana','andyka@student.email','082119265376');
Hasil output:
6.Membuat Table team
Contoh sintaks SQL:
CREATE TABLE `tb_team` (
`id_team` INT(11) NOT NULL AUTO_INCREMENT,
`nama_team` VARCHAR(50) DEFAULT NULL,
`id_koordinator` INT(1) NULL DEFAULT NULL,
PRIMARY KEY(`id_team`)USING BTREE);
Hasil output:
7.Membuat Table Koordinator
Contoh sintaks SQL:
CREATE TABLE tb_koordinator (
id_koordinator INT,
nama VARCHAR(50)
);
Hasil output:
Hasil output:
8.Menambahka Constraint Foreign Key Dengan Tambahan Update Cascade On Delete Cascade
Contoh sintaks SQL:
ALTER TABLE tb_team
ADD CONSTRAINT fk_team_koordinator
FOREIGN KEY (id_koordinator)
REFERENCES tb_koordinator(id_koordinator)
ON UPDATE CASCADE
ON DELETE CASCADE;
Hasil output:
9.Insert Into Table tb_team And tb_koordinator
Contoh sintaks SQL:
INSERT INTO tb_koordinator VALUES(19,'ufotable');
INSERT INTO tb_team VALUES(20,'anime',238);
Hasil output:
10.Get Data For Each Table Created for all fields
Contoh sintaks SQL:
SELECT nama, email, no_telp FROM tb_pengguna;
Hasil output:
11.Get Data For Each Table Created for some fields
Contoh sintaks SQL:
select nama from karyawan;
Hasil output:
12.Get Data From Tables Created Using Filter
Contoh sintaks SQL:
select *from tb_pengguna WHERE nama='Fajar Santoso';
Hasil ouput:
13.Get Data From Table tb_team And tb_koordinator Using Inner Join
Contoh sintaks SQL:
select *from tb_team as a INNER JOIN tb_koordinator as b ON a.id_team= id_koordnator;
Hasil output:
14.Get Data From Table tb_team And tb_koordinator Using LEFT JOIN
Contoh sintak SQL:
select *from tb_team as a LEFT JOIN tb_koordinator as b ON a.id_team= id_koordnator;
Hasil output:
15.Get Data From Tabel tb_team And tb_koordinator Using RIGHT JOIN
Contoh sintaks SQL:
select *from tb_team as a RIGHT JOIN tb_koordinator as b ON a.id_team= id_koordnator;
Hasil output:
16.Rename Tb_pengguna To Tb_anggota
Contoh sintaks SQL:
ALTER TABLE tb_pengguna RENAME TO tb_anggota;
Hasil output:
17.Add Column Team On Tb_anggota
Contoh sintaks SQL:
ALTER TABLE tb_anggota ADD COLUMN team VARCHAR(50);
Hasil output:
18.Add Relation Between Tb_anggota And Tb_team
Contoh sintaks SQL:
ALTER TABLE tb_anggota ADD CONSTRAINT fk_anggota_team FOREIGN KEY (Teamm) REFERECE tb_team(nama_team) ON UPDATE CASCADE ON DELETE CASCADE;
Hasil output:
19.Truncate All Tables
Contoh sintaks SQL:
TRUNCATE TABLE tb_team;
TRUNCATE TABLE tb_anggota;
TRUNCATE TABLE tb_koordinator;
Hasil output:
20.Insert Data Into Table tb_koordinator at least 5 data
Contoh sintaks SQL:
INSERT INTO tb_koordinator (id_koordinator,nama)
VALUES ('1','Manay');
Hasil output:
21.Insert Data Into Table tb_team at least 6 data with 2 data null for column id_koordinator
Contoh sintaks SQL:
Hasil output:
22.Insert Data Into Table tb_anggota at least 60 data with some of field team from tb_team and some field team with null values
Contoh sintaks SQL:
insert into tb_anggota values ('1','Andy','ndyy@gmail.com','088272536278','EVOS');
Hasil output:
23.Get Data From All Tables With Null Data From Table Tb_koordinator
Contoh sintaks SQL:
24.Get Data From All Tables Without Null Data From Table Tb_koordinator
Contoh sintaks SQL:
25.Get Data only field fullname from tb_koordinator and fullname from tb_anggota without null data
SELECT k.fullname AS koordinator_fullname, a.fullname AS anggota_fullname FROM tb_koordinator k INNER JOIN tb_anggota a ON a.id_koordinator = k.id WHERE k.fullname IS NOT NULL AND a.fullname IS NOT NULL;
Hasil output:
26.Get Data only field fullname from tb_koordinator and fullname from tb_anggota with null data
Contoh sintaks SQL:
27.Get Data From All Tables Sort By Fullname From Tb_anggota Alphabetically
Contoh sintaks SQL:
Hasil output:
28.Count The Number Of Members Based On The Team
Hasil output:
29.Count The Number Of Members Based On Fullname On Tb_koordinator That Has Members More Than Or Equal To 5.
Hasil output:
30.Create View From Data On Point 26
Hasil output:
31.Create Temporary Table From Data On Point 26
Hasil output:
32.Truncate Table Temporary
Hasil output:
33.Insert Data Into Table Temporary From View Using Query Insert Data Using Select
34.Add Column Captain Using Data Type Boolean
Hasil output:
35.Update Data tb_anggota For Rach Team Have 1 Captain
36.Add Column jenis_kelamin Using Data Type Enum
Hasil output:
37.Update Data tb_anggota For Column jenis_kelamin
38.Get Data From 3 All Table Sort By Fullname From Tb_anggota Alphabetically
hasil output:
Contoh sintaks SQL:
SELECT nama, email, no_telp FROM tb_pengguna;
Hasil output:
11.Get Data For Each Table Created for some fields
Contoh sintaks SQL:
select nama from karyawan;
Hasil output:
12.Get Data From Tables Created Using Filter
Contoh sintaks SQL:
select *from tb_pengguna WHERE nama='Fajar Santoso';
Hasil ouput:
13.Get Data From Table tb_team And tb_koordinator Using Inner Join
Contoh sintaks SQL:
select *from tb_team as a INNER JOIN tb_koordinator as b ON a.id_team= id_koordnator;
Hasil output:
14.Get Data From Table tb_team And tb_koordinator Using LEFT JOIN
Contoh sintak SQL:
select *from tb_team as a LEFT JOIN tb_koordinator as b ON a.id_team= id_koordnator;
Hasil output:
15.Get Data From Tabel tb_team And tb_koordinator Using RIGHT JOIN
Contoh sintaks SQL:
select *from tb_team as a RIGHT JOIN tb_koordinator as b ON a.id_team= id_koordnator;
Hasil output:
16.Rename Tb_pengguna To Tb_anggota
Contoh sintaks SQL:
ALTER TABLE tb_pengguna RENAME TO tb_anggota;
Hasil output:
17.Add Column Team On Tb_anggota
Contoh sintaks SQL:
ALTER TABLE tb_anggota ADD COLUMN team VARCHAR(50);
Hasil output:
18.Add Relation Between Tb_anggota And Tb_team
Contoh sintaks SQL:
ALTER TABLE tb_anggota ADD CONSTRAINT fk_anggota_team FOREIGN KEY (Teamm) REFERECE tb_team(nama_team) ON UPDATE CASCADE ON DELETE CASCADE;
Hasil output:
19.Truncate All Tables
Contoh sintaks SQL:
TRUNCATE TABLE tb_team;
TRUNCATE TABLE tb_anggota;
TRUNCATE TABLE tb_koordinator;
Hasil output:
20.Insert Data Into Table tb_koordinator at least 5 data
Contoh sintaks SQL:
INSERT INTO tb_koordinator (id_koordinator,nama)
VALUES ('1','Manay');
Hasil output:
21.Insert Data Into Table tb_team at least 6 data with 2 data null for column id_koordinator
Contoh sintaks SQL:
INSERT INTO tb_team (id, team_name, id_koordinator)
VALUES
(1, 'Team A', 1),
(2, 'Team B', NULL),
(3, 'Team C', 2),
(4, 'Team D', NULL),
(5, 'Team E', 3),
(6, 'Team F', NULL);
Hasil output:
22.Insert Data Into Table tb_anggota at least 60 data with some of field team from tb_team and some field team with null values
Contoh sintaks SQL:
insert into tb_anggota values ('1','Andy','ndyy@gmail.com','088272536278','EVOS');
Hasil output:
23.Get Data From All Tables With Null Data From Table Tb_koordinator
Contoh sintaks SQL:
SELECT * FROM tb_team t LEFT JOIN tb_anggota a ON t.id = a.team LEFT JOIN tb_koordinator k ON t.id_koordinator = k.id WHERE k.id IS NULL;
Hasil output:
Hasil output:
24.Get Data From All Tables Without Null Data From Table Tb_koordinator
Contoh sintaks SQL:
SELECT * FROM tb_team t INNER JOIN tb_anggota a ON t.id = a.team INNER JOIN tb_koordinator k ON t.id_koordinator = k.id;
Hasil output:
Hasil output:
25.Get Data only field fullname from tb_koordinator and fullname from tb_anggota without null data
SELECT k.fullname AS koordinator_fullname, a.fullname AS anggota_fullname FROM tb_koordinator k INNER JOIN tb_anggota a ON a.id_koordinator = k.id WHERE k.fullname IS NOT NULL AND a.fullname IS NOT NULL;
Hasil output:
26.Get Data only field fullname from tb_koordinator and fullname from tb_anggota with null data
Contoh sintaks SQL:
SELECT k.fullname AS koordinator_fullname, a.fullname AS anggota_fullname FROM tb_koordinator k LEFT JOIN tb_anggota a ON a.id_koordinator = k.id WHERE k.fullname IS NULL OR a.fullname IS NULL;
Hasil output:
Hasil output:
27.Get Data From All Tables Sort By Fullname From Tb_anggota Alphabetically
Contoh sintaks SQL:
SELECT t.*, k.fullname AS koordinator_fullname, a.fullname AS anggota_fullname
FROM tb_team t
INNER JOIN tb_koordinator k ON t.id_koordinator = k.id
INNER JOIN tb_anggota a ON t.id = a.team
ORDER BY a.fullname ASC;
Hasil output:
28.Count The Number Of Members Based On The Team
SELECT team, COUNT(*) as member_count
FROM tb_anggota
GROUP BY team;
Hasil output:
29.Count The Number Of Members Based On Fullname On Tb_koordinator That Has Members More Than Or Equal To 5.
SELECT k.fullname AS koordinator_fullname, COUNT(*) AS member_count
FROM tb_koordinator k
JOIN tb_team t ON k.id = t.id_koordinator
JOIN tb_anggota a ON t.id = a.team
GROUP BY k.fullname
HAVING COUNT(*) >= 5;
Hasil output:
30.Create View From Data On Point 26
CREATE VIEW koordinator_member_count AS
SELECT k.fullname AS koordinator_fullname, COUNT(*) AS member_count
FROM tb_koordinator k
JOIN tb_team t ON k.id = t.id_koordinator
JOIN tb_anggota a ON t.id = a.team
GROUP BY k.fullname
HAVING COUNT(*) >= 5;
Hasil output:
31.Create Temporary Table From Data On Point 26
CREATE TEMPORARY TABLE temp_koordinator_member_count AS
SELECT k.fullname AS koordinator_fullname, COUNT(*) AS member_count
FROM tb_koordinator k
JOIN tb_team t ON k.id = t.id_koordinator
JOIN tb_anggota a ON t.id = a.team
GROUP BY k.fullname
HAVING COUNT(*) >= 5;
Hasil output:
32.Truncate Table Temporary
TRUNCATE TABLE temp_koordinator_member_count;
Hasil output:
33.Insert Data Into Table Temporary From View Using Query Insert Data Using Select
CREATE TEMPORARY TABLE temp_koordinator_member_count AS
SELECT k.fullname AS koordinator_fullname, COUNT(*) AS member_count
FROM tb_koordinator k
JOIN tb_team t ON k.id = t.id_koordinator
JOIN tb_anggota a ON t.id = a.team
GROUP BY k.fullname
HAVING COUNT(*) >= 5;
Hasil output:
Hasil output:
34.Add Column Captain Using Data Type Boolean
ALTER TABLE tb_anggota
ADD COLUMN captain BOOLEAN;
Hasil output:
35.Update Data tb_anggota For Rach Team Have 1 Captain
UPDATE tb_anggota
SET captain = 1
WHERE id IN (
SELECT id
FROM tb_anggota
WHERE captain IS NULL
ORDER BY team
LIMIT (SELECT COUNT(DISTINCT team) FROM tb_anggota);
hasil output:
hasil output:
36.Add Column jenis_kelamin Using Data Type Enum
ALTER TABLE tb_anggota
ADD COLUMN jenis_kelamin ENUM('Laki-laki', 'Perempuan');
Hasil output:
37.Update Data tb_anggota For Column jenis_kelamin
UPDATE tb_anggota
SET jenis_kelamin = 'Perempuan'
WHERE nama = 'Jane Doe';
38.Get Data From 3 All Table Sort By Fullname From Tb_anggota Alphabetically
SELECT a.fullname, t.nama_team, k.nama_koordinator
FROM tb_anggota a
LEFT JOIN tb_team t ON a.team = t.id_team
LEFT JOIN tb_koordinator k ON t.id_koordinator = k.id_koordinator
ORDER BY a.fullname ASC;
hasil output:
Komentar
Posting Komentar