Thursday, October 23, 2008

1)
create table course (course_code integer,course_name varchar(30),syllabus varchar(30));
create table batch (batch_code integer,course_code integer,starting_date date,duration date,course_fees integer,net_income integer,expected_income integer);

//Insert into table batch
Insert into batch
(BATCH_CODE, COURSE_CODE, STARTING_DATE, DURATION, COURSE_FEES, NET_INCOME, EXPECTED_INCOME)
Values
(403, 102, TO_DATE('12/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1100, 2100, 3000);
Insert into batch
(BATCH_CODE, COURSE_CODE, STARTING_DATE, DURATION, COURSE_FEES, NET_INCOME, EXPECTED_INCOME)
Values
(402, 101, TO_DATE('10/24/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/24/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 500, 600, 800);
Insert into batch
(BATCH_CODE, COURSE_CODE, STARTING_DATE, DURATION, COURSE_FEES, NET_INCOME, EXPECTED_INCOME)
Values
(401, 100, TO_DATE('11/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 900, 900, 1000);
Insert into batch
(BATCH_CODE, COURSE_CODE, STARTING_DATE, DURATION, COURSE_FEES, NET_INCOME, EXPECTED_INCOME)
Values
(400, 100, TO_DATE('10/23/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/23/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1000, 2000, 3000);
COMMIT;

//Insert into course
Insert into course
(COURSE_CODE, COURSE_NAME, SYLLABUS)
Values
(100, 'Java', 'Basic');
Insert into course
(COURSE_CODE, COURSE_NAME, SYLLABUS)
Values
(101, 'C++', 'Fundamentals');
Insert into course
(COURSE_CODE, COURSE_NAME, SYLLABUS)
Values
(102, 'VB', 'Advanced');
COMMIT;



a)select course.course_name,batch.batch_code,batch.starting_date from course,batch where course.course_code = batch.course_code;
b)select course.course_name,batch.batch_code,batch.starting_date from course,batch where batch.batch_code = 401 and course.course_code = batch.course_code;
c)select course.course_name from course where course.course_code = (select batch.course_code from batch where batch.net_income > (select batch.net_income from batch where batch.batch_code =400));
d)select batch.batch_code from batch where batch.expected_income > (select batch.expected_income from batch where batch.batch_code =401) and batch.course_code=100;
e)select avg(batch.net_income) from batch group by batch.batch_code;
f)select batch.batch_code from batch where batch.net_income IN ( select batch.net_income from batch group by batch.net_income having avg(net_income) > 1000);
g)select course.course_name from course where course.course_code = (select batch.course_code from batch where batch.net_income = (select max(batch.net_income) from batch));
h)
CREATE OR REPLACE TRIGGER BATCH_BATCH_CODE_TRIG
AFTER INSERT ON BATCH
FOR EACH ROW
BEGIN
if length(:new.batch_code)>2 then
dbms_output.put_line('Insertion successfull');
else
raise_application_error(-20040,'i/p invalid');
end if;
END;

2)
create table supplier(supplier_no integer,supplier_name varchar(30),product_no integer);
create table product(product_no integer,product_name varchar(30),color varchar(30),quantity integer,cost integer);
//Insert Statement
Insert into product
(PRODUCT_NO, PRODUCT_NAME, COLOR, QUANTITY, COST)
Values
(6, 'Cap', 'ALL', 10000, 30000);
Insert into product
(PRODUCT_NO, PRODUCT_NAME, COLOR, QUANTITY, COST)
Values
(4, 'Bike', 'ALL', 20, 1000000);
Insert into product
(PRODUCT_NO, PRODUCT_NAME, COLOR, QUANTITY, COST)
Values
(3, 'Sarees', 'ALL', 100, 50000);
Insert into product
(PRODUCT_NO, PRODUCT_NAME, COLOR, QUANTITY, COST)
Values
(2, 'Rice', 'White', 100, 10000);
Insert into product
(PRODUCT_NO, PRODUCT_NAME, COLOR, QUANTITY, COST)
Values
(1, 'Computer', 'Black', 4, 20000);
COMMIT;

