SQL

APEX stands for --------------------.

(Oracle) Application Express

Which of the following special character cannot be part of a table name?

- (Dash)

The ------ delimiter is the Range operator.

..

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

03/31/2019

This wizard does not support tables having more than ----columns.

127

Return the result of the following queries: B)SELECT ABS(-161.6) FROM DUAL;

161.6

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

171.8

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

2

Return the result of the following queries: A)SELECT ROUND(25.123,1) "Round" FROM DUAL;

25.1

SELECT ROUND(25.123,1) "Round" FROM DUAL;

25.1

SELECT POWER(4,4) "Raised" FROM DUAL;

2569

SELECT ROUND(43.129,2) "Round" FROM DUAL;

43.13

SELECT id, SUBSTR(name,2,2) "Substring" FROM employee where id>=7;ID | Substring | --------

7 | ar 8 | un 9 | ne 10| ub

Return the result of the following queries: D) SELECT POWER(2,3) FROM DUAL;

8

The ------ delimiter is the remote access indicator.

@

Name the events which can be executed by using triggers?

A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).A database definition (DDL) statement (CREATE, ALTER, or DROP).A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

What is a form in Oracle APEX application?

A form is a window on the screen that provides areas for entering or modifying data in a database.

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.

What is a sequence in Oracle database?

A sequence is an object in Oracle that is used to generate a number sequence.

Which part of APEX is Gathering real-time comments, enhancement requests, and bugs from your application users?

A) Features B) Feedback C) Milestones D) Browser

In APEX use Application Builderto assemble an ---------- interface. A) HTML B) CSS C) SLT D) GML

A) HTML

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

ASCII

In APEX, --------------------------- - creates dynamic database driven web applications.

App Builder

The ---------------- is a low code, high productivity integrated development environment where you can develop applications on top of your data.

App Builder

APEX

ApplicationExpress

Which delimiter describes the Association Operator? A) || B) => C) ; D) ==

B) =>

APEX formerly called ............ A) XML DB B) HTML DB C) XSLT DB D) SGML DB

B) HTML DB

Which delimiter describes the Host Variable Indicator? A) <> B) = C) ; D) :

D) :

Which one is a user in APEX environment? A) Workspace administrators B) Developers C) End users D) All of the above

D) All of the Above

Name three types of Oracle data type.

DATE, VARCHAR2, NUMBER

________ is a set of commands used to control a database, including security.

DCL (GRANT, REVOKE)

Write a query and remove the HISTORY course from course table.

DELETE FROM student WHERE courseid = 4683 COMMIT;

Write a query and remove all the data from department table.

Delete from employee; COMMIT; Delete from employee; COMMIT;

The ---------------------------- control or modify how an application logs errors.

Error Handling

Foreign Key

Every value in the column must match a value in another column in this table or another table.

The ----------------------- can be written in the old syntax with using a UNION query.

FULL OUTER JOIN

A single schema can be associated with only one workspace one or more workspaces.

False

HTML stands for-------------------.

Hypertext Markup Language

IDE stands for --------------------.

Integrated Development Environment

The ------------------- reports enable end users to create highly customized reports.

Interactive

What is an Interactive Grid in Oracle APEX application?

It presents users a set of data in a searchable, customizable report.

A database is an organized collection of _______ related data.

Logically

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

MONTHS_BETWEEN

In APEX, ---------------tracks events associated with the development process.

Milestones

In ------------------------ will start the process from the inner aggregate function.

Nested Aggregate Function

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

Numeric

Which clause is used to sort the records in your result set?

ORDER BY

Aggregate functions can appear in select lists and in -----------, and ----------clauses.

ORDER BY, GROUP BY

"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)

Referential Integrity Constraints are generally established between:

Primary and Foreign keys

RAD:

Rapid Application Development

The Theme Roller window is displayed in the -------------------.

Runtime Toolbar

The ORDER BY and GROUP BY clauses can only be used in _________ statement.

SELECT

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.

SELECT

