SQL Basics Flashcards
Covering SQL basics: - Tables - Relationships - Joins - Subqueries - Regular expressions - + more!
1
Q
Define a database
A
- A database is a collection of data/information, stored in a format that can be accessed in various ways
2
Q
Define DBMS
A
- A Database Management System, a software application that uses instructions to query or modify data
3
Q
Define Data
A
- Data is a collection of facts and figures, or information in raw form
4
Q
What do the commands SELECT, FROM & WHERE do?
A
- SELECT is used to identify the columns you wish to return
- FROM is used to identify the table you wish to query
- WHERE is used to filter the requirements to specific conditions
5
Q
Why do people use databases over files as a storage method?
A
Why files are not ideal:
- It’s a complex process to retrieve data
- Size becomes an issue with large data sets
- Organising data becomes difficult
- Data redundancy (repetition)
- GDPR requests can come difficult to adhere to
- Loss of data when multiple users access
6
Q
When might you use files over a database?
A
- Storing less important data
- For on-device/offline access
- When there is frequent incoming data
7
Q
What happens on the backend when a user is trying to engage with a database?
A
- The user interacts with the application, the application interacts with the DBMS, the DBMS interacts with the operating system
USER —> APPLICATION —> DBMS —> OPERATING SYSTEM
8
Q
What are the functions of a DBMS?
A
The functions of a Database Management System are:
- Data Management, store retrieve and modify data
- Security, access to authorised users only
- Concurrency, simultaneous access for multiple users
- Utilities, backing up, logging, data import & export
- Transactions, modifying a data base should be successful or must not happen at all
- Integrity, maintains data accuracy
9
Q
What different types of databases are there?
A
- Hierarchical database, this is the tree like data base. Kind of looks like a flow chart
- Network/Graph database, commonly used for things like social media. Any tables can be linked and interacting with any other tables, not so much of a structure. neo4j is the main example of this database
- NoSQL, non tabular and represented using key value pairs. MongoDB is the main example of this database
- Relational database, tabular structure with a very clear relationship between tables. Examples of this are MySQL, PostgresSQL, Oracle and MS Access
10
Q
When talking about relational databases, what are the correct terms for rows, columns, the number of rows and the number of columns?
A
- Rows = Records/Tuples
- Columns = Attributes/Fields
- Number of rows = Degree of relation
- Number of Columns = Cardinality of the relation
11
Q
What is DBMS integrity ?
A
- DBMS integrity maintains data integrity through constraints, which basically restricts the data that can be entered or modified in a database
- The 3 types of integrity are entity, domain and referential integrity
12
Q
What is Entity, Domain and Referential integrity?
A
- Entity integrity is that each table should have a column or a set of columns which can be used to identify a record (Primary Keys)
- Domain integrity is that all attributes of a table must have a defined domain, such as a finite number of values which have to be used, or to check age is an integer as it should be. Data types & check constraint)
- Referential integrity is that every value of a column in a table must exist in a value of another column in a different table (Foreign Keys)
13
Q
What is a database key & why do we need them ?
A
- A DBMS key is an attribute or set of attributes which helps us uniquely identify a record
We need them to:
- To establish relationships in tables
- To uniquely identify a record
- To enforce data integrity and constraints
14
Q
What are the 8 types of database keys ?
A
- Super Key - a combination of all of the unique attributes, in all possible combinations
- Candidate Key - all of the unique attributes in singular form (not combined)
- Primary Key - a part of the candidate key that is selected to become a primary key. It must be unique, not null and ideally is small and numerical. Used to identify records.
- Alternate Key - the candidate keys that didn’t become primary keys
- Foreign Key - a primary key in one table that behaves as a foreign key for relationships in another table
- Composite Key - a combination of keys that are unique attributes
- Compound Key - if one of the keys from a composite key is a foreign key, it becomes a compound key
- Surrogate Key - when there are no unique attributes, a surrogate key is added to be a unique identifier
15
Q
What is an E-R diagram?
A
- An E-R diagram is used to explain the structure of the database diagrammatically, to show how the tables are related
- E-R = Entity Relations
