Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

SQL

memorize.ai (lvl 286)

(0)

17 cards

()

Section 1

Preview this deck

When comparing a value, say "account_id != 6", what is the pitfall?

Front

1 / 17

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

6 years ago

Date created

Mar 1, 2020

Cards (17)

Section 1

(17 cards)

When comparing a value, say "account_id != 6", what is the pitfall?

Front

If any rows have NULL "account_id" they won't be returned

Back

What is the HAVING statement used for?

Front

Filtering grouped data

Back

What is the purpose of a foreign key constraint?

Front

The DB doesn't commit a change unless the foreign key is present in it's associated table

Back

Can you use an equal sign (=) to check for NULL values?

Front

NO

Back

When do you use the GROUP BY statement?

Front

When using an aggregate function AND you want to return a column not used in the aggregate function. Example: If you want the names of employees and the sum of their sales for the year, you would need to use GROUP BY employee. However, if you just wanted to get the total, avg, min, and max sales for all employees you wouldn't need to GROUP BY anything because all the columns in the result set are aggregate functions

Back

How do you test if a value is null?

Front

IS NULL

Back

What is a view table?

Front

A query stored in a data dictionary. It does not hold data itself. Instead, when the view table is referenced, it merges it's query with the calling query - effectively creating a subquery.

Back

When should you use a varchar vs. a char vs. a text data type for a column?

Front

Varchar for variable length strings like names Char for fixed length strings like state abbreviations Text for long strings like emails

Back

Why can't you execute aggregate functions in the WHERE clause?

Front

Filters in the WHERE clause are executed before the grouping occurs, so the server can't yet perform any functions on groups

Back

What are two important things to remember about the BETWEEN clause?

Front

1) You must put the lower bound first and upper bound second 2) Both ranges are inclusive

Back

What are the two wildcard characters for matching conditions and how are they used?

Front

1) "-" matches exactly one character 2 "%" matches any number of characters (including 0)

Back

When would you use a self-join?

Front

There is a column in the table that is both the primary key and a foreign key for another table. Example: In an employees table, you would have the id for the employee_id and potentially have a manager_id that serves as a foreign key to a manager table but also references the primary key of the employee table.

Back

How does left outer join work?

Front

It returns all the rows for the first table, even if the second table has a NULL value

Back

What is the order of operations for a SQL query?

Front

FROM WHERE GROUP BY HAVING SELECT ORDER BY LIMIT

Back

Can you include aggregate functions in the HAVING clause? Why or why not?

Front

Yes because the grouping has already been done by the time the HAVING clause is invoked.

Back

What are the three types of tables?

Front

Permanent - made using CREATE table_name Temporary - rows created with a subquery Virtual - created using the "create view" statement

Back

What is the difference between COUNT(*) and COUNT(row)

Front

COUNT(*) counts the number of rows, which includes NULL rows. COUNT(row) only counts rows that aren't NULL

Back