Write a SELECT statement: B) To list the information of students whose CIN is greater than or equal 1002 and is living in the area with zip code 91016.

SELECT * FROM student WHERE cin >= 1002, zip = 91016;

Write a query to return the average of the maximum salaries of all the departments. Make an alias for result and name it "avg_of_max _sal".

SELECT AVG(MAX(salary)) avg_of_max_sal FROM emp-susan GROUP BY depno;

Write a query to return the minimum and maximum salary of all employees.

SELECT MIN(salary), MAX(salary) FROM emp_susan;

Write a query: A) To return the minimum and maximum salary of all employees.

SELECT MIN(salary), MAX(salary) FROM employee;

Write a query to return the total amount of salary paid to all employees.

SELECT SUM(salary) FROM emp_susan;

Write a query: A)To return all student's names in uppercase.

SELECT UPPER (name) FROM student;

Write a query: To return all student's names in uppercase.

SELECT UPPER (name) FROM student;

Write a SELECT statement to list all students' CIN and names who takes either 3050 or 4580 courses. Also return the result of the query.

SELECT cin, name FROM student WHERE courseid IN (3050,4580);

Write a query to return the course ID and the total number of students in the associated course.

SELECT courseid, COUNT(*) FROM student GROUP BY courseid;

Write a SELECT statement: A) To list students' name and zip where name starts with 'S'.

SELECT name, zip FROM student WHERE name LIKE 'S%';

The --------------------- statement retrieves data from a table, and the ------------------ statement removes data from a table.

SELECT, DELETE

SELECT UPPER (name) from employee where salary=3000;

SNEHA MARY

A --------------is a logical grouping of tables, indexes, triggers, and other data objects under one qualifying name.

Schema

The ________ is the structure that contains descriptions of objects such as tables and views created by users.

Schema

SELECT name, gender FROM employee WHERE ASCII(SUBSTR(name, 1, 1)) = 83; NAME | GENDER --------------------------

Sunitha | Female Sunil | Male Sneha | Female

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

TERE 3 is starting position 4 is for length

The table name must begin with a letter.

TRUE

------------------ fetches the styles for your application and loads them in the editor.

Theme Roller

What is an Oracle trigger?

Triggers are stored programs, which are automatically executed or fired when some events occur.

A composite index is an index on multiple columns in a table.

True

A schema is a logical container for database objects like tables, views, and stored procedures.

True

APEX is a Database-centric Web Application development tool.

True

APEX is a browser based for development, deployment and runtime.

True

Oracle sequence is useful when we need to create a unique number to act as a primary key.

True

PL/SQL allows blocks of statements to be sent to Oracle in a single transmission.

True

Procedural Language is a programming language allows the actions of an end user to be converted into instructions that a computer can understand.

True

Red locked padlock — Indicates the page is locked by another user.

True

The ORDER BY clause can only be used in SELECT statement.

True

Triggers can be written to enforce referential integrity in the database.

True

Use Application Builderto assemble an HTML interface (or application) on top of database objects such as tables and procedures.

True

Use SQL Workshop to browse your database objects and run SQL queries.

True

We can create an index on one or more columns of a table.

True

We can create templates through APEX.

True

We can generate sequential numbers by using sequences.

True

EMPLOYEE (emp_id, emp_name, emp_address, emp_zip, emp_city, emp_state, dept_id), DEPARTMENT (dept_id, dept_name, dept_phone) 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; COMMIT;

EMPLOYEE (emp_id, emp_name, emp_address, emp_zip, emp_city, emp_state, dept_id), DEPARTMENT (dept_id, dept_name, dept_phone) Write a query and update the salary for employee by id equal 105 to $5000.

UPDATE employee SET emp_salary=5000 WHERE emp_id=105; COMMIT;

D) To change all 91107 zip codes to 91007 in student table.

UPDATE student SET zip = 91007 WHERE zip = 91107; COMMIT;

When creating triggers, ------------- provides a condition for rows for which the trigger would fire.

