DDL COMMANDS (Create, Drop & Alter – Add, modify, drop, rename)

Command: CREATE

1. To create a new table

2. To create a new table from existing table – All columns of existing tables are required
3. To create a new table from existing table – only few columns of existing tables are required

➢ TO create new table

Syntax:
CREATE TABLE table_name
(Column1 datatype (size),
Column2 datatype (size),
Column3 datatype (size));

E.g. Create table named student

create table student
(sid integer,
name varchar (20),
age char (10),
DOB date);

➢ TO see created table

select * from student;

➢ To create a new table from existing table – All columns of existing tables are required

Syntax:

CRETE TABLE new table name

AS SELECT * FROM existing table name;

E.g. Create table named student1 similar to existing table student

create table student1

as select * from student;

➢ To create a new table from existing table – only few columns of existing tables are
required

Syntax:

CRETE TABLE new table name (column1, column2)

AS SELECT column1, column2 FROM existing table name;

E.g. Create table named student2 having 2 columns (Aadhar & name) similar to column
sid & name of existing table student

create table student2 (aadahr,name)

as select sid,name from student;
 Command: DROP

Syntax:
DROP TABLE tablename;

E.g. to delete/drop table named student2

drop table student2;

Command: ALTER (ADD, MODIFY, RENAME, DROP column)

1. To add column in existing table

2. To modify size of datatype of the column of existing table
3. To modify datatype of the column of existing table
4. To remove/delete column from existing table
5. To rename column of existing table
6. To rename existing tables
7. To remove/delete all records of existing table

➢ TO add column in existing table

Syntax:
ALTER TABLE existing table_name
ADD COLUMN new column name datatype (size);
E.g. to add new column named aadhar of datatype number in existing table named
student
alter table student
add column aadhar numeric (10);
➢ To modify size of datatype of the column of existing table

Syntax:
ALTER TABLE existing table_name
ALTER COLUMN column name TYPE datatype (new size)
E.g. to modify size of datatype from 10 to 15 of column named aadhar in existing table
named student
alter table student
alter column aadhar type numeric (15);

➢ To modify datatype of the column of existing table

E.g. to modify datatype of column named aadhar in existing table named student from
numeric to varchar (10) (to convert numeric to text)
Syntax:
ALTER TABLE existing table_name
ALTER COLUMN column name TYPE new datatype (size) # (to convert numeric to text)

alter table student

alter column age type varchar (10);
E.g. to modify datatype of column named age in existing table named student from
varchar (10) to numeric (15) (only to convert text to numeric)
Syntax:
ALTER TABLE existing table_name
ALTER COLUMN column name TYPE new datatype (size)
Using column name :: new datatype (size); # (only to convert text to numeric)
alter table student
alter column aadhar type numeric (15)
using aadhar :: numeric(15);

➢ TO remove/drop Column in existing table

Syntax:
ALTER TABLE existing table_name
DROP COLUMN column name;
E.g. to drop/delete column named aadhar in existing table
alter table student
drop column aadhar;
➢ TO rename Column in existing table

Syntax:
ALTER TABLE existing table_name
RENAME COLUMN column name TO new column name;
E.g. to rename column named name to sname in existing table
alter table student
rename column name to sname;

➢ TO rename Table in existing table

Syntax:
ALTER TABLE existing table_name
RENAME TO new table name;
E.g. to rename table name student to std in existing table
alter table student
rename to std;
Command: TRUNCATE

Syntax:
TRUNCATE TABLE tablename;

E.g. to delete/drop all data/records from table named std

truncate table std;
 DML COMMANDS (Insert, Update & Delete)
Command: INSERT

E.g. to insert data/records in table named student1 (Particular column)

Syntax:
INSERT INTO table name (column1, column2, column3,….columnN)
VALUES (value1, value 2, value 3,…. valueN);

insert into student (sid,sname)

values (1,'abc');

E.g. to insert data/records in table named student1 (all columns)

Syntax:
INSERT INTO table name
VALUES (value1, value 2, value 3,…. valueN);

insert into student

values (2,'xyz','18','2005-05-01');
E.g. to insert data/records in table named student1 (multiple data entry)
INSERT INTO table name
VALUES (value1, value 2, value 3,…. valueN),
(value1, value 2, value 3,…. valueN);

insert into student

values (3,'pqr','17','2006-07-15'),
(4,'yrt','19','2005-11-09');

Command: UPDATE

E.g. Update sid-4 to sid-5

Syntax

Update table name

Set col-name = new value where condition;

update student
set sid=5 where sid=4;
E.g. Update age = 17 and dob = 2006-12-05

Syntax

Update table name

Set col-name1 = new value1, col-name2 = new value2 where condition;

update student
set age='17',dob='2006-12-05' where sid=1;

Command: Delete

E.g. delete row having sid=5

Syntax

Delete from table name where condition;

delete from student where sid=5;

QB – 95 Write queries for the following tables:
T1 ( Empno, Ename , Salary, Designation) ,T2 (Empno, Deptno.)
(1) Add a new column Deptname in table T2.
(2) Change the designation of Geeta from ‘Manager’ to ‘Senior Manager’.

(1) Add a new column Deptname in table T2.

alter table T2
add column Deptname varchar (10);

(2) Change the designation of Geeta from ‘Manager’ to ‘Senior Manager’.

Update T1
Set Designation = ‘Senior Manager’ where Empno = 1;

QB – 97 Write query for the following: Student_info(college_id,college_name,branch)

(1) add a new column for CGPA.
(2) change the name of the table from student_info to student_details
(3) delete the Student_details Table.

(1) add a new column for CGPA.

Alter table Student_info

Add column CGPA integer;

(2) change the name of the table from student_info to student_details

alter table student_info

rename to student_details;

(3) delete the Student_details Table

drop table student_details;

QB – 98 We have following relations:
Supplier(S#,sname,status,city)
Parts(P#,pname,color,weight,city)
SP(S#,P#,quantity)
Answer the following queries in SQL.
(1) Delete records in supplier table whose status is 40.
(2) Add one field in supplier table.

(1) Delete records in supplier table whose status is 40.

Delete from supplier where status = 40;

(2) Add one field in supplier table.

(as field name is not given we can choose any field,let us add mobileno)
Alter table Supplier add mobileno integer;

QB – 99 Write a Query to create the following table named

Member (ID char(6), Name varchar(30), Fee int(10), DOJ Date)
Perform the following tasks:
(1) After Creating table add one new column named Email_id of Varchar type with
size=12
(2) Modify datatype and size of column named “ID” from char (6) to Number
(3) Rename Fee column to new name “Fees”
(4) (5) Insert minimum 5 records to the given Table
(5) Update at least two records
(6) Delete the record having ID=101
(7) Rename the Table from Member to Member_details

(1) After Creating table add one new column named Email_id of Varchar type with
size=12

Create table
Create table member
(ID char (6),
Name varchar (30),
Fee integer,
DOJ date);

Alter table member

Add column email_id varchar(12);
 (2) Modify datatype and size of column named “ID” from char (6) to Number

Alter table member

Alter column ID type numeric
Using id :: numeric;

(3) Rename Fee column to new name “Fees”

Alter table member

Rename column fee to fees;

(4) Insert minimum 5 records to the given Table

Insert into member

Values (1, ‘abc’,1000, ‘2021-12-05’),
(2, ‘xyz’,900, ‘2020-01-12’),
(3, ‘pqr’,1100, ‘2023-02-24’),
(4, ‘srt’,500, ‘2018-09-15’),
(5, ‘rde’,1200, ‘2023-05-01’);

(5) Update at least two records

Update member
Set doj = ‘2020-05-12’ where id = 2;

Update member
Set name = ‘yrt where id = 4;

(6) Delete the record having ID=101

Delete from member where id=101;

(7) Rename the Table from Member to Member_details

Alter table member

Rename to member_details;