What is Database?
It is used to collect and store the data using structured and unstructured patterns. the database provides different rules to store and manage data.
1) DBMS:- It provides storage of data using file pattern, we can not establish a relationship between files and records.
MS-Excel, XML
2) RDBMS: It means relational database management system, it provides a set of rows and columns to store data means it provides a database table to store and manage records.
We can establish relationships between tables and database objects.
Name of RDBMS Software:-
1) Oracle
2) MYSQL
3) MS-SQL
4) PostgreSQL
5) IBM DB2
3) ORDBMS:- It means Object-Relational Database Management System.
It provides storage of data using object patterns we can perform all operations under objects to manipulate the data.
ORDBMS Tools:- Mongo DB is the database software tool to implement in Object patten
Oracle Object & Forms
4) CDBMS:- CDBMS means a Cloud-based database system, many cloud providers provide cloud databases where we can create and operate databases from any device using the internet.
AWS
Window AZURE
Salesforce
Google Cloud
Download MySQL Server by the XAMPP with this link?
https://www.apachefriends.org/download.html
What is SQL?
It is a structured query language that is used to provide a set of commands to perform database operations. This language is common for all database software tools that follow the principle.
SQL Language is Categorised into five different parts:-
1) DDL(Data definition language)
create, alter, drop, truncate
2) DML(Data Manipulation language):-
Insert, Update, Delete
3) DQL(Data Query Language):-
Select
4) DCL(Data Control Language):-
Grant
Revoke
Deny
5) TCL(Transaction Control Language):-
Rollback
Commit
SavePoint
Practice SQL SCRIPT of MS-SQL Queries?
create table student(rno int,sname varchar(50),branch varchar(50),fees int)
insert into student values(1001,'xyz','CS',45000)
insert into student values(1002,'abc','IT',65000)
select * from student;
select Rno,Fees from Student;
insert into student(rno,fees) values(1007,36000)
update student set sname='Ramesh' where rno=1001
update student set sname='Kapil',branch='EC' where branch is null;
delete from student where rno=1001
How to execute MYSQL Console in Windows Command Prompt
cd c:\xampp\mysql\bin
mysql.exe -u root --password
Command to show all databases
show databases
command to enter into a particular database
use databasename
If you want to create a database
create database databasename
https://www.freeprojectz.com/entity-relationship/college-management-system-er-diagram
Syntax of creating a table
create table tablename(columnname datatype, columnname datatype)
datatype:- it is used to represent a pattern of data and the size of data in memory.
int:- Integer type data
float:- it is used to contain decimal type data
char:- It is used to contain char and char size always will be fixed
char(10):- it will contain 10 char
varchar:- it is called variable-length char means varchar size is not fixed it will allocate memory according to assigned data.
varchar(50):- it will allocate nothing by default
Text:- it will contain a collection of chars without defining the size, and it supports HTML, XML, SCRIPT based text.
Date:- it will store date type data using dd/mm/yyyy pattern
DateTime:- It will store data using date and time format dd/mm/yyyy hh:mm:ss
Binary:- it is used to store images or any binary file
CLOB:- character large object
BLOB:- Binary Large Object
LOB:- It will handle both
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
Data Constraint
Primary key:- It is used to create a unique and not null value-based column, it will be single in one table.
Unique:- It is used to provide restrictions to enter unique data, but data can be null. it can be applied on multiple columns.
NOT Null:- It is used to provide restrictions to enter data on a particular column
Foreign key:- It is used to provide referential integrity constraints between tables. the primary key of the master table will work as a foreign key in the child table.
Table Level Constraint Declaration of Foreign key:-
This type of constraint will be declared after the declaration of all the columns
Create table tablename(columnname datatype, constraint foreign key(columnname) references matsertable(mastertableprimarykeycolumnname))
Master table
create table dept(deptid int primary key,deptname varchar(50) NOT NULL);
create table emp(empid int primary key, empname varchar(50) NOT NULL,job varchar(50),salary float, dept_deptid int, constraint foreignkey(dept_deptid) references dept(deptid));
Multiple Constraint Declaration?
create table tablename(columnname1 datatype, columnname2 datatype NOT NULL, constraint unique(columnname2), constraint constraintname primary key(columnname1);
Constraintname pk_columnname_tablename
Alter Command In SQL:-
It is used to modify the structure of a table, for example, if we want to add a new column, drop a column, add a constraint(primary key, foreign key), rename column name, change the data type, increase or decrease column size.
Syntax to add a new column:-
Alter table tablename add column datatype.
Syntax to rename a column:-
Alter table tablename change oldcolumn newcolumnnae datatype.
Syntax to change data type or extend size of datatype-
Alter table tablename change columnname columnname datatype.
Syntax to drop a column:-
Alter table tablename drop column columname.
Syntax to drop constraint and add constraint:-
Alter table tablename add constraint constraintname(columnname)
Alter table tablename drop foreign key constraintname?
Alter table tablename drop index constraintname
to show the structure of table in MySQL:-
show create table tablename;
Drop Command:-
It is used to destroy the complete structure of the table.
drop table table-name;
Truncate:- it is used to clear all the records, truncate will reset the value of auto-incement (identity) column value but delete will not reset the identity column value, we can not apply where clause under truncate but we can apply where clause under delete command.
Syntax of Truncate Command:-
Truncate table tablename;
MariaDB [collegeerp]> create table stu(rno int auto_increment primary key,nam
archar(50));
Query OK, 0 rows affected (0.234 sec)
MariaDB [collegeerp]> insert into stu(name) values('xyz');
Query OK, 1 row affected (0.089 sec)
MariaDB [collegeerp]> insert into stu(name) values('abc');
Query OK, 1 row affected (0.083 sec)
MariaDB [collegeerp]> insert into stu(name) values('mno');
Query OK, 1 row affected (0.083 sec)
MariaDB [collegeerp]> select * from stu;
+-----+------+
| rno | name |
+-----+------+
| 1 | xyz |
| 2 | abc |
| 3 | mno |
+-----+------+
3 rows in set (0.000 sec)
MariaDB [collegeerp]> delete from stu;
Query OK, 3 rows affected (0.093 sec)
MariaDB [collegeerp]> insert into stu(name) values('mno');
Query OK, 1 row affected (0.085 sec)
MariaDB [collegeerp]> select * from stu;
+-----+------+
| rno | name |
+-----+------+
| 4 | mno |
+-----+------+
1 row in set (0.002 sec)
MariaDB [collegeerp]> truncate table stu;
Query OK, 0 rows affected (0.312 sec)
MariaDB [collegeerp]> insert into stu(name) values('mno');
Query OK, 1 row affected (0.065 sec)
MariaDB [collegeerp]> select * from stu;
+-----+------+
| rno | name |
+-----+------+
| 1 | mno |
+-----+------+
1 row in set (0.000 sec)
DML:- It is used to insert, update and delete records into tables.
insert:- It is used to add single record or multiple records;
insert into tablename(columname1,columnname2) values(value1,value2);
Script:-
MariaDB [collegeerp]> create table stu(rno int auto_increment primary key,nam
archar(50));
Query OK, 0 rows affected (0.234 sec)
MariaDB [collegeerp]> insert into stu(name) values('xyz');
Query OK, 1 row affected (0.089 sec)
MariaDB [collegeerp]> insert into stu(name) values('abc');
Query OK, 1 row affected (0.083 sec)
MariaDB [collegeerp]> insert into stu(name) values('mno');
Query OK, 1 row affected (0.083 sec)
MariaDB [collegeerp]> select * from stu;
+-----+------+
| rno | name |
+-----+------+
| 1 | xyz |
| 2 | abc |
| 3 | mno |
+-----+------+
3 rows in set (0.000 sec)
MariaDB [collegeerp]> delete from stu;
Query OK, 3 rows affected (0.093 sec)
MariaDB [collegeerp]> insert into stu(name) values('mno');
Query OK, 1 row affected (0.085 sec)
MariaDB [collegeerp]> select * from stu;
+-----+------+
| rno | name |
+-----+------+
| 4 | mno |
+-----+------+
1 row in set (0.002 sec)
MariaDB [collegeerp]> truncate table stu;
Query OK, 0 rows affected (0.312 sec)
MariaDB [collegeerp]> insert into stu(name) values('mno');
Query OK, 1 row affected (0.065 sec)
MariaDB [collegeerp]> select * from stu;
+-----+------+
| rno | name |
+-----+------+
| 1 | mno |
+-----+------+
1 row in set (0.000 sec)
Update Command:- It is used to edit the record from tables, we can update single rows, single columns, multiple columns, and multiple rows based on condition, but the update command always will work based on rows.
Syntax of update.
update specific column and specific row:-
update tablename set columnname=value where columnname=value;
update multiple columns and multiple row:-
update tablename set columnname=value,columnname=value where columnname=value;
update tablename set columnname=value,columnname=value;
update multiple rows and columns without condition:-
update tablename set columnname=value,columnname=value;
WAQ to edit the record of student name whose name is null?
update student set name='ramesh' where name is null;
WAQ to edit the record of a student whose name is blank and the name is null?
update student set name='test' where name is null or name='';
WAQ to edit the record of a student whose fee is between 1000 to 5000, increase their fee with 2000 extra amount?
MariaDB [collegeerp]> update stu set fee=fee+2000 where fee>=1000 and fee<=5000;
Query OK, 2 rows affected (0.178 sec)
Rows matched: 2 Changed: 2 Warnings: 0
WAQ to update the fee with 5000 students whose fee is null and rno is odd?
update student set fee=5000 where fee is null and mod(rno,2)==0;
Delete Command:- it is used to remove the individual row and group of rows based on condition;
to delete all the record
delete from tablename;
to delete the particular record
delete from tablename where columnname=data.
WAQ to delete the record of students whose rno is 1, 3,5, and 7?
MariaDB [collegeerp]> delete from stu where rno=1 or rno=3 or rno=5 or rno=7;
Query OK, 4 rows affected (0.102 sec)
WAQ to delete the record of students whose rno is between 1 to 5?
delete from stu where rno>=1 and rno<=5;
WAQ to delete the record of students whose rno is 6 and fees is null?
delete from stu where rno=6 and fee is null
Select Command:-
This command is used to display the particular column data or all columns data. if we want to display all records then we use the (*) symbol.
select * from tablename
select col1,col2 from table name
we can perform operations also under the select command
select 1+2 from table name
Apply condition under select command:-
If we want to apply conditions under the select command then we will use clauses.
Select support operator, predefined method, clause, subqueries, join, set operation to perform database operation and selection.
WAQ to display the record of students whose fee is >10000 and rno is even?
select * from stu where fee>10000 and mod(rno,2)=0;
WAQ to display the record of students whose fees are 5 digits and names start from k and j?
select * from stu where fee>=10000 and fee<100000 and (name like 'k%' or name like 'j%')
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
Operator in SQL:-
It is used to perform various operations using column data.
1) Arithmetic operator:-
+ ----> addition
- ----> substraction
/ ------> divide
* -------> multiplication
mod -----> modulas
mod(base,denominator)
2) Conditional operator:-
> Greater then
< Less then
>= Greater then equall
<= Less then equall
!= Not equall
is null (for null comparison)
is not null (for not null comparison)
= for equality condition
3) In and Not in operator:-
it is used to match the group of records from the column values, if it matches then display the record.
It is the alternative operator of the "or" operator.
Syntax of In Operator:-
select * from tablename where columnname in (values)
WAQ to display the record of students whose rno is 1, 3, and 5.
select * from stu where rno in (1,3,5)
Not in operator:- it is the opposite of the "In" operator, if a record does not match then display record.
Syntax of Not in operator
select * from stu where rno not in (1,3,5);
4) between and not between:- it is used to display range-based data. between operator will work under numeric column and date type column. between will exclude the record.
Syntax
select * from tablename where columnname between startvalue and endingvalue.
WAQ to display the record of students whose rno is a one-digit number?
select * from stu where rno between 1 and 9;
Not between:- It is the opposite of between operator
select * from tablename where columnname not between start and end;
5) Like and Not Like:-
It is used to find the record based on pattern matching, the pattern will be created by wildcard characters [], % and _
% is used to match the char from starting position and last position
<char>%:- it is used to match char from starting position
%<char>:- it is used to match char from the last position
<char>%<char>:- it is used to match the char from starting and last position.
_ is used to skip the char for pattern matching, we can use multiple _ to skip multiple char.
WAQ to find the record student whose name starts from a vowel?
select * from stu where name like 'a%' or name like 'e%' or name like 'i%' or name like 'o%' or name like 'u%';
Not like:- It is the opposite of like operator
select * from stu where name not like 'patternmatch';
WAQ to display the record of a student whose second last char is a?
select * from stu where name like %a_'
WAQ to display the record of students whose names start from i and e and end with r?
select * from stu where name like 'i%r' or name like 'e%r';
6) Logical operator:-
and:- it will return true when all conditions will be true
select * from tablename columnname = value and columnname and value
or:- it will return true when only one condition is true
not:- it will convert the true condition to false and false conditions, it can be used as a symbol or keyword.
!=
not in
7) Set operator:-
it is used to merge the rows of a table using union, union all, intersect, and minus.
if we apply set operator then column type should be the same on all tables. we can implement set operator on single columns and multiple columns both.
union:- it is used to combine the record but repeated value display once.
select * from tablename1 union select * from tablename2;
union all:- It is used to combine the records but display repeated value .
select * from tablename union all select * from tablename2;
intersect:- Display common column data.
select * from tablename intersect select * from tablename2;
minus:- It is used to display the record of the first table that is not matched by another table.
select * from tablename except select * from tablename2;
8) Join Operator:- Join is used to merge the table columns based on matching conditions, join is also called the denormalization process because normalization is used to divide the tables and denormalization will split the records of the table.
type of Join:-
1) Default Join or Cross Join:-
it will cross multiply the number of rows from the first table to the second table.
select * from table1 cross join table2; //ANSI
select * from table1, table2; // NON ANSI
2) inner join:-
it will display only matching record from both the tables.
select * from table1 inner join table2 on table1.column=table2.columname ; #ansi
select * from table1,table2 where table1.columnname=table2.columnname; #NONANSI
select empname,deptname from emp inner join dept on emp.dno=dept.deptid;
select empname,deptname from emp ,dept where emp.dno=dept.deptid;
WQ to display matching employee name and dept name?
select empname,deptname from emp inner join dept on emp.dno=dept.deptid;
WAQ to exclude the record of dept 10 under employee and dept table?
select * from emp inner join dept on emp.dno=dept.deptid where deptid!=10;
or
select * from emp, dept where emp.dno=dept.deptid and deptid!=10;
Outer join:-
It will display matching details and non-matching details from both tables.
2.1) Left Outer Join:-
This join operation displays the matching of rows from first table to second table and display null value under the second table.
select * from dept left outer join emp on dept.deptid=emp.dno?
2.2) Right Outer Join
This join operation displays the matching of rows from second table to second table and display null value under the second table.
select * from dept right outer join emp on dept.deptid=emp.dno?
2.3) Full Outer Join:- Not Supported by MYSQL and MariaDB?
Natural Join:- It is similar to inner join but it will work when matching column data type and column name should be same, it is alternative of inner join.
select * from table1 natural join table2;
select * from emp natural join dept;
Self Join:- It is used to implement the Join operation under a single table.
it will create two dynamic instances of a table and perform a join operation.
for example, if we create a student table and identify the student name and respective class representative name then we can apply self join to implement this operation.
create table tablename(rno int,same varchar(50),monitored int);
WAQ to display studentname and monitor name under the above table?
Subqueries:-
If we write more than one query in a nested sequence then it is called subqueries.
subquery returns a single record or multiple records according to the output of the internal query we provide the operator on the outer query based on a result of inner query.
if Inner query return single row then we can use the = operator otherwise we will use IN, NOT IN, ANY, ALL, EXIST, NOT EXIST
select * from tablename where columnname operator (select * from tablename)
WAQ to display the employee, who is working on sales dept?
Answer on download sheet?
WAQ to display the employee, who is working on sales dept?
WAQ to find the second max salary of an employee?
WAQ to find the record of the employee who is working on sales and IT both?
Operator of SubQuery:-
1) In, Not in for multiple records:-
It is used to display particular column value
2) Any, All for multiple records:-
Any will return true when only one condition of inner query is true.
select * from tablename where columnname = any(inner query)
All will return true when all condition of inner quiery is true.
select * from tablename where columnname=all(innerquery)
3) Exist and Not Exist to manage true and false:-
It will execute the result of an outer query when the inner query condition is true otherwise not executed.
select * from tablename where columnname exist(inner query)
4) =,>=, != for Single record
A clause in SQL:-
.....................................................................................................
It is used to apply the condition under the select statement and filter the record.
1) Where:- It is used to apply the condition initially.
select * from tablename where columnname=data;
2) Group by:- It is used to logically create the subgroup of table columns then apply conditions.
Groupby clause uses agreegate function (min,max,count,sum,avg) .
We can select only the group column or aggregate function under group by clause.
3) Having:- It is used to apply the condition under column after group by. having always apply to filter using the aggregate function.
select group column name, aggregate function from table name group by column name having column name condition value.
4) Order by:-
It is used to arrange the data using ascending and descending order. it will apply at last.
select * from tablename order by;
select group column name, aggregate function from table name group by column name having column name condition value order by.
In-built Function:-
1) Agreegate Function:-
1.1) max():- It is used to find maximum record
select max(columnname) from tablename.
1.2) min():- It is used to find minimum record
select min(columnname) from tablename.
1.3) avg():- it is used to display average record
select avg(columnname) from tablename.
1.4) stddev():- It is used to display standard deviation
select avg(columnname) from tablename.
1.5) count():- It is used to count number of records
select count(columnname) from tablename.
1.6) Sum():- It is used to display sum of records
select sum(columnname) from tablename.
The in-built function of String
1) ASCII():- it is used to return ASCII code of any character, A to Z ASCII code is 65 to 90.
select ascii('a')
2) substring():- It is used to split the string from indexing.
substring(columnname,startindex,lastindex)
3) reverse():- it isd used to reverse the string
reverse(columnname)
4) replace():- It is used to replace the char from the string columnname
select replace(columnname,'oldchar','newchar') from tablename;
5) Trim(), RTrime() and LTrim():- It is used to remove space from lefty, right and both.
Trim() remove space from left, rtrim() remove space from right and ltrim() remove space from left.
6) Space():- count the number of spaces in the string.
select space(6);
7) Repeat():- repeat the number of strings.
select repeat(5);
8) Rpad and Lpad:- It is used to pad the content under columns right and left.
select rpad(columnname,coumnsize,'char')
9) Soundex:- It is used to match the content from sounded code.
select * from tablename where sounded(columnname)=soundex(columnname);
Date time function in Mysql:-
This function is used to manage date and time operation in MYSQL
1) curdate:- it displays the current date of the server or system
select cordate()
2) sysdate:- It displays the system date and time
select sysdate()
3) curtime():- it displays the current time of the system or server
select curtime()
4) addtime():- it is used to append time under date.
select addtime (date,'hh:mm:ss ms')
5) datediff():- It is used to display the difference between date
select datediff(startdate,enddate) ;
6) date_format():- It is used to display the date into different date format
select date_format(date,"%D-%M-%Y)
7) day():- It return current day under date
select day(curdate())
8) dayname():- It display, dayname that today is wednasday.
select dayname(currdate
9) current_timestamp():- It will display the date according to date, time with a different timezone.
select current_timestamp();
10) now() and localtime():- now will display current hosted machine time and localtime() will display local machine time.
select now();
select localtime()
11) makedate:- we can make date using year and day.
select makedate(year,day);
12) maketime():- display current time
select maketime(hrs,minute,second)
Mathematical function in MySQL:-
1) abs():- it is used to convert negative value to positive or absolute value.
select abs();
2) sqrt():- It is used to calculate the Square root of any number.
select sqrt(5);
3) pow():- It is used to return power of any number;
select pow(5);
4) exp():- It provide exponent value of any number;
select exp(5);
5) log():- It provide display log file
select log(5);
6) ceil():- it is used to return highest integer value. such as 3.8 to 4, 4.1 to 5
select ceil(2.3)
7) floor():- it is used to return current integer value, such as 3.8 to 3 and 4.1 to 4
select floor(5.3)
8) round():- It is used to return rounded value
select round(2.456,2); // 2.46
9) truncate():- it display exact data after decimal
select truncate(2.4563,2)
10) sin(),cos(),tan(),cosec(),sec(),cot():-
we pass the value it return particular data.
PL/SQL:-
PL means procedure-based language to write multiple SQL statements, if we want to perform logical operations using multiple statements then we can use PL/SQL block.
It provides multiple database objects to create the block
1) Views:-
It is the logical view of the table, if we want to subcategorize the table data then we can create a view.
the view depends on the tables. we can also create the view to contain a complex query that can be used multiple times in an application.
Syntax of view
create view <viewname> as
SQL Statements
how to call view
select * from viewname;
Type of view:-
1) Simple view:- Simple view structure is similar to a table, we can perform insert, update and delete operations under view.
if we want to categorize the table into multiple groups then we can use simpleview.
2) Complex view:- This type of view are generally used to contain complex SQL query that can be used multiple times.
for example, if we want to display the record of the max fee paid by students record then we can use a complex view.
2) Procedure:-it is precompiled block of code that is used to contain a set of SQL statements to complete the task.
Procedure performance is better as compared to normal SQL queries because SQL queries compile then execute but the procedure will directly execute.
We can define two different types of procedures in the MySql Database Server
1) Scaler procedure:-
using this procedure, we can solve the mathematical, logical, conditional problems based on table columns with scaler values.
DELIMTER //
create procedure procedurename(parameters)
begin
statement1
end
end //
Create Addition Program Procedure for addition:-
CALL procedure_name(argument_list);
create procedure add_proc()
begin
declare a,b,c int DEFAULT 0;
set a=10;
set b=20;
set c = a+b;
select c;
end;
call add_proc()
Create Addition Program using ParaMETRISED pROCEDURE:-
DELIMITER //
create procedure add_proc1(a int,b int)
begin
declare c int DEFAULT 0;
set c = a+b;
select c;
end //
call add_proc1(10,2)
Create a Stored procedure to apply conditions and check even and odd?
DELIMITER //
create procedure chek_even_odd(a int)
begin
declare c varchar(50);
IF mod(a,2)=0 THEN
set c = "even";
ELSE
set c = "odd";
End IF;
select c;
end //
Call chek_even_odd(a int)
Wap to check greatest number?
DELIMITER //
create procedure chek_greatest(a int,b int,c int)
begin
declare c varchar(50);
IF a>b and a>c THEN
set c = "a is greatest";
ELSE IF b>c THEN
set c = "b is greatest";
ELSE
set c = "c is greatest"
End IF;
select c;
end //
Create Procedure to check Greatest Number?
DELIMITER //
create procedure check_greatest(a int,b int,c int)
begin
declare r varchar(50);
IF a>b and a>c THEN
set r = 'a is greatest';
ELSEIF b>c THEN
set r = 'b is greatest';
ELSE
set r = 'c is greatest';
End IF;
select r;
end//
Call this procedure:-
call check_greatest(100,200,300);
Create a Stored Procedure to print a table of any number?
DELIMITER //
create or REPLACE procedure print_table(a int)
begin
declare i int;
declare s varchar(50);
set i=1;
label1:WHILE i<=10 DO
set s = 'hello';
set i = i+1;
end WHILE label1;
select s;
end //
call print_table(5)
Create procedure to print factorial:-
DELIMITER //
create or REPLACE procedure print_fact(a int)
begin
declare i int;
declare f int;
set i=1;
set f=1;
label1:WHILE i<=a DO
set f = f*i;
set i = i+1;
end WHILE label1;
SELECT f;
end //
call print_fact(5);
2) Table-based Procedure:-
This type of procedure contain SQL query and return data under table format.
DELIMITER //
create procedure getstudata()
begin
select * from stu;
end //
Create a procedure to display name and salary and salary is taxable or not where empid will be passed as a parameter;
DELIMITER //
create or REPLACE procedure print_sal(eid int)
begin
declare name varchar(50);
declare sal int;
declare tax varchar(50);
select empname,salary into name,sal from emp where empid=eid;
select name,sal;
end //
call print_sal(1001)
Part2:-
DELIMITER //
create or REPLACE procedure print_sal(eid int)
begin
declare name varchar(50);
declare sal int;
declare tax varchar(50);
select empname,salary into name,sal from emp where empid=eid;
IF sal>40000 THEN
set tax = "Taxable";
ELSE
set tax = "NOT TAXABLE";
END IF;
select name,sal,tax;
end //
call print_sal(1001)
Stored Procedure for Data Insertion:-
DELIMITER //
create or REPLACE procedure insert_stu(rn int,sname varchar(50),fee int)
begin
insert into stu1(rn,sname,fee) values(rn,sname,fee);
end //
call insert_stu(1892,'xyz',5000);
Update Storedprocedure:-
DELIMITER //
create or REPLACE procedure edit_stu(r int,s varchar(50),f int)
begin
update stu1 set sname=s,fee=f where rn=r;
end //
call edit_stu(1,'ram',400)
Type of Parameter in Procedure:-
1) In:- The default parameter is in, it is used to manage input data.
2) Out:- It is called the output parameter, it will contain output data
Example of In and Out
DELIMITER //
create or REPLACE procedure display_sal_max(IN eid int,OUT msal int)
begin
Select max(salary) into msal from emp where empid=eid;
end //
call display_sal_max(1001,@m); //
select @m;
3) INout:- it is used to implement common tasks means we can use it as an Input and Output both.
Create Procedure to print max salary of an employee where empid will be pass as an Input parameter and maxsal will pass as an out parameter.
DELIMITER //
create or REPLACE procedure display_sal_max(INOUT msal int,in eid int)
begin
Select max(salary) into msal from emp where empid=eid;
end //
call display_sal_max(@msal,1001); //
select @msal;
3) Function:-
It is also a pre-compile block of the statement, but it returns data that can be gotten by calling the program.
DELIMTER //
create function procedurename(parameters) RETURNS datatype
begin
statement1
end
return output;
end //
how to call:-
select function_name(argument_list);
Create Addition Program Procedure for addition:-
DELIMITER //
create function add_fun() returns int
begin
declare a,b,c int DEFAULT 0;
set a=10;
set b=20;
set c = a+b;
return c;
end //
Call function
SELECT add_fun();
Parametrised function:-
DELIMITER //
create function add_fun1(a int,b int) returns int
begin
declare c int;
set c = a+b;
return c;
end //
Call :-
SELECT add_fun1(10,2);
Create a function to find the salary of emp according to empid;
DELIMITER //
create or REPLACE function display_sal_max_fun(eid int) returns int
begin
declare msal int;
Select max(salary) into msal from emp where empid=eid;
return msal;
end //
select display_sal_max_fun(101)
How to call function under procedure:-
DELIMITER //
create or REPLACE procedure fun_call()
begin
declare msal int;
set msal = display_sal_max_fun(1001);
select msal;
end //
call fun_call();
4) Triggers:-
It is a special type of procedure that will be called implicit(automatically) during DML operation(insert, update and delete)
CREATE TRIGGER trigger_name
(AFTER | BEFORE) (INSERT | UPDATE | DELETE)
ON table_name FOR EACH ROW
BEGIN
--variable declarations
--trigger code
END;
Before Trigger Example:-
create a trigger for data insertion on the student table, if the fee is <1000 then fee will be 0?
DELIMITER //
Create Trigger before_insert_stufee
BEFORE INSERT ON stu1 FOR EACH ROW
BEGIN
IF NEW.fee < 1000 THEN SET NEW.fee = 0;
END IF;
END //
When we insert the record into the student table then this trigger will be called automatically.
Create trigger under student table, if a fee is more than 50000 then data not inserted into the database and provide acknowledgment.
DELIMITER //
Create Trigger before_insert_stufee_max
BEFORE INSERT ON stu1 FOR EACH ROW
BEGIN
IF NEW.fee > 50000 THEN
SIGNAL SQLSTATE '50001' SET MESSAGE_TEXT = 'Fee should not be above 50000';
END IF;
END //
When we insert data more than 50000 then the trigger will be called implicit.
insert into stu1(rn,sname,fee) values(9999,'test',70000).
After Trigger:-
After Trigger will be used to perform an operation on any dependent table after insertion, update and deletion?
When data is inserted into the stu1 table then it should be deleted from stu table.
DELIMITER //
Create Trigger after_insert_stu
After INSERT ON stu1 FOR EACH ROW
BEGIN
delete from stu where rno=NEW.rn;
END //
5) Cursor:-
It is a special type of data type that is used to store composite types of data under memory.
the cursor can contain table data under select statements.
Step for cursor:-
1) declare cursor
declare cursorname cursor for select statement;
2) open cursor
open cursorname
3) fetch data from cursor
fetch next from cursorname into varname
4) close cursor
close cursorname
Create a procedure to display names of all students into list form?
DELIMITER //
create procedure sp_get_stu(INOUT name_list varchar(4000))
begin
declare st integer default 0;
declare s_name varchar(50) DEFAULT '';
declare stu_cur CURSOR FOR
Select sname from stu1;
declare continue handler for not found set st = 1;
open stu_cur;
get_list : Loop
FETCH stu_cur into s_name;
if st = 1 THEN
LEAVE get_list;
END IF;
set name_list = concat(s_name,",",name_list);
END Loop get_list;
close stu_cur;
end //
Call custor
set @name_list = '';
call sp_get_stu(@name_list);
select @name_list;
POST Answer of Questions and ASK to Doubt