Study Day One

Question 1

What is Database?

Answer 1

A structured collection of data organized and stored into a computer. Designed to efficiently to manage, store, retrieve, and manipulate vast amounts of data.

Question 2

What is DBMS (Database Management System?

Answer 2

A Database Management System (DBMS) is software that provides an interface and tools for creating, managing, and interacting with databases.

Question 3

What is RDBMS? How is it different from DBMS?

Answer 3

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

Question 4

What is SQL?

Answer 4

SQL (Structured Query Language) is a domain-specific language used to communicate with and manipulate relational databases.

Question 5

What is the difference between SQL and MySQL?

Answer 5

SQL is a language used to interact with relational databases, while MySQL is a specific database management system that implements the SQL language.

Question 6

What are Tables and Fields?

Answer 6

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.

Question 7

What are Constraints in SQL?

Answer 7

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

Question 8

What is a Primary Key?

Answer 8

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.

Question 9

What is a UNIQUE constraint?

Answer 9

Ensures that the values in a column or set of columns are unique across all the records in a table

Question 10

What is a Foreign Key?

Answer 10

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.

Question 11

What is a JOIN, describe different types.

Answer 11

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.

Question 12

What is an Index?

Answer 12

Reference to the physical location of rows int a table improving the speed of data retrieval

Question 13

What is the difference between Clustered and a Non-clustered index?

Answer 13

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.

Question 14

What is a Query?

Answer 14

A request for information or specific action issued to a DBMS

Question 15

What is an Alias in SQL?

Answer 15

User-defined alternative name given to a table or column in a query. Allowing you to temporarily rename a table or column.

Question 16

What is Normalization?

Answer 16

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.

Question 17

What are the various forms of Normalization?

Answer 17

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

Question 18

What are the TRUNCATE, DELETE, and DROP statements?

Answer 18

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)

Question 19

What are Aggregate functions?

Answer 19

Are functions that perform calculations on a set of rows and return a single result summarizing the data.
COUNT
SUM
AVG
MAX
MIN

Question 20

What is a Stored Procedure?

Answer 20

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

Question 21

What is a Recursive Stored Procedure?

Answer 21

Type of stored procedure in a database that calls itself during its execution. Used on tables that have a hierarchical data structure of categories.

Question 22

What is Pattern Matching in SQL?

Answer 22

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.

Question 23

What is CLAUSE in SQL?

Answer 23

Specific and distinct part of a SQL statement that serves a particular purpose in querying or modifying data in a database.

Question 24

How can you select unique records from a table?

Answer 24

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.

Question 25

What is a View?

Answer 25

A virtual table derived from the result of a SELECT query. Can be used to hide table data if needed.