Homework three (OCP Preparation QUESTIONS):
In our textbook, there are a few sample tests and questions/answers.  
You should go through some of them to see how the test is given and get the idea how to prepare for it.  
This homework includes the sample questions from previous test samples.  
There may be  the same type questions in the midterm and final.  
-----------------------------------------------------------------------------------------------------------
Q1:
Which two clauses must be included in a select statement ?(choose two).
A. having.
B. Where.
C. Select
D. Order by
E. From
F. Group by.

	C and E (SELECT , FROM)
-----------------------------------------------------------------------------------------------------------
Q2:
Which three values are displayed by the DESCRIBE command? (Choose three)
A. table name.
B. column names.
C. the table name.
D. column datatypes.
E. not null columns.
F. all column constraints.

	B,D, and E(COLUMN NAMES, COLUMN DATATYPES, AND NOT NULL COLUMNS)
----------------------------------------------------------------------------------------------------------
Q3:
The emp table contains these columns:
	Empno		number(2);
	Ename		varchar2(30);
Evaluate this SQL statement:
Select *  From  emp
Order by empno, ename;
How will the results be stored ?
A. numerically.
B. Alphabetically
C. Numerically and then Alphabetically.
D. Alphabetically and then Numerically.

	C(NUMERICALLY AND THEN ALPHABETICALLY)
-----------------------------------------------------------------------------------------------------------Q4.
Which three ways can the SQL buffer be terminated ? (choose three.)
A. Enter a slash ( / ).
B. Press [ESC] twice.
C. Press [RETURN] once.
D. Press [RETURN] twice.
E. Enter an asterisk (*).
F. Enter a semicolon(;).

	D(DOUBLE RETURN),F(SEMICOLON),A(SLASH)
-----------------------------------------------------------------------------------------------------------
Q5. 
You attempt to query the database with this command:

     SELECT         e.ename, d.dname
     FROM             emp e, dept d
     WHERE          e.empno  = d.deptno
     ORDER BY     d.deptno;

Which clause causes an error?
A. Order by d.deptno;
B. FROM  emp e, dept d
C. SELECT         e.ename, d.dname
D. WHERE          e.empno  =  d.deptno
	D
-----------------------------------------------------------------------------------------------------------
Q6
Evaluate this clause:
     SELECT           e.ename, d.dname, d.loc
     FROM              emp e, dept d
     WHERE           e.deptno = d.deptno
     ORDER BY      1;
Eliminating which clause will cause all the rows in the EMP table to be joined to all the rows in the DEPT table?.

A. ORDER BY 1;
B. FROM              emp e, dept d
C. WHERE           e.deptno = d.deptno
D. SELECT           e.ename, d.dname, d.loc
	C(I don't really understand this question, but if you take C away, it will show all info)
-----------------------------------------------------------------------------------------------------------
Q7.
Which SQL command would you use to remove the PARTS_VU view?
A. DROP parts_vu;
B. DELETE parts_vu;
C. DROP VIEW parts_vu;
D. DELETE VIEW parts_vu;
	C(DROP VIEW  PARTS_VU)
------------------------------------------------------------------------------------------------------------
Q8
Which ALTER TABLE statement would you use to add a primary key constraint on the EMPNO column of the EMP table?
A. ALTER TABLE emp  ADD CONSTRAINT pk_emp PRIMARY KEY;
B. ALTER TABLE emp  ADD CONSTRAINT pk_emp PRIMARY KEY (empno);
C. ALTER TABLE emp  MODIFY CONSTRAINT PRIMARY KEY empno;
D. ALTER TABLE emp  MODIFY EMPNO CONSTRAINT PRIMARY KEY;
	B
-----------------------------------------------------------------------------------------------------------
Q9
Which command is used to add new column or modify old columns in an existing table?
A. ALTER  
B. INTO
C. INSERT
D. CREATE
E. UPDATE
	A(ALTER)
-----------------------------------------------------------------------------------------------------------
Q10
You logged onto the database to update the INVENTORY table.  After your session began, you issued three UPDATE 
commands and then you issued an ALTER table command to add a column constraint.  You were about to issue a COMMIT 
command when the system crashed.  Which changes were made to the INVENTORY table?
A. only the UPDATE commands
B. only the ALTER TABLE command
C. both the UPDATE commands and the ALTER TABLE command
D. none
 	B(ALTER ONLY, WHEN INSERTING DATA - NEED A COMMIT TO SAVE)
-----------------------------------------------------------------------------------------------------------
Q11:
Which ALTER TABLE option would you use to increase the precision of a numeric column?
A. ADD
B. DROP
C. MODIFY
D. DISABLE
	C(MODIFY)
-----------------------------------------------------------------------------------------------------------
Q12
You need to create the ELEMENTS table.  The atomic weights of elements have varying 
decimal places.  For example, values could be 4, 4.35, or 4.3567.  Which datatype would be most 
appropriate for the atomic weight values?
A. LONG
B. NUMBER
C. NUMBER(p,s)
D. none
	C. NUMBER(p,s)
------------------------------------------------------------------------------------------------------------
Q13
Evaluate this SQL script:

CREATE USER hr IDENTIFIED BY hr01;
CREATE ROLE hr_director;
GRANT hr_director TO hr;
GRANT SELECT ON teacher TO hr_director;
CREATE OR REPLACE ROLE hr_director
/
How many users are granted the HR_DIRECTOR role 
and how many privileges are granted to the HR_DIRECTOR role?
A. 1 user and 1 privilige
B. 1 user and no privileges
C. no users and one privilige
D. no users and no privileges.
	HR IS GRANTED THE HR_DIRECTOR ROLE
	TEACHER PRIVILAGES ARE GIVEN TO HR_DIRECTOR
 	THEREFORE A?
-----------------------------------------------------------------------------------------------------------
Q14:
What is the datatype is variable in length?
A. DATE
B. VARCHAR2
C. RAW(SIZE)
D. CHAR(SIZE)
E. NUMBER(p,s)
	B.  VARCHAR2(N)
-----------------------------------------------------------------------------------------------------------
Q15:
You accidentally changed all the TECHNICIAN_ID values to 999999 using the UPDATE command
without a WHERE clause. Which command could you issue to undo these change?
A. EXIT
B. QUIT
C. COMMIT
D. ROLLBACK
	D. ROLLBACK	
-----------------------------------------------------------------------------------------------------------
Q16
Evaluate this command:
 SELECT         id_number, description, manufacturer_id
     	FROM            inventory
     	WHERE        price > 7.00
     	ORDER BY   manufacturer_id;

If all the columns of the INVENTORY table have non-unique indexes, which clause will most likely use an index?
A.	SELECT         id_number, description, manufacturer_id
B.	FROM            inventory
C.	WHERE        price > 7.00
D.	ORDER BY   manufacturer_id	
A. 
	D. 
-----------------------------------------------------------------------------------------------------------
Q17:
Which integrity constraint type states that a foreign key value in the 
EMP table must match a primary key value in the DEPT table or be NULL?
A. entity
B. column
C. referential
D. user-defined
	B
-----------------------------------------------------------------------------------------------------------
Q18:
Which two commands would cause an implicit COMMIT command? (Choose two.)
A. GRANT
B. UPDATE
C. CREATE
D. COMMIT
E. SELECT
F. ROLLBACK
	C AND A
-----------------------------------------------------------------------------------------------------------
Q19:
Which type of command could be part of an uncommitted transaction?
A. DML
B. DDL
C. DCL
D. NONE
	B. DLL CAN REPLACE THE COMMIT COMMAND TO END THE TRANSACTION
----------------------------------------------------------------------------------------------------------
Q20:
Evaluate this statement:
     SELECT    column_name
     FROM       user_cons_columns
     WHERE    table_name = 'INVENTORY';
Which values would be displayed?
A. name of all the INVENTORY table columns
B. only the names of the columns in the INVENTORY table  that have constraints
C. only the name of the constraints defined for columns in the INVENTORY table .
D. the column name and constraint type for the columns in the INVENTORY table  that have constraints
	D.?

-----------------------------------------------------------------------------------------------------------
Q21:
For which two types of constraints is an index automatically created? (Choose two.)
A. CHECK
B. UNIQUE
C. NOT NULL
D. FOREIGN KEY
E. PRIMARY KEY
	PRIMARY KEY AND FOREIGN KEY
-----------------------------------------------------------------------------------------------------------
Q22:
Which clause would you use in an ALTER TABLE command to drop the
 PRICE column from the INVENTORY table?
A. DROP
B. ALTER
C. DELETE
D. REMOVE
E. A column canot be dropped from a table.
	ALTER TABLE TABLENAME	
	DROP COLUMN COL. NAME
-----------------------------------------------------------------------------------------------------------
Q23:
Evaluate this command:

     SELECT            empno  "Employee Number", SUM(sal) "Salary"
     FROM               emp
     WHERE            sal > 1000
     GROUP BY       "Employee Number"
     ORDER BY       2;

Which clause will cause an error?
A.	ORDER BY       2
B.	GROUP BY       "Employee Number"
C.	SELECT            empno  "Employee Number", SUM(sal) "Salary"
D.	WHERE            sal > 1000
	B.
-----------------------------------------------------------------------------------------------------------
Q24:
You query the database with this command:
     SELECT  price
     FROM     inventory
     WHERE  price (BETWEEN 1 AND 50)
     OR          (price IN(25, 70, 95)
    AND         price BETWEEN 25 AND 75);
Which value could the statement retrieve?

A. 30
B. 51
C. 75
D. 95
	C.
-----------------------------------------------------------------------------------------------------------
Q25:
The TEACHER table contains these columns:

LAST_NAME            VARCHAR2(25)
FIRST_NAME           VARCHAR2(25)
EMAIL                       VARCHAR(50)

Which SQL statement would you use to display the names of teachers that do not have an E-mail address?
A. select last_name, first_name from teacher where email = NULL;
B. select last_name, first_name from teacher where email <>NULL;
C. select last_name, first_name from teacher where email IS NULL;
D. select last_name, first_name from teacher where email IS NOT NULL;

C AND A?