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