Rabu, 28 Maret 2012

The ALTER TABLE statement allows you to rename an existing table. It can also be used to add, modify, or drop a column from an existing table.

Renaming a table

The basic syntax for renaming a table is:

ALTER TABLE table_name
 RENAME TO new_table_name;

For Example:


ALTER TABLE suppliers
 RENAME TO vendors;
This will rename the suppliers table to vendors.

Adding column(s) to a table

Syntax #1
To add a column to an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
 ADD column_name column-definition;

For Example:


ALTER TABLE supplier
 ADD supplier_name  varchar2(50);
This will add a column called supplier_name to the supplier table.

Syntax #2
To add multiple columns to an existing table, the ALTER TABLE syntax is:
ALTER TABLE table_name
ADD (column_1column-definition,
column_2column-definition,
...
column_ncolumn_definition );

For Example:

ALTER TABLE supplier
ADD (supplier_namevarchar2(50),
cityvarchar2(45) );
This will add two columns (supplier_name and city) to the supplier table.

Modifying column(s) in a table

Syntax #1
To modify a column in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
 MODIFY column_name column_type;

For Example:


ALTER TABLE supplier
 MODIFY supplier_name   varchar2(100)     not null;
This will modify the column called supplier_name to be a data type of varchar2(100) and force the column to not allow null values.

Syntax #2
To modify multiple columns in an existing table, the ALTER TABLE syntax is:
ALTER TABLE table_name
MODIFY (column_1column_type,
column_2column_type,
...
column_ncolumn_type );

For Example:

ALTER TABLE supplier
MODIFY (supplier_namevarchar2(100)not null,
cityvarchar2(75));
This will modify both the supplier_name and city columns.

Drop column(s) in a table

Syntax #1
To drop a column in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
 DROP COLUMN column_name;

For Example:


ALTER TABLE supplier
 DROP COLUMN supplier_name;
This will drop the column called supplier_name from the table called supplier.

Rename column(s) in a table
(NEW in Oracle 9i Release 2)

Syntax #1
Starting in Oracle 9i Release 2, you can now rename a column.
To rename a column in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
 RENAME COLUMN old_name to new_name;

For Example:


ALTER TABLE supplier
 RENAME COLUMN supplier_name to sname;
This will rename the column called supplier_name to sname.
Acknowledgements: Thanks to Dave M., Craig A., and Susan W. for contributing to this solution!

Practice Exercise #1:
Based on the departments table below, rename the departments table to depts.
CREATE TABLE departments
(department_idnumber(10)not null,
department_namevarchar2(50)not null,
CONSTRAINT departments_pk PRIMARY KEY (department_id)
);
Solution:
The following ALTER TABLE statement would rename the departments table to depts:

ALTER TABLE departments
 RENAME TO depts;

Practice Exercise #2:
Based on the employees table below, add a column called salary that is a number(6) datatype.
CREATE TABLE employees
(employee_numbernumber(10)not null,
employee_namevarchar2(50)not null,
department_idnumber(10),
CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);
Solution:
The following ALTER TABLE statement would add a salary column to the employees table:

ALTER TABLE employees
 ADD salary number(6);

Practice Exercise #3:
Based on the customers table below, add two columns - one column called contact_name that is a varchar2(50) datatype and one column called last_contacted that is a date datatype.
CREATE TABLE customers
(customer_idnumber(10)not null,
customer_namevarchar2(50)not null,
addressvarchar2(50),
cityvarchar2(50),
statevarchar2(25),
zip_codevarchar2(10),
CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);
Solution:
The following ALTER TABLE statement would add the contact_name and last_contacted columns to the customers table:
ALTER TABLE customers
ADD (contact_namevarchar2(50),
last_contacteddate );

Practice Exercise #4:
Based on the employees table below, change the employee_name column to a varchar2(75) datatype.
CREATE TABLE employees
(employee_numbernumber(10)not null,
employee_namevarchar2(50)not null,
department_idnumber(10),
CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);
Solution:
The following ALTER TABLE statement would change the datatype for the employee_name column to varchar2(75):

ALTER TABLE employees
 MODIFY employee_name varchar2(75);

Practice Exercise #5:
Based on the customers table below, change the customer_name column to NOT allow null values and change the state column to a varchar2(2) datatype.
CREATE TABLE customers
(customer_idnumber(10)not null,
customer_namevarchar2(50),
addressvarchar2(50),
cityvarchar2(50),
statevarchar2(25),
zip_codevarchar2(10),
CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);
Solution:
The following ALTER TABLE statement would modify the customer_name and state columns accordingly in the customers table:
ALTER TABLE customers
MODIFY (customer_namevarchar2(50) not null,
statevarchar2(2) );

