database final

Question 1

Create a table with column constraints, two attributes; vendor_id (number, is primary key) and vendor_name (varchar2(50) and not null, is unique):
CREATE [blank_start]TABLE[blank_end] vendors
(
[blank_start]vendor_id NUMBER PRIMARY KEY[blank_end],
[blank_start]vendor_name VARCHAR2(50) NOT NULL UNIQUE[blank_end] 
)

Accepted Answer

TABLE

Accepted Answer

vendor_id NUMBER PRIMARY KEY

Accepted Answer

vendor_name VARCHAR2(50) NOT NULL UNIQUE

Question 2

vendor_id NUMBER PRIMARY KEY - [blank_start]unnamed constraint[blank_end]

vendor_id NUMBER CONSTRAINT vendors_pk PRIMARY KEY - [blank_start]named constraint[blank_end]

Accepted Answer

unnamed constraint

Accepted Answer

named constraint

Answer

constraint

Question 3

composite primary key constraints are written as:
CONSTRAINT value PRIMARY KEY (value1, value2)

Answer

true

Question 4

foreign key constraint is also known as reference constraint

Answer

true

Question 5

create table with foreign key constraint for vendors table (vendor_id value): 
CREATE TABLE invoices(
invoice_id NUMBER PRIMARY KEY,
vendor_id NUMBER [blank_start]REFERENCES vendors[blank_end] ([blank_start]vendor_id[blank_end]), 
invoice_number VARCHAR2(50) NOT NULL UNIQUE
);

Accepted Answer

REFERENCES vendors

Accepted Answer

vendor_id

Question 6

table level foreign key constraints are written as:

CONSTRAINT named_foreign_key FOREIGN KEY (value) REFERENCES table (value)

Answer

true

Question 7

Check constraints are used to restrict values, add two checks to the table, check if invoice_total is >= 0 and payment_total is >= 0

CREATE TABLE invoices(
invoice_id NUMBER PRIMARY KEY, 
invoice_total NUMBER(9,2) NOT NULL
               [blank_start]CHECK[blank_end] ([blank_start]invoice_total >= 0[blank_end]), 
payment_total NUMBER(9,2) DEFAULT 0
               CHECK ([blank_start]payment_total >= 0[blank_end])
)

Accepted Answer

CHECK

Accepted Answer

invoice_total >= 0

Accepted Answer

payment_total >= 0

Question 8

What are the following commands:

Rename a table: [blank_start]rename[blank_end] vendors to vendor
delete all data from a table: [blank_start]truncate[blank_end] table vendor
delete a table from the database: [blank_start]drop[blank_end] table vendor
check table structure: [blank_start]desc[blank_end] vendor

Accepted Answer

rename

Accepted Answer

truncate

Accepted Answer

drop

Accepted Answer

desc

Question 9

Insert command is as follows:

