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
















































0 komentar:

Posting Komentar