Google Search

Wednesday, October 12, 2011

Creation, altering and dropping of tables and inserting rows into a table (use constraints while creating tables) examples using SELECT command.DBMS LAB FOR M.E/M.TECH STUDENTS


Creation, altering and dropping of tables and inserting rows into a table (use constraints while creating tables) examples using SELECT command.
1. CREATE:
(a)create table: This is used to create a new relation
Syntax: create table relation_name
(field_1 data_type(Size),field_2 data_type(Size), .. . );
Example:
  SQL>create table Student (sno NUMBER(3) PRIMARY KEY ,sname
char(10),class char(5));
2. ALTER:
(a)ALTER TABLE ...ADD...: This is used to add some extra fields into existing relation.
Syntax: ALTER TABLE relation_name ADD(new field_1 data_type(size), new field_2 data_type(size),..);
Example :  SQL>ALTER TABLE std ADD(Address CHAR(10));
(b)ALTER table...modify...: This is used to change the width as well as data type of fields of existing relations.
Syntax: alter table relation_name modify (field_1 newdata_type(Size), field_2 newdata_type(Size),....field_newdata_type(Size));
Example: SQL>alter table student modify(sname varchar(10),class
varchar(5));
3. drop table: This is used to delete the structure of a relation. It permanently deletes the records in the table.
Syntax:                        drop table relation_name;
Example: SQL>drop table std;
4. INSERT:
Syntax:  insert into relation_name field_1,field_2,.....field_n) values   
               (&data_1,&data_2,........&data_n);
Example:  SQL>insert into student(sno,sname,class,address)   
      VALUES(&sno,’&sname’,’&class’,’&address’);
Enter value for sno: 101
Enter value for name: SIRISHA
Enter value for class: CSE
Enter value for address: Palakol

5. select from: To display all fields for all records.
Syntax :           select * from relation_name;
Example :        SQL> select * from student;
SNO  SNAME      CLASS      ADDRESS
     ---- --------   ------     -------
     101   SIRISHA   CSE        PALAKOL
     102   DEVAKI     CSE       NARSAPUR  
     103   KUMAR      CAD       BHIMAVARAM
     104   RAVI       VLSI       PALAKOL
2. Select from:  To display a set of fields for all records of relation.
Syntax:                        select a set of fields FROM relation_name;
Example:         SQL> select sno, sname from student;
SNO  SNAME     
     ---- --------  
     101   SIRISHA  
     102   DEVAKI    
     103   KUMAR     
     104   RAVI      
3. select - from -WHERE: This query is used to display a selected set of fields for a selected set of records of a relation.
Syntax:            select a set of fields from relation_name where  condition;
Example: SQL> select * FROM student WHERE class=’CSE’;
SNO  SNAME      CLASS      ADDRESS
     ---- --------   ------     -------
     101   SIRISHA   CSE        PALAKOL
     102   DEVAKI     CSE       NARSAPUR   

0 comments:

Post a Comment