SQL

Question 1

What’s the syntax to refer to both a table name and column name in a SELECT statement?

Answer 1

tableName.columnName

Question 2

In SQLite, how do you format the output of a select statement?

Answer 2

output the name of each column

.headers on

now we are in column mode, enabling us to run the next two .width commands

.mode column

adjusts and normalizes column width

.width auto

customize column device-width

.width NUM1, NUM2

Question 3

In SQLite, what is the syntax for selecting a row where a column value is null?

Answer 3

SELECT * FROM cats WHERE name IS NULL;

Question 4

In SQLite, what is the syntax for inserting a row that includes null values?

Answer 4

INSERT INTO cats (name, age, breed) VALUES (NULL, NULL, “Tabby”);

Question 5

What is the syntax to retrieve two columns from two different tables when the column names are identical?

Answer 5

SELECT cats.name, dogs.name FROM cats, dogs;

Question 6

In SQLite, what is the syntax for inserting a row into a table?

Answer 6

INSERT INTO cats (name, age, breed) VALUES (‘Maru’, 3, ‘Scottish Fold’);

Question 7

In SQLite, do you have to add the primary key when inserting a row into a table?

Answer 7

No. Primary Key columns are auto-incrementing. As long as you have defined an id column with a data type of INTEGER PRIMARY KEY, a newly inserted row’s id column will be automatically given the correct value.

Question 8

In SQLite, what is the syntax for an UPDATE statement?

Answer 8

sqlite> UPDATE cats SET name = “Hana” WHERE name = “Hannah”;

Question 9

In SQLite, what is the syntax to delete a row from a table?

Answer 9

sqlite> DELETE FROM cats WHERE id = 2;

Question 10

What does it mean to “type” a table?

Answer 10

“Typing refers to adding data types to row definitions when creating a table.”

Question 11

In SQLite, what is the TEXT datatype?

Answer 11

Any alphanumeric characters which we want to represent as plain text.

Question 12

In SQLite, what is the BLOB datatype?

Answer 12

generally used for holding binary data

Question 13

In SQLite, what are the datatypes?

Answer 13

TEXT, INTEGER, REAL, BLOB

Question 14

In SQLite, what is the INTEGER datatype? And when do you use it?\n

Answer 14

Anything we want to represent as a whole number. If it’s a number and contains no letter or special characters or decimal points then we should store it as an integer. If we use it to perform math or create a comparison between two different rows in our database, then we definitely want to store it as an integer. If it’s just a number, it’s generally not a bad idea to store it as an integer. You might never add two house address numbers together, but you might want to sort them numerically. For example in the preceding case, you might want to get the biggest number and not the longest piece of text.

Question 15

In SQLite, how many actual datatypes are there?

Answer 15

SQLite allows the programmer to use other common datatypes outside of the four mentioned above. This is why we are referring to TEXT INTEGER REAL BLOB as datatype “categories”. All other common datatypes are lumped into one of the four existing datatypes recognized by SQLite.

Question 16

In SQLite, what is the REAL datatype?

Answer 16

Anything that’s a plain old decimal like 1.3 or 2.25. SQLite will store decimals up to 15 characters long. You can store 1.2345678912345 or 1234.5678912345, but 1.23456789123456789 would only store 1.2345678912345. In other database systems this is called ‘double precision.’

Question 17

What is the Double Precision datatype?

Answer 17

A decimal number with up to 15 digits.

Question 18

What is the syntax to execute a file in the CLI?

Answer 18

sqlite3 mydb.db < my_sql_file.sql

Question 19

In SQLite, what command will exit out of the sqlite CLI?

Answer 19

.quit

Question 20

In SQLite, what is the command to enter a database?

Answer 20

sqlite3 pets_database.db

Question 21

How do you create a table in SQLite?

Answer 21

\n sqlite> CREATE TABLE cats (\n id INTEGER PRIMARY KEY,\n name TEXT, \n age INTEGER\n );\n

Question 22

What command in SQLite will show a table’s structure?\n

Answer 22

\n sqlite> .schema\n CREATE TABLE cats(\n id INTEGER PRIMARY KEY,\n name TEXT,\n age INTEGER\n );

Question 23

How are words in a column name separated?

Answer 23

when we have multiple words in a column name, we link them together using underscores rather than spaces. We call this convention “snake_case”.

Question 24

Should column names be upper case or lower case?

Answer 24

By convention, always use lowercase letters when referring to columns.

Question 25

In SQLite, what is the syntax to add a column to a table?

Answer 25

sqlite> ALTER TABLE cats ADD COLUMN breed TEXT;

Question 26

In SQLite, what command will remove a table?

Answer 26