WHEN (condition)

RESTful Services helps you to create a RESTful --------------- declaratively.

WebService

Global Notification communicates system status to ------------------------.

application users

When designing applications, we can use -------------- to direct users to a specific page or URL, or to post or process information.

buttons

Which delimiter is the sign of NOT EQUAL? A) <> B) ^= C) Both of them D) None of them

c) Both of Them

A primary key that consists of more than one attribute is called a: A) foreign key B) multivalued key C) composite key D) cardinal key

c) Composite key

In PL/SQL programming, a ------------------ is a symbol with a special meaning.

delimiter

Write a command and remove the emp_trig trigger from the database.

drop trigger emp_trig;

Page Unlocked indicates the current page is unlocked and ---------------.

editable

-------------------- are handling errors in the PL/SQL programing.

exceptions

A ----------- is a window on the screen that provides areas for entering or modifying data in a database.

form

In an editable ----------------------, users can also add to, modify, and refresh the dataset directly on the page.

interactive grid

Enabling caching is an effective way to improve the------------------.

performance

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

pin

In APEX, a ----------- retrieves and displays data.

report

ROLLBACK

restore database to original since the last COMMIT.

A ------------- is an object in Oracle that is used to generate a number sequence.

sequence

We can create an autonumber field by using------------------.

sequences

SELECT LOWER(country) "value" FROM employee where id < 5;value --------

uk india uk india

Clear unlocked padlock — Indicates the page is----------------.

unlocked

In APEX, the area where we develop applications is called a ----------------------.

workspace

"select lower('WORLD') FROM DUAL;" will return --------------.

world

9) Which of the following is an inequality operator?

!=,^=,<> All of the above

The result for "SELECT POWER(6,3) FROMDUAL; " is ----------------- .

216

------------------------statement is used to add, modify, or drop/delete columns in a table.

ALTER TABLE

Write a query and remove column emp_city from employee table.

ALTER TABLE employee DROP column emp_city;

---------------- lists the column definitions.

Both of the above (Desc, Describe)

---------------------- statement is used to delete a single record or multiple records from a table.

DELETE

EMPLOYEE (emp_id, emp_name, emp_address, emp_zip, emp_city, emp_state, dept_id), DEPARTMENT (dept_id, dept_name, dept_phone) 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;

In an SQL statement which of the following parts states the conditions for row selection?

Where

What is the different kind of SQL statements?(Just Name and Abbreviations)

ata Query Language (DQL)Data Definition Language (DDL) Data Manipulation Language (DML)Data Control Language (DCL)Transaction Control Language (TCL)

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

greatest/ least

The following insert command would work fine: insert into budget values(121,222,111);

FALSE

The DROP command deletes rows from a table individually or in groups.

FALSE (DELETE)

DCL is used to update the database with new records.

FALSE (DML)

The DELETE TABLE DDL command is used to remove a table from the database

FALSE (DROP)

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

FALSE (Logically)

In databases, null values are equivalent to zero.

FALSE (not)

An attribute in a relation of a database that serves as the primary key of another relation in the same database is called a:

Foreign key

employee table

ID Name Dept Gender Country Salary 1 John, IT, Male, UK, 5000 2 Mary, HR, Female, India 3000 3 Todd IT Male UK 3500 4 Pam HR Female India 4000 5 Tatcher Payroll Male USA 2000 6 Sunil IT Male USA 1400 7 Hari Payroll Male UK 2500 8 Sunitha HR Female India 4000 9 Sneha IT Female India 3000 10 Ruby Payroll Male UK 4600

EMPLOYEE (emp_id, emp_name, emp_address, emp_zip, emp_city, emp_state, dept_id), DEPARTMENT (dept_id, dept_name, dept_phone) 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; or INSERT INTO employee (emp_id, emp_name, dept_id) values (279, 'Walter White', '234 Duarte'); COMMIT;

