What is a grouping set ?

a set of columns by which you group by using the GROUP BY clause

a set of indexes by which you group by using the GROUP BY clause

a set of rows by which you group by using the GROUP BY clause

a set of operators by which you group by using the GROUP BY clause

a set of views by which you group by using the GROUP BY clause

The RANK() function...

assigns ranking within an ordered partition. If rows have the same value, the function assigns the same rank, with the next ranking(s) skipped

returns a value evaluated against the first row within its partition

assigns a sequential to each row in each partition

returns a value evaluated against the last row in its partition

assigns a rank to each row within an ordered partition, but the ranks have no gap. In other words, the same ranks are assigned o multiple rows and now ranks are skipped

Select two correct descriptions about views

A view is a virtual table which does not exist

A view is created to simplify complicated queries

A view is created by 'DECLARE VIEW', and deleted by 'DROP VIEW'

A view only exist while the session is running, and is deleted when the session stops

Select one statement which will cause a syntax error

SELECT * FROM (SELECT * FROM customer)

SELECT * FROM sale WHERE cid = ANY(SELECT cid FROM customer );

SELECT * FROM sale WHERE name IN (SELECT name FROM names);

SELECT (SELECT item FROM sale WHERE id = 1);

SELECT * FROM item WHERE cid IN (SELECT cid FROM customer)

The essence of a transaction is...

it combines a sequence of actions into one operation

it returns alternative data access

it combines the user defined functions and procedures

A view can be very useful in some cases such as:

A view provides a consistent layer even the columns of underlying table changes

A view returns the duplicate rows, when you use the set operator

A view assumes a hierarchy among the input columns and generates all grouping sets that make sense considering the hierarchy

A view specifies the dimension columns within the parentheses of the subclause

A view returns rows by comparing the result sets of two or more queries

The DENSE_RANK() function

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

assigns a sequential number to each row in each partition

returns a value evaluated against the first row within its partition

returns a value evaluated against the last row in its partition

assigns ranking within an ordered partition. If rows have the same value, the function assigns the same rank, with the next ranking(s) skipped

What is the function of EXISTS operator in subqueries?

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

returns from the subquery to insert into another table

performs a calculations across a set of table rows that are somehow related to the current row

returns only one row so we can use GROUP BY clause

deletes all existing subqueries

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:

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

It gives an error because the GROUP BY expression is not valid.

It executes successfully and gives the correct output.

It executes successfully but does not give the correct output.

Which statements are true regarding the COUNT function? (Choose all that apply.) Select one or more:

COUNT(*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns.

COUNT(DISTINCT inv_amt)returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column.

COUNT(cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column.

A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.

The COUNT function can be used only for CHAR,VARCHAR2,and NUMBER data types.

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

Use the positional notation for parameters.

Return a BOOLEAN or VARCHAR data type.

Have both IN and OUT parameters.

Which two SQL statements would give the same output? (Choose two.)

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

SELECT prod_id FROM products INTERSECT SELECT prod_id FROM sales;

SELECT DISTINCT p.prod_id FROM products p JOIN sales s ON p.prod_id <> s.prod_id;

SELECT prod_id FROM products EXCEPT SELECT prod_id FROM sales;

The STUDENT_GRADES table has these columns: STUDENT_ID NUMERIC(12) SEMESTER_END DATE GPA NUMERIC(4,3) Which statement finds the highest grade point average (GPA) per semester?

SELECT MAX (gpa) FROM student grades WHERE gpa IS NOT NULL GROUP BY semesterend;

SELECT (gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;

SELECT MAX (gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM student _ grades;

SELECT MAX(gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;

SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL;

Which statements are true regarding indexes? (Choose all that apply.)

You can create more than one index by using the same columns if you specify distinctly different combinations of the columns.

They can be created on tables and simple views.

You can create only one index by using the same columns.

SELECT COUNT(*),prod_category_id FROM products GROUP BY prod_category_id HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM products); What is the outcome?

It generates an error because the subquery does not have a GROUP BY clause.

It generates an error because = is not valid and should be replaced by the IN operator.

It executes successfully but does not give the correct output.

It executes successfully and gives the correct output.

Examine the subquery: SELECT last_name FROM employees WHERE salary IN (SELECT MAX(salary) FROM employees GROUP BY department_id); Which statement is true?

The SELECT statement is syntactically accurate.

The SELECT statement does not work because the column specified in the GROUP BY clause is not in the SELECT list.

The SELECT statement does not work because there is no HAVING clause.

The SELECT statement does not work because the GROUP BY clause should be in the main query and not in the subquery.

In the declarative section of a PL/pgSQL block, you created but did not initialize a number variable. When the block executes what will be the initial value of the variable?

The block will not execute because the variable was not initialized.

It depends on the scale and precision of the variable.

Which statements are true about constraints? (Choose all that apply.)

A UNIQUE index gets created for columns with PRIMARY KEY and UNIQUE constraints.

The NOT NULL constraint ensures that null values are not permitted for the column

The PRIMARY KEY and FOREIGN KEY constraints create a UNIQUE index.

The UNIQUE constraint does not permit a null value for the column.

Which statement is true regarding the UNION operator?

NULL values are not ignored during execution.

By default, the output is sorted.

The number of columns selected in all SELECT statements need not be the same.

Names of all columns must be identical across all SELECT statements.

You create the sales table with this command: CREATE TABLE sale ( Purchase_id int PRIMARY KEY, Customer_id int NOT NULL); Which index or indexes are created for this table?

An index is created for purchase_id column

An index is created for each column.

An index is created for the customer_id column.

No indexes are created for this table

Which are true about aggregate functions? (Choose all that apply.)

You can use aggregate functions in any clause of a SELECT statement.

You can pass column names, expressions, constants, or functions as parameter to an aggregate function.

You can use aggregate functions on a table, only by grouping the whole table as one single group.

You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.

You can use aggregate functions only in the column list of the select clause and in the WHERE clause of a SELECT statement.

DBMS endterm

Authored by lucky star

Computers

University

Used 11+ times

AI Actions

Add similar questions

Adjust reading levels

Convert to real-world scenario

Translate activity

More...

Content View

Student View

45 questions

Show all answers

MULTIPLE CHOICE QUESTION

30 sec • 1 pt

Select incorrect statement about the following SQL statement
CREATE OR REPLACE VIEW sales_view
AS SELECT * FROM sales_table ORDER BY sales_date DESC
LIMIT 10

Replaces "sales_view" if it already exists

Some errors occur when "SELECT * FROM sales_table" is executed after the view is defined

When you 'SELECT' the "sales_view", it displays the first 10 records from the "sales_table" sorted by the "sales_date" in desc order

Defines the view called "sales_view"

MULTIPLE CHOICE QUESTION

30 sec • 1 pt

An error occurs when executing the (2) SQL statement

An error occurs when executing the (4) SQL statement

No error is generated

An error occurs when executing the (1) SQL statement

An error occurs when executing the (3) SQL statement

MULTIPLE CHOICE QUESTION

30 sec • 1 pt

Based on the relationship of columns within a table, select the most suitable description that shows that column A is dependent on the column B

The value in column A is uniquely determined when a value in column B is selected

As long as column B exists, the amount of the information won't decrease even if column A is deleted

When the value in column B is changed, the corresponding value of column A also must be changed

The value in column B is uniquely determined when a value in column A is selected

When the value in column A is changed, the corresponding value of column B also must be changed

MULTIPLE CHOICE QUESTION

30 sec • 1 pt

You can use aggregate functions as expressions only in the following clauses

LIMIT and DISTINCT clauses

SELECT and HAVING clauses

CREATE and DELETE clauses

GROUP BY and JOIN clauses

WHERE and ORDER BY clauses

MULTIPLE CHOICE QUESTION

30 sec • 1 pt

PostgreSQL evaluates the GROUP BY clause after...

the FROM and WHERE clause

the LIMIT clause

the ORDER BY clause

the HAVING clause

the SELECT clause

MULTIPLE CHOICE QUESTION

30 sec • 1 pt

This operator removes all duplicate rows from the combined data set

SELECT

UNION ALL

INTERSECT

UNION

EXCEPT

MULTIPLE CHOICE QUESTION

30 sec • 1 pt

NAND

UNION ALL

INTERSECT

UNION

EXCEPT

Access all questions and much more by creating a free account

Create resources

Host any resource

Get auto-graded reports

Continue with Google

Continue with Email

Continue with Classlink

Continue with Clever

or continue with

Microsoft

Apple

Others

Already have an account?

Similar Resources on Wayground

40 questions

Komputer Akuntansi Kelas XI AK

Quiz

•

KG - Professional Dev...

50 questions

IUTCS Freshers

Quiz

•

University

40 questions

First QUIZ of Database - Edited by: Eng. Javier Daza 2023-1

Quiz

•

University

50 questions

Computer System Servicing

Quiz

•

University

40 questions

KUIS SISTEM OPERASI

Quiz

•

University

42 questions

network

Quiz

•

University

50 questions

Quiz

•

University

50 questions

MS Excel Quiz

Quiz

•

University

Popular Resources on Wayground

15 questions

Fractions on a Number Line

Quiz

•

3rd Grade

20 questions

Equivalent Fractions

Quiz

•

3rd Grade

25 questions

Multiplication Facts

Quiz

•

5th Grade

$fractions$

22 questions

fractions

Quiz

•

3rd Grade

20 questions

Main Idea and Details

Quiz

•

5th Grade

20 questions

Context Clues

Quiz

•

6th Grade

15 questions

Equivalent Fractions

Quiz

•

4th Grade

20 questions

Figurative Language Review

Quiz

•

6th Grade

Discover more resources for Computers

30 questions

Quiz 1 Review

Quiz

•

University

DBMS endterm

Select incorrect statement about the following SQL statement CREATE OR REPLACE VIEW sales_viewAS SELECT * FROM sales_table ORDER BY sales_date DESCLIMIT 10

Based on the relationship of columns within a table, select the most suitable description that shows that column A is dependent on the column B

You can use aggregate functions as expressions only in the following clauses

PostgreSQL evaluates the GROUP BY clause after...

This operator removes all duplicate rows from the combined data set

What is a grouping set ?

The RANK() function...

Which of the following are NOT a property of database transactions? Choose all that apply

Access all questions and much more by creating a free account

Similar Resources on Wayground

Popular Resources on Wayground

Discover more resources for Computers

Select incorrect statement about the following SQL statement
CREATE OR REPLACE VIEW sales_view
AS SELECT * FROM sales_table ORDER BY sales_date DESC
LIMIT 10