CIS4580

12) The result of " SELECT ADD_MONTHS('02/01/2019',2) FROM dual; " is -----------------.

04/01/2019

The result for "SELECT POWER(5,3) FROM DUAL; " is ----------------- .

125

3) The result for "SELECT ABS(-16.8) FROM DUAL; " is ----------------.

16.8

The result for "select chr(50) from dual; " is -----------------------.

2

13) The result of " select last_day('03/10/2019') from dual; " is -----------------.

3/31/19

What is the maximum length of a table name in oracle 11g?

30

5) ________ is a set of commands used to update a database. A) DML B) DDL C) DCL D) DPL

A

A database is an organized collection of ________ related data. A) logically B) physically C) loosely D) badly

A

Explain Oracle join operator.

A relational operation that causes two or more tables with a common domain to be combined into a single table or view. Oracle JOINS are used to combine data from multiple tables to form a single result set.

18) The SQL GROUP BY clause can be used in a -------------- statement to collect data across multiple records and group the results by one or more columns. A) SELECT B) DELETE C) DROP D) ALTER

A. Select

---------- returns the absolute value of n.

ABS

10) ------------- returns the decimal representation in the database character set of the first character of the result.

ASCII

Which of the following is not a valid oracle data type?

Alphanumeric

Write a query and add a new column to employee table, and name it emp_salary with a data type number(20).

Alter table employee add emp_salary number(20);

Write a query and remove column emp_city from employee table.

Alter table employee drop column emp_city;

ANSI stands for: --------------------------------

American National Standards Institute

ASCII stands for: --------------------------------

American Standard Code for Information Interchange

1) Which of the following special character cannot be part of a table name?A) $ (Dollar sign)B) - (Dash)C) _ (Underscore)D) # (Hash)

B

2) ________ are established between entities in a well-structured database so that the desired information can be retrieved.A) EntitiesB) RelationshipsC) LinesD) Ties

B

3) The ________ is the structure that contains descriptions of objects such as tables and views created by users. A) SQL B) schema C) catalog D) master view

B

6) DDL is typically used during which phase of the development process? A) Implementation B) Physical design C) Analysis D) Evaluation

B

7) Any create command may be reversed by using a(n) ________ command. A) undoB) drop C) delete D) unpack

B

8) The SQL command ________ adds one or more new columns to a table. A) create table B) alter table C) create view D) create relationship

B

11) ---------------------- statement is used to delete a single record or multiple records from a table. A) Drop B) Delete C) Truncate D) All of the above

B. Delete

9) An ---------- is an optional structure, associated with a ---------, and it speeds data access. A) Table, index B) Index, table C) Index, view D) View, index

B. Index, table

13) Which of the following statement retrieves data from a table? A) Insert B) Select C) Alter D) Update

B. Select

2) Which one is a valid table name in an Oracle database?A) Customer Orders B) Order C) Orders D) 2Order

C

4) ________ is a set of commands used to control a database, including security. A) DML B) DDL C) DCL D) TCL

C

7) A ------------- is a logical representation of another table or combination of tables. A) Virtual table B) View C) Both of the above D) None of the above

C Both of the above

17) Which clause is used to sort the records in your result set. A) Group by B) Sort by C) Order by D) None of the above

C. Order by

_______ returns number of rows

COUNT

Use the _________ statement to define a view.

CREATE VIEW

11) ---------------- returns the current date and time set for the operating system on which the database resides

CURRENT_TIMESTAMP

SELECT CHR(67)||CHR(108)||CHR(97)||CHR(115)||CHR(115) FROM DUAL;" will return ---------.

Class (based on ASCII)

9) How to make changes permanent after manipulating a table?A) RollbackB) UpdateC) DeleteD) Commit

D

12) Which of the following is an inequality operator? A) != B) ^= C) <> D) All of the above

D. All

16) ------------------------statement is used to add, modify, or drop/delete columns in a table. A) Update table B) Delete table C) Truncate table D) Alter table

D. Alter table

10) ---------------- lists the column definitions. A) Desc B) Describe C) None of the above D) Both of the above

D. Both

8) Which one is not an advantage of views? A) Enhance programming productivity B) Use little storage space C) Simplify query commands D) Contain old data of a table

D. Contain old data of a table

14) Which of the following object will be created by default for the primary key column if you define a primary key on a table? A) View B) Sequence C) Trigger D) Index

D. Index

Write a query to remove the records of employee with the zip code of 91016.

Delete from employee where emp_zip=91016;

Write a query and remove all the data in employee table.

Delete from employee; OR Delete employee; OR Truncate table employee;

Write a query to remove the employee table.

Drop table employee;

Which is not a valid table name in Oracle database?A) Emp_billingB) Emp-billingC) Emp$billingD) Emp#billing

Emp-billing

The Oracle INSERT ALL statement is used to add multiple rows just in one table at a time.

FALSE

21) Table name cannot contain more than one word.

False

25) Alter table dept add dept_phone number(10);

False

Oracle JOINS are used to retrieve data from just one table

False

SELECT INITCAP('I am a student.') FROM DUAL; " will return ----------------------.

