Querying and SQL Functions Notes
Querying and SQL Functions Notes
Querying and SQL Functions Notes
1. Data :- It can be described as the raw of facts and figures.
2. Information :- Processing of data into meaningful statements enhances our knowledge.
3. Database :- Organised collection of logically related data.
4. DBMS (Data Base Management System) :- A computer system that allows us to manage Databases.
5. Need for DBMS :- Database provides centralised control on its operational data. Thus, with the help of a database
redundancy can be reduced.
inconsistence can be avoided.
data integrity is maintained.
data can be shared.ecurity restrictions can be imposed
6. Data Redundancy :- Duplication (Replication) of data is known as Data Redundancy.
7. Data Inconsistency :- It is a situation/problem where the same data is stored in two different files in a different format.
8. Data Security :- Protection of data from unauthorised persons against accidental/intentional addition or modification or deletion.
9. Relation :- It is a two dimensional format which contains rows and columns. In database a relation is also known as Table.
10. Domain :- A set of unique pool of values allowed for a column is known as its domain.
11. Tuple :- A row/record in a relation or a horizontal subset is known as table.
12. Attribute :- A column/ Field in a relation or vertical subset.
13. Cardinality :- Number of rows in a relation denotes cardinality of the table.
14. Degree :- Number of columns in a relation.
15. SQL :- Stands for Structured Query Language. It is a standard programming language used to store, retrieve and manage data in relational database.
16. Candidate Key :- A column or a set of columns which uniquely identities a row in a relation. A table/relation can have multiple candidate key.
17. Primary Key :- A column or a set of columns which can uniquely identify a row in a relation.
18. Alternate key :- A candidate key which is not a primary key but can serve as a primary key.
19. Foreign Key :- It is a column that references the primary key of another table.
20. DDL :- Stands for Data Definition Language. It is a category of SQL command which is used to manipulate database objects like table structure. Examples of DDL commands are: Create Table, Alter Table, Drop Table. Create database, Drop database.
21. DML :- Stands for Data Manipulation Language. It is a category of SQL command which is used to manipulate the data.
Examples of DML commands are Insert, Delete, Update and Select.
22. Arithmetic Operators are the operators used for mathematical calculation For example: +, -, *,/,%
23. Relational Operators :- These operators are used to compare two values or a column with value in SQL. Different relational
operators are > (less than), >=(less than or equal to), < (greater than, <=(greater than or equal to), = (equal to), ! <> (not
equal to).
24. Logical Operators :- These operators are used to combine two or more conditions. Different logical operators are AND(&&), OR, NOT(-)
25. Data Type :- Data type is required to specify that what sort of value will be shared in particular column. It is important for the overall optimization of our database.
MySQL Data Types:
Numeric Types
| 🔢 Type | 📝 Description | 💾 Storage | 🎯 Precision / Range | 💡 Use Case | 🧪 Example |
|---|---|---|---|---|---|
| Int | Whole numbers, no decimals, positive or negative | 4 bytes (32 bits) | –2,147,483,648 to 2,147,483,647 | Counting, loops, indexing, IDs | int count = 100; |
| Float | Real numbers with decimal, approximate single precision | 4 bytes (32 bits) | ~6 to 7 digits | Scientific calc, graphics, estimations | float temperature = 36.5f; |
| Double | Higher precision floating-point, more accurate than float | 8 bytes (64 bits) | ~15 to 16 digits | Scientific computing, complex math | double pi = 3.14159265358979; |
| Decimal | Real numbers with fixed precision, very accurate | ~16 bytes (128 bits) | ~28 to 29 significant digits | Finance, billing, currencies | decimal salary = 12345.67m; |
String Types
| 🔠 Type | 📝 Description | 💾 Storage | 🎯 Use Case | 🧪 Example |
|---|---|---|---|---|
| Char | Fixed-length string. Always takes the defined length, even if the value is shorter. | n bytes (where n is fixed length) | Codes, fixed-size fields (e.g., gender) | CHAR(5) stores 'Hi ' |
| Varchar | Variable-length string. Uses only as much space as needed. | n + 1 bytes (or n + 2 for large values) | Names, emails, addresses | VARCHAR(20) stores 'Hello' |
| Text | Used for large text data. Stores lengthy paragraphs or articles. | Up to 65,535 bytes (~64KB) | Descriptions, blogs, messages | TEXT stores blog content |
Date & Time Types
| 📅 Type | 📝 Description | 💾 Storage | 🧪 Example | 🎯 Use Case |
|---|---|---|---|---|
| YEAR | Stores a 4-digit year (from 1901 to 2155) | 1 byte | YEAR = 2025 |
Storing birth years, joining years |
| DATE | Stores date only in YYYY-MM-DD format | 3 bytes | DATE = 2025-06-19 |
Birthdates, registration dates |
| TIME | Stores time only in HH:MM:SS format | 3 bytes | TIME = 14:30:00 |
Working hours, appointment times |
| DATETIME | Stores date and time in YYYY-MM-DD HH:MM:SS format | 8 bytes | DATETIME = 2025-06-19 14:30:00 |
Timestamps, logs, booking history |
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.