sql

Question 1

1. What is SQL and how is it different from languages like JavaScript?

Answer 1

a. Structured Query Language (SQL) is the primary way of interacting with relational databases.
b. It is a powerful way of retrieving, creating, and manipulating data in a relational database.
c. Difference: JavaScript is an imperative language while because SQL is a declarative language

Question 2

2. How do you retrieve specific columns from a database table?

Answer 2

a. Use the select keyword followed by the “columns” in double quotes, separated by commas.

Question 3

3. How do you filter rows based on some specific criteria?

Answer 3

a. Use the “where” keyword followed by a condition you are looking to compare or match.

Question 4

4. What are the benefits of formatting your SQL?

Answer 4

a. Cleaner and easier to read

Question 5

5. What are four comparison operators that can be used in a where clause?

Answer 5

a. “=” / “ < ” / “ > ” / “ != ”

Question 6

6. How do you limit the number of rows returned in a result set?

Answer 6

a. Use the limit keyword

Question 7

7. How do you retrieve all columns from a database table?

Answer 7

a. Replace the list of column names with an asterisk ( * )
i. The order of the results is NOT predictable.
ii. The asterisk is NOT in quotes.

Question 8

8. How do you control the sort order of a result set?

Answer 8

a. Use the order by keywords, followed by desc or asc abbreviated keywords without double quotes.

Question 9

1. How do you add a row to a SQL table?

Answer 9

a. Use the INSERT statement

Question 10

2. What is a tuple?

Answer 10

a. Tuples are unordered sets of known values with names.
b. Tuple is often referred to as rows in a relational database model.
c. They are effectively a data structure like an array – New Tim.

Question 11

3. How do you add multiple rows to a SQL table at once?

Answer 11

a. Data rows can be batch inserted into a database table by specifying more than one tuple of values, separated by commas.

Question 12

4. How do you get back the row being inserted into a table without a separate select statement?

Answer 12

a. returning *;

b. sometimes you don’t need to use the return keyword and can specify the columns.

Question 13

1. How do you update rows in a database table?

Answer 13

a. Use the ‘UPDATE’ statement

Question 14

2. Why is it important to include a where clause in your update statements?

Answer 14

a. If you don’t use a WHERE statement, it will updated EVERY row in the table.
b. Great care must be taken to include a “WHERE” clause in order to ONLY target specific rows.

Question 15

1. How do you delete rows from a database table?

Answer 15

a. Use the “DELETE” statement

Question 16

2. How do you accidentally delete all rows from a table?

Answer 16

a. By failing to target specific rows and including a where clause – we may accidently delete the entire table.

Question 17

2. How do you accidentally delete all rows from a table?

Answer 17

a. By failing to target specific rows and including a where clause – we may accidently delete the entire table.

Question 18

1. What is a foreign key?

Answer 18

c. A foreign key is a constraint that one column has to match of another column – New Tim.

Question 19

2. How do you join two SQL tables?

Answer 19

a. Use the JOIN statement

Question 20

3. How do you temporarily rename columns or tables in a SQL statement?

Answer 20

a. Use aliases.

Question 21

1. What are some examples of aggregate functions?

Answer 21

a. max()
b. avg()
c. count()

Question 22

2. What is the purpose of a group by clause?

Answer 22

a. Sometimes you don’t want to ask a question about every row in a table. Instead, you want to separate rows into groups and perform aggregate functions on those groups of rows.
b. It groups multiple rows into one bucket row.
c. For each group by, you are actually increasing the row count as there are more things that need to match.