Psst.. new poll here.
Psst.. new forums here.
Microsoft is blocking us again (TY IP Reputation!) so just use oauth login instead. :)
Paste
Pasted as C by RR ( 9 years ago )
Q-1.1: Get the details of all the database objects and their types created by the current
user.
SELECT object_name,object_type from USER_OBJECTS;
OBJECT_TYPE
-------------------
CUSTID
SEQUENCE
CUST
TABLE
CURRENT_PRODUCTS
TABLE
OBJECT_NAME
--------------------------------------------------------------------------------
Q-1.2 Get the details of all the table names owned by current user
SELECT user_name from user_tables;
TABLE_NAME
------------------------------
TGT_SALESSUMMARY_17
VNVLAB10
EMP10
STUDENT1
CAR
CUST
REG
INDEXVNV
NEW_EMP
PROJECT
COMP_EMP
TABLE_NAME
------------------------------
U_EMP
STUDENT_DETAILS
MARKS_DETAILS
T
SALGRADE
CUSTOMER2016
HOSPITAL2016
FLATS_MASTERS
DEPT
BONUS
DUMMY
Q-1.3 Get the details of table names and corresponding column names
select table_name,column_name from user_tab_cols;
TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
COMBO F3
EMP101_SALES_VW HIREDATE
MYVIEW2 MEMBER_ID
MYVIEW7A1 MEMBER_ID
Q-1.4 Get the details of column names and corresponding constraint names
SELECT constraint_name,column_name FROM user_cons_columns;
CONSTRAINT_NAME
------------------------------
COLUMN_NAME
--------------------------------
BIN$ylBpflEpRZCs0P9TMCxE+g==$0
TITLE
BIN$AR8T4rVFS92GtIjSfLt/Tg==$0
DEPTNO
Q-1.5: Get the details of the constraints and corresponding table name.
SELECT constraint_name,table_name FROM user_constraints;
CONSTRAINT_NAME TABLE_NAME
------------------------------ ------------------------------
SYS_C00675016 STAFF_MASTERS
SYS_C00675021 BOOK_MASTERS
BIN$O4UmRtAgSPukCzomqPeSqg==$0 BIN$aZ9uiRVIRHKOllOPNGqPDg==$0
SYS_C00678354 MY_EMPLOYEES
SYS_C00678360 MY_EXECUTIVES
SYS_C00680974 TEMP1
CUSTID_PK CUSTOMERMASTER
ACC_PK ACCOUNTSMASTER
Q-1.6: Get the details of all the View names and corresponding Text of the same.
select view_name,text from user_views;
MYVIEW6
select i.book_no,
book_name,member_id,
issue_date,return_date
from book b, issu
MYVIEW7A
VIEW_NAME
------------------------------
TEXT
-----------------------------------------------------
select i.book_no,book_name,i.member_id,member_name,
issue_date,return_date from
MYVIEW7
select i.book_no,book_name,i.member_id,member_name,
issue_date,return_date from
Q-1.7: Get the details of all the Sequence names and their last numbers reached so far.
select sequence_name,last_number from user_sequences where rownum=10;
SEQUENCE_NAME LAST_NUMBER
------------------------------ -----------
S1 11
S2 11
SEQ 21
SEQ_BILL_NUM 81
SEQ_EMP 21
SEQ_NAME 120
SEQ_NAME1 21
STUD_NO 21
USER_MASTER_SEQ 9028
Q-1.8: Get the details of all the Synonym names and their parent object names.
select synonym_name,table_name from user_synonyms;
SYNONYM_NAME TABLE_NAME
-----------------------------------------------------------------------
SYNEMP1 EMP101
Q-1.9: Get the list of all the Index names
select index_name from all_indexes;
INDEX_NAME
------------------------------
SYS_C00678360
CUSTID_PK
ACC_PK
PLSQL NO 2
Q-2.1
Identify the problems(if any) in the below declarations:
DECLARE
V_Sample1 NUMBER(2);
V_Sample2 CONSTANT NUMBER(2) ;
V_Sample3 NUMBER(2) NOT NULL ;
V_Sample4 NUMBER(2) := 50;
V_Sample5 NUMBER(2) DEFAULT 25;
>> sample 2 and sample 3 should be initialized.
-----------------------------------------------------------------------------------------------------------------------
Q-2.2
The following PL/SQL block is incomplete.
Modify the block to achieve requirements as stated in the comments in the block.
<<outer>>
DECLARE --outer block
var_num1 NUMBER := 5;
BEGIN
<<inner>>
DECLARE --inner block
var_num1 NUMBER := 10;
BEGIN
DBMS_OUTPUT.PUT_LINE('Value for var_num1:' ||var_num1);
DBMS_OUTPUT.PUT_LINE('Value for var_num1:' ||OUTER.var_num1);
--Can outer block variable (var_num1) be printed here.If Yes,Print the same.
END;
--Can inner block variable(var_num1) be printed here.If Yes,Print the same.
DBMS_OUTPUT.PUT_LINE('Value for var_num1:' ||INNER.var_num1);
END;
DBMS_OUTPUT.PUT_LINE('Value for var_num1:' ||INNER.var_num1);
*
ERROR at line 15:
ORA-06550: line 15, column 52:
PLS-00219: label 'INNER' reference is out of scope
ORA-06550: line 15, column 1:
PL/SQL: Statement ignored
>>The inner reference is out of scope.
<<outer>>
DECLARE --outer block
var_num1 NUMBER := 5;
BEGIN
<<inner>>
DECLARE --inner block
var_num1 NUMBER := 10;
BEGIN
DBMS_OUTPUT.PUT_LINE('Value for var_num1:' ||INNER.var_num1);
DBMS_OUTPUT.PUT_LINE('Value for var_num1:' ||OUTER.var_num1);
--Can outer block variable (var_num1) be printed here.If Yes,Print the same.
END;
--Can inner block variable(var_num1) be printed here.If Yes,Print the same.
END;
Value for var_num1:10
Value for var_num1:5
PL/SQL procedure successfully completed.
Q-2.3. Write a PL/SQL block to retrieve all staff (code, name, salary) under specific department number
and display the result. (Note: The Department_Code will be accepted from user. Cursor to be used.)
SQL> DECLARE
2 CURSOR c_emp is select dept_code,staff_name,staff_sal
3 from staff_masters
4 where dept_code=&id;
5 V_emp C_emp%ROWTYPE;
6 BEGIN
7 OPEN C_emp;
8 LOOP
9 FETCH C_emp INTO V_emp;
10 DBMS_OUTPUT.PUT_LINE('Name: '|| V_emp.staff_name || 'ID: '
11 || V_emp.dept_code || 'Salary: ' || V_emp.staff_sal);
12 FETCH C_emp INTO V_emp;
13 EXIT WHEN C_emp%NOTFOUND;
14 END LOOP;
15 CLOSE C_emp;
16 END;
17 /
Enter value for id: 20
old 4: where dept_code=&id;
new 4: where dept_code=20;
Name: ShyamID: 20Salary: 20000
Name: SmithID: 20Salary: 62000
Name: RahulID: 20Salary: 22000
PL/SQL procedure successfully completed.
Q-2.4. Write a PL/SQL block to increase the salary by 30 % or 5000 whichever minimum for
a given Department_Code.
SQL> DECLARE
2 CURSOR C_sal is select first_name,salary from employees
3 where department_id=&id;
4 V_sal C_sal%ROWTYPE;
5 V_new employees.salary%TYPE;
6 BEGIN
7 OPEN C_sal;
8 LOOP
9 FETCH C_sal into V_sal;
10 V_new:=((30*V_sal.salary)/100);
11 if(V_new<5000)
12 THEN DBMS_OUTPUT.PUT_LINE('Salary= '||(V_sal.salary+V_new));
13 else
14 DBMS_OUTPUT.PUT_LINE('Salary= '||V_sal.salary+5000);
15 end if;
16 exit when C_sal%NOTFOUND;
17 END LOOP;
18 END;
19 /
Enter value for id: 10
old 3: where department_id=&id;
new 3: where department_id=10;
Salary= 5720
Salary= 5720
PL/SQL procedure successfully completed.
Q-2.5. Write a PL/SQL block to generate the following report for a given Department code
Student_Code Sudent_Name Subject1 Subject2 Subject3 Total Percentage
Grade
Note: Display suitable error massage if wrong department code has entered and if there
is no student in the given department.
For Grade:
Student should pass in each subject individually (pass marks 60).
Percent >= 80 then grade= A
Percent >= 70 and < 80 xss=removed>= 60 and < 70 xss=removed> DECLARE
CURSOR C_stu is select distinct d.student_code,s.subject1,s.subject2,
s.subject3,
d.student_name,d.dept_code
from student_marks s, student_master d
where s.student_code=d.student_code AND d.dept_code=&code;
BEGIN
FOR R IN C_stu
LOOP
DBMS_OUTPUT.PUT_LINE('STudent code: '||R.student_code||'Sudent_N
ame'||R.student_name||
'Subject1: '||R.subject1||'Subject2: '||R.subject2||'Subject3: '
||R.subject3||
'total_marks: '||to_char(R.subject1+R.subject2+R.subject3)||'Per
centage: '||
Round((R.subject1+R.subject2+R.subject3)/3));
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Invalid code!!!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Invalid Department!!!');
END;
/
Enter value for code: 10
old 5: where s.student_code=d.student_code AND d.dept_code=&code;
new 5: where s.student_code=d.student_code AND d.dept_code=10;
STudent code: 1002Sudent_NameRaviSubject1: 66Subject2: 74Subject3:
88total_marks: 228Percentage: 76
STudent code: 1008Sudent_NameDevSubject1: 69Subject2: 44Subject3: 52total_marks:
165Percentage: 55
STudent code: 1020Sudent_NameAmritSubject1: 45Subject2: 55Subject3:
65total_marks: 165Percentage: 55
STudent code: 1002Sudent_NameRaviSubject1: 89Subject2: 96Subject3:
78total_marks: 263Percentage: 88
STudent code: 1008Sudent_NameDevSubject1: 72Subject2: 55Subject3: 55total_marks:
182Percentage: 61
STudent code: 1014Sudent_NameSunilSubject1: 65Subject2: 64Subject3:
90total_marks: 219Percentage: 73
STudent code: 1001Sudent_NameAmitSubject1: 68Subject2: 44Subject3:
92total_marks: 204Percentage: 68
STudent code: 1014Sudent_NameSunilSubject1: 73Subject2: 74Subject3:
65total_marks: 212Percentage: 71
STudent code: 1020Sudent_NameAmritSubject1: 55Subject2: 45Subject3:
78total_marks: 178Percentage: 59
STudent code: 1001Sudent_NameAmitSubject1: 55Subject2: 45Subject3:
78total_marks: 178Percentage: 59
PL/SQL procedure successfully completed.
Q-2.6. Write a PL/SQL block to retrieve the details of the staff belonging to a particular
department. Department code should be passed as a parameter to the cursor
DECLARE
CURSOR staff(p_num NUMBER) IS SELECT * FROM staff_masters where dept_code=p_num;
staffDet staff_masters%ROWTYPE;
deptno staff_masters.dept_code%ROWTYPE;
BEGIN
deptno:=&dept_code;
IF staff%ISOPEN
THEN
null;
ELSE
OPEN staff(deptno);
END IF;
FETCH staff INTO staffDet;
DBMS_OUTPUT.PUT_LINE(chr(10));
DBMS_OUTPUT.PUT_LINE('Code'||chr(9)||'Name'||chr(9)||'Designation'||chr(9)||
'Mgr_code'||chr(9)||'Dept_code'||chr(9)||'Staff_DOB'||chr(9)||'Hiredate'||chr(9)
'Staff_sal'||chr(9)||'Staff_address');
LOOP
DBMS_OUTPUT.PUT_LINE(staff_Det.staff_code||chr(9)||staff_Det.staff_name||chr(9)||
staff_Det.design_code||chr(9)||staff_Det.mgr_code||chr(9)||staff_Det.dept_code||chr(9)||
staff_Det.staff_dob||chr(9)||staff_Det.hiredate||chr(9)||staff_Det.staff_sal||chr(9)||
staff_Det.staff_address);
DBMS_OUTPUT.PUT_LINE(chr(10));
FETCH staff INTO staffDet;
EXIT WHEN staff%NOTFOUND;
END LOOP;
Revise this Paste
Children: 84939