SQL

Question 1

relational database

Answer 1

represents a collection of related (two-dimensional) tables. Each of the tables are similar to an Excel spreadsheet, with a fixed number of named columns (the attributes or properties of the table) and any number of rows of data.

Question 2

SQL

Answer 2

structured query language

Question 3

in SQL columns represent x?

Answer 3

properties

Question 4

in SQL rows represent x?

Answer 4

instances

Question 5

how to select all columns of data in a table?

“dumping all the data at once”

Answer 5

SELECT *

FROM table_name;

Question 6

how to select several columns in a database?

Answer 6

by separating them with a comma:

SELECT title, director FROM movies;

Question 7

conditional word for operations with constraints

Answer 7

WHERE
e.g.:
SELECT column, another_column, …
FROM mytable
WHERE condition
    AND/OR another_condition
    AND/OR …;

Question 8

operator for: Number is within range of two values (inclusive)

Answer 8

BETWEEN … AND …
e.g.:
col_name BETWEEN 1.5 AND 10.5

Question 9

operator for: Number is not within range of two values (inclusive)

Answer 9

NOT BETWEEN … AND …
e.g.:
col_name NOT BETWEEN 1 AND 10

Question 10

operator for: number exists in a list

Answer 10

IN (…)
e.g.:
col_name IN (2, 4, 6)

Question 11

operator for: number does not exist in a list

Answer 11

NOT IN (…)
e.g.:
col_name NOT IN (1, 3, 5)

Question 12

query capitalization mandatory?

Answer 12

NO. Just a convention to help distinguish from column and table names

Question 13

operator: Case sensitive exact string comparison

Answer 13

=

Question 14

operator: Case sensitive exact string inequality comparison

Answer 14

!= or <>

Question 15

operator: Case insensitive exact string comparison

Answer 15

LIKE

Question 16

operator: Case insensitive exact string inequality comparison

Answer 16

NOT LIKE

Question 17

operator: Used anywhere in a string to match a sequence of zero or more characters (only with LIKE or NOT LIKE)

Answer 17

%
e.g. col_name LIKE “%AT%”
(matches “AT”, “ATTIC”, “CAT” or even “BATS”)

Question 18

operator: Used anywhere in a string to match a single character (only with LIKE or NOT LIKE)

Answer 18

_ (underscore)
e.g.: col_name LIKE “AN_”
(matches “AND”, but not “AN”)

Question 19

string quoting mandatory?

Answer 19

yes. Allows to distinguish strings from SQL keywords

Question 20

how do we add an additional condition?

Answer 20

By introducing AND/OR after WHERE.
e.g.:
SELECT column, another_column, …
FROM mytable
WHERE condition
    AND/OR another_condition
    AND/OR …;

Question 21

keyword: a convenient way to discard rows that have a duplicate column value

Answer 21

DISTINCT
SELECT DISTINCT column, another_column, …
FROM mytable
WHERE condition(s);

Question 22

clause: SQL provides a way to sort your results by a given column in ascending or descending order (alphanumerically)

Answer 22

ORDER BY
e.g.:
SELECT column, another_column, …
FROM mytable
WHERE condition(s)
ORDER BY column ASC/DESC;

Question 23

clause: will specify where to begin counting the number rows from

Answer 23

OFFSET

Question 24

clause: will reduce the number of rows to return

Answer 24

LIMIT
e.g.:
SELECT column, another_column, …
FROM mytable
WHERE condition(s)
ORDER BY column ASC/DESC
LIMIT num_limit OFFSET num_offset;

Question 25

SQL is used by …?

Answer 25

• database administrators
• developers writing data integration scripts
• data analysts looking to set up and run analytical queries

Question 26

SQL is used for…?

Answer 26

• modifying database table and index structures;
• adding, updating and deleting rows of data;
• retrieving subsets of information from within relational database management systems (RDBMSes)

Question 27

database object: VIEW

Answer 27

logical representations of data assembled from one or more database tables

Question 28

database object: INDEX

Answer 28

lookup tables that help speed up database lookup functions

Question 29

database object: REPORT

Answer 29

consist of data retrieved from one or more tables, usually a subset of that data that is selected based on search criteria

Question 30

SQL status?

Answer 30

de facto standard programming language for relational databases

Question 31

database normalization

Answer 31

splitting data into separate tables

helps minimize duplicate data in any single table, and allows for data in the database to grow independently of each other. BUT, queries get slightly more complex since they have to be able to find data from different parts of the database, and performance issues can arise when working with many large tables