Brainscape's Knowledge GenomeTM

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


See full index

DS backup 4/22/21 > SQL > Flashcards

SQL Flashcards

1
Q

SQL: Datatype for an id

A

INTEGER PRIMARY KEY

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

SQL: How to create a table called “mytable” with an id and columns a, b and c, which are text, integer, text respectively.

A

CREATE TABLE mytable (id INTEGER PRIMARY KEY, a TEXT, b INTEGER, c TEXT);

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

SQL: Datatype for an integer

A

INTEGER

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

SQL: Datatype for a string

A

TEXT

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

SQL: How to insert a row with ints and strings into table “mytable”

A

INSERT INTO mytable VALUES (“yes”, 1, “no”, 2);

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

SQL: What goes at the end of every line?

A

;

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

SQL: How to select all columns, or the entire table, from table “mytable”

A

SELECT * FROM mytable;

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

SQL: How to select column “a” from table “mytable”

A

SELECT a FROM mytable;

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

SQL: How to select columns “a” and “c” from table “mytable”

A

SELECT a, c FROM mytable;

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

SQL: How to select all columns from table “mytable” and order by column “b”

A

SELECT * FROM mytable ORDER BY b;

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

SQL: Does ordering by a column return with that column in ascending or descending order?

A

Ascending

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

SQL: How to select all columns from table “mytable”, but only rows where column “b” is greater than 5?

A

SELECT * FROM mytable WHERE b > 5;

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

SQL: How to get sum of column b from table “mytable”

A

SELECT SUM(b) FROM mytable;

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

SQL: How to get max of column b from table “mytable”

A

SELECT MAX(b) FROM mytable;

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

SQL: How to get table “mytable” grouped by column “c”, with summary statistics of the max and sum of column “b”, while also displaying the value from column “c” corresponding to each group?

A

SELECT c, MAX(b), SUM(b) FROM mytable GROUP BY c;

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

SQL: Summary statistic for total number of rows in a group

A

COUNT(*) (could also use a particular column)

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

SQL: Summary statistic for average value of column “b” in a group

A

AVG(b)

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

SQL: What pitfall can happen when you include a non-aggregated column in a group by, and how do you avoid this?

A

SQL displays the first value of the row for that group, misleading when there are multiple rows. To avoid this, only include the column you’re grouping by as a non-aggregated column, and no others.

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

SQL: How to combine multiple logicals?

A

AND or OR

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

SQL: How to select rows from table “mytable” where column “b” has a value of either “warriors,” “bucks,” or “raptors”?

A

Select * From mytable Where b in (“warriors”, “bucks”, “raptors”);

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

SQL: How to select rows from table “mytable” where column “b” is only one of the values present in column “b” for a different table called “other_table”?

A

Select * From mytable Where b in
(Select b From other_table);

(I feel like this could be optimized…)

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

SQL: How to revers the value of any logical?

A

NOT

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

SQL: Describe the difference between WHERE and HAVING

A

WHERE filters the rows from the table you’re querying to only include some of them in the results of your query.

HAVING filters out some of the rows in your aggregated data table. So when you are using a Group By, HAVING allows you to filter the results of the Group By based on some criterion.

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

SQL: How do you get the sum of column “b” for table “mytable” when grouped by column “c”, and only include groups where the sum of column “b” exceeds 50?

A

Select c, SUM(b) as sum_b From my_table
Group By c
Having sum_b > 50;

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

SQL: How do you get the sum of column “b” for table “mytable” when grouped by column “c”, but have your sum only incorporate rows from “mytable” where the “b” value was nonnegative?

A

Select c, SUM(b) From my_table
Where b >= 0
Group By c;

26
Q

SQL: How do you add a custom, calculated row to the data table resulting from your query?

A

Include a CASE statement in your Select statement, so:

Select a, b, CASE….. From *

27
Q

SQL: What is the syntax to write a case statement within your select statement, and then group by the custom field in your case statement?

A 
SELECT COUNT(*),
CASE
WHEN a > 5 THEN "category 1"
WHEN a > 3 THEN "category 2"
WHEN a > 1 THEN "category 3"
ELSE "category 4"
END AS "custom_field"
FROM mytable
GROUP BY custom_field;
28
Q

SQL: Why do we often split one table into several?

A

To avoid information being repeated multiple times in several rows, as when columns are related.

29
Q

SQL: How do we make sure we can join two tables containing related information?

A

We include a unique identifier for each row of every table.

30
Q

SQL: What do we want to avoid when we have multiple tables where rows are the same entity?

A

Repeated columns

31
Q

SQL: What is an inner join?

A

When selecting based on a common column, say id, you only get the ids that are present in both tables.

32
Q

SQL: How do I do an inner join on id, a column present in both tables “students” and “student_grades”?

A

SELECT * FROM students
JOIN student_grades
ON students.id = student_grades.id;

33
Q

SQL: How do I do an inner join on id, a column present in both tables “students” and “student_grades”, but only receive the “name” column from students and the “letter_grade” column from student_grades? Do not assume that these column names only appear in one of the tables.

A

SELECT students.name, student_grades.letter_grade FROM students
JOIN student_grades
ON students.id = student_grades.id;

34
Q

SQL: How do I do an inner join on id, a column present in both tables “students” and “student_grades”, but only get the (combined) rows where the “grade” field from the “student_grades” table is either “A” or “B”?

A