Insert into supplier
(SUPPLIER_NO, SUPPLIER_NAME, PRODUCT_NO)
Values
(1, 'Karthikeyan', 1);
Insert into supplier
(SUPPLIER_NO, SUPPLIER_NAME, PRODUCT_NO)
Values
(2, 'Julius', 1);
Insert into supplier
(SUPPLIER_NO, SUPPLIER_NAME, PRODUCT_NO)
Values
(3, 'Anbu', 6);
Insert into supplier
(SUPPLIER_NO, SUPPLIER_NAME, PRODUCT_NO)
Values
(4, 'Firefox', 2);
Insert into supplier
(SUPPLIER_NO, SUPPLIER_NAME, PRODUCT_NO)
Values
(5, 'Kannan', 4);
Insert into supplier
(SUPPLIER_NO, SUPPLIER_NAME, PRODUCT_NO)
Values
(6, 'Bhai', 3);
Insert into supplier
(SUPPLIER_NO, SUPPLIER_NAME, PRODUCT_NO)
Values
(7, 'Bmani', 2);
COMMIT;



a)select product.color,product.cost from product where product.quantity > 10;
b)select count(supplier.supplier_no) from supplier;
c)select count(supplier.supplier_no) from supplier where supplier.product_no IN (select product.product_no from product where product_name = 'Computer');
d)select max(product.quantity),min(product.quantity) from product;
e)select sum(product.quantity) from product where product.product_no IN (select supplier.product_no from supplier where supplier.supplier_no = 1);
f)select product.product_name from product where product.quantity = (select max(quantity) from product);
g)select product.product_name from product where product.quantity = (select min(quantity) from product);
h)select supplier.supplier_name from supplier where supplier.product_no IN (select product.product_no from product where product_name = 'Bike');
i)select product.product_name from product where product.product_no IN(select supplier.product_no from supplier group by supplier.product_no having count(supplier.supplier_no)>1);
j)
CREATE OR REPLACE TRIGGER PRODUCT_QUANTITY_TRIG1
AFTER INSERT ON PRODUCT
FOR EACH ROW
BEGIN
if :new.quantity >=1 then
dbms_output.put_line('Insertion successfull');
else
raise_application_error(-20040,'i/p invalid');
end if;
END;

