Ask Anantha

Part 2 – Questions on SQL and SQL *Plus   1. A user is setting up a join operation between tables EMP and DEPT. There are some employees in the EMP table that the user wants returned by the query, but the employees are not assigned to departments yet. Which SELECT statement is most appropriate for this user?   A.       select e.empid, d.head from emp e, dept d; B.       select e.empid, d.head from emp e, dept d where e.dept# = d.dept#; C.       select e.empid, d.head from emp e, dept d where e.dept# = d.dept# (+); D.      select e.empid, d.head from emp e, dept d where e.dept# (+) = d.dept#;   2. Developer ANJU executes the following statement: CREATE TABLE animals AS SELECT * from MASTER.ANIMALS; What is the effect of this statement?   A.       A table named ANIMALS will be created in the MASTER schema with the same d...

Part 1 – Questions on SQL and SQL *Plus   1. Which of the following statements contains an error?   A. SELECT * FROM emp WHERE empid = 493945; B. SELECT empid FROM emp WHERE empid= 493945; C. SELECT empid FROM emp; D. SELECT empid WHERE empid = 56949 AND lastname = 'SMITH';   2. Which of the following correctly describes how to specify a column alias?   A. Place the alias at the beginning of the statement to describe the table. B. Place the alias after each column, separated by white space, to describe the column. C. Place the alias after each column, separated by a comma, to describe the column. D. Place the alias at the end of the statement to describe the table.   3. The NVL function A. Assists in the distribution of output across multiple columns. B. Allows the user to specify alternate output for non-null column values. C. Allows the user to specify alternate output for null column values. D. Nullifies the val...

This script lists the tablespace which cannot extent causing tablespace full. select a.owner||'.'||a.segment_name "Segment Name", a.segment_type "Segment Type", a.bytes/1024/1024 "Size(MB)", a.next_extent/1024/1024 "Next Extent", a.tablespace_name "Tablespace Name" from sys.dba_segments a where a.tablespace_name not like 'T%MP%' -- Exclude TEMP tablespaces and next_extent * 1 > ( -- Cannot extend 1x, can change to 2x... select max(b.bytes) from dba_free_space b where a.tablespace_name = b.tablespace_name) order by 3 desc;