some questions about sql question 40 1. Evaluate the SELECT statements in the fo
ID: 3730855 • Letter: S
Question
some questions about sql
question 40
1. Evaluate the SELECT statements in the following SQL compound queries:
SELECT emp_id
FROM emp
INTERSECT
SELECT emp_id
FROM emp_hist;
SELECT emp_id
FROM emp_hist
INTERSECT
SELECT emp_id
FROM emp;
Which statement is TRUE regarding these SQL compound queries?
The results of the compound queries will be identical.
The first compound query will return more results than the second.
The second compound query will return more results than the first.
The second compound query will return a syntax error.
0.5 points
Question 45
1.
You have created the following function:
CREATE OR REPLACE FUNCTION get_salary
(v_employee_id NUMBER)
RETURN NUMBER
IS
v_salary NUMBER;
BEGIN
SELECT salary
INTO v_salary
FROM employees
WHERE employee_id = v_emp_id;
RETURN v_salary;
END get_salary;
Which two statements will successfully invoke the get_salary function from the SQL*Plus environment?
(Choose two.)
a.
get_salary(205);
b.
EXECUTE get_salary(200);
c.
SELECT get_salary(150) from emp;
d.
SELECT get_salary(205) from dual;
e.
UPDATE emp SET SALARY = get_salary(200) WHERE employee_id = 250;
Question 48
1.
Examine this procedure:
CREATE OR REPLACE PROCEDURE find_cpt
(v_movie_id {argument mode} NUMBER, v_cost_per_ticket {argument mode} NUMBER)
IS
BEGIN
IF v_cost_per_ticket > 9.50 THEN
SELECT cost_per_ticket
INTO v_cost_per_ticket
FROM gross_receipt
WHERE movie_id = v_movie_id;
END IF;
END;
Which argument mode should be used for v_movie_id ?
a.
IN
b.
OUT
c.
IN OUT
d.
IN RETURN
Question 51
1.
Examine this function:
CREATE OR REPLACE FUNCTION set_budget
(v_studio_id IN NUMBER, v_new_budget IN NUMBER)
RETURN NUMBER
IS
BEGIN
UPDATE studio
SET yearly_budget = v_new_budget
WHERE id = v_studio_id;
COMMIT;
RETURN SQL%ROWCOUNT;
END;
While executing this in SQL*Plus, you want to see the value of SQL%ROWCOUNT displayed on the screen.
Which line of code will accomplish this?
a.
OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT));
b.
DBMS_DEBUG.PUT_LINE(TO_CHAR(SQL%ROWCOUNT));
c.
DBMS_OUTPUT.DISPLAY(TO_CHAR(SQL%ROWCOUNT));
d.
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT));
Question 58
1. The ACCOUNT table contains these columns:
ACCOUNT_ID NUMBER(12) NEW_BALANCE NUMBER(7,2) PREV_BALANCE NUMBER(7,2) FINANCE_CHARGE NUMBER(7,2)
You must create statements to be mailed to all account holders. Each customer's statement must include the account holder's previous balance and finance charge in this format:
Previous Balance: 5000 Finance Charge: 45
Which SELECT statement will produce these results?
SELECT Previous Balance: ||prev_balance|| Finance Charge: ||prev_balance * .009 FROM account;
SELECT 'Previous Balance:' ||prev_balance|| 'Finance Charge:' ||prev_balance * .009 FROM account;
SELECT 'Previous Balance: '||prev_balance||' Finance Charge: '||prev_balance * .009 FROM account;
SELECT "Previous Balance: "||prev_balance||" Finance Charge: "||prev_balance * .009 FROM account;
Question 60
1.
Examine the following code:
CREATE OR REPLACE PACKAGE BODY update_emp
AS
FUNCTION show_sal(emp_id)
RETURN NUMBER
IS
v_salary NUMBER;
inc_sal NUMBER;
BEGIN
SELECT salary INTO v_salary FROM emp
WHERE employee_id = emp_id;
RETURN (v_salary);
END show_sal;
BEGIN
inc_sal := 5000;
END update_emp;
In the given code, when will the inc_sal variable be initialized?
a.
every time the update_emp package is invoked in a session
b.
when the package body of the update_emp package is compiled
c.
when the update_emp package is invoked for the first time in a session
d.
every time you invoke the show_sal function contained in the update_emp package
The results of the compound queries will be identical.
The first compound query will return more results than the second.
The second compound query will return more results than the first.
The second compound query will return a syntax error.
0.5 points
Question 45
1.
You have created the following function:
CREATE OR REPLACE FUNCTION get_salary
(v_employee_id NUMBER)
RETURN NUMBER
IS
v_salary NUMBER;
BEGIN
SELECT salary
INTO v_salary
FROM employees
WHERE employee_id = v_emp_id;
RETURN v_salary;
END get_salary;
Which two statements will successfully invoke the get_salary function from the SQL*Plus environment?
(Choose two.)
a.
get_salary(205);
b.
EXECUTE get_salary(200);
c.
SELECT get_salary(150) from emp;
d.
SELECT get_salary(205) from dual;
e.
UPDATE emp SET SALARY = get_salary(200) WHERE employee_id = 250;
Explanation / Answer
question 40:
solution: The results of the compound queries will be identical.
proof:
assume we take two table one is emp table and anther is Emp_hist.....
EMP(emp_id, some other domain or column entries)------>assume empid ==> 1,2,3
EMP_Hist(emp_id, some other domain or column entries)------>assume empid ==> 1,2,3,,4,5
in case one query.---->result gives empid----->1,2,3;
in case second query.---->result gives empid----->1,2,3;
Question 45 : solution:
a) get_salary(205);
d) SELECT get_salary(205) from dual;
in pl/sql function can executed from
sql prompt
select statement
anther pl/sql block
any front end application
Question 48: solution:
a) IN
in pl/sql formal parameter is three types:
IN - ----> in parameter allways receive value from main program,and it is read only and it is default
OUT ------> OUT parameter allways send value to main program ..it is write only.
IN OUT ---->receive value and send value to main program. read and write
Question 51 solution
d) DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT));
example for print value in pl/sql
DBMS_OUTPUT.PUT_LINE(empid || ' '||ename); when we try to print empid and ename.
DBMS_OUTPUT.PUT_LINE()---->this command use for displaying result .
Question 58 solution:
SELECT 'Previous Balance: '||prev_balance||' Finance Charge: '||prev_balance * .009 FROM account;
Question 60 solution:
d) every time you invoke the show_sal function contained in the update_emp package
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.