بعد التحية
هذه بعض الأسئلة من اختبارات اوراكل :-
1. Evaluate the following statement:
SELECT count(*)
FROM employee
WHERE salary >
(SELECT AVG(salary)
FROM employee
WHERE department_id =
(SELECT department_id
FROM employee
WHERE last_name = 'SMITH'));
This statement will display:
a). number of employees whose salary is more than the average salary of all employees
b). number of departments with average salary higher than the average salary of department where Mr. SMITH is working in
c). number of employees whose salary is more than the average salary of department where Mr. SMITH is working in
2. Evaluate a data model with the entity of SALES, CAR and EMPLOYEE. one SALES may sale many CARs, one CAR could be sold, then trade in and then sold in another SALES. What is the relationship between CAR and SALES?
a). 1 to 1 relationship
b). 1 to many relationship
c). many to many relationship
3. Check the following 2 statements:
Statement 1:
SELECT *
FROM user_tables;
Statement 2:
SELECT *
FROM all_tables;
a) Statement 1 and Statement 2 will display same results;
b) Statement 1 will show all tables the user created, Statement 2 will
show all tables the user can access;
c) Statement 2 can't execute.
4. A user Mary issued:
SELECT table_names
FROM all_tables
WHERE user_name='SCOTT';
a) All tables belonged to user SCOTT will be displayed;
b) the SQL can't be excuted;
c) Some system privilege should be granted to Mary,
d) All tables belonged to user SCOTT will be displayed if SCOTT
issued "GRANT SELECT ... TO MARY".
5. what are two advantages of using %TYPE to declare PL/SQL variable?
a) the datatype of underlying database column may be unknown;
b) the datatype of underlying database column may change during runtime;
c) the number and the datatype of underlying database column may be unknown;
d) all column constrains will be applied to declared variable in %TYPE;
6. Look at the following SALESMAN table:
ID NAME SAL COMM
1 Tom 10000
2 Mary 12000
3 Ford 8000
4 Chales 25000
5 Michael 40000
6 Richard 35000
7 Dennis 80000
8 Henry 9000
CREATE VIEW salesman_vu
AS SELECT ID, NAME, SAL, COMM
FROM salesman
WHERE sal > 38000
WITH CHECK OPTION;
Whose salary could be updated on the view?
a) Michael
b) Mary
c) Henry
d) None
e) You can not use WHERE in CREATE VIEW;
7. Look at the SALES table:
Order-id pid Price Salesman
PK
NN, U NN NN NN
NUMBER NUMBER NUMBER Varchar2(10)
How to drop an index on column Order-id, sales_orderid_ind:
a) DROP INDEX sales_orderid_ind
ON sales;
b) REMOVE INDEX sales_orderid_ind
ON sales;
c) DELETE INDEX sales_orderid_ind
ON sales;
d) you can not drop an index made on primary key column;
8. Look at the SALES table:
SALES
Order-id pid price Sm_id ...
PK
NN, U NN NN NN
NUMBER NUMBER NUMBER NUMBER
Examine the following SQL:
INSERT INTO sales
Values (7321, 55,124)
What causes an error?
a) a keyword missing
b) a mandatory condition is not matched
c) INSERT clause should be modified
9. which is a correct name for a table
a) number
b) 1996_invoice
c) invoice-1996
d) invoice@1996
e) catch_#
10. Evaluate the following statements:
SELECT ename, sal
FROM employee
WHERE sal IN (SELECT AVG(sal)
FROM employee
WHERE deptno=3
OR deptno=5
GROUP BY deptno);
a) this statement contains error;
b) all employees' name with their salary will be displyed;
c) a list of names and salary for those employees who have
the same salary as the average salary of department 3 or department 5;
d) a list of names and salary for those employees who works
in department 3 or department 5 and have the same salary as the average salary in their department .
11. The table 'player' has the records of all players'name and their salary, hired date, commission, and their manager ID, a manager is also a player. Then issued statement:
SELECT p.id, p.hire_date, m.id, m.hire_date
FROM player p, player m
WHERE p.manager_id=m.id
AND p.hire_date <= m.hire_date
AND p.commission <> 0
You desire:
1) display all player's id and their hire date with their managers' id an hiredate;
2) exclude those who were hired later than their manager;
3) exclude those who do not earn commission.
a) all of your desired will be arrived;
b) 1 of your desired will be arrived;
c) 2 of your desired will be arrived;
d) none of your desired will be arrived;
e) the statement contains error;
12. What values will be returned for the following statement:
SELECT name
FROM custom
WHERE (name LIKE '%s%' AND name LIKE '%t%' AND name LIKE '%p%' ) OR (name LIKE '%r%' )
What names would be displayed?
a) 'stop' and 'tropys' but not "pulse";
b) 'price' and 'tropys' but not "topics";
c) 'true' and 'robots' but not "mary";
d) 'frank' and 'henry' but not "politics";
13. which query can be used to view the name of primary key you created on a table, the table name should be prompted for user input?
a) SELECT constrain_names
FROM user_cons_columns
WHERE table_name='&table_name' and CONSTAIN_TYPE='PRIMARY';
b) SELECT constrain_names
FROM all_constains
WHERE table_name='&table_name' and CONSTAIN_TYPE='P';
c) SELECT constrain_names
FROM user_constrains
WHERE table_name='&table_name' and CONSTAIN_TYPE='P';
14. FOR I in 1..10 LOOP
IF I=3 or I=6 THEN
NULL;
ELSE
INSERT TABLE x
VALUES(I);
ENDIF;
COMMIT;
END LOOP;
ROLLBACK;
How many rows would be insert into table x
a) 0
b) 1
c) 4
d) 6
e) 8
f) 10
15. What will happen if you create indexes for all 6 columns on employee table?
a) Increase the speed of INSERT;
b) Reduce the system I/O speed when use SELECT;
c) Reduce the system I/O speed when DELETE rows;
d) Increase the speed of using WHERE
16. Which statements will return DATE value?(choose 3)
a) '01-JAN-89'-'01-JAN-90';
b) MONTHS_BETWEEN('01-JAN-89', '01-JAN-90');
c) '01-JAN-89'-20;
d) '01-JAN-89'-(365/12)
e) ROUND('01-JAN-89', 'YAER');