Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

ECS519U - Database Systems > Week 4 - Further SQL > Flashcards

Week 4 - Further SQL Flashcards

1
Q

What are aggregate functions?

A

Functions which act on a set of rows/tuples along a chosen column.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

List aggregate function keywords in SQL

A

COUNT, SUM, AVG, MAX, MIN

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What do aggregate functions do by default?

A

They work on all rows returned by SELECT query.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Can the DISTINCT keyword be used in the argument of aggregate functions?

A

Yes

mysql> select COUNT(distinct DEPTNO) from EMP;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

SELECT * FROM zoo
WHERE animal LIKE ‘%e%’

What does this mean?

A

Select all rows in the zoo table where the value held in the animal column contains the letter ‘e’.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

SELECT * FROM zoo
WHERE animal LIKE ‘e%’

What does this mean?

A

Select all rows in the zoo table where the value held in the animal column starts with the letter ‘e’.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

SELECT * FROM zoo
WHERE animal LIKE ‘%e’

What does this mean?

A

Select all rows in the zoo table where the value held in the animal column ends with the letter ‘e’.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

SELECT * FROM zoo
WHERE animal LIKE ‘e_’

What does this mean?

A

Select all rows in the zoo table where the value held in the animal column starts with the letter ‘e’ and has a single character following it.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

SELECT * FROM zoo
WHERE animal LIKE ‘z_b_a’

What does this mean?

A

Select all rows in the zoo table where the value held in the animal column starts with a z, has any single character, then has a b, then has any single character, then ends with an a.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

SELECT * FROM zoo
WHERE animal LIKE ‘_____’ – 5 underscores

What does this mean?

A

Select all rows in the zoo table where the value held in the animal column has exactly 5 characters.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

SELECT * FROM zoo
WHERE uniq_id = 1
OR uniq_id = 2
OR uniq_id = 3
OR uniq_id = 4
OR uniq_id = 5;

How can you condense this query?

A

Using IN.

SELECT * FROM zoo
WHERE uniq_id IN (1,2,3,4,5)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

SELECT * FROM zoo
WHERE animal NOT LIKE ‘_____’ – 5 underscores

What does this do?

A

Returns all animals whose names are not 5 characters in length.
For example, it could return giraffe, but definitely not zebra.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

How does aliasing work?

A

Use this after a column name:

AS alias_name

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What does GROUP BY do?

A

Splits records into separate groups of unique values of a given column.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What does functionally dependent ensure in the context of GROUP BY?

A

It ensures additional columns have a unique and deterministic value within each group created by the GROUP BY clause.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What happens if a column is used that isn’t functionally dependent in GROUP BY?

A

The DBMS cannot determine which value to pick for the group. There is ambiguity.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What interesting thing occurs when adding a WHERE condition to an aggregate function query?

A

It causes rows to be removed before the aggregate function is applied.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What keyword can you use to select rows only after the aggregate function has been applied?

A

HAVING

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Provide an example of using the HAVING keyword

A

SELECT F(attribute) FROM TABLE
WHERE condition_1 –filters out rows before aggregate
GROUP BY attribute
HAVING condition_2 –filters out rows after aggregate

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

What is the operation order of a general SQL query (logical processing order)

A

FROM/JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

What is the syntax order of a general SQL query?

A

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What are the requirements of the SQL UNION operator?

EG:
SELECT * FROM customers
UNION
SELECT * FROM orders;

A

1) The queries must return the same number of columns.
2) The data types don’t have to match exactly but must be implicitly convertible (e.g. DATE can become VARCHAR, but BLOB cannot become INT)
3) By default, UNION removes duplicates (use UNION ALL to keep them).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What does the SQL INTERSECT operator do?

A

It finds the common rows between two queries (the overlap).

24
Q

What are the the requirements of the SQL INTERSECT operator?

A

1) Same Number of Columns: Both queries must return the same number of columns.
2) Compatible Data Types: Columns in the same position must have compatible types.
3) Duplicates Removed: INTERSECT automatically removes duplicate rows from the result.

25
Q

What does the SQL UNION operator do?

A

It combines the results of two or more SELECT statements into a single result set.

26
Q

What does INNER JOIN do?

A

It combines rows from two or more tables based on a matching condition. The result contains only rows where the condition matches in both tables. If no match is found, the row is excluded from the result.

27
Q

Explain INNER JOIN syntax in terms of venn diagrams.

SELECT *
FROM Table1
INNER JOIN Table2
ON Table1.col = Table2.col

A

The first table you mention will be the left side of the venn diagram (Table1).
The second table mentioned after INNER JOIN is the right side of the venn diagram (Table2).
The query returns the overlap in the venn diagram.

