Materi Database1

CREATE TABLE buildings (
    building_no INT PRIMARY KEY AUTO_INCREMENT,
    building_name VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL
);

CREATE TABLE rooms (
    room_no INT PRIMARY KEY AUTO_INCREMENT,
    room_name VARCHAR(255) NOT NULL,
    building_no INT NOT NULL,
    FOREIGN KEY (building_no)
        REFERENCES buildings (building_no)
        ON DELETE CASCADE
);

INSERT INTO buildings(building_name,address)
VALUES('ACME Headquaters','3950 North 1st Street CA 95134'),
      ('ACME Sales','5000 North 1st Street CA 95134');

INSERT INTO rooms(room_name,building_no)
VALUES('Amazon',1),
      ('War Room',1),
      ('Office of CEO',1),
      ('Marketing',2),
      ('Showroom',2);

DELETE FROM buildings
WHERE building_no = 2;

ALTER TABLE rooms ADD CONSTRAINT fk_grade_id FOREIGN KEY (building_no) REFERENCES buildings(building_no) on delete cascade;

CREATE TABLE IF NOT EXISTS rooms2

SELECT * FROM rooms;

SELECT * FROM Customers INNER JOIN Orders ON Orders.CustomerID = Customers.CustomerID;

select * from mahasiswa RIGHT JOIN praktikan ON mahasiswa.id = praktikan.mahasiswa_id where praktikan.asal_kelas_id = 3 limit 3;

select * from praktikan where tgl_registrasi BETWEEN '2019-09-03' AND '2019-09-04';

select * from praktikan where tgl_registrasi BETWEEN '2019-09-03' AND '2019-09-04';

select * from praktikan order by mahasiswa_id asc;
select * from praktikan order by mahasiswa_id desc;



select * from mahasiswa where nama like '%Ahmad%';
select * from mahasiswa where nama like '%Ahmad';
select * from mahasiswa where nama like 'Ahmad%';

select * from praktikan where tgl_registrasi BETWEEN '2019-09-01' AND '2019-09-30' order by tgl_registrasi DESC;


select max(total) as rata from penjualan;
select min(total) as rata from penjualan;
select avg(total) as rata from penjualan;
select sum(total) as rata from penjualan;

select sum(total) as totalPenjualan from penjualan where tgl between '2013-08-01' and '2013-08-30';

Cari nama mahasiswa yang daftar praktikum bulan 09-2019

select m.nama,p.tgl_registrasi from mahasiswa m join praktikan p on m.id=p.id where tgl_registrasi between '2019-09-01' and '2019-09-30' order by tgl_registrasi desc;


cari rata2 dari total ketinggian air dalam bulan 08-2013

Insert update delete select, between, like, distinct, order by, group by,having, join (inner, outer, left, right, cross), fungsi agregat (avg, min, max, sum)