MySQL Basics

Question 1

Database

Answer 1

A repository designed for organizing and accessing information

Question 2

Database Management System (DBMS)

Answer 2

Software designed to store, manipulate, and retrieve data in a database

Question 3

Database Manipulation Language (DML)

Answer 3

Used to store and retrieve data from the database

Question 4

Database Control Language (DCL)

Answer 4

Used to restrict access to data by certain users

Question 5

Relational Model

Answer 5

Database contains a set of tables

Question 6

Character String types

Answer 6

char(n) - always of num size
varchar(n) - no padding

Question 7

Numeric types

Answer 7

• integer, smallint, bigint
• numeric(p,s) for signed fixed-point numbers with p digits and s decimal places
• decimal(p,s)
• real
• double precision
• float(p)

Question 8

Temporal types

Answer 8

Datetimes and Intervals

Question 9

Datetime subtypes

Answer 9

• Date YYYY-MM-DD
• Time HH:MM:SS
• Timestamp YYYY-MM-DD HH:MM:SS

Question 10

Interval subtypes

Answer 10

Year-Month Interval
- ‘3-1’ Year to Month
Day-Time Interval
- ‘5 10:30:22.5’ Day to Second

Question 11

Binary types

Answer 11

• Bit(L)
• BitVarying(L)
• BLOB(L)

Question 12

Boolean Values

Answer 12

True, false, unknown

Question 13

Primary Key (PK)

Answer 13

Values that uniquely identify a row

• The value of a PK for every row is unique
• No PK can be NULL

Question 14

Foreign Key (FK)

Answer 14

Column(s) that refers to some other column(s) in some table

Question 15

Types of table relations

Answer 15

Many-to-many, one-to-one, one-to-many

Question 16

Schema

Answer 16

Collection of tables and other data description objects

Question 17

Select

Answer 17

Gets information from the table

Question 18

Select *

Answer 18

Gets every row from a table that meets the condition

Question 19

Multi-line comments

Answer 19

/* */

Question 20

Single-line comments

Answer 20

–

Question 21

What does Select generate

Answer 21

A result table

Question 22

Where clause

Answer 22

Used to specify a condition each row must satisfy to be in the query result

Question 23

Operators

Answer 23

Traditional: =, <, >, <=, >=
- [not] Between
- Is [not] NULL
- [not] Like
- [not] In
- Not

Question 24

Like Regex

Answer 24

% - Matches any substring with 0+ characters
_ - Matches any single character

Question 25

How to change Like escape character

Answer 25

Like ‘%#_%’ Escape ‘#’ allows the use of _ as a char instead of a wildcard

Question 26

Operator precedence

Answer 26

Comparison operators
Not
And
Or

Question 27

What kind of range does Between specify

Answer 27

Inclusive range

Question 28

How to rename and attribute in a Select

Answer 28

Select [attribute] as [name]

Question 29

Distinct

Answer 29

Eliminates duplicate rows

Question 30

Derived Attributes

Answer 30

Abs(n)
Ceil(n)
Exp(n)
Floor(n)
Ln(n)
Mod(n,d)
Power(b,e)
Sqrt(n)

Question 31

String functions

Answer 31

• Substring( [source] from [start] {for length}
• Trim({{Leading | Trailing | Both} [to_trim] from} [source])
• Lower([source]
• Upper([source])
• Position([substr] in [source])

Question 32

Order by

Answer 32

Order by <expression> [ASC | DESC] [NULLS FIRST | NULLS LAST]</expression>

Question 33

Casting

Answer 33

Cast([source] as [result_type])

Question 34

Conditional Results

Answer 34

Case [target expression]
when [candidate] then [result]
else [result]
END