Zbook Chapter 1 Flashcards
(356 cards)
1
Q
Information Management System
A
a software application that manages corporate data for a specific business function
2
Q
What is a database administrator responsible for?
A
Securing the database system against unauthorized users and enforcing procedures for user access and database system availability.
3
Q
What does a database designer determine?
A
The format of each data element and the overall database structure.
4
Q
What priorities must a database designer balance?
A
Storage, response time, and support for rules that govern the data.
5
Q
What does a database programmer do?
A
Develops computer programs that utilize a database and writes applications that combine database query languages and general-purpose programming languages.
6
Q
Who is a database user?
A
A consumer of data in a database who requests, updates, or uses stored data to generate reports or information.
7
Q
How do database users usually access the database?
A
Via applications, but they can also submit queries directly to the database system.
8
Q
A
9
Q
How can small databases shared by one or two users be managed?
A
In a text file or spreadsheet.
10
Q
Why are text files and spreadsheets inadequate for large, complex databases?
A
Because as databases grow in size, complexity, and use, they have special requirements that text files and spreadsheets cannot meet.
11
Q
What happens to query response time when many users and applications access large databases simultaneously?
A
Query response time degrades rapidly.
12
Q
How do database systems maintain fast response times?
A
By structuring data properly on storage media and processing queries efficiently.
13
Q
By structuring data properly on storage media and processing queries efficiently.
A
Because many database users should have limited access to specific tables, columns, or rows of a database.
14
Q
How do database systems ensure security?
A
By ensuring authorized users only access permissible data and protecting against hackers through encryption and restricted access.
15
Q
What do database systems ensure regarding data consistency?
A
That data is consistent with structural and business rules, such as synchronizing multiple copies of data and ensuring referenced data exists.
16
Q
What must database systems do in case of failures?
A
Recover from failures and restore the database to a consistent state without loss of data.
17
Q
What is a transaction in database systems?
A
A group of queries that must be either completed or rejected as a whole to ensure data consistency.
18
Q
Why must a transaction be processed completely or not at all?
A
Because executing some but not all queries can result in inconsistent or incorrect data.
19
Q
Give an example of a transaction that must be processed completely or not at all.
A
A debit-credit transaction where $100 is transferred from one bank account to another. Both the debit and credit queries must succeed or fail together.
20
Q
What must database systems do if a computer or application fails while processing a transaction?
A
Reverse partial results and restore the database to the values prior to the transaction.
21
Q
Why is it important to prevent conflicts between concurrent transactions?
A
To avoid conflicts that can occur when multiple transactions access the same data at the same time.
22
Q
Give an example of a conflict between concurrent transactions.
A
Sam selects a seat on a flight, and Maria purchases the same seat in a separate transaction before Sam completes his transaction, making the seat unavailable for Sam.
23
Q
What must database systems ensure about transaction results?
A
That transaction results are never lost and are always saved on storage media, regardless of application or computer failures.
24
Q
What does the architecture of a database system describe?
A
The internal components and the relationships between components.
25
What is the role of the query processor in a database system?
It interprets queries, creates a plan to modify the database or retrieve data, and returns query results to the application. It also performs query optimization.
26
How does the storage manager function in a database system?
It translates the query processor instructions into low-level file-system commands that modify or retrieve data and uses indexes to quickly locate data.
27
What is the responsibility of the transaction manager?
Ensuring transactions are properly executed, preventing conflicts between concurrent transactions, and restoring the database to a consistent state in the event of a failure.
28
What is the purpose of the log in a database system?
It contains a complete record of all inserts, updates, and deletes processed by the database, and is used by the transaction manager to restore the database in the event of a failure.
29
What is the catalog (or data dictionary) in a database system?
A directory of tables, columns, indexes, and other database objects used by other components to process and execute queries.
30
Why do database systems have different capabilities and component details?
Because some database systems do not support transactions and therefore have no transaction manager, and the storage manager implementation depends on the physical structure of data on storage media.
31
Steps for database query
A database system consists of:
Query processor
Storage manager
Transaction manager
Log
Catalog
An application sends queries to the query processor.
The query processor uses information from the catalog to perform query optimization.
The storage manager translates the query processor instructions into file-system commands and uses an index to quickly locate the requested data.
The transaction manager logs insert, update, and delete queries.
The result is sent back to the application.
32
Metadata
data about the database, such as column names and the number of rows in each table
33
What is a relational database?
A database that stores data in tables, columns, and rows, similar to a spreadsheet.
34
What does SQL stand for and what does it do?
SQL stands for Structured Query Language and includes statements to read/write data, create/delete tables, and administer the database.
35
For what types of databases are relational systems ideal?
Databases requiring an accurate record of every transaction, such as banking, airline reservations, and student records.
36
What is big data and how did it impact database systems?
Big data refers to massive volumes of online data with poorly structured or missing information, leading to the development of NoSQL systems optimized for big data.
37
What is NoSQL?
NoSQL stands for "not only SQL" and refers to non-relational systems optimized for big data.
38
How did the licensing model for database systems change after 2000?
Open source software became popular, allowing anyone to inspect, copy, and modify software with no licensing fee.
39
How are database systems ranked on db-engines.com?
By tracking product references on social media, internet searches, job websites, and technical websites.
40
CRUD operations
Create, Read, Update, and Delete data
41
What does the SQL statement INSERT do?
Inserts rows into a table.
42
What does the SQL statement SELECT do?
Retrieves data from a table.
43
What does the SQL statement UPDATE do?
Modifies data in a table.
44
What does the SQL statement DELETE do?
Deletes rows from a table.
45
What does the SQL CREATE TABLE statement do?
Creates a new table by specifying the table and column names.
46
What does the INT data type store?
Integer values.
47
What does the DECIMAL data type store?
Fractional numeric values.
48
What does the VARCHAR data type store?
Textual values.
49
What does the DATE data type store?
Year, month, and day.
50
What does VARCHAR(10) indicate?
A textual value with a maximum of ten characters.
51
What does DECIMAL(10, 3) indicate?
A numeric value with ten significant digits, including three after the decimal point.
52
What is a database design?
A specification of database objects such as tables, columns, data types, and indexes, and the process used to develop this specification.
53
How is the database design process different for small and large databases?
For small, simple databases, the process can be informal and unstructured. For large, complex databases, it has three phases: Analysis, Logical design, and Physical design.
54
What does the analysis phase specify?
Database requirements without regard to a specific database system, represented as entities, relationships, and attributes.
55
What is an entity in database design?
A person, place, activity, or thing.
56
What is a relationship in database design
A link between entities.
57
What is an attribute in database design?
A descriptive property of an entity.
58
What are some alternative names for the analysis phase?
Conceptual design, entity-relationship modeling, and requirements definition.
59
How are entities, relationships, and attributes depicted in ER diagrams?
Rectangles represent entities.
Lines between rectangles represent relationships.
Text inside rectangles and below entity names represent attributes.
60
What does the logical design phase implement?
Database requirements in a specific database system.
61
What does logical design convert in relational database systems?
Entities, relationships, and attributes into tables, keys, and columns.
62
What is a key in a table?
A column used to identify individual rows of a table.
63
How are tables, keys, and columns specified in SQL?
With CREATE TABLE statements.
64
How are tables depicted in a table diagram?
Rectangles represent tables.
Table names appear at the top of rectangles.
Text within rectangles and below table names represents columns.
Solid bullets (●) indicate key columns.
Empty bullets (○) and arrows indicate columns that refer to keys.
65
logical design
either a specification for tables, keys, and columns, or the process that creates the specification.
66
What does the physical design phase add and specify?
Adds indexes and specifies how tables are organized on storage media.
67
How is physical design specified?
With SQL statements such as CREATE INDEX.
68
Why are physical design diagrams not commonly used?
Because logical design is more important for database users and programmers.
69
How do logical and physical design affect queries differently?
Logical design affects the query result, while physical design affects query processing speed but not the result.
70
What is data independence?
The principle that physical design never affects query results, allowing performance tuning without changes to application programs.
71
Why is data independence important?
It allows database designers to tune query performance without changing applications, contributing to the rapid adoption of relational technology in the 1980s.
72
Tuple
an ordered collection of elements enclosed in parentheses.
Ex: (a, b, c) and (c, b, a) are different, since tuples are ordered.
73
Data Definition Language (DDL)
Defines the structure of the database.
CREATE TABLE Students (
ID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT
);
74
Data Query Language (DQL)
Retrieves data from the database
SELECT * FROM Students WHERE Age > 18;
75
Data Manipulation Language (DML)
Manipulates data stored in a database
INSERT INTO Students (ID, Name, Age) VALUES (1, 'Alice', 20);
UPDATE Students SET Age = 21 WHERE ID = 1;
DELETE FROM Students WHERE ID = 1;
76
Data Transaction Language (DTL)
Manages database transactions
BEGIN TRANSACTION;
UPDATE Students SET Age = 22 WHERE ID = 1;
COMMIT;
ROLLBACK;
77
What is the benefit of data independence in relational databases?
Applications can be programmed before physical design is in place, running slowly but generating correct results.
78
What is a major limitation of SQL?
SQL lacks important programming features and is not object-oriented.
79
How is SQL typically used in database programming?
SQL is combined with general-purpose languages like C++, Java, or Python.
80
What is an API in the context of database programming?
An API is a library of procedures or classes linking a host programming language to a database.
81
What is JDBC?
JDBC is a library of Java classes that access relational databases.
82
Do programming languages support multiple database APIs?
Yes, major languages like C++ and Java support several APIs.
83
Why is MySQL popular?
Easy to install and use, advanced capabilities, runs on all major OS.
84
What are the two editions of MySQL?
MySQL Community (free) and MySQL Enterprise (paid).
85
What is MySQL Community suitable for?
Non-commercial applications like education.
86
What does MySQL Enterprise include?
MySQL Server and additional administrative applications.
87
What is the MySQL Command-Line Client?
A text interface included in the MySQL Server download for connecting to the database server, performing administrative functions, and executing SQL statements.
88
MySQL Workbench
GUI of MySQL Server
89
What is a database model?
A conceptual framework for database systems with data structures, operations, and rules.
90
What is the relational model based on?
A tabular data structure
91
How are the data structure, operations, and rules of the relational model standardized?
SQL
92
Which database models were dominant in the 1960s and 1970s?
Hierarchical and network databases.
93
Hierarchical Model
Data Structure: Tree
Product Release: 1960s
Example System: IMS
Strengths: Fast queries, efficient storage
94
Network Model
Data Structure: Linked list
Product Release: 1970s
Example System: IDMS
Strengths: Fast queries, efficient storage
95
Relational Model
Data Structure: Table
Product Release: 1980s
Example System: Oracle Database
Strengths: Productivity and simplicity, transactional applications
96
Object Model
Data Structure: Class
Product Release: 1990s
Example System: ObjectStore
Strengths: Integration with object-oriented programming languages
97
Graph Model
Data Structure: Vertex and edge
Product Release: 2000s
Example System: Neo4j
Strengths: Flexible schema, evolving business requirements
98
Document Model
Data Structure: XML, JSON
Product Release: 2010s
Example System: MongoDB
Strengths: Flexible schema, unstructured and semi-structured data
99
Set Theory
Concept: The relational data structure is based on set theory.
Example: {a, b, c} and {c, b, a} are the same set.
100
Tuple
Concept: A tuple is an ordered collection of elements.
Example: (a, b, c) and (c, b, a) are different tuples.
101
Table Structure
Concept: A table has a name, a fixed tuple of columns, and a varying set of rows.
102
Column
A column has a name and a data type.
103
Row
A row is an unnamed tuple of values, each corresponding to a column.
104
Data Type
A data type is a named set of values from which column values are drawn.
105
Row Order
Since a table is a set of rows, the rows have no inherent order.
106
Select
Selects a subset of rows of a table.
107
Project
Eliminates one or more columns of a table.
108
Product
Lists all combinations of rows of two tables.
109
Join
Combines two tables by comparing related columns.
110
Union
Selects all rows of two tables.
111
Intersect
Selects rows common to two tables.
112
Difference
Selects rows that appear in one table but not another.
113
Rename
Changes a table name.
114
Aggregate
Computes functions over multiple table rows, such as sum and count.
115
What are relational rules?
Logical constraints that ensure data is valid in every relational database.
116
What is a unique primary key?
A primary key column, or group of columns, in which values may not repeat.
117
What is the rule about column names in a table?
Different columns of the same table must have different names.
118
What is the rule about duplicate rows in a table?
No two rows of the same table can have identical values in all columns.
119
What are business rules?
Constraints based on business policy and specific to a particular database.
EX: Constraints based on business policy and specific to a particular database.
120
How are relational rules implemented?
As SQL constraints and enforced by the database system.
121
What is an SQL statement?
A complete command composed of one or more clauses.
121
How should an SQL statement be written for good practice?
Each clause should be written on a separate line.
121
What does a clause in an SQL statement group together?
SQL keywords (e.g., SELECT, FROM, WHERE), table names (e.g., City), column names (e.g., Name), and conditions (e.g., Population > 100000).
121
What does Data Definition Language (DDL) do?
Defines the structure of the database.
122
How do SQL statements end in MySQL?
With a semicolon.
122
Are SQL keywords case sensitive?
No, SQL keywords like SELECT, FROM, and WHERE are not case sensitive.
122
Are identifiers like column names and table names case sensitive?
Yes, in many database systems, identifiers are case sensitive.
122
What does Data Query Language (DQL) do?
Retrieves data from the database.
122
What does Data Manipulation Language (DML) do?
Manipulates data stored in a database.
122
What does Data Control Language (DCL) do?
Controls database user access.
122
What does Data Transaction Language (DTL) do?
Manages database transactions.
123
What is an automated script in SQL?
A series of SQL statements that is executed repeatedly.
124
What does the SQL statement CREATE DATABASE DatabaseName do?
Creates a new database.
125
What does the SQL statement DROP DATABASE DatabaseName do?
Deletes a database, including all tables in the database.
126
What does the SHOW statement provide?
Information about databases, database contents (tables, columns, etc.), and server status information.
126
What does SHOW DATABASES do?
Lists databases available in the database system.
126
What does SHOW TABLES do?
Lists tables available in the currently selected database.
126
What does SHOW COLUMNS do?
Lists columns available in a specific table named by a FROM clause.
127
What does SHOW CREATE TABLE do?
Shows the CREATE TABLE statement for a given table.
127
What is a table in a relational database?
A table has a name, a fixed sequence of columns, and a varying set of rows.
127
What does the USE statement do?
Selects a database and is required to show information about tables within a specific database.
128
What is a row in a table?
An unnamed sequence of values, with each value corresponding to a column and belonging to the column's data type.
128
What does a column in a table have?
A name and a data type.
129
What is a cell in a table?
A single column of a single row.
130
What is the minimum and maximum number of columns and rows a table can have?
A table must have at least one column but can have any number of rows. A table without rows is called an empty table.
131
What is the rule about values per cell in a table?
Exactly one value per cell. Unknown data is represented with a special NULL value.
132
What is the rule about column names in a table?
No duplicate column names are allowed in the same table.
133
What is the rule about duplicate rows in a table?
No two rows may have identical values in all columns.
134
What is the rule about row order in a table?
Rows are not ordered, and the organization of rows on a storage device never affects query results.
135
What is data independence?
The principle that allows database administrators to improve query performance by changing the organization of data on storage devices without affecting query results.
136
What does the INT or INTEGER data type store?
Integer values.
137
What does the VARCHAR(N) data type store?
Values with 0 to N characters.
138
What does the DATE data type store?
Date values.
139
What does the DECIMAL(M, D) data type store?
Numeric values with M digits, of which D digits follow the decimal point.
140
What does the DROP TABLE statement do?
Deletes a table, along with all the table's rows, from a database.
141
What does the ADD clause in ALTER TABLE do?
Adds a column.
EX
ALTER TABLE TableName
ADD ColumnName DataType;
142
What does the CHANGE clause in ALTER TABLE do?
Modifies a column.
EX:
ALTER TABLE TableName
CHANGE CurrentColumnName NewColumnName NewDataType;
143
What does the DROP clause in ALTER TABLE do?
Deletes a column.
EX:
ALTER TABLE TableName
DROP ColumnName;
144
What is the transpose operation on a table?
An operation where rows become columns, and columns become rows.
145
What do integer data types represent?
Positive and negative integers.
146
Give examples of common integer data types.
INT (4 bytes) and SMALLINT (2 bytes).
147
What do decimal data types represent?
Numbers with fractional values.
148
Give examples of common decimal data types
FLOAT and DECIMAL
149
What do character data types represent?
Textual characters.
150
Give examples of common character data types
CHAR (fixed length) and VARCHAR (variable length).
151
What do date and time data types represent?
Date, time, or both.
152
Give examples of common date and time data types
DATE, TIME, DATETIME, and TIMESTAMP
153
What do binary data types store?
Data exactly as it appears in memory or computer files, bit for bit.
154
Give examples of common binary data types
BLOB, BINARY, VARBINARY, and IMAGE.
155
What do spatial data types store?
Geometric information, such as lines, polygons, and map coordinates.
156
Give examples of spatial data types
POLYGON, POINT, and GEOMETRY.
157
What do document data types contain?
Textual data in a structured format such as XML or JSON.
158
What are some additional specialized data types supported by databases?
MONEY (currency values), BOOLEAN (true-false values), BIT (zeros and ones), and ENUM (a small, fixed set of alternative values).
159
What is the storage size and range of TINYINT?
1 byte.
Signed range: -128 to 127
Unsigned range: 0 to 255.
160
What is the storage size and range of SMALLINT?
2 bytes
Signed range: -32,768 to 32,767
Unsigned range: 0 to 65,535.
161
What is the storage size and range of MEDIUMINT?
3 bytes.
Signed range: -8,388,608 to 8,388,607.
Unsigned range: 0 to 16,777,215.
162
What is the storage size and range of INTEGER or INT?
4 bytes
Signed range: -2,147,483,648 to 2,147,483,647
Unsigned range: 0 to 4,294,967,295.
163
What is the storage size and range of BIGINT?
8 bytes
8 bytes. Signed range: -2^63 to 2^63 - 1
Unsigned range: 0 to 2^64 - 1.
164
What does DECIMAL(M,D) represent?
Exact decimal number where M = number of significant digits, D = number of digits after the decimal point.
165
What is the storage size and range of FLOAT?
4 bytes
Approximate decimal numbers with range: -3.4E+38 to 3.4E+38.
166
What is the storage size and range of DOUBLE?
8 bytes
Approximate decimal numbers with range: -1.8E+308 to 1.8E+308.
167
What is the format and range of DATE?
Format: YYYY-MM-DD.
Range: '1000-01-01' to '9999-12-31'.
168
What is the format of TIME?
Format: hh:mm:ss.
169
What is the format and range of DATETIME?
Format: YYYY-MM-DD hh:mm:ss
Range: '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
170
What is the storage size of CHAR(N)?
N bytes
Fixed-length string of length N; 0 ≤ N ≤ 255
171
What is the storage size of VARCHAR(N)?
Length of characters + 1 byte
Variable-length string with maximum N characters; 0 ≤ N ≤ 65,535.
172
What is the storage size of TEXT?
Length of characters + 2 bytes
Variable-length string with maximum 65,535 characters.
173
What is an operator?
A symbol that computes a value from one or more other values, called operands.
174
What do arithmetic operators compute?
Numeric values from numeric operands.
175
What do comparison operators compute?
Logical values TRUE or FALSE
Operands may be numeric, character, and other data types.
176
What do logical operators compute?
Logical values from logical operands.
177
What is a unary operator?
An operator with one operand.
178
What is a binary operator?
An operator with two operands
179
Give an example of a unary operator
The logical operator NOT.
180
Give an example of an operator that can be both unary and binary
The arithmetic operator -
181
What happens when either operand is NULL in an operation?
Operators may return NULL
182
What does the arithmetic operator + do?
Adds two numeric values.
Example: 4 + 3
Value: 7
183
What does the unary arithmetic operator - do?
Reverses the sign of one numeric value.
Example: -(-2)
Value: 2
184
What does the binary arithmetic operator - do?
Subtracts one numeric value from another.
Example: 11 - 5
Value: 6
185
What does the arithmetic operator * do?
Multiplies two numeric values.
Example: 3 * 5
Value: 15
186
What does the arithmetic operator / do?
Divides one numeric value by another.
Example: 4 / 2
Value: 2
187
What does the arithmetic operator % (modulo) do?
Divides one numeric value by another and returns the integer remainder.
Example: 5 % 2
Value: 1
188
What does the arithmetic operator ^ do?
Raises one numeric value to the power of another.
Example: 5^2
Value: 25
189
What does the comparison operator = do?
Compares two values for equality.
Example: 1 = 2
Value: FALSE
190
What do the comparison operators != and <> do?
Compare two values for inequality.
Example: 1 != 2 or 1 <> 2
Value: TRUE
191
What does the comparison operator < do?
Compares two values with <.
Example: 2 < 2
Value: FALSE
192
What does the comparison operator <= do?
Compares two values with ≤.
Example: 2 <= 2
Value: TRUE
193
What does the comparison operator > do?
Compares two values with >.
Example: '2019-08-13' > '2021-08-13'
Value: FALSE
194
What does the comparison operator >= do?
Compares two values with ≥.
Example: 'apple' >= 'banana'
Value: FALSE
195
What does the logical operator AND do?
Returns TRUE only when both values are TRUE.
Example: TRUE AND FALSE
Value: FALSE
196
What does the logical operator OR do?
Returns FALSE only when both values are FALSE.
Example: TRUE OR FALSE
Value: TRUE
197
What does the logical operator NOT do?
Reverses a logical value.
Example: NOT FALSE
Value: TRUE
198
What is an expression?
An expression is a string of operators, operands, and parentheses that evaluates to a single value.
Example: Salary > 34000 AND Department = 'Marketing'.
199
What is a simple expression?
A simple expression may consist of a single column name or a fixed value.
Example: EmployeeName or 'Maria'.
200
How are expressions evaluated?
Column names in expressions are replaced with column values for a specific row, so the expression's value can vary by row.
201
How does operator precedence affect expressions?
Operators are evaluated in the order of precedence. Operators of the same precedence are evaluated left to right. Parentheses override precedence.
202
Can expressions evaluate to NULL?
Yes, expressions can evaluate to NULL.
203
Operator Precedence
1 - (unary)
2 ^
3 * / %
4 + - (binary)
5 = != < > <= >=
6 NOT
7 AND
8 OR
204
What do the SELECT and FROM clauses specify?
The SELECT clause specifies expressions to return values for each row, and the FROM clause specifies the table from which rows are selected.
205
How do you write a SELECT statement with expressions?
SELECT Expression1, Expression2, ... FROM TableName;
206
How do you write a SELECT statement with column names?
SELECT Column1, Column2, ... FROM TableName;
207
How do you select all columns in a table?
Use an asterisk: SELECT *
FROM TableName;
208
How do you use the LIMIT clause to return only the first 100 rows from a table?
SELECT * FROM City LIMIT 100;
209
What is a condition in an expression?
A condition is an expression that evaluates to a logical value (TRUE, FALSE, or NULL).
210
What is the purpose of the WHERE clause in a SELECT statement?
The WHERE clause specifies a condition for selecting rows. Only rows where the condition is TRUE are selected.
211
What happens to rows when the condition in the WHERE clause is FALSE or NULL?
Rows are omitted when the condition is FALSE or NULL.
212
WHERE clause
SELECT Expression1, Expression2, ...
FROM TableName
WHERE Condition;
213
What is NULL?
NULL is a special value representing unknown or inapplicable data. It is not the same as zero or blanks.
214
What is the default behavior for column values regarding NULL?
By default, columns may contain NULL values.
215
What does the NOT NULL constraint do?
The NOT NULL constraint prevents a column from having a NULL value.
216
What happens when comparison operators are used with NULL values?
Comparison operators return NULL when either operand is NULL, so they cannot be used to select NULL values.
Example: =
217
How do you select rows where a value is NULL?
Use the IS NULL operator. Example: Value IS NULL returns TRUE when the value is NULL.
218
How do you select rows where a value is not NULL?
Use the IS NOT NULL operator. Example: Value IS NOT NULL returns TRUE when the value is not NULL.
219
Logical expressions when concerning a null value
TRUE AND TRUE is TRUE.
TRUE AND FALSE is FALSE.
TRUE AND NULL is NULL.
220
Truth Table
x y x AND y x OR y
TRUE NULL NULL TRUE
NULL TRUE
FALSE NULL FALSE NULL
NULL FALSE
NULL NULL NULL NULL
x NOT x
NULL NULL
221
What does the INSERT INTO clause specify?
The INSERT INTO clause names the table and columns where data is to be added. The keyword INTO is optional.
222
What does the VALUES clause specify?
The VALUES clause specifies the column values to be added.
Back: The VALUES clause may list any number of rows in parentheses to insert multiple rows.
223
What happens when columns are omitted from an INSERT statement?
When columns are omitted, they are assigned a NULL value. If the column has a NOT NULL constraint, the insert is rejected.
224
How can you specify a default value for a column?
Use the DEFAULT keyword and default value after the column name and data type in a CREATE TABLE statement.
225
What happens when a column with a default value is omitted from an INSERT statement?
The column is assigned the default value instead of NULL.
226
What does the UPDATE statement do?
The UPDATE statement modifies existing rows in a table.
227
What does the SET clause specify in an UPDATE statement?
The SET clause specifies the new column values.
228
What is the purpose of the WHERE clause in an UPDATE statement?
The WHERE clause specifies which rows are updated. Omitting the WHERE clause results in all rows being updated.
229
What does the DELETE statement do?
The DELETE statement deletes existing rows in a table.
230
What follows the FROM keyword in a DELETE statement?
The table name whose rows are to be deleted follows the FROM keyword.
231
What is the purpose of the WHERE clause in a DELETE statement?
The WHERE clause specifies which rows should be deleted. Omitting the WHERE clause results in all rows being deleted.
232
What does the TRUNCATE statement do?
The TRUNCATE statement deletes all rows from a table.
TRUNCATE TABLE TableName;
233
What is a primary key?
A primary key is a column, or group of columns, used to identify a row
234
Where is the primary key usually located in table diagrams?
The primary key is usually the table's first column and appears on the left of table diagrams, but its position is not significant to the database.
235
How is the primary key represented in table diagrams in this material?
A solid circle (●) precedes the primary key in table diagrams.
236
Can any unique column be specified as a primary key?
Yes, any unique column, or group of columns, may be specified as the primary key if the table contains several unique columns.
237
What does the uniqueness rule for primary keys ensure?
The uniqueness rule ensures that each value identifies at most one row.
238
What does the NOT NULL rule for primary keys ensure?
The NOT NULL rule ensures that each value identifies at least one row
239
What do the combined rules for primary keys ensure?
Together, the uniqueness and NOT NULL rules ensure that each primary key value identifies exactly one row.
240
What is a composite primary key?
A composite primary key consists of multiple columns used together to identify a row.
Example: (ColumnA, ColumnB).
241
What is the uniqueness requirement for composite primary keys?
Values of primary key columns, when grouped together, must be unique. No group of values may repeat in multiple rows.
242
What is the NOT NULL requirement for composite primary keys?
No column of a composite primary key may contain a NULL value.
243
What is the minimal requirement for composite primary keys?
All primary key columns are necessary for uniqueness. Removing any column makes the key no longer unique.
244
How are simple and composite primary keys different in terms of minimality?
Simple primary keys are necessarily minimal, as no column can be removed from a simple key.
245
What does the PRIMARY KEY constraint do in a CREATE TABLE statement?
The PRIMARY KEY constraint names the table's primary key and ensures that a column or group of columns is always unique and non-null.
246
What is an auto-increment column?
An auto-increment column is a numeric column that is assigned an automatically incrementing value when a new row is inserted.
247
How is an auto-increment column defined in a CREATE TABLE statement?
The AUTO_INCREMENT keyword follows the column's data type to define an auto-increment column.
248
How are integer primary keys commonly implemented?
Integer primary keys are commonly implemented as auto-increment columns.
249
To which columns can AUTO_INCREMENT be applied in MySQL?
In MySQL, AUTO_INCREMENT may be applied only to primary key columns.
250
What is a common error with auto-increment primary keys?
Inserting values for auto-increment primary keys.
251
What is a common error with primary keys that are not auto-increment columns?
Omitting values for primary keys that are not auto-increment columns.
252
Does MySQL allow inserting specific values to auto-increment columns?
Yes, MySQL allows it, but overriding auto-increment for a primary key is usually a mistake.
253
What is a foreign key?
A foreign key is a column, or group of columns, that refer to a primary key. The data types must be the same, but the names may be different.
253
Do foreign keys obey the same rules as primary keys?
No, foreign key values may be repeated and may be NULL.
EX: The Sales and Marketing departments can have the same manager, and the Technical Support department may not have a manager.
254
How are foreign keys represented in table diagrams in this material?
An empty circle (○) precedes foreign keys, and an arrow leads to the referenced primary key.
254
What is referential integrity?
Referential integrity requires that foreign key values must either be NULL or match some value of the referenced primary key.
254
Can multiple foreign keys refer to the same primary key?
Yes, multiple foreign keys can refer to the same primary key.
Example: In the DepartmentStaff table, the Manager and Assistant foreign keys both refer to the ID primary key of Employee.
255
Can a foreign key refer to a primary key in the same table?
Yes, a foreign key can refer to a primary key in the same table.
Example: In the EmployeeManager table, the Manager foreign key refers to the ID primary key.
255
What is required for a foreign key that refers to a composite primary key?
A foreign key that refers to a composite primary key must also be composite. All columns of the composite foreign key must either be NULL or match the corresponding primary key columns.
255
How is a foreign key constraint added in a CREATE TABLE statement?
A foreign key constraint is added with the FOREIGN KEY and REFERENCES keywords.
256
What happens when a foreign key constraint is specified?
The database rejects insert, update, and delete statements that violate referential integrity.
257
Foreign Key example
CREATE TABLE Department (
Code TINYINT UNSIGNED,
Name VARCHAR(20),
ManagerID SMALLINT UNSIGNED,
PRIMARY KEY (Code),
FOREIGN KEY (ManagerID) REFERENCES Employee(ID)
);
258
FOREIGN KEY and PRIAMRY constraint
Parentheses are required around both the foreign key and primary key column names.
Ex: FOREIGN KEY (ShipmentID) REFERENCES Shipment(ID)
259
What is referential integrity?
A relational rule requiring foreign key values to be either fully NULL or match a primary key value.
260
What is a fully NULL foreign key?
A foreign key where all columns are NULL.
261
Why must foreign keys obey referential integrity?
To ensure data consistency and prevent errors in the database.
262
What happens if referential integrity is violated?
Violations must be corrected before data is stored in the database.
263
How can referential integrity be violated?
A primary key is updated.
A foreign key is updated.
A row containing a primary key is deleted.
A row containing a foreign key is inserted.
264
What is the RESTRICT action in referential integrity?
RESTRICT rejects an insert, update, or delete that violates referential integrity.
265
What does the SET NULL action do?
SET NULL sets invalid foreign keys to NULL.
266
What is the purpose of the SET DEFAULT action?
SET DEFAULT sets invalid foreign keys to the foreign key default value
267
How does the CASCADE action work for primary key updates?
CASCADE propagates primary key changes to foreign keys, updating matching foreign keys to the same value.
268
How does the CASCADE action work for primary key deletes?
If a primary key is deleted, rows containing matching foreign keys are also deleted.
269
Which actions apply to both primary key updates and deletes, and foreign key inserts and updates?
RESTRICT, SET NULL, and SET DEFAULT.
270
Which action applies only to primary key updates and deletes?
CASCADE
271
What action does MySQL support for foreign key inserts and updates?
MySQL supports only RESTRICT for foreign key inserts and updates.
272
Which actions does MySQL support for primary key updates and deletes?
MySQL supports RESTRICT, SET NULL, SET DEFAULT, and CASCADE
273
Where are actions for primary key updates and deletes specified in MySQL?
In the optional ON UPDATE and ON DELETE clauses of the FOREIGN KEY constraint.
274
What do the ON UPDATE and ON DELETE clauses determine?
They determine what happens to the foreign key when the referenced primary key is updated or deleted.
275
Can different actions be specified for several foreign keys referring to the same primary key?
Yes, different actions can be specified for each foreign key.
276
What happens if the ON UPDATE or ON DELETE clause is omitted?
RESTRICT is applied.
277
When can SET NULL not be used?
When a foreign key is not allowed NULL values.
278
What is a constraint in a database?
A rule that governs allowable values in a database, based on relational and business rules.
279
What happens if an insert, update, or delete statement violates a constraint?
The database automatically rejects the statement.
280
What is a column constraint?
A constraint that appears after the column name and data type in a CREATE TABLE statement, governing values in a single column.
281
Give an example of a column constraint.
NOT NULL
282
What is a table constraint?
A constraint that appears in a separate clause of a CREATE TABLE statement, governing values in one or more columns.
283
Give an example of a table constraint.
FOREIGN KEY.
284
Can some constraint types be defined as either column or table constraints?
Yes, for example, a PRIMARY KEY constraint on a single column can appear either in the column declaration or a separate CREATE TABLE clause.
285
How must a PRIMARY KEY constraint on a composite column be defined?
As a table constraint.
286
FOREIGN KEY
A FOREIGN KEY constraint governs values in both the foreign key column(s) and the referenced primary key column(s). The foreign and primary keys may be either simple or composite. The FOREIGN KEY constraint must appear in a separate clause.
287
NOT NULL
The NOT NULL constraint governs values in a single column and must appear in a column declaration.
288
PRIMARY KEY
A simple PRIMARY KEY constraint governs values in a single column and may appear either in a column declaration or a separate clause. A composite PRIMARY KEY constraint governs values in multiple columns and must appear in a separate clause.
289
What does the UNIQUE constraint ensure?
It ensures that values in a column, or group of columns, are unique.
290
Where can the UNIQUE constraint appear when applied to a single column?
Either in the column declaration or a separate clause.
291
Where must the UNIQUE constraint appear when applied to a group of columns?
As a table constraint in a separate clause.
292
Is the UNIQUE constraint necessary for primary key columns?
No, because primary keys are automatically unique.
293
What does MySQL create for each UNIQUE constraint?
An index that stores the values of the unique column, or group of columns, in sorted order.
294
How does MySQL use the index created for a UNIQUE constraint?
It searches the index to quickly determine if new values are unique when they are inserted or updated.
295
What is the purpose of the SQL `IN` operator?
The `IN` operator specifies multiple possible values for a column in the `WHERE` clause, acting as a shorthand for multiple `OR` conditions
EX:
SELECT * FROM table_name
WHERE Language IN ('Dutch', 'Kongo', 'Albanian');
296
What does the SQL `BETWEEN` operator do?
It filters values within a specified range, including the boundary values.
EX:
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
297
What does the SQL `LIKE` operator do?
It matches text in a column against a specified pattern using wildcards.
298
What are the two wildcards used with `LIKE`?
`%`: Matches zero or more characters (e.g., `'L%t'` matches “Lt”, “Lift”).
`_`: Matches exactly one character (e.g., `'L_t'` matches “Lot” but not “Loot”).
299
Is the `LIKE` operator case-sensitive?
By default, it is case-insensitive but can be made case-sensitive using `BINARY` (e.g., `LIKE BINARY 'L%t'`)
300
How do you search for `%` or `_` as literal characters?
Use a backslash (`\`) before them, e.g., `'a\%'`.
301
What does the SQL `DISTINCT` clause do?
It returns only unique (non-duplicate) rows from a query result.
EX:
SELECT DISTINCT column1, column2, ...
FROM table_name
WHERE condition;
302
What does the `ORDER BY` clause do in SQL?
It sorts query results in ascending (default) or descending order based on one or more columns
By default it goes from lowest to highest or A-Z
Add DESC to do highest to lowest
EX:
SELECT columns FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];
303
What is a numeric function in SQL?
A function that operates on numeric data types (integer or decimal) and returns a numeric result.
304
Q: What does `ABS(n)` do?
A: Returns the absolute value of `n`.
305
Q: What does `LOG(n)` do?
A: Returns the natural logarithm of `n`.
306
Q: What does `POW(x, y)` do?
A: Returns `x` raised to the power of `y`.
307
Q: What does `RAND()` do?
A: Returns a random number between 0 (inclusive) and 1 (exclusive).
308
Q: What does `SQRT(n)` do?
A: Returns the square root of `n`.
309
Q: What does `CONCAT(s1, s2, ...)` do?
A: Returns a string by concatenating the arguments.
Example:
SELECT CONCAT('Dis', 'en', 'gage'); -- Returns: 'Disengage'
310
Q: What does `LOWER(s)` do?
A: Converts all characters in `s` to lowercase.
Example:
SELECT LOWER('MySQL'); -- Returns: 'mysql'
311
Q: What does `REPLACE(s, from, to)` do?
A: Replaces all occurrences of `from` with `to` in `s`.
Example:
SELECT REPLACE('This and that', 'and', 'or'); -- Returns: 'This or that'
312
Q: What does `SUBSTRING(s, pos, len)` do?
A: Extracts a substring from `s` starting at position `pos` with length `len`.
EX:
SELECT SUBSTRING('Boomerang', 1, 4); -- Returns: 'Boom'
313
Q: What does `TRIM(s)` do?
A: Removes leading and trailing spaces from `s`.
Example:
SELECT TRIM(' test '); -- Returns: 'test'
314
Q: What does `UPPER(s)` do?
A: Converts all characters in `s` to uppercase.
Example:
SELECT UPPER('mysql'); -- Returns: 'MYSQL'
315
Q: What does `CURDATE()` do?
A: Returns the current date in `YYYY-MM-DD` format.
Example:
SELECT CURDATE(); -- Returns: '2019-01-25'
316
Q: What does `CURTIME()` do?
A: Returns the current time in `HH:MM:SS` format.
Example:
SELECT CURTIME(); -- Returns: '21:05:44'
317
Q: What does `NOW()` do?
A: Returns the current date and time in `YYYY-MM-DD HH:MM:SS` format.
Example:
SELECT NOW(); -- Returns: '2019-01-25 21:05:44'
318
Q: What does `DATE(expr)` do?
A: Extracts the date part from a datetime expression.
Example:
SELECT DATE('2013-03-25 22:11:45'); -- Returns: '2013-03-25'
319
Q: What does `TIME(expr)` do?
A: Extracts the time part from a datetime expression.
Example:
SELECT TIME('2013-03-25 22:11:45'); -- Returns: '22:11:45'
320
Q: What do `DAY(d)`, `MONTH(d)`, and `YEAR(d)` do?
A: Extract the day, month, or year from a date.
Examples:
SELECT DAY('2016-10-25'); -- Returns: 25
SELECT MONTH('2016-10-25'); -- Returns: 10
SELECT YEAR('2016-10-25'); -- Returns: 2016
321
Q: What do `HOUR(t)`, `MINUTE(t)`, and `SECOND(t)` do?
A: Extract the hour, minute, or second from a time.
Examples:
SELECT HOUR('22:11:45'); -- Returns: 22
SELECT MINUTE('22:11:45'); -- Returns: 11
SELECT SECOND('22:11:45'); -- Returns: 45
322
Q: What does `DATEDIFF(expr1, expr2)` do?
A: Calculates the difference in days between two dates.
Example:
SELECT DATEDIFF('2013-03-10', '2013-03-04'); -- Returns: 6
323
Q: What does `TIMEDIFF(expr1, expr2)` do?
A: Calculates the difference in time between two time values.
Example:
SELECT TIMEDIFF('10:00:00', '09:45:30'); -- Returns: '00:14:30'
324
Q: What is an aggregate function in SQL?
A: A function that processes values from multiple rows and returns a single summary value.
EX:
COUNT() counts the number of rows in the set.
MIN() finds the minimum value in the set.
MAX() finds the maximum value in the set.
SUM() sums all the values in the set.
AVG() computes the arithmetic mean of all the values in the set.
325
Q: What does the `GROUP BY` clause do in SQL?
A: It groups rows with identical values in specified columns and computes aggregate functions for each group
326
Q: What does the `GROUP BY` clause do in SQL?
A: It groups rows with identical values in specified columns and computes aggregate functions for each group
327
Q: What is an SQL `JOIN`?
A: A `JOIN` combines rows from two or more tables based on a related column, typically using a foreign key and primary key relationship.
328
Q: What are the main types of SQL Joins?
A:
• INNER JOIN: Returns rows with matching values in both tables.
• LEFT JOIN (OUTER): Returns all rows from the left table, with matching rows from the right table or `NULL` if no match.
• RIGHT JOIN (OUTER): Returns all rows from the right table, with matching rows from the left table or `NULL` if no match.
• FULL JOIN (OUTER): Returns all rows from both tables, with `NULL` for non-matching rows.
329
Why are prefixes used in SQL joins?
A: To distinguish columns with the same name from different tables by prefixing the table name, e.g., `Table1.Column`
330
Q: What does an `INNER JOIN` do?
A: Selects only rows with matching values in both the left and right tables.
331
Q: What does a `FULL JOIN` do?
A: Selects all rows from both tables, with `NULL` for non-matching rows.
332
Q: How does the `ON` clause work in joins?
A: Specifies the condition to match rows between two tables, typically comparing a foreign key in one table to a primary key in another.
333
Q: What does a `LEFT JOIN` do?
A: Returns all rows from the left table and matching rows from the right table. Unmatched right table rows are filled with `NULL`.
334
Q: What does a `RIGHT JOIN` do?
A: Returns all rows from the right table and matching rows from the left table. Unmatched left table rows are filled with `NULL`.
335
Q: What is an outer join?
A: Any join that includes unmatched rows, such as `LEFT JOIN`, `RIGHT JOIN`, or `FULL JOIN`.
336
Q: Can joins be written without a `JOIN` clause?
A: Yes, joins can be written using alternative methods like `UNION` or subqueries, but using the `JOIN` clause is clearer and better practice.
