A Database is an organized collection of data stored electronically.
Example table:
Student_ID Name Department
101 Suganya CSE
102 Divya IT
A DBMS is software that allows users to create, store, update, delete, and retrieve data from a database.
Features: Create databases Insert records Update records Delete records Search data Manage users Examples: FoxPro dBase Microsoft Access
An RDBMS is an advanced type of DBMS where data is stored in tables, and relationships are established using Primary Keys and Foreign Keys.
Example:
Department Table:
Dept_ID Department
1 CSE
2 IT
SQL is a standard language used to create, manage, and manipulate data in relational databases.
Uses of SQL: - Create databases and tables - Insert data - Retrieve data - Update data - Delete data - Manage users and permissions - Create views, indexes, and stored procedures
MySQL is an open-source RDBMS developed by Oracle Corporation that stores data in tables and uses SQL to perform database operations.
Features of MySQL
-Open source
-Fast and reliable
-Supports multiple users
-Stores data in tables
Definition
A table is a database object that stores related information in the form of rows (records) and columns (fields).
Structure of a Table
Column (Field): Defines the type of information stored (e.g., employee_id, name, salary).
Row (Record): Represents one complete set of data.
Definition:
The INSERT command is used to add one or more new records into a database table. It allows users to store new information in the database.
Definition:
The SELECT command is the most commonly used SQL command. It retrieves data from one or more tables and can be combined with clauses such as WHERE, ORDER BY, GROUP BY, and HAVING to produce meaningful results.
Definition:
The FROM clause is used to specify the table from which the data will be retrieved. It tells the SQL query where to fetch the records.
example
Definition:
The WHERE clause is used to filter records based on a specified condition. It returns only those rows that satisfy the given condition.
Definition
The ORDER BY clause is used to sort the result set in either ascending (ASC) or descending (DESC) order based on one or more columns.
Definition:
The GROUP BY clause is used to group rows that have the same values in a specified column into summary rows. It is commonly used with aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX().
Definition:
The COUNT() function is used to count the total number of rows or non-NULL values in a column.
Definition:
The SUM() function is used to calculate the total sum of numeric values in a specified column.
Definition:
The AVG() function is used to calculate the average value of a numeric column.
Definition:
The MAX() function is used to return the highest value from a specified column.
Definition:
The MIN() function is used to return the lowest value from a specified column.
Definition:
The HAVING clause is used to filter grouped records after the GROUP BY clause has been applied. It is mainly used with aggregate functions.
Definition:
The LIMIT clause is used to restrict the number of rows returned by a query. It is useful when you want to display only a specific number of records.
Definition
DISTINCT is a SQL keyword used to remove duplicate values from the result set and return only unique (different) records from one or more columns.
It is commonly used when a table contains repeated values and you want to display each value only once.
Definition:
Arithmetic operators are used to perform mathematical calculations on numeric values in SQL.
Adds two numeric values.
Subtracts one numeric value from another.
Multiplies two numeric values.
Divides one numeric value by another.
Definition:
Comparison operators are used to compare two values. They return TRUE if the condition is satisfied; otherwise, they return FALSE.
Checks if two values are equal.
Checks if two values are not equal.
Checks if the left value is greater than the right value.
Checks if the left value is less than the right value.
Checks if the left value is greater than or equal to the right value.
Checks if the left value is less than or equal to the right value.
Definition:
Logical operators are used to combine multiple conditions in a SQL query.
Returns TRUE only if all conditions are TRUE.
Returns TRUE if at least one condition is TRUE.
Reverses the result of a condition (TRUE becomes FALSE and FALSE becomes TRUE).
Definition:
Special operators are used to perform special types of comparisons and filtering in SQL queries.
Selects values within a specified range (inclusive).
Checks whether a value matches any value in a given list.
Checks whether a value does not match any value in a given list.
Searches for a specified pattern in a column.
Checks whether a column contains NULL values.
Checks whether a column contains non-NULL values.
Definition:
String functions are built-in SQL functions used to manipulate and process text (character) data.
Combines two or more strings into a single string.
Converts all characters in a string to uppercase.
Converts all characters in a string to lowercase.
Returns the number of bytes in a string.
Returns the number of characters in a string.
Extracts a specified part of a string.
Returns the leftmost specified number of characters from a string.
Returns the rightmost specified number of characters from a string.
Replaces all occurrences of a specified substring with another substring.
Removes leading and trailing spaces from a string.
Reverses the characters in a string.
definition:
MySQL ALTER statement is used when you want to change the name of your table or any table field.
It is also used to add or delete an existing column in a table.
The ALTER statement is always used with "ADD", "DROP" and "MODIFY" commands according to the situation.
Definition:
The UPDATE command is used to modify the existing records in a table. It can update one or multiple rows based on a specified condition.
Definition:
The DELETE command is used to remove one or more records from a table. It supports conditions to delete only the required records while keeping the table structure unchanged.
Definition:
The TRUNCATE command is used to remove all records from a table while preserving the table structure, columns, indexes, and constraints. It is faster than the DELETE command because it removes all rows at once.
Definition:
The DROP command is used to permanently remove a database object, such as a table or database, along with all its data, structure, indexes, and constraints. Once dropped, the object cannot be used unless it is created again.
Definition:
INNER JOIN is used to retrieve only the records that have matching values in both tables. If there is no matching record in either table, it is not included in the result.
Key Point: Returns only matching rows from both tables.
Definition:
LEFT JOIN is used to retrieve all records from the left table and the matching records from the right table. If there is no matching record in the right table, the result displays NULL values for the right table's columns.
Key Point: Returns all rows from the left table and only the matching rows from the right table.
Definition:
RIGHT JOIN is used to retrieve all records from the right table and the matching records from the left table. If there is no matching record in the left table, the result displays NULL values for the left table's columns.
Key Point: Returns all rows from the right table and only the matching rows from the left table.
Definition:
A subquery is a query written inside another SQL query. It is used to obtain a result that is then used by the outer query
.
Definition:
CTE (Common Table Expression) is a temporary named result set created using the WITH keyword. It makes complex queries easier to read and reuse.
Definition:
A window function performs calculations across a set of related rows without combining those rows into a single row.
Unlike GROUP BY, a window function keeps the individual rows.
** Common window functions:**
ROW_NUMBER()
RANK()
DENSE_RANK()
SUM()
AVG()
MIN()
MAX()
LAG()
LEAD()
Definition:
ROW_NUMBER() assigns a unique sequential number to each row based on the specified ordering.
Definition:
RANK() assigns a rank to each row based on the specified ordering. If two rows have the same value, they receive the same rank, and the next rank is skipped.
Definition:
DENSE_RANK() assigns the same rank to equal values, but unlike RANK(), it does not skip the next rank.
Definition:
PARTITION BY divides the rows into groups before applying a window function.
Definition:
LEAD() retrieves the value from the next row in the specified order.
Definition:
LAG() retrieves the value from the previous row in the specified order.
definition:
A VIEW in SQL is like a virtual table. It doesn’t store data itself but shows the result of a query as if it were a table. You can query a view just like a normal table.
A Stored Procedure in SQL is a precompiled collection of one or more SQL statements stored in the database, which can be executed as a single unit. It improves performance, supports parameters, enhances security, and allows reuse of complex logic.
