DDL, DML and DQL
DDL - Create, Alter, Drop and Truncate
Create table Employee_info
(
Emp_ID varchar(30),
Fname varchar(20),
Lname varchar(30),
Desg varchar(35),
sal number(5) );
Desc Employee_info;
Alter Table Employee_info rename to
employee_data1;
Desc employee_data1;
Alter table employee_data1
Add Address varchar(100);
Alter table employee_data1 modify Lname
varchar(20);
Alter table employee_data1drop column Address;
desc employee_data1;
Alter Command:
Add a Column to an Existing Table:
ALTER TABLE table_name
ADD column_name data_type;
Ex: ALTER TABLE employees
ADD email VARCHAR2(100);
Modify an Existing Column:
ALTER TABLE table_name
MODIFY column_name new_data_type;
Ex: ALTER TABLE employees
MODIFY salary NUMBER(10, 2);
Rename a Column:
ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;
Ex: ALTER TABLE employees
RENAME COLUMN emp_name TO employee_name;
Drop a Column:
ALTER TABLE table_name
DROP COLUMN column_name;
Ex: ALTER TABLE employees
DROP COLUMN email;
Add or Modify Constraints:
To modify constraint
ALTER TABLE employees
ADD CONSTRAINT pk_employee_id PRIMARY KEY (employee_id);
Rename table:
ALTER TABLE old_table_name
RENAME TO new_table_name;
Ex:
ALTER TABLE employees
RENAME TO staff;
Author
UPDATE author
SET auth_city = Delhi
WHERE employee_id =
000000001;
DML – INSERT, DELETE, UPDATE
desc employee_data1;
Insert into employee_data1 values('110012','Sana','Khan','Clerk',20000);
Insert into employee_data1 values('110013','jeba','Khan','Manager',90000);
Insert into employee_data1 values('110014','Jiya','Bansal','Accountant',50000);
DQL- Select
select * from employee_data1; ---TO DISPLAY DATA
select emp_name from employee_data1; ---TO DISPLAY PARTICULAR COLUMN
Delete duplicate row from Table
select distinct * from employee_data1;
