Brainscape's Knowledge GenomeTM

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


See full index

SQL > Foundations > Flashcards

Foundations Flashcards

1
Q

What is SQL?

A

a programming language designed to manage data stored in relational databases

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

What is a relational database?

A

a type of database that stores and provides access to data points that are related to one another

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

What is a table?

A

a collection of data organized into rows and columns

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

What is a column?

A

a set of data values of a particular type

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

What is a row?

A

a single record in a table

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

What are common data types?

A

integer, text, date, real

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

What is an integer data type?

A

a positive or negative whole number

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

What is a text data type?

A

a text string

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

What is a date data type?

A

the date formatted as YYYY-MM-DD

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

What is a real data type?

A

a decimal value

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

What is a statement?

A

text that the database recognizes as a valid command, always ends in a semicolon (;)

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

What does CREATE TABLE do?

A

a clause also known as a command; performs specific tasks in SQL. conventionally written in capital letters.

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

What is the point of the tablename in a statement?

A

refers to the name of the table that the command is applied to

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

What is a parameter?

A

a list of columns, data types, or values that are passed to a clause as an argument

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

How do we write statements?

A

they can be written all on one line, or split up across multiple lines if it makes it easier to read

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

What does CREATE do?

A

allows us to create a new table in the database

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

What is an example of creating a table?

A

CREATE TABLE celebs (
id INTEGER,
name TEXT,
age INTEGER,
);

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

What does INSERT do?

A

it inserts a new row into a table

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

When do we use INSERT?

A

when we want to add new records

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

What is an example of an INSERT?

A

INSERT INTO celebs (id, name, age)
VALUES (1, ‘Justin Beiber’, 29);

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

What does INSERT INTO do?

A

a clause that adds the specified row or rows

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

What does SELECT do?

A

used to fetch data from a database

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

What does SELECT return?

A

a new table called the result set

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

What is an example of a SELECT statement?

A

SELECT name FROM celebs;

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

What is the use of the *?

A

a special wildcard character that we can use to refer to “all”

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

What does ALTER TABLE do?

A

adds a new column to a table

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

What is an example of an ALTER TABLE?

A

ALTER TABLE celebs
ADD COLUMN twitter_handle TEXT;

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

What does NULL mean?

A

a special value in SQL that represents missing or unknown data

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

What does CTE stand for?

A

Common Table Expression

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

What is a CTE?

A

essentially a named subquery, it functions as a virtual table that only its main query can access

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

Why do we use CTEs?

A

it helps save space (and money) in our database because we are only allowed a certain amount of helper tables

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

What does UPDATE do?

A

edits a row in a table

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

What is an example of an UPDATE statement?

A

UPDATE celebs
SET twitter_handle = ‘@taylorswift13’
WHERE id=4;

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

What does DELETE do?

A

(FROM) deletes one or more rows from a table

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

What is an example of a DELETE statement?

A

DELETE FROM celebs
WHERE twitter_handle IS NULL;

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

What does IS NULL represent?

A

a condition in SQL that returns true when the value is NULL and false otherwise

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

What are constraints?

A

add information about how a column can be used, are invoked after specifying the data type for a column

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

What is a primary key?

A

columns can be used to uniquely identify the row

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

What happens when we attempt to insert a row with an identical value to a row already in a table?

A

it will result in a constraint violation

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

What does UNIQUE represent?

A

columns have a different value for every row (tables can have many different unique columns)

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

What does NOT NULL represent?

A

a column must have a value

42
Q

What does DEFAULT represent?

A

column take an additional argument that will be the assumed value for an inserted row if the new row does not specify a value for that column

43
Q

What does querying mean?

A

retrieving information stored in a database

44
Q

What do line breaks mean?

A

they don’t mean anything specific in SQL

45
Q

What does AS represent?

A

a keyword in SQL that allows you to rename a column or table using an alias; can be anything you cant as long as they are inside of single quotes

46
Q

What is an example of an AS statement?

A

SELECT imdb_rating AS ‘IMDb
FROM movies;

47
Q

What does DISTINCT represent?

A

used tor return unique values in the output

48
Q

What is an example of a DISTINCT statement?

A

SELECT DISTINCT tools
FROM inventory;

49
Q

What does WHERE represent?

A

filters the result set to only include rows where the following condition is true

50
Q

What is an example of a WHERE statement?

A

SELECT *
FROM movies
WHERE imdb_rating > 8;

51
Q

What does LIKE represent?

A

can be a useful operator when you want to compare similar values

52
Q

Selecting all movies that start with ‘Se’ and end with ‘en’ and have exactly one character in the middle?

A

SELECT *
FROM movies
WHERE name LIKE ‘Se_en’;

53
Q

What is a FOREIGN KEY?

A

a field in one table, that refers to the PRIMARY KEY in another table

54
Q

What is % used for?

A