I Am A Student.

The Oracle ----------------- statement is used to add multiple rows with a single INSERT statement

INSERT ALL

Write a query and add one record into emp_id, emp_name, dept_id columns in table.

Insert into employee (emp_id, emp_name, dept_id) values (100, 'Jack Smith', 10); Commit;

Write a query and add one record in all columns in Employee table.

Insert into employee values (101, 'Sara Beteta ', '123 Santa anita ave ', 90107, 'Pasadena ', 'CA ', 10); Commit;

15) The -------------- conditions specify an involving pattern matching. A) LIKE B) = C) None of the above D) Both of the above

LIKE

14) --------------function returns the number of months between date1 and date2.

MONTHS_BETWEEN

---------------- functions accept numeric input and return numeric values.

Numeric

SELECT initcap(SUBSTR('wonderful',2,5)) FROM DUAL; " will return -----------------.

Onder

What are the types of Oracle join (no explanation)?

Oracle INNER JOIN (JOIN) Oracle LEFT OUTER JOIN (LEFT JOIN) Oracle RIGHT OUTER JOIN (RIGHT JOIN) Oracle FULL OUTER JOIN (FULL JOIN)

16) The result of "SELECT REPLACE('Pen','e','i') FROM DUAL; " is -----------------.

Pin

7. Write a SELECT statement to list the information of employees whose id is less than or equal 250

Select * from employee where emp_id <=250;

6. Write a SELECT statement to list all employees that live either in zip code 91106, 91023, or 11430.

Select * from employee where emp_zip in (91106,91023,11430);

Write a query to see all records of employee table.

Select * from employee;

Write a query to find out the number of records in employee table.

Select count(*) from employee;

2. Write a SELECT statement to list the address of employees that do not have the department ID equals 100.

Select emp_address from employee where dept_id<>100;

5. Write a SELECT statement to list the cities which zip codes include 11 in it.

Select emp_city from employee where emp_zip like '%11%';

3. Write a SELECT statement to list the id of employees who doesn't live in Los Angeles and Pasadena.

Select emp_id from employee where emp_city not in ('Los Angeles', 'Pasadena'); OR Select emp_id from employee where emp_city <>'Los Angeles ' and emp_city <>'Pasadena ';

4. Write a SELECT statement to list the employees name whose address start with 234 duarte.

Select emp_name from employee where emp_address like '234 duarte%';

Write a SELECT statement to list employee name and address where name ends with 'M'.

Select emp_name, emp_address from employee where emp_name like '%M';

Based on EMPLOYEE (emp_id, emp_name, emp_address, emp_zip, emp_city, emp_state, dept_id) 1. Write a SELECT statement to list zip codes and their cities in California.

Select emp_zip , emp_city from employee where emp_state= 'CA' ;

SELECT UPPER(SUBSTR('interesting',3,4)) FROM DUAL; " will return -----------------.

TERE

19) A database schema is a way to logically group objects such as tables, views, stored procedures, etc.

TRUE

6) -------------------- is the basic unit of data storage in an Oracle Database. A) Index B) Block C) View D) Table

Table

20) The table name must begin with a letter.

True

23) A view is a logical representation of another table or combination of tables.

True

24) Indexes are reducing disk Input/Output.

True

Write a query to change the name of the city to 'Pasadena' where zip code is 91107.

Update employee set emp_city= 'Pasadena ' where emp_zip=91107;

Write a query to change the name of the city to 'Pasadena' and the zip code to 91107 where zip code is 91006.

Update employee set emp_city='Pasadena' , emp_zip=91107 where emp_zip=91006;

12. Write a query and update the salary for employee by id equal 100 to $50000

Update employee set emp_salary=50000 where emp_id=100;

------------------------------ is a virtual table that does not physically exist. Rather, it is created by a query joining one or more tables.

View

An ---------------------------- summarizes the results of an expression over a number of rows, returning a single value.

aggregate function

--------------------- are used to give a column in a table, a temporary name.

column alias

A HAVING clause restricts the results of an ORDER BY in a select

false

The DISTINCT clause ignores NULL values

false

The FULL OUTER JOIN can be written in the old syntax without using a UNION query.

false

The general comparison functions determine the ------------and/or -----------value from a set of values.

greatest, least

The result of "SELECT lower(REPLACE('Pen','e','i')) "Changes" FROM DUAL; " is -----------------.

pin

The Oracle DISTINCT clause is used to --------------------------- from the result set.

remove duplicates

FULL OUTER JOIN returns all rows from the LEFT-hand table and RIGHT-hand table.

true

Oracle JOINS return all rows from multiple tables where the join condition is met.

true

RIGHT OUTER JOIN returns all rows from the Right-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal.

true

Tables are the basic unit of data storage in an Oracle Database.

true

The DISTINCT clause can only be used with SELECT statements.

true

The Oracle INNER JOIN would return the records where table1 and table2 intersect

true

Use the HAVING clause to restrict the groups of returned rows to those groups for which the specified conditions.

true

select lower('WORLD') from dual; " will return -------------------------------.

world