SQL QUERY FOR PRIMARY AND FOREIGN KEY


Practical-3

1.Create table Account_key


Column name
Data Type
Size
Attributes
Acc_no
Varchar2
5
Primary key / first letter must start with ‘A’
Name
Varchar2
30
NOT NULL
City
Varchar2
20
NOT NULL
Balance
Number
10,2
Balance >=500
Loan_taken
Varchar2
3
Values(‘NO’,’YES’)

ANS . create table account_key(acc_no varchar2(5) primary key check(acc_no like 'A%'),name varchar2(30) not null,city varchar2(20) not null,balance number(10,2) check(balance>=500),loan_taken varchar2(3) check(loan_taken='yes' or loan_taken='no'));


acc_no
Name
City
Balance
loan_taken
A001
Patel Jigar
Mehsana
50000
YES
A002
Patel Ramesh
Mehsana
50000
YES
A003
Dave Hardik
Ahmedabad
75000
NO
A004
SoniHetal
Ahmedabad
100000
NO
A005
Sony Atul
Vadodara
100000
YES
A005
Patel Arun
Surat
4000
No
A006
NULL
Baroda
5000
NO
A007
Patel Rachit
NULL
6000
NO
A008
Patel Vir
Mehsana
400
NO
A009
Patel Vyom
Surat
1000
ABC

ANS: 
insert  intoaccount_key values('A001','Patel Jigar','Mehsana',50000,'yes');
insert  intoaccount_key values('A002','Patel Ramesh','Mehsana',50000,'yes');
insert  intoaccount_key values('A003','Dave Hardik','Ahmedabad',75000,'no');
insert  intoaccount_key values('A004','Soni Hetal','Ahmedabad',100000,'no');
insert  intoaccount_key values('A005','Sony Atul','Vadodara',100000,'yes');
insert  intoaccount_key values('A005',NULL,'Baroda',5000,'no');
insert  intoaccount_key values('A007','Patel Rachit',NULL,6000,'no');
insert  intoaccount_key values('A008','Patel Vir','Mehsana',400,'no');
insert  intoaccount_key values('A009','Patel Vyom','Surat',1000,'abc');
select * from Account_key;


2.Create Table LOAN_key


Column Name
Data Type
Size
Attributes
Loan_no
Varchar2
5
Primary Key / first letter must start with ‘L’
Acc_no
Varchar2
5
Foreign key References Acc_no of account table
Loan_amt
Number
10,2
NOT NULL
Interest_rate
Number
5,2
NOT NULL
Loan_date
Date


Remaining_loan
Number
10,2
Remaining loan<loan amount

ANS : create table loan_key(loan_no varchar2(5) primary key ,acc_no varchar2(5) references account_key(acc_no),loan_amt number(10,2) not null,intrest_rate number(5,2) not null,loan_datedate,remaining_loan number(10,2), check(loan_no like 'L%'), check(remaining_loan<loan_amt));

Loan_no
Acc_no
Loan_amt
Interest_rate
Loan_date
Remaining_loan
L001
A001
100000
7
1-jan-04
75000
L002
A002
300000
9
18-may-04
150000
L003
A005
500000
11
15-june-04
300000


ANS : insert into loan_key values('L001','A001',100000,7,'1-jan-04',75000);
insert into loan_key values('L002','A002',300000,9,'18-may-04',150000);
insert into loan_key values('L003','A005',500000,11,'15-june-04',300000);
Select * from LOAN_key;

3.Create Table INSTALLMENT_key

Column Name
Data Type
Size
Attributes
Loan_no
Varchar2
5
Foreign key References Loan_no of Loan table
Inst_no
Varchar2
5
first letter must start with ‘I’
IDate
Date

NOT NULL
Amount
Number
10,2
NOT NULL

ANS : create table installment_key(loan_no varchar2(5) references loan_key(loan_no),inst_no varchar2(5) check(inst_no like 'I%'),idate date not null,amount number(10,2) not null);


Loan_no
Inst_no
Date
Amount
L001
I001
2-Feb-04
15000
L002
I002
18-June-04
20000
L003
I003
15-July-04
20000


ANS : insert into installment_key values('L001','I001','2-Feb-04',15000);
insert into installment_key values('L002','I002','18-June-04',20000);
insert into installment_key values('L003','I003','15-July-04',20000);
Select * from INSTALLMENT_key;


CONSTRAINTS Based queries.

3. Create  Table:STUDENT

Name of column
Type and Size
Rollno
Varchar2(6)
Name
Varchar2(20)
Branch
Varchar2(6)
Address
Varchar2(20)

ANS : create table student(rollno varchar2(6),name varchar2(20),branch varchar2(6),address varchar2(20));

1. Add PRIMARY KEY (roll no) and provide constraint name PRIM_rollno.
Ans:alter table student add constraint prim_rollno primary key(rollno);

2      2. Add NOT NULL constraint to name,branch for student table.
Ans: alter table student modify(name varchar2(20) constraint nn not null,branch varchar2(6) constraint nn1 not null);

