Intro To Databases (COURSERA. META. Backend Development Certification) Flashcards
Revise SQL
1
Q
Name three typical use cases for databases.
A
Banks (customer and transaction data), hospitals (patient and staff data), and online stores (user profiles and shopping history).
2
Q
What has led to the revolution in database technology?
A
The rise of Big Data and the Internet of Things (IoT), which generate vast amounts of diverse data.
3
Q
List four key actions a database typically performs.
A
- Storing data,
- Forming relationships
- Filtering data
- Searching data.
4
Q
What practical project will you undertake during this course?
A
Building a fully operational database and setting up software for local and remote database management.
5
Q
According to Daniel, what is critical for a database engineer?
A
Creating an effective data layer for quick and accurate user responses, and influencing other aspects like user interfaces and APIs.
6
Q
What soft skills are emphasized by Daniel for database engineers?
A
Communication and organization, especially explaining technical work to team members and end-users.
7
Q
What advice does Daniel give about database development?
A
Avoid overcomplicating solutions, focus on current data needs, iterate frequently, and emphasize documentation.
8
Q
How should you relate technical concepts to real-life scenarios?
A
Use relatable examples, like a recipe book, to understand and apply database concepts.
9
Q
What additional task is important alongside coding according to Daniel?
A
Writing documentation, status updates, and enhancing project documentation.
10
Q
What are the prerequisites for the Introduction to Databases course?
A
No previous database or coding experience required, but eagerness to start coding is essential.
11
Q
What does CRUD stand for?
A
Create, Read, Update, Delete.
12
Q
What is database normalization?
A
The process of organizing data to reduce redundancy and improve data integrity.
13
Q
What is data?
A
Data are facts and figures about anything, such as name, age, email for a person, or order number, description, and quantity for a purchase.
14
Q
What is a database?
A
A database is electronic storage where data is organized systematically, making it manageable, efficient, and secure.
15
Q
What are entities in a database?
A
Entities are elements like employees, customers, products (physical) or orders, invoices (conceptual), stored in a table-like format.
16
Q
What do attributes and rows represent in a database table?
A
Attributes are the columns representing features of the entity, and rows are the instances of the entity.
17
Q
What are the different types of databases mentioned?
A
- Relational Databases: Use tables and relations.
- Object-Oriented Databases: Store data as objects.
- Graph Databases: Use nodes and edges to represent data and relationships.
- Document Databases: Use JSON objects organized into collections.
18
Q
How can databases be stored?
A
On-Premises: Hosted on a dedicated machine within an organization.
Cloud Databases: Hosted on the cloud, accessed via the Internet, providing lower-cost options for data management.
19
Q
What are the key takeaways about databases?
A
- Understand what a database is and how it functions.
- Identify real-world uses of databases.
- Understand how data is systematically organized within a database.
20
Q
Why are data relationships important in a database?
A
Data must be related to be processed into meaningful information.
21
Q
What is an example of a task that requires understanding data relationships in a database?
A
Retrieving customer details from one table and finding the corresponding order from another table.
22
Q
What fields are typically found in a customer table?
A
Customer ID, FirstName, LastName, Email.
23
Q
What is a primary key?
A
A unique field in a table that identifies each record (e.g., Customer ID).
24
Q
What fields are typically found in an order table?
A
Order ID (primary key), Customer ID (foreign key).
25
What is a foreign key?
A field in one table that connects to the primary key in another table (e.g., Customer ID in the order table).
26
What are the purposes of using charts for data presentation?
To help people understand data better and to illustrate data relationships visually.
27
What does a bar chart represent?
Categorical data with rectangular bars, where the heights of the bars are proportional to the values they represent.
28
What insight does the bookshop's bar chart from 2018 to 2022 provide?
2018 had the highest sales, while 2022 had the lowest sales.
29
How does a bubble chart compare different values?
By using bubbles of different sizes, with larger bubbles representing larger values.
30
What population sizes are represented by the largest bubbles in the 2015 bubble chart example?
China (1.4 billion) and India (1.3 billion).
31
What does a line chart show?
Trends over time by connecting data points with straight line segments.
32
What trend does the gold price line chart depict?
Changes in gold prices over a month, highlighting positive and negative changes.
33
How does a pie chart display data?
By showing how various data points make up a whole (100%) with each slice representing a percentage.
34
What percentage of students prefer soccer in the sports pie chart example?
50%
35
What additional types of charts are mentioned for different purposes?
Area charts, dual axis charts, Gantt charts, heat maps, and scatter plots.
36
What factors should be considered when choosing a chart for data presentation?
The target audience, the type and amount of data, the message, and the goal.
37
What is the best use of a line chart?
Identifying trends and predicting future data.
38
What is a simple and effective use of a pie chart?
Showing how various parts create a whole.
39
What are the chronological stages of database development?
1. 1970s-1990s: Flat files, hierarchical, and network databases.
1. 1980s-present: Relational databases.
1. 1990s-present: Object-oriented, object-relational, and web-enabled databases.
40
What is a flat file database?
A type of database that stores data in a single file or table, typically as text files.
41
How is data organized in a hierarchical database?
Data is stored hierarchically, representing one-to-many relationships.
42
How do network databases differ from hierarchical databases?
Network databases allow multiple parent and child relationships, representing many-to-many relationships.
43
What key concepts define a relational database?
Storing data in tables with rows and columns, using primary keys for unique IDs, and foreign keys to establish relationships between tables.
44
What is an object-oriented database?
A database that represents data as objects, aligning with object-oriented programming languages.
45
What are the main types of NoSQL databases?
1. Document Databases
1. Key-Value Databases
1. Wide-Column Databases
1. Graph Databases
46
Why are NoSQL databases preferred for handling unstructured data?
Because of their higher scalability, distributed architecture, lower costs, flexible schema, and ability to process unstructured and semi-structured data.
47
What advantages do NoSQL databases offer?
* Higher scalability
* Distributed
* Lower costs
* Flexible schema
* Can process unstructured and semi-structured data
* No complex relationships
48
What is SQL?
SQL (Structured Query Language) is the standard language used to interact with all databases, especially relational databases.
49
What are CRUD operations in SQL?
Create, Read, Update, and Delete operations.
50
Name some examples of relational databases that use SQL.
MySQL, PostgreSQL, Oracle, Microsoft SQL Server.
51
What is the role of a Database Management System (DBMS) in SQL?
A DBMS interprets and executes SQL instructions for the underlying database.
52
What does the CREATE command do in SQL?
It is used to create database objects like tables (part of DDL).
53
What is the purpose of the ALTER command in SQL?
It modifies the structure of database objects (part of DDL).
54
What does the DROP command do in SQL?
It removes database objects (part of DDL).
55
What does the INSERT command do in SQL?
It adds data to tables (part of DML).
56
How do you modify existing data in SQL?
Using the UPDATE command (part of DML).
57
What command is used to remove data from tables in SQL?
The DELETE command (part of DML).
58
What is the SELECT command used for in SQL?
To retrieve data from one or multiple tables (part of DQL).
59
What are the GRANT and REVOKE commands used for in SQL?
GRANT gives users access privileges to data, and REVOKE removes those privileges (part of DCL).
60
What is DDL in SQL?
Data Definition Language, used to define the structure of a database.
61
What is DML in SQL?
Data Manipulation Language, used to manipulate data within database objects.
62
What is DQL in SQL?
Data Query Language, used to read or retrieve data from the database.
63
What is DCL in SQL?
Data Control Language, used to control access to data within the database.
64
Give an example scenario where CRUD operations are used.
Creating a database for a college, inserting data, modifying data, and managing access.
65
How does SQL act as an interface between the database and its users?
By providing commands and sublanguages to manage and interact with the database effectively.
66
What are the advantages of SQL?
1. User-friendly
1. Interactive
1. Standard language
1. Portable
1. Comprehensive
1. Efficient
67
Why is SQL considered user-friendly?
It requires minimal coding skills and uses a set of keywords, making it easy to perform CRUD operations.
68
What makes SQL interactive?
It allows developers to write complex queries quickly.
69
How is SQL a standard language?
It can be used with all relational databases like MySQL, PostgreSQL, and Oracle, and has extensive support and resources available.
70
Why is SQL considered portable?
SQL code runs on any hardware and operating system, behaving the same across different platforms.
71
What makes SQL comprehensive?
It covers all aspects of database management, including creation, manipulation, retrieval, and security.
72
What are the subsets of SQL?
1. Data Definition Language (DDL)
1. Data Manipulation Language (DML)
1. Data Query Language (DQL)
1. Data Control Language (DCL)
73
What commands are included in Data Definition Language (DDL)?
1. CREATE: Creates database objects like tables.
1. ALTER: Modifies existing database objects.
1. DROP: Deletes database objects.
74
What commands are included in Data Manipulation Language (DML)?
1. INSERT: Adds data to tables.
1. UPDATE: Modifies existing data.
1. DELETE: Removes data from tables.
75
What is the main command in Data Query Language (DQL)?
SELECT: Retrieves data from the database.`
76
What commands are included in Data Control Language (DCL)?
1. GRANT: Gives users access privileges.
1. REVOKE: Removes access privileges.
77
How do you create a database using SQL?
`CREATE DATABASE college;`
78
How do you create a table using SQL?
```
CREATE TABLE student (
ID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DateOfBirth DATE
);
```
79
How do you insert data into a table using SQL?
```
INSERT INTO student (ID, FirstName, LastName, DateOfBirth)
VALUES (1, 'John', 'Murphy', '2000-01-01');
```
80
How do you update data in a table using SQL?
```
UPDATE student
SET DateOfBirth = '2000-02-01'
WHERE ID = 1;
```
81
How do you delete data from a table using SQL?
```
DELETE FROM student
WHERE ID = 3;
```
82
How do you query data from a table using SQL?
```
SELECT FirstName, LastName
FROM student
WHERE ID = 1;
```
83
What is SQL used for?
SQL is used for managing and retrieving data in relational databases.
84
What are the four main categories of SQL commands?
1. Data Definition Language (DDL)
1. Data Query Language (DQL)
1. Data Manipulation Language (DML)
1. Data Control Language (DCL)
1. Transaction Control Language (TCL)
85
What is the purpose of the CREATE command in DDL?
To create a database or tables.
86
What is the syntax for creating a table?
```
CREATE TABLE table_name (
column_name1 datatype(size),
column_name2 datatype(size),
column_name3 datatype(size)
);
```
87
What is the purpose of the DROP command in DDL?
To delete a database or table.
88
What is the syntax for dropping a table?
`DROP TABLE table_name;`
89
What is the purpose of the ALTER command in DDL?
To change the structure of a table.
90
What is the syntax for adding a column to a table?
`ALTER TABLE table_name ADD (column_name datatype(size));`
91
What is the syntax for adding a primary key to a table?
```
ALTER TABLE table_name ADD primary key (column_name);
```
92
What is the purpose of the TRUNCATE command in DDL?
To remove all records from a table without deleting the table.
93
What is the syntax for truncating a table?
`TRUNCATE TABLE table_name;`
94
What is the purpose of the COMMENT command in DDL?
To add comments to SQL statements.
95
What is the syntax for adding a comment in SQL?
```
-- Retrieve all data from a table
SELECT * FROM table_name;
```
96
What is the purpose of the SELECT command in DQL?
To retrieve data from tables.
97
What is the syntax for selecting data from a table?
`SELECT * FROM table_name;`
98
What is the purpose of the INSERT command in DML?
To add records to a table.
99
What is the syntax for inserting data into a table?
`INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3);`
100
What is the purpose of the UPDATE command in DML?
To modify data in a table.
101
What is the syntax for updating data in a table?
`UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;`
102
What is the purpose of the DELETE command in DML?
To delete data from a table.
103
What is the syntax for deleting data from a table?
`DELETE FROM table_name WHERE condition;`
104
What is the purpose of the GRANT command in DCL?
To provide user privileges.
105
What is the syntax for granting privileges?
`GRANT privilege_name ON object TO user;`
106
What is the purpose of the REVOKE command in DCL?
To remove user privileges.
107
What is the syntax for revoking privileges?
`REVOKE privilege_name ON object FROM user;`
108
What is the purpose of the COMMIT command in TCL?
To save all changes made in the current transaction.
109
What is the syntax for committing changes?
`COMMIT;`
110
What is the purpose of the ROLLBACK command in TCL?
To restore the database to the last committed state.
111
What is the syntax for rolling back changes?
`ROLLBACK;`
112
What is a database table?
A database table stores and organizes data logically using rows and columns.
113
What are the components of a database table?
Rows (Records/Tuples), Columns (Fields/Attributes), and Cells.
114
What do rows in a database table represent?
Individual entries or records.
115
What do columns in a database table define?
The type of data stored.
116
What are binary data types used for in SQL?
To store binary data like images and files, such as BINARY, VARBINARY.
117
What is a primary key?
A column or combination of columns that uniquely identifies each row in a table.
118
What is a composite primary key?
A primary key that combines multiple columns to create a unique identifier.
119
What is a foreign key?
A column in one table that links to the primary key of another table.
120
What are domain constraints?
Rules that define valid values for a column.
121
What are referential integrity constraints?
Ensure foreign key values exist in the referenced table.
122
Give an example of a primary key in a Student Table.
student_id
123
Give an example of a foreign key relationship.
student_id in the Department Table referencing the Student Table.
124
What is a table (entity) in a database?
A table stores data in rows and columns, representing a type of entity.
125
What are binary data types used for in SQL?
To store binary data like images and files, such as BINARY, VARBINARY.
126
What is an Entity Relationship Diagram (ERD)?
A visual representation of how entities relate to each other in a database.
127
What is cardinality of relationships?
Defines how entities are related: One-to-One, One-to-Many, Many-to-Many.
128
How are relationships established in the physical database structure?
Using foreign keys.
129
What does the logical structure of a database include?
The logical structure includes entities, attributes, their names, and data types, represented in an ERD.
130
What is the purpose of assigning data types to columns in a database table?
To keep the data consistent and define what type of data can be stored in each column.
131
What is the relational database model based on?
Tables (entities) and relations (connections between tables).
132
What is a key attribute?
A value used to uniquely identify a record in a table.
133
Give an example of a key attribute.
staff_id in a staff table.
134
What is a candidate key?
Any attribute that contains a unique value in each row.
135
Give examples of candidate keys.
staff_id and contact_number in a staff table.
136
Give an example of a composite key.
Combination of staff_name and staff_title.
137
What is a primary key?
The main key attribute used to uniquely identify records.
138
Give an example of a primary key.
staff_id in a staff table.
139
What is an alternate key (secondary key)?
A candidate key that was not selected as the primary key.
140
Give an example of an alternate key.
contact_number in a staff table.
141
What is a foreign key?
An attribute that references a unique key in another table.
142
What is a database?
An organized collection of data.
143
Give examples of real-world database usage.
Banking systems, online stores.
144
How is data organized in a database?
Data is organized into tables with related data.
145
What is SQL (Structured Query Language)?
A language used to interact with and manage databases.
146
What are the advantages of SQL?
1. Low entry level
1. Wide range of applications
1. Portability across operating systems
147
What are the main subsets of SQL?
1. DDL (Data Definition Language)
1. DML (Data Manipulation Language)
1. DQL (Data Query Language)
148
What are the main SQL commands?
1. CREATE, ALTER, DROP (DDL)
1. INSERT, UPDATE, DELETE (DML)
1. SELECT (DQL)
149
What is the purpose of data types in a database table?
To define the kind of data stored (e.g., INT, VARCHAR, DATE).
150
What did you learn about databases in this module?
Basics of databases, SQL, database structure, how data is stored, and methods for interacting with databases.
151
What is the purpose of data types in a database?
To define what kind of data is accepted by each field in a table and ensure columns accept the correct type of data.
152
What are the most common data types used in databases?
Numeric, string, and date and time data types.
153
What are numeric data types?
Data types that allow a column to store data as numbers in the database.
154
What is the integer data type used for?
For storing whole numbers.
155
What is the decimal data type used for?
For storing numbers with fractional values.
156
Give an example of a column that uses the integer data type.
Product quantity column.
157
Give an example of a column that uses the decimal data type.
Total price column.
158
What is TINYINT used for in a MySQL Database Management System?
For storing very small integer number values, with a maximum value of 255.
159
What is INT used for in a MySQL Database Management System?
For storing large integer number values, with a maximum value of over four billion.
160
What are string data types used for?
To define columns that accept both numeric and text characters.
161
What does CHAR stand for and what is it used for?
CHAR stands for character and is used to hold characters of a fixed length.
162
What does VARCHAR stand for and what is it used for?
VARCHAR stands for variable character and is used to hold characters of a variable length.
163
Give an example of a column that uses the CHAR data type.
Username column defined as CHAR(50).
164
Give an example of a column that uses the VARCHAR data type.
Student name column defined as VARCHAR(50).
165
What is TINYTEXT used for?
For defining columns that require less than 255 characters, like short paragraphs.
166
What is TEXT used for?
For defining columns that require less than 65,000 characters, like an article.
167
What is MEDIUMTEXT used for?
For defining columns that require up to 16.7 million characters, like the text of a book.
168
What is LONGTEXT used for?
For defining columns that require up to four gigabytes of text data.
169
What is the purpose of database constraints?
Constraints limit the type of data that can be stored in a table to ensure accuracy and reliability.
170
What happens if a data operation violates a constraint?
The operation is aborted.
171
What are column-level constraints?
Rules that apply to specific columns in a table.
172
What are table-level constraints?
Rules that apply to the entire table.
173
Give an example of a table-level constraint.
Foreign key constraints prevent actions that destroy links between tables.
174
What is the purpose of the NOT NULL constraint?
Ensures that data fields are always completed and never left blank.
175
Provide an example of a NOT NULL constraint in an SQL statement.
```
CREATE TABLE customer (
customer_id INT NOT NULL,
customer_name VARCHAR NOT NULL
);
```
176
What is the purpose of the DEFAULT constraint?
Sets a default value for a column if no value is specified.
177
Provide an example of a DEFAULT constraint in an SQL statement.
```
CREATE TABLE player (
player_name VARCHAR NOT NULL,
city VARCHAR DEFAULT 'Barcelona'
);
```
178
Why are NOT NULL constraints important?
They prevent empty value fields and ensure fields are always filled.
179
Why are DEFAULT constraints important?
They automatically fill a column with a default value if no data is provided.
180
How do constraints ensure data integrity?
By validating data entry and preventing incorrect data.
181
How do constraints automate data entry?
By reducing the need to repeatedly enter the same values.
182
How do constraints enforce rules in a database?
By applying specific rules to columns or tables to maintain consistency and reliability.
183
What is the purpose of creating a database?
To store and organize data relevant to the business needs, such as book titles, authors, customers, and sales for an online bookstore.
184
What is the SQL syntax to create a database?
`CREATE DATABASE database_name;`
185
What are the requirements for a database name?
The name must be meaningful, relevant, unique, and within 63 characters.
186
How do you create a database named "bookstore2_db"?
`CREATE DATABASE bookstore2_db;`
187
What is the purpose of dropping a database?
To remove an existing database from the system.
188
What is the SQL syntax to drop a database?
`DROP DATABASE database_name;`
189
How do you drop a database named "bookstore_db"?
`DROP DATABASE bookstore_db;`
190
What is the purpose of creating a table in a database?
To hold and organize data in a structured format within a database.
191
What must exist before you can create tables in a database?
A database must already be created on the server.
192
What is the SQL syntax to create a table?
```
CREATE TABLE table_name (
column1_name data_type,
column2_name data_type
);
```
193
How do you create a table named "customers" with columns "customer_name" and "phone_number"?
```
CREATE TABLE customers (
customer_name VARCHAR(255),
phone_number INT
);
```
194
What data type should be used for a column holding text data?
VARCHAR
195
What data type should be used for a column holding whole numbers?
INT
196
What is the purpose of altering tables in a database?
To restructure tables by adding, removing, or modifying columns and their attributes.
197
What are the SQL keywords used to inform the database of changes to a table?
ALTER TABLE
198
How do you add a new column to a table using SQL?
`ALTER TABLE table_name ADD (column_name data_type);`
199
Provide an example of an SQL statement to add columns age, country, and nationality to the students table.
`ALTER TABLE students ADD (age INT, country VARCHAR(50), nationality VARCHAR(255));`
200
How do you remove a column from a table using SQL?
`ALTER TABLE table_name DROP COLUMN column_name;`
201
Provide an example of an SQL statement to remove the nationality column from the students table.
`ALTER TABLE students DROP COLUMN nationality;`
202
How do you modify a column in a table using SQL?
`ALTER TABLE table_name MODIFY column_name new_data_type;`
203
Provide an example of an SQL statement to change the country column to hold 100 characters instead of 50.
`ALTER TABLE students MODIFY country VARCHAR(100);`
204
What is the purpose of the INSERT INTO clause in SQL?
To add new rows of data to a table.
205
Provide the basic syntax for inserting a single row of data into a table.
`INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3);`
206
Provide an example of an SQL statement to insert player data for Yuval into the players table.
`INSERT INTO players (ID, Name, Age, Start_date) VALUES (1, 'Yuval', 25, '2020-10-15');`
207
How do you insert multiple rows of data into a table using SQL?
```
INSERT INTO table_name (column1, column2, column3) VALUES
(value1, value2, value3),
(value4, value5, value6);
```
208
Provide an example of an SQL statement to insert player data for Mark and Karl into the players table.
```
INSERT INTO players (ID, Name, Age, Start_date) VALUES
(2, 'Mark', 27, '2020-10-12'),
(3, 'Karl', 26, '2020-10-07');
```
209
What is the purpose of the SELECT statement in SQL?
To retrieve data from a table.
210
Provide the basic syntax for retrieving all data from a table using SQL.
`SELECT * FROM table_name;`
211
Provide an example of an SQL statement to retrieve all data from the players table.
`SELECT * FROM players;`
212
What is the main objective of learning the CREATE TABLE statement?
To develop a practical understanding of how the CREATE TABLE statement works and to use its syntax correctly.
213
Why is it important to give meaningful names to your table and its columns/fields?
It helps in documentation and understanding the purpose of the table and columns.
214
Why do data types vary between database systems?
Different database systems support different data types (e.g., NUMBER in Oracle vs. INT in MySQL).
215
What should you do to ensure the correct data type usage in your database system?
Refer to the list of data types supported by the specific database system you are using.
216
Why is VARCHAR preferred for storing text-based data?
Because it saves space, taking 1 byte per character plus 2 bytes for length.
217
Provide an example of how much space VARCHAR takes for storing a name "Jen" with VARCHAR(100).
It takes 5 bytes (3 bytes for J, E, N, plus 2 bytes for length).
218
What is the basic syntax for creating a table in SQL?
```
CREATE TABLE table_name (
column1_name data_type(length),
column2_name data_type(length),
...
);
```
219
Provide an example of a CREATE TABLE statement for a table named customers.
```
CREATE TABLE customers (
CustomerId INT,
FirstName VARCHAR(40),
LastName VARCHAR(20),
Company VARCHAR(80),
Address VARCHAR(70),
City VARCHAR(40),
State VARCHAR(40),
Country VARCHAR(40),
PostalCode VARCHAR(10),
Phone VARCHAR(24),
Fax VARCHAR(24),
Email VARCHAR(60),
SupportRapid INT
);
```
220
What should follow the table name in the CREATE TABLE statement?
A pair of parentheses enclosing column definitions.
221
What should be included within the parentheses of the CREATE TABLE statement?
Column names, their respective data types, and the size or length of data that can be stored.
222
What is the purpose of the semicolon at the end of the CREATE TABLE statement?
To indicate the end of the SQL statement.
223
What is the data type used for storing numeric values in the customers table example?
INT
224
What data type and length are used for storing text-based data in the customers table example?
VARCHAR with varying lengths specified for each column.
225
What is the purpose of the SQL SELECT statement?
To retrieve data from a table and perform actions such as math calculations, date and time queries, and concatenation functions.
226
What is the basic syntax for a SQL SELECT statement?
`SELECT column_name FROM table_name;`
227
Provide an example of a SQL SELECT statement to retrieve player names from a players table.
`SELECT name FROM players;`
228
How do you retrieve data from multiple columns in a SQL SELECT statement?
`SELECT column1, column2 FROM table_name;`
229
Provide an example of a SQL SELECT statement to retrieve player names and skill levels from a players table.
`SELECT name, level FROM players;`
230
How do you retrieve all data from all columns in a table using a SQL SELECT statement?
`SELECT * FROM table_name;`
231
Provide an example of a SQL SELECT statement to retrieve all data from a players table.
`SELECT * FROM players;`
232
What is the purpose of the INSERT INTO SELECT statement?
To retrieve information from one or more tables and populate columns in another table.
233
What is the basic syntax for an INSERT INTO SELECT statement?
`INSERT INTO target_table (column_name) SELECT column_name FROM source_table;`
234
Provide an example of an INSERT INTO SELECT statement to populate the countryName column in a country table with data from the country column in a player table.
`INSERT INTO country_table (countryName) SELECT country FROM player_table;`
235
What are the main keywords used in an INSERT INTO SELECT statement?
INSERT INTO, SELECT, FROM
236
What should you do after writing an INSERT INTO SELECT statement?
Execute the command to insert data from the source table into the target table.
237
How can you verify that data has been correctly inserted using an INSERT INTO SELECT statement?
Check the target table to ensure the appropriate column has been populated with the correct data.
238
What is the purpose of the SQL UPDATE statement?
To update specific columns in a table for one or multiple records.
239
What is the basic syntax for a SQL UPDATE statement?
```
UPDATE table_name
SET column1 = 'new_value1', column2 = 'new_value2'
WHERE condition;
```
240
Provide an example of an UPDATE statement to update the home address and contact number for the student with ID 3.
```
UPDATE student_table
SET home_address = 'new_address', contact_number = 'new_number'
WHERE ID = 3;
```
241
How do you update the college address for all engineering students?
```
UPDATE student_table
SET college_address = 'Harper Building'
WHERE department = 'engineering';
```
242
How do you update multiple columns with one UPDATE statement?
```
UPDATE student_table
SET home_address = 'new_address', college_address = 'Harper Building'
WHERE department = 'engineering';
```
243
What is the purpose of the SQL DELETE statement?
To remove specific records or all records from a table.
244
What is the basic syntax for a SQL DELETE statement?
```
DELETE FROM table_name
WHERE condition;
```
245
Provide an example of a DELETE statement to remove the record of the student with the last name 'Miller'.
```
DELETE FROM student_table
WHERE last_name = 'Miller';
```
246
How do you delete records of all engineering students?
```
DELETE FROM student_table
WHERE department = 'engineering';
```
247
How do you delete all records from a table using a DELETE statement?
`DELETE FROM student_table;`
248
What is the key difference in the DELETE statement when removing multiple records vs. all records?
When removing multiple records, include a WHERE clause; when removing all records, omit the WHERE clause.
249
What are operators in SQL?
Operators are specific words or characters that help perform different activities in a database, similar to operation keys on a calculator.
250
What do arithmetic operators in SQL do?
Arithmetic operators perform calculations and return results, and are used with numerical data in SQL tables.
251
List the five main arithmetic operators in SQL.
1. Addition (+)
1. Subtraction (-)
1. Multiplication (*)
1. Division (/)
1. Modulus (%)
252
How do you perform an addition operation in SQL?
`SELECT column_name1 + column_name2 FROM table_name;`
`SELECT salary + allowance FROM employee;`
253
How do you use an addition operator in a WHERE clause?
`SELECT * FROM employee WHERE salary + allowance = 25000;`
Employees with IDs 1 and 4.
254
How do you perform a subtraction operation in SQL?
`SELECT column_name1 - column_name2 FROM table_name;`
`SELECT salary - tax FROM employee;`
255
How do you use a subtraction operator in a WHERE clause?
`SELECT * FROM employee WHERE salary - tax = 50000;`
256
Why are arithmetic operators useful in SQL?
They are useful for performing mathematical operations on the data in tables while retrieving them by writing SQL SELECT queries.
257
How can arithmetic operators be used in SQL clauses?
They can be used in the SELECT clause to retrieve data and in the WHERE clause to filter data based on specified conditions.
258
Provide an example of using the modulus operator in SQL.
`SELECT 100 % 10;`
0 (since 100 divided by 10 equals 10 with no remainder).
259
How do you write a SQL SELECT statement to double the tax amounts for each employee in a table?
`SELECT tax * 2 FROM employee;`
260
Write the SQL SELECT statement to find employees who must pay an amount of tax equal to 4000 after doubling the current tax value.
```
SELECT *
FROM employee
WHERE tax * 2 = 4000;
```
261
What is the result of the following SQL query: SELECT tax * 2 FROM employee; if the tax values are 1000, 2000, 2000, 1000?
```
tax * 2
2000
4000
4000
2000
```
262
How do you find the allowance percentage each employee receives using SQL?
`SELECT allowance / salary * 100 FROM employee;`
263
Write the SQL SELECT statement to find employees receiving an allowance of at least 5%.
```
SELECT *
FROM employee
WHERE allowance / salary * 100 >= 5;
```
264
What is the result of the SQL query SELECT allowance / salary * 100 FROM employee; if the salary and allowance values are (24000, 1000), (55000, 3000), (52000, 3000), and (24000, 1000)?
```
allowance / salary * 100
4.1667
5.4545
5.7692
4.1667
```
265
What is the purpose of using arithmetic operators in the WHERE clause of a SQL SELECT statement?
Arithmetic operators in the WHERE clause are used to filter out data based on specific arithmetic conditions applied to numerical columns.
266
What is the result of the following SQL query: SELECT * FROM employee WHERE allowance / salary * 100 >= 5; if the table contains (employee_id, employee_name, salary, allowance, tax) values (1, 'alex', 24000, 1000, 1000), (2, 'John', 55000, 3000, 2000), (3, 'James', 52000, 3000, 2000), (4, 'Sam', 24000, 1000, 1000)?
```
employee_id | employee_name | salary | allowance | tax
2 | John | 55000 | 3000 | 2000
3 | James | 52000 | 3000 | 2000
```
267
What does the modulus operator (%) do in SQL?
It returns the remainder when the numerical values of one column are divided by the numerical values of another column.
268
What is the syntax for using the modulus operator in SQL?
`SELECT column_name1 % column_name2 FROM table_name;`
269
How can you check if the number of hours worked by each employee is even or odd using SQL?
`SELECT hours % 2 FROM employee;`
270
What does a remainder of 0 indicate when using the modulus operator on the hours column?
It indicates that the number of hours worked is an even number.
271
How do you filter out employees who worked an even number of hours using the modulus operator in SQL?
`SELECT * FROM employee WHERE hours % 2 = 0;`
272
What is the output when the modulus operation returns 1 on the hours column?
It indicates that the number of hours worked is an odd number.
273
What are the key arithmetic operators in SQL?
Addition (+), Subtraction (-), Multiplication (*), Division (/), and Modulus (%).
274
How can arithmetic operators be used in SQL?
They can be used in the SELECT clause to perform mathematical operations on data and in the WHERE clause to filter data based on specific conditions.
275
What is an example of using the addition operator in SQL?
`SELECT salary + allowance FROM employee;`
276
How do you use the subtraction operator in SQL to find the remaining salary after tax deduction?
`SELECT salary - tax FROM employee;`
277
What SQL operator is used to add a $500 bonus to each employee's salary?
`SELECT salary + 500 FROM employee;`
278
How do you deduct $500 from each employee's salary using SQL?
`SELECT salary - 500 FROM employee;`
279
What is the SQL query to double the current annual salary of each employee?
`SELECT salary * 2 FROM employee;`
280
How can you determine the monthly salary of each employee using SQL?
`SELECT salary / 12 FROM employee;`
281
How do you check if an employee's ID is even or odd using SQL?
`SELECT ID % 2 FROM employee;`
282
What does the SQL query SELECT salary + 500 FROM employee; do?
Adds a $500 bonus to each employee's salary.
283
What does the SQL query SELECT salary - 500 FROM employee; accomplish?
Deducts $500 from each employee's salary.
284
What is the purpose of the query SELECT salary * 2 FROM employee;?
To double each employee's salary.
285
What does the query SELECT salary / 12 FROM employee; calculate?
The monthly salary of each employee.
286
What information does the query SELECT ID % 2 FROM employee; provide?
It determines if an employee ID is even (remainder 0) or odd.
287
What result would you get from SELECT salary + 500 FROM employee; if an employee's current salary is $3000?
$3500
288
If an employee's ID is 5, what would the query SELECT ID % 2 FROM employee; return?
1 (indicating an odd number)
289
Using the query SELECT salary - 500 FROM employee;, what is the new salary if the original salary was $2000?
$1500
290
In the query SELECT salary / 12 FROM employee;, if the annual salary is $36000, what is the result?
$3000 (monthly salary)
291
What does a result of 0 from the query SELECT ID % 2 FROM employee; indicate about the employee's ID?
The employee ID is even.
292
Name the common SQL comparison operators.
Equal to (=), Less than (<), Greater than (>), Less than or equal to (<=), Greater than or equal to (>=), Not equal to (<> or !=).
293
Write a SQL query to find employees earning exactly $18,000 per year.
`SELECT * FROM employee WHERE salary = 18000;`
294
Which operator would you use to find employees earning less than $24,000 per year?
Less than (<) operator.
295
Provide an example SQL query using the less than operator to find employees earning less than $24,000.
`SELECT * FROM employee WHERE salary < 24000;`
296
How would you write a query to find employees earning $24,000 or less per year?
`SELECT * FROM employee WHERE salary <= 24000;`
297
Write a SQL query to find employees earning $24,000 or more per year.
`SELECT * FROM employee WHERE salary >= 24000;`
298
Which operator checks if values are not equal in SQL?
Not equal to (<> or !=).
299
Provide a SQL query to find employees with a salary not equal to $24,000.
`SELECT * FROM employee WHERE salary <> 24000;`
300
Explain the result of the following query: SELECT * FROM employee WHERE salary <= 24000;
It returns all employees earning $24,000 or less per year.
301
What does the "=" operator do in SQL?
Checks for equality
302
What do the "<>" or "!=" operators do in SQL?
Check for inequality.
303
What does the ">" operator do in SQL?
Checks if something is greater than.
304
What does the ">=" operator do in SQL?
Checks if something is greater than or equal to.
305
What does the "<" operator do in SQL?
Checks if something is less than.
306
What does the "<=" operator do in SQL?
Checks if something is less than or equal to.
307
How would you write a SQL query to retrieve data for the employee with ID 1?
`SELECT * FROM employee WHERE employee_id = 1;`
308
How would you write a SQL query to retrieve data for the employee named James?
`SELECT * FROM employee WHERE employee_name = 'James';`
309
In SQL, what must you surround text values with when using the equality operator?
Single quotation marks.
310
What would be the result of the query SELECT * FROM employee WHERE employee_id = 1; on the provided table?
```
| employee_ID | employee_name | salary | hours | allowance | tax |
|-------------|---------------|--------|-------|-----------|------|
| 1 | Alex | 24000 | 10 | 1000 | 1000 |
```
311
What is the purpose of using comparison operators in the WHERE clause of a SELECT statement?
To filter out records from a table based on specific conditions.
312
How do you write a SQL query to find employees with an allowance less than 2500?
```
SELECT *
FROM employee
WHERE allowance < 2500;
```
313
How do you write a SQL query to find employees who worked for less than or equal to 10 hours?
```
SELECT *
FROM employee
WHERE hours <= 10;
```
314
What is the purpose of the ORDER BY clause in SQL?
The ORDER BY clause is used to sort data in either ascending (ASC) or descending (DESC) order.
315
Is the ORDER BY clause mandatory in a SELECT statement?
No, the ORDER BY clause is optional.
316
What is the default sort order if neither ASC nor DESC is specified in the ORDER BY clause?
The default sort order is ascending (ASC).
317
How do you write a basic ORDER BY clause to sort data by a single column?
```
SELECT column1, column2, ...
FROM table_name
ORDER BY column_name [ASC|DESC];
```
318
How do you write an ORDER BY clause to sort data by multiple columns?
```
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;
```
319
How does the data type of a column affect sorting with the ORDER BY clause?
Numeric data types are sorted in numerical order, and text-based or string data types are sorted in alphabetical order.
320
Write an SQL statement to sort students by nationality in ascending order.
```
SELECT ID, first_name, last_name, nationality
FROM student_table
ORDER BY nationality ASC;
```
321
Write an SQL statement to sort students by nationality in descending order.
```
SELECT ID, first_name, last_name, nationality
FROM student_table
ORDER BY nationality DESC;
```
322
Write an SQL statement to sort students by nationality in ascending order and date of birth in descending order.
```
SELECT ID, first_name, last_name, date_of_birth, nationality
FROM student_table
ORDER BY nationality ASC, date_of_birth DESC;
```
323
How do you select all columns in a table for sorting using the ORDER BY clause?
```
SELECT *
FROM table_name
ORDER BY column_name [ASC|DESC];
```
324
What is the basic syntax for using the ORDER BY clause in SQL?
```
SELECT *
FROM Employee
ORDER BY [ASC|DESC];
```
325
What happens if you do not specify ASC or DESC in the ORDER BY clause?
The data is sorted in ascending order by default.
326
How does the ORDER BY clause handle different data types?
It sorts numeric columns in numerical order, text columns in alphabetical order, and date columns in chronological order.
327
Write an SQL statement to sort customer data by CustomerId in descending order.
```
SELECT *
FROM customers
ORDER BY CustomerId DESC;
```
328
How do you sort a text column, such as City, in ascending order?
```
SELECT *
FROM customers
ORDER BY City;
```
329
How do you sort a date column, such as InvoiceDate, in ascending order?
```
SELECT *
FROM invoices
ORDER BY InvoiceDate;
```
330
Write an SQL statement to sort data by City in descending order.
```
SELECT *
FROM customers
ORDER BY City DESC;
```
331
How do you sort data by multiple columns, for example by BillingCity in ascending order and InvoiceDate in descending order?
```
SELECT *
FROM invoices
ORDER BY BillingCity ASC, InvoiceDate DESC;
```
332
What is the result of the following SQL query?
```
SELECT *
FROM invoices
ORDER BY InvoiceDate DESC;
```
The data is sorted from the largest to smallest date, which is in descending order.
333
If you want to sort the CustomerId column in ascending order, which SQL keyword can be omitted?
ASC can be omitted because ascending order is the default.
334
What is the purpose of the WHERE clause in SQL?
The WHERE clause is used to filter and extract records from a database that meet specified conditions.
335
Write the basic syntax of a SQL SELECT statement with a WHERE clause.
```
SELECT column_names
FROM table_name
WHERE condition;
```
336
How do you filter records where the column value is equal to a specified operand in SQL?
Use the equals operator (=) in the WHERE clause.
`SELECT * FROM student_table WHERE student_id = 01;`
337
List other comparison operators that can be used in the WHERE clause.
* Less than (<)
* Greater than (>)
* Less than or equal to (<=)
* Greater than or equal to (>=)
* Not equal to (<> or !=)
338
How does the BETWEEN operator work in the WHERE clause?
The BETWEEN operator filters records within a specific numeric or date range.
`SELECT * FROM Students WHERE DateOfBirth BETWEEN '2010-01-01' AND '2010-05-30';`
339
How does the LIKE operator work in the WHERE clause?
The LIKE operator filters records that match a specified pattern. % represents zero or more characters, _ represents a single character.
`SELECT * FROM student_table WHERE faculty LIKE 'Sc%';`
340
How does the IN operator work in the WHERE clause?
The IN operator filters records where the column value matches any value in a specified list.
`SELECT * FROM student_table WHERE country IN ('USA', 'UK');`
341
Write an example SQL query to retrieve details of students in the engineering faculty.
`SELECT * FROM student_table WHERE faculty = 'engineering';`
342
Write an SQL query to retrieve students born between January 1, 2010, and May 30, 2010.
`SELECT * FROM Students WHERE DateOfBirth BETWEEN '2010-01-01' AND '2010-05-30';`
343
Write an SQL query to retrieve students in the science faculty (starting with "Sc").
`SELECT * FROM student_table WHERE faculty LIKE 'Sc%';`
344
Write an SQL query to retrieve students from the USA and UK.
`SELECT * FROM student_table WHERE country IN ('USA', 'UK');`
345
Can the WHERE clause be used in other SQL statements apart from SELECT?
Yes, the WHERE clause can also be used in UPDATE and DELETE statements to filter records that should be updated or deleted.
346
What is the function of the ALL logical operator?
Used to compare a single value to all values in another set.
347
What is the function of the AND logical operator?
Allows for the existence of multiple conditions in an SQL statement's WHERE clause.
348
What is the function of the ANY logical operator?
Used to compare a value to any applicable value in the list as per the condition.
349
What is the function of the BETWEEN logical operator?
Used to search for values within a set of values, given the minimum value and the maximum value.
350
What is the function of the EXISTS logical operator?
Used to search for the presence of a row in a specified table that meets a certain criterion.
351
What is the function of the IN logical operator?
Used to compare a value to a list of literal values that have been specified.
352
What is the function of the LIKE logical operator?
Used to compare a value to similar values using wildcard operators.
353
What is the function of the NOT logical operator?
Reverses the meaning of the logical operator with which it is used.
354
What is the function of the OR logical operator?
Used to combine multiple conditions in an SQL statement's WHERE clause.
355
What is the function of the IS NULL logical operator?
Used to compare a value with a NULL value.
356
What is the function of the UNIQUE logical operator?
Searches every row of a specified table for uniqueness (no duplicates).
357
How would you use the WHERE clause to fetch invoices with a total value greater than $2?
```
SELECT *
FROM invoices
WHERE Total > 2;
```
358
How can you combine multiple conditions in the WHERE clause using the AND operator?
```
SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];
```
359
Provide an example of using the AND operator to fetch invoices with a total value greater than $2 and BillingCountry as the USA.
```
SELECT *
FROM invoices
WHERE Total > 2 AND BillingCountry = 'USA';
```
360
How can you combine multiple conditions in the WHERE clause using the OR operator?
```
SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN];
```
361
Provide an example of using the OR operator to fetch invoices where BillingCountry is either the USA or France.
```
SELECT *
FROM invoices
WHERE BillingCountry = 'USA' OR BillingCountry = 'France';
```
362
How can you use both AND and OR operators together in the WHERE clause to fetch invoices with a total value over $2 and BillingCountry as either the USA or France?
`SELECT * FROM invoices WHERE Total > 2 AND (BillingCountry = 'USA' OR BillingCountry = 'France');`
363
What is the purpose of the SELECT DISTINCT clause?
The SELECT DISTINCT clause is used to retrieve unique values from a column or columns in a table, eliminating duplicate records from the result set.
364
What is the basic syntax for using SELECT DISTINCT?
`SELECT DISTINCT column_name
FROM table_name;`
365
How does the SELECT DISTINCT clause work with a single column?
It removes duplicate entries and returns only unique values from the specified column.
366
How can you retrieve unique combinations of values from multiple columns using SELECT DISTINCT?
`SELECT DISTINCT column1, column2
FROM table_name;`
367
How does SELECT DISTINCT handle NULL values in columns?
SELECT DISTINCT treats NULL values as unique, so rows with NULL values will be included in the result as distinct entries.
368
Provide an example of using SELECT DISTINCT on a single column.
`SELECT DISTINCT country FROM students;`
369
Provide an example of using SELECT DISTINCT on multiple columns.
`SELECT DISTINCT faculty, country
FROM students;
`
370
How can SELECT DISTINCT be used with SQL aggregate functions like COUNT?
`SELECT COUNT(DISTINCT column_name)
FROM table_name;
`
`SELECT COUNT(DISTINCT country) FROM customers;`
371
What is an important point to remember about SELECT DISTINCT with multiple columns?
When multiple columns are specified, SELECT DISTINCT returns unique combinations of values for those columns.
372
Provide an example query to get unique billing countries from an invoices table.
`SELECT DISTINCT BillingCountry
FROM invoices
ORDER BY BillingCountry;
`
373
Provide an example query to get unique combinations of billing countries and cities from an invoices table.
`SELECT DISTINCT BillingCountry, BillingCity
FROM invoices
ORDER BY BillingCountry, BillingCity;
`
374
What happens when SELECT DISTINCT is used on a column containing NULL values?
It includes NULL as a unique value, treating it as distinct from other values.
375
What is the default order when using the SELECT DISTINCT clause?
The default order is ascending.
376
Provide an example query to find unique combinations of BillingCountry and BillingCity including NULL values.
`SELECT DISTINCT BillingCountry, BillingCity
FROM invoices
ORDER BY BillingCountry, BillingCity;
`
377
What does the SELECT DISTINCT clause do?
Eliminates duplicate values from the result set.
378
How do you use SELECT DISTINCT with a single column?
`SELECT DISTINCT column_name FROM table_name;`
379
How do you use SELECT DISTINCT with multiple columns?
`SELECT DISTINCT column1, column2 FROM table_name;`
380
Does SELECT DISTINCT consider NULL values as unique?
Yes, it treats any NULL values in the DISTINCT column(s) as unique.
381
What are some key skills acquired from this module?
Using SQL arithmetic operators, applying comparison operators, sorting data with ORDER BY, filtering data with WHERE, and eliminating duplicates with SELECT DISTINCT.
382
What is a database schema?
A blueprint of how data is organized and related in a database.
383
How are schema and database terms used in MySQL?
In MySQL, schema and database are interchangeable terms.
384
What does a SQL Server schema include?
A collection of components like tables, fields, datatypes, and keys.
385
What is a conceptual or logical schema?
It describes the structure of the entire database for all users in terms of entities and their relationships.
386
What does an internal or physical schema describe?
The physical storage of the database, including how data is stored on disk in the form of tables, columns, and records.
387
What is an external or view schema?
A user-specific view of the database that shows only relevant data for that user.
388
What are the three types of database schema?
Conceptual or logical schema, internal or physical schema, external or view schema.
389
What is an example of a logical schema?
A diagram depicting entities like Employee and Department with their attributes and relationships.
390
What is an example of an internal schema?
A detailed description of how the Employee table should physically store its data.
391
How does an external schema benefit different users?
It provides different views of the database tailored to the specific needs of different users.
392
What is the three-schema architecture?
A framework that includes conceptual, internal, and external levels of schema.
393
What is data modeling in the context of databases?
The process of designing the database schema, which serves as the skeleton of the database without storing actual data.
394
How do schemas provide security?
By granting permission to separate and protect database objects based on user access rights.
395
What command is used to create the cart_order table with foreign keys?
`CREATE TABLE cart_order (
orderID INT PRIMARY KEY,
customerID INT,
productID INT,
quantity INT,
order_date DATE,
status VARCHAR(100),
FOREIGN KEY (customerID) REFERENCES customer(customerID),
FOREIGN KEY (productID) REFERENCES product(productID)
);`
396
How would you create a composite primary key in SQL?
`CREATE TABLE delivery (
customer_id INT,
product_code INT,
PRIMARY KEY (customer_id, product_code)
);`
397
How would you define a foreign key in SQL?
```
CREATE TABLE order (
order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);
```
398
How would you define multiple foreign keys in SQL?
`CREATE TABLE order (
order_id INT PRIMARY KEY,
customer_id INT,
product_id INT,
FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
FOREIGN KEY (product_id) REFERENCES product(product_id)
);`
399
How do you select the 'automobile' database in SQL?
`USE automobile;`
400
Write the SQL syntax to create the vehicle table.
`CREATE TABLE vehicle(
vehicleID VARCHAR(10),
ownerID VARCHAR(10),
plateNumber VARCHAR(10),
phoneNumber INT,
PRIMARY KEY (vehicleID)
);`
401
How do you display all tables in the 'automobile' database?
`SHOW TABLES;`
402
How do you display the structure of the vehicle table in SQL?
`SHOW COLUMNS FROM vehicle;`
403
How do you alter the vehicle table to add a foreign key linking to the owner table?
`ALTER TABLE vehicle
ADD CONSTRAINT fk_owner
FOREIGN KEY (ownerID)
REFERENCES Owner(ownerID);`
404
What SQL command is used to add a foreign key to an existing table?
`ALTER TABLE table_name ADD FOREIGN KEY (column_name) REFERENCES referenced_table (referenced_column);`
405
What does the key type "PRI" indicate in MySQL?
It indicates that the column is a primary key.
406
What does the key type "UNI" indicate in MySQL?
It indicates that the column is a unique key.
407
What does the key type "MUL" indicate in MySQL?
It indicates that the column can contain duplicate values and is part of a foreign key relationship.
408
Differentiate between simple and composite attributes.
Simple attributes cannot be divided further (e.g., grade), while composite attributes can be split into sub-components (e.g., a name split into first and last name).
409
What is a single-valued attribute?
A single-valued attribute can store only one value per entity instance, like a date of birth.
410
Describe a multi-valued attribute and why it should be avoided.
A multi-valued attribute can store multiple values per entity instance, such as multiple email addresses. It should be avoided to maintain database normalization.
411
What is a derived attribute?
A derived attribute's value is calculated from other attributes, such as age derived from the date of birth.
412
What are the three common anomalies in non-normalized databases?
Insert anomaly, update anomaly, and deletion anomaly.
413
What is an insert anomaly?
An insert anomaly occurs when new data insertion requires additional data to be inserted, causing problems when a primary key column cannot contain empty fields.
414
Can you give an example of an insert anomaly?
In a college enrollment table, you cannot add a new course without enrolling new students and assigning them IDs, as the student ID column is the primary key and cannot be empty.
415
What is an update anomaly?
An update anomaly occurs when updating a record requires updating multiple other records, leading to potential data inconsistency if updates are missed.
416
Can you give an example of an update anomaly?
If the director of a department changes, you need to update the director's name for all students enrolled in that department. Missing any updates can lead to inconsistent data.
417
What is a deletion anomaly?
A deletion anomaly occurs when deleting a record causes unintended deletions of other related records.
418
Can you give an example of a deletion anomaly?
Deleting a student's record may result in losing the records for the entire department they were associated with.
419
How does normalization address anomalies in a database?
Normalization optimizes the database design by creating tables with a single purpose, thus fixing insert, update, and deletion anomalies.
420
What are the benefits of database normalization?
Fixes insert, update, and deletion anomalies, simplifies writing SQL queries, and ensures data consistency and accuracy.
421
Why is it important to address anomalies in database design?
To ensure efficient data management, prevent data duplication, avoid modification issues, and simplify data queries.
422
Name the three fundamental normalization forms.
The three fundamental normalization forms are:
* First Normal Form (1NF)
* Second Normal Form (2NF)
* Third Normal Form (3NF)
423
What issue does the First Normal Form (1NF) address?
The First Normal Form (1NF) addresses the issue of data atomicity, ensuring that each cell contains only a single instance of data and eliminating repeating data groups.
424
How does the First Normal Form (1NF) ensure data atomicity?
The First Normal Form (1NF) ensures data atomicity by organizing related data into separate tables where each column cell contains only one single instance of data.
425
What are the steps to normalize data to First Normal Form (1NF)?
Steps to normalize data to 1NF include:
1. Creating a separate table for entities (e.g., Patient Table, Doctor Table, Surgery Table).
1. Ensuring each column cell contains a single instance of data.
1. Removing repeating groups of data.
426
Provide an example SQL statement to create a normalized Patient Table in 1NF.
```
CREATE TABLE Patient (
PatientID VARCHAR(10) NOT NULL,
PatientName VARCHAR(50),
SlotID VARCHAR(10) NOT NULL,
TotalCost Decimal,
CONSTRAINT PK_Patient PRIMARY KEY (PatientID, SlotID)
);
```
427
What are the characteristics of the unnormalized table in the example?
The unnormalized table has repeating groups of data, multiple instances of data in cells, and difficulties in updating, querying, and assigning unique primary keys.
428
What is the main objective of the Second Normal Form (2NF)?
The main objective of 2NF is to avoid partial dependency relationships between data in a table.
429
What is partial dependency?
Partial dependency occurs when a non-key attribute depends only on part of a composite primary key.
430
When should you focus on partial dependencies in a table?
You should focus on partial dependencies in tables with composite primary keys.
431
How can you identify partial dependency in a table?
Check if any non-key attributes depend only on part of the composite key.
432
Give an example of a partial dependency in a patient table.
In the patient table, the patient's name depends only on PatientID, and the total cost depends only on SlotID.
433
How do you fix partial dependencies in a table?
Split the table to remove partial dependencies, ensuring that non-key attributes depend on the entire composite key.
434
What is required for a relation to be in the Third Normal Form (3NF)?
It must already be in the Second Normal Form (2NF) and have no transitive dependency.
435
What is a transitive dependency?
It occurs when a non-key attribute in a table is functionally dependent on another non-key attribute in the same table.
436
Why is transitive dependency not allowed in the Third Normal Form?
Because changing one non-key attribute would require changes in another non-key attribute, which violates the normalization rules.
437
What is the benefit of achieving the Third Normal Form in database design?
It helps develop a database that is easy to access and query, well-structured, well-organized, consistent, and without unnecessary data duplications.
438
What is data atomicity?
The principle that each field in a table should contain only one single value.
