sql_flashcards

Question 1

What naming convention should be used for columns and tables in a database?

Answer 1

Columns should be singular (e.g., ‘name’), while tables can be plural.

Question 2

Where is data stored in a database server?

Answer 2

On the server’s hard disk.

Question 3

What are the sizes and ranges of TINYINT, SMALLINT, INT, and BIGINT?

Answer 3

TINYINT = 1 byte → 0 to 255
SMALLINT = 2 bytes → -32,768 to 32,767
INT = 4 bytes
BIGINT = 8 bytes

Question 4

What does NUMERIC(p, s) mean in SQL?

Answer 4

Precision (p) = total digits (max 38), Scale (s) = digits after decimal point (0 ≤ s ≤ p).

Question 5

What is a database schema?

Answer 5

A blueprint defining tables, columns, types, relationships, views, and indexes.

Question 6

What does SELECT * do in SQL?

Answer 6

Uses the wildcard * to select all columns.

Question 7

How does DISTINCT work with two columns?

Answer 7

Returns unique combinations of the two columns.

Question 8

What is a SQL view?

Answer 8

A saved query, not a saved result. Created with CREATE VIEW view_name AS …

Question 9

How do you limit query results in PostgreSQL vs SQL Server?

Answer 9

LIMIT n in PostgreSQL; TOP(n) in SQL Server.

Question 10

What’s the difference between COUNT(field_name) and COUNT(*)?

Answer 10

COUNT(field_name) → ignores nulls
COUNT(*) → counts all rows

Question 11

What does COUNT(DISTINCT field) do?

Answer 11

Counts unique non-null values of the field.

Question 12

What is the SQL query execution order?

Answer 12

FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

Question 13

Why can’t aliases be used in the WHERE clause?

Answer 13

Because WHERE is executed before SELECT, where aliases are defined.

Question 14

Where can you use column aliases in SQL?

Answer 14

In SELECT and ORDER BY clauses.

Question 15

What are % and _ wildcards used for?

Answer 15

% → any number of characters
_ → exactly one character

Question 16

Is the LIKE operator case-sensitive?

Answer 16

Yes, by default.

Question 17

What is the difference between IS NULL and = NULL?

Answer 17

Use IS NULL or IS NOT NULL; = NULL does not work.

Question 18

What is an aggregate function?

Answer 18

A function that summarizes multiple values into one (e.g., SUM, AVG, COUNT).

Question 19

How do MIN and MAX behave with non-numeric fields?

Answer 19

Return alphabetical lowest/highest or earliest/latest dates.

Question 20

What does ROUND(field, n) do when n is positive or negative?

Answer 20

Positive → rounds after the decimal
Negative → rounds before the decimal

Question 21

How does integer division behave in SQL?

Answer 21

It returns an integer result.

Question 22

What’s the difference between arithmetic and aggregate operations?

Answer 22

Arithmetic → row-wise
Aggregate → column-wise

Question 23

What happens if you don’t alias a calculated field?

Answer 23

The result is shown under ?column?.

Question 24

What does GROUP BY do?

Answer 24

Groups data to compute aggregates per group.

Question 25

Why can’t aliases be used in HAVING?

Answer 25

Because HAVING comes before SELECT where aliases are defined.

Question 26

When should you use USING(field) in a JOIN?

Answer 26

When the field name is the same in both tables.

Question 27

What is a one-to-one relationship in a database?

Answer 27

Each row in Table A corresponds to exactly one row in Table B using the same primary key.

Question 28

How do you store images in a SQL database?

Answer 28

Use a binary data type (e.g., BLOB).

Question 29

What is a many-to-many relationship?

Answer 29

When multiple records in one table relate to multiple in another (e.g., countries ↔ languages).

Question 30

What does SQL style guide recommend for formatting?

Answer 30

Follow Holywell’s style guide: https://www.sqlstyle.guide/

Question 31

Why should SQL queries end with a semicolon?

Answer 31

It terminates the query, especially needed when executing multiple statements.

Question 32

When should you use double quotes in field names?

Answer 32

When the field name contains spaces or special characters (e.g., "release year").