EMPLOYEE (emp_id, emp_name, emp_address, emp_zip, emp_city, emp_state, dept_id), DEPARTMENT (dept_id, dept_name, dept_phone) 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; or INSERT INTO Employee values (358, 'Jessie Pinkman', '378 Howard Ave', '9801', 'Albuquerque', 'NM', 987); COMMIT;

_______ are established between entities in a well-structured database so that the desired information can be retrieved.

Relationships

EMPLOYEE (emp_id, emp_name, emp_address, emp_zip, emp_city, emp_state, dept_id), DEPARTMENT (dept_id, dept_name, dept_phone) Write a SELECT statement to list all employees who lives either in zip code 91006, 91023, or 11420.

SELECT * FROM employee WHERE emp_zip IN (91006,91023,11420); or SELECT * FROM employee WHERE emp_zip=91006 or emp_zip=91023 or emp_zip=11420;

EMPLOYEE (emp_id, emp_name, emp_address, emp_zip, emp_city, emp_state, dept_id), DEPARTMENT (dept_id, dept_name, dept_phone) Write a SELECT statement to list the cities which zip codes include 10 in it.

SELECT emp_city FROM employee WHERE emp_zip LIKE '%10%';

EMPLOYEE (emp_id, emp_name, emp_address, emp_zip, emp_city, emp_state, dept_id), DEPARTMENT (dept_id, dept_name, dept_phone) 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 ';

SELECT CONCAT(CONCAT(CONCAT(name, ' is from '),country), '. ') "phrase" FROM employee WHERE gender= 'Female';phrase

Sunitha is from India. Sneha is from India . Mary is from India . Pam is from India .

The SQL command used to populate tables with data is the INSERT command

TRUE

All aggregate functions group data to ultimately produce a single result value.

TRUE

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

DISTINCT

INSERT INTO is an example of ________ code.

DML

What is the different kind of SQL statements? Name, Abbreviations, and one example for each

Data Definition Language (DDL, ALTER), Data Manipulation Language (INSERT, DML), Data Query Language (DQL, SELECT), Data Control Language (DCL, REVOKE), Transaction Control Statement (TCL, COMMIT).

Aggregate functions can be nested.

False (can)

When creating a new sequence, the INCREMENT BY clause specify the interval between sequence numbers. This integer value can be any positive or negative integer, or zero.

False (cannot be zero)

The DISTINCT clause ignores NULL values.

False (doesn't)

Write a query to return the following result. Sandra takes 3050.

SELECT CONCAT(CONCAT(CONCAT(name, 'takes'),courseid),'.') 'phrase' FROM student; WHERE courseid = 3050, name = 'Sandra';

EMPLOYEE (emp_id, emp_name, emp_address, emp_zip, emp_city, emp_state, dept_id), DEPARTMENT (dept_id, dept_name, dept_phone) Write a query to find out the number of records in employee table.

SELECT COUNT(*), FROM Employee;

Write a query to return the total number of departments and the total number of distinct of zip codes.

SELECT COUNT(DISTINCT(depno)), COUNT(zip) FROM dept_susan;

A database table is defined using the data definition language (DDL)

TRUE

The schema is a logical grouping of tables, indexes, triggers, and other data objects under one qualifying name, and SQL is a standard language for database access.

TRUE

Redo Undo reverts the previous update you made within Page Designer.

False

When creating a PL/SQL block, executable commands is enclosed between the keywords declare BEGIN and END.

False

Workspace Oracle Application Express administrators manage an entire hosted instance using the APEX Services application

False

Header Declaration is used to identify the type of the code block and its name.

False (Header)

Web and email services are not part of APEX.

False (are)

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

False (or multiple tables)

Columns are the basic unit of storage in an Oracle Database

False (tables)

Which lock option indicates the page is locked by you. A) Green locked padlock B) Red locked padlock C) Clear locked padlock D) None of the above

A) Green Locked paglock

Which option in APEX enables you to view object properties and create new objects? A) Object Browser B) App Builder C) Utilities D) None of the above

A) Object Browser