Practice Exercise #6:
Based on the employees table below, drop the salary column.
CREATE TABLE employees
(employee_numbernumber(10)not null,
employee_namevarchar2(50)not null,
department_idnumber(10),
salarynumber(6),
CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);
Solution:
The following ALTER TABLE statement would drop the salary column from the employees table:

ALTER TABLE employees
 DROP COLUMN salary;

Practice Exercise #7:
Based on the departments table below, rename the department_name column to dept_name.
CREATE TABLE departments
(department_idnumber(10)not null,
department_namevarchar2(50)not null,
CONSTRAINT departments_pk PRIMARY KEY (department_id)
);
Solution:
The following ALTER TABLE statement would rename the department_name column to dept_name in the departments table:

ALTER TABLE departments
 RENAME COLUMN department_name to dept_name;

Senin, 19 Maret 2012

Contoh script php untuk keperluan extract/unzip dengan nama file archive.zip dan akan di extract ke folder /tmp/extracted/ :
// create object
$zip = new ZipArchive() ;
// open archive
if ($zip->open(‘archive. zip’) !== TRUE) {
die (‘Tidak Dapat Dibuka’);
}
// extract contents to destination directory
$zip->extractTo(‘/ tmp/extracted/ ‘);
// close archive
// print success message

$zip->close();
echo ‘Extract file Berhasil’;

Rabu, 14 Maret 2012

Mungkin ini hanya dokumentasi dari yang pernah saya lakukan disaat akan reset sequence pada oracle.

--Drop sequence
DROP SEQUENCE MY_SEQ;

-- Create sequence 
create sequence MY_SEQ
minvalue 1
maxvalue 999999999999999999999
start with 1
increment by 1
cache 20;

Senin, 12 Maret 2012

Jika mengalami error pada tampilan awal di xampp seperti gambar berikut


Maka untuk mengatasinya yaitu dengan memodifikasi file httpd-xampp.conf yang letak filenya di c:/xampp/apache/conf/extra. Dengan membukanya menggunakan notepad, kemudian jika anda ingin menambahkan user lain agar bisa mengakses http://ip-server/xampp maka anda harus menambahkan ip user ke file tersebut.
Misalnya ip usernya 10.252.247.214 maka ip tersebut tinggal ditambahkan setelah 127.0.0.0/8.

# Close XAMPP sites here
<LocationMatch "^/(?i:(?:xampp|licenses|phpmyadmin|webalizer|server-status|server-info))">
    Order deny,allow
    Deny from all
    Allow from ::1 127.0.0.0/8 10.252.247.214
    ErrorDocument 403 /error/HTTP_XAMPP_FORBIDDEN.html.var
</LocationMatch>

 Jika ingin diperluas bisa ditambahkan dengan 10.252.247.0/24. Atau jika ingin semua bisa mengakses maka "Deny from all" diganti dengan "Allow from all"

Setelah itu simpan dan restartlah apachenya. Semoga Bermanfaat.

Minggu, 11 Maret 2012

Setelah pusing-pusing googling internet cara untuk mengamankan MySQL serta saat user mengakses server kita maka dipaksa harus mengisikan user serta password, Sebenarnya di halaman awal xampp sudah ada menu untuk men-security-nya sehingga tinggal ikuti aja perintahnya… sangatlah mudah cara untuk secure phpmyadmin serta server xampp. kita Okelah langsung aja saya tunjukkan file-file mana saja yang harus di edit agar phpmyadmin kita tidak bisa di akses semua orang.
Langkah untuk secure di xampp:
 Mengamankan server xampp dan php
1. Masuk ke http://localhost, maka akan muncul halaman index dari xampp.. Pada menu sebelah kiri terdapat menu security.. coba km klik maka akan muncul window baru seperti gambar di bawah ini
2. Dari Tabel tersebut kita bisa melihat bahwa:
* Halaman XAMPP tidaklah aman(unsecure) dan dapat diakses oleh siapa saja yang tersambung dengan jaringan,
* Administrator database Mysql dengan user root juga tidak terproteksi
* PhpMyAdmin terbuka dan bebas diakses oleh jaringan
* PHP tidak berjalan di “safe mode”

3. Sekarang kita akan mengamankan point 1,2 dan 3 dengan masuk ke http://localhost/security/xamppsecurity.php
 Mengamankan server xampp dan php
Nah dari halaman tersebut kita dapat men-set password phpMyadmin dengan user default adalah root.. phpMyadmin authentification secara default cookie.. jika pengen menyimpan password yang belum ter-enkripsi centang aja pilihan “safe plain password in text file?”. Untuk menganmankan direktori Xampp (waktu kita masuk ke localhost akan muncul kotak login) maka kamu dapat men-set user dan password sesuka kamu.. (kalo km pelupa maka centang aja pilihan unutk menyimpan plaintext pass-nya).
Subscribe to RSS Feed Follow me on Twitter!