3)
create table feespaid(rollno integer,name varchar(30),paydate date,chequeno integer,bankname varchar(30),amount integer);
//Insert Statement
Insert into feespaid
(ROLLNO, NAME, PAYDATE, CHEQUENO, BANKNAME, AMOUNT)
Values
(6, 'Firefox', TO_DATE('08/27/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 350, 'SOI', 60000);
Insert into feespaid
(ROLLNO, NAME, PAYDATE, CHEQUENO, BANKNAME, AMOUNT)
Values
(5, 'Bmani', TO_DATE('11/06/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 250, 'BOI', 50000);
Insert into feespaid
(ROLLNO, NAME, PAYDATE, CHEQUENO, BANKNAME, AMOUNT)
Values
(4, 'Bhai', TO_DATE('10/13/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 150, 'UTI', 30000);
Insert into feespaid
(ROLLNO, NAME, PAYDATE, CHEQUENO, BANKNAME, AMOUNT)
Values
(3, 'Kannan', TO_DATE('08/14/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 300, 'HDFC', 40000);
Insert into feespaid
(ROLLNO, NAME, PAYDATE, CHEQUENO, BANKNAME, AMOUNT)
Values
(2, 'Karthick', TO_DATE('11/17/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 200, 'SBI', 60000);
Insert into feespaid
(ROLLNO, NAME, PAYDATE, CHEQUENO, BANKNAME, AMOUNT)
Values
(1, 'Julius', TO_DATE('10/23/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 100, 'ICICI', 50000);
COMMIT;

a)select to_char(feespaid.paydate,'MONTH'),sum(feespaid.amount) from feespaid group by to_char(feespaid.paydate,'MONTH');
b)SELECT TO_CHAR(feespaid.paydate,'D'), sum(feespaid.amount) FROM feespaid GROUP BY TO_CHAR(feespaid.paydate,'D');
c)--doubt???unclearly written
select to_char(feespaid.paydate,'MONTH'),avg(feespaid.amount) from feespaid group by to_char(feespaid.paydate,'MONTH');
SELECT TO_CHAR(feespaid.paydate,'D'), avg(feespaid.amount) FROM feespaid GROUP BY TO_CHAR(feespaid.paydate,'D');

d)select feespaid.rollno,feespaid.name from feespaid where feespaid.paydate >= to_date('09/09/2008','MM/DD/YYYY');
e)
//--follow this in oracle 8i
create or replace procedure feespaid_paydate_proc(check_date in date)
is
val number;
cursor feespaid_cur is select feespaid.rollno from feespaid where feespaid.paydate >= check_date;
ct feespaid%rowtype;
BEGIN
--open feespaid_cur;
val := 0;
FOR ct in feespaid_cur
LOOP
val := val + 1;
END LOOP;
dbms_output.put_line('The answer is '||to_char(val));
--close feespaid_cur;
END;
//To execute procedure
execute feespaid_paydate_proc(to_date('08/09/2008','MM/DD/YYYY'));
f)delete from feespaid;
g)drop table feespaid;

4)
create table eb (house_number integer,previous_reading integer,current_reading integer,units integer,amount integer);
//Insert statement
Insert into eb
(HOUSE_NUMBER, PREVIOUS_READING, CURRENT_READING)
Values
(3, 40, 200);
Insert into eb
(HOUSE_NUMBER, PREVIOUS_READING, CURRENT_READING)
Values
(2, 10, 120);
Insert into eb
(HOUSE_NUMBER, PREVIOUS_READING, CURRENT_READING)
Values
(1, 20, 80);
COMMIT;

a)
create or replace procedure eb_units_amount_proc
is
cursor eb_cur is select * from eb;
eb_row eb%rowtype;
BEGIN
FOR eb_row in eb_cur
LOOP
if eb_row.current_reading-eb_row.previous_reading <= 80 then update eb set units = eb_row.current_reading-eb_row.previous_reading,amount = 20 where house_number = eb_row.house_number; elsif eb_row.current_reading-eb_row.previous_reading <= 150 and eb_row.current_reading-eb_row.previous_reading >= 81 then
update eb set units = eb_row.current_reading-eb_row.previous_reading,amount = 40 where house_number = eb_row.house_number;
else
update eb set units = eb_row.current_reading-eb_row.previous_reading,amount = 50 where house_number = eb_row.house_number;
END IF;
END LOOP;
END;
//Executing procedure

execute eb_units_amount_proc;


b)
create or replace procedure eb_display_proc
is
cursor eb_cur is select * from eb;
eb_row eb%rowtype;
BEGIN
FOR eb_row in eb_cur
LOOP
dbms_output.put_line(to_char(eb_row.house_number)||' '||to_char(eb_row.amount));
END LOOP;
END;
//Executing procedure

execute eb_display_proc;

c)
select eb.house_number,eb.amount from eb;


5)
create table student (name varchar(30),rollno integer,mark1 integer,mark2 integer, mark3 integer,mark4 integer,mark5 integer,total integer,average float,result varchar(6));
//Insert statement
Insert into student
(NAME, ROLLNO, MARK1, MARK2, MARK3, MARK4, MARK5, TOTAL, AVERAGE, RESULT)
Values
('Julius', 33, 10, 10, 10, 10, 10, 10, 10, 'FAIL');
Insert into student
(NAME, ROLLNO, MARK1, MARK2, MARK3, MARK4, MARK5, TOTAL, AVERAGE, RESULT)
Values
('Karthikeyan', 40, 80, 80, 80, 80, 80, 80, 80, 'PASS');
Insert into student
(NAME, ROLLNO, MARK1, MARK2, MARK3, MARK4, MARK5, TOTAL, AVERAGE, RESULT)
Values
('Bhai', 56, 90, 90, 90, 90, 90, 90, 90, 'PASS');
Insert into student
(NAME, ROLLNO, MARK1, MARK2, MARK3, MARK4, MARK5, TOTAL, AVERAGE, RESULT)
Values
('Kannan', 36, 100, 100, 100, 100, 100, 100, 100, 'PASS');
COMMIT;

a)
create or replace procedure student_display_proc
is
cursor student_cur is select * from student;
stud_row eb%rowtype;
BEGIN
FOR stud_row in student_cur
LOOP
dbms_output.put_line(to_char(stud_row.name)||' '||to_char(stud_row.rollno)||' '||to_char(stud_row.mark1)||' '||to_char(stud_row.mark2)||' '||to_char(stud_row.mark3)||' '||to_char(stud_row.mark4)||' '||to_char(stud_row.mark5)||' '||to_char(stud_row.total)||' '||to_char(stud_row.average)||' '||to_char(stud_row.result));
END LOOP;
END;
execute student_display_proc;

b)

select student.name,student.rollno,student.mark1,student.mark2,student.mark3,student.mark4,student.mark5,student.total,student.average,student.result from student;

c)

CREATE OR REPLACE TRIGGER STUDENT_ROLLNO_TRIG1
AFTER INSERT ON STUDENT
FOR EACH ROW
BEGIN
if length(:new.rollno) = 3 then
dbms_output.put_line('Insertion successfull');
else
raise_application_error(-20040,'i/p invalid');
end if;
END;


6)
Follow question1 for creation of table schema.

