DBMS FINAL

The string defining the message uses ___placeholders, which will be replaced by arguments

%

Examine the code: DO $$ DECLARE v_name emp.ename%TYPE; v_num NUMERIC; v_sal NUMERIC(8,2); BEGIN --- This code displays salaries if larger than 10,000. SELECT ename, sal INTO v_name, v_sal FROM emp WHERE empno=101; IF(v_sal.GT 10000) THEN raise notice 'Salary is %', sal; END IF; END; $$; This statement produces a compilation error when above PL/pgSQL block is executed? Select one:

IF (v_sal.GT 10000) THEN

You use GROUPING functions to:

Identify the extra row values created by either a ROLLUP or CUBE operation

Which of the join operations do not preserve non matched tuples?

Inner join

SELECT promo_name,CASE WHEN promo_cost >= (SELECT AVG(promo_cost) FROM promotions WHERE promo_category='TV') then 'HIGH' else 'LOW' END PROMOTION FROM promotions; Which statement is true regarding the outcome of the above query?

It shows result for all the promos in the table.

Returns the number of characters in a string value

LENGTH()

____________function returns the number of characters in the string

LENGTH()

Checks whether an attribute value matches a given string pattern

LIKE

Which of the keywords is more suitable for this definition "They check whether a string matches a given pattern.

LIKE

________relationship is not supported directly in the relational environment. However, it can be implemented by creating a new entity in 1:M relationships with the original entities.

M:N

Notice that a view does not store data physically except for a

MATERIALIZED VIEW

Ensures that a column will not have null values

NOT NULL

Which statement is true regarding the UNION operator? Select one: a. The number of columns selected in all SELECT statements need not be the same. b. NULL values are not ignored during execution. c. Names of all columns must be identical across all SELECT statements. d. By default, the output is sorted.

NULL values are not ignored during execution.

What are the different categories of PL/pgSQL program units? Each correct answer represents a complete solution. Choose all that apply.

Named unnamed

The list of expressions whose values should be sorted is specified after the

ORDER BY

_____ uses the NULLS FIRST or NULL LAST option of specify whether nullable values should be first or last in the result set

ORDER BY

Which of the following functions are similar?

RANK and DENSE_RANK

Which method for ranking records repeats a ranking when there is a tie and may have a result set that is not consecutive?

RANK()

Which command is used to undo changes made in transactions?

ROLLBACK

Which SQL statement is used to return only different values?

SELECT DISTINC

Find query to get resulting table with ranked salaries of employees:

SELECT salary, DENSE_RANK() OVER (ORDER BY salary) dense_rank FROM employees ORDER BY salary;

return the sum of all or distinct values

SUM()

A database management system (DBMS) is a:

Software system used to create, maintain, and provide controlled access to a database

The distribution in which each row denote some related values collection is classified as

TABLE

Which statement is true regarding the INTERSECT operator?

The number of columns and data types must be identical for all SELECT statements in the query

SELECT p.prod_name, i.item_cnt FROM (SELECT prod_id, COUNT(*) item_cnt FROM sales GROUP BY prod_id) i RIGHT OUTER JOIN products p ON i.prod_id = p.prod_id; What happens when the above statement is executed?

The statement executes successfully and produces the required output.

For which constraints does the PostgreSQL implicitly create a unique index? (Choose all that apply)

UNIQUE PRIMARY KEY

To be callable from a SQL expression, a user-defined function must do what?

Use the position notation for parameters

________is named query that provides another way to present data in the database tables.

VIEW

__is named query that provides another way to present data in the database tables.

VIEW

Consider following query: CREATE Emp_two VIEW AS SELECT nothing FROM Employees WHERE employee_id < 7; Find the mistake in this query

View name must be after keyword view and 'nothing' is not a keyword , so should be replaced with *.

Select the correct SQL syntax for creating a view.

a. CREATE VIEW customers AS SELECT * FROM customers WHERE Name LIKE '%I';

In which two situations does a transaction complete? Select one or more: a. When a ROLLBACK command is executed b. When a PL/pgSQL anonymous block is executed c. When a data definition language (DDL) statement is executed d. When a DELETE statement is executed

a. When a ROLLBACK command is executed b. When a data definition language (DDL) statement is executed

