DATABASES

Data, Database, DBMS , Applications

 is statically raw and unprocessed information. For example – name, class,

marks,

 A database is an organized collection of data stored in a computer system and

usually controlled by a database management system (DBMS). The data in common
databases is modeled in tables, making querying and processing efficient. Structured
query language (SQL) is commonly used for data querying and writing.

 It can be managed through a Database Management System (DBMS), a software

used to manage data. Database refers to related data in a structured form.

 Company Information, Account information, manufacturing, banking, finance

transactions, telecommunications.
TABLE
 Table in Database

 A table is a collection of
related data in an organized
manner in the form of rows
and columns. It is an
organized arrangement of
data and information
in tabular form containing
rows and columns, making it
easier to understand and
compare data.
 Field, Record, Validation
❑ In database terminology, field is often used to refer to the individual cells
within a row or column. However, it can also refer to the whole column itself.

❑ When referring to an individual cell, we’re usually referring to the value

within that cell. So a user might ask “what value is in the FirstName field?” when
referring to an individual record.

❑ When referring to the whole column, we’re usually referring to the name of the
column, its data type, constraints, and any data contained within that column.
 Record/Row
 row table

 An example of a record could be a single row in a “Customers” table. This row could
contain the customer’s first name and last name for example.

 The terms record and row are often used interchangeably

 Data Validation
 Data validation involves systematically checking and cleaning data to prevent
incorrect, incomplete, or irrelevant data from entering a database, thereby
safeguarding the reliability of subsequent analysis.

❖ Numerous forms of data validation exist, and most validation processes involve conducting one
or more of these checks to verify the accuracy of the data prior to its storage in a database.

▪ Data type check -For instance, if a field only accepts text, any input that is not text —such as
numeric or special characters—should be rejected by the system.

▪ Code check- This is especially applicable in scenarios where entries must confirm to
standardized formats, such as International Standard Book Numbers (ISBNs) for books or
vehicle identification numbers (VINs) for cars.
 Types of Data Validation

▪ Range check -For instance, in a temperature control system, the acceptable range
may be set between -10°C and 35°C.

▪ Format check -A typical example is ensuring that phone numbers are entered in a
consistent format, such as "(XXX) XXX-XXXX" or "XXX-XXX-XXXX."

▪ Consistency check -For example, it may involve ensuring that a patient's recorded
age aligns with their date of birth.
 Data types
❖ Data types are used to define the type of data stored in a database

❖ SQL data types are used to specify the type of data that a column or variable can store.

❖ Character/string data types are used to store alphanumeric characters in a database.

❖ The most commonly used of these are CHAR and VARCHAR.

❖ The CHAR data type is used to store fixed-length strings of characters, while
❖ the VARCHAR data type is used to store variable-length strings.
When adding a VARCHARor CHAR variable, you specify the maximum number of characters
allowed.

❖ Numeric data types are used to store numeric values in a database.

The most commonly used numeric data types are INT and FLOAT.
The INT data type is used to store whole numbers.FLOAT data type is used to store decimal
numbers.
 Data types cont...
 Data and time data types, to store date and time values in a database.-Dates and times in
SQL are typically stored using DATE, TIME, and DATETIME, and TIMESTAMP. The DATE data type
is used to store a date value in the format of “YYYY-MM-DD”, the TIME data type is used to
store a just time value in the format of “HH:MM:SS”, and the DATETIME data type is used to
store both date and time values in the format of “YYYY-MM-DD HH:MM:SS”.

 Boolean expressions are that expression that returns boolean datatype as result. In SQL
there are two values for boolean datatype, those are:

 TRUE

 FALSE

 Real - The REAL data type allows you to store single-precision floating-point numbers in the
database.
 Primary Key and its purpose
 PRIMARY KEY

 For example, if we have a table of United States citizens, we can use the
column social_security_number to identify each row in the table
 Primary Key and Purpose
 The most suitable column to identify a record in the table invoice is invoice_number,
because every value in invoice_number can identify at most one record; in other words, we
can’t have two records with the same invoice_number.

 Purpose -

Primary key ensure data consistency

This is very helpful when performing any operation on the data such as updating, deleting,
searching, etc.

 Due to Primary key the access of records become faster.

 Properties of Primary key
 Properties of Primary Key


 SELECT STATEMENT
 https://youtu.be/af4LckivJT8?list=PLP9IO4UYNF0UQkBXlTMSw0CYsxv-GDkkI

The SQL SELECT statement is used to query data from a single table in a
database. It allows you to specify the exact columns you want to retrieve, as
well as the table from which to retrieve the data

Eg-SELECT CustomerName, City FROM Customers;

FROM Keyword

 FROM

 SELECT FROM
ORDER BY KEYWORD
 ORDER BY

 ORDER BY
DESC

 SELECT * FROM Customers

ORDER BY CustomerName; (Ascending Order)

 Eg- SELECT * FROM CUSTOMERS

 ORDER BY CustomerName DESC; (Descending order)

The SQL SUM() Function

 SUM()

 Quantity OrderDetails

 SELECT
COUNT() function
 COUNT()

 Products

 SELECT COUNT
Activity
Database Terms
SQL