Fundamentals of SQL

Question 1

SQL

Answer 1

Structured Query Language. Special purpose programming language. Built to manipulate relational databases. Declarative language.Contains both a data definition syntax as well as a data manipulation syntax.

Question 2

Database

Answer 2

Container that helps us organize data in a way that makes the data useful or constructive. Useful when you have some threshold amount of data. 500 excel spreadsheets instead of 5. Putting all of the data in one place makes it easy to query data, update data, insert new data, and delete old data. One source of truth rather than many sources of truth. Many types: relational, object-oriented, document-based.

Question 3

Relational

Answer 3

Data and relationship between data entities. The pure Relational Model is math all the way down. Based on relational algebra and tuple relational calculus.

Question 4

Table

Answer 4

In a relational database, data is stored in a construct known as a Table. Name, and a collection of Columns.

Question 5

Column

Answer 5

Has a name. Has a restriction that restricts the size and category of data that can be stored in that Column.

Question 6

Required Columns

Answer 6

row contains data. Data not required is referred as null.

Question 7

Rows

Answer 7

Rows can be retrieved by asking questions of the data. Put data in a database to get it out and find answers to questions. Questions will related to values stored in those columns. “Who are all my contacts that have a phone number that starts with 310?” Asking such questions with SQL is known as querying.

Question 8

Keys

Answer 8

Belong to tables. Each table should/must have one column that uniquely identify a row. This column is known as the Primary Key. If more than one table uses the same primary key, you can then merge those two tables together.

Question 9

Foreign Key

Answer 9

Table can also have a column that is classified as foreign. This key links that table to another Table’s Primary Key. Allows rows from each table to be linked together.

Question 10

Natural Keys

Answer 10

Like an ISBN number for a book - unique across all published books on the planet.

Question 11

Invented Keys

Answer 11

Usually an integer is sufficient. Often a database will add this data in each row automatically for you - often called an identity column.

Question 12

Normalization

Answer 12

The design process in database design.

Question 13

SQL Statement

Answer 13

A valid SQL statement is made up of an actionable set of valid words. Some of the words are defined by SQL, some are defined by you. SQL is english-based so it is readable. Not case-sensitive.

Question 14

Clause

Answer 14

Most parts of a SQL statement can be broken down into clauses. SQL component parts.

Question 15

Valid SQL Statement

Answer 15

A valid SQL statement has a semi-colon(;) at the end of the statement.

Question 16

Comments

Answer 16

Comments are good for documentation of SQL statements saved for re-use. – is for single-line comments. /* is for multi-line comments*/

Question 17

Command

Answer 17

SQL statements all start with a command. Generally the command is a verb.

Question 18

Select

Answer 18

tells the system we would like to select a piece of data. It is supposed to give us something. i.e “SELECT VALUES From TableName ; “

Question 19

Naming things

Answer 19

important for being able to use a database successfully.

Question 20

Table Names

Answer 20

Should be singular. a table name describes what a row of data in that table “is” (e.g. user,email, phone).

Question 21

Column Names

Answer 21

Will never be repeated inside of a particular database. This is to keep things clear when looking at names of columns.

Question 22

Scoped Names

Answer 22

Names in SQL are scoped. A database will have a name. Tables inside of a database will have a name, but a Table name should be referred to using its “full” name, which includes the Database name. A Period is used to separate each part of the name. i.e.”Database.Table”. Columns are also scoped: “Table.Column”

Question 23

Character Data

Answer 23

Can hold N character values - Set to N statically.

Question 24

Character Varying Data

Answer 24

Can hold N character values - Set to N dynamically - Storage can be less than N.

Question 25

Binary Data

Answer 25

Hexadecimal Data

Question 26

SMALLINT

Answer 26

-2^15(-32,768 to 2^15-1 (32,767)

Question 27

Integer

Answer 27

-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)

Question 28

BIGINT

Answer 28

-2^63 (-9,223,372,036,854,775,808 to 2^63-1 (9,223,372,036,854,775,807)

Question 29

BOOLEAN

Answer 29

TRUE or FALSE

Question 30

DATE

Answer 30

YEAR, MONTH, and DAY in the format YYYY-MM-DD

Question 31

TIME

Answer 31

HOUR,MINUTE, and SECOND in the format HH:MM:SS[.sF] where F is the fractional part of the second value.

Question 32

TIMESTAMP

Answer 32

Both DATE and TIME

Question 33

RDMS

Answer 33

Relational Database Management System. More than just a Database. Also includes tools and application to help manage larger-scale database installations.

Question 34

ANSI SQL

Answer 34

Most relational database management systems extend ANSI with vendor-specific extensions. Most are proprietary:

• Oracle has PL/SQL
• SQL Server has T-SQL

Most ANSI SQL will work with any RDMS.

Question 35

MYSQL

Answer 35

Free and open-source. Runs on all major platforms:Windows, OSX, Linux. Has an ANSI operation mode that enforces ANSI compliance (Most RDMS do not have this feature.)