Database Server Vs Database Client
25 February 2023 17:35
1. Database Server: A database server is a software application that provides access
to a database over a network. It manages and processes requests from multiple
clients, and performs tasks such as managing user authentication, concurrency
control, and transaction management. A database server is typically installed on a
dedicated computer or server, and is responsible for storing, managing, and
processing data.
2. Database Client: A database client is a software application that connects to a
database server and sends requests for data or database operations. It is the front-
end interface that allows users to interact with the database, and provides tools
such as query editors, data visualization, and reporting.
3. Database: A database is a collection of related data that is organized and stored in
a structured format. It is designed to efficiently store, retrieve, and manage large
amounts of data. A database can include multiple tables, indexes, and other
objects that are used to manage and manipulate data.
4. DBMS (Database Management System): A DBMS is a software application that
provides tools for creating, managing, and manipulating databases. It includes a
variety of functions and features, such as data storage, data retrieval, data backup
and recovery, user management, and security. The DBMS is responsible for
managing the underlying database, and provides a mechanism for users to interact
with the data.
What are Database Engines
25 February 2023 17:35
A database engine, also known as a database management system (DBMS) engine,
is the software component of a DBMS that is responsible for managing the
storage, organization, and retrieval of data in a database. It is the core component
of a DBMS that provides the necessary tools and services for creating, modifying,
and querying the database.
A database engine typically includes several key components, such as a query
optimizer, transaction manager, storage manager, and buffer manager. The query
optimizer is responsible for optimizing SQL queries to retrieve data from the
database efficiently, while the transaction manager ensures that multiple
transactions are executed correctly and consistently. The storage manager handles
the physical storage of the data on disk or in memory, and the buffer manager is
responsible for managing the cache of data in memory to optimize query
performance.
Components of DBMS
25 February 2023 19:19
1. Database Engine
2. Security and Access Control - used to manage user permissions and access
rights to the database.
3. Backup and Recovery - used to create backups of the database and recover the
data in case of failures.
4. Data Dictionary - used to store metadata about the database schema and data
5. User Interface - used to provide a graphical interface to interact with the
database.
What is Collation
25 February 2023 17:35
Collation refers to the rules and algorithms used to compare and sort
characters in a database. It determines how character strings are
compared and sorted, including the order of the characters, the
treatment of case sensitivity, and the handling of accent marks or other
special characters.
Collation is important in database management because it affects the
way queries are executed and results are returned. If the collation of a
database is not set correctly, queries may return incorrect results or the
database may not sort data properly.
1. Binary: Compares strings byte by byte. It is case-sensitive and accent-
sensitive.
2. Case-insensitive: Compares strings without regard to case, but is
accent-sensitive.
3. Accent-insensitive["café" and "cafe"]: Compares strings without regard
to accents, but is case-sensitive.
4. Case- and accent-insensitive: Compares strings without regard to case
or accents.
5. Unicode: Supports Unicode character sets, and is available in multiple
variants, such as utf8mb4_unicode_ci, utf8mb4_unicode_520_ci,
utf8mb4_unicode_520_ci_ai, etc.
Diff between COUNT(*) and COUNT(col)
25 February 2023 17:35
Dealing with NULL values
25 February 2023 17:36
1. How it deals with NON NULL values
2. Order By
3. Group By - NULL values are treated as a separate group and are not
included in any group that contains non-NULL values.
4. Aggregate
When performing aggregate operations in MySQL, NULL values
are treated differently depending on whether or not the
GROUP BY clause is used.
Without GROUP BY:
If the aggregate function is SUM, AVG, MAX, MIN, or
COUNT, NULL values are ignored and not included in the
calculation.
If the aggregate function is GROUP_CONCAT or CONCAT,
NULL values are included in the result, but a NULL value is
returned if all the values being concatenated are NULL.
With GROUP BY:
If the aggregate function is COUNT, NULL values are not
included in the count for each group. However, if you use
COUNT(*) instead of COUNT(column), then NULL values
are included in the count.
If the aggregate function is SUM, AVG, MAX, or MIN, NULL
values are ignored and not included in the calculation for
each group. If a group contains only NULL values, then the
result for that group will be NULL.
If the aggregate function is GROUP_CONCAT or CONCAT,
NULL values are included in the result for each group, but a
NULL value is returned if all the values being concatenated
in a group are NULL.
• How to find null values?
• How to replace null values?
DELETE Vs TRUNCATE
25 February 2023 17:36
• DELETE is a Data Manipulation Language (DML) statement, whereas TRUNCATE is a
Data Definition Language (DDL) statement. This means that TRUNCATE requires
the ALTER TABLE privilege, whereas DELETE requires the DELETE privilege on the
table.
• DELETE can be rolled back using a transaction log, which means that you can undo
the changes made by DELETE if necessary. TRUNCATE, on the other hand, cannot
be rolled back because it does not generate a transaction log.
• DELETE is slower than TRUNCATE because it generates transaction log entries for
each deleted row. If you need to delete a large number of rows, TRUNCATE may
be a better option for performance reasons.
• If you use foreign key constraints in your database, DELETE can cause integrity
issues if you delete rows that are referenced by other tables. In this case, you
should use TRUNCATE or disable the foreign key constraints before using DELETE.
Non-equi joins
25 February 2023 17:37
In a non equi join, the join condition is based on operators other than
equality. Specifically, the join condition can use operators such as
greater than, less than, or not equal to, among others. Non equi joins
are useful when you need to join tables on columns with similar but not
identical data, or when you need to join tables based on a range of
values rather than an exact match.
Natural Joins
25 February 2023 17:37
A natural join is a type of join in SQL where two tables are joined based
on the columns with the same name and data type. In other words, it is
a join where the join condition is implicitly based on the column names
that exist in both tables, and it eliminates the duplicate columns from
the result set.
Anti Joins
25 February 2023 17:37
An excluding join, also known as an anti-join, is a type of join operation in SQL that
returns only the rows from one table that do not have any matching rows in
another table. In other words, it returns the rows that are not included in the
result set of an inner join between the two tables.
All and Any Operators
25 February 2023 17:37
Removing Duplicate Rows
25 February 2023 17:42
1. Find duplicate values
2. Delete duplicate
Metadata Queries
25 February 2023 17:42
- How to see all the tables of a database
- How to print col names
- How to see all the constraints of a table (Homework)
- copy table definition -> How to create empty tables with the same
structure as another table?