Which is not true about APEX? A) Only one user can access APEX workspace using a Web browser. B) It enables a single database to become a shared workgroup database service. C) It is consuming minimal CPU resources. D) It does not use a dedicated database connection.

A) Only one user can access APEX workspace using a web browser.

Which delimiter is the concatenation operator? A) || B) => C) ; D) ==

A) | |

---------- returns the absolute value of

ABS

Make the corrections in the following queries. Alter table order drop ord_no;

ALTER TABLE order DROP COLUMN ord_no;

Write a query: C) To add a new column to keep the students' addresses in student table.

ALTER TABLE student ADD student_addresss varchar2(30);

Which one is not an option in left pane in page designer? A) Dynamic Actions B) Help C) Processing D) Shared Components

B) Help

select chr(61) from dual;

C - =

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

C) <>

Composite index, also called ---------------. A) Complex index B) Filtered index C) Concatenated index D) Clustered index

C) Concatenated Index

Which one is not a valid table name in an Oracle database?

Emp-Billing

The only PL/SQL code blocks keyword that is followed by a semi-colon is the ------- keyword.

End

Primary Key

Every value must be unique and cannot be null.

DELETE command removes all records from a table, including all spaces allocated for the records are removed.

FALSE

The DISTINCT clause can only be used with SELECT and UPDATE statements.

FALSE

The CREATE SCHEMA DDL command is used to create a table.

FALSE (TABLE)

CREATE INDEX emp_id_indx ON emp_id(employee) employee(emp_id);

False, SYNTAX: CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME)

In APEX after the database processes the PL/SQL, the results are relayed back to your browser as ----------------------.

HTML

HTMLDB

Hypertext Markup Language DataBase

Return the result of the following queries: C) SELECT REPLACE('Back','B','J') FROM DUAL;

Jack

Charts are based on Oracle --------------------------- Data Visualizations.c

JavaScript Extension

Which one is a valid table name in an Oracle database?

Orders

Developers view and manage application in ----------------------.

Page Designer

We use -------------------------------- to set the attribute and the properties of pages in our application.

Page Designer

The -------------------------displays at the top of the page and contains both buttons and menu options.

Page Designer toolbar

DDL is typically used during which phase of the development process?

Physical Design

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

Pin

An attribute (or attributes) that uniquely identifies each row in a relation is a --------------.

Primary Key

Answer the following questions. A) Name two types of constraints

Primary and Foreign keys

PL/SQL stands for ----------------------------------------------.

Procedural Language/Structured Query Language

In APEX, install and run a ------------------------- from the Packaged Apps gallery.

Productivity application

Write a query to return the average of the maximum salaries of all the departments. Make an alias for result and name it "avgmax _sal".

SELECT AVG(MAX(salary)) avgmax_sal FROM employee GROUP BY depno;

By using Oracle join, write a query to list every employee number with their corresponding department number and name as well as total amount of employee's bonus sorted by the department number.

SELECT emp_susan.empno, dept_susan.depno, depname, sum(empbonus) FROM emp_susan JOIN dept_susan ON dept_susan.depno=emp_susan.depno JOIN bonus_susan ON emp_susan.empno=bonus_susan.empno Group by emp_susan.empno, dept_susan.depno, depname order by dept_susan.depno;

A database table is defined using the data definition language (DDL).

TRUE (CREATE, DROP, ALTER, TRUNCATE, COMMENT, RENAME)

--------------- removes all records from a table, including all spaces allocated for the records are removed.

TRUNCATE

-------------------- is the basic unit of data storage in an Oracle Database.

Table

All aggregate functions group data to ultimately produce a single result value.

True

Application Builder is the tool you use to build the pages that comprise an application.

True

Developers can have their own workspace or share a workspace.

True

Executables are mandatory sections in PL/SQL.

True

In nested aggregate functions will start the process from the inner aggregate function.

True

The DISTINCT clause can only be used with SELECT statements

True

Use the Property Editor to update -------------- for the selected component.

attributes

