SQL Concepts Flashcards
What is a database?
A db is an organized collection of data, stored and retrieved digitally from a variety of sources
What is DBMS?
A DBMS is a database management system. It’s responsible for the creation, deletion, updates and otherwise interaction with a database.
RDBMS vs. DBMS
A relational database management system exists surrounding the concept of a table. A way to reference each individual table is where “relational” comes from.
What is SQL?
Structured Query Language.
What are tables and fields?
A table is an organized collection of data stored in the form of rows and columns. Columns can be categorized as vertical and rows as horizontal. The columns in a table are called fields while the rows can be referred to as records.
What are Constraints in SQL?
Constraints are used to specify the rules concerning the data in a table. They are:
NOT NULL - Restricts NULL value from being inserted into a column.
CHECK - Verifies that all values in a field satisfy a condition.
DEFAULT - Automatically assigns a default value if no value has been specified for the field.
UNIQUE - Ensures unique values to be inserted into the field.
INDEX - Indexes a field providing faster retrieval of records.
PRIMARY KEY - Uniquely identifies each record in a table.
FOREIGN KEY - Ensures referential integrity for a record in another table.
What is a Primary Key?
The PRIMARY KEY constraint uniquely identifies each row in a table. It must contain UNIQUE values and has an implicit NOT NULL constraint.
What is a UNIQUE constraint?
A UNIQUE constraint ensures that all values in a column are different.
What is a FOREIGN KEY?
A FOREIGN KEY comprises of single or collection of fields in a table that essentially refers to the PRIMARY KEY in another table. Foreign key constraint ensures referential integrity in the relation between two tables.
What is a JOIN?
It’s a way to combine rows from two or more different tables. There is
LEFT JOIN - all the left, whatever matches on the right
INNER JOIN - Only what matches from all
RIGHT JOIN - all the right, whatever matches the left
FULL OUTER JOIN - Wherever there is a match in the left or right
What is an Index? Explain the different types
A database index is a data structure that provides a quick lookup of data in a column or columns of a table. It enhances the speed of operations accessing data from a database table at the cost of additional writes and memory to maintain the index data structure.
Unique vs. Non-Unique: Unique indexes are indexes that help maintain data integrity by ensuring that no two rows of data in a table have identical key values.
Clustered vs. Non-Clustered: Clustered indexes are indexes whose order of the rows in the database corresponds to the order of the rows in the index. This is why only one clustered index can exist in a given table, whereas, multiple non-clustered indexes can exist in the table.
What is Data Integrity?
Data Integrity is the assurance of accuracy and consistency of data over its entire life-cycle and is a critical aspect of the design, implementation, and usage of any system which stores, processes, or retrieves data. It also defines integrity constraints to enforce business rules on the data when it is entered into an application or a database.
What is a Query?
A query is a request for data or information from a database.
What is a subquery?
A subquery is a query within another query, also known as a nested query or inner query. There are two types:
Correlated: A correlated subquery cannot be considered as an independent query, but it can refer to the column in a table listed in the FROM of the main query.
Non-correlated: A non-correlated subquery can be considered as an independent query and the output of the subquery is substituted in the main query.
What is SELECT?
SELECT operator in SQL is used to select data from a database. The data returned is stored in a result table, called the result-set.
