Arpit Mishra's Blog

Posts

Basics of SQL Part 7

- October 12, 2022

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 ; // Sample: delimiter // create trigger marks_verify_st before insert on student for each row if new mark < 0 then set new.marks = 50; end if ; // How to drop the trigger? Syntax : drop trigger trigger_name ; Views in SQL Views are actually virtual tables that do not store data from the rows but display data stored in other tables. views are

Basics of SQL Part 6

- October 11, 2022

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 from emp e left join dept d on e.dept_id=d.dept_id union select e.emp_id, e.first_name, e.last_name, d.dept_id, d.dept_name from emp e right join dept d on e.dept_id=d.dept_id; SQL Cross Join - Assume the

Basics of SQL Part 5

- October 10, 2022

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 result

Basics of SQL Database Part 4

- October 09, 2022

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: Checks an attribute value matches a given str

Basics of Database Part 3

- October 08, 2022

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, first_name varchar(20), last_name varchar (20) not null, salary int (10) not null, primary key (emp_id)

Basics of Database Part 02

- October 07, 2022

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 attributes and key at

Search This Blog