Fundamentals of SQL Flashcards
SQL
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.
Database
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.
Relational
Data and relationship between data entities. The pure Relational Model is math all the way down. Based on relational algebra and tuple relational calculus.
Table
In a relational database, data is stored in a construct known as a Table. Name, and a collection of Columns.
Column
Has a name. Has a restriction that restricts the size and category of data that can be stored in that Column.
Required Columns
row contains data. Data not required is referred as null.
Rows
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.
Keys
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.
Foreign Key
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.
Natural Keys
Like an ISBN number for a book - unique across all published books on the planet.
Invented Keys
Usually an integer is sufficient. Often a database will add this data in each row automatically for you - often called an identity column.
Normalization
The design process in database design.
SQL Statement
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.
Clause
Most parts of a SQL statement can be broken down into clauses. SQL component parts.
Valid SQL Statement
A valid SQL statement has a semi-colon(;) at the end of the statement.
Comments
Comments are good for documentation of SQL statements saved for re-use. – is for single-line comments. /* is for multi-line comments*/
Command
SQL statements all start with a command. Generally the command is a verb.
Select
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 ; “
Naming things
important for being able to use a database successfully.
Table Names
Should be singular. a table name describes what a row of data in that table “is” (e.g. user,email, phone).
Column Names
Will never be repeated inside of a particular database. This is to keep things clear when looking at names of columns.
Scoped Names
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”
Character Data
Can hold N character values - Set to N statically.
Character Varying Data
Can hold N character values - Set to N dynamically - Storage can be less than N.
Binary Data
Hexadecimal Data
SMALLINT
-2^15(-32,768 to 2^15-1 (32,767)
Integer
-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
BIGINT
-2^63 (-9,223,372,036,854,775,808 to 2^63-1 (9,223,372,036,854,775,807)
BOOLEAN
TRUE or FALSE
DATE
YEAR, MONTH, and DAY in the format YYYY-MM-DD
TIME
HOUR,MINUTE, and SECOND in the format HH:MM:SS[.sF] where F is the fractional part of the second value.
TIMESTAMP
Both DATE and TIME
RDMS
Relational Database Management System. More than just a Database. Also includes tools and application to help manage larger-scale database installations.
ANSI SQL
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.
MYSQL
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.)
