dbms quiz 2

What will the following SQL query return?

SELECT DISTINCT City
FROM Customers;

Only unique cities

What will this query return?
SELECT COUNT(*)
FROM Employees
WHERE Salary > 50000;

Number of employees with salary > 50000

What will this query return?
SELECT Name, City
FROM CUSTOMERS
WHERE City = ‘London’;

Names and cities of London customers only

This query will find:
SELECT COUNT(*)
FROM ORDERS;

Total number of orders

What does this query show?
SELECT Department,AVG(Salary)
FROM EMPLOYEES
GROUP BY Department;

Average salary per department

This query returns:
SELECT Name, Price
FROM PRODUCTS
WHERE Price > 100
ORDER BY Price DESC;

Products over $100, most expensive first

Which join returns matching records from both tables?

INNER JOIN

Which clause is used to eliminate duplicate rows?

DISTINCT

What does the BETWEEN operator do?

Checks range of values

WHICH SQL clause is used to search for NULL values?

IS NULL

What is COUNT?

Number of rows containing non-null values

What is MIN?

Minimum attribute value encountered in a given column

What is MAX?

Maximum attribute value encountered in a given column

What is SUM?

Sum of all values for a given column

What is AVG?

The arithmetic mean (average) for a specified column

What is DELETE command?

Used to delete selected rows from a table

What is UPDATE command?

Used to change individual columns of selected rows in a table

What is COMMIT?

Makes a change permanent

What is ROLLBACK?

Cancels all insertion, deletion, and update operations since the most recent COMMIT

What is COMMIT and ROLLBACK?

Are transaction management commands

Are table creation, drop and alter commands affected by COMMIT or ROLLBACK?

No, none are affected

What is GROUP BY?

Allows grouping results

What is INTERSECT?

Combines rows from two queries, returning only the rows that appear in both sets
ex: query INTERSECT query

What is EXCEPT (minus)?

Combines rows from two queries and returns only the rows that appear in the first set
ex: query EXCEPT query, query MINUS query

Can IN and NOT IN subsqueries be used in place of INTERSECT?

Yes

What is GRANT command?

Granting privileges to others

What is a Subqueries and what can it return?

Query inside another query
One single value
List of values
Virtual table

What is One single value?

One column and one row

What is List of values?

One column and multiple rows

What is virtual table?

Multicolumn, multirow set of values