Mark works as an Application Developer for Tech-Mart Inc. The company uses a POSTGRESQL database. The database contains a table named Employees. Following is the structure of the table: Emp_ID INTEGER Emp_Name VARCHAR(30) Dept_ID INTEGER Salary INTEGER Mark wants to create a procedure named Add_Emp that will be used to insert new records in the Employees table. In order to accomplish this, Mark executes the following statement: CREATE OR REPLACE PROCEDURE Add_Emp (IN p_emp_id INTEGER, p_name Emplovees.Emp_Name%TYPE, p_dept_id INTEGER, p_salary Employees. Salary %TYPE) RETURNS INTEGER AS $$ BEGIN INSERT INTO Employees (Emp_ID, Emp_Name, Dept_ID, Salary) VALUES (p_emp_id, p_name, p_dept_id, p_salary); END; $$; What will happen while executing the statement?

. It will return an error because the RETURN clause cannot be used in a procedure declaration

Which of the following options is not a method for numbering or ranking records?

. NUMBER()

Returns a date value using a character string and a date format mask.

. TO_DATE()

_______________function removes the longest string that contains a specific character from a string

. TRIM()

The basic operations that can be performed on relations are

. deletion b. all presented answers c. insertion d. modification

The structure of the DEPT table is as follows: Deptno Not Null INTEGER, Dname VARCHAR(20) , Loc VARCHAR(10). Examine the code: DO $$ DECLARE dept_rec record; BEGIN Select * INTO dept_rec FROM dept WHERE deptno = 10; END; $$; Which statement displays the name of selected department?

. raise notice '%', dept_rec.dname;

Which of the following statements are TRUE about SQL query? 1. An SQL query can contain a HAVING clause even if it does not have a GROUP BY clause; 2. An SQL query can contain a HAVING clause only if it has a GROUP BY clause; 3. All attributes used in the GROUP BY clause must appear in the SELECT clause; 4. Not all attributes used in the GROUP BY clause need to appear in the SELECT clause;

2 & 3

Which statement is true? Select one: a. A procedure must have a return data type specify in its declaration. b. A function must return a value. c. A procedure must return a value. d. A function executes a PL/pgSQL statement.

A function must return a value.

Which one of the following is not true for a view: Select one: a. View is derived from other tables b. View never contains derived columns c. A view definition is permanently stored as part of the database d. View is a virtual table

A view definition is permanently stored as part of the database

Anonymous blocks are used when building scripts to seed data or perform one-time processing activities. In which of the following sections of the anonymous-block of PL/pgSQL can anonymous block programs be nested?

EXECUTION

operator only cares about the number of rows returned from the subquery, not the content of the rows

EXISTS

The rows returned by a query using ROW_NUMBER() will be ordered exactly the same with each execution

False

What SQL command can be used to add columns to a table?

ALTER TABLE tableName add column columName

Which of the following is NOT a type of SQL constrain

ALTERNATE KEY

When creating procedures, parameters should be placed before which keyword?

AS

What options does ORDER BY have?

ASC, DESC

What are the methods through which a value can be assigned to a variable? Each correct answer represents a complete solution. Choose all that apply

By using the SQL SELECT INTO statement. By using the assignment operator (=)

returns distinct rows from the first(left) query that are not in the output of the second(right) query.

EXCEPT

Which of the following is NOT a supported transaction control statement?

CHECKPOINT

Which of the following command makes the updates performed by the transaction permanent in the database?

COMMIT

Which program declarations are correct for stored procedures and functions? (Choose all that apply) Select one or more: a. CREATE OR REPLACE PROCEDURE TAX_AMT (B_ID INT) RETURNS INT b. CREATE OR REPLACE PROCEDURE TAX_AMT (B_ID NUMERIC, B_AMOUNT OUT NUMERIC (10,2)) c. CREATE OR REPLACE FUNCTION TAX_AMT (B_ID INT OUT) RETURNS INTEGER(10,2) d. CREATE OR REPLACE FUNCTION TAX_AMT (B_ID INT) RETURNS INT e. CREATE OR REPLACE PROCEDURE TAX_AMT (B_ID INT, OUT B_AMOUNT INT)

