Brainscape's Knowledge GenomeTM

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


See full index

MYSQL > Retrieving data from a single table > Flashcards

Retrieving data from a single table Flashcards

1
Q

True or False

SQL is case sensitive?

A

False

SQL is not case sensitive but keywords are usually CAPITALIZED.

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

USE

A

to select what database to query and must be terminated ;

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

SELECT

A

which columns to query or * for all columns

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

FROM

A

what table to query

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

MYSQL how to execute a high-lighted sub query (Windows)

A

shift + ctrl + enter

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

WHERE

A

used to filter the data on a conditional basis

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

True or False

This SQL query is in the correct order of operations?

USE

SELECT

FROM

WHERE

ORDER BY

A

True

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

ORDER BY

A

determines sort order ascending is the default

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

How do you make a comment in SQL

A

– this is a comment in SQL crtl + /

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

True or False

in SQL the FROM, WHERE, and ORDER BY clauses are optional?

A

True

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

What does this code produce?

USE

SELECT 1, 2

A

creates two columns 1 and 2

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

True or False

The order doesn’t matter in SQL?

A

False

order matters when querying a database

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

True or False

White space is ignored in SQL?

A

True

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

This code does what?

SELECT
last_name,
first_name,
points,
points + 10
FROM customers

A

selects columns last_name, first_name, and points columns from customers table and adds 10 points to the points column values then creates a column points + 10 to hold those values.

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

How do you create an alias in SQL

A

AS

FROM customers c

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

How would you change the order of operation in an expression?

A

with parathesis (points + 10) * 100

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

True or False

It’s a good idea to use an alias on SQL arithmetic expressions?

A

True

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

In SQL this “discount factor” is seen as a

A

string

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

SELECT DISTINCT does what?

A

removes or ignores duplicated values

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

What is the WHERE clause doing in this query?

  • *SELECT** *
  • *FROM** Customers
  • *WHERE** points > 3000
A

filtering customers whose points are greater than 3000

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

What are the SQL comparison operators?

A

> greater than

>= greater than or equal

< less than

<= less than or equal

= equal

!=, <> not equal

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

What is the WHERE clause doing in this query?

SELECT *

FROM Customers

WHERE state = ‘VA’

A

filtering customers by the state of ‘VA’

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

What is the WHERE clause doing in this query?

  • *SELECT** *
  • *FROM** customers
  • *WHERE** state = ! ‘VA’
A

filtering customers that are NOT in the state of ‘VA’

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

What is the WHERE clause doing in this query?

  • *SELECT** *
  • *FROM** customers
  • *WHERE** birth_date > ‘1990-01-01’
A

filtering customer whose birth date is greater than 01-01-1990

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

When comparing multiple conditions in SQL what’s needed?

A

AND, OR, NOT operators

26
Q

How many conditions does this SQL query possess?

  • *SELECT** *
  • *FROM** Customers
  • *WHERE** birth_date > ‘1990-01-01’ AND points > 1000
A

there are two conditions (see the WHERE clause)

27
Q

True or False

In SQL the AND operator is always executed first as a first-order operator?

A

True

28
Q

In SQL the NOT clause does what?

  • *SELECT** *
  • *FROM** Customers
  • *WHERE NOT** birth_date > ‘1990-01-01’ OR points > 1000
A

negates a condition

> negates to <

OR negates to AND

> negates to <

29
Q

In SQL arithmetic expressions are?

A

simple calculations performed within a query

  • *SELECT** *
  • *FROM** order_items
  • *WHERE** order_id = 6 AND (quantity * unit_price > 30)
30
Q

Why can’t you do this in SQL?

WHERE state = ‘VA’ OR‘GA’ OR‘FL’?

A

the conditions are boolean values and OR‘GA’ OR‘FL’ are string values

31
Q

How would you refactor this query?

  • *SELECT** *
  • *FROM** Customers
  • *WHERE** state = ‘VA’ OR state = ‘GA’ OR state = ‘FL’
A
  • *SELECT** *
  • *FROM** Customers
  • *WHERE** state IN (‘VA’, ‘GA’, ‘FL’)

similiar to the python membership operator IN

32
Q

What is this SQL query doing?

  • *SELECT** *
  • *FROM** Customers
  • *WHERE** state NOT IN (‘VA’, ‘FL’, ‘GA’)
A

selecting all records from the Customers table and filtering out states VA, GA, and FL

33
Q

Refactor this code

  • *SELECT** *
  • *FROM** customers
  • *WHERE** points >= 1000 AND points <= 3000
A
  • *SELECT** *
  • *FROM** customers
  • *WHERE** points BETWEEN 1000 AND 3000
34
Q

What type of data type is the birth_date column?

  • *SELECT** *
  • *FROM** customers
  • *WHERE** birth_date BETWEEN‘1990-01-01’ AND‘2000-01-01’
A

string

35
Q

This query is returning what?

  • *SELECT** *
  • *FROM** customers
  • *WHERE** last_name LIKE‘%y’
A

all the last names from the customer’s table that end in a y

36
Q

This query is returning what?

  • *SELECT** *
  • *FROM** customers
  • *WHERE** last_name LIKE‘%y%’
A

all customers where the last name has a letter y in it

37
Q

In MySQL following the LIKE statement what does ‘-‘ represent in a string?

A

a single character

38
Q

what is any number of characters in MySql regex?

A

%

39
Q

what is a represents a single character?

A

_

40
Q

What is this query returning?

  • *SELECT** *
  • *FROM** customers
  • *WHERE** phone NOT LIKE‘%9’
A

all customers whose phone numbers that do not end in 9

41
Q

What MySQL statement is used for searching string values?

A

REGEXP regular expression

42
Q

True or False

This:
WHERE last_name LIKE‘%field%’

is comparable to this:
WHERE last_name REGEXP‘field’

A

True

43
Q

This ^ represents what in a MySQL REGEXP

A

the beginning of a string

‘^waters’

44
Q

This $ represents what in a MySQL REGEXP

A

the end of a string

‘field$’
would return Brushfield.

45
Q

This | represents what in a MySQL REGEXP

A

a separator to perform multiple string searches simultaneously

WHERE last_name REGEXP‘field | mac | rose’

46
Q

True or False

This is ‘legal’ in MySQL

  • *SELECT** *
  • *FROM** customers
  • *WHERE** last_name REGEXP‘field | mac | rose’
A

False

no spacing allowed between ‘field|mac|rose’

47
Q

What is this query returning?

  • *SELECT** *
  • *FROM** customers
  • *WHERE** last_name REGEXP‘[gim]e’
A

all customers whose last name ends with e but have a g, i, or m that precedes the e in it.
ge, ie, me

48
Q

True or False

you can use a range of values in a MySQL REGEXP much like:
WHERE last_name REGEXP‘[a-h]e’

A

True

49
Q

List some MySQL REGEXP?

A

^ beginning

$ end

| logical or (called a pipe)

[abcd]

[a-f] range

50
Q

This query is returning what?

  • *SELECT** *
  • *FROM** customers
  • *WHERE** first_name REGEXP‘elka|ambur’
A

all customers whose first name are Elka or Ambur

51
Q

This query is returning what?

  • *SELECT** *
  • *FROM** customers
  • *WHERE** last_name REGEXP‘ey$|on$’
A

all customers whose last name ends in ey or on.

52
Q

This query is returning what?

  • *SELECT** *
  • *FROM** customers
  • *WHERE** last_name REGEXP ‘^my|se’
A

all customers whose last name starts with my or contains se.

53
Q

This query is returning what?

  • *SELECT** *
  • *FROM** customers
  • *WHERE** last_name REGEXP‘b[ru]’
A

all customers whose last name has a b followed by r or u

54
Q

This query is returning what?

  • *SELECT** *
  • *FROM** customers
  • *WHERE** phone IS NULL
A

all the customers whose phones values are missing (null)

55
Q

What is this query returning?

  • *SELECT** *
  • *FROM** orders
  • *WHERE** shipped_date IS NULL
A

all orders that haven’t shipped

56
Q

True or False

the ORDER BY clause changes a table’s default (primary key) sorting order

A

True

by ascending order unless DESC clause is used.

57
Q

True or False

This query is legal in MySQL

  • *SELECT** first_name, last_name
  • *FROM** customers
  • *ORDER BY** birth_date
A

True

ordering by a column not in the selection query!

58
Q

True or False

Sorting data by columns positions is a good practice?

  • *SELECT** first_name, last_name
  • *FROM** customers
  • *ORDER** BY 1, 2
A

False

if another column is added to the table, changing the order, you could be screwing yourself!

59
Q

This query is returning what?

  • *SELECT** *
  • *FROM** customers
  • *ORDER BY** points DESC
  • *LIMIT** 3
A

the top three customers in desc order

60
Q

True or False

the LIMIT clause should be the last line in a query if used.

A

True

limits the # of records to return

MYSQL (19 decks)

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