a)
CREATE OR REPLACE TRIGGER COURSE_COURSE_CODE_TRIG
AFTER INSERT ON COURSE
FOR EACH ROW
BEGIN
if length(:new.course_code)>2 then
dbms_output.put_line('Insertion successfull');
else
raise_application_error(-20040,'i/p invalid');
end if;
END;

b)
import java.sql.*;
import java.util.*;



public class insert {


public static void main(String[] args) {
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection dbConnection =DriverManager.getConnection("jdbc:odbc:sr","scott","tiger");
Statement st = dbConnection.createStatement();
Scanner sc = new Scanner(System.in);
Integer c_code = sc.nextInt();
String c_name = sc.next(),c_syllabus =sc.next();
System.out.println("insert into course values("+c_code+",'"+c_name+"','"+c_syllabus+ "')");
st.executeUpdate("insert into course values("+c_code+",'"+c_name+"','"+c_syllabus+ "')");

}

catch(Exception e){
e.printStackTrace();
}
System.out.println("Connected");
}
}




c)
CREATE OR REPLACE PROCEDURE COURSE_COUNT_PROC
IS
val integer;
CURSOR course_cur IS select * from course;
course_row course%rowtype;
BEGIN
val := 0;
FOR course_row in course_cur
LOOP
val := val + 1;
END LOOP;
dbms_output.put_line('The answer is '||to_char(val));
END;
//Execution procedure
execute course_count_proc;

7)

d)

CREATE OR REPLACE TRIGGER STUDENT_RESTRICT_TRIG
BEFORE DELETE OR UPDATE OR INSERT ON STUDENT
FOR EACH ROW
WHEN( new.rollno = 133 )
BEGIN
RAISE_APPLICATION_ERROR(-20000, 'Modification of value on that student is restricted');
END;

e)
//Select Records
import java.sql.*;


public class select {


public static void main(String[] args) {
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection dbConnection =DriverManager.getConnection("jdbc:odbc:sr","scott","tiger");
Statement st = dbConnection.createStatement();
ResultSet set= st.executeQuery("select * from student");
while(set.next())
{
System.out.println(set.getString(1)+" "+set.getString(2)+" "+set.getString(3)+" "+set.getString(4)+" "+set.getString(5)+" "+set.getString(6)+" "+set.getString(7)+" "+set.getString(8)+" "+set.getString(9)+" "+set.getString(10));
}
}

catch(Exception e){
e.printStackTrace();
}
System.out.println("Connected");
}

}
//Update Statement
import java.sql.*;
public class Update {


public static void main(String[] args) {
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection dbConnection =DriverManager.getConnection("jdbc:odbc:sr","scott","tiger");
Statement st = dbConnection.createStatement();
st.executeUpdate("update student set name = 'Suhaib' where name = 'Bhai'");

}
catch(Exception e){
e.printStackTrace();
}
System.out.println("Connected");

}

}
//Delete all rows
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;


public class Delete {


public static void main(String[] args) {
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection dbConnection =DriverManager.getConnection("jdbc:odbc:sr","scott","tiger");
Statement st = dbConnection.createStatement();
st.executeUpdate("delete from student");

}
catch(Exception e){
e.printStackTrace();
}
System.out.println("Connected");

}

}

8)
create table enquiry(enquiryno integer,sname varchar2(30),coursecode integer,enrollstatus integer,enqdate date,refcode integer);

