Brainscape's Knowledge GenomeTM

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


See full index

SQL > Basic SQL > Flashcards

Basic SQL Flashcards

1
Q

Is it necessary to use capitalized commands when using SQL?

A

No, SQL will understand these commands if you type them in lowercase. Capitalizing commands is simply a convention that makes queries easier to read.

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

Does SQL care about spacing?

A

SQL treats one space, multiple spaces, or a line break as being the same thing.

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

If you want to have spaces in column names, you don’t need to refer to those columns in double quotes. True/False?

A

False. If you want to have spaces in column names, you need to always refer to those columns in double quotes.

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

If you’d like your results to look a bit more presentable, you can rename columns to include spaces. How can we do this? What’s something we need to pay attention to when naming the column? Code

A

SELECT west AS “West Region” # IT HAS TO BE IN DOUBLE QUOTATION
FROM tutorial.us_housing_units

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

The clauses always need to be in this order: SELECT, FROM, WHERE. True/False?

A

True

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

How can we use LIMIT command in SQL? Code

A

SELECT *
FROM tutorial.us_housing_units
LIMIT 100

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

Do the comparison operations in SQL work on non-numerical data?

A

All of the comparison operators work on non-numerical data as well.

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

What should we do to the non-numerical values of a column before using a comparison operator with them?

A

SQL uses single quotes to reference column values when using them with a comparison operator

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

How do “>” and “<” operate when used with a non-numerical value?

A

They filter based on alphabetical order

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

SQL doesn’t consider ‘Ja’ to be greater than ‘J’. True/False?

A

False, SQL considers ‘Ja’ to be greater than ‘J’ because it has an extra letter.

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

Write a query that only shows rows for which the month_name starts with the letter “N” or an earlier letter in the alphabet.

A

SELECT *
FROM tutorial.us_housing_units
WHERE month_name < ‘o’ #MUST BE SINGLE QUOTATION

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

How do arithmetic operators work in SQL? Row-wise or column-wise?

A

Column-wise (across columns)

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

What should we do if we need to do arithmetic across rows? (values in a column)

A

you’ll need to use aggregate functions

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

You can chain arithmetic functions, including both column names and actual numbers. True/False?

A

True.
SELECT year,
month,
west,
south,
west + south - 4 * year AS nonsense_column
FROM tutorial.us_housing_units

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

What are the columns that contain the arithmetic functions called?

A

Derived columns

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

As in Excel, you can use parentheses in SQL to manage the order of operations. True/False?

A

True

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

What is the use of logical operators in SQL?

A

Logical operators allow you to use multiple comparison operators in one query.

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

What are the logical operators of SQL? What do they do?

A

LIKE allows you to match similar values, instead of exact values.
IN allows you to specify a list of values you’d like to include.
BETWEEN allows you to select only rows within a certain range.
IS NULL allows you to select rows that contain no data in a given column.
AND allows you to select only rows that satisfy two conditions.
OR allows you to select rows that satisfy either of two conditions.
NOT allows you to select rows that do not match a certain condition.

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

What does this code do?
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE “group” LIKE ‘Snoop%’

A

the results from the Billboard Music Charts dataset will include rows for which “group” starts with “Snoop” and is followed by any number and selection of characters.

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

Why in this code, group is in double quotation?
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE “group” LIKE ‘Snoop%’

A

“group” appears in quotations above because GROUP/group is actually the name of a function in SQL. The double quotes (as opposed to single: ‘) are a way of indicating that you are referring to the column name “group”, not the SQL function. In general, putting double quotes around a word or phrase will indicate that you are referring to that column name.

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

What is % character in this code representing? What is this character called?
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE “group” LIKE ‘Snoop%’

A

The % used above represents any character or set of characters. In this case, % is referred to as a “wildcard.”

22
Q

What do we use instead of LIKE to remove case sensitivity?

A

ILIKE

23
Q

What do we use instead of LIKE to remove case sensitivity?

A

ILIKE

24
Q

How can we show an individual character when using LIKE? Code

A

You can also use _ (a single underscore) to substitute for an individual character.
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE artist ILIKE ‘dr_ke’

25
Q

Write a query that returns all rows for which Ludacris was a member of the group.

A

SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE “group” ilike ‘%ludacris%’

26
Q

What does LIKE do in SQL?

A

LIKE is a logical operator in SQL that allows you to match on similar values rather than exact ones.

27
Q

What does IN do in SQL?

A

IN is a logical operator in SQL that allows you to specify a list of values that you’d like to include in the results.

28
Q

What does below code do?
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE year_rank IN (1, 2, 3)

A

It will return results for which the year_rank column is equal to one of the values in the list

29
Q

How can we use non-numerical data when using IN command?

A

They need to go inside single quotes:
…
WHERE artist IN (‘Taylor Swift’, ‘Usher’, ‘Ludacris’)

30
Q

What does BETWEEN do?

A

BETWEEN is a logical operator in SQL that allows you to select only rows that are within a specific range.

31
Q

What does BETWEEN have to be paired with?

A

It has to be paired with the AND operator

32
Q

Does BETWEEN include the range bounds?

A

Yes. BETWEEN includes the range bounds that you specify in the query, in addition to the values between them.

33
Q

What does IS NULL do?

A

IS NULL is a logical operator in SQL that allows you to exclude rows with missing data from your results.

34
Q

What is below code equivalent to?
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE year_rank BETWEEN 5 AND 10

A

SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE year_rank >= 5 AND year_rank <= 10

35
Q

The difference between a cell having no data and a cell containing a space isn’t meaningful in Excel. In SQL, the implications can be pretty serious.True/False?

A

True

36
Q

Does WHERE artist = NULL work? Explain your answer

A

It will not work—you can’t perform arithmetic on null values.
WHERE artist IS NULL/ISNULL

37
Q

What does AND do?

A

AND is a logical operator in SQL that allows you to select only rows that satisfy two or more conditions all at the same time.

38
Q

You can use SQL’s AND operator with additional AND statements or any other comparison operator, as many times as you want. True/False?

A

True

39
Q

Write a query that surfaces all rows for top-10 hits for which Ludacris is part of the Group.

A

WHERE year_rank <= 10
AND “group” ILIKE ‘%ludacris%’

40
Q

What does OR do?

A

OR is a logical operator in SQL that allows you to select rows that satisfy either of two or more conditions.

41
Q

How can we combine AND with OR?

A

You can combine AND with OR using parenthesis.

42
Q

Does
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE year_rank <= 10
AND (“group” ILIKE ‘%katy perry%’ OR “group” ILIKE ‘%bon jovi%’)
Return the same result as:
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE year_rank <= 10
AND “group” ILIKE ‘%katy perry%’ OR “group” ILIKE ‘%bon jovi%’

A

No

43
Q

What does NOT do?

A

NOT is a logical operator in SQL that you can put before any conditional statement to select rows for which that statement is false.

44
Q

What other logical operator is NOT used with commonly?

A

NOT is commonly used with LIKE

45
Q

NOT is also frequently used to identify non-null rows, how is it used? Code

A

SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE year = 2013
AND artist IS NOT NULL

46
Q

What does ORDER BY do?

A

The ORDER BY clause allows you to reorder your results based on the data in one or more columns.

47
Q

What does this code do?
SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE year_rank <= 3
ORDER BY year DESC, year_rank

A

This example query makes the most recent years come first but orders top-ranks songs before lower-ranked songs

48
Q

How can we make a comment?

A

You can use– (two dashes) to comment out everything to the right of them on a given line

49
Q

How can we make multiple line comments?

A

/* Here’s a comment so long and descriptive that
it could only fit on multiple lines. Fortunately,
it, too, will not affect how this code runs. */

50
Q

How do you write O’NEIL as a string, since it has a single quotation within it?

A

We write it like this: ‘O’‘Neil’

51
Q

What’s the order of clause execution in SQL?

A
  1. FROM clause
  2. ON clause
  3. JOIN clause
  4. WHERE clause
  5. GROUP BY clause
  6. WITH CUBE or WITH ROLLUP clause
  7. HAVING clause
  8. SELECT clause
  9. DISTINCT clause
  10. ORDER BY clause
  11. TOP clause

ME: it’s table setting clauses, conditional clauses, column selection clauses, ordering clauses

52
Q

When having nested select clauses, which one does SQL execute first?

A

In SQL, when having nested select clauses, the innermost subquery is executed first, followed by the next level subquery, and so on until the outermost query is executed.

SQL (5 decks)

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