3      3.Add check constraint and check name is in capital letter.
Ans: alter table student add constraint ckcheck(name =upper(name));

4      4.Drop the primary key.
Ans: alter table student drop constraint prim_rollno;

5       5.Drop the constraint.
      Ans: alter table student drop constraint nn drop constraint nn1;


Create Table REGISTER.
  
Name of column
Type and Size
Rollno
Varchar2(6)
Name
Varchar2(20)

ANS : create table register(rollno varchar2(6),name varchar2(20));

1.Provide foreign key references rollno of student table
Ans:alter table register add constraint fk foreign key (rollno) references student(rollno) ;
2     2. Add check constraint to check name’s first letter is always capital.
Ans: alter table register add constraint ck1 check(name like 'N%');

3        3. Add NOT NULL constraint to name of register table.
Ans:alter table register modify (name varchar2(20) constraint nn2 not null);

4        4. Drop foreign key of REGISTER table.
Ans: alter table register drop constraint fk;
                                                                                                                                                     
5        5.Drop NOT NULL constraint.
Ans:alter table register drop constraint nn2;


Comments

Post a Comment

Popular posts from this blog

SQL QUERY

                                Prac tical-2 1. Retrieve specified information for the account holder who are not in ‘Ahmedabad’. Ans: select * from account where city not in ‘Ahemdabad’; 2. Retrieve specified information for the account holder who are not in ‘Ahmedabad or ‘Vadodara’. Ans: select * from account where city not in('Ahemadabad', 'Vadodara'); 3. Retrieve those records of Account holder whose balance between is 50000 and 100000. Ans: select * from account where balance between 50000 and   1000000; .4. Retrieve those records of Account holder whose balance not between is 50000 and 100000. Ans: select * from account where balance not between 50000 and 100000; 5. Display only those records whose amount is 5000, 25000, 30000. Ans: select * from Transaction where amount in (5000, 25000, 30000); 6. Display only those records whose amount not in 5000, 25000, 30000. Ans:   select * from Transaction where Amt not in (5
Read more

SQL QUERY practical-5

                                     PRACTICAL-5 CREATE TABLE SALESMEN(SNUM VARCHAR2(6) PRIMARY KEY CHECK(SNUM LIKE 'S%'), SNAME VARCHAR2(20) NOT NULL, CITY VARCHAR2(15), COMM NUMBER(5,2)); INSERT INTO SALESMEN VALUES('S1001','PIYUSH','LONDON',0.12); INSERT INTO SALESMEN VALUES('S1002','NIRAJ','SAN JOSE',0.13); INSERT INTO SALESMEN VALUES('S1003','MITI','LONDON',0.11); INSERT INTO SALESMEN VALUES('S1004','RAJESH','BARCELONA',0.15); INSERT INTO SALESMEN VALUES('S1005','HARESH','NEW YORK',0.10); INSERT INTO SALESMEN VALUES('S1006','RAM','BOMBAY',0.10); INSERT INTO SALESMEN VALUES('S1007','NEHAL','DELHI',0.09); CREATE TABLE CUSTOMER(CNUM VARCHAR2(6) PRIMARY KEY CHECK(CNUM LIKE 'C%'), CNAME VARCHAR2(20) NOT NULL, CITY VARCHAR2(15), RATING NUMBER(5), SNUM VARCHAR2(6)); INSERT INTO CUSTOMER VALUES('
Read more

Practicals of RollBack, Commit and Cursor programs

Image
                                                                 Practical-10 AIM :- RollBack   , Commit and Cursor bases Programs. 1. Analyze output of following scripts: A.      Begin                               Insert into new_salary values ('6', '6000');                               Update new_salary set salary=4000 where id=4;                               Commit;                               Rollback;                 End;               / Ans:-                B.   Begin                                 Insert into new_salary values (8, 8000);                Update new_salary set salary=50000 where id=5;                Savepoint   first;                    Rollback to savepoint first;        End;        /               Ans:- 2. Write pl/sql blocks that first insert the new record into table salary. Update the salary of employee name ‘Riya’ and ‘Rima’ by 7000/- then check the total salary   which does
Read more

Programs Based on Exception Handling and Trigger.

                                                       PRACTICAL-11 Aim :-   Programs Based on Exception Handling and Trigger. 1. Table: STUDENT (sr_no, name, address). Write a pl/sql code to retrieve the data from                student table whose sr.no is 02ce33. if     data not found then show the message “data not                found “  (Use EXCEPTION in pl/sql code). Ans :- declare no int; nm varchar2(20); begin no:=&no; select NAME into nm from student where ROLLNO=no; dbms_output.put_line('Name of student is '||nm); exception when NO_DATA_FOUND then dbms_output.put_line('No data found!!!'); end; / 2. Give Example of “TOO_MANY_ROWS” Exception.     Give Example of Unnamed system Exception.(Give Example of Primary key constraint   violation) Ans:- declare nm varchar2(20); begin select name into nm from student; dbms_output.put_line('Name of student is '||nm);   exception when TOO_MANY_R
Read more