Scribd
Upload
262 views2 pages

Student Table Schema and Constraints

The document contains DDL statements to create and modify tables. It creates tables for students, books, members, and transactions. It adds constraints like primary keys, foreign keys, checks, defaults and modifies column properties.

Uploaded by

vishwas
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
262 views2 pages

Student Table Schema and Constraints

The document contains DDL statements to create and modify tables. It creates tables for students, books, members, and transactions. It adds constraints like primary keys, foreign keys, checks, defaults and modifies column properties.

Uploaded by

vishwas
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 2

DDL Assignments

1. create table student (SID number(5) primary key, SNAME varchar2(30),


ADDRESS varchar2(50), AGE number(2), ROLLNO varchar2(10))

2. 1. alter table student modify SNAME varchar2(30) not null;

2. alter table student add constraint age_con check (age>3);

3. alter table student add unique(rollno);

4. alter table student modify age default 4;

5. alter table student add contactno number(10);

6. alter table student drop constraint age_con;

7. alter table student drop column contactno;

3. create table book (bookid number(3), bookname varchar2(30), author


varchar2(20), edition number(3), price number(5));

Adding constraints:

i) alter table book add primary key(bookid);

ii) alter table book modify bookname varchar2(30) not null;

iii) alter table book modify edition default 2;

iv) alter table book add publication varchar2(20);

v) alter table book rename column price to bookprice;

4. create table member (mid number(3), mname varchar2(20), mailid varchar2(40),


address varchar2(50));

Adding constraints:
i) alter table member add primary key(mid);

ii) alter table member modify mname varchar2(20) not null;

iii) alter table member add membertype varchar2(7) check (membertype='student'


or membertype='faculty');

5. create table transaction ( bid number(3), memid number(3), issuedate date,


returndate date);

Adding constraints:

i) alter table transaction modify issuedate default sysdate;

ii) alter table transaction add foreign key(bid) references book(bookid);

iii) alter table transaction add foreign key(memid) references member(mid);

iv) alter table transaction add primary key(memid, bid);

You might also like