SELECT * FROM students
JOIN student_grades
ON students.id = student_grades.id
Where student_grades.grade In (“A”,”B”);

35
Q

SQL: What is a left outer join?

A

It joins the rows from 2 different tables based on a common field, but it includes ALL of the values of the field from the “left” table, or the first table included in the join statement. If any of those values aren’t present in the “right” table, the values in the right table’s columns are filled in as NULL.

36
Q

SQL: Why, at a high level, are right outer joins not often used, and not supported in all SQL frameworks?

A

A right outer join can be very easily switched to a left outer join by switching the order of the tables, and left outer join is more common by convention.

37
Q

SQL: What is a full outer join?

A

It joins the rows from 2 different tables based on a common field, but it includes ALL of the values of the field from BOTH tables. If any of those values aren’t present in one of the tables, the info of that table’s columns is filled in as NULL for that value’s row.

38
Q

SQL: How do you do a left outer join on id, a column present in both tables “students” and “student_grades”, with “students” being on the left?

A

Select *
From students
LEFT OUTER JOIN student_grades;

39
Q

SQL: When ordering by column a, how do you make it in descending order

A

ORDER BY a DESC

40
Q

SQL: At a high level, why would we want to do a self join?

A

If the items in each row have relationships to other items, and we want info about both items in a pair of items. For example, maybe we have a table where each row is a student, and each student has an id, but one column is the id of that student’s partner. We might want a table with, say, the emails of both partners in the pair.

41
Q

SQL: At a high level, what issue arises when doing a self join, and how do we fix it?

A

When selecting columns of the original table to show in our joined table, we need to decide from which of our two original-table-instances each column will come. (So whether each columns will get its value from the first item in the item pair, or the second). But the tables have the same name, so we need to give one of the two instances an alias in order to differentiate them.

42
Q

SQL: Say I have a table “students” where each student has an id, and one of the columns, “buddy_id,” is the id of their buddy. How do I do a self join that gives the “name” field of the first table instance, and the “email” field of the second table instance aliased as “buddies”?

A

Select students.name, buddies.email
From students
Join students buddies
On students.buddy_id = buddies.id;

43
Q

SQL: Can you do more than one join under the same select statement?

A

Yes

44
Q

SQL: Why would you want to do more than one join under the same select statement?

A

Say you have a table of object pairs, and another table that has information on each object. If you wanted a table that has pairs as a row, and information on each of the objects in the pair, you need 2 joins: one for the object-info table instance for the first object in the pair, and a second for the second instance which pertains to the second object.

45
Q

SQL: Say you have a table “student_pairs”, with the ids of each student in a pair in columns “studentID_a” and “studentID_b”, and you have a table “students”, which has a field “email”. How do you get a table with the emails of both students in each pair (so a row is a pair of students)?

A 
Select a.email, b.email
From student_pairs
Join students A 
On student_pairs.studentID_a = A.id
Join students B
On student_pairs.studentID_b = B.id;
46
Q

SQL: Can you ask most SQL engines how they plan to execute a query, in order to optimize it?

A

Yes

I just want to remember this exists

47
Q

SQL: At a very high level, why would you want to query planning or optimization?

A

When your SQL engine will execute your query in a way that is sub-optimal given your use case.

48
Q

SQL: In table mytable, how would you update all of the rows where column A have values below zero to now have values of zero?

A

Update mytable Set A = 0

Where A < 0;

49
Q

SQL: At a very high level, why do you need to be very careful when editing or deleting a table?

A

These changes often cannot be undone, and could thus wreak havoc on a database system.

50
Q

SQL: In table mytable, how would you delete all of the rows where column A have values below zero?

A

Delete From mytable Where A < 0;

51
Q

SQL: How do you delete the table mytable

A

Drop Table mytable;

52
Q

SQL: How to add a column called newcolumn to table mytable which has values of integer and whose default value of 0 (for cells already in table which don’t have a value yet)?

A

Alter Table mytable Add newcolumn Integer Default 0

53
Q

SQL: Name 4 common sql-based relational database management systems

A

MySQL
PostgreSQL
Oracle
MS SQL

54
Q

SQL: What is a relational database?

A

Basically, a set of tables where structured to recognize the relationships between the items in each of the tables, and to optimally store data given your awareness of the relationships between different types of items.

55
Q

SQL: What does “LIMIT n” do in MySQL?

A

It only has the first n rows of the query show up in the result

56
Q

SQL: What does “OFFSET n” do in MySQL?

A

When paired with LIMIT, as with “LIMIT m OFFSET n”, it specifies where the first element being output is. So the first element output is at index n (zero-indexed), and we output m total

57
Q

SQL: Where does a “LIMIT n OFFSET m” statement usually go in a query in MySQL?

A

At the end (for example, after the where statement).

58
Q

SQL: How does SELECT DISTINCT differ from SELECT?

A

SELECT DISTINCT only displays each unique value being queried, not repeats. So if there are repeat rows, it only shows one. If you’re just querying a single column, it shows only the unique values in the column.

59
Q

SQL: When executing a query, how would you return NULL if there is no result, otherwise return the normal response?

A

SELECT IFNULL( (myQuery…), NULL);

60
Q

SQL: Within a MySQL function, how do you declare an integer variable “myVariable” and set it equal to 42?

A

DECLARE myVariableINT;

SET myVariable = 42;

DS backup 4/22/21 (5 decks)

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