Introduction to SQL - Relational Databases Flashcards
Before writing any SQL queries, it’s important to understand the underlying data. In this chapter, we’ll discover the role of SQL in creating and querying relational databases. Using a database for a local library, we will explore database and table organization, data types and storage, and best practices for database construction.
What type of database defines the relationship between tables of data inside the database?
Relational Databases
What are the 3 advantages of a database over a spreadsheet?
1) more storage
2) more encryption
3) multiple users
What does SQL stand for?
Structured Query Language
Table names should follow what 3 rules?
1) lowercase
2) have no spaces - use underscores
3) plural
What is a record?
a row that hold data on an individual observation
What is a field?
a column that holds one piece of information about all records
Field names should follow what 5 rules?
1) lowercase
2) no spaces
3) singular
4) different from field names
5) different from the table name
Unique identifiers are used to identify what?
records in a table
What is a feature of unique identifiers (IDs) apart from being unique?
they are often numbers
Having more tables each with a clearly marked subject is generally better than what?
having fewer tables where information is combined
What are the 2 reasons we use data types?
1) different storage requirements
2) some operations only apply to certain types
What is the definition of a string?
a sequence of characters such as letters or punctuation (or numbers with special characters)
What is a popular string data type in SQL?
VARCHAR
What does an integer store?
whole numbers
What is a popular integer data type in SQL?
INT
What do floats store?
numbers that include a fractional part
What is a flexible and popular float data type in SQL?
NUMERIC
Schemas are often referred to as what?
the blueprints of databases
What 3 things does a schema show about a databases design?
1) it’s tables
2) relationships between its tables
3) data type in tables
What are the 3 most common data types in SQL and their respective descriptions?
1) VARCHAR - text strings
2) INT - whole numbers
3) NUMERIC - decimals
What data type would be best suited for this example? Product prices in dollars such as 63.75?
NUMERIC
What data type would be best suited for this example? Weight in tons such as 5.67
NUMERIC
What data type would be best suited for this example? Phone number such as 321-123-5555
VARCHAR
What data type would be best suited for this example? Model year such as 2004
INT
What data type would be best suited for this example? Number of mailing list subscribers such as 9782
INT
What data type would be best suited for this example? Product reviews written by customers
VARCHAR