INSERT INTO members (value1, value2, value3)
VALUES (‘value1', 'value2', 'value3');

Answer

true

Question 10

Update table options to change ocost to 115.00 and olist to 200.00 where the ocode is 'CD2'

UPDATE options
[blank_start]SET ocost[blank_end] = 115.00,
[blank_start]olist[blank_end] = 200.00 [blank_start]WHERE ocode[blank_end] = ‘[blank_start]CD2[blank_end]’;
(1 rows updated)

Accepted Answer

SET ocost

Accepted Answer

olist

Accepted Answer

WHERE ocode

Accepted Answer

CD2

Question 11

delete command:

DELETE FROM table WHERE value = ‘value’;

Answer

true

Question 12

What command makes your changes in the database permanent:

Accepted Answer

commit

Answer

save

Answer

process

Answer

union

Question 13

what command will revert the changes you have made?

Accepted Answer

rollback

Answer

undo

Answer

revert

Answer

cancel

Question 14

SELECT • List the columns (and expressions) that should be returned from the query 
FROM • Indicate the table(s) or view(s) from which data will be obtained 
[blank_start]WHERE[blank_end] • Indicate the conditions under which a row will be included in the result 
[blank_start]GROUP BY[blank_end] • Indicate categorization of results 
[blank_start]HAVING[blank_end] • Indicate the conditions under which a category (group) will be included 
[blank_start]ORDER BY[blank_end] • Sorts the result according to specified criteria

Accepted Answer

WHERE

Accepted Answer

GROUP BY

Accepted Answer

HAVING

Accepted Answer

ORDER BY

Question 15

select all values from hr.employees where the depeartment_id is 100 and salary is greater than 7000

SELECT *
[blank_start]FROM[blank_end]  hr.employees
[blank_start]WHERE[blank_end] department_id = [blank_start]100 AND salary >= 7000[blank_end];

Accepted Answer

FROM

Accepted Answer

WHERE

Accepted Answer

100 AND salary >= 7000

Question 16

write a select command that shows all values from hr.employees where the salary is between 15000 and 20000

SELECT *
    FROM hr.employees
  [blank_start]WHERE salary BETWEEN[blank_end] 15000 AND 20000;

Accepted Answer

WHERE salary BETWEEN

Question 17

When using the LIKE operator, % matches and character and _ matches and single character

Answer

true

Question 18

select all values from hr.employees where first_name starts with Alex

SELECT *
FROM hr.employees
      [blank_start]WHERE first_name LIKE 'Alex%'[blank_end];

Accepted Answer

WHERE first_name LIKE 'Alex%'

Question 19

select all values from hr.employees where last_name is ols something n

SELECT *
       FROM hr.employees
      [blank_start]WHERE last_name LIKE 'Ols_n'[blank_end];

Accepted Answer

WHERE last_name LIKE 'Ols_n'

Question 20

select all from hr.locations where country_id is 'US', or 'CA'

SELECT *
FROM hr.locations
[blank_start]WHERE country_id IN[blank_end] ('US', 'CA');

Accepted Answer

WHERE country_id IN

Question 21

select department_id and department_name from hr.departments where the manager_id is null

SELECT department_id, department_name FROM hr.departments
WHERE [blank_start]manager_id IS NULL[blank_end];

Accepted Answer

manager_id IS NULL

Question 22

select first_name, last_name and years they have been hired for AS years from hr.employees (use SYSDATE and hire_date)

SELECT first_name, last_name,
[blank_start]ROUND((SYSDATE - hire_date)/365)[blank_end] AS years
FROM hr.employees;

Accepted Answer

ROUND((SYSDATE - hire_date)/365)

Question 23

logical conditions:

[blank_start]AND[blank_end] - return true if both conditions are true
[blank_start]OR[blank_end] - return true if either condition is true
[blank_start]NOT[blank_end] - return true if condition is false

Accepted Answer

AND

Accepted Answer

OR

Accepted Answer

NOT

Question 24

select only unique salary from hr.employees

SELECT [blank_start]DISTINCT[blank_end] salary FROM hr.employees;

Accepted Answer

DISTINCT

Question 25

Subqueries: A subquery is a SELECT statement that is coded inside another SQL statement

Answer

true

Question 26

create a subquery that selects all employee_id from hr.job_history where the end_date is past 2007

SELECT *
   FROM hr.employees
WHERE employee_id [blank_start]IN[blank_end] ([blank_start]SELECT employee_idFROM[blank_end] hr.job_history
[blank_start]WHERE end_date[blank_end] = '2007-12-31');

Accepted Answer

IN

Accepted Answer

WHERE end_date

Accepted Answer

SELECT employee_id FROM

Question 27

ORDER BY clause can be used inside subquery

Answer

true

Question 28

select all cname from saleinv where net is the lowest (min) value of that table

SELECT cname FROM saleinv
[blank_start]WHERE net =[blank_end] (SELECT [blank_start]MIN(net)[blank_end] FROM saleinv);

Accepted Answer

WHERE net =

Accepted Answer

MIN(net)

Question 29

use DISTINCT in subquerys when multiple of the same value can appear, for the sake of efficiency

Answer

true

Question 30

select all cname and chphone from customer where cname is In prospect (no duplicate values)

SELECT cname, chphone FROM customer
[blank_start]WHERE cname IN[blank_end] (SELECT [blank_start]DISTINCT[blank_end] cname FROM prospect);

Accepted Answer

WHERE cname IN

Accepted Answer

DISTINCT

Question 31

JOIN is much more efficient than subquery

Answer

true

Question 32

Inner Joins: A join creates combinations from two or more tables in a single result set

Answer

true

Question 33

select all first_name, last_name and job_title from hr.employees and also display the job_id of each job_title

SELECT first_name, last_name, job_title
    FROM hr.employees [blank_start]JOIN[blank_end] hr.jobs
             [blank_start]ON employees.job_id =  jobs.job_id[blank_end];

Accepted Answer

JOIN

Accepted Answer

ON employees.job_id = jobs.job_id

Question 34

Explicit join uses JOIN
implicit join uses WHERE

Answer

true

Question 35

select first_name, last_name and job_title from hr.employees and hr.jobs where the job_id is equal to job_title id

SELECT first_name, last_name, job_title
FROM hr.employees ,hr.jobs
[blank_start]WHERE employees.job_id[blank_end] = [blank_start]jobs.job_id[blank_end];

Accepted Answer

WHERE employees.job_id

Accepted Answer

jobs.job_id

Question 36

Explicit join example: (where at end)

SELECT DISTINCT department_name, city, state_province, country_name
FROM hr.departments d
JOIN hr.locations l ON d.location_id = l.location_id 
JOIN hr.countries c ON l.country_id = c.country_id 
JOIN hr.regions r ON c.region_id = r.region_id
   WHERE region_name = 'Europe';

Answer

true

Question 37

inexplicit join example (where before joins)

SELECT DISTINCT department_name, city, state_province, country_name
     FROM hr.departments d, hr.locations l, hr.countries c, hr.regions r
   WHERE d.location_id = l.location_id
       AND l.country_id = c.country_id
       AND c.region_id = r.region_id
       AND region_name = 'Europe';

Answer

true

Question 38

Get max salary from hr.employees:

SELECT [blank_start]MAX(salary)[blank_end]
     FROM hr.employees;

Accepted Answer

MAX(salary)

Question 39

Get count # of entry for hr.employees

SELECT [blank_start]COUNT(*)[blank_end]
     FROM hr.employees;

Accepted Answer

COUNT(*)

Question 40

Calculate and display the number of business locations in ‘CA’, ‘US’, ‘MX’ and ‘UK’

SELECT c.country_id, count(*)
FROM hr.locations l [blank_start]JOIN[blank_end] hr.countries c
[blank_start]ON[blank_end] l.country_id = c.country_id
[blank_start]WHERE[blank_end] c.country_id [blank_start]IN[blank_end] ('CA', 'US', 'MX', 'UK') 
GROUP BY c.country_id
ORDER BY count(*);

Accepted Answer

IN

Accepted Answer

WHERE

Accepted Answer

JOIN

Accepted Answer

ON

Question 41

A SELECT clause that contains an aggregate function can contain only aggregate functions unless:

Accepted Answer

The column specification results in a literal

Accepted Answer

GROUP BY clause contains all selected columns

Answer

JOINING 2 tables in a single statement

Question 42

HAVING clause determines which groups are included in the final results
– It is similar to WHERE clause, but it is used for searching after the grouping results

Answer

true

Question 43

Display the department_id, department_name, the number of employees and the average salary for all of the departments that have more than 10 employees. Order them by department_name.

SELECT d.department_id, department_name, COUNT(*)
AS "No. of emp",
            ROUND(AVG(salary),2) AS "Average Salary"
    FROM hr.departments d JOIN hr.employees e
ON d.department_id = e.department_id GROUP BY d.department_id, department_name
  [blank_start]HAVING COUNT(*)[blank_end] > 10
   [blank_start]ORDER BY department_name[blank_end];

Accepted Answer

ORDER BY department_name

Accepted Answer

HAVING COUNT(*)

Question 44

display make of a car with 5 or more records in the car table

SELECT make, COUNT(*) FROM s9.car
[blank_start]GROUP BY make HAVING COUNT(*)[blank_end]>=5;

Accepted Answer

GROUP BY make HAVING COUNT(*)

Question 45

You can only use HAVING with the GROUP BY clause.

Answer

true

Question 46

Benefits of Using Views

Accepted Answer

Design Independence

Accepted Answer

Data Security

Accepted Answer

Simplified Queries

Accepted Answer

Updatability

Answer

Efficiency

Answer

Easy to scale

Question 47

Create a view in your own schema that brings together the data from countries and regions of ‘hr’ tables:

[blank_start]CREATE OR REPLACE VIEW[blank_end] countries_ids_regions AS
SELECT country_id, country_name, region_name, r.region_id
FROM hr.countries c JOIN hr.regions r ON c.region_id = r.region_id;

Accepted Answer

CREATE OR REPLACE VIEW

Question 48

Create a VIEW that keeps track of frequent car buyers. A frequent car buyer is considered someone that has bought more than 1 car. Show the number of cars and the total amount they’ve spent.

[blank_start]CREATE OR REPLACE VIEW[blank_end] frequent_customer AS SELECT cname,
[blank_start]COUNT[blank_end](*) AS num_of_cars, [blank_start]SUM[blank_end](net) AS cars_total_sum
FROM s9.saleinv GROUP BY cname
[blank_start]HAVING COUNT[blank_end](*) > 1;

Accepted Answer

CREATE OR REPLACE VIEW

Accepted Answer

SUM

Accepted Answer

COUNT

Accepted Answer

HAVING COUNT

Question 49

Oracle allows cases when you can create VIEWs before crating tables
– In that case you need to use FORCE keyword – Example:
CREATE FORCE VIEW products_list AS
SELECT product_description, product_price
FROM products;

Answer

true

Question 50

Substitution variable          [blank_start]starts with p_[blank_end]             [blank_start]& in front[blank_end]
Bind variable             [blank_start]starts with g_[blank_end]              [blank_start]: in front[blank_end]
PL variable             starts with v_

Accepted Answer

starts with p_

Accepted Answer

& in front

Accepted Answer

: in front

Accepted Answer

starts with g_

Question 51

Subsititution variable:

[blank_start]ACCEPT[blank_end] p_name [blank_start]PROMPT[blank_end] 'Enter Name: ' SELECT *
FROM customer
WHERE cname = '[blank_start]&p_name[blank_end]';

Accepted Answer

PROMPT

Accepted Answer

ACCEPT

Accepted Answer

&p_name

Question 52

PL block example:
BEGIN
-- do something here;
END; /

Answer

true

Question 53

-- declare an SQLplus bind variable 
VARIABLE g_name VARCHAR2(32000) 
BEGIN
-- assign value of substitution variable to the bind variable
:g_name := '&p_name'; 
END;
/
PRINT g_name

Notes on the script:
• VARIABLE - bind variables:
– must be declared before they can be used
– cannot be declared inside a block
– A variable type and size must be given

• :g_name - The colon:
– must be used in front of bind variables inside a block
– is used for scope resolution. It tells PL to look outside the block and in the SqlPlus environment to find the variable.

• p_name - Since the substitution variable does not yet exist, you will be automatically prompted to enter a value for p_name.

• / - Execute the block

• PRINT - Print the value of the bind variable.

Only bind variable can be printed with the PRINT command.
PRINT p_name is illegal.

Answer

true

Question 54

Simple script example:

ACCEPT p_name PROMPT 'Enter Customer Name: ' 
[blank_start]VARIABLE[blank_end] g_output VARCHAR2(200)
DECLARE
v_street VARCHAR2(30); 
v_city VARCHAR2(20); 
v_prov VARCHAR2(20); 
v_postal VARCHAR2(10);
BEGIN
SELECT cstreet, ccity, cprov, cpostal
INTO [blank_start]v_street[blank_end],[blank_start]v_city[blank_end],[blank_start]v_prov[blank_end],[blank_start]v_postal[blank_end]
FROM customer
WHERE cname = '&[blank_start]p_name[blank_end]';
:g_output := '&p_name' || ' ' ||v_street || ' ' || v_city;
:g_output := :g_output || ' ' || v_prov || ' ' || v_postal; 
[blank_start]END[blank_end];
[blank_start]/[blank_end]
PRINT g_output

Accepted Answer

p_name

Accepted Answer

VARIABLE

Accepted Answer

v_street

Accepted Answer

v_city

Accepted Answer

v_prov

Accepted Answer

v_postal

Accepted Answer

END

Accepted Answer

/

Question 55

Get data type of field:

DECLARE
v_street customer.cstreet%TYPE

Answer

true

Question 56

Get data type of field:

DECLARE
v_customer customer%ROWTYPE;

Answer

true

Question 57

[blank_start]NO_DATA_FOUND[blank_end]
– no data can be found to be put into a variable in a SELECT - INTO statement.
• [blank_start]TOO_MANY_ROWS[blank_end]
– too may rows have been found for a SELECT - INTO statement. Variables can only hold one value.
• [blank_start]ZERO_DIVIDE[blank_end]
– a division by zero was attempted.
• [blank_start]VALUE_ERROR[blank_end]
– a data size or type conversion was incorrect. E.g. name CHAR(3) := 'ABCDE';

Accepted Answer

NO_DATA_FOUND

Accepted Answer

TOO_MANY_ROWS

Accepted Answer

ZERO_DIVIDE

Accepted Answer

VALUE_ERROR

Question 58

Example of NO_DATA_FOUND:

[blank_start]EXCEPTION[blank_end]
[blank_start]WHEN NO_DATA_FOUND THEN[blank_end]
:g_output := '&p_name' || ' not found';

Accepted Answer

EXCEPTION

Accepted Answer

WHEN NO_DATA_FOUND THEN

Question 59

User define exception:

ACCEPT p_name PROMPT 'Enter Customer Name: '
VARIABLE g_output VARCHAR2(200)
DECLARE
v_count NUMBER(6);
v_toomany EXCEPTION;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM s9.prospect
WHERE cname = '&p_name';
IF v_count > 5 THEN RAISE v_toomany;
END IF;
:g_output := '&p_name' || ' ' || ' wants ' || v_count | ' cars';
EXCEPTION
WHEN v_toomany THEN
:g_output := '&p_name' || ' wants too many cars';
END;
/
PRINT g_output

Answer

true

Question 60

In databases, a cursor is a temporary table.

Answer

true

Question 61

Cursor is created as so:
CURSOR value IS
SELECT statement...

Answer

true

Question 62

Cursor for loop:

FOR value IN value LOOP
output...
END LOOP;

Answer

true

Question 63

Procedure example:

[blank_start]CREATE OR REPLACE PROCEDURE[blank_end] AddServinv
(pl_cname servinv.cname%TYPE
) [blank_start]AS BEGIN[blank_end]
INSERT INTO servinv (cname)
VALUES (pl_cname);
COMMIT;
END AddServinv; 
/

Accepted Answer

CREATE OR REPLACE PROCEDURE

Accepted Answer

AS BEGIN

Question 64

Some rules with procedures:

Parameters are always placed in brackets following the procedure name.
The keyword AS is required (IS may be used as a synonym for AS).
Any variables that need to be declared are placed between AS and BEGIN (without the DECLARE word).

Answer

true

database final

Preguntas del test (72)

Información

Escríbenos

¿Qué te parece el nuevo diseño?