SQL Concepts Flashcards
(137 cards)
1
Q
What is a Database?
A
A structured collection of data, organized for efficient storage, retrieval, and management.
2
Q
What is SQL?
A
Stands for Structured Query Language; it’s a standard language for interacting with and managing databases.
3
Q
Difference between DBMS and RDBMS?
A
DBMS manages databases; RDBMS is a type of DBMS that stores data in related tables.
4
Q
What are Constraints in SQL?
A
Rules enforced on data columns in a table to ensure data accuracy and reliability.
5
Q
What is Data Integrity?
A
The overall accuracy, completeness, and consistency of data.
6
Q
What is NULL in SQL?
A
Represents a missing or unknown value in a database field.
7
Q
What is Data Redundancy?
A
Storing the same piece of data multiple times within a database.
8
Q
What is a Primary Key?
A
A constraint that uniquely identifies each record in a table.
9
Q
What is a Foreign Key?
A
A field in one table that refers to the Primary Key in another table.
10
Q
What is Referential Integrity?
A
Ensures that relationships between tables remain consistent.
11
Q
What is an ERD (Entity-Relationship Diagram)?
A
A visual representation of the structure of a database.
12
Q
What are the main components of an ERD?
A
Entities, Attributes, and Relationships.
13
Q
What is an Entity in ERD?
A
A real-world object or concept about which data is stored.
14
Q
Difference between Strong and Weak Entity?
A
A Strong Entity has its own primary key; a Weak Entity depends on a strong entity.
15
Q
What is an Attribute in ERD?
A
A property or characteristic of an entity.
16
Q
What is a Relationship in ERD?
A
An association between two or more entities.
17
Q
What are Cardinality Symbols in ERD?
A
Symbols that define the numerical relationship between entities.
18
Q
Difference between Cardinality and Ordinality?
A
Cardinality specifies the maximum number of times; Ordinality specifies the minimum number of times.
19
Q
What is Total vs. Partial Participation?
A
Total Participation means every instance must participate; Partial Participation means participation is optional.
20
Q
Describe a One-to-One (1:1) Relationship.
A
One instance of Entity A can relate to only one instance of Entity B.
21
Q
Describe a One-to-Many (1:M) / Many-to-One (M:1) Relationship.
A
One instance of Entity A can relate to many instances of Entity B.
22
Q
Describe a Many-to-Many (M:N) Relationship.
A
One instance of Entity A can relate to many instances of Entity B, and vice-versa.
23
Q
What is Generalization in ERD?
A
A bottom-up approach where common attributes of multiple entities are combined.
24
Q
What is Specialization in ERD?
A
A top-down approach where a higher-level entity is broken down into more specific entities.
25
What is Aggregation in ERD?
Treats a relationship set as a higher-level entity.
26
What is an Associative Entity (Junction Table)?
An entity used to implement a Many-to-Many relationship.
27
What is a Relational Schema?
A set of related relational tables and associated items.
28
How do you convert a Self-Referential relationship to Relational Schema?
Add a foreign key column within the same table.
29
How do you convert Composite Attributes to Relational Schema?
Create separate columns for each component of the composite attribute.
30
How do you convert Multi-Valued Attributes to Relational Schema?
Create a new table containing the primary key of the original entity.
31
How do you convert Derived Attributes to Relational Schema?
Generally, derived attributes are not stored directly but calculated when needed.
32
How do you convert a One-to-One relationship to Relational Schema?
Include the primary key of one entity as a foreign key in the other entity's table.
33
How do you convert a One-to-Many/Many-to-One relationship to Relational Schema?
Include the primary key of the 'one' side entity as a foreign key in the 'many' side entity's table.
34
How do you convert a Many-to-Many relationship to Relational Schema?
Create a new 'junction' or 'associative' table.
35
How do you convert Generalization/Specialization to Relational Schema?
Treat it as a One-to-One relationship.
36
How do you convert an Associative Entity to Relational Schema?
It directly becomes a table.
37
SQL command to create a new database?
CREATE DATABASE database_name;
38
SQL command to delete a database?
DROP DATABASE database_name;
39
SQL command to select a database to work with?
USE database_name;
40
SQL command to create a new table?
CREATE TABLE table_name (column1 datatype constraints, column2 datatype constraints, ...);
41
SQL command to delete a table?
DROP TABLE table_name;
42
SQL command to add a column to an existing table?
ALTER TABLE table_name ADD column_name datatype;
43
SQL command to rename a column (Most Databases)?
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
44
SQL Server command to rename a column?
exec sp_rename 'table_name.old_column_name', 'new_column_name', 'COLUMN';
45
SQL command to rename a table (Most Databases)?
ALTER TABLE old_table_name RENAME TO new_table_name;
46
SQL Server command to rename a table?
exec sp_rename 'old_table_name', 'new_table_name';
47
SQL command to change a column's data type (SQL Server/PostgreSQL)?
ALTER TABLE table_name ALTER COLUMN column_name new_datatype;
48
SQL command to change a column's data type (MySQL/Oracle)?
ALTER TABLE table_name MODIFY COLUMN column_name new_datatype;
49
SQL command to delete a column from a table?
ALTER TABLE table_name DROP COLUMN column_name;
50
Name some common SQL Exact Numeric Data Types.
`BIT`, `INT`, `BIGINT`, `SMALLINT`, `TINYINT`, `DECIMAL`/`NUMERIC`, `MONEY`, `SMALLMONEY`.
51
Name some common SQL Approximate Numeric Data Types.
`FLOAT`, `REAL`.
52
Name some common SQL Character String Data Types.
`CHAR`, `VARCHAR`, `TEXT`.
53
Name some common SQL Unicode Character String Data Types.
`NCHAR`, `NVARCHAR`, `NTEXT`.
54
Name some common SQL Date & Time Data Types.
`DATE`, `TIME`, `DATETIME`, `DATETIME2`, `SMALLDATETIME`, `DATETIMEOFFSET`.
55
Name some common SQL Binary String Data Types.
`BINARY`, `VARBINARY`, `IMAGE`.
56
Name some common SQL Binary String Data Types.
`BINARY`, `VARBINARY`, `IMAGE`
## Footnote
Use `VARBINARY(MAX)` instead of `IMAGE` in SQL Server.
57
Name some other SQL Data Types.
`SQL_VARIANT`, `TIMESTAMP`/`ROWVERSION`, `UNIQUEIDENTIFIER`, `XML`, `CURSOR`, `TABLE`, Spatial Geometry/Geography Types.
58
SQL command to create a full database backup?
`BACKUP DATABASE database_name TO DISK = 'file_path.bak';
59
What is a Differential Backup?
Backs up only the parts of the database that have changed since the last full backup.
60
SQL command to create a differential database backup?
`BACKUP DATABASE database_name TO DISK = 'file_path.bak' WITH DIFFERENTIAL;
61
SQL command to restore a database from a backup?
`RESTORE DATABASE database_name FROM DISK = 'file_path.bak';
62
SQL command to insert a new record into a table?
`INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);` or `INSERT INTO table_name VALUES (value1, value2, ...);`
63
SQL command to copy data from one table to another *existing* table?
`INSERT INTO table2 (column_list) SELECT column_list FROM table1 WHERE condition;
64
SQL command to copy data from one table into a *new* table?
`SELECT column_list INTO new_table FROM existing_table WHERE condition;
65
SQL command to modify existing records in a table?
`UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;`
66
SQL command to delete records from a table?
`DELETE FROM table_name WHERE condition;`
67
Difference between `DELETE` and `TRUNCATE`?
`DELETE` removes rows one by one and can use a `WHERE` clause. `TRUNCATE` removes all rows quickly and resets identity counters.
68
How to define a Foreign Key when creating a table?
Inline: `column_name datatype FOREIGN KEY REFERENCES parent_table(parent_pk_column)` or as table constraint: `CONSTRAINT fk_name FOREIGN KEY (column_name) REFERENCES parent_table(parent_pk_column)`.
69
How to add a Foreign Key to an existing table?
`ALTER TABLE child_table ADD CONSTRAINT fk_name FOREIGN KEY (column_name) REFERENCES parent_table(parent_pk_column);`
70
What is an Auto Increment / Identity Field?
A field (often Primary Key) that automatically generates a unique sequential number when a new record is inserted.
71
How to define an Identity field in SQL Server?
`column_name INT IDENTITY(seed, increment) PRIMARY KEY;`
72
How to get the last generated Identity value in SQL Server?
`PRINT @@IDENTITY;`
73
Basic SQL statement to select data?
`SELECT column1, column2, ... FROM table_name;`
74
How to select all columns from a table?
`SELECT * FROM table_name;`
75
How to select only unique/distinct values from a column?
`SELECT DISTINCT column_name FROM table_name;`
76
Purpose of the `WHERE` clause?
To filter records and retrieve only those that satisfy a specified condition.
77
How to combine multiple conditions in `WHERE`?
Use `AND`, `OR`, `NOT`.
78
Purpose of the `IN` operator?
Allows specifying multiple values in a `WHERE` clause.
79
Purpose of the `ORDER BY` clause?
Sorts the result set in ascending or descending order based on one or more columns.
80
Purpose of the `SELECT TOP` clause (SQL Server/MS Access)?
Specifies the number or percentage of records to return from the top of the result set.
81
How to give a temporary name (alias) to a column or table?
Use the `AS` keyword: `SELECT column_name AS alias_name FROM table_name;`
82
Purpose of the `BETWEEN` operator?
Selects values within a given range (inclusive).
83
SQL function to count rows or non-NULL values?
`COUNT(column_name)` or `COUNT(*)`.
84
SQL function to calculate the sum of values in a column?
`SUM(column_name)`.
85
SQL function to calculate the average of values in a column?
`AVG(column_name)`.
86
SQL function to find the minimum value in a column?
`MIN(column_name)`.
87
SQL function to find the maximum value in a column?
`MAX(column_name)`.
88
Purpose of the `GROUP BY` statement?
Groups rows with the same values in specified columns into a summary row.
89
Purpose of the `HAVING` clause?
Filters groups based on a specified condition.
90
Purpose of the `LIKE` operator?
Used in a `WHERE` clause to search for a specified pattern in a column.
91
Wildcard characters used with `LIKE`?
`%` (zero, one, or multiple characters), `_` (a single character).
92
SQL Server wildcard `[charlist]`?
Matches any single character within the specified set.
93
SQL Server wildcard `[start-end]`?
Matches any single character within the specified range.
94
SQL Server wildcard `[^charlist]` or `[!charlist]`?
Matches any single character *not* within the specified set or range.
95
Purpose of SQL `JOIN` clauses?
To combine rows from two or more tables based on a related column.
96
What does `INNER JOIN` (or just `JOIN`) return?
Returns only the records that have matching values in both tables.
97
What does `LEFT JOIN` (or `LEFT OUTER JOIN`) return?
Returns all records from the left table, and the matched records from the right table.
98
What does `RIGHT JOIN` (or `RIGHT OUTER JOIN`) return?
Returns all records from the right table, and the matched records from the left table.
99
What does `FULL OUTER JOIN` (or `FULL JOIN`) return?
Returns all records when there is a match in either the left or right table.
100
Basic syntax for `INNER JOIN`?
`SELECT ... FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;`
101
What is a SQL View?
A virtual table based on the result-set of a stored SQL statement.
102
SQL command to create a View?
`CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;`
103
Purpose of the `EXISTS` operator?
Tests for the existence of any record in a subquery.
104
Basic syntax for `EXISTS`?
`SELECT ... FROM table_name WHERE EXISTS (SELECT 1 FROM other_table WHERE condition);`
105
Purpose of the `CASE` expression?
Provides IF-THEN-ELSE logic within SQL statements.
106
Basic syntax for `CASE`?
`CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE result END`
107
Purpose of the `UNION` operator?
Combines the result sets of two or more `SELECT` statements.
108
Purpose of the `UNION ALL` operator?
Combines the result sets of two or more `SELECT` statements, including all duplicate rows.
109
Requirements for using `UNION` / `UNION ALL`?
Each `SELECT` statement must have the same number of columns.
110
How to define a Primary Key constraint (table level)?
`CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ...);`
111
How to add a Primary Key constraint to an existing table?
`ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ...);`
112
How to drop a Primary Key constraint?
`ALTER TABLE table_name DROP CONSTRAINT constraint_name;`
113
How to define a Foreign Key constraint (table level)?
`CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES parent_table(parent_pk_column);`
114
How to drop a Foreign Key constraint?
`ALTER TABLE table_name DROP CONSTRAINT constraint_name;`
115
How to enforce that a column cannot accept NULL values?
Use the `NOT NULL` constraint during table creation.
116
Purpose of the `DEFAULT` constraint?
Sets a default value for a column if no value is specified during `INSERT`.
117
How to define a `DEFAULT` constraint?
During creation: `column_name datatype DEFAULT default_value`.
118
How to drop a `DEFAULT` constraint?
`ALTER TABLE table_name DROP CONSTRAINT constraint_name;`
119
Purpose of the `CHECK` constraint?
Limits the value range or format that can be placed in a column.
120
How to define a `CHECK` constraint?
During creation: `column_name datatype CHECK (condition)`.
121
How to drop a `CHECK` constraint?
`ALTER TABLE table_name DROP CONSTRAINT constraint_name;`
122
Purpose of the `UNIQUE` constraint?
Ensures that all values in a column are unique across the table.
123
How to define a `UNIQUE` constraint?
During creation: `column_name datatype UNIQUE`.
124
How to drop a `UNIQUE` constraint?
`ALTER TABLE table_name DROP CONSTRAINT constraint_name;`
125
What is a SQL Index?
A database object used to speed up data retrieval operations on a table.
126
SQL command to create an Index?
`CREATE INDEX index_name ON table_name (column1, column2, ...);`
127
SQL command to create a Unique Index?
`CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);`
128
SQL command to delete an Index?
`DROP INDEX table_name.index_name;`
129
What is a Clustered Index (SQL Server)?
Sorts and stores the data rows in the table based on their key values.
130
What is Database Normalization?
The process of organizing data in a database to reduce redundancy.
131
Goals of Normalization?
* Minimize data redundancy
* Avoid data modification anomalies
* Simplify data structure
* Ensure data consistency
132
What is a clustered index?
A clustered index sorts and stores the data rows in the table based on their key values. A table can have only one clustered index. Often created automatically on the Primary Key.
## Footnote
A clustered index determines the physical order of data in a table.
133
What is Database Normalization?
The process of organizing data in a database to reduce redundancy and improve data integrity by dividing larger tables into smaller, more manageable ones and defining relationships between them.
## Footnote
Normalization helps in maintaining data accuracy and efficiency.
134
What are the goals of Normalization?
Minimize data redundancy, avoid data modification anomalies (insert, update, delete anomalies), simplify data structure, ensure data consistency.
## Footnote
These goals lead to more efficient database management.
135
What is First Normal Form (1NF)?
A table is in 1NF if: * It has a primary key. * Each column contains atomic (indivisible) values (no multi-valued attributes). * There are no repeating groups of columns.
## Footnote
1NF is the foundational step in the normalization process.
136
What is Second Normal Form (2NF)?
A table is in 2NF if: * It is in 1NF. * All non-key attributes are fully functionally dependent on the entire primary key (no partial dependencies on a composite primary key).
## Footnote
2NF eliminates partial dependencies, enhancing data integrity.
137
What is Third Normal Form (3NF)?
A table is in 3NF if: * It is in 2NF. * There are no transitive dependencies (non-key attributes do not depend on other non-key attributes).
## Footnote
3NF further reduces redundancy and improves data integrity.
