My Addition Flashcards
1
Q
What is data?
A
Data can exist in various forms such as numeric, textual, visual, or audio, and is used to describe real-world systems.
Data is essential for understanding and analyzing different aspects of reality.
2
Q
Based on scope, how can data be characterized?
A
- global data: accessible to everyone within a system
- scoped data: restricted to specific users or groups, allowing access only to a defined subset of information within the system
Data varies in scope, format, and access.
3
Q
How can data be represented?
A
Data can be represented as numbers, text, images, audio, or video.
Different formats allow for diverse applications of data in various fields.
4
Q
What does data access refer to?
A
- Data can be private to organizations or
- Publicly available due to regulations.
This distinction is crucial for understanding data privacy and sharing policies.
5
Q
What was the historical context of data?
A
- Historically, data was mostly analog, encoded as continuous variations on various physical media.
- Today, data is mostly digital, encoded as zeros and ones on electronic and magnetic media.
This transition has enabled the development of large databases.
6
Q
What is an information management system?
A
An information management system is a software application that manages corporate data for a specific business function.
These systems are vital for efficient data handling in organizations.
7
Q
What components typically comprise an information management system?
comprise: consist of
A
An information management system usually includes:
* A database system
* A user interface
* Business logic
* Interfaces to other systems
These components work together to facilitate data management.
8
Q
What are the primary roles in database management?
A
Roles in database management include:
- Database Administrator root account
- Database Designer
- Database Programmer
- Database User
Each role has distinct responsibilities crucial for effective database operation.
9
Q
What is the role of a Database Administrator?
A
A Database Administrator ensures security and user access.
This role is critical for protecting sensitive data.
10
Q
What does a Database Designer do?
A
A Database Designer structures the data and database format for quick access.
Effective design is key to database performance.
11
Q
What is the responsibility of a Database Programmer?
A
A Database Programmer writes applications using databases.
This role bridges the gap between users and data.
12
Q
Who are Database Users?
A
Database Users: consume data, request, or update information.
Users play a vital role in leveraging data for decision-making.
13
Q
What defines a Database?
A
Databases are defined as organized collections of data.
This organization enables efficient data retrieval and management.
14
Q
What is a Database Management System (DBMS)?
A
A DBMS is software that manages databases, ensuring security and consistency.
It acts as an intermediary between users and the database.
15
Q
What is a Query Language?
A
A specialized programming languages to retrieve or manipulate data in databases.
SQL is one of the most commonly used query languages.
16
Q
What is a Database Application?
A
A Database Application is software for users to interact more easily with databases.
These applications enhance user experience and accessibility.
17
Q
These are Examples of :
* data.gov (U.S. government data)
* cancer.gov/research (cancer research data)
* kaggle.com (community-driven data sets)
* data.nasa.gov (NASA’s data)
* opendata.cityofnewyork.us (data from NYC)
A
These are examples of public data sources
Public data sets are valuable for research and analysis.
18
Q
True or False: Data is primarily analog in today’s context.
A
False
Today, data is predominantly digital.
19
Q
What are the key requirements of database systems?
A
- Performance
- Authorization
- Security
- Rules
- Recovery
These requirements ensure effective management of data and user access.
20
Q
What types of database systems are most commonly used?
A
- Relational databases (using SQL)
- Non-relational databases (NoSQL)
NoSQL databases have emerged to handle large volumes of less structured data.
21
Q
Database systems are more suitable than file systems for large, complex databases, because?
A
Database systems provide better performance, security, and data recovery.
22
Q
What are the characteristics of transaction management in database systems?
A
- Ensure complete transaction processing
- Prevent conflicts between concurrent transactions
- Ensure transaction results are saved
These characteristics maintain data integrity and consistency.
23
Q
What does ACID stand for in the context of relational databases?
A
- Atomicity
- Consistency
- Isolation
- Durability
These properties ensure reliable transaction processing.
24
Q
Fill in the blank: Relational databases store data in _______.
A
tables
Each table represents a specific entity and maintains relationships with other tables.
25
What are some advantages of relational databases?
* Structured data *organization*
* Flexibility in Scalability & querying
* Data redundancy control
* Support for **transactions**
* Data security & integrity
* Widely supported and **standardized**
## Footnote
These advantages make relational databases effective for various applications.
26
What role do *NoSQL* databases serve in modern data management?
* Big Variety of Real-Time data handling
* Flexible schema
* High availability and scalability
* Integration with cloud systems
* Support for various applications
## Footnote
NoSQL databases complement relational databases by providing flexibility and performance for unstructured data.
27
What are the main components of a database system?
* Query processor
* Storage manager
* Transaction manager
* Log
* Catalog (data dictionary)
## Footnote
Each component has specific functions that contribute to the overall operation of the database system.
28
What is the function of a **catalog** in a database system?
It serves as a directory *containing metadata* about database objects.
## Footnote
This metadata helps other components understand how to access and manipulate data effectively.
29
What is a limitation of **file systems** compared to database systems?
* Inability to manage *concurrent access* effectively
* Lack of security features
* No enforcement of data integrity
## Footnote
These limitations can lead to data corruption and inconsistencies.
30
How does a database avoid issues arising from simultaneous access?
Databases provide **mechanisms** such as *managing reading and writing to shared data* which prevent conflicts and ensures data integrity.
31
What might happen if two programs access a text file simultaneously?
* One program may miss updates *made by the other*
* Data corruption may occur
* Inconsistent data may be read
## Footnote
These issues highlight the limitations of file systems in managing concurrent data access.
32
What are the four common CRUD operations?
Create, Read, Update, Delete
33
What does the Create operation do in CRUD?
Inserts new records into a database
34
What does the Read operation do in CRUD?
Retrieves existing records from a database
35
What does the Update operation do in CRUD?
Modifies existing records in a database
36
What does the Delete operation do in CRUD?
Removes records from a database
37
What is the primary focus of CRUD operations?
Direct manipulation of data within the database
38
Which SQL command is used for the Create operation?
INSERT
39
Which SQL command is used for the Read operation?
SELECT
40
Which SQL command is used for the Update operation?
UPDATE
41
Which SQL command is used for the Delete operation?
DELETE
42
What does ACID stand for in the context of database transactions?
Atomicity, Consistency, Isolation, Durability
## Footnote
[Learn more](https://g.co/gemini/share/b8407572cb7c)
43
CRUD operations does not include:?
schema management commands
44
What are schema operations in database management?
Commands that manage database structure
45
What do access control commands like GRANT and REVOKE manage?
User permissions and access to data
46
What is the SQL CREATE TABLE statement used for?
To define a new table and its columns
47
SQL is the primary language used for writing _______ in relational database systems.
queries
48
What data type is used to store integer values in SQL?
INT
49
What data type is used to store textual values in SQL?
VARCHAR
50
What is the main function of *SQL* in database _management_?
To interact with relational databases through *commands that manage data*.
51
What does the `SELECT` statement enable users to do?
**Retrieve** specific data from one or more tables
52
What is the purpose of *database administration* in SQL?
Control database *management tasks*, including **backup**, **restore**, and overall administration
53
What does the DECIMAL data type store in SQL?
Fractional numeric values
54
What does the DATE data type in SQL store?
Year, month, and day
55
While some **NoSQL** databases might offer some level of _____1___, it's not a defining feature.
1. **SQL compatibility** ; NoSQL are named for their _distinction_ from traditional SQL databases.
2. NoSQL databases often use different query languages or data models.
56
What is an Attribute?
1. In essence, an attribute is a characteristic of something.
2. But a Unnecessary attribute can be removed *without losing the ability* to uniquely identify each row.
57
relational model development
- Developed by E. F. Codd of **IBM** in 1970, the relational model is based on *mathematical set theory* and represents data as independent relations
58
# Refer to the ER
CustomerID is:
*Columns that refer to a foreign key* have an empty bullet prefix.
59
# Refer to the ER
The arrow from Sale to Book corresponds to which column of Sale?
The BookID
60
# Refer to the ER
What element of the ER diagram does the BookAuthor table implement?
Writes
61
What are the two parts of logical design?
1. Either a specification for tables, keys, and columns, or
2. The process that creates the specification
62
A schema is depicted in:
A schema specifies the *result of logical design*, not the result of analysis.
63
# Refer to the ER
What is 'Writes'?
* Author-*Writes*-Book is a **relationship** between the Author and Book entities.
* Relationships are depicted as lines on ER diagrams.
64
# Refer to the ER
What is 'Category'?
*Category* appears inside the Book rectangle. Therefore *Category* is an **attribute**.
65
# Refer to the ER
What is 'Sale'?
**Entities** are depicted as rectangles on ER diagrams.
## Footnote
A sale is a thing tracked in the database, with properties unit price, quantity, and date
66
What are the three phases of the **database design process** for large, complex databases?
1. Analysis/conceptual
2. Logical Design
3. Physical Design
67
In ER diagrams, what do rectangles represent?
Entities
68
What do lines between rectangles in ER diagrams represent?
Relationships
69
What is depicted inside rectangles and below entity names in ER diagrams?
Attributes
70
What is SQL identified as?
The standard relational query language
71
**SQL** is not considered a fully programming language like its counterparts $$?
While SQL is a powerful language for managing data, it's not generally considered a full-fledged programming language like **Python** or **Java**
72
What is an API in the context of database programming?
A library of *procedures or classes* that connects a host programming language to a database
73
What is an example of an API for Java?
**Java Database Connectivity** (JDBC) an (API) that allows Java programs to connect to databases
74
What role do cursors play in database programming?
They help bridge the gap between SQL and host languages
75
What does the *logical design phase* implement?
Database requirements in a **specific** database system
76
In a relational database, what do keys identify?
Individual rows of a table
77
What is a database schema?
The _logical design_ specified in SQL, depicted in a table diagram
78
What do solid bullets (●) indicate in table diagrams?
Key columns
79
What is the overall process of *determining and implementing* database requirements called?
Database design
80
What does the **analysis phase** of database design involve?
## Footnote
Intertwined with conceptual
Gathering and documenting database requirements
81
What principle states that **physical design** affects *query performance* but _not_ *query results*?
Data independence
82
What is the difference between logical design and physical design?
1. Logical design **structures** data requirements
2. Physical design focuses on **storage details** and indexing
83
What is information independence often used interchangeably with?
Data independence
84
What is the purpose of the *physical design phase*?
To determine how the database will be stored physically, including **indexing** strategies
85
Fill in the blank: A database **design** is defined as the specification of database objects such as tables, columns, data types, and _______.
Indexes
86
How does data independence benefit relational databases?
It allows changes to the physical design without impacting application programs
87
True or False: Logical design is more important for database users and programmers than physical design.
True
88
What is the *visual representation* of database requirements in the analysis phase?
Entity-Relationship (ER) diagrams
89
What does the logical design phase result in?
Tables, keys, and columns
90
What affects the speed of query processing in a relational database?
Physical design
91
What is MySQL?
A leading relational database management system **(RDBMS)** sponsored by Oracle
92
What does RDBMS stand for?
Relational Database Management System
93
What is SQL?
Structured Query Language, a standard **programming language** for managing databases
94
What is an API?
Application Programming Interface, a set of routines and tools for _building software_
95
What operating systems does MySQL run on?
Windows, Linux, Unix, and macOS
96
What are the two editions of MySQL?
* MySQL Community (free edition)
* MySQL Enterprise (paid edition)
97
What is the MySQL *Command-Line Client*?
A lightweight SQL shell (text interface) that allows *users to interact with a MySQL server:*
1. Create and manage databases and tables,
2. Add new rows
3. Fetch data
4. Connect to a server on a remote or local host
5. Specify a database to connect to
98
How do you start the MySQL Command-Line Client?
1. Open Command Prompt (Windows)
2. Terminal (macOS)
3. Enter the appropriate command
99
What happens when an SQL statement is incorrectly written in MySQL?
MySQL returns error codes for troubleshooting
100
What is MySQL Workbench?
A graphical user interface (GUI) tool for interacting with MySQL Server
101
What can users do with MySQL Workbench?
* Execute SQL commands
* Manage databases visually
102
What is the root account in MySQL?
The account with the highest privileges that *can create other user accounts*
103
What must users enter to connect to MySQL through the *Command-Line Client*?
The root password set during installation
104
Fill in the blank: MySQL returns error codes when an SQL statement is ______.
incorrectly written
105
What is the command to start the MySQL *Command-Line Client* with a username?
Use the -u option followed by the username
106
What is the purpose of the SELECT statement in SQL?
To retrieve data from a database
107
ERROR 1064 (42000): Syntax error
## Footnote
This is an example of _?
An example of an **error code** in MySQL?
108
Where can users find the latest versions of MySQL Server?
The MySQL download page
109
What does the MySQL Installation Video for Windows cover?
Installation steps and configurations for **MySQL Server** on Windows
110
True or False: The root account password is set during MySQL installation.
True
111
Does the *MySQL Command-Line Client* provide a graphical interface?
No, it is a text-based interface
112
What must be clicked to execute SQL statements in MySQL Workbench?
The lightning bolt icon
113
What is the first step to download MySQL?
Navigate to the MySQL download page
114
What is the Sakila database used for?
Learning and practicing SQL commands
115
What is the overall MySQL ranking compared to all database systems?
2
## Footnote
Oracle, MySQL, Microsoft SQL Server, PostgreSQL
116
What does MySQL Documentation - *Problems and Common Errors* provide?
Solutions to frequently encountered issues and errors
117
Fill in the blank: MySQL Workbench allows users to manage databases ______.
visually
118
What is the purpose of the MySQL Documentation - *Installation*?
Guide on the installation process for MySQL
119
What is the MySQL Workbench home screen used for?
To connect to MySQL Server running on the same computer
120
How can users troubleshoot issues in MySQL?
Refer to the *problems and common errors* documentation
121
Are these tables the same?
{ (8, mango, FALSE), (-11, watermelon, FALSE) }
{ (-11, watermelon, FALSE), (8, mango, FALSE) }
Both tables contain the same rows. Since a table is a set, the order of the rows does not matter.
122
Explain this action
1. A query selects a specific row by specifying one or more row values.
2. This selects rows with the value *2594* in the *EmployeeID* column. If each row has a different value, only one row is selected.
3. In this example, the result of the query would be "Jane Doe".
123
What were the dominant database models in the 1960s and 1970s?
Hierarchical and network databases
124
Which terms are commonly used in database processing?
Table, column, row, data type
125
What are the equivalent mathematical terms for relation, attribute, tuple, and domain?
1. Relation: Table
2. Attribute: Column
3. Tuple: Row
4. Domain: Data Type
126
What operation selects a specific row by specifying one or more row values?
SELECT
127
What does the SELECT query do in relational databases?
Selects specific rows from a table based on _conditions_
128
Fill in the blank: The _______ operation eliminates one or more columns of a table.
Project
129
What is the function of the `JOIN` operation in relational databases?
Combines *two tables* by comparing related columns.
## Footnote
UNION: Combines result sets vertically without comparing columns.
130
List the fundamental relational operations.
* Select
* Project
* Join
* Union
* Intersect
* Aggregate
131
Why is the *result of a relational operation* not stored in the database?
1. Relational operations (like SELECT, JOIN) *produce temporary result sets*, not permanent changes to the database.
2. Databases store base tables with raw data; storing every result would be redundant and inefficient.
## Footnote
Ex- the list of books you find isn't stored in the library, it's just a temporary result of your search.
132
What are SQL constraints used for in relational databases?
To enforce relational rules and maintain data integrity
133
What is a primary key constraint?
_Ensures_ that each table has a unique identifier
134
What does a foreign key constraint establish?
A link between two tables by referencing a primary key
135
Fill in the blank: The _______ constraint ensures that a column cannot contain null values.
Not Null
136
What is the purpose of a check constraint in a database?
To enforce specific _conditions_ on data in a column
137
Define the term 'Delete Cascade' in the context of relational databases.
Automatically deletes *related records* when a record in one table is deleted
138
What is the significance of *unique employee identifiers* in a database?
Ensures that no two employees share the same ID
139
List some common business rules in relational databases.
* Valid entries in required fields
* Salary constraints
* Department code validity
* Product availability check
140
What is the structure of a table in a relational database?
A table has a name, fixed columns, and variable rows
141
A _______ is an unordered collection of elements in set theory.
Set
142
What defines a tuple in set theory?
An ordered collection of elements
143
True or False: Table rows in a relational database have an inherent order.
False
144
1. Who introduced the relational model and when?
2. What was the *initial hindrance* to commercial adoption?
1. E. F. Codd in 1970
2. As relational databases matured, **processing speed** rapidly *caught up with* speedy competitions; non-relational databases.
145
What was the primary goal of the relational model when it was established?
To handle transactional data with simplicity
146
What is the role of SQL in relational databases?
Standardized language for defining and manipulating relational data
147
List the core operations of relational algebra.
* Select
* Project
* Join
* Union
* Intersect
* Aggregate
148
What does the **aggregate** operation do in relational databases?
Computes **functions** over multiple table rows
## Footnote
such as sum and count
149
Fill in the blank: The _______ operation selects rows common to two tables.
Intersect
150
What type of database model is still dominant despite the *emergence of alternatives*?
Relational database model
151
What do Empty bullets (○) and arrows indicate?
**foreign keys** use empty bullets with arrows pointing to referenced tables
152
What is a foreign key?
A foreign key is a column in one table that *references the primary key* of another table to establish relationships between data.
153
Do column names need to match for foreign keys?
No, the column names don't need to match; what matters is that the values correspond.
154
Can you provide an example of a foreign key relationship?
Yes, an Order Table could have a CustomerID column (foreign key) that links to a Customer table's ID column (primary key).
## Footnote
Even though the column names differ (CustomerID vs ID), the relationship works because the values match.
155
What does the 'sales' foreign key indicate to the database?
The foreign key tells the database that 'this order belongs to the customer with this ID.'
156
This displays what type of SQL syntax?
Literals
1. Explicit values that are *string, numeric, or binary*.
2. Strings must be surrounded by single quotes or double quotes.
3. Binary values are represented with x'0' where the 0 is any hex value.
157
This displays what type of SQL syntax?
Keywords
1. Words with special meaning.
158
This displays what type of SQL syntax?
Identifyer
1. Objects from the database like tables, columns, etc.
159
This displays what type of SQL syntax?
Comments
1. Statement intended only for humans and *ignored by the database* when **parsing** an SQL statement.
160
The INSERT statement adds a student to the Student table. How many clauses are in the INSERT statement?
1. The INSERT INTO clause is followed by the VALUES clause.
2. This INSERT statement consists of two **main parts** rather than two distinct clauses.
161
The SQL statement below is used to select students with the last name "Smith". What is wrong with the statement?
1. 'Single' or "double" quotes must surround string literals.
2. The literal "Smith" must be surrounded by single or double quotes
162
What is wrong with the SQL statement
1. In MySQL and most other database systems, all SQL statements require a terminating semicolon.
2. The semicolon indicates the end of an SQL statement
163
What is wrong with the SQL statement
1. The double dashes preceding the 'FROM' clause cause the database to ignore the 'FROM' clause; because its a **comment**.
2. The 'FROM' clause is required in a 'SELECT' statement
164
What are the clauses in the image.
1. SELECT
2. FROM
3. WHERE
165
What is SQL?
SQL is the standard language for relational databases, used for *storing, manipulating, and retrieving* data.
166
What are the sublanguages of SQL?
1. DCL (Data Control Language)
2. DDL (Data Definition Language)
3. DML (Data Manipulation Language)
4. DQL (Data Query Language)
5. DTL (Data Transaction Language)
167
What are the main components of SQL syntax?
* Clauses (e.g., SELECT, FROM, WHERE)
* Keywords (e.g., INSERT, UPDATE)
168
What should you focus on to understand SQL syntax?
Mastering the structure of SQL statements, including clauses and keywords.
169
What is a common pitfall to avoid in SQL?
Being aware of *case sensitivity* and *proper statement formats* to reduce errors.
170
What does the SELECT clause do in SQL?
Retrieves data from one or more tables.
171
Fill in the blank: The _______ clause specifies the table from which to retrieve data.
FROM
172
What is the purpose of the `WHERE` clause?
Filters _records_ based on specified conditions.
173
What does the `ORDER BY` clause do?
Sorts the **result set** in ascending or descending order.
174
What does GROUP BY do in SQL?
Groups rows that have the *same values* in specified columns.
## Footnote
Keyword
175
What is the function of the HAVING clause?
Filters **groups** based on specific conditions
## Footnote
often used with GROUP BY.
176
What does JOIN do in SQL?
Combines rows from two or more tables based on a related column.
177
What is the difference between INNER JOIN and LEFT JOIN?
1. `INNER JOIN` returns *matching records* from both tables.
2. `LEFT JOIN` returns *all records from the left table* and matched records from the right table.
178
What command is used to add new records to a table?
`INSERT INTO`
179
What is the purpose of the UPDATE command?
Modifies existing records in a table.
180
What does the DELETE command do?
Removes existing records from a table.
181
The _______ clause specifies the maximum number of records to return.
LIMIT
182
What is a common misunderstanding regarding SQL keywords and identifiers?
Keywords are not case-sensitive, while identifiers often are case-sensitive.
183
What is a typical mistake to avoid when writing SQL _statements_?
Forgetting to add semicolons at the end of SQL statements.
184
What is an effective way to master SQL?
Regularly practice writing SQL queries on a diverse range of datasets.
185
What are some essential resources for learning SQL?
* Online tutorials (Codecademy, W3Schools)
* SQL documentation for specific dialects
186
What is a high-impact practice method for mastering SQL?
Engage in hands-on projects or datasets available online (Kaggle, etc.).
187
In order to create a new database called [petStore] you would use which statement?
CREATE DATABASE; statement
188
In order to deletes the database called [petStore] you would use which statement?
DROP DATABASE; statement
189
1. Assume the database system currently contains only one user database, called `university`.
2. Why can't the statement `CREATE DATABASE university;` create a second university database.
Each database is uniquely named, so _two_ databases named `university` cannot exist.
190
1. Assume the database system currently contains only one user database, called `university`.
2. The statement `DROP DATABASE nonprofit;` cannot delete the nonprofit database, because?
The statement `DROP DATABASE nonprofit;` cannot delete the `nonprofit` database because the `nonprofit` database does not exist.
191
In order to select the database called `world` you would?
USE world;
192
Which statement *must precede* a `SHOW TABLES` statement to see the tables from the `world` database?
USE world;
193
How would you shows all the columns in the `Country` table
`SHOW COLUMNS `
`FROM Country;`
194
What is the third columns name?
`Salary`
195
What is this componet
1. 30500 occupies the **cell** in the Salary column of the second row.
2. A cell is a single column of a single row.
## Footnote
Aka Field
196
What are the components of a column?
1. A column has both a name and a data type.
2. The name and data type are specified in the SQL language when a table is created.
197
This is against which rule of governing tables?
No duplicate column names
198
This is against which rule of governing tables?
Exactly one value per cell
199
This is against which rule of governing tables?
A table cannot have multiple rows with identical data
200
Explain each 1,2,3
Table Name, Column Name, Column Values
201
The 'Name' column contains what type of value?
The data type of `Name` is `VARCHAR(60)`, which specifies values of 0 to 60 characters.
202
Why would the statement `DROP Employee;` fail to delete the Employee table?
1. It does not include the keyword `TABLE`.
2. The correct syntax should be `DROP TABLE Employee;`
203
# Suppose the Table is ['Department']
How would add a column called `Description` to the `Department` table.
```
ALTER TABLE Department
ADD Description VARCHAR(50);
```
204
# Suppose the Table is `Department`
How would you rename column `Description` to `ShortDesc`
`ALTER TABLE Department
CHANGE Description ShortDesc
VARCHAR(50);`
205
# Suppose the Table is `Department`
How would you change column `ShortDesc` to accept up to 80 characters.
```
ALTER TABLE Department
CHANGE ShortDesc ShortDesc VARCHAR(80);
```
206
# Suppose the Table is `Department`
How would you delete the column `ShortDesc`?
```
ALTER TABLE Department
DROP ShortDesc;
```
207
```
SELECT Expression1, Expression2, ...
FROM TableName;
```
1. The `FROM` clause specifies the table from which rows are selected.
2. The `SELECT` clause specifies one or more expressions, separated by commas.
3. Expressions determine what values are returned for each row.
| SELECT with expressions
208
```
SELECT Column1, Column2, ...
FROM TableName;
```
1. The `FROM` clause specifies the table *from which* rows are selected.
2. The `SELECT` clause specifies one or more expressions, separated by commas.
3. Expressions determine what values are returned for each row.
| SELECT with columns
209
```
SELECT *
FROM TableName;
```
1. The `FROM` clause specifies the table from which rows are selected.
2. The `SELECT` clause specifies one or more expressions, separated by commas.
3. Expressions determine what values are returned for each row.
| SELECT with asterisks | To select all columns
210
What is an expression in SQL?
A combination of *operators, operands, and parentheses* that evaluates to a single value.
211
What are operands in SQL expressions?
Column names or fixed values that can vary in data type.
212
What is the purpose of the `WHERE` clause in a `SELECT` statement?
To filter _records_ from a database table based on specified conditions.
213
What happens if a row does not meet the condition in a WHERE clause?
The row is excluded from the result set.
214
What is the significance of operator precedence?
It dictates the order in which operators are evaluated in an expression.
215
Which operator has the highest precedence?
Parentheses.
216
What is the mnemonic for remembering operator precedence?
Please Excuse My Dear Aunt Sally; Cats Need Alot Of Love.
217
What types of operators are there in SQL?
Arithmetic, comparison, and logical operators.
218
What is the difference between unary and binary operators?
Unary operators operate on a single operand; binary operators require two operands.
219
What are the unary operators?
220
What is an example of a binary operator?
221
What does the `SELECT` statement do in SQL?
It **queries data**, specifying *columns to return* through the `SELECT` and `FROM` clauses.
222
How can you select all columns in a table using SQL?
`SELECT *
FROM TableName`
223
What is the `LIMIT` clause used for in SQL?
To restrict the *number of rows* returned, enhancing query performance.
224
Delete
`FALSE`
225
What are the comparison operators in SQL?
=, !=, <, >, <=, >=
226
What is the order of operations for evaluating expressions?
Parentheses, Exponents, Multiplication/Division, Addition/Subtraction, Comparison, NOT, AND, OR
227
Which operator has higher precedence the `=` operator or the `AND` operator.
The assignment operator `=` has lower precedence than the logical `AND` operator
228
What is the `CREAT TABLE` Statement
```
CREATE TABLE TableName (
Column1 DATA_TYPE,
Column2 DATA_TYPE,
...
ColumnN DATA_TYPE
);
```
229
What is the `DROP TABLE` Statement
```
DROP TABLE TableName
```
230
What is the `CREAT TABLE` & `DROP TABLE` Statement
```
CREATE TABLE TableName (
Column1 DATA_TYPE,
Column2 DATA_TYPE,
...
ColumnN DATA_TYPE
);
DROP TABLE TableName
```
231
The `Name` column does not have a default value which means what during value inserts?
`CREATE TABLE Employee (
ID SMALLINT UNSIGNED,
Name VARCHAR(60),
BirthDate DATE DEFAULT '2000-01-01',
Salary DECIMAL(7,2) DEFAULT 0.00
);
`
The `Name` column does not have a default value and is assigned `NULL` when omitted from an insert
232
Which Columns have assigned a default value of [?] when omitted from an insert?
* The second row uses the `BirthDate` default when the value is ommited from an insert.
* The third row uses the `Salary` default when the value is ommited from an insert.
233
1. What is the definition of a **referential integrity error**?
2. What typically causes this to happen?
1. This is a violation of a relationship between 2 tables in a Relational DB.
2. This typically happens when a foreign key in one table points to a primary key in another table, but the *value in the foreign key column does not match any existing values* in the primary key column.
234
What is Oracle Database?
1. A highly robust and *scalable enterprise-grade database system* known for its performance, security features, and stability.
2. Widely used in large organizations and for **mission-critical applications** such as financial systems and large-scale e-commerce.
235
What type of database is MySQL?
**Relational** : MySQL is a very popular *open-source database* known for its speed and reliability, commonly used in web applications.
## Footnote
Open source software is typically free to use and is developed through collaboration.
236
What is the primary purpose of Microsoft SQL Server?
A robust database system commonly used in enterprise environments with strong integration with Microsoft products.
## Footnote
Often used for business intelligence and data warehousing.
237
What distinguishes PostgreSQL?
1. A powerful **open-source** database known for its advanced features and _standards compliance_.
2. Excellent for complex data and applications *requiring high data integrity*.
238
What type of database is MongoDB?
NoSQL (Document)
## Footnote
Stores data in flexible, JSON-like documents and is great for *handling semi-structured data*.
239
What is the purpose of Redis?
An *in-memory data store* often used for caching, **session management**, and real-time data processing.
## Footnote
Extremely fast for read and write operations.
240
What does Elasticsearch excel at?
Full-text search, log analysis, and data visualization.
## Footnote
It is a powerful search and analytics engine.
241
What type of database is IBM DB2?
Relational
## Footnote
Known for its reliability and scalability, often used in large organizations with mainframe systems.
242
What is SQLite primarily used for?
A lightweight, *file-based database* commonly embedded in mobile apps and devices.
## Footnote
It requires no server.
243
What is MariaDB?
A community-developed fork of MySQL known for its **open-source** nature and strong community support.
## Footnote
Often seen as a drop-in replacement for MySQL.
244
What type of database is Cassandra?
NoSQL (Wide-Column)
## Footnote
Designed for handling massive amounts of data across multiple servers.
245
Oracle Database is widely used in _______.
large organizations and for mission-critical applications
246
Is MySQL is primarily used for enterprise-level applications?
No; MySQL is commonly used for *web applications*, particularly those built with _PHP_.
## Footnote
PHP: free, open source **programming language** that uses the *server-side* (the code is executed on the server).
247
List three example uses of PostgreSQL.
* Geospatial applications
* Scientific data management
* Financial systems
248
What is the main feature of Redis?
Extremely fast for *read and write* operations.
## Footnote
It is often used for caching and session management.
249
Elasticsearch is often used for _______ monitoring.
log
250
What is a common use of SQLite?
Mobile apps (iOS, Android)
## Footnote
SQLite is a lightweight database that *requires no server*.
251
What type of data does MongoDB handle well?
Semi-structured data
## Footnote
It stores data in flexible, JSON-like documents.
252
What is Oracle Database?
1. A *highly robust and scalable* enterprise-grade database system known for its performance, security features, and stability.
2. Widely used in large organizations and for **mission-critical applications** such as financial systems and large-scale e-commerce.
253
What type of database is MySQL?
Relational
## Footnote
It is a very popular open-source database known for its speed and reliability, commonly used in web applications.
254
What is the primary purpose of Microsoft SQL Server?
A robust database system commonly used in enterprise environments with strong integration with Microsoft products.
## Footnote
Often used for business intelligence and data warehousing.
255
What distinguishes PostgreSQL?
1. A powerful *open-source database* known for its advanced features and standards compliance.
2. Excellent for complex data and applications *requiring high data integrity*.
256
What type of database is MongoDB?
- **NoSQL** (Document)
- Stores data in *flexible*, JSON-like documents and is *great for handling semi-structured data*.
257
What is the purpose of Redis?
An *in-memory data store* often used for caching, session management, and **real-time data processing**.
## Footnote
Extremely fast for *read and write* operations.
258
What does Elasticsearch excel at?
Full-text search, log analysis, and data visualization.
## Footnote
It is a powerful search and analytics engine.
259
What type of database is IBM *DB2*?
Relational
## Footnote
Known for its reliability and scalability, often used in large organizations with _mainframe_ systems.
260
What is SQLite & it's primarily use?
A lightweight, *file-based database* commonly embedded in mobile apps and devices.
## Footnote
It requires no server.
261
What is MariaDB?
A community-developed **fork of MySQL** known for its open-source nature and strong community support.
## Footnote
Often seen as a drop-in replacement for MySQL.
262
What type of database is Cassandra?
1. NoSQL (Wide-Column)
2. Designed for handling massive amounts of data *across multiple servers*.
263
Oracle Database is widely used in _______.
large organizations and for mission-critical applications
264
MySQL is primarily used for w⃟h⃟a⃟t⃟ applications.
MySQL is commonly used for web applications, particularly those built with PHP.
265
* Geospatial applications
* Scientific data management
* Financial systems
These three example are uses of p⃟o⃟s⃟t⃟g⃟r⃟e⃟.
These three example are uses of PostgreSQL.
266
What is the main feature of Redis?
Extremely fast for *read and write* operations.
## Footnote
It is often used for caching and **session management**.
267
Elasticsearch is often used for _______ monitoring.
log
268
What is a common use of SQLite?
Mobile apps (iOS, Android)
## Footnote
SQLite is a lightweight database that requires no server.
269
What type of data does MongoDB handle well?
Semi-structured data
## Footnote
It stores data in flexible, JSON-like documents.
270
What commands change the database?
Insert, update, and delete
## Footnote
These commands modify the data in the database.
271
Which command does not change the database?
Select
## Footnote
The select query retrieves data without altering it.
272
Are insert, select, update, and delete the only types of commands *necessary to interact* with a database system?
No **Other commands are necessary** to *create* and *delete* databases, tables, columns, and *other database objects*.
273
What is the most common database commands?
Insert, select, update, and delete
## Footnote
These commands are frequently used to manage data.
274
What must be present in the database for an *update query to work*?
Data
## Footnote
An update query requires existing data to modify.