it is another wildcard character that can be used with LIKE; matches zero or more missing characters in the pattern.

55
Q

What is an example of using %?

A

SELECT *
FROM movies
WHERE name LIKE ‘A%’;

56
Q

What does the example ‘%a’ mean?

A

it matches all movies that end with ‘a’

57
Q

When can we not test for null values?

A

it is not possible to test for null values with comparison operators such as = and !=

58
Q

What does BETWEEN represent?

A

used in a WHERE clause to filter the result set within a certain range

59
Q

What does BETWEEN accept?

A

two values that are either numbers, text, or dates

60
Q

What is an example of a BETWEEN statement?

A

SELECT *
FROM movies
WHERE year BETWEEN 1990 and 1999;

61
Q

What values are retrieved when the values are text?

A

the result set is within an alphabetical range

62
Q

What does AND represent?

A

it combines multiple conditions in a where clause to make the result set more specific and useful

63
Q

SQL example: only return 90’s romance movies

A

SELECT *
FROM movies
WHERE year BETWEEN 1990 and 1999
AND genre = ‘romance’;

64
Q

What does OR represent?

A

it is used to combine multiple conditions and displays a row if any condition is true

65
Q

What does ORDER BY represent?

A

used to list the data in our result in a particular order; can be done either alphabetically or numerically

66
Q

What is an example of an ORDER BY expression?

A

SELECT *
FROM movies
ORDER BY name;

67
Q

What is a feature we can use with ORDER BY?

A

DESC or ASC

68
Q

What does LIMIT represent?

A

a clause that lets you specify the maximum number of rows the result set will have

69
Q

What is an example of a LIMIT query?

A

SELECT *
FROM movies
LIMIT 10;

70
Q

Where does ORDER BY have to go?

A

it always goes after WHERE (if present)

71
Q

Why is the LIMIT statement important?

A

it saves screen space and it makes our queries run faster

72
Q

What are CASE statements?

A

allows us to create different outputs (usually in the SELECT statement); SQL’s way of handling IF-THEN logic

73
Q

What is an example of a CASE statement query?

A

SELECT name,
CASE
WHEN imdb_rating > 8 THEN ‘Fantastic’
WHEN imdb_rating > 6 THEN ‘Poorly Received’
ELSE ‘Avoid at All Costs’
END as ‘New Column Name’
FROM movies;

74
Q

What are aggregates?

A

calculations performed on multiple rows of a table

75
Q

What does COUNT() do?

A

count the number of rows

76
Q

What does SUM() DO?

A

sum of the values in a column

77
Q

What does MAX/MIN() do?

A

retrieves the largest/smallest value

78
Q

What does AVG() do?

A

returns the average of the values in a column

79
Q

What does ROUND() do?

A

rounds the values in the column

80
Q

What is an example of COUNT()?

A

SELECT COUNT(*)
FROM table_name;

81
Q

What is an example of SUM()?

A

SELECT SUM(downloads)
FROM fake_apps;

82
Q

What is an example of MAX/MIN()?

A

SELECT MAX(downloads)
FROM fake_apps;

83
Q

What arguments does ROUND() take?

A

it takes two arguments: a column name, and an integer

84
Q

What is an example of ROUND()?

A

SELECT ROUND(price, 0)
FROM fake_apps;

85
Q

What does GROUP BY represent?

A

a clause in SQL that is used to aggregate functions. it is used in collaboration with the SELECT statement to arrange identical data into groups

86
Q

Where do we place GROUP BY in our queries?

A

come after any WHERE statements, but before ORDER BY or LIMIT

87
Q

What is an example of GROUP BY?

A

SELECT year, AVG(imdb_rating)
FROM movies
GROUP BY year
ORDER BY year;

88
Q

What does HAVING represent?

A

allows you to filter which groups to include and which to exclude

89
Q

When will we use the WHERE?

A

when we want to limit the results of a query based on values of the individual rows

90
Q

When will we use HAVING?

A

when we want to limit the results of a query based on an aggregate property

91
Q

What does JOIN do?

A

used to combine tables

92
Q

What is an example of a JOIN query?

A

SELECT *
FROM orders
JOIN customers
ON orders.customer_id = customers.customer_id;

93
Q

What is another name for an INNER JOIN?

A

a simple join

94
Q

What does INNER JOIN do?

A

our result only includes rows that match on our ON condition

95
Q

What does a LEFT JOIN do?

A

will keep all rows from the first table, regardless of where there is a matching row in the second table

96
Q

What do cross joins not require?

A

an ON statement

97
Q

What does UNION represent?

A

used to stack one dataset on top of the other

98
Q

What is an example of a UNION query?

A

SELECT *
FROM table_1
UNION
SELECT *
FROM table_2

99
Q

What are the rules for appending?

A

must have the same number of columns and columns must have the same data types and same order as first table

100
Q

What does dbo stand for?

A

database owner

SQL (1 decks)

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