SQL und PL/SQL Fragen
Question1: Which SQL statement will execute successfully?
(A) CREATE FORCE VIEW last_first_vu
AS SELECT name ||', '|| job "Employee & Position"
FROM employee
WHERE department_id = 30
(B) CREATE FORCE VIEW last_first_vu
AS SELECT name ||', '|| job "Employee & Position",
department_id
FROM employee
ORDER BY "Employee & Position";
(C) CREATE FORCE VIEW last_first_vu
AS SELECT name ||', '|| job "Employee & Position",
department_id "Department"
FROM employee
WHERE Department = 10;
(D) CREATE FORCE VIEW last_first_vu
AS SELECT name ||', '|| job "Employee & Position",
department_id "Department"
FROM employee
ORDER BY name
GROUP BY department_id;
Answer1:
Question2: Given this PL/SQL block:
BEGIN
INSERT INTO employee(salary, last_name, first_name) VALUES(35000, 'Wagner', 'Madeline');
SAVEPOINT save_a;
INSERT INTO employee(salary, last_name,first_name) VALUES(40000, 'Southall', 'David');
SAVEPOINT save_b;
DELETE FROM employee WHERE dept_no = 10;
SAVEPOINT save_c;
INSERT INTO employee(salary, last_name, first_name) VALUES(25000, 'Brown', 'Bert');
ROLLBACK TO SAVEPOINT save_c;
INSERT INTO employee(salary, last_name, first_name) VALUE(32000, 'Dean', 'Mike');
ROLLBACK TO SAVEPOINT save_b;
COMMIT;
END;
Which two changes to the database will be made permanent?
(Choose two.)
(A) DELETE FROM employee
WHERE dept_no = 10;
(B) INSERT INTO employee(salary, last_name, first_name)
VALUE(32000, 'Dean', 'Mike');
(C) INSERT INTO employee(salary, last_name, first_name)
VALUES(25000, 'Brown', 'Bert');
(D) INSERT INTO employee(salary, last_name, first_name)
VALUES(40000, 'Southall', 'David');
(E) INSERT INTO employee(salary, last_name, first_name)
VALUES(35000, 'Wagner', 'Madeline');
Answer2:
Question3:
The PRODUCT table contains these columns:
ID NUMBER(7) PK
SALE_PRICE NUMBER(7,2)
Evaluate these two SQL statements:
1. SELECT MAX(sale_price), MIN(sale_price),
AVG(sale_price)
FROM product;
2. SELECT ROUND(MAX(sale_price),2),
ROUND(MIN(sale_price),2), ROUND(AVG(sale_price),2)
FROM product
GROUP BY sale_price;
How will the results differ?
(A) One of the statements will generate an error.
(B) Statement 2 will only display one row of results; statement 1 could display more than one.
(C) Statement 1 will display three values; statement 2 will display three values for each sale
price.
(D) Statement 1 will display a result for each sale price; statement 2 will display a result for each
product.
Answer3:
Question4: Under which circumstance should you create an index on a table?
(A) The table is small.
(B) The table is updated frequently.
(C) A column's values are static and contain a narrow range of values.
(D) Two columns are consistently used in the WHERE clause join condition of SELECT
statements.
Answer4:
Question5:
The EMPLOYEE table contains these columns:
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
JOB VARCHAR2(25)
SALARY NUMBER(7,2)
DEPT_ID NUMBER(3)
You need to display the first name and last name for
employees whose:
1. Last Name is Brown, Chan, or Lindsey.
2. Job is Manager, Technician, or Clerk.
3. Salary is greater than 30000.
You issue this SQL statement:
SELECT first_name, last_name
FROM employee
WHERE UPPER(last_name) IN ('BROWN', 'CHAN', 'LINDSEY')
AND UPPER(job) IN ('MANAGER', 'TECHNICIAN','CLERK')
AND salary = region.manager_id
Answer 9:
Datum
Studiengang
Semester
Matrnr
Name
Thema SQL und PLSQL
Frage Antwort
1
2
3
4
5
6
7
8
9
DBA Fragen
Question 1: Which command will force Oracle to stop writing to an online redo log
and start writing to a new one?
(A) ALTER DATABASE LOGFILE
(B) ALTER SYSTEM CHECKPOINT
(C) ALTER SYSTEM LOG SWITCH
(D) ALTER SYSTEM SWITCH LOGFILE
Answer 1:
Question 2: If you create a new tablespace, which parameter is required?
(A) DATAFILE
(B) PERMANENT
(C) MINIMUM EXTENT
(D) DEFAULT STORAGE
Answer 2:
Question 3: You are creating a new constraint and only want it to be enforced at the
end of each transaction. Which clause should you use?
(A) DEFERRABLE INITIALLY
(B) NOT DEFERRABLE
(C) INITIALLY DEFERRED
(D) INITIALLY IMMEDIATE
Answer 3:
Question 4: You attempt to create a rollback segment using this command:
CREATE ROLLBACK SEGMENT rbs01
TABLESPACE rbs
STORAGE (
INITIAL 100K
NEXT 100K
MINEXTENTS 1
MAXEXTENTS 100
OPTIMAL 2000K);
Why will this statement fail?
(A) NEXT must be larger than INITIAL.
(B) OPTIMAL cannot be set for rollback segments.
(C) PCTINCREASE must be set for rollback segments.
(D) MINEXTENTS must be set to a value of at least two.
Answer 4:
Question 5: Which administrative script would you run to create the data dictionary
views?
(A) cat*.sql
(B) utl*.sql
(C) prvt*.sql
(D) dbms*.sql
Answer 5:
Question 6: Which view could you query to obtain the number of extents allocated to
the EMP table?
(A) DBA_SEGMENTS
(B) DBA_DATA_FILES
(C) DBA_FREE_SPACE
(D) DBA_TAB_EXTENTS
Answer 6:
Question 7: Which statement about direct-path loads is true?
(A) INSERT triggers fire during direct-path data loads.
(B) All constraints are enforced during a direct-path data load.
(C) Data can be loaded into clustered tables using direct-path loads.
(D) Other users cannot make changes to data during a direct-path data load.
Answer 7:
Question 8: Examine this command:
ALTER SYSTEM ENABLE RESTRICTED SESSION;
What is the state of the instance when this command is executed?
(A) Future connections are not permitted for any users.
(B) Future user connections cannot perform DML, but can only perform queries.
(C) Future connections are permitted only for users with the RESTRICTED SESSION privilege.
(D) All current user sessions are terminated and only users with RESTRICTED SESSION privileges
can access the database.
Answer 8:
Question 9: When can the National Character Set for the database be defined?
(A) at database startup
(B) at database creation
(C) anytime using the dynamic NLS parameters
Answer 9:
Question 10: Which command would you use to regain space for an index containing
deleted entries?
(A) ALTER TABLE emp DROP INDEX;
(B) ALTER INDEX emp_idx REBUILD;
(C) ALTER INDEX emp_idx COALESCE;
(D) ALTER INDEX emp_idx DEALLOCATE UNUSED;
Answer 10:
Question 11: Which three parameters should be specified in the init.ora file before
starting the instance? (Choose three.)
(A) DB_NAME
(B) PROCESSES
(C) DB_BLOCK_SIZE
(D) CONTROL_FILES
(E) DB_BLOCK_BUFFERS
(F) LICENSE_MAX_USERS
Answer 11:
Question 12: Which information is stored in the control file?
(A) redo log data
(B) error log information
(C) data file and redo log locations
(D) log sequence numbers since instance startup
Answer 12:
Question 13: You want user SCOTT to be able to create tables in different schemas.
Which keyword(s) will you use?
(A) ANY
(B) ALL
(C) UNLIMITED
(D) WITH ADMIN
Answer 13:
Question 14: Hector was initially assigned a QUOTA of 20MB for tablespace DATA01.
You want to prevent him from creating any new objects in this tablespace, but still
allow him access to the objects that currently exist. What should you do?
(A) Change his quota to 0.
(B) Lock his user account.
(C) Change his quota to UNLIMITED.
(D) Delete his user account and re-create it using a new quota with a value less than 20MB.
Answer 14:
Question 15: Which two ways can a DBA be authenticated when the
REMOTE_LOGIN_PASSWORDFILE=SHARED parameter value is set?
(Choose two.)
(A) SYS
(B) SYSDBA
(C) ORACLE
(D) SYSOPER
(E) INTERNAL
(F) SYSADMIN
Answer 15:
Question 16: You want to create a new table based on columns in the EMP table using
a subquery. Which clause should you use to prevent the generation of redo log
entries and speed up the creation of the table?
(A) CACHE
(B) LOGGING
(C) NOCACHE
(D) NOLOGGING
Answer 16:
Question 17: What is the first step you should take when transporting a tablespace?
(A) Make the tablespace read-only.
(B) Export the metadata from the source.
(C) Copy the data files to the target system.
(D) Transfer the export file to the target machine.
Answer 17:
Datum
Studiengang
Semester
Matrnr
Name
Thema DBA
Frage Antwort
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17