DBMS

Margaret creates a transaction within a transaction. Her first (outermost) transaction rolls back data and the second (innermost) transaction commits data. What will be the outcome?

Both transactions are rolled back.

_ marks the end of a successful implicit or explicit transaction.

COMMIT WORK

____marks the end of a successful implicit or explicit transaction

COMMIT WORK

all aggregate functions ignore nulls except for

COUNT(*)

Advantages of Transactions?

Chaining Events Together: We can chain some events together using multiple transactions in a database; Flexibility: Using transactions allows us to change values in the database without accessing sensitive information—a perfect use case for corporate employee databases; Avoiding Data Loss: Transactional databases will also reduce the risk of losing any intermediate data if there is a power cut or an unexpected system shutdown; Database Management: Transactional databases make the jobs of many database administrators quite simple.

Select two incorrect statements about indexes. A. An index is created by 'CREATE INDEX', and deleted by 'DROP INDEX'. B. By using an index effectively, searching and sorting performs faster. C. There are B-tree, Hash, R-tree and GiST index types. D. By creating an index, performance always improves. E. Creating an unused index does not affect the performance of a database at all.

D. By creating an index, performance always improves. E. Creating an unused index does not affect the performance of a database at all.

Which keyword used with UNION does not retain duplicate rows?

DISTINCT

To get a record from one table, which is not present in another table we can use the clause

EXCEPT

The numbers returned by the dense_rank function may have gaps and does not always have consecutive ranks. TRUE/FALSE?

FALSE

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

FALSE

The sequence of the columns in a GROUP BY clause has no effect in the ordering of the output. True/False?

False

What is the significance of the statement "HAVING COUNT (emp_id)>2" in the given query?

Filter out all rows whose total emp_id below 2 & Selecting those rows whose total emp_id > 2

For the purposes of ............, null values are considered equal to other nulls and are grouped together into a single result row

GROUP BY

Which clause is used with an "aggregate functions"?:

Group By

Aggregate functions can be used in the select list or the_______clause of a select statement or subquery. They cannot be used in a __ clause

HAVING, WHERE

subquery is an sql expression that is placed _ another sql statement

INSIDE

What is true about order by with Union operator?

It can be issued for the overall result set.

Suppose it is desired that UNION operation should return not more than 3 rows. Which keyword is used for this?

LIMIT

SQL EXCEPT statement behaves similar to____ operator in mathematics

MINUS

specifies the order of rows in each partition to which the window function is applied. Default ___: with no ___ clause, the order of rows within each partition is arbitrary.

ORDER BY

which clause is used to sort a union result as a whole

ORDER BY

___ - divides rows into multiple groups, called partitions, to which the window function is applied. Default ___: with no ___ clause, the entire result set is the partition

PARTITION BY

Fred finds out that many of his users are complaining that data is lost. He does some research and finds out that his transactions are using the lowest isolation level. What level are his transactions using?

READ COMMITED

Which of the following clause is mandatorily used in a sub-query?

SELECT

If you want to select columns that have the same name in both emo and dept table, which of the among is correct?

SELECT empno, ename, job, dname, loc FROM emp NATURAL JOIN dept

You need to find the salaries for all the employees who have a higher salary than the Vice President of a company 'ABC'.Which of the following queries will give you the required result?

SELECT first_name, last_name, salary FROM employees WHERE salary > (SELECT salary FROM employees WHERE job_id = 'VICE-PRESIDENT');

which isolation level has the highest risk of blocking

SERIALIZABLE

Which of the following is TRUE about Savepoint command?

Selectively undo some parts of a transaction and commit all others

What among the following is true about sub-queries?

Sub-queries can be written on either side of a comparison operator

A _____ consists of a sequence of query and/or update statements:

TRANSACTIONS

Having clause is processed after the group by clause and any aggregate functions

TRUE

If you SELECT attributes and use an aggregate function, you must GROUP BY the non-aggregate attributes. True/False?

TRUE

Similar to the WHERE clause, the HAVING clause requires that the column names that appear in the clause must also appear as column names in the GROUP BY clause

TRUE