CREATE OR REPLACE FUNCTION TAX_AMT (B_ID INT) RETURNS INT e. CREATE OR REPLACE PROCEDURE TAX_AMT (B_ID INT, OUT B_AMOUNT INT)

The command used in SQL to specify new relation, its initial constraints and attributes is classified as

CREATE TABLE

Which tasks can your perform by using the TO_CHAR function? (Choose all that apply) Select one or more: a. Convert a date to a character expression b. Convert '10' to 10 c. Convert 10 to 'TEN' d. Convert 'TEN' to 10 e. Convert a character expression to a date f. Convert '10' to '10'

Convert '10' to '10' Convert a date to a character expression

SQL query and modification commands make up a(n) ________

DML

Permanently deletes a table (and its data)

DROP TABLE

___________refers to the activities that focus on the design of the database structure that will be used to store and manage end-user data.

Database design

Find right variant of the for loop statement in the query which iterate over largest 5 employees salary: Select one: a. declare sal record; begin for sal in select salary, last_name from employees order by salary asc, last_name limit 5 loop raise notice '% - % ', sal.salary, sal.last_name; end loop; end; b. declare sal record; begin for sal in select salary, last_name from employees order by salary desc, last_name limit 5 loop raise notice '% - % ', sal.salary, sal.last_name; end loop; end; c. declare sal record; begin for sal select salary, last_name from employees order by salary limit 5 loop raise notice '% - % ', sal.salary, sal.last_name; end loop; end;

a. declare sal record; begin for sal in select salary, last_name from employees order by salary asc, last_name limit 5 loop raise notice '% - % ', sal.salary, sal.last_name; end loop; end;

Select the correct example of a composed query using JOIN. This request will give us the details of the order ID, the name of the customer and the seller: Select one: a. select Orders.id, Orders.customer_name, Sellers.id from Orders LEFT JOIN Sellers ON Orders.seller_id = Sellers.id; b. select Orders.id, Orders.customer_name, Sellers.id from Orders JOIN Sellers WHEN Orders.seller_id = Sellers.id; c. select Orders.id, Orders.customer_name, Sellers.id from Orders LEFT JOIN ON Sellers AND Orders.seller_id = Sellers.id; d. select id AND customer_name AND seller_id from Orders LEFT JOIN Sellers ON seller_id = id;

a. select Orders.id, Orders.customer_name, Sellers.id from Orders LEFT JOIN Sellers ON Orders.seller_id = Sellers.id

A reason for using an SQL view to hide columns is:

a.to simplify a result only. b. None of the above are reasons for using an SQL view. c. to prevent the display of sensitive data only. d. to accomplish both of the above.

When using _______ keyword, the comparison result should be true for all values returned by the subquery

all

What is the purpose of index in SQL server? Select one: a. It leads to provide an index to a record. b. It leads to perform fast searches. c. All of the presented. d. None of the above e. It leads to enhance the query performance.

all of presented

Which of the following is a valid SQL type? Select one: a. FLOAT b. All of the presented c. NUMERIC d. CHARACTER

all of the presented

The PostgreSQL ASCII() function returns________

an ASCII code value of a character

the ROW_NUMBER () function

assigns a sequential number to each row in each partition

To get the year, quarter, month, week, day from a date value, you use the _____________ function.

b. EXTRACT()

Assume that you have the following: CREATE FUNCTION get_max_price (productid INT) ........ END If you want to query this function, the command will be:

b. SELECT get_max_price (3);

SQL Server automatically creates an index on:

both primary and foreign keys

SELECT item_no, AVG(qty) FROM ord_items HAVING AVG(qty) > MIN(qty) * 2 GROUP BY item_no; Which statement is true regarding the outcome of the above query

c. It displays the item_nos with their average quantity where the average quantity is more than double the minimum quantity of that item in the table

What is a data warehouse?

c. is a specialized database that stores data in a format optimized for decision support.

validates data in an attribute

check

the relational model consist of

data in the form of tables

Limits values to unique values

distinct

Which of the following command is used to delete a table in SQL?

drop

A transaction for which all committed changes are permanent is called:

durable

In ER diagrams, the rectangles are used to denote

entity type

The______ operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement

except

