SQL DDL Statements(Looking at different Scenarios)

/*Scenario 1*/

/* creating master table dept */

create table dept

(deptno int primary key,

 dname varchar(10),

 loc varchar(10))

/* inserting records in dept */

insert into dept values (10,'sales','khi')

insert into dept (deptno,dname,loc) values (20,'accounts','lhr')

insert into dept (dname,deptno,loc) values ('production',30,'isl')

/* creating child table emp */

create table emp

(empno int primary key,

 ename varchar(10),

 sal float,

 deptno int references dept(deptno))

insert into emp values (1,'faisal',1000,10)

insert into emp values (2,'amir',1200,10)

insert into emp values (3,'javed',1300,10)

insert into emp values (4,'khan',1200,20)

insert into emp values (5,'nasir',1250,20)

insert into emp values (6,'rehan',1100,20)

insert into emp values (7,'jamal',1300,30)

insert into emp values (8,'kamran',1400,30)

insert into emp values (9,'bilal',1450,30)

/* checking dept and emp records */

select * from dept

select * from emp

/* Note for scenario 2 drop both tables first 'emp' and then dept' */

drop table emp

drop table dept

/* Scenario 2 */

/* emp */

create table emp

(empno int primary key,

 ename varchar(10),

 sal float,

 deptno int)

/* on above statement no foreign key reference could be give since

   'dept' table is not there */

/* dept */

create table dept

(deptno int primary key,

 dname varchar(10),

 loc varchar(10))

/* Now applying foreign key constraints on emp(deptno) field */

alter table emp

add constraint emp_fk foreign key(deptno) references dept(deptno)

/* for scenario 3 drop both emp and dept tables */

drop table emp

drop table dept

/* scenario 3 */

/* emp */

create table emp

(empno int,

 ename varchar(10),

 sal float,

 deptno int)

/* dept */

create table dept

(deptno int,

 dname varchar(10),

 loc varchar(10))

/* applying primary key on emp(empno) */

alter table emp

alter column empno int not null

alter table emp

add constraint emp_pk primary key(empno)

/* applying primary key on dept(deptno) */

alter table dept

alter column deptno int not null

alter table dept

add constraint dept_pk primary key(deptno)

/* Now applying foreign key constraints on emp(deptno) field */

alter table emp

add constraint emp_fk foreign key(deptno) references dept(deptno)

/* getting 'emp' and 'dept' structure information */

sp_help emp

sp_help dept

/* numeric data type */

create table person

(pid numeric(2) primary key,

 pname varchar(10),

 sal numeric(4,2))

insert into person values (21,'faisal',20.55)

insert into person values (201,'javed',20.55) /* error: 201 */

insert into person values (22,'javed',500.55) /* error: since (4,2) means

total 4 digits out of which 2 canbe

decimal precision */

insert into person values (22,'javed',500.5) /* error */

insert into person values (22,'javed',5.555) /* no error

since rounding of is being done */

insert into person values (23,'amir',5.554)  /* no error but rounding */

select * from person

/* changing data types of a column if no data is present */

create table person1

(pid numeric(2) primary key,

 pname varchar(10),

 sal numeric(4,2))

alter table person1

alter column pid numeric(4) /* error: as it is primary key column */

alter table person1

alter column sal numeric(6,2) /* no error */

sp_help person1

alter table person1

alter column sal float /* no error */

sp_help person1

alter table person1

alter column pname float /* no error */

sp_help person1

/* changing data types of a column with data */

/* person table is required with some records */

select * from person

alter table person

alter column pname float  /* error */

select * from person

alter table person alter column sal numeric(8,2) /* no error */

select * from person

insert into person values (24,'kamran',2000.56)

select * from person

alter table person alter column sal numeric(5,2) /* error: as truncation is not

possible */

select * from person

alter table person alter column sal numeric(6,2) /* no error */

select * from person

sp_help person

select * from person

alter table person alter column sal numeric(6) /* no error: data rounding is done */

select * from person

alter table person alter column sal float

insert into person values (25,'haris',4.56)

select * from person