Arpit Mishra's Blog

 Triggers in SQL A trigger is a special type of stored procedure that runs automatically when an event occurs in the database server.  Types of triggers in SQL - Data Manipulation trigger - Data Definition trigger - Logon trigger How to use before insert trigger?   Trigger Command: delimiter //                                create trigger trigger_name()                                 before insert on table_name()                                 for each row                                 if new.marks < 0 then set new.marks =50;                                  end if ...

 SQL Full Outer Join The full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables and fill in NULLs for missing matches on either side. Syntax: select table1.col1, table2.col2...              from table1                  left join table2                on table1.commonfield=table2.commonfield;                union                select table1.col1, table.col2....                from table1                right join table2                on table1.commonfield=table.2.commonfield; Sample: select e.emp_id, e.first_name, e.last_name, d.dept_id, d.dept_name ...

  SQL Union - Used to combine the result set of two or more select statements removing duplicates. - Each select statement with the union must have the same number of columns. - The selected columns must be of similar data types and must be in the same order in each select statement. - More than 2 queries can be clubbed. Syntax: select column name from table 1 union select column name from table 2; Sample: select product _name from items 1 union select product _name from items 2; SQL Union All - Used to combine the results of two select statements including duplicate rows. - The same rules that apply to the union clause will apply to the union all operator. Syntax: select column name from table 1 union all select column name from table 2; Sample: select product _name from items 1 union all select product _name from items 2; SQL Joins Combine rows/columns from two or more tables, based on a related column between them in a database. SQL Inner Join: The inner join creates a new re...

 SQL Operators - Filters Where clause - Used to specify a condition while fetching the data from a single table or by joining with multiple tables  - Not only used in the select statement but it is also used in the update, delete statement etc. syntax: select * from table name where condition; Exp: select * from emp where emp_id= 101; SQL Operators - Logical AND: (5<2) and (5>3) - False OR.  : (5<2) or (5>3) - True NOT: not (5<2) - True Exp: select * from emp where first_name ='steven' and salary =15000;          select * from emp where first_name ='steven' or salary =15000;          select * from emp where first_name ='steven' and salary !=15000; SQL Operators - Comparison > : Greater than >= : Greater than or Equal to < : Less than <= :  Less than or Equal to <> or ! : Not Equal to = : Equal to SQL Operators - Special Between: Checks an attribute value within range. Like: ...

SQL Command Groups  DDL( Data Definition Language): Creation of Objects DML( Data Manipulation Language): Manipulation of data DCL( Data Control Language): Assignment & removal of permissions TCL( Transaction Control Language): Saving and restoring changes to a database. DDL( Data Definition Language) Create: Creates object in the database/database objects. Alter: Alters the structure of the database/database objects Drop: Delete Objects from the database Truncate: Remove all records from the table permanently Rename: Rename an object. Syntax:  create table table name(              column name data type,              column n data type,               primary key (one or more columns)               ); Exp: create table emp(                       emp_id int(10) not null,  ...

 Normalization - Decompose larger, complex table into simpler and smaller ones. - Moves from lower normal forms to higher normal forms. Normal Forms - First normal form (1NF) - Second normal form (2NF) - Third normal form (3NF) - Higher normal form (BCNF, 4NF, 5NF) Need of Normalisation - In order to produce good database design. - To ensure all database operations to be efficiently performed. - Avoid any expensive DBMS Operations. - Avoid unnecessary replication of information. Functional Dependency Types - Partial functional Dependency - Transitive Dependency First normal form (1NF) - All attributes in the relation are atomic (indivisible value) - There are no repeating elements or group of elements Second normal form (2NF) - A relation is said to be in 2NF  if and only if it is in 1st normal form - No partial dependency exists between non key attributes and key attributes. Third normal form (3NF)   - It is in 2NF. no transitive dependency exists between non key attribu...

 What is Database? A database is a collection of information organized for easy access, management, and maintenance. Exp: Customer data          Product Inventory etc. Types of Data Models - Record-based logical model - Hierarchical Data Model - Network Data Model - Relational Data Model - Object-based logical model - Entity relationship model DBMS Operations - Adding new files - Inserting data - Retrieving data - Modifying data - Removing data - Removing files Advantages of DBMS - Sharing of data across applications - Enhanced security mechanism - Enforce integrity constraints - Better transaction support  - Backup and recovery features Introduction to RDBMS - A relational database refers to a database that stores data in a structured format, using rows and columns. - This makes it easier to locate and access specific values within the database. - It is "relational" because the values within each table are related to each other. tables may also be re...