Category: SQL Fundamentals I

To become an Oracle Associate, the first step is to pass a SQL Fundamentals exam. For Oracle 11g, that exam is 1Z0-051. This exam can be taken online as a non-proctored exam, or in a test center as a proctored exam. This category will go through the Exam Topics, explaining the topic and posting questions from sample exams for explorations into the topics.

Restricting Data – Selection in the WHERE Clause

Exam Topic: Restricting and Sorting Data - Limit the rows that are retrieved by a query Returning the right data by restricting rows to answer questions is at the heart of SQL Development. As shown in The Capabilities of a SQL SELECT Statement, the WHERE clause is used to specify conditions for which rows should be returned. If the … Continue reading Restricting Data – Selection in the WHERE Clause

The Capabilities of a SQL SELECT Statement

Exam Topic: Retrieving Data Using the SQL SELECT Statement - List the capabilities of SQL SELECT statements By far, the most important skill for anyone to possess when working with a database is the skill to extract from the database the data you want in the format you desire. The SELECT statement, therefore, is the most important … Continue reading The Capabilities of a SQL SELECT Statement

INTERVAL: Clock Math

INTERVAL is a function that provides a way to add, subtract or just convert. SELECT INTERVAL '300' MONTH, INTERVAL '54-2' YEAR TO MONTH, INTERVAL '11:12:10.1234567' HOUR FROM DUAL; Which is the correct output of the above query? A. +25-00, +54-02, +00 11:12:10.123457 B. +00-300, +54-02,+00 11:12:10.123457 C. +25-00,+00-650,+00 11:12:10.123457 D. +00-300,+00-650,+00 11:12:10.123457 ANSWER: A INTERVAL … Continue reading INTERVAL: Clock Math

Oracle Set Operators: UNION, INTERSECT and MINUS

Did you know you can do set-based math with Oracle? With the UNION, INTERSECT and MINUS set operators, you can compare one result set to another and add or subtract results, called a compound query. This can be a handy tool for your toolbox. First, a couple of things to keep in mind. The number of … Continue reading Oracle Set Operators: UNION, INTERSECT and MINUS

Ending a Transaction

QUESTION: Which three statements/commands would cause a transaction to end? (Choose three.) A. COMMIT B. SELECT C. CREATE D. ROLLBACK E. SAVEPOINT Answer: A,C,D A transaction is a unit of work containing SQL statements. Transactions end when they are committed or rolled back, or when a DDL statement is issued. COMMIT will make the changes … Continue reading Ending a Transaction

Transactions and SAVEPOINT

QUESTION: Which two statements describe the consequence of issuing the ROLLBACK TO SAVE POINT a command in the session? SQL> CREATE TABLE product 2 (pcode NUMBER(2), 3 pname VARCHAR2(10)); Table created. SQL> INSERT INTO product VALUES (1,'pen'); 1 row created. SQL> INSERT INTO product VALUES (2,'pencil'); 1 row created. SQL> SAVEPOINT a; Savepoint created. SQL> … Continue reading Transactions and SAVEPOINT