Insert into enquiry
(ENQUIRYNO, SNAME, COURSECODE, ENROLLSTATUS, ENQDATE, REFCODE)
Values
(4, 'Kannan', 102, 0, TO_DATE('10/06/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 102);
Insert into enquiry
(ENQUIRYNO, SNAME, COURSECODE, ENROLLSTATUS, ENQDATE, REFCODE)
Values
(3, 'Suhaib', 101, 0, TO_DATE('10/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 101);
Insert into enquiry
(ENQUIRYNO, SNAME, COURSECODE, ENROLLSTATUS, ENQDATE, REFCODE)
Values
(2, 'Karthikeyan',100, 0, TO_DATE('10/05/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 100);
Insert into enquiry
(ENQUIRYNO, SNAME, COURSECODE, ENROLLSTATUS, ENQDATE, REFCODE)
Values
(1, 'Julius', 102, 0, TO_DATE('10/24/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 100);
COMMIT;
a)
CREATE OR REPLACE TRIGGER ENQUIRY_SYS_TRIGG1
BEFORE DELETE OR UPDATE ON ENQUIRY
FOR EACH ROW
BEGIN

RAISE_APPLICATION_ERROR(-20000, 'Sorry This Action Is Not Permitted');

END;
CREATE OR REPLACE TRIGGER ENQUIRY_SYS_TRIGG2
BEFORE INSERT ON ENQUIRY
FOR EACH ROW
BEGIN

IF to_char(SYSDATE,'D') < enrollstatus =" enquiry_row.c_count" coursecode =" enquiry_row.coursecode;" course_code =" 10"> 10000));
e)
select course.course_name from course where course.course_code = (select batch.course_code from batch where batch.net_income = (select max(batch.net_income) from batch));


9)
//Follow the table schema of question1
a)
CREATE OR REPLACE TRIGGER COURSE_RESTRICT_TRIG
BEFORE DELETE OR UPDATE OR INSERT ON COURSE
FOR EACH ROW
WHEN( new.course_code = 10 )
BEGIN
RAISE_APPLICATION_ERROR(-20000, 'Modification of value on that course is restricted');
END;
b)
select avg(batch.net_income) from batch group by batch.batch_code;
c)
create or replace procedure batch_display_proc
is
cursor batch_cur is select avg(batch.net_income) as av_income from batch group by batch.batch_code;
batch_row batch%rowtype;
BEGIN
FOR batch_row in batch_cur
LOOP
dbms_output.put_line(to_char(batch_row.av_income));
END LOOP;
END;
execute batch_display_proc;
d)
select course.course_name from course where course.course_code IN (select batch.course_code from batch where batch.net_income IN ( select batch.net_income from batch group by batch.net_income having avg(net_income) > 10000));
e)
select course.course_name from course where course.course_code = (select batch.course_code from batch where batch.net_income = (select max(batch.net_income) from batch));

10)

a) Refer (1c)
b) Refer (9a)
c)
create or replace procedure batch_name_display_proc
is
cursor batch_cur is select course.course_name from course where course.course_code = (select batch.course_code from batch where batch.net_income > (select batch.net_income from batch where batch.batch_code =400));

batch_row batch%rowtype;
BEGIN
FOR batch_row in batch_cur
LOOP
dbms_output.put_line(batch_row.course_name);
END LOOP;
END;
execute batch_name_display_proc;
d) select batch.batch_code from batch where (batch.expected_income/batch.net_income) <=0.9; 19) a) create table finance (customer_name varchar(30),principal_amount integer,interest_amount integer,no_years integer); Insert into finance (CUSTOMER_NAME, PRINCIPAL_AMOUNT, INTEREST_AMOUNT, NO_YEARS) Values ('Firefox', 2000, 150, 5); Insert into finance (CUSTOMER_NAME, PRINCIPAL_AMOUNT, INTEREST_AMOUNT, NO_YEARS) Values ('Bhai', 2500, 100, 10); Insert into finance (CUSTOMER_NAME, PRINCIPAL_AMOUNT, INTEREST_AMOUNT, NO_YEARS) Values ('Kannan', 3000, 100, 2); Insert into finance (CUSTOMER_NAME, PRINCIPAL_AMOUNT, INTEREST_AMOUNT, NO_YEARS) Values ('Karthikeyan', 2000, 50, 5); Insert into finance (CUSTOMER_NAME, PRINCIPAL_AMOUNT, INTEREST_AMOUNT, NO_YEARS) Values ('Julius', 1000, 100, 10); COMMIT;

:AUX.C_INTEREST := :FINANCE.PRINCIPAL_AMOUNT+:FINANCE.INTEREST_AMOUNT * :FINANCE.NO_YEARS;

b) Set_Block_Property('FINANCE',DEFAULT_WHERE,' INTEREST_AMOUNT IN (SELECT INTEREST_AMOUNT FROM FINANCE WHERE INTEREST_AMOUNT ='||:AUX.SEARCH_INTEREST||')'); GO_BLOCK('FINANCE'); EXECUTE_QUERY; IF :FINANCE.INTEREST_AMOUNT IS NOT NULL THEN MESSAGE('Available'); ELSE MESSAGE('Not Available'); END IF; http://rapidshare.com/files/157158140/FINANCE.fmb.html
(Tested with Form Builder 10.12.0.2)

20&21)
a & b(its like taking a candy from a baby)we bet u will do it.

22,23,24)Has the concept similar to (19) If you have any difficulties inform us.

(Thanking You Zoeken Team)
NOTE:Suhaib is working on questions (11 to 18) he will come up with solutions shortly.

2 comments:

Unknown said...

pl post answers for other queries also...tank u so much.....
pl post network pgm also.........

hariA

Unknown said...

hi guys....
U hv done a gr8 job.... Thanx da... my wishes to the zoeken team...