Join is a relational operation that causes two or more tables with a ---------------to be combined into a single table or view.

common domain

The ---------------- of a PL/SQL code block starts with keyword Begin and is terminated with the keyword End.

executable portion

Triggers are stored programs, which are automatically ------------------- when some events occur.

executed or fired

Use the small trianglelabeled Collapse in the center of each splitter in page designer to -----------or -------------------- each pane.

expand, collapse

Oracle JOINS are used to return data from ------------------- tables.

multiple

Which of the following statements is true? A) Oracle considers PL/SQL to be a procedural language extension of SQL. B) Oracle does not consider PL/SQL to be a procedural language. C) Oracle considers SQL to be a procedural language. D) Oracle does not consider SQL to be a programming language.

(A) Oracle considers PL/SQL to be a procedural language extension of SQL

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

30

What is a report in Oracle APEX application?

A report retrieves and displays data.

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

Alphanumeric

The SQL command ________ adds one or more new columns to a table.

Alter

Alter table dept add column dep_phone number(5);

Alter table dept add dep_phone number(10); or Varchar2(15) 5 IS NOT LONG ENOUGH TO HOLD PHONE NUMBERS

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

American Standard Code for Information Interchange

--------------------- includes a number of wizards you can use to create formsautomatically.

Application Builder

APEX stands for ---------------------.

Application Express

Which part of APEX contains installable Productivity Applications? A) SQL Workshop B) APP Gallery C) Object Browser D) Application Store

B) APP Gallery

Create dynamic database driven web applications by using APEX ............... A) Application service B) Application builder C) Automation D) SQL builder

B) Application Builder

Which one is not an advantage of APEX? A) easy B) Secure C) High level programming experience D) Rapid and empowering

C) High level programming experience

APEX is ............... framework. A) Agile B) Spiral C) RAD D) Waterfall

C) RAD

APEX is a ------------- framework. A) XP B) Agile C) RAD D) Spiral

C) RAD

In an SQL statement, ------------- clause states the conditions for row selection and -------------- clause is used to sort the records in your result set. A) Select, sort by B) From, order by C) Where, order by D) Group By, sort by

C) Where, Order By

After performing a DELETE operation you need to ------------ or -------------- the transaction to make the change permanent or to undo it.

COMMIT, ROLLBACK

The ---------------- function returns the number of rows, and -------------- lists the column definitions.

COUNT, DESC

Write a query and create an index on column city in city_state table.

CREATE INDEX ind_city_susan ON city_state_susan(city);

Create a composite index on columns job and salary in employee table.

CREATE INDEX ind_emp_susan ON emp_susan(job, salary);

1) Create a sequence and a trigger to fire before insert into department table to make the depno to autonumber field.

CREATE SEQUENCE depno_seq START WITH 80 INCREMENT BY 10; create or replace trigger trig_dep_no BEFORE INSERT ON dept FOR EACH ROW Begin select depno_seq.nextval into :new.depno from dual; end; /

---------------- 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

How to make changes permanent after manipulating a table?

Commit

A primary key that consists of more than one attribute is called a:

Composite Key

The || delimiter is the ----------------------.

Concatenation operator

Any create command may be reversed by using a(n) ________ command.

Drop

In Apex the area where you develop applications is called a schema workspace.

False (just 'Workspace)

Apex is consuming lots of minimal CPU resources.

False (minimal)

The COUNT(DISTINCT expression) function returns the number of unique and null items in a group.

False (non-null)

JET stands for -------------------------------

JavaScript Extension

The -------------- conditions specify an involving pattern matching.

LIKE

select MONTHS_BETWEEN ('02/10/2020', '04/10/2019' ) as mon_num from dual;

MON_NUM ---------------- 10

Oracle APEX is ------------------------------ framework for the Oracle database.

RAD

------------- withdraws access privileges given with the GRANT command.

REVOKE

Define the following concepts: ROLLBACK: Constraint: INSERT ALL: ORDER BY:

ROLLBACK - To restore a database to its original last commit. Constraint - Allows database designer to enforce business rules on how the data is stored in the database as well as the relationships between tables. Insert all - Used to add multiple rows in one table at a time Order by - used to sort records in result set

Write a query: B) To return the average of employees' salaries based on each department, also round the result by 3 digits to the right of decimal point.

