Brainscape's Knowledge GenomeTM

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


See full index

SQL > Basics > Flashcards

Basics Flashcards

1
Q

What are the different data types that can be stored in a database?

A 
Integer
Decimal
Text
CHAR
VARCHAR
DATE
TIME
Datetime
Boolean
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What does SELECT * mean?

A

Select all columns

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

How do you select columns from a table?

A

SELECT NAME, NAME2 FROM CUSTOMER;

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

Is a semicolon optional or mandatory?

A

Option, but if you want to run multiple queries at once it is necessary?

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

Is select a write command?

A

No

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

When importing multiple columns do you add a comma after the last column name?

A

No

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

How do you add a new column?

A

SELECT COL1 * 1.05 AS COL2 FROM TABLE;

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

Can you use spaces in column names?

A

No - use an underscore

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

How do you add comments?

A

– or */ for multiline comments

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

How do you concatenate text?

A

||

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

How do you filter for only particular rows?

A

WHERE

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

What are operators?

A

=
!=
>=
<=

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

= or == for where?

A

=

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

How do you get a range of dates using a where statement?

A

between

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

Is between inclusive or exclusive?

A

Inclusive

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

What are the wilcards?

A

% and _

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

What is the difference between % and _

A

% is any number of characters and _ is one

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

How are booleans stored?

A

0 false

1 true

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

Do you have to use = 1 for where clauses where you want true?

A

No but you do for false

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

How do you select null values?

A

is null

is not null

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

Are null values included in where clauses?

A

No so you must specific with an or statement

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

How can you filter data more efficiently than writing multiple or statements?

A

where ZYD IN (2010,2011,2012);

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

How do you filter on text?

A

WHERE code = ‘dfdsfsdf’

put the search term in single quotes

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

Can you use where and in on text values?

A

Yes - but they have to be in single quotes

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

How do you filter on the length of the values?

A

WHERE length(report_code) != 6

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

How do you search using wildcards?

A

WHERE report_code LIKE ‘A%’

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

How do you filter on a boolean?

A

WHERE hail = 1 AND thunder = 1

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

What are your options to include null values?

A

WHERE precipitation IS NULL OR precipitation <= 0
or
coalesce(precipitation,0) <= 0

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

What order should statements be made?

A 
SELECT
FROM
WHERE
GROUP BY
ORDER BY 
HAVING
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

How do you get the number of records?

A 
SELECT COUNT(*) as record_count
FROM station_data
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

What do you have to have in group by?

A

In select you have to have the column that you are grouping on

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

How do you sort?

A

ORDER BY year, month

33
Q

What is the default sort order?

A

ascending

34
Q

How do you get a descending sort?

A

ORDER BY year DESC, month

35
Q

What is the difference between count(*) and count(preciptitation?)

A

Count referring to a column will ignore null values

36
Q

Do you have to use as alias when using an aggregate function?

A

No but it makes life easier

37
Q

SELECT COUNT() as record_count or SELECT COUNT()

A

Select count as record_count

38
Q

What is the average code?

A

AVG(temperature)

39
Q

How do you get to 2 decimal places?

A

round(avg(temperature),2)

40
Q

What are the aggregate functions?

A

AVG
MIN
MAX
SUM

41
Q

Can you use where on aggregate values?

A

No use having

42
Q

What does where filter on?

A

Records

43
Q

How do you assign another value based on certain conditions?

A

Case statement

44
Q

What is the case structure?

A

CASE
WHEN THEN
ELSE
END as alias

45
Q

What is the indent structure of case?

A

WHEN THEN and ELSE are indented

46
Q

Can you group on a case expression?

A

Yes

47
Q

When selecting using a case expression do you need a comma at the end?

A

Yes

48
Q

How do you count the records using the case expression

A

Count(*) not count(cat)

49
Q

Do you have to have a comma at the end of the case expression?

A

Yes

50
Q

How do you get two columns of different values of T/F for a different variable without using a join?

