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"
