SQL: ALTER TABLE Statement

ALTER TABLE deyimi, tabloyu yeniden adlandirma, colon ekleme, silme, degistirmek icin kullanilir. Asagida, ALTER TABLE deyimi ile birlikte neler yapabiliriz onlari gorelim.

Temel olarak bir tablonun adini degistirmek:

ALTER TABLE table_name RENAME TO new_table_name;

Ornek :

ALTER TABLE students RENAME TO ogrenciler;

Tabloya kolon(lar) ekleme :

ALTER TABLE table_name  ADD column_name column-definition;

Ornek :

ALTER TABLE student  ADD email varchar2(50);

Kolonlar ekleme :

ALTER TABLE table_name
ADD (	column_1	column-definition,
	column_2	column-definition,
	...
	column_n	column_definition );

Ornek :

ALTER TABLE student
ADD (	first_name	varchar2(50),
	city	varchar2(45) );

Kolon bilgilerini degistirme :

ALTER TABLE table_name  MODIFY column_name column_type;

Ornek :

ALTER TABLE students  MODIFY email varchar2(100) not null;

Kolon Silme :
ALTER TABLE table_name  DROP COLUMN column_name;

Ornek :

ALTER TABLE students  DROP email ;

Kolon Ismini degistirme :
ALTER TABLE table_name  RENAME COLUMN old_name to new_name;

 Ornek : 

 ALTER TABLE students  RENAME COLUMN email to mail_address;

Practice Exercise #1:

Asagidaki departments tablosunun ismini degistirin.

CREATE TABLE departments
(	department_id	number(10)	not null,
	department_name	varchar2(50)	not null,
	CONSTRAINT departments_pk PRIMARY KEY (department_id)
);

Solution:

ALTER TABLE departments
 RENAME TO depts;

Practice Exercise #2:

Asagidaki Employees tablosuna, ismini salary, datatype number(6) olan bir kolon ekleyin.

CREATE TABLE employees
(	employee_number	number(10)	not null,
	employee_name	varchar2(50)	not null,
	department_id	number(10),
	CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

Solution:

            

ALTER TABLE employees ADD salary number(6);

Practice Exercise #3:

Asagidaki Customers tablosuna, contact_name isminde ve varchar2(50) tipinde ve last_contancted isminde ve tarih tipinde colonlar ekleyin.

CREATE TABLE customers
(	customer_id	number(10)	not null,
	customer_name	varchar2(50)	not null,
	address	varchar2(50),
	city	varchar2(50),
	state	varchar2(25),
	zip_code	varchar2(10),
	CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

Solution:

ALTER TABLE customers
ADD (	contact_name	varchar2(50),
	last_contacted	date );

Practice Exercise #4:

Employees tablosunda employee_name kolonun datatipini, 75 yapiniz.

CREATE TABLE employees
(	employee_number	number(10)	not null,
	employee_name	varchar2(50)	not null,
	department_id	number(10),
	CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

Solution:

ALTER TABLE employees
 MODIFY employee_name varchar2(75);

Practice Exercise #5:

change the customer_name column to NOT allow null values and change the state column to a varchar2(2) datatype.

CREATE TABLE customers
(	customer_id	number(10)	not null,
	customer_name	varchar2(50),
	address	varchar2(50),
	city	varchar2(50),
	state	varchar2(25),
	zip_code	varchar2(10),
	CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

Solution:

ALTER TABLE customers
MODIFY (	customer_name	varchar2(50) not null,
	state	varchar2(2) );

Practice Exercise #6:

Asagidaki tablodaki salary kolonunu siliniz.

CREATE TABLE employees
(	employee_number	number(10)	not null,
	employee_name	varchar2(50)	not null,
	department_id	number(10),
	salary	number(6),
	CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

Solution:

ALTER TABLE employees  DROP COLUMN salary;

Practice Exercise #7:

Asagiadki tablodaki department_name kolonunun ismini, dept_name ile degistirin.

CREATE TABLE departments
(	department_id	number(10)	not null,
	department_name	varchar2(50)	not null,
	CONSTRAINT departments_pk PRIMARY KEY (department_id)
);

Solution:

ALTER TABLE departments
 RENAME COLUMN department_name to dept_name;