1.CREATE TABLE
command:
CREATE TABLE `tb_pengguna` ( `userid` VARCHAR(50) NOT NULL, `Nama` VARCHAR(50) , `Email` VARCHAR(50) , `No_telp` INT(12) , PRIMARY KEY (`userid`));
2.ADDED UIQUE
commend:
ALTER TABLE `tb_pengguna` ADD UNIQUE INDEX `Email` (`Email`);
3.ADDED INDEX INTO TABEL
commend:
ALTER TABLE tb_pengguna ADD INDEX index_fn_nama_notelp (Nama,No_telp);
4.EDIT FIELD DATA TYPE
commend:
ALTER TABLE tb_pengguna CHANGE COLUMN No_telp No_telp BIGINT NOT NULL DEFAULT 0 AFTER Email ;
5.INSERT DATA INTO TABLE tb_pengguna
commmend:
INSERT INTO tb_pengguna VALUES ('1', 'Nurul eka maryana', 'nurul@student', '08962345678');
6.CREATE TABLE tb_team
commend:
CREATE TABLE tb_team ( id_team INT AUTO_INCREMENT PRIMARY KEY NOT NULL, nama_team VARCHAR(100) , id_koordinator INT );
7.CREATE TABLE tb_koordinator
commend:
CREATE TABLE tb_koordinator ( id_koordinator INT AUTO_INCREMENT PRIMARY KEY NOT NULL, nama VARCHAR(100) );
8.ADD CONSTRAINT FOREIGN KEY
commend:
ALTER TABLE tb_team ADD CONSTRAINT fk_team_koordinator FOREIGN KEY (id_koordinator) REFERENCES tb_koordinator (id_koordinator) ON UPDATE CASCADE ON DELETE CASCADE ;
9.INSERT INTO TABLR tb_team AND tb_koordinator
command:
INSERT INTO tb_team VALUES ('1','teamZ','1'),('2','teamV','2'),('3','teamX', '3');
INSERT INTO tb_koordinator VALUES ('1','Nurul'), ('2','Muthia'), ('3','Kartika'),('4','Alya');
10.GET DATA FOR EACH TABLE CREATED FOR ALL FIELDS
commend:
SELECT * FROM tb_team a, tb_koordinator b WHERE a.id_koordinator=b.id_koordinator;
11.GET DATA FOR EACH TABLE CREATED FOR SOME FILEDS
commend:
SELECT id_team, nama_team FROM tb_team;
12.GET DATA FROM TABLES CREATED USING FILTER
commend:
SELECT * FROM tb_team WHERE nama_team LIKE '%Z' OR id_koordinator = '2';
13.GET DATA FROM TABLE tb_team AND tb_koordinator USING INNER JOIN
command:
SELECT a.id_team, a.nama_team, b.id_koordinator FROM tb_team a INNER JOIN tb_koordinator b ON a.id_koordinator = b.id_koordinator;
14.GET DATA FROM TABLE tb_team AND tb_koordinator USING LEFT JOIN
command:
SELECT b.id_koordinator, a.nama_team, b.nama FROM tb_team AS a LEFT JOIN tb_koordinator AS b ON a.id_koordinator = b.id_koordinator;
15.GET DATA FROM TABLE tb_team AND tb_koordinator USING RIGHT JOIN
commend:
SELECT a.id_team, b.nama, b.id_koordinator FROM tb_team a RIGHT JOIN tb_koordinator b ON a.id_koordinator = b.id_koordinator;
16.RENAME tb_pengguna TO tb_anggota
command:
17.ADD COLUMN TEAM ON tb_anggota
RENAME TABLE tb_pengguna TO tb_anggota;
command:
ALTER TABLE tb_anggota ADD COLUMN team VARCHAR(50);
18.ADD RELATION BETWEEN tb_anggota AND tb_team
commend:
ALTER TABLE tb_team ADD INDEX idx_nama_team (nama_team);
ALTER TABLE tb_anggota ADD CONSTRAINT fk_anggota_team FOREIGN KEY (team) REFERENCES tb_team (nama_team) ON UPDATE CASCADE ON DELETE CASCADE;
19.TRUNCATE ALL TABLES
command:
TRUNCATE TABLE tb_anggota;
TRUNCATE TABLE tb_team;
TRUNCATE TABLE tb_koordinator;
20.INSERT DATA INTO TABLE tb_koordinator
command:
INSERT INTO tb_koordinator VALUE ('1','EXO'),('2','BP'),('3','BTS'),('4','TW'),('5','GF'),('6','OWO');
21.INSERT DATA INTO TABLE tb_team
commend:
INSERT INTO tb_team (nama_team, id_koordinator) VALUES ('Team A', 1), ('Team B', 2), ('Team C', NULL),('Team D', 3),('Team E', NULL),('Team F', 4);
INSERT INTO tb_team (nama_team, id_koordinator) VALUES ('Team A', 1), ('Team B', 2), ('Team C', NULL),('Team D', 3),('Team E', NULL),('Team F', 4);
22.INSERT DATA INTO tb_anggota
command:
INSERT INTO tb_anggota (Nama, Email, No_telp, team) VALUES
('Aef Aditia Gelar Nugraha', 'aefaditiya@gmail.com', '082120202021', 'Team B'),
('Alya Elidhiya', 'alyael@gmail.com', '082119909192', 'Team A'),
('Ariezto Zucov', 'ariezto@gmail.com', '089876543212', 'Team C'),
('Arzenna Haqi Muhammad', 'arzennahaqi@gmail.com', '08121314151617', 'Team D'),
('Asep Rizki Juliansyah', 'aseprizki@gmail.com', '081122334455', NULL),
('David Lutfi', 'davidlutfi@gmail.com', '082233445565', NULL),
('Eben Hezer Wangsa Djaja', 'ebenhezer@gmail.com', '081254367589', 'Team E'),
('Egan Wiryawan', 'egan@gmail.com', '089828234325', 'Team A'),
('Egi Renaldi', 'egirenal@gmail.com', '085678432156', 'Team F'),
('Fadhl Gavindaffa Abdullah', 'fadhlgavin@gmail.com', '089876453214', 'Team A'),
('Fariz Fadli Rafiudin', 'farizfadli@gmail.com', '087865423565', NULL),
dan seterusnya
23.GET DATA FROM ALL TABLES WIHT NULL DATA TABLE tb_koordinator;
command:
26.GET DATA ONLY FIELD FULLNAME FROM tb_koordinator AND FULLNAME FROM tb_anggota WITH NULL DATA
select * from tb_anggota left join tb_team on tb_anggota.team = tb_team.nama_team left join tb_koordinator on tb_team.id_koordinator = tb_koordinakoordinator where tb_koordinator.nama is null;
24.GET DATA FROM ALL TABLES WITHOUT NULL DATA FROM TABLE tb_koordinator
command:
select * from tb_anggota left join tb_team on tb_anggota.team = tb_team.nama_team left join tb_koordinator on tb_team.id_koordinator = tb_koordin _koordinator where tb_koordinator.nama is not null;
25.GET DATA ONLY FIELD FROM tb_koordinator AND fullname FROM tb_anggota WITHOUT NULL DATA
command:
SELECT tb_koordinator.nama AS koordinator_nama, tb_anggota.Nama AS anggota_nama
FROM tb_anggota
JOIN tb_team ON tb_anggota.team = tb_team.nama_team
JOIN tb_koordinator ON tb_team.id_koordinator = tb_koordinator.id_koordinator
WHERE tb_anggota.Nama IS NOT NULL AND tb_koordinator.nama IS NOT NULL;
command:
SELECT tb_koordinator.nama AS koordinator_fullname, tb_anggota.Nama AS anggota_fullname
FROM tb_anggota
LEFT JOIN tb_team ON tb_anggota.team = tb_team.nama_team
LEFT JOIN tb_koordinator ON tb_team.id_koordinator = tb_koordinator.id_koordinator;
27.GET DATA FROM ALL TABLES SORT BY FULLNAME FROM tb_anggota ALPHABETICALLY
command:
SELECT tb_koordinator.nama AS koordinator_fullname, tb_anggota.nama AS anggota_fullname, tb_anggota.Nama AS anggotame, tb_team.nama_team FROM tb_anggota LEFT JOIN tb_team ON tb_anggota.team = tb_team.nama_team LEFT JOIN tb_koordinator ON tb_team.id_koordinator = tb_koordinator.id_koordinator ORDER BY tb_anggota.Nama ASC;
28.COUNT THE NUMBER OF MEMBERS BASED ON THE TEAM
command:
SELECT tb_team.nama_team AS team_name, COUNT(tb_anggota.userid) AS member_count
FROM tb_team
LEFT JOIN tb_anggota ON tb_team.nama_team = tb_anggota.team
GROUP BY tb_team.nama_team;
29.COUNT THE NUMBER OF MEMBERS BASED ON FULLNAME ON tb_koordinator THAT HAS MEMBERS MORE THAN OR EQUAL TO 5.
command:
select k.nama as koordinator, t.nama_team as team, count(a.userid) as jumlah_anggota from tb_koordinator k join tb_team t on k.id_koordinator = t.id_koordinator left join tb_anggota a on t.nama_team = a.team where k.nama in (select k2.nama from tb_koordinator k2 join tb_team t2 on k2.id_koordinator = t2.id_koordinator group by k2.nama having count(t2.nama_team) <= 5) group by k.nama, t.nama_team;
30.CREATE VIEW FROM DATA ON POINT 26
command:
create view view_member_count as select tb_team.nama_team, count(tb_anggota.userid) as member_count from tb_team left join tb_anggota on tb_team.nama_team = tb_anggota.team group by tb_team.nama_team having count(tb_anggota.userid) > 0;
31.CREATE temporary TABLE FROM DATA ON POINT 26
command:
CREATE TEMPORARY TABLE temp_table2 AS
SELECT nama_team, member_count
FROM (
SELECT k.nama AS koordinator, t.nama_team, COUNT(a.userid) AS member_count
FROM tb_koordinator k
LEFT JOIN tb_team t ON k.id_koordinator = t.id_koordinator
LEFT JOIN tb_anggota a ON t.nama_team = a.team
GROUP BY k.nama, t.nama_team
HAVING COUNT(a.userid) >= 0
ORDER BY k.nama ASC, t.nama_team ASC
)AS subquery;
32.TRUNCATE TABLE temporary
command:
TRUNCATE TABLE temp_table2;
33.INSERT DATA INTO TABLE temporary FROM VIEW USING QUERY INSERT DATA USING SELECT
command:
INSERT INTO temp_table2 VALUES ('Team G','15'), ('Team H','2');
insert into temp_table2 (nama_team, member_count) select nama_team, member_count from view_member_count;
34.ADD COLUMN captain USING DATA TYPE BOOLEAN
command:
ALTER TABLE tb_anggota ADD COLUMN captain BOOLEAN;
35.UPDATE DATA tb_anggota FOR RACH TEAM HAVE 1 CAPTAIN
command:
UPDATE tb_anggota SET captain = '1' WHERE team = 'Team G';
UPDATE tb_anggota SET captain = '1';
36.ADD COLUMN jenis_kelamin USING DATA TYPE ENUM
command:
alter table tb_anggota add column jenis_kelamin enum('L','P') not null;
37.UPDATE DATA tb_anggota FOR COLUMN jenis_kelamin
command:
update tb_anggota set jenis_kelamin = 'P' where team = 'Team B';
update tb_anggota set jenis_kelamin = 'P' where team = 'Team C';
38.GET DATA FROM 3 ALL TABLE SORT BY fullname FROM tb_anggota ALPHABETICALLY
command:
SELECT a.Nama AS anggota_nama, k.nama AS koordinator_nama, t.nama_team
FROM tb_anggota a
LEFT JOIN tb_team t ON a.team = t.nama_team
LEFT JOIN tb_koordinator k ON t.id_koordinator = k.id_koordinator
ORDER BY a.Nama ASC;
Komentar
Posting Komentar