Tugas 4 kelompok SMBD modul 2

tugas sebelumnya ada di sini klik

TUGAS 4 Kelompok SMBD

TUGAS 4 KELOMPOK

SISTEM MANAJEMEN BASIS DATA

   NAMA KELOMPOK :

                                       Andrianto                      : 101051005

                                       Nur Arifah Juliyanti     : 101051047

                                       Anggreawan                 : 101051059

                                       Bambang Wahyu S       : 101051081

                                   

                            

JURUSAN TEKNIK INFORMATIKA

FAKULTAS TEKNOLOGI INDUSTRI

INSTITUT SAINS DAN TEKNOLOGI AKPRIND YOGYAKARTA

2013

Modul 2

mysql> CREATE DATABASE PRAK2;

mysql>use PRAK2;

mysql>CREATE TABLE TABLE1(id INT AUTO_INCREMENT primary key,name VARCHAR(30) NOT NULL,salary FLOAT(10,2) DEFAULT 1000000);

mysql> DESCRIBE TABLE1;

mysql> CREATE TABLE TABLE2(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(30));

mysql> DESCRIBE TABLE2;

mysql> CREATE TABLE TABLE3(id INT AUTO_INCREMENT,name VARCHAR(30),PRIMARY KEY (id));

mysql> DESCRIBE TABLE3;

mysql> CREATE TABLE cars(id INT PRIMARY KEY AUTO_INCREMENT,plate VARCHAR (10),brand VARCHAR (10),UNIQUE (plate));

mysql> DESC cars;

mysql> INSERT INTO cars VALUES (1, 'AB-1234-CD', 'Toyota');

mysql> SELECT * FROM cars;

mysql> INSERT INTO cars VALUES (2, 'AB-1234-CD', 'Suzuki');

mysql> SHOW TABLES;

mysql> CREATE TABLE IF NOT EXISTS TABLE12(id INT AUTO_INCREMENT primary key, name VARCHAR(30) NOT NULL, salary FLOAT(10,2) DEFAULT 1000000);

mysql> CREATE TABLE IF NOT EXISTS TABLE4(id INT AUTO_INCREMENT primary key,name VARCHAR(30) NOT NULL,salary FLOAT(10,2) DEFAULT 1000000);

mysql> SHOW TABLES;

mysql> CREATE TABLE employee(id INT AUTO_INCREMENT PRIMARY KEY,first_name VARCHAR(15),last_name VARCHAR(15),start_date DATE,end_date DATE,salary FLOAT(8,2),city VARCHAR(10),description VARCHAR(15));

mysql> DESCRIBE employee;

mysql> LOAD DATA INFILE 'G://employee1.txt' INTO TABLE employee FIELDS TERMINATED BY '\t';

mysql> CREATE TABLE employee_copy AS SELECT * FROM employee WHERE start_date BETWEEN '1970-01-01' AND '1990-12-31';

mysql> SHOW TABLES;

mysql> SELECT * FROM employee_copy;

mysql> CREATE TEMPORARY TABLE TEMPTBL(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(30));

mysql> SHOW TABLES;

mysql> show databases;

mysql> USE PRAK2

mysql> SELECT * FROM TEMPTBL;

mysql> CREATE TABLE models (modelid smallint not null auto_increment,name varchar(40) not

null,primary key (modelid))engine=InnoDB;

mysql> desc models;

mysql> CREATE TABLE orders (id SMALLINT NOT NULL PRIMARY KEY,modelid SMALLINT NOT NULL,description VARCHAR(40),FOREIGN KEY (modelid) REFERENCES models (modelid) ON UPDATE CASCADE ON DELETE CASCADE) engine=InnoDB;

mysql> desc orders;

mysql> INSERT INTO models VALUES (1, 'testsatu');

mysql> SELECT * FROM models;

mysql> insert into orders value (1, 1, 'deskripsisatu');

mysql> select * from orders;

mysql> DELETE FROM models WHERE modelid = 1;

mysql> SELECT * FROM models;

mysql> select * from orders;

mysql> CREATE TABLE TABLE5(id int);

mysql> desc TABLE5;

mysql> ALTER TABLE TABLE5 ADD name VARCHAR(5);

mysql> desc TABLE5;

mysql> ALTER TABLE TABLE5 ADD first_name VARCHAR(30) AFTER id;

mysql> desc TABLE5;

mysql> ALTER TABLE TABLE5 CHANGE name last_name VARCHAR(30);

mysql> desc TABLE5;

mysql> ALTER TABLE TABLE5 MODIFY first_name VARCHAR(15),MODIFY last_name VARCHAR(15);

mysql> desc TABLE5;

mysql> ALTER TABLE TABLE5 ADD PRIMARY KEY (id);

mysql> ALTER TABLE TABLE5 DROP first_name;

mysql> DESC TABLE5;

mysql> SHOW TABLES;

mysql> ALTER TABLE employee_copy RENAME senior_employee;

mysql> SHOW TABLES;

mysql> CREATE TABLE employee_copy AS SELECT * FROM employee;

mysql> SELECT * FROM employee_copy;

mysql> TRUNCATE TABLE employee_copy;

mysql> SELECT * FROM employee_copy;

mysql> DROP TABLE employee_copy;

mysql> SHOW TABLES;

mysql> DESC employee;

mysql> INSERT INTO employee VALUES

('9','James','Bond','1982-04-21','2002-09-23','1234.56','London','Spy'),

('10','Hercule','Poirot','1973-05-23','2001-08-09','4312.98','Brussels','Detective'),

('11','Lincoln','Rhyme','1999-05-25','2011-07-13','3212.98','New York','Forensics'),

('12','Sherlock','Holmes','1923-08-12','1945-07-21','4123.21','London','Detective');

mysql> SELECT * FROM employee;

mysql> CREATE TABLE employee2 (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(15),city VARCHAR(20));

mysql> DESC employee2;

mysql> INSERT INTO employee2 (id, name, city) SELECT id, CONCAT(first_name,' ',last_name), city

FROM employee;

mysql> SELECT * FROM employee2;

mysql> UPDATE employee2 SET city = 'Ottawa'WHERE city = 'Vancouver';

mysql> SELECT * FROM employee2;

mysql> SELECT salary FROM employee;

mysql> UPDATE employee SET salary = salary + 1;

mysql> SELECT salary FROM employee;

mysql> REPLACE INTO employee2 VALUES
mysql> (12,'Sherlock Holmes','Manchester');
mysql> SELECT * FROM employee2;
mysql> SELECT * FROM employee2;
mysql> DELETE FROM employee2 WHERE city = 'New York';
mysql> SELECT * FROM employee2;

Penjelasan sederhan :

tugas modul selanjutnya di sini klik ini