SELECT depno, ROUND(AVG(salary),3) FROM employee GROUP BY depno;

Write a query to return the total amount of salary paid in each department to all employees.

SELECT depno, SUM(salary) FROM emp_susan GROUP BY depno;

Write a query to return the department's numbers, employee's job, and the avg of salaries in each department, also sort the result by department numbers.

SELECT depno,job, AVG(salary) FROM emp_susan GROUP BY depno,job ORDER BY depno;

SELECT dept.depno, depname, avg(empbonus) FROM dept, bonus WHERE dept.depno=bonus.depno group by dept.depno,depname;

SELECT dept.depno, depname, avg(empbonus) FROM deptjoin bonus on dept.depno=bonus.depno group by dept.depno,depname;

EMPLOYEE (emp_id, emp_name, emp_address, emp_zip, emp_city, emp_state, dept_id), DEPARTMENT (dept_id, dept_name, dept_phone) Write a SELECT statement to list the address of employees that do not have the department ID equals 10.

SELECT emp_address FROM employee WHERE dept_id <> 10;

1) The ORDER BY and GROUP BY clauses can only be used in select statement.

TRUE

SQL is a standard language for database access

TRUE

A --------------- is a logical container for database objects.

schema

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

INSET ALL

What does the following SQL command do? insert into student values (1002,'John Smith','231 West St','Boston','MA','02115');

Adds a new record to the student table

EMPLOYEE (emp_id, emp_name, emp_address, emp_zip, emp_city, emp_state, dept_id), DEPARTMENT (dept_id, dept_name, dept_phone) Write a query and add a new column to department table, and name it dept_address.

Alter table department add dept_address varchar2(30);

When changing application properties, the ---------------------- shows how to change a logo in page.

Application Icon

A ________ defines or constrains some aspect of the business.

Business Rule

Which one is an APEX feature? A) Reports B) Charts C) Templates D) All of the above

D) All of the above

Write a query: A) To return number of employees who works in departments 30 or 50 and earns more than $45500.

SELECT COUNT (*) FROM employees WHERE deparments IN (30,50) AND salary > 45500;

Substitution defines ------------------ substitution strings for your application.

static

EMPLOYEE (emp_id, emp_name, emp_address, emp_zip, emp_city, emp_state, dept_id), DEPARTMENT (dept_id, dept_name, dept_phone) Write a query to see all records of department table.

SELECT * FROM department;

EMPLOYEE (emp_id, emp_name, emp_address, emp_zip, emp_city, emp_state, dept_id), DEPARTMENT (dept_id, dept_name, dept_phone) Write a SELECT statement to list the information of employees whose id is less than or equal 150.

SELECT *FROM employee WHERE emp_id <= 150;

Any create command may be reversed by using a ---------- command.

DROP

RDBMS stands for Related Data Build Management System

FALSE (Relational Database Management System)

Make the correction in the following query. SELECT depname, AVG(salary) FROM emp_susan WHERE emp_susan.depno=30 JOIN dept_susan ON dept_susan.depno=emp_susan.depno GROUP BY depno;

SELECT depname, avg(salary) FROM emp_susan JOIN dept_susan ON dept_susan.depno=emp_susan.depno WHERE emp_susan.depno=30 GROUP BY depname;

Write a query to return the average of employees' salaries based on each department, also round the result by 2 digits to the right of decimal point.

SELECT depno, ROUND(AVG(salary),2) FROM emp_susan GROUP BY depno;

Write a query: B) To list employees' name and job where name ends with 'a'.

SELECT emp_name, emp_job FROM employee WHERE emp_name LIKE '%a';

End users create and edit applications have no development privileges/Cannot create or edit.

False