Where filters data before grouping and having filters data after grouping

TRUE

A _____ consists of a sequence of query and/or update statements

Transaction

The column names in the result of a UNION (of tables) are always equal to the column names in the 1st SELECT statement with the UNION operator.

True

To combine multiple retrievals, we write several SELECT statements and put the keyword between them. What is the keyword?

UNION

Which clause is similar to "HAVING" clause

WHERE

is a set of rows that are somehow related to the current row. It is evaluated separately within each partition.

WINDOW FRAME

Evaluate this IF statement: IF v_value > 100 THEN v_new_value = 2*v_value; ELSEIF v_value > 200 THEN v_new_value = 3*v_value; ELSEIF v_value > 300 THEN V_new_value = 4*v_value; ELSE v_new_value = 5*v_value; END IF ; What would be assigned to v_new_value if v_value=250? Select one: a. 500 b. 250 c. 750 d. 1000

a. 500

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. Select one or more: a. By using the SQL SELECT INTO statement. b. By using the Default statement. c. By using the assignment operator (=). d. By using the command line argument.

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

Which statements are true regarding the COUNT function? (Choose all that apply.) Select one or more: a. COUNT(*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns. b. COUNT(cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column. c. A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause. d. The COUNT function can be used only for CHAR,VARCHAR2,and NUMBER data types. e. COUNT(DISTINCT inv_amt)returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column.

a. COUNT(*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns. e. COUNT(DISTINCT inv_amt)returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column.

To be callable from a SQL expression, a user-defined function must do what? Select one: a. Use the positional notation for parameters. b. Have both IN and OUT parameters. c. Be stored only in the database. d. Return a BOOLEAN or VARCHAR data type

a. Use the positional notation for parameters.

What is acid properties of dbms transaction?

atomicity, consistency, isolation, and durability

Aggregate functions

avg, min, max, sum, count

Validates data in an attribute Select one: a. PRIMARY KEY b. CHECK c. NOT NULL d. UNIQUE

b. CHECK

Which two SQL statements would give the same output? (Choose two.) Select one or more: a. SELECT DISTINCT p.prod_id FROM products p JOIN sales s ON p.prod_id <> s.prod_id; b. SELECT DISTINCT p.prod_id FROM products p JOIN sales s ON p.prod_id=s.prod_id; c. SELECT prod_id FROM products EXCEPT SELECT prod_id FROM sales; d. SELECT prod_id FROM products INTERSECT SELECT prod_id FROM sales;

b. SELECT DISTINCT p.prod_id FROM products p JOIN sales s ON p.prod_id=s.prod_id; d. SELECT prod_id FROM products INTERSECT SELECT prod_id FROM sales;

To be callable from a SQL expression, a user-defined function must do what? Select one: a. Be stored only in the database. b. Use the positional notation for parameters. c. Return a BOOLEAN or VARCHAR data type. d. Have both IN and OUT parameters.

b. Use the positional notation for parameters.

.____ command should be the first word of a transaction

begin

Which of the following is NOT a supported transaction control statement? Select one: a. COMMIT b. SAVEPOINT c. CHECKPOINT d. ROLLBACK

c. CHECKPOINT

____________operator only cares about the number of rows returned from the subquery, not the content of the rows. Select one: a. ANY b. ALL c. EXISTS d. IN

c. EXISTS

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

d. NULL values are not ignored during execution

The changes of a successful transaction occurs even if the system failure occurs

durability property

For the purposes of ............, null values are considered equal to other nulls and are grouped together into a single result now

group by

What is the meaning of group by clause

group data by row values

Aggregate functions can be used in the select list or the_______clause of a select statement or subquery. They cannot be used in aBY clausclause

having, where

Multiple transactions occur independently without interference

isolation property

subqueries cannot

join tables

____ command is used to undo the changes done in transaction

rollback

savepoint command is used with ___ command

rollback

Difference between window function and aggregate function?

unlike aggregate functions, window functions do not collapse rows

All changes made in a transaction are ____ once it commits

visible

FROM, JOIN WHERE ORDER BY UNION/INTERSECT/EXCEPT OFFSET aggregate functions HAVING GROUP BY LIMIT/FETCH/TOP window functions SELECT DISTINCT

1. FROM, JOIN 2. WHERE 3. GROUP BY 4. aggregate functions 5. HAVING 6. window functions 7. SELECT 8. DISTINCT 9. UNION/INTERSECT/EXCEPT 10. ORDER BY 11. OFFSET 12. LIMIT/FETCH/TOP

Transactions that are____ do not expose all changes

ABORTED

Which keyword used with UNION retains duplicate rows?

ALL

can be created, deleted or modified with this SQL

ALTER TABLE

"COUNT" keyword belongs to which categories in Mysql?

Aggregate functions

The entire transaction takes place at once or doesn't happen at all

Atomicity property

Which of the following query deletes?

BEGIN; DELETE FROM HumanResources WHERE JobCandidateID = 13; COMMIT;

The _____ operator cannot be used with the sub query, but within it

BETWEEN

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? Select one: a. It gives an error because the GROUP BY expression is not valid. b. It executes successfully and gives the correct output. c. It gives an error because the HAVING clause is not valid. d. It executes successfully but does not give the correct output.

c. It gives an error because the HAVING clause is not valid.

Evaluate the following SQL statement: SELECT promo_id, promo_category FROM promotions WHERE promo_category = 'Internet' ORDER BY promo_id UNION SELECT promo_id, promo_category FROM promotions WHERE promo_category = 'TV' UNION SELECT promo_id, promo_category FROM promotions WHERE promo_category = 'Radio'; Which statement is true regarding the outcome of the above query? Select one: a. It executes successfully but ignores the order by clause because it is not located at the end of the compound statement. b. It executes successfully and displays rows in the descending order of PROMO_CATEGORY. c. It produces an error because the order by clause should appear only at the end of a compound query-that is, with the last select statement. d. It produces an error because positional notation cannot be usedi n theo rder by clause with se toperators

c. It produces an error because the order by clause should appear only at the end of a compound query-that is, with the last select statement.

They check whether a string matches a given pattern. a. GROUP BY b. DISTINCT c. LIKE d. IN

c. LIKE

_____________uses the NULLS FIRST or NULLS LAST option to specify whether nullable values should be first or last in the result set. Select one: a. PARTITION BY b. GROUP BY c. ORDER BY d. SORT BY

c. ORDER BY

Which of the following is not an advantage of PL/PGSQL? Select one: a. Instead of sending multiple statements to the server one by one, you can send one statement to execute the object stored in the server. b. Reduce the number of round trips between the application and the PostgreSQL database server. c. Slower in software development because PL/pgSQL requires specialized skills that many developers do not possess. d. PL/pgSQL wraps multiple statements in an object and store it on the PostgreSQL database server.

c. Slower in software development because PL/pgSQL requires specialized skills that many developers do not possess.

Which are true about aggregate functions? (Choose all that apply.) Select one or more: a. You can use aggregate functions on a table, only by grouping the whole table as one single group. b. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns. c. You can use aggregate functions in any clause of a SELECT statement. d. You can use aggregate functions only in the column list of the select clause and in the WHERE clause of a SELECT statement. e. You can pass column names, expressions, constants, or functions as parameter to an aggregate function. f. You cannot group the rows of a table by more than one column while using aggregate function

c. You can use aggregate functions in any clause of a SELECT statement. e. You can pass column names, expressions, constants, or functions as parameter to an aggregate function.

The ROW_NUMBER() function... Select one: a. assigns a rank to each row within an ordered partition, but the ranks have no gap. In other words, the same ranks are assigned to multiple rows and no ranks are skipped b. returns a value evaluated against the first row within its partition c. assigns a sequential number to each row in each partition d. assigns ranking within an ordered partition. If rows have the same values, the function assigns the same rank, with the next ranking(s) skipped e. returns a value evaluated against the last row in its partition

c. assigns a sequential number to each row in each partition

What is xyz in the following SQL statement? SELECT xyz FROM table1 UNION xyz FROM table2

column name

The database must be consistent before and after the transaction

consistency property