Evaluate the following SQL statement: SELECT promo_category, AVG(promo_cost) Avg_Cost, AVG(promo_cost)*.25 Avg_Overhead FROM promotions WHERE UPPER(promo_category) IN ('TV', 'INTERNET','POST') GROUP BY Avg_Cost ORDER BY Avg_Overhead; The above query generates an error on execution. Which clause in the above SQL statement causes the error

group by

UPDATE table_name SET year = 2... ::Identify the attribute/s from the codes below: UPDATE table_name SET year = 2...::Identify the attribute/s from the codes below: UPDATE table_name SET year = 2 WHERE id = 21;

id year

______________are effective tools to enhance database performance. Select one: a. INDEXES b. WINDOW FUNCTIONS c. GROUPING SETS d. VIEWS

index

SELECT AVG(MAX(qty)) FROM ord_items GROUP BY item_no HAVING AVG(MAX(qty))>50; Which statement is true regarding the outcome of this query?

it gives an error because the HAVING clause is not valid

Mark works as an Application Developer for Tech-Mart Inc. The company uses a POSTGRESQL database. The database contains a table named Employees. Following is the structure of the table: Emp_ID INTEGER Emp_Name VARCHAR(30) Dept_ID INTEGER Salary INTEGER Mark wants to create a procedure named Add_Emp that will be used to insert new records in the Employees table. In order to accomplish this, Mark executes the following statement: CREATE OR REPLACE PROCEDURE Add_Emp (IN p_emp_id INTEGER, p_name Emplovees.Emp_Name%TYPE, p_dept_id INTEGER, p_salary Employees. Salary %TYPE) RETURNS INTEGER AS $$ BEGIN INSERT INTO Employees (Emp_ID, Emp_Name, Dept_ID, Salary) VALUES (p_emp_id, p_name, p_dept_id, p_salary); END;$$;

it wiil return an error because the RETURN clause cannot be used in a procedure declaration

A data manipulation command the combines the records from one or more tables is called

join

Subqueries cannot: Select one: a. Join tables b. Retrieve data from a table different from the one in the outer query c. Use group by or group functions d. Appear in select, update, delete, insert statements.

join table

Examine the structure of the EMP_DEPT_VU view: EMPLOYEE_ID INT, EMP_NAME VARCHAR(30), JOB_ID VARCHAR(20), SALARY INT , DEPARTMENT_ID INT, DEPT_NAME VARCHAR(30). Which SQL statement produces an error? Select one: a. SELECT department_id, SUM(salary) FROM emp_dept_vu GROUP BY department _ id; b. SELECT department_id, job_id, AVG(salary) FROM emp_dept_vu GROUP BY department _ id, job_id; c. SELECT job_id, SUM(salary) FROM emp_dept_vu WHERE department_id IN (10,20) GROUP BY job_id HAVING SUM (salary) > 20000 d. SELECT * FROM emp_dept_vu; e. None of the statements

none of the statements

Which of the following is correct: Select one: a. SQL permits attribute names to be repeated in the same relation. b. a SQL query automatically eliminates duplicates. c. a SQL query will not work if there are no indexes on the relations d. None of these

none of these

Which of the following is not valid literal for BOOLEAN data type?

null

When you define a _______________ or a unique constraint for a table, PostgreSQL automatically creates a corresponding UNIQUE index.

primary key

COUNT()

return the number of values

To get more than one row out of the function you should use:

returns setof

To return more than one row or multiple rows with the columns that defined by output parameters you should use ___ as a returning method

returns setof record

A PL/SQL function: Must return a single value b. Must contain at least one RETURN statement c. Can be invoked as part of an expression d. Must contain a RETURN clause in the header e. Does not contain a RETURN clause in the header

single value, at least one return, part of expression

Which of the following is not an advantage of PL/PGSQL?

slower in software development because PL/pgSQL requires specialized skills that many developers do not process

Ensures that a column will not have duplicate values

unique

How to copy data type of a table column or another variable

variable_name table_name.column_name%type; variable_name Variable%type;

identify situations in which you can use the %ROWTYPE attribute

when you are not sure about the structure of the underlying database table when you want retrieve an entire row from a table

Which of the following is not an aggregate function?

with