SQL Flashcards
SQL: Datatype for an id
INTEGER PRIMARY KEY
SQL: How to create a table called “mytable” with an id and columns a, b and c, which are text, integer, text respectively.
CREATE TABLE mytable (id INTEGER PRIMARY KEY, a TEXT, b INTEGER, c TEXT);
SQL: Datatype for an integer
INTEGER
SQL: Datatype for a string
TEXT
SQL: How to insert a row with ints and strings into table “mytable”
INSERT INTO mytable VALUES (“yes”, 1, “no”, 2);
SQL: What goes at the end of every line?
;
SQL: How to select all columns, or the entire table, from table “mytable”
SELECT * FROM mytable;
SQL: How to select column “a” from table “mytable”
SELECT a FROM mytable;
SQL: How to select columns “a” and “c” from table “mytable”
SELECT a, c FROM mytable;
SQL: How to select all columns from table “mytable” and order by column “b”
SELECT * FROM mytable ORDER BY b;
SQL: Does ordering by a column return with that column in ascending or descending order?
Ascending
SQL: How to select all columns from table “mytable”, but only rows where column “b” is greater than 5?
SELECT * FROM mytable WHERE b > 5;
SQL: How to get sum of column b from table “mytable”
SELECT SUM(b) FROM mytable;
SQL: How to get max of column b from table “mytable”
SELECT MAX(b) FROM mytable;
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?
SELECT c, MAX(b), SUM(b) FROM mytable GROUP BY c;
SQL: Summary statistic for total number of rows in a group
COUNT(*) (could also use a particular column)
SQL: Summary statistic for average value of column “b” in a group
AVG(b)
SQL: What pitfall can happen when you include a non-aggregated column in a group by, and how do you avoid this?
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.
SQL: How to combine multiple logicals?
AND or OR
SQL: How to select rows from table “mytable” where column “b” has a value of either “warriors,” “bucks,” or “raptors”?
Select * From mytable Where b in (“warriors”, “bucks”, “raptors”);
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”?
Select * From mytable Where b in
(Select b From other_table);
(I feel like this could be optimized…)
SQL: How to revers the value of any logical?
NOT
SQL: Describe the difference between WHERE and HAVING
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.
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?
Select c, SUM(b) as sum_b From my_table
Group By c
Having sum_b > 50;
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?
Select c, SUM(b) From my_table
Where b >= 0
Group By c;
SQL: How do you add a custom, calculated row to the data table resulting from your query?
Include a CASE statement in your Select statement, so:
Select a, b, CASE….. From *
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?
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;
SQL: Why do we often split one table into several?
To avoid information being repeated multiple times in several rows, as when columns are related.
SQL: How do we make sure we can join two tables containing related information?
We include a unique identifier for each row of every table.
SQL: What do we want to avoid when we have multiple tables where rows are the same entity?
Repeated columns
SQL: What is an inner join?
When selecting based on a common column, say id, you only get the ids that are present in both tables.
SQL: How do I do an inner join on id, a column present in both tables “students” and “student_grades”?
SELECT * FROM students
JOIN student_grades
ON students.id = student_grades.id;
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.
SELECT students.name, student_grades.letter_grade FROM students
JOIN student_grades
ON students.id = student_grades.id;
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”?
SELECT * FROM students
JOIN student_grades
ON students.id = student_grades.id
Where student_grades.grade In (“A”,”B”);
SQL: What is a left outer join?
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.
SQL: Why, at a high level, are right outer joins not often used, and not supported in all SQL frameworks?
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.
SQL: What is a full outer join?
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.
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?
Select *
From students
LEFT OUTER JOIN student_grades;
SQL: When ordering by column a, how do you make it in descending order
ORDER BY a DESC
SQL: At a high level, why would we want to do a self join?
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.
SQL: At a high level, what issue arises when doing a self join, and how do we fix it?
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.
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”?
Select students.name, buddies.email
From students
Join students buddies
On students.buddy_id = buddies.id;
SQL: Can you do more than one join under the same select statement?
Yes
SQL: Why would you want to do more than one join under the same select statement?
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.
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)?
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;
SQL: Can you ask most SQL engines how they plan to execute a query, in order to optimize it?
Yes
I just want to remember this exists
SQL: At a very high level, why would you want to query planning or optimization?
When your SQL engine will execute your query in a way that is sub-optimal given your use case.
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?
Update mytable Set A = 0
Where A < 0;
SQL: At a very high level, why do you need to be very careful when editing or deleting a table?
These changes often cannot be undone, and could thus wreak havoc on a database system.
SQL: In table mytable, how would you delete all of the rows where column A have values below zero?
Delete From mytable Where A < 0;
SQL: How do you delete the table mytable
Drop Table mytable;
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)?
Alter Table mytable Add newcolumn Integer Default 0
SQL: Name 4 common sql-based relational database management systems
MySQL
PostgreSQL
Oracle
MS SQL
SQL: What is a relational database?
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.
SQL: What does “LIMIT n” do in MySQL?
It only has the first n rows of the query show up in the result
SQL: What does “OFFSET n” do in MySQL?
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
SQL: Where does a “LIMIT n OFFSET m” statement usually go in a query in MySQL?
At the end (for example, after the where statement).
SQL: How does SELECT DISTINCT differ from SELECT?
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.
SQL: When executing a query, how would you return NULL if there is no result, otherwise return the normal response?
SELECT IFNULL( (myQuery…), NULL);
SQL: Within a MySQL function, how do you declare an integer variable “myVariable” and set it equal to 42?
DECLARE myVariableINT;
SET myVariable = 42;
