Study Day One Flashcards
What is Database?
A structured collection of data organized and stored into a computer. Designed to efficiently to manage, store, retrieve, and manipulate vast amounts of data.
What is DBMS (Database Management System?
A Database Management System (DBMS) is software that provides an interface and tools for creating, managing, and interacting with databases.
What is RDBMS? How is it different from DBMS?
It is a specific type of DBMS that organizes data into tables with rows and columns, and it manages the relationships between these tables.
DBMS: Not restricted structure, relationships between data not explicitly defined, data integrity is not as strictly enforced, schema may be dynamic and change frequently
RDBMS: Organized tables with relationships between them, well defined relationships, strict data integrity rules, fixed schema
What is SQL?
SQL (Structured Query Language) is a domain-specific language used to communicate with and manipulate relational databases.
What is the difference between SQL and MySQL?
SQL is a language used to interact with relational databases, while MySQL is a specific database management system that implements the SQL language.
What are Tables and Fields?
A table is a component in a database that represents data organized into rows and columns.
A Field or Attributes are the individual data elements that make up the columns in a table.
What are Constraints in SQL?
Rules and conditions that are applied to the columns or tables in a database to maintain data integrity
NO NULL
UNIQUE
PRIMARY KEY (Combines not null and unique)
FORIGN KEY
CHECK
DEFAULT
What is a Primary Key?
A special column or set of columns that uniquely identifies that record. A table can only have one primary key and can not be null.
What is a UNIQUE constraint?
Ensures that the values in a column or set of columns are unique across all the records in a table
What is a Foreign Key?
It is a column or a set of columns in one table that references the primary key of another table. The number can very from one to many.
What is a JOIN, describe different types.
Used to combine rows from two or more tables based on a related column(s) between them.
INNER JOIN - returns only the rows that have matching values
LEFT JOIN(LEFT OUTER JOIN) - Returns all the rows from the first table and the matching rows from the second
RIGHT JOIN(RIGHT OUTER JOIN) - Returns all the rows from the second table and the match rows from the first
FULL JOIN(FULL OUTER JOIN) - Returns all the rows when there is a match in either
CROSS JOIN - Combines each row from the first with every row from the second
SELF JOIN - Creates relations between records on the same table.
What is an Index?
Reference to the physical location of rows int a table improving the speed of data retrieval
What is the difference between Clustered and a Non-clustered index?
Clustered Index: The physical order of the rows in the table is determined by the clustered index, and there can be only one per table. Well-suited for range queries and exact match queries on the indexed column(s).
Non-clustered Index: Creates a separate data structure to hold the indexed column(s) and pointers to the corresponding rows in the table.
What is a Query?
A request for information or specific action issued to a DBMS
What is an Alias in SQL?
User-defined alternative name given to a table or column in a query. Allowing you to temporarily rename a table or column.
What is Normalization?
Used to organize and structure relational databases in a way that reduces data redundancy and ensure data integrity. This can be down by breaking down large tables into smaller organized tables with clear relationships.
What are the various forms of Normalization?
1NF: It eliminates repeating groups by organizing data into simple, single-valued attributes. One Value per Field
2NF: To achieve 2NF, tables must be in 1NF, and the primary key must consist of a single column. Table has a primary key
3NF: It ensures that non-key attributes are not dependent on other non-key attributes. No duplicate fields across tables
What are the TRUNCATE, DELETE, and DROP statements?
All used to remove data from the database.
Truncate is used to remove all the rows from a table (Data Definition Language)
Delete is used to remove specific rows or all rows from a table (DML statement)
Drop is used to remove database objects such as tables, views, indexes, or even entire databases from the database system. (DDL)
What are Aggregate functions?
Are functions that perform calculations on a set of rows and return a single result summarizing the data.
COUNT
SUM
AVG
MAX
MIN
What is a Stored Procedure?
A named and precompiled set of SQL statements and procedural logic that is stored in the database. It is a database object that can be invoked and executed multiple times with different parameters. Manage a database efficiently by providing code reusability, performance optimization , security, and data consistency
What is a Recursive Stored Procedure?
Type of stored procedure in a database that calls itself during its execution. Used on tables that have a hierarchical data structure of categories.
What is Pattern Matching in SQL?
The ability to search and identify specific patterns or substrings within character strings stored in the database. Allow you to perform complex searches based on wildcards and regular expressions.
What is CLAUSE in SQL?
Specific and distinct part of a SQL statement that serves a particular purpose in querying or modifying data in a database.
How can you select unique records from a table?
Use the DISTINCT keyword with the SELECT statement. The DISTINCT keyword instructs the database to eliminate duplicate rows from the result set, returning only the distinct or unique records.
What is a View?
A virtual table derived from the result of a SELECT query. Can be used to hide table data if needed.
