Brainscape's Knowledge GenomeTM

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


See full index

MySQL > Section 7 - The World of String Functions > Flashcards

Section 7 - The World of String Functions Flashcards

1
Q

Name 3 things we can do with String Functions.

A
  • Join two pieces of data together
  • Reverse a string
  • Replace all spaces in a string with a particular character
  • Conjoin a first and last name
  • Combine two pieces of data together
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What is a better method of writing (permanent) code than writing it directly in the console? Why?

A

Writing it in a file and run code from that file.

  • It’s much easier to update and to modify!
  • The formatting is a lot better!
  • There is nice syntax highlighting!
  • We can run them when we’re ready, not just instantly.
  • Files are better for projects and more serious needs. Files allow us to share much more easily.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What’s the magic syntax for running a file?

A

source.sql

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

What does the source.sql command do?

A

This runs the mentioned file.

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

Why does it matter what directory you are in when you initialize the mysql cli?

A
  • If the file you’re running is in the parent directory of the file you’re in, you’ll need to navigate to the whole file path.
  • Otherwise, if not the above, MySQL will just look in the folder you’re currently in, and won’t be able to find the file.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What does the
ls
command do?

A

This checks the files in your current directory.

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

What command checks the files in your current directory?

A

ls

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

Is it a good idea to memorize all the string functions possible in MySQL? Why?

A

NO.

There’s a million for them, and you’ll likely only be using around 5 at a time.

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

What is the function for combining data?

A

CONCAT

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

What does CONCAT do?

A

It combines data.

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

TRUE or FALSE:

The CONCAT function combines data.

A

TRUE

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

CONCAT combines pieces of data (strings).

Is the above TRUE or FALSE?

A

TRUE

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

What is the correct syntax for using the CONCAT function?

A

SELECT CONCAT(‘This will create, ‘ ‘, ‘a full sentence.’);

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

Is this correct syntax?

CONCAT(‘Is this correct,’ ‘ ‘, ‘syntax?’);

Why or why not?

A

NO.

You can’t go around CONCAT-ing willy nilly. You need to let the system know what you’re selecting.

It should be written as:

SELECT CONCAT(‘Is this correct,’ ‘ ‘, ‘syntax?’);

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

Write a function that does the following:

Prints the author_fname + author_lname columns into a complete name, from a table named bookshop.

Make sure there’s a space between author_fname and author_lname.

A

SELECT CONCAT(author_fname, ‘ ‘, author_lname) FROM bookshop;

You can also format it this way, for easier readability:

SELECT
CONCAT(author_fname, ‘ ‘, author_lname)
FROM bookshop;

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

Does CONCAT-ing change the data? Or does it only change the way it’s printed out?

A

No, CONCAT does not change the data. It only changes the way it’s printed out.

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

Write a function that concatenates the president_fname and president_lname columns, and assigns them an alias named ‘Dead Presidents’, from a table called world_leaders.

A

SELECT CONCAT(president_fname, ‘ ‘, president_lname) AS ‘Dead Presidents’ FROM world_leaders;

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

Write the following function:

  • Print column date_released to the left of a concatenated column featuring actor_firstinitial and actor_lastname. Actor_firstinitial and actor_lastname should be joined together by a hyphen.
  • The table name is imdbd.
  • Assign the alias “Released Date” to date_released and “Actor Name” to the concatenated actor first initial / actor last name column.
A

SELECT date_released AS ‘Released Date’,
CONCAT(actor_firstinitial, ‘-‘, actor_lastname) AS
‘Actor Name’
FROM imdbd;

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

What does the CONCAT_WS function do?

A

It concatenates the same symbol/letter/number/etc between each of your fields of data.
For example, you could use this to add a hyphen between each of 10+ different columns of data.

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

Write a CONCAT function that adds a hyphen between column names first_name, middle_name, and last_name.

The function should add both hyphens at once. You should NOT have to type two separate hyphens in your function.

The table name is middle_management_employees.

Hint: Use a particular CONCAT function.

A

SELECT CONCAT_WS(‘-‘, first_name, middle_name, last_name) FROM middle_management_employees;

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

In the CONCAT_WS function, where do you put the character/text/string/symbol that you want repeated before other symbols?

Give an example in practice.

A

You put it first in the lineup. For example:

SELECT CONCAT_WS(‘firstinlineup’, ‘first name’, ‘middle name’, ‘last name’);

The ‘firstinlineup’ text would be inserted between first name and middle name, and between middle and last name.

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

When using the CONCAT_WS function, does the function insert the repeated symbol/character/string/etc before and after the other data, or only between?

A

It only inserts it between.

For example:

SELECT CONCAT_WS(‘$’, ‘first name’, ‘last name’);

The above would print as “first name$last name”.

It would NOT print as $first name$last name$.

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

What does SUBSTRING do?

A

It allows us to work with part of a string.

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

What is the function for working with only part of a string?

A

SUBSTRING

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

Name a few things the function SUBSTRING could help us print.

A
  • The first character of a string
  • The middle 10 characters of the string
  • The last letter of the string
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

Where do MySQL’s indices start? At what character?

A

MySQL’s indices start at 1 (one) NOT 0 (zero) as many programming languages do.

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

TRUE or FALSE:

MySQL’s indices start at 1.

A

TRUE

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

TRUE or FALSE:

MYSQL’s indices start at zero.

A

FALSE

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

What would we return if we ran this code?

SELECT SUBSTRING(‘Hello World’, 1, 4);

A

It would return “Hell”.

    Eg it would return characters 1-4.
30
Q

What would we return if we ran this code?

SELECT SUBSTRING(‘Hello World’, 0, 4);

A

It would return nothing, kind of like an error, since MySQL indices start at 1 and not at 0.

31
Q

What would we return if we ran this code?

SELECT SUBSTRING(‘My Name Pete’, 8, 12);

A

It would return “Pete”.

Note that spaces count as an index/indice.

32
Q

TRUE or FALSE:

Spaces do NOT count as an index/indice.

A

FALSE.

Spaces DO count as an index/indice.

33
Q

What would happen if we ran this code?

SELECT SUBSTRING(‘Hello World’, 7);

Why?

A

We’d return “World”.

Why?

If you pass through only parameter, it’ll start from whatever the index is and go to the end. (The end is the parameter we gave it, index 7.)

34
Q

What happens when you use a negative number when selecting SUBSTRINGs?

A

It counts from the end. (It counts from the right to the left.)

For example:

SELECT SUBSTRING(‘Hello World’, -3);

The above would return “rld”.

35
Q

What would this function print/return?

SELECT SUBSTRING(‘Jeremy Irons’, -5, 4);

A

It would return “Iron”.

Count back 5: s-n-o-r-I, then count forward 4: Iron.

36
Q

Write this function:

Write a function that prints the first 9 characters of every book title (column name = book_title) from a table named ‘book_shop’.

A

SELECT SUBSTRING(book_title, 1, 9) FROM book_shop;

37
Q

What will happen if we run the below function?

SELECT SUBSTRING (‘Hello World’, 1, 4);

A

Answer:

You’d return an error, since there’s a space between “SUBSTRING” and the parentheses.

38
Q

What is the abbreviation for SUBSTRING?

A

It’s SUBSTR.

39
Q

Does using SUBSTR work the same as using SUBSTRING?

A

YES.

40
Q

Write a function that:

  • Prints the first 10 characters of each book title from the column title “book_title”
  • Prints “…” after the first 10 characters of each book title
  • The table name is book_shop.
A

SELECT
CONCAT(SUBSTRING(book_title, 1, 10), ‘…’)
FROM books;

41
Q

What would this function do?

SELECT CONCAT(SUBSTRING(released_year, -2, 2), ‘A.D.’) FROM book_shop;

A

This would concatenate the last 2 digits of the released_year column (from the book_shop table) with the characters “A.D.”

For example, it may print 19AD for a book released in 2019.

42
Q

What does the REPLACE function do?

A

It replaces part of strings.

43
Q

Give 2 examples of what the REPLACE function can do.

A
  • Replace ever occurrence of a certain character

- Replace all spaces with commas, dashes, etc.

44
Q

Give an example of REPLACE syntax in practice.

A

SELECT REPLACE(‘Hello World’, ‘Hell’, ‘&$%$’);

This would return “&$%$ World”.

The first string is what is being referenced. The second string is what is being replaced. The 3rd string is what’s replacing the 2nd string.

45
Q

Write a REPLACE function that replaces all e’s in a string with 3’s.

A

SELECT REPLACE(‘Augies Leet Speak Example’, ‘e’, 3);

This would return “Augi3s L33t Sp3ak Exampl3”.

46
Q

TRUE or FALSE:

The REPLACE function is not case sensitive.

A

Answer: FALSE

47
Q

TRUE or FALSE

The REPLACE function is case sensitive.

A

Answer: TRUE

48
Q

What would we return in the below example?

SELECT REPLACE(‘Hello World’, ‘ll’, 7);

A

This is a bit of a trick question. Note that we are only telling it to replace double l’s [ll], not single l’s.

Because of this, it will only replace the double ll’s in Hello, not the single l in World.

Thus, we’ll return “He7o World”.

Note also that the double l is replaced with a SINGLE 7, because, again, we are replacing the double l’s [ll] as a unit.

49
Q

What would we return in the below example?

SELECT REPLACE(‘HellO World’, ‘o’ ‘*’);

A

We would return:

“HellO W*rld”.

Why is that?

Because the RETURN function is case sensitive.

50
Q

Can REPLACE be used to replace multiple thing / recurring things?

A

YES

51
Q

What would the below example return?

SELECT REPLACE(‘beef-jerky slurpees chocolate breathmints’ ‘ ‘, ‘ and’);

A

We would return:

“beef-jerky and slurpees and chocolate and breathmints”

(Not the space in front of our “and” in the query.)

52
Q

What is the shortcut for commenting something out in MySQL?

A

cmd + / - MAC

ctrl + / - PC

53
Q

What character(s) do you use to comment something out in MySQL?

A

// (two slashes in front of what you want commented out)

54
Q

What function lets you print a string in the opposite order?

A

REVERSE

55
Q

Write a function that prints the string “Hello World” backwards.

A

SELECT REVERSE(‘Hello World’)

This returns “dlroW olleH”

56
Q

What would this function do?

SELECT REVERSE(name) AS ‘Reversed first name’ FROM people;

A

This would return the name column, printed in reverse order, with the title “Reversed first name” from the people table.

For instance, the name “Cooper” in the name column would print back as “repooC”.

57
Q

Write a function that prints oofdaadoof from the following strings:

“oofda” “oofda”

A

SELECT CONCAT(‘oofda’, REVERSE(‘oofda’));

58
Q

What does the CHAR_LENGTH function do?

A

It tells you how many characters are in a given string.

59
Q

What function tells you how many characters are in a given string?

A

CHAR_LENGTH

60
Q

Write a function that returns the length of every author’s first name from the author_fname column from a table named book_shop.

A

SELECT CHAR_LENGTH(author_fname) FROM book_shop;

61
Q

What does the UPPER function do?

A

It changes a string’s case to upper case.

62
Q

What function do you use to change a string’s case to upper case?

A

SELECT UPPER(‘string goes here’);

63
Q

What function would you use to change a string’s case to lower case?

A

SELECT LOWER(‘relevant string goes here’);

64
Q

Write a function that returns the string “Hello World” in all capital letters.

A

SELECT UPPER(‘Hello World’);

65
Q

Write a function that returns the string “WRITE LIKE E.E. CUMMINGS” in all lowercase letters.

A

SELECT LOWER(‘WRITE LIKE E.E. CUMMINGS’);

66
Q

Print back every student_firstname in all capitals from a table called students. Assign an alias to the results “Student NAMES”.

A

SELECT UPPER(student_firstname) AS ‘Student NAMES’

67
Q

Is order important when combining certain string functions?

A

YES. Certain string functions will NOT work if the order of the functions is not correct.

68
Q

Write a function that returns “Hello World” reversed and in uppercase.

A 
SELECT UPPER(REVERSE('Hello World');
     OR
SELECT REVERSE(UPPER('Hello World');
69
Q

What does this function print out?

SELECT REPLACE(CONCAT(‘Dogs’, ‘ ‘, ‘Are’, ‘ ‘, ‘SLOBBERY’), ‘ ‘, ‘-‘);

A

“Dogs-Are-SLOBBERY”

70
Q

Write the following function:

  • Table name is book_shop
  • Column name is title
  • Write a function that returns all spaces in book titles with $ signs instead of spaces.
A

SELECT REPLACE(title, ‘ ‘, ‘$’) FROM book_shop;

MySQL (9 decks)

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