28
Q

What’s an outer join?

A

They are inner joins supplemented by rows for which no match could be made.

29
Q

What are the three types of outer join?

A

Left, right and full

30
Q

Explain left joins

A

It returns all rows from the left table, even if there’s no matching row in the right table. If no match is found, columns in the right table will all contain NULL.

31
Q

Explain right joins

A

It returns all rows from the right table, even if there’s no matching row in the left table. If no match is found, columns in the left table will all contain NULL.

32
Q

What’s an SQL VIEW, and how does it work?

A

It’s a virtual table created by a stored query. It acts like a table but doesn’t store any data itself.

33
Q

Why use SQL VIEWs?

A

It simplifies complex queries by encapsulating them into a reusable and easy-to-reference format.

34
Q

How is data fetched in SQL VIEW?

A

The data is dynamically fetched from the base tables when the VIEW is queried.

35
Q

Give an example of using SQL VIEW

A

– Query 1
CREATE VIEW StudentCourses AS
SELECT Students.Name, Enrollments.Course
FROM Students
INNER JOIN Enrollments
ON Students.StudentID = Enrollments.StudentID;

– Query 2
SELECT * FROM StudentCourses;

36
Q

Advantage of using SQL VIEW

A

Code is easier to read and maintain

37
Q

How does SQL WITH work?

A

Works like VIEW, except it doesn’t create a virtual table in the DB. Moreover, WITH is part of a single query, not a standalone operation.

38
Q

Give an example of using the WITH clause

A

– A single query
WITH salesforce AS
(SELECT * FROM EMP WHERE JOB = ‘SALESMAN’)
SELECT * FROM salesforce;

– Can also be written using nested query / subquery, using compulsory aliasing
SELECT * FROM (SELECT * FROM EMP WHERE JOB = ‘SALESMAN’) AS salesforce;

39
Q

Break down this complex query

SELECT E.ENAME FROM
(SELECT AVG(SAL) AS SALESAVG FROM EMP
WHERE JOB=‘SALESMAN’) AS S, EMP E
WHERE E.SAL>S.SALESAVG;

A

The subquery gives us a table with a single column (SALESAVG), with a single row containing the average salesman salary. This table is aliased as S.

We’re selecting the names from EMP (aliased as E) of everyone (not just salesmen) who earns more than the average salesman salary.

40
Q

What’s a predicate?

A

A condition/expression in SQL that evaluates to true, false or NULL (unknown).
They often involve comparisons (e.g. =, >, <, BETWEEN, IN, LIKE).
They filter rows based on conditions such as matching values, ranges or patterns.
You could also define them with subqueries, allowing you to create complex conditions for filtering data.

41
Q

In which clauses might you see a predicate being used?

A

WHERE
HAVING
JOIN

42
Q

What does this query do?

SELECT attribute_1
FROM table_1
WHERE attribute_2 IN (SELECT attribute FROM table_2);

A

It will return the values in the attribute_1 column from table_1, only for the rows where attribute_2 matches one of the values found in the attribute column from table_2.

43
Q

What does the EXISTS predicate do?

A

It checks if a subquery returns at least one row. If it does, the condition is true.

44
Q

What does the ANY predicate do?

A

It allows you to compare a value to any value returned by the subquery. The condition is true if the value is greater/less than any value in the result set of the subquery.

45
Q

What does the ALL predicate do?

A

It compares a value to all of the values returned by a subquery. The condition is true if the value is greater/less/equal to every value in the result set of the subquery.

46
Q

Any operation with NULL returns what?

A

NULL

47
Q

Q1) NULL + 1 = ?
Q2) NULL * 2 = ?

A

A1) NULL
A2) NULL

48
Q

NULL op X = ?

‘op’ ∈ (<, >, =, <>, ≤, ≥)
‘X’ is anything

A

NULL

49
Q

NULL = NULL evaluates to what?

A

NULL

50
Q

NULL LIKE “%mystring” evaluates to what?

A

NULL

51
Q

LEFT(NULL, 2) evaluates to what?

A

NULL

52
Q

NOT NULL evaluates to what?

A

NULL

53
Q

In a WHERE/HAVING clause, what happens if a predicate evaluates to NULL?

A

It is rejected, so it behaves like FALSE.

54
Q

In a CHECK constraint, what happens if a predicate evaluates to NULL?

A

It is accepted, so it behaves like TRUE.

55
Q

How do you directly test for a NULL value?

A

IS NULL
IS NOT NULL

ECS519U - Database Systems (12 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions