Master Revision Deck 1 Flashcards
(181 cards)
1
Q
[Understanding DB] What is a database?
A
A structured data storage designed for ease of data accessibility, manipulation, and manageability.
2
Q
[Understanding DB] Why use a database instead of storing data in files?
A
Databases support data integrity, allow constraints, enable efficient querying, and provide better security through authentication and authorization.
3
Q
[Understanding DB] What is structured data?
A
Data that can be organized in tables, such as relational databases, spreadsheets, or logs.
4
Q
[Understanding DB] Give 3 real-world examples of structured data.
A
Relational database with customer records, Excel employee spreadsheet, bank transaction log.
5
Q
[Understanding DB] What is unstructured data?
A
Data that lacks a predefined format, and cannot be organized in tables.
6
Q
[Understanding DB] Give 3 real-world examples of unstructured data.
A
Social media posts, scanned PDF contracts, audio files from support calls.
7
Q
[Understanding DB] What are ACID properties in databases?
A
Atomicity, Consistency, Isolation, Durability — they ensure reliable transactions.
8
Q
[Understanding DB] What does Atomicity ensure?
A
That a transaction fully succeeds or fully aborts — no partial changes.
9
Q
[Understanding DB] What does Consistency ensure?
A
That the data remains valid before and after a transaction.
10
Q
[Understanding DB] What does Isolation ensure?
A
That transactions do not interfere with each other.
11
Q
[Understanding DB] What does Durability ensure?
A
That once committed, a transaction’s changes are permanently saved.
12
Q
[Understanding DB] Why are queries better than file system searches?
A
Because queries allow targeted retrieval, while file systems often require full scans.
13
Q
[Understanding DB] Why are constraints important in databases?
A
They enforce data correctness, preventing invalid or absurd entries.
14
Q
[Understanding DB] How do databases offer better security than files?
A
Through authentication and authorization mechanisms.
15
Q
[Understanding DB] List four main types of databases.
A
Relational, NoSQL, Graph, Object-Oriented.
16
Q
[Understanding DB] Which type of database uses tables and keys?
A
Relational databases.
17
Q
[Understanding DB] Which type of database is best for unstructured or flexible data?
A
NoSQL databases.
18
Q
[Understanding DB] Which database type focuses on relationships and connections?
A
Graph databases.
19
Q
[Understanding DB] Which database stores data like object-oriented code?
A
Object-Oriented databases.
20
Q
[Relational DB] What is a relational database?
A
A database that stores data in tables, with rows as data records and columns as attributes.
21
Q
[Relational DB] How is data organized in a relational database?
A
Into multiple tables connected by primary and foreign keys.
22
Q
[Relational DB] What is the role of primary and foreign keys?
A
To uniquely identify records and link related data across tables.
23
Q
[Relational DB] Why must all attributes in a table depend only on the primary key?
A
To ensure data integrity and prevent anomalies.
24
Q
[Relational DB] What does it mean to give every informational theme its own table?
A
Each distinct business concept is stored separately for clarity and normalization.
25
[Relational DB] What is a Relational Database Management System (RDBMS)?
Software that lets users operate and interact with a relational database.
26
[Relational DB] Why is an RDBMS like an operating system for the database?
It interprets user queries and performs database operations accordingly.
27
[Relational DB] What operations can an RDBMS perform?
Database design, construction, data manipulation, and reporting.
28
[Relational DB] Which language is used to communicate with relational databases?
SQL (Structured Query Language).
29
[Relational DB] If two tables need to represent customer and orders, how would you link them?
Using a primary key in customers and a foreign key in orders.
30
[Relational DB] What happens if a table has no primary key?
You risk data duplication and lack of unique identification.
31
[Relational DB] Spot the error: 'Attributes in a table can depend on multiple keys.'
Incorrect. All attributes must depend only on the primary key.
32
[Relational DB] Spot the mistake: 'RDBMS is optional for relational databases.'
Incorrect. An RDBMS is essential for interacting with relational databases.
33
[Relational DB] Explain how a relational database improves over flat file storage.
By organizing data into separate related tables, ensuring integrity, and allowing complex queries.
34
[Relational DB] Describe how a foreign key helps maintain consistency.
It ensures that the value exists in the referenced table, preserving relational logic.
35
[Relational DB] If data isn't normalized in a relational database, what might happen?
Redundancy, inconsistency, and potential anomalies during data operations.
36
[SQL] What does SQL stand for?
Structured Query Language.
37
[SQL] What is SQL used for?
To communicate with and operate on relational databases.
38
[SQL] Why is SQL important for relational databases?
It provides the commands for creating, manipulating, and querying the data.
39
[SQL] Is SQL a database management system?
No, it's a language used to interact with a DBMS like MySQL.
40
[SQL] List categories of SQL commands.
Database Design-Construction, Data Manipulation, Reporting (querying), Data Control, Transaction Control, Miscellaneous Commands.
41
[SQL] What does the Database Design-Construction category include?
Commands for creating and modifying database structure, like CREATE or ALTER.
42
[SQL] What does the Data Manipulation category include?
Commands like INSERT, UPDATE, DELETE — used for changing actual data.
43
[SQL] What does Reporting (querying) involve?
Commands like SELECT and aggregate functions used to extract and analyze data.
44
[SQL] What does Data Control refer to?
Managing user access and privileges (e.g., GRANT, REVOKE).
45
[SQL] What does Transaction Control involve?
Managing changes with commands like COMMIT and ROLLBACK.
46
[SQL] What command connects to MySQL from terminal?
mysql -u user_name -p
47
[SQL] What does '-u' and '-p' mean in the connection command?
'-u' is the username, '-p' triggers password prompt.
48
[SQL] Which category includes SELECT queries?
Reporting (querying).
49
[SQL] If you are modifying tables and constraints, what category are you working in?
Database Design-Construction.
50
[SQL] If you want to rollback a failed transaction, what category does that fall under?
Transaction Control.
51
[SQL] Spot the mistake: 'sql -u user -p'
Missing 'mysql'. Correct command: mysql -u user -p
52
[SQL] Spot the mistake: 'SQL is a DBMS.'
Incorrect. SQL is a language, not a DBMS.
53
[SQL] Explain the relationship between SQL and an RDBMS.
SQL is the language used to interact with an RDBMS which executes the operations on the data.
54
[SQL] Describe the purpose of categorizing SQL commands.
It helps understand the different types of tasks SQL can perform and organizes learning.
55
[SQL] If you confuse SQL with a DBMS, what kind of conceptual error are you making?
You're mistaking the tool (language) with the system that processes it.
56
[DB Design] What is the goal of SQL design-construction commands?
To define and modify the database schema.
57
[DB Design] What SQL command creates a new database?
CREATE DATABASE database_name;
58
[DB Design] What SQL command deletes a database?
DROP DATABASE database_name;
59
[DB Design] What is the basic syntax to create a table?
CREATE TABLE table_name (column1 datatype constraint, ...);
60
[DB Design] What is the purpose of constraints in table creation?
To enforce data validity rules like uniqueness, non-null values, or references.
61
[DB Design] How do you define a primary key in CREATE TABLE?
CONSTRAINT pk_name PRIMARY KEY (column_name)
62
[DB Design] How do you define a foreign key in CREATE TABLE?
CONSTRAINT fk_name FOREIGN KEY (column_name) REFERENCES parent_table(parent_column)
63
[DB Design] List four major categories of SQL data types.
Numerical, String, Date-Time, Boolean.
64
[DB Design] Give examples of SQL string data types.
VARCHAR(n), TEXT
65
[DB Design] What is the BOOLEAN type used for?
To store TRUE or FALSE values.
66
[DB Design] What does the PRIMARY KEY constraint enforce?
That column values are unique and not null.
67
[DB Design] What does the FOREIGN KEY constraint do?
It ensures values in the column exist in the referenced table’s primary key.
68
[DB Design] What does NOT NULL constraint enforce?
That a column cannot have NULL values.
69
[DB Design] What does the UNIQUE constraint enforce?
That no duplicate values exist in the column.
70
[DB Design] What does the DEFAULT constraint do?
Assigns a default value if no value is provided.
71
[DB Design] What does the CHECK constraint do?
Ensures a column value satisfies a given condition.
72
[DB Design] Give a syntax example using DEFAULT.
column_name datatype DEFAULT value
73
[DB Design] Give a syntax example using CHECK.
column_name datatype CHECK (condition)
74
[DB Design] What command shows the structure of a table?
DESC table_name;
75
[DB Design] What command adds a column to a table?
ALTER TABLE table_name ADD column_name datatype constraint;
76
[DB Design] How do you add a column at a specific position?
Use AFTER keyword in ADD COLUMN: ... ADD COLUMN name AFTER existing_column;
77
[DB Design] What command deletes a column?
ALTER TABLE table_name DROP column_name;
78
[DB Design] What command modifies a column's type or constraint?
ALTER TABLE table_name MODIFY COLUMN column_name new_type [constraint];
79
[DB Design] What command renames a column?
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
80
[DB Design] Can multiple ALTER operations be combined?
Yes, they can be chained in one ALTER TABLE statement.
81
[DB Design] Give an example of multiple ALTER operations.
ALTER TABLE Employees DROP COLUMN MiddleName, ADD Email VARCHAR(100), MODIFY Department VARCHAR(50);
82
[DB Design] Spot the mistake: 'CREATE TABLE employees (id INT PRIMARY, name VARCHAR(100));'
PRIMARY should be PRIMARY KEY.
83
[DB Design] Spot the mistake: 'ALTER TABLE employees MODIFY name VARCHAR(100);'
Use MODIFY COLUMN instead: ALTER TABLE employees MODIFY COLUMN name VARCHAR(100);
84
[DB Design] If a column must never be empty, what constraint is needed?
NOT NULL
85
[DB Design] If a column must be unique but also optional, what combination of constraints works?
UNIQUE without NOT NULL
86
[DB Design] Explain why constraints are vital in database design.
They prevent bad data from being entered and protect integrity.
87
[DB Design] If you forget the primary key when designing a table, what problems may arise?
Duplicates, difficulty linking records, poor integrity.
88
[Data Manip] What is data manipulation in SQL?
Using commands to insert, update, or delete data inside tables — the CRUD operations.
89
[Data Manip] What does CRUD stand for?
Create, Read, Update, Delete.
90
[Data Manip] What command is used to insert data into a table?
INSERT INTO table_name (columns) VALUES (values);
91
[Data Manip] What happens to columns not listed in the INSERT?
They receive NULL by default unless a DEFAULT value is defined.
92
[Data Manip] Why must the values in INSERT match the column order?
Because the values are assigned by position, not by name.
93
[Data Manip] How can you insert multiple rows at once?
List multiple sets of values, separated by commas.
94
[Data Manip] What is the syntax for updating table values?
UPDATE table_name SET column1 = value1, ... WHERE condition;
95
[Data Manip] What happens if you omit the WHERE clause in UPDATE?
All rows in the table are updated.
96
[Data Manip] Why is WHERE clause important in UPDATE?
To restrict changes only to relevant rows.
97
[Data Manip] What is the syntax to delete rows from a table?
DELETE FROM table_name WHERE condition;
98
[Data Manip] What happens if you omit WHERE in DELETE?
All rows in the table will be deleted.
99
[Data Manip] Why is the WHERE clause critical in DELETE?
To prevent accidental deletion of all data.
100
[Data Manip] If you want to delete all employees from 'Sales', what command would you use?
DELETE FROM employees WHERE department = 'Sales';
101
[Data Manip] If you want to increase all salaries by 10%, what command would you use?
UPDATE employees SET salary = salary * 1.10;
102
[Data Manip] How would you insert a new student with name and email?
INSERT INTO students (name, email) VALUES ('Alice', 'alice@example.com');
103
[Data Manip] Spot the mistake: 'INSERT table_name (col1) VALUES (val1);'
Missing INTO — should be INSERT INTO table_name ...
104
[Data Manip] Spot the mistake: 'UPDATE SET name = 'Tom' WHERE id = 2;'
Missing table name after UPDATE.
105
[Data Manip] Spot the mistake: 'DELETE users WHERE age < 18;'
Missing FROM — should be DELETE FROM users ...
106
[Data Manip] Which SQL command is used to add new data?
INSERT.
107
[Data Manip] Which SQL command updates existing data?
UPDATE.
108
[Data Manip] Which SQL command removes data from a table?
DELETE.
109
[Data Manip] Explain why WHERE is crucial in UPDATE/DELETE operations.
Without WHERE, the operation affects every row — which can cause catastrophic data loss.
110
[Data Manip] Describe the role of INSERT in data manipulation.
It creates new rows in the specified table.
111
[Data Manip] If you run DELETE without a WHERE clause by mistake, what’s the result?
All records are permanently deleted from the table.
112
[Data Manip] If you omit columns in an INSERT, what values do they take?
NULL (or DEFAULT, if defined).
113
[Querying] What is querying in SQL?
The act of retrieving specific data from a database using SELECT-based commands.
114
[Querying] What is the full structure of an SQL query?
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...
115
[Querying] Which clause is executed last in SQL?
SELECT.
116
[Querying] Why is SELECT executed last?
Because it operates on the table that has already been filtered, grouped, and sorted by previous clauses.
117
[Querying] What does the SELECT clause do?
It retrieves specific columns, expressions, or function outputs from the processed table.
118
[Querying] What does DISTINCT do in SELECT?
Removes duplicate results from the returned set.
119
[Querying] What does AS do in SELECT?
Assigns a temporary alias to a column or expression for readability.
120
[Querying] What is the purpose of the FROM clause?
It tells SQL which table(s) to retrieve data from.
121
[Querying] Why is the FROM clause necessary even when the table name is mentioned?
Because SQL needs an explicit instruction to locate data sources.
122
[Querying] What does the WHERE clause do?
Filters rows before grouping, keeping only rows where conditions are true.
123
[Querying] Give examples of condition types usable in WHERE.
Comparison operators (> < =), logic (AND, OR), IN, BETWEEN, IS NULL.
124
[Querying] What does the GROUP BY clause do?
It groups rows based on matching values in one or more columns.
125
[Querying] What happens if you GROUP BY multiple columns?
SQL groups rows that match on all specified columns.
126
[Querying] What does the HAVING clause do?
Filters groups after GROUP BY has created them.
127
[Querying] Can you use HAVING without GROUP BY?
Yes, it then treats the result set as one big group.
128
[Querying] Why can't WHERE be used after GROUP BY?
Because WHERE works on individual rows, not groups.
129
[Querying] What does ORDER BY do?
Sorts the result rows based on column values.
130
[Querying] What is the default sort order in ORDER BY?
Ascending (ASC).
131
[Querying] How do you sort descending?
Use DESC after the column name.
132
[Querying] What happens when sorting by multiple columns?
SQL sorts by the first column, then breaks ties using the next.
133
[Querying] What does the LIMIT clause do?
Restricts the number of rows returned.
134
[Querying] How can you skip a number of rows before returning results?
Use OFFSET with LIMIT: LIMIT offset, count
135
[Querying] What are aggregate functions used for?
To summarize or perform operations on groups of rows.
136
[Querying] Give 5 aggregate functions.
COUNT(), SUM(), AVG(), MIN(), MAX()
137
[Querying] If you want to find employees in 'Sales', what clause do you use?
WHERE department = 'Sales'
138
[Querying] If you want to see departments with total salaries over $100k?
Use GROUP BY department and HAVING SUM(salary) > 100000
139
[Querying] If you want the top 3 oldest employees?
ORDER BY age DESC LIMIT 3
140
[Querying] Spot the mistake: 'SELECT * employees;'
Missing FROM clause: should be SELECT * FROM employees;
141
[Querying] Spot the mistake: 'ORDER name BY ASC;'
Correct form is: ORDER BY name ASC;
142
[Querying] List the execution order of SQL SELECT query parts.
FROM → WHERE → GROUP BY → HAVING → ORDER BY → SELECT → LIMIT
143
[Querying] Explain the difference between WHERE and HAVING.
WHERE filters rows before grouping; HAVING filters after groups are formed.
144
[Querying] Describe the role of GROUP BY in data summarization.
It segments data by categories so aggregate functions can operate on each group.
145
[Querying] If you forget GROUP BY when using HAVING, what happens?
SQL treats the entire table as one group.
146
[Querying] If you omit WHERE and HAVING, what filtering is applied?
None. All rows (and groups) are included.
147
[Funcs & Procs] What are functions and procedures in SQL?
They are a set of SQL statements that accomplish a specific task, packaged under a name for reuse.
148
[Funcs & Procs] Why are functions and procedures useful?
They abstract logic, promote reuse, and can be permission-controlled by the database admin.
149
[Funcs & Procs] What is the main role of a function in SQL?
To operate on input data and return a single usable output without affecting the table directly.
150
[Funcs & Procs] What are the two types of functions in MySQL?
Built-in (system) functions and User-defined functions.
151
[Funcs & Procs] What is a built-in function in SQL?
A predefined function provided by MySQL, such as COUNT(), AVG(), NOW(), etc.
152
[Funcs & Procs] What is a user-defined function?
A function created by the user to operate on inputs and return a result.
153
[Funcs & Procs] What is the syntax to define a user-defined function in MySQL?
DELIMITER // CREATE FUNCTION name (...) RETURNS type BEGIN ... RETURN ...; END // DELIMITER ;
154
[Funcs & Procs] Why must every SQL function return something?
Because functions are designed to return a single value as output.
155
[Funcs & Procs] What is the DELIMITER keyword used for in MySQL?
To change the statement terminator so SQL doesn't misinterpret the function body.
156
[Funcs & Procs] Why change the delimiter when creating a function?
So that semicolons inside the function body are not treated as the end of the entire function declaration.
157
[Funcs & Procs] What does deterministic mean for a function?
It always returns the same output for the same input.
158
[Funcs & Procs] What does non-deterministic mean for a function?
It may return different outputs for the same input (e.g., using NOW()).
159
[Funcs & Procs] What is a procedure in SQL?
A block of SQL statements that may return none, one, or many outputs, usually executed using CALL.
160
[Funcs & Procs] What is the difference between a function and a procedure?
A function returns a value and is used in expressions, while a procedure performs actions and uses SELECT or OUT parameters for output.
161
[Funcs & Procs] Can a procedure be used as an expression in another SQL statement?
No, only functions can be used as expressions.
162
[Funcs & Procs] How do you define a procedure in SQL?
Using CREATE PROCEDURE with optional IN, OUT, and INOUT parameters.
163
[Funcs & Procs] What is the syntax to create a procedure in MySQL?
DELIMITER // CREATE PROCEDURE name ([params]) BEGIN ... END // DELIMITER ;
164
[Funcs & Procs] What does IN mean in procedure parameters?
The variable is used inside the procedure (input only).
165
[Funcs & Procs] What does OUT mean in procedure parameters?
The variable is used to return data from the procedure (output only).
166
[Funcs & Procs] What does INOUT mean in procedure parameters?
The variable is both input and output.
167
[Funcs & Procs] How do you execute a procedure?
Using the CALL keyword, e.g., CALL procedure_name();
168
[Funcs & Procs] What command deletes a function?
DROP FUNCTION IF EXISTS function_name;
169
[Funcs & Procs] What command deletes a procedure?
DROP PROCEDURE IF EXISTS procedure_name;
170
[Funcs & Procs] If you want to reuse a calculation logic across queries, what should you use?
A user-defined function.
171
[Funcs & Procs] If you want to perform multiple actions and possibly return multiple results, what should you use?
A procedure.
172
[Funcs & Procs] Spot the mistake: 'CREATE FUNCTION myfunc() BEGIN RETURN 1; END;'
Missing RETURN type and proper delimiter syntax.
173
[Funcs & Procs] Spot the mistake: 'CALL myfunc();'
CALL is used for procedures, not functions.
174
[Funcs & Procs] Explain when you'd choose a function over a procedure.
Use a function when you need a single return value that can be embedded in an expression; use a procedure for complex workflows.
175
[Funcs & Procs] Describe the role of DELIMITER in function and procedure definitions.
It prevents early termination of SQL statements inside BEGIN...END blocks by changing the SQL statement terminator.
176
[Joins] What does the JOIN clause do in SQL?
It combines rows from two or more tables based on related columns.
177
[Joins] What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only matched rows; LEFT JOIN includes all from left with NULLs for no matches.
178
[Joins] What is RIGHT JOIN?
Includes all rows from right table, and matching rows from left.
179
[Misc] What does SHOW DATABASES do?
Lists all databases on the server.
180
[Misc] What does USE database_name do?
Selects a database to run SQL queries against.
181
[Misc] What does SHOW * KEYS FROM table do?
Displays all indexes (including primary/foreign keys) in a table.