sqlite> DROP TABLE cats;

Question 27

Which command in SQLite will list all tables in the database?

Answer 27

Use the .tables command to list all the tables in a database

Question 28

Which command in SQLite will show the structure of the database?

Answer 28

Use the .schema command to look at the structure of a database

Question 29

How do you create a new database from the command line in SQLite?

Answer 29

sqlite3 test_sqlite.db

Question 30

What does the sqlite cli look like when it starts? What does the prompt look like?

Answer 30

\n SQLite version 3.7.12 2013-03-19 12:42:02\n Enter “.help” for instructions\n Enter SQL statements terminated with a “;”\n sqlite>\n

Question 31

How do you create a table in SQLite?

Answer 31

sqlite> create table test_table(id);

Question 32

What happens if you don’t terminate a SQLite command with a semicolon?

Answer 32

All SQL statements that you write in your terminal, inside the sqlite prompt, sqlite3>, must be terminated with a semi-colon ;. If you hit enter without adding a semi-colon to the end of your line, you will be trapped! Don’t worry though, just add that ; on the new line and hit enter again. The only command that doesn’t require, and in fact doesn’t even work with, a ; is the .quit command.

Question 33

What is the difference between SQL and NoSQL?

Answer 33

SQL (Structured Query Language) is a language for managing data in a database. Unlike some other programming languages, it’s only used for one thing: talking to databases. Thus, you might hear it referred to as a “special purpose”, or “domain specific” programming language. NoSQL databases are structured differently from the relational database structure. With relational, we structure tables by the type of relations, but NoSQL keeps all the information in one place, in the form of key-values or documents. For example, consider a database of people and their subscriptions to a newsletter. Using a relational database structure, we might separate the information for each person, having their id, name, and email, in one table named customers, and then another table for the subscriptions, having the newsletter name, and other information associated with the subscriptions. With a NoSQL database, instead of separating information in this way, we might just have a single document with the person’s information, as well as the subscription information, all in one document. As you might tell, this type of structuring might have some benefits and some cons compared to relational databases.

Question 34

What is an outer join?

Answer 34

An outer join will combine rows from different tables even if the join condition is not met. In a LEFT JOIN, every row in the left table is returned in the result set, and if the join condition is not met, then NULL values are used to fill in the columns from the right table.

Question 35

What is the syntax for a LEFT JOIN?

Answer 35

\n SELECT column_name\n FROM table1\n LEFT JOIN table2\n ON table1.column_name = table2.column_name;\n

Question 36

What does the WITH clause do?

Answer 36

>The WITH clause stores the result of a query in a temporary table using an alias. Multiple temporary tables can be defined with one instance of the WITH keyword.

Question 37

What is the syntax for a WITH clause?

Answer 37

\n WITH temp_table \n AS (SELECT * FROM actual_table)\n SELECT *\n FROM temp_table\n WHERE year BETWEEN 2000 AND 2020;\n

Question 38

What does the UNION clause do?

Answer 38

The UNION clause is used to combine results that appear from multiple SELECT statements and filter duplicates.

Question 39

What is the syntax for the UNION clause?

Answer 39

\n SELECT name\n FROM first_names\n UNION\n SELECT name\n FROM last_names\n

Question 40

What does the CROSS JOIN clause do?

Answer 40

The CROSS JOIN clause is used to combine each row from one table with each row from another in the result set. This JOIN is helpful for creating all possible combinations for the records (rows) in two tables.

Question 41

What is the syntax for the CROSS JOIN clause?

Answer 41

\n SELECT shirts.shirt_color,\n pants.pants_color\n FROM shirts\n CROSS JOIN pants;\n

Question 42

What does an INNER JOIN do?

Answer 42

The JOIN clause allows for the return of results from more than one table by joining them together with other results based on common column values specified using an ON clause. INNER JOIN is the default JOIN and it will only return results matching the condition specified by ON.

Question 43

What is the default join type?

Answer 43

INNER JOIN

Question 44

What is the syntax for an INNER JOIN?

Answer 44

\n SELECT * \n FROM books\n JOIN authors\n ON books.author_id = authors.id;\n

Question 45

What does GROUP BY do?

Answer 45

GROUP BY

Question 46

What does ORDER BY do?

Answer 46

ORDER BY

Question 47

What does the HAVING clause do?

Answer 47

The HAVING clause is used to further filter the result set groups provided by the GROUP BY clause. HAVING is often used with aggregate functions to filter the result set groups based on an aggregate property.

Question 48

What is the syntax for the HAVING clause?

Answer 48

\n SELECT year, \n COUNT(*) \n FROM movies \n GROUP BY year\n HAVING COUNT(*) > 5;