APEX is able to build professional applications which are fast but not secure.

False

APEX needs does not need dedicated database connection.

False

Business Rules

A statement that defines or constrains some aspect of the business. It controls the behavior of business.

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

American National Standards Institute

16) Abbreviations: ANSI: ASCII: RDBMS: SQL:

American National Standards Institute, American Standard Code for Information Interchange Relational Database Management System Structured Query Language

Triggers are stored programs, which are automatically executed or fired when some events like ---------- and ------- occur. A) DCL, DDL B) DML,DDL C) DCL,TCL D) TCL,DDL

B) DML, DDL

Which one is an option in central pane in page designer? A) Layout B) Help C) None of the D) Both A and B

D) Both A and B (Layout, Help)

------------ is a set of commands used to control a database, and -------- is typically used during physical design in the development process. A) DML, DDL B) DDL, DML C) DDL, DCL D) DCL, DDL

D) DCL, DDL

We can create an autonumber field by using ---------------. A) Primary key B) Index C) Function D) Sequences

D) Sequences

________ is a set of commands used to update a database.

DML (INSERT, UPDATE, DELETE)

Any create command may be reversed by using a ---------- command. A) Delete B) Drop C) Truncate D) All of the above

DROP

Composite indexes cannot speed retrieval of data for SELECT statements in which the WHERE clause references all or the leading portion of the columns in the composite index.

False

Table name can contain more than one word.

FALSE (cannot)

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

I Am A Senior Student

Make the corrections in the following queries. insert employee (empno, emp name, depno) value (100, Jack , 10);

INSERT employee (empno, empname, depno) VALUES (100, 'Jack', 10); COMMIT;

Which of the following statement retrieves data from a table?

SELECT

Write a query: B) To return the first two letters of the courses' names.

SELECT coursename, SUBSTR(name,1,2)'Substring' FROM course;

Write a query to return the department number and the total number of employees (in the associated department) whose makes less than $55,000.

SELECT depno, COUNT (*) FROM emp_susan WHERE salary <55000 GROUP BY depno;

Write a query to return all department's information plus the corresponding state. SELECT dept_susan.depno, dept_susan.depname, city_state_susan.city, dept_susan.zip, city_state_susan.state FROM dept_susan LEFT JOIN city_state_susan ON dept_susan.zip = city_state_susan.zip; Here we should use LEFT JOIN, because the we want to have all the departments information even if the city and state are blank. 70 Delivary (has no city and state) but it is one of the department and we want to have in our list.

SELECT dept_susan.depno, dept_susan.depname, dept_susan.city, dept_susan.zip, city_state.state with SELECT dept_susan.*, city_state_susan.state Use * when you want you select all the columns from a table.

EMPLOYEE (emp_id, emp_name, emp_address, emp_zip, emp_city, emp_state, dept_id), DEPARTMENT (dept_id, dept_name, dept_phone) 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%"

EMPLOYEE (emp_id, emp_name, emp_address, emp_zip, emp_city, emp_state, dept_id), DEPARTMENT (dept_id, dept_name, dept_phone) Write a SELECT statement to list employee name and address where name ends with 'D'.

SELECT emp_name, emp_address FROM employee WHERE emp_name LIKE "%D%";

Write a query to find the employee's name and address and sort the result based on zip codes.

SELECT emp_name, emp_address FROM employee order by emp_zip;

EMPLOYEE (emp_id, emp_name, emp_address, emp_zip, emp_city, emp_state, dept_id), DEPARTMENT (dept_id, dept_name, dept_phone) 1. Write a SELECT statement to list zip codes and their cities in New York.

SELECT emp_zip, emp_city FROM employee WHERE emp_state = "NY";

Constraint

Allows the database designer to enforce business rulesabout the data stored in the database's tables and the relationships between tables.

Alter table dept drop dep_phone;

Alter table dept drop column dep_phone

What does the following SQL statement do? Alter Table employee Add Type (varchar2 (10));

Alters employee table, and adds a field called "Type"