Practical 1
Write SQL queries for DDL commands: create, Desc, Alter, Rename, Drop.
i. Create the table STUDENT with the following specifications.
Table Name: STUDENT
Description:
Column Name Data Type Size Default Constraint
ID Char 3 Primary Key
Name Varchar 10
DOB Date
Gender Char 1
Contact_No Number 10
Course Varchar 10
Address1 Varchar2 30
Address2 Varchar2 30
City Varchar2 15
State Varchar2 15
sql>create table STUDENT(ID char(3) PRIMARY KEY,Name varchar(10),DOB
date,Gender char(1),Contact_No Number(10),Course varchar(10),Address1
varchar2(30), Address2 varchar2(30),City varchar2(15),State varchar2(15));
Output:
Table created.
ii. Show the table structure of table STUDENT
sql>DESC Student;
Output:
iii. Alter the Student table to allow the NAME field to hold maximum of 30 characters.
SQL> Alter table Student
2 Modify( NAME varchar2(30));
Output : Table altered.
To see the changes, we describe the Student table
SQL> Desc Student;
Output:
iv. Change the name of Student table to Students.
SQL> Rename student to Students;
Output:
Table renamed.
Note:
To see the changes, try to access student table using desc Student, which no longer
works.
SQL> desc student;
ERROR:
ORA-04043: object student does not exist
v. Remove the table Students along with the data held.
Sql> DROP TABLE Students;
Output:
Table dropped.
vi. Enter a new field called Pin_Code of type Number(6) for Students table.
Sql>ALTER TABLE Students ADD(Pin_Code Number(6));
Output:
Table altered.
To see the changes, we describe the table Students.
vii. Drop the column Pin_Code from the Students table.
SQL> alter table Students drop column Pin_Code;
Table altered.
To see the changes, we describe the table.
Sql>desc Students;
Practical 2-Write SQL queries by using DML commands: Insert, Select, Update,
Delete.
i. Insert the values into the Students table.
SQL> insert into students
2 values('001','Amit','10-jan-04','M',9934210911,'BCA','HNo-123','Sector
3','Faridabad','Haryana');
1 row created.
SQL> values('002','Ankita','1-jan-04','F',9877710911,'BCA','P-
7','Shahdra','Delhi','Delhi');
SP2-0734: unknown command beginning "values('00..." - rest of line ignored.
SQL> insert into students
2 values('002','Ankita','1-jan-04','F',9877710911,'BCA','P-
7','Shahdra','Delhi','Delhi');
1 row created.
SQL> insert into students
2 values('003','Raj','13-jun-
04','M',9999023108,'BBA','A/5','Rohini','Delhi','Delhi');
1 row created.
SQL> insert into students
2 values('004','Ragini','23-mar-04','F',7852100308,'BA','B/45','Dabua
Colony','Faridabad','Haryana');
1 row created.
SQL> insert into students
2 values('005','Omkar','20-Nov-04','M',7809621308,'BA','G/450','Gandhi
Colony','Faridabad','Haryana');
1 row created.
SQL> insert into students
2 values('006','Ashwini','22-Dec-05','M',7011983438,'MSc','650','Sector-
8','Faridabad','Haryana');
1 row created.
ii. List all the student details from the Students table.
iii. Retrieve the list of ID and names of students who are enrolled in BA course.
SQL> select ID,Name
2 from students
3 where COURSE='BA';
Output:
ID NAME
--- ------------------------------
004 Ragini
005 Omkar
iv. Change the date of birth of student whose name is ‘Ragini’ from ’23-Mar-04’
to ’23-May-04’.
SQL> Update Students
2 SET DOB='23-May-04'
3 where Name='Ragini';
1 row updated.
SQL> select *
2 from students
3 where name='Ragini';
ID NAME DOB G CONTACT_NO COURSE
--- ------------------------------ --------- - ---------- ----------
ADDRESS1 ADDRESS2 CITY
------------------------------ ------------------------------ ---------------
STATE
---------------
004 Ragini 23-MAY-04 F 7852100308 BA
B/45 Dabua Colony Faridabad
Haryana
v. Delete all students from the Students table who are living in Delhi.