A

Use a case statement;

SUM(CASE WHEN precipitation = 1 THEN precipitation ELSE NULL END) as tornado_precipt

51
Q

How do you do an inner join?

A

FROM CUSTOMER INNER JOIN CUSTOMER_ORDER

ON CUSTOMER.CUSTOMER_ID = CUSTOMER_ORDER.CUSTOMER_ID

52
Q

Where do you do an inner join?

A

In the from statement

53
Q

What comes first when specifying what table a column should come from?

A

table.column

54
Q

What do you do for a left join?

A

FROM CUSTOMER LEFT JOIN CUSTOMER_ORDER

ON CUSTOMER.CUSTOMER_ID = CUSTOMER_ORDER.CUSTOMER_ID

55
Q

Which table is the left table in a left join?

A

The first one

56
Q

How do you use a left join to find customers who have no orders?

A

SELECT
CUSTOMER.CUSTOMER_ID,
NAME AS CUSTOMER_NAME

FROM CUSTOMER LEFT JOIN CUSTOMER_ORDER
ON CUSTOMER.CUSTOMER_ID = CUSTOMER_ORDER.CUSTOMER_ID

WHERE ORDER_ID IS NULL

57
Q

How do you join multiple tables?

A

Do multiple joins:
FROM CUSTOMER

INNER JOIN CUSTOMER_ORDER
ON CUSTOMER_ORDER.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID

INNER JOIN PRODUCT
ON CUSTOMER_ORDER.PRODUCT_ID = PRODUCT.PRODUCT_ID

58
Q

How do you create a table?

A 
CREATE TABLE NAME (
COMPANY_ID INTEGER PRIMARY KEY
COLNAME TEXT NOT NULL
DESCRIPTION VARCHAR(10)
PRIMARY_ATT_ID
FOREIGN KEY (PRIMARY_ATT_ID) REFERENCES ATTENDEE(ATTENDEE_ID)
);
59
Q

How do you assign a primary key?

A

COLNAME INTEGER PRIMARY KEY

60
Q

How do you assign a foreign key?

A

FOREIGN KEY (NAME) REFERENCES OTHER_TABLE(NAMEINOTHERTABLE)

61
Q

When creating a table how do you make a column mandatory?

A

NOT NULL

62
Q

Does each table require a primary key?

A

Yes

63
Q

How do you autogenerate a value?

A

AUTOINCREMENT

64
Q

Do you have to have a comma after the column names in create a table?

A

yes

65
Q

Can you specify a default value when creating a table?

A

DEFAULT(VALUE)

66
Q

Can you have more than 2 foreign keys?

A

Yes

67
Q

Where do the columns go between the foreign keys?

A

After the end of the last column name and no further ones

68
Q

Do you indent on create table?

A

Yes

69
Q

If you are creating different tables in the same query do you need semicolons?

A

Yes

70
Q

How do you create a view?

A

CREATE VIEW NAME AS …..

71
Q

How do you add a record?

A

INSERT INTO ATTENDEE (FIRST_NAME, LAST_NAME)

VALUES (‘THOMAS’, ‘NIELD’)

72
Q

When inserting records into a database, is it values ‘jon’ or values(‘jon’)

A

values(‘jon’)

73
Q

Can you insert multiple records at a time?

A

Yes

values(‘j’,’k’),(‘l’,’m’),(‘k’,’j’)

74
Q

Can you insert record that has a foreign key that does not exist as a primary key in the original table?

A

No

75
Q

How do you round down?

A

FLOOR

76
Q

How do you round up?

A

CEIL()

77
Q

How do you give a subquery an alias?

A

Indent, and then AS WHATEVER after the parenteses
(SELECT NAME, MONTHS, SALARY, MONTHS * SALARY AS EARNINGS
FROM EMPLOYEE
ORDER BY EARNINGS DESC) AS EARNINGS

78
Q

How do you return only the first row?

A

LIMIT 1

SQL (1 decks)

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