h ps://github.

com/sainathatheli/5APlusplus

What is a Database?

A Data Base is an Organized Collec on of Data which can be easily

accessed, managed and updated.

In today’s World, Data plays a vital role in every business. In our day to day life, we see or

interact with many applica ons and So ware’s, every applica on that we see or work with will

have two parts:

1. GUI (Graphical User Interface / Front end)

2. Database

To keep it simple GUI is the part where user interacts with (like Facebook applica ons – look

and feel) and the Data that we see in the applica on (like Facebook profile, messages, images

and videos) are pulled from Database.

End User who interacts with the applica on may not know how the data is being fetched and

where so much of informa on is stored. Internally all the dynamic content that we see in the

applica on is fetched from Database.

Database and all its components should be designed and configured at the me of applica on

development. Once the applica on is developed we will not be able to make changes to the

database structure as every change will hugely affect the business applica on GUI code.

It is very important to make sure that data is securely maintained and accurately stored.

So to maintain security and accuracy in database a set of rules / so ware system is defined and that
we call it as DBMS (Data Base Management System – which performs all the opera ons on the

database)

What is DBMS?

DBMS (Database Management System) is a so ware tool that is used to store and manage data

in the Database.

A database management system contains a set of programs that control the crea on,

maintenance, and use of a database. Like:

Adding new data to the table.

Modifying exis ng data.

Dele ng unwanted data.

DBMS allows different user applica on programs to concurrently access the same database.

Before Database and DBMS were introduced, tradi onal approach was to store data in flat files

(physical files in system) but there were some disadvantages with it.

What is RDBMS?

A rela onal database management system (RDBMS) is a Database Management System

(DBMS) that is based on the rela onal model introduced by E. F. Codd and most popular

databases currently in use are based on the rela onal database model. To put in different

words RDBMS is built on top of in which data is stored in tables and the rela onships among

the data are maintained. The data stored in a table is organized into rows and columns. Each

row in a table represents an individual record and each column represents a field. A record is

an individual entry in the database.

Difference between DBMS and RDBMS

----------------------------------

For example, consider the database which stores employee informa on.
In DBMS, all details like empno, ename, job, salary, deptno, dname, loca on, street, city, state, phone
will

be stored in a single table.

But in RDBMS, the data model will be designed in such a way that

like the empno, ename, job, salary and deptno will be stored in emp table and deptno, dname,
loca on will be stored in dept table and loca on, street, city, state, phone will be stored under
loca ons table.

Any informa on to be obtained is done by properly rela ng the ‘emp’, ‘dept’

and ‘loca ons’ tables.

MYSQL:

======-------

Sql is classified into

DDL

===

Data defini on Language

DDl is used to specify the structure or schema for Objects

cmds are : create,truncate,alter,rename,drop

DML

===

Data manipula on Language

DMl is used to perform insert or update or delete a record from database Object(table)
commands are :- insert,update,delete

DRl(Data retrevial language)

or DQL(Data query language)

============================

Drl is used t retrieve the data from the table

command:

=======

select

DDL:

====

create command is used to specify the structure for

Objects(database,table,view,procedure,func on...etc)

Step1:

======

if you want to create any objects in mysql we need to use database.

1)createing a database:
syntax:

=======

create database <specify database name>

ex:

===

create a database named as cts.

sol:

====

create database cts;

to list all the database:

=========================

cmd is :---

show databases;

to create any objects(database,table,view,procedure,func on...etc)

in database first we need to use the database:

====================================================================

cmd:

-----
use database;

ex:

----

use cts;

to check current your are in which database:

=============================================

cmd:

----

select database();

crea ng a table:

=================

syntax:

=======

creat table [if not exists] tableName (

col1 datatype,

col2 dataetype,

col3 datatype,,,,,,,,,coln datatype);

ex:

===
tablename: employee

columnsName datatype size

empno int 4

ename varchar 20

sal decimal 10,2 -->738.30

sol:

====

create table employee(empno int(4),ename varchar(20),sal decimal(10,2));

to list all the tables from database:

=====================================

cmd:

----

show tables;

to describe the structure of the table:

=======================================

syntax:

-------

describe tablename;
ex:

---

describe employee;

Once the table is created we need to insert data(record) into

table for all columns using insert command

====================================================

syntax:

-------

insert into tablename values(value1,value2.....,valuen);

ex:

===

insert five records into table employee

sol:

----

insert into employee values(101,'smith',3000);

insert into employee values(103,'sco ',4000);

insert into employee values(104,'mar n',2000);

insert into employee values(102,'john',5000);

insert into employee values(105,'raju',3000);

to list the data or records from the table:

===========================================

syntax:

=======

select * from tablename;

ex:

===

select * from employee;

alter:

======

alter command is used to modifiy or alter the structure of the table.

a) using alter add a new column

-------------------------------

syntax:

-------

alter table tablename add columnName datatype;

ex:

---

Add column name is job to exis ng table employee.

sol:

====

alter table employee add job varchar(20);

ex2:

===

add mu ple columns address,mobilno columns to employee;

sol:

====

alter table employee add (mobileNo int(10),address varchar(20));

b)using alter modifying exis ng column:

----------------------------------------

syntax:

-------

alter table tablename modify colunname datatype;

ex:
===

modify the size of job column 20 to 10 size.

sol:

=====

alter table employee modify job varchar(10);

ex:

===

modify the size of address column 20 to 30 size.

sol:

----

alter table employee modify address varchar(30);

ex:

---

change mobileno datatype int to decimal

sol:

----

alter table employee modify mobileNo decimal(10);

note:

=====

if want to decrease the size or change the datatype of column

firt we need to make column should contain null values.

c)using alter we can drop a column

===================================

syntax:

-------

alter table tablename drop columname;

ex:

===

drop a column address using alter from employee table

sol:

----

alter table employee drop address;

d)using alter we can rename a columnanme:

==========================================

syntax:

--------

alter table tablename rename column oldcolname to new colname;

ex:

---

rename columname sal to salary in employee table

sol:
---

alter table employee rename column sal to salary;

e)using alter we can add a constraint:

======================================

syntax:

-------

alter table tablename add constraint constraintname column;

3)rename at DDL:

==============

rename a object

syntax:

=======

rename table oldtablename to newtablename;

ex:

===

rename table name employee to employees;

sol:

---
rename table employee to employees;

4) truncate :

=============

truncate command is used to remove or delete all the records or rows or tuples from a table.

note:

====

once table is truncated we cannot restore those delete records.

syntax:

-------

truncate table tablename;

ex:

---

truncate a table employees;

sol:

----

truncate table employee;

5)drop

======

drop command is used to drop a objects(table,func on,procedure...etc);

note:
-----

drop command will remove the strucutre and data from the database.

syntax:

========

drop table tablename;

ex:

---

drop a table named as employees;

sol:

----

drop table employees;

DML:

====

Dml is used to perform manipula on(insert,update,delete) on the table data.

insert:

=======

using insert command we can insert a row or record into table.

1)insert record for the all the columns:

=========================================
a) create table named as employees(empno,ename,job,sal,hiredate); date format:- 'yyyy-mm-dd'

b) insert five records into employees table for all the columns.

sol:

====

create table employees(empno int(4),ename varchar(20),job varchar(15),sal decimal(10,2),hiredate

date);

insert into employees values(7547,'smith','salesman',3000,'2018-10-02');

insert into employees values(7566,'mar n','accounts',4000,'2017-01-08');

insert into employees values(7544,'john','hr',5000,'2018-01-10');

insert into employees values(7565,'rahul','research',5000,'2015-09-21');

insert into employees values(7556,'roja','clerk',4000,'2014-02-02');

2)insert the record into a table for specific columns:

------------------------------------------------------

syntax:

=======

insert into tablename (col1,col2,col3,col4) values (value1,value2,value3,value4);

ex:

===

insert 3 record into a table for specific columns(empno,ename,sal) of employees

sol:

====

insert into employees(empno,ename,sal) values (7856,'venkat',6000);

insert into employees(empno,ename,sal) values (7968,'srikar',5000);

insert into employees(empno,ename,sal) values (7868,'sumit',4000);

3)insert mul ple records :

============================

syntax:

-------

insert into tablename values

(value1,value2,value3,value4),

(value1,value2,value3,value4),

(value1,value2,value3,value4),

(value1,value2,value3,value4),

(value1,value2,value3,value4);
ex:

===

insert mul ple records into employees table

sol:

----

insert into employees values

(7187,'madhu','Trainee',4000,'2020-12-10'),

(7154,'suhas','manager',6000,'2009-09-09'),

(7787,'balu','analyist',9000,'2018-10-02');

Update:

=======

update is used to update the exis ng record data from the table.

syntax:

=======

update tablename set colname1=value1,colname2=value2,colname=value3

[where condi on]

note:

====

If where condi on is not specified then all the records will be

updated in the table.

Ex:

===

update sal column(3000 to 5000) of employees record where empno 7547

sol:

====

update employees set sal=5000 where empno=7547;

Ex

---

write a query to update job,ename columns of employees table where sal is 5000;

sol:-

=====

update employees set ename='AAA',job='consultant' where sal=5000;

Ex:

===

write a query to update sal with bonus 100 for all the employees;

sol:

----

update employees set sal=sal+1000;

delete:
=======

delete command is used to delete a specific records based on condi on or

we can delete all the records with out condi on.

syntax:

-------

delete from tablename [where condi on];

note:

=====

if where condi on is not specified all the records will be deleted.

ex:

===

write a query to delete a record from employees table where sal is 10000;

sol:

----

delete from employees where sal=10000;

ex:

===

write a query to delete a records from employees table where sal is 6000;

sol:

----

delete from employees where sal=6000;

DRL:

====

cmd: select

select command is used to display all columns or specific columns data from a table.

syntax:

-------

select * [specific columns] from tablename [where condi on]

ex1:

====

write a query to all the records from the employees table?

sol:

----

select * from employees;

ex2:

====

write a query to display empno,enmae,sal from employees table ?

sol:

----

select empno,ename,sal from employees;

ex3:

----

write a query to display records where sal is 5000 to 7000 ?

sol:

----

select * from employees where sal>=5000 and sal<=7000;

Insert into select:

===================

inser into select statement copies the data from one table and

inserts into antoher table

note:

-----

In insert into select statement that source data types and target

data types should be same.

sytntax:

--------

insert into tgt_tablename select *[specific columns ] from src_tablename

[where condi on]

ex1:
===

copy the empno,ename columns data from employees table and insert

into student(sid,sname);

sol:

====

create table student(sid int(4),sname varchar(20));

insert into student select empno,ename from employees ;

or

insert into student(sid,sname) select empno,ename from employees;

to display the current sysdate

==============================

select current_date();

using current_date() in insert command:

=======================================

insert into employees values (7777,'rakesh','clerk',4000,current_date())

copy table columns schema but not data:

======================================

create table tablename as select * [specific columns ] from table where 1=2;
ex1:

===

create a table named as emp11 as copy structure of empno,ename,sal with out data.

sol:

====

create table emp11 as select empno,ename,sal from employees where 1=2;

copy table columns schema with data:

======================================

create table tablename as select * [specific columns ] from table;

ex1:

===

create a table named as emp12 as copy structure and data of empno,ename,sal

with data.

sol:

====

create table emp12 as select empno,ename,sal from employees;

CONSTRAINTS:

============

MySQL CONSTRAINT is used to define rules to allow or restrict what values can be stored in columns.

The purpose of inducing constraints is to enforce the integrity of a database.

MySQL CONSTRAINTS are used to limit the type of data that can be inserted into a table.

MySQL CONSTRAINTS can be classified into two types - column level and table level.

The column level constraints can apply only to one column where as table level constraints are
applied

to the en re table.

MySQL CONSTRAINTs are :

-----------------------

NOT NULL

UNIQUE

PRIMARY KEY

FOREIGN KEY

CHECK

DEFAULT

NOT NULL:

---------

In MySQL NOT NULL constraint allows to specify that a column can not contain any NULL value.

syntax:
-------

create table tablename (

colum1 datatype not null,

column2 datatype not null,

column3 datatype not null)

Ex:

---

create table named as ex1(empno,ename,sal) with not null constraints.

sol:

====

create table ex1(

empno int(4) not null,

ename varchar(20) not null,

sal decimal(10,2) not null);

mysql> insert into ex1 values(1,null,3000);

ERROR 1048 (23000): Column 'ename' cannot be null

DEFAULT:

--------

It ensures that the column sets a default value for empty records.

In a MySQL table, each column must contain a value ( including a NULL). While inser ng data into a
table,
if no value is supplied to a column, then the column gets the value set as DEFAULT.

syntax:

=======

create table tablename(

col1 datatype [null | not null] default value,

col2 datatype [null | not null] default value,

col3 datatype [null | not null] default value);

Ex:

---

create table named as ex2(sid,sname,marks) with not noll and default constraint for marks.

sol:

----

create table ex2(

sid int(4) not null,

sname varchar(10) not null,

marks int(4) not null default 0);

mysql> insert into ex2 values(1,'ss',30);

Query OK, 1 row affected (0.01 sec)

mysql> insert into ex2 values(1,'ss',null);

ERROR 1048 (23000): Column 'marks' cannot be null

mysql> insert into ex2 (sid,sname) values (2,'BB');

Query OK, 1 row affected (0.01 sec)

mysql> select * from ex2;

+-----+-------+-------+

| sid | sname | marks |

+-----+-------+-------+

| 1 | ss | 30 |

| 2 | BB | 0|

+-----+-------+-------+

2 rows in set (0.00 sec)

CHECK:

------

A CHECK constraint controls the values in the associated column.

The CHECK constraint determines whether the value is valid or not from a logical expression
(condi on).

syntax:

-------

check (condi on)

create table tablename(

col1 datatype [null | not null] default value,

col2 datatype [null | not null] check(condi on) ,

col3 datatype [null | not null] default value);

ex:

---

create a table named as ex3(sid,sname,age,address) with not null constraint and apply
check constraint for age column(age should be greater than 18)

sol:

----

colum-level

-----------

create table ex3(

sid int(4) not null,

sname varchar(10) not null,

age int(3) not null check(age>18),

address varchar(10) not null);

mysql> insert into ex3 values(1,'smith',19,'CHE');

Query OK, 1 row affected (0.01 sec)

mysql> insert into ex3 values(2,'smith',17,'CHE');

ERROR 3819 (HY000): Check constraint 'ex3_chk_1' is violated.

mysql>

table-level:

============

syntax;

------

create table tablename(

col1 datatype [null | not null],

col2 datatype [null | not null] ,

col3 datatype [null | not null] default value,

check(condi on) );
ex:

---

create a table named as ex4(empno,ename,sal,job,address) with check constraint for sal(sal>1500) at

table level

sol:

----

create table ex4(

empno int(4) not null,

ename varchar(10) not null,

sal int(5) not null,

job varchar(10) not null,

address varchar(10),

check(sal>1500));

mysql> insert into ex4 values(101,'john',1600,'clerk','boston');

Query OK, 1 row affected (0.01 sec)

mysql> insert into ex4 values(102,'mar n',1400,'accounts','ny');

ERROR 3819 (HY000): Check constraint 'ex4_chk_1' is violated.

UNIQUE:

-------

The UNIQUE constraint in MySQL does not allow to insert a duplicate value in a column.

The UNIQUE constraint maintains the uniqueness of a column in a table.

More than one UNIQUE column can be used in a table.

colum-level

-----------

create table tablename(

col1 datatype [null | not null] default value,

col2 datatype [null | not null] unique,

col3 datatype [null | not null] default value);

ex:

---

create table named as ex5(empno,ename,sal) with not null constraint for ename,sal and unique
constraint

for empno

sol:

----

create table ex5(

empno int(5) unique,

ename varchar(10) not null,

sal int(5) not null);

mysql> insert into ex5 values(1,'rohan',4000);

Query OK, 1 row affected (0.01 sec)

mysql> insert into ex5 values(1,'ew',5000);

ERROR 1062 (23000): Duplicate entry '1' for key 'empno'

mysql> insert into ex5 values(null,'ew',5000);

Table-level

-----------

create table tablename(

col1 datatype [null | not null],

col2 datatype [null | not null],

col3 datatype [null | not null],

unique(columname),

);

ex:

---

create table ex6( empno int(4),

ename varchar(10) not null,

sal int(5) not null,

job varchar(10) not null,

unique(empno));

mysql> insert into ex6 values(1,'ew',5000,'hr');

Query OK, 1 row affected (0.01 sec)

mysql> insert into ex6 values(1,'ew',5000,'hr');

ERROR 1062 (23000): Duplicate entry '1' for key 'empno'

PRIMARY KEY:
------------

It uniquely iden fies a row in the table. It is a combina on of NOT NULL and UNIQUE constraints

A PRIMARY KEY constraint for a table enforces the table to accept unique data for a specific column.

Note:

-----

A Table Should contain only one primary key.

syntax:

-------

colum-level

create table tablename(

col1 datatype primary key,

col2 datatype not null

col3 datatype not null);

ex:

---

create a table named as ex7(empno,ename,sal)with primary key (empno)

sol:

----

create table ex7( empno int(4) primary key,

ename varchar(10) not null,

sal int(5));

mysql> insert into ex7 values(1,'ashok',300);

ERROR 1062 (23000): Duplicate entry '1' for key 'Primay'

mysql> insert into ex7 values(null,'ashok',300);

ERROR 1048 (23000): Column 'empno' cannot be null

Table-level:

-------------

create table tablename(

col1 datatype ,

col2 datatype not null,

col3 datatype not null,

primary key(columnname),

);

ex:

---

create table ex8( empno int(4),

ename varchar(10),

sal int(5),

primary key(empno));

mysql> insert into ex8 values(1,'ashok',300);

Query OK, 1 row affected (0.01 sec)

mysql> insert into ex8 values(1,'ashok',300);

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

mysql> insert into ex8 values(null,'ashok',300);

ERROR 1048 (23000): Column 'empno' cannot be null

mysql>
FOREIGN KEY:

------------

A FOREIGN KEY in MySQL creates a link between two tables by one specific column of both tables.

The specified column in one table must be a PRIMARY KEY and

referred by the column of another table known as FOREIGN KEY.

we can add foreign key for specified column using References keyword.

syntax:

-------

column-level

------------

create table tablename(col1 datatype,

col2 datatype,

col 3 datatype,

specified_column datatype references parent_table(primary key column

name));

specified_column datatype references parent_table(primary key column name));

ex:

---

create paren able named as dept_1(deptno,dname,loc) and here deptno is primary key

create child table named as emp_1(empno,ename,sal deptno) here-deptno is specified column

and empno is pk ,link between two tables dept_1,emp_1 using specified column(deptno);

sol:
---

create table dept_1(

deptno int(3) primary key,

dname varchar(10),

loc varchar(10)

);

create table emp_1(

empno int(4) primary key,

ename varchar(10),

sal int(5),

deptno int(3) references dept_1(deptno));

first insert into parent table followed by child table.

select COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME,

REFERENCED_TABLE_NAME

from informa on_schema.KEY_COLUMN_USAGE

where TABLE_NAME = 'EMP_1';

Ex:

---

table-level

-----------

syntax:

-------

create table tablename(col1 datatype,

col2 datatype,

col 3 datatype,
 specifiedcolumn datatype,

foreign key(specified_column) REFERENCES parent_table(Specified_column-pk);

create a table name is course(cid,cname,fee) -->cid act as pk

create a table name is student_1(sid,sname,address,cid)--> sid is pk and cid

link two tables using a specified column(cid).

sol:

----

create table course(

cid int(4),

cname varchar(10),

fees int(4),

primary key(cid));

child table:

-------------

create table student_1(

sid int(4),

sname varchar(10),

address varchar(10),

cid int(4),

primary key(sid),

foreign key(cid) REFERENCES course(cid));

to display all constraints from STUDENT_1 table

================================================

select COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME,

REFERENCED_TABLE_NAME

from informa on_schema.KEY_COLUMN_USAGE

where TABLE_NAME = 'STUDENT_1';

first insert the records into parent table followed by child table.

ON DELETE CASCADE:

==================

IF TRY TO DELETE A RECORD FROM PARENT TABLE IF

THEIR EXISTS DEPENDENT RECORD (CHILD RECORD),WE WILL GET

ERROR

TO RESOLVE THIS ERROR

----------------------

WE NEED TO CREATE A CHILD TABLE WITH FOREIGN KEY FOLLOWED BY

ON DELETE CASCADE CONSTRAINT.

Ex:

---

create table named as product_1(pid,pname,price) --> pid is pk

create table named as customer_1(cid,cname,address,pid)--> cid is pk and pid is

link two tables using specified column(pid) with on delete cascade;

sol:

====

create table product_1(pid int(4) primary key,

pname varchar(10),

price decimal(10,2));

child table with on delete cascade:

-----------------------------------

create table customer_1(

cid int(4) primary key,

cname varchar(10),

caddress varchar(10),

pid int(4),

foreign key(pid) REFERENCES product_1(pid) on delete cascade);

first insert the records into parent table followed by child table.

delete a record from parent check it record is delete or not ? yes deleted

To display all constraints on a table, you can try any of the following methods

----------------------------------------------------------------------------------

Method 1 − Using SHOW command

------------------------------

You can check with the help of show command.

The syntax is as follows

------------------------

SHOW CREATE TABLE yourTableName;

Method 2 − Using informa on.schema

------------------------------------

you can use informa on.schema.

The syntax is as follows

------------------------

select COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME,

REFERENCED_TABLE_NAME

from informa on_schema.KEY_COLUMN_USAGE

where TABLE_NAME = 'yourTableName';

ex:

====

select COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME,

REFERENCED_TABLE_NAME

from informa on_schema.KEY_COLUMN_USAGE

where TABLE_NAME = 'EMP_1';

Alter Level:

============

ex:
---

create table emp_dup(empno int(4),ename varchar(10),sal int(5));

adding primary key for empno at alter level:

-----------------------------------------

alter table emp_dup add primary key(empno);

adding check constraint for sal alter level:

--------------------------------------------

alter table emp_dup add check(sal>1500);

adding unique constraint at alter level:

=========================================

syntax:

-------

alter table tablename add unique(columname);

adding foreign key constraint at alter level:

==============================================

syntax:

-------

alter table tablename add foreign key(specifiedcolumn) REFERENCES parent_table(specified-column-

pk);

note:

-----

Adding not null and default constraint at alter level and table level are not possibe.
select COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME,
REFERENCED_TABLE_NAME

from informa on_schema.KEY_COLUMN_USAGE

where TABLE_NAME = 'EMP_DUP';

to drop a constraint a column:

==============================

alter table tablename drop constraint_name;

alter table emp_dup drop PRIMARY key;

Mysql Aliases:

==============

Mysql aliases can be used to create a temporary name for columns or tables.

syntax for columns:

-------------------

column_name [As] alias_name

syntax for tables:

==================

table_name [as] alias_name

ex:

----

mysql> select current_date() as 'todayDate' ;

mysql> select current_date() 'todayDate' ;

mysql> select current_date() todayDate ;

+------------+

| todayDate |

+------------+

| 2021-03-03 |

+------------+

ex:

---

mysql> select e.ename,e.sal from employees as e;

mysql> select e.ename,e.sal from employees e;

Mysql Aliases:

==============

Mysql aliases can be used to create a temporary name for columns or tables.

syntax for columns:

-------------------

column_name [As] alias_name

syntax for tables:

==================

table_name [as] alias_name

ex:

----

mysql> select current_date() as 'todayDate' ;

mysql> select current_date() 'todayDate' ;

mysql> select current_date() todayDate ;

+------------+

| todayDate |

+------------+

| 2021-03-03 |

+------------+

ex:

---

mysql> select e.ename,e.sal from employees as e;

mysql> select e.ename,e.sal from employees e;

Scrip ile:

===========
create a file , specify group of mysql commands and save the file with filename.sql

to run script file:

===================

source <absoultepathof the scrip ile.sql>

Ex:

===

create a file named as mysqlscript.sql

and add the following below commands

1) create a table named as perosns(person_id,person_name,address);

2) describe table persons

3) insert mul ple records into persons

4) display the records from the persons table.

to run :

--------

source mysqlscript.sql

JOINS:

======

Join are used to retrieve the records from mul ple tables .
join are used to create temporary a rela on between the tables

based on common column.

if suppose we have n tables ,then the atleast the possible

join condi ons are n-1.

join are performed based on a common column datatype.

Mysql supports following the types of joins

-------------------------------------------

Inner join

Le join

Right join

Full outer join

Self-join

Cross join

InnerJoin or equi-join:

-----------------------

Inner joins fetch or return the matched records from the both tables.

syntax:

--------

select specific_columns from table1 alias name inner join table2 aliasename

on(table1.common_column_name=table2.common_column_name);
Ex:

---

write a inner join or equi-join to fetch matched records from dept,emp tables.

dept(deptno,dname,loc)

emp(empno,ename,sal,job,deptno)

sol:

---

select d.deptno,d.dname,d.loc,e.empno,e.ename,e.sal,e.job,e.deptno

from dept d inner join emp e on(d.deptno=e.deptno);

le Join:

---------

le outer join fetches the matched records from both tables and

non-matched records from the le table.

syntax:

------

select specific_columns from table1 alias name le [outer] join table2 aliasename

on(table1.common_column_name=table2.common_column_name);

note:

----

Here table1 act as le table.

Ex:

---
write a join to fetch matched records from dept,emp tables and non-matched records from dept.

dept(deptno,dname,loc)

emp(empno,ename,sal,deptno)

sol:

---

select d.deptno,d.dname,d.loc,e.empno,e.ename,e.sal,e.deptno

from dept d le join emp e on(d.deptno=e.deptno);

Right Join:

---------

Right outer join fetches the matched records from both tables and

non-matched records from the Right table.

syntax:

------

select specific_columns from table1 alias name Right [outer] join table2 aliasename

on(table1.common_column_name=table2.common_column_name);

note:

----

Here table2 act as right table.

Ex:

---

write a join to fetch matched records from dept,emp tables and non-matched records from emp.

dept(deptno,dname,loc)

emp(empno,ename,sal,deptno)

sol:
---

select d.deptno,d.dname,d.loc,e.empno,e.ename,e.sal,e.deptno

from dept d Right join emp e on(d.deptno=e.deptno);

Full outer Join:

---------------

Full outer join fetches the matched records and

non-matched records from the both tables.

syntax:

------

select specific_columns from table1 alias name Full [outer] join table2 aliasename

on(table1.common_column_name=table2.common_column_name);

Ex:

---

write a join to fetch matched records and non-matched records from dept,emp tables.

emp(empno,ename,sal,deptno)

sol:

---

select d.deptno,d.dname,d.loc,e.empno,e.ename,e.sal,e.deptno

from dept d full join emp e on(d.deptno=e.deptno);

--we can perform full outer join by union using le join and right join

------------------------------------------------------------------------

select d.deptno,d.dname,d.loc,e.empno,e.ename,e.sal,e.deptno

from dept d le join emp e on(d.deptno=e.deptno)

 union

select d.deptno,d.dname,d.loc,e.empno,e.ename,e.sal,e.deptno

from dept d Right join emp e on(d.deptno=e.deptno);

1)Write a query to get empno,ename,sal from emp

deptno,dname,loc from dept by using selec on criteria display the deptno 20 decords.

sol:

----

select e.empno,e.ename,e.sal,d.deptno,d.dname,d.loc from dept d join emp e

on(d.deptno=e.deptno) and d.deptno=20;

or

select e.empno,e.ename,e.sal,d.deptno,d.dname,d.loc from dept d,emp e

where d.depnto=e.deptno and d.deptno=20;

self Join:

---------

A self join is a join that is used to join a table with itself.

note:

----

we need to create two aliasname on the same table.

ex:

---

write a query to display employeenames and its managers from emp table using self join?

sol:

----

select e.ename,m.ename as manager from emp e ,emp m where m.empno=e.mgr;

cross Join:

-----------

It will perform cartesian product.

ex:

==

write query to perform cross join between dept and emp ?

dept--5records

emp -- 15 records

cross join -- 5*15 = 75 records

sol:

----
select d.deptno,d.dname,d.loc,e.empno,e.ename,e.sal from dept d cross join emp e;

or

select d.deptno,d.dname,d.loc,e.empno,e.ename,e.sal from dept d,emp e;

NATURAL JOIN:

=============

NATURAL JOIN IS SIMILAR TO INNER JOIN,

BUT THE CONDITION IS PERFORMED IMPLICITY BASED ON COLUMN NAME.

EX:

===

SELECT E.ENAME,D.DNAME FROM

DEPT D NATURAL JOIN EMP E;

NOTE:

=====

IN NATURAL JOIN THE COMMON COLUMN NAME BETWEEN THE TWO TABLE

MUST BE SAME. IF NOT IT WILL PERFORM CROSS JOIN

NATURAL JOIN IS DEPENDENT ON COLUMN NAME .

NON-EQUI JOIN:
==============

=,!= OTHER THAN THESE OPERATORS(like between,in,...)

SALGRADE Table

------------------

CREATE TABLE SALGRADE (GRADE int,LOSAL int,HISAL int);

INSERT INTO SALGRADE VALUES (1, 700, 1200);

INSERT INTO SALGRADE VALUES (2, 1201, 1400);

INSERT INTO SALGRADE VALUES (3, 1401, 2000);

INSERT INTO SALGRADE VALUES (4, 2001, 3000);

INSERT INTO SALGRADE VALUES (5, 3001, 9999);

1)WRITE A NON-EQUI JOIN QUERY

TO GET EMPNO,ENAME,SAL FROM EMP

GRADE FROM SAL GRADE

AND DISPLAY SALARIES BASED ON E.SAL COLUMN BETWEEN

THE RANGE OF LOSAL AND HISAL COLUMNS OF SALGRADE TABLE.

SOL:

=====

SELECT E.EMPNO,E.ENAME,E.SAL,S.GRADE

FROM EMP E,SALGRADE S WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;

Operators in MySQL

-------------------

Operators are used to specifying a condi on in a statement in MySQL.

Below are the different types of operators used in MySQL.

1. Arithme c Operators

------------------------

In MySQL, arithme c operators are used to perform the arithme c opera ons as described below.

Arithme c Operators in MySQL

-----------------------------

Operator Descrip on
Example

+ Addi on of two operands a+

b

– Subtrac on of right operand from the le operand a–

b

* Mul plica on of two operands a*

b

/ Division of le operand by the right operand a/

b

% Modulus – the remainder of the division of le operand by the right a%

b

Comparison Operators or rela on operators

------------------------------------------

The comparison operators in MySql are used to compare values between operands and
return true or false according to the condi on specified in the statement.

Comparison Operators in MySQL

-----------------------------

Operator Descrip on

-------- ------------

> If the value of le operand is greater than that of the value of the right operand,

the condi on becomes true; if not then false.

< If the value of le operand is less than that of a value of the right operand,

the condi on becomes true; if not then false.

= If both the operands have equal value, the condi on becomes true; if not then false.

!= If both the operands do not have equal value, the condi on becomes true; if not then false.

>= If the value of le operand is greater than or equal to the right operand, the condi on
becomes true;

if not then false.

<= If the value of le operand is less than or equal to the right operand, the condi on becomes
true;

if not then false.

<> If the values of two operands are not equal, the condi on becomes true; if not then false.

BETWEEN RETURNS THE VALUE ,THE VALUE SPECIFIED IN THE RANGE.

(INCLUSIVE)
IN RETURNS THE VALUE ,THE VALUE AVAILABLE IN THE

GIVEN LIST OF VALUES.

Ex:

---

SELECT * FROM EMP WHERE SAL > 4000;

SELECT * FROM EMP WHERE SAL = 3000;

SELECT * FROM EMP WHERE SAL < 3000;

SELECT * FROM EMP WHERE SAL !=3000;

SELECT * FROM EMP WHERE SAL <>3000; -- not equal

select * from emp where sal in(2000,3000,4000);

Logical operators

-----------------

logical operators we perform boolean opera ons.

MySQL understands these logical operators:

AND, OR and NOT

Logical operators return TRUE or FALSE. In MySQL, 1 is true, 0 is false.

Ex:

---

1)The AND operator evaluates to true if both operands are true.

mysql> SELECT FALSE AND FALSE, FALSE AND TRUE,TRUE AND FALSE, TRUE AND TRUE;

2)The OR operator evaluates to true if at least one of the operands is true.

mysql> SELECT FALSE OR FALSE, FALSE OR TRUE, TRUE OR FALSE, TRUE OR TRUE;

3)The NOT operator is nega on operator. It makes true false and false true.

mysql> SELECT NOT TRUE, NOT FALSE;

mysql> SELECT NOT (3=3);

Ex:

---

1)WRITE A QUERY TO DISPLAY RECORD WITH SPECIFIED RANGE(between oper)

SAL 2000 AND 3000?

sol:-

select * from emp where sal between 2000 and 3000;

select * from emp where sal>=2000 and sal<=3000;

2)WRITE A QUERY TO DISPLAY THE RECORDS BASED ON IN operator WHERE

SAL 1000,2000,3000 ON EMP TABLE.

select * from emp where sal in(1000,2000,3000);

or

select * from emp where sal=1000 or sal=2000 or sal=3000;

LIKE OPERATOR:

==============

IT WILL RETRUN THE VALUE BASED ON SEARCH OF PATTERN

THERE ARE TWO PATTERNS

% --PERCENATAGE-- REPRESENT ONE OR MORE CHARACTERS

_ ---UNDERSCORE -- REPRESENT SINGLE CHARACTER

SYNTAX:

=======

LIKE 'PATTERN'

EX:

===
WRITE A QUERY TO DISPALY ALL ENAMES FROM EMP

WHERE ENAME START WITH S.

SOL:

====

SELECT ENAME FROM EMP WHERE ENAME LIKE 'S%';

ex:

===

write a query to display all enames start with

sol:

=====

select ename from emp where ename like 'A_L%';

ORDER BY CLAUSE:

================

THIS CLAUSE IS USED TO SORT THE OUTPUT STATEMENT BASED ON SELECT

QUERY.

BY DEFAULT IT SORT ASCENDING OR ASC

DESCENDING

-----------

DESC

SYNTAX:

========

SELECT * FROM TABLENAME [WHERE] [ORDER BY COLUMNANME...];

AGGREGATE FUNCTION or single row func ons

===========================================

An aggregate func on performs a calcula on on mul ple values

and returns a single value.

ex:

---

SUM

AVG

MIN

MAX

COUNT

DISTINCT -- TO REMOVE DUPLICATE VALUES

GROUP BY Clause

---------------

GROUP BY Clause is used to collect data from mul ple records and group the result

by one or more column.

It is generally used in a SELECT statement.

 You can also use some aggregate func ons like COUNT, SUM, MIN, MAX, AVG etc. on the grouped
column.

SYNTAX:

=======

SELECT exp1,expr2,aggregate_func on(expr) FROM TABLNAME [WHERE][GROUP BY

exp1,expr2][ORDER BY COLUMNS];

note:

----

The expressions that are not encapsulated within an aggregate func on and must be included in the
GROUP BY clause.

EX

===

1)WRITE A QUERY TO PERFORM SUM OF SALARIES BASED ON for EACH

DEPARTMENT NO FROM EMP TABLE.

sol:

----

select Deptno,sum(sal) from emp group by deptno;

or

select Deptno,sum(sal) from emp group by deptno order by deptno;

2) WRITE A QUERY TO PERFROM SUM OF SALARIES BASED ON for EACH

DEPARMTENT NO AND COUNT NO EMPLOYESS IN EACH DEPT FROM EMP.

 sol:

----

select deptno,count(ename),sum(sal) from emp group by deptno order by deptno;

3)WRITE A QUERY TO PEFROM SUM OF SALARIES BASED ON

for EACH DEPTNO AND for EACH JOB .

sol:

----

select deptno,job,sum(sal),count(ename) from emp group by deptno,job order by deptno;

HAVING Clause

-------------

HAVING Clause is used with GROUP BY clause. It always returns the rows where condi on is TRUE.

The HAVING clause is used in the SELECT statement to specify filter condi ons for a group of rows
or aggregates.

The HAVING clause is o en used with the GROUP BY clause to filter groups based on a specified
condi on.

If the GROUP BY clause is omi ed, the HAVING clause behaves like the WHERE clause.

syntax:

-------

SELECT exp1,expr2,aggregate_func on(expr) FROM TABLNAME [WHERE][GROUP BY exp1,expr2]

[Having condi on][ORDER BY

COLUMNS];
Ex;

===

Write a query to display sum(sal) of for each dept sum(sal)greater 8750?

sol:

---

select deptno,sum(sal) as ssal from emp group by deptno having sum(sal)>8750;

or

select deptno,sum(sal) as ssal from emp group by deptno having ssal>8750 ;

String Func ons:

------------------

MySQL string func ons that allow you to manipulate character string data effec vely.

1. ASCII(str)

--------------

Returns the ASCII value of the le most character of the string str.

Ex:

--

mysql> select ascii('a');

+------------+

| ascii('a') |

+------------+

| 97 |
+------------+

1 row in set (0.00 sec)

mysql> select ascii('A');

+------------+

| ascii('A') |

+------------+

| 65 |

+------------+

1 row in set (0.00 sec)

Ex:

---

mysql> select ascii(ename) from emp;

+--------------+

| ascii(ename) |

+--------------+

| 83 |

| 65 |

| 87 |

| 74 |

| 77 |

| 66 |

| 67 |

| 83 |

| 75 |

| 84 |

| 65 |

| 74 |

| 70 |

| 77 |
+--------------+

2.CHAR_LENGTH(str)

------------------

Returns the length of the string str in characters.

ex:

----

mysql> select char_length('welcome');

+------------------------+

| char_length('welcome') |

+------------------------+

| 7|

+------------------------+

1 row in set (0.00 sec)

ex:

----

select ename,char_length(ename) as "len" from emp;

3.CONCAT(str1, str2, …., strn)

------------------------------

Returns a string formed by joining str1 to strn. If any sub-string is NULL, the result is NULL.

ex:

---

mysql> select concat('hello','hi','welcome') as concat;

mysql> select concat('hello ',ename) from emp;

4.CONCAT_WS(separator, str1, …, strn)

--------------------------------------

Returns a concatenated string separated by a separator.

Ex:

---

mysql> select concat_ws('#','hello','hi','welcome') as concat_ws;

+------------------+

| concat_ws |

+------------------+

| hello#hi#welcome |

+------------------+

Ex:

---

mysql> select concat_ws('$','hello',ename) as concatws from emp;

5.INSERT(str, pos, len, newstr)

--------------------------------

Replaces the sub-string star ng at posi on pos and of length len in the string str with newstr.

Ex:

---
mysql> select insert('welcome','3','3','aaa');

+---------------------------------+

| insert('welcome','3','3','aaa') |

+---------------------------------+

| weaaame |

+---------------------------------+

Ex:

---

mysql> select ename,insert(ename,3,3,'hii') from emp;

smith,3,3,'hii' --smhii

6.INSTR(str, substr) / LOCATE(substr, str) / POSITION(substr IN str)

---------------------------------------------------------------------

Returns the posi on of the first occurrence of substr in str.

Ex:

---

mysql> select instr('foobar','oo');

7.LCASE() / LOWER() and UCASE() / UPPER()

-----------------------------------------

Converts a string to lower case and upper case.

Ex:

===
mysql> select lcase('HELLO');

mysql> select lower('HELLO')

mysql> select ucase('sdsdsd');

mysql> select upper('sdsdsd');

write a query to dislay the ename in lower case from emp table

select lower(ename) from emp;

8.LEFT(str, len) / RIGHT(str, len)

----------------------------------

Returns the le most and rightmost len characters from the string str.

Ex:

--

mysql> select le ('hello',3);

+-----------------+

| le ('hello',3) |

+-----------------+

| hel |

+-----------------+

1 row in set (0.00 sec)

mysql> select right('hello',3);

+------------------+

| right('hello',3) |

+------------------+
| llo |

+------------------+

1 row in set (0.00 sec)

select le (ename,3) from emp;

select right(ename,3) from emp;

9.LENGTH(str) / OCTET_LENGTH(str)

---------------------------------

Returns the length of the string str in bytes.

This is very much like CHAR_LENGTH func on.

Ex:

===

mysql> select length('welcome');

mysql> select ename,length(ename),char_length(ename) from emp;

LTRIM(str) / RTRIM(str) / TRIM(str)

--------------------------------------

Returns the string str a er trimming white spaces from le , right or both ends.

Ex;

---

mysql> select ltrim(' hello');

mysql> select rtrim('hello ');

mysql> select trim(' hello ');

12.QUOTE(str)

-------------

This query quotes and un-quotes the string str.

Special characters are escaped.

mysql> select quote('welcome$hello');

+------------------------+

| quote('welcome$hello') |

+------------------------+

| 'welcome$hello' |

+------------------------+

1 row in set (0.00 sec)

mysql> select quote('welcome\n hello');

+--------------------------+

| quote('welcome\n hello') |

+--------------------------+

| 'welcome

hello' |

+--------------------------+

select quote(ename) from emp;

13.REPLACE(str, from_str, to_str)

-----------------------------------

Replaces all occurrences of sub-string from_str with sub-string to_str in the string str.

It is case-sensi ve.

Ex:

---

mysql> select replace('welcome','el','hi');

+------------------------------+

| replace('welcome','el','hi') |

+------------------------------+

| whicome |

+------------------------------+

1 row in set (0.00 sec)

Ex:

---

mysql> select ename,replace(ename,'S','A') from emp;

14.REVERSE(str)

---------------

Reverses the string str.

Ex:

---

mysql> select reverse('hello') ;

mysql> select ename,reverse(ename) from emp;

15.SUBSTR(str, pos)/SUBSTRING(str, pos)/SUBSTR(str, pos, len)/SUBSTRING(str, pos, len)

--------------------------------------------------------------------------------------

Returns a substring from the string str star ng from posi on pos,

ll the end if no len is specified or for len characters otherwise.

Remember, Index starts from 1 in MySQL

Ex:

==

mysql> select substr('hello',2),substring('hello',2);

+-------------------+----------------------+

| substr('hello',2) | substring('hello',2) |

+-------------------+----------------------+

| ello | ello |

+-------------------+----------------------+

1 row in set (0.01 sec)

mysql> select substr('hello',2,2),substring('hello',2,2);

+---------------------+------------------------+

| substr('hello',2,2) | substring('hello',2,2) |

+---------------------+------------------------+

| el | el |

+---------------------+------------------------+

1 row in set (0.00 sec)

Ex:

---

mysql> select substr('welcome',-4,3);

+------------------------+

| substr('welcome',-4,3) |

+------------------------+

| com |

+------------------------+

1 row in set (0.00 sec)

NUMBER FUNCTIONS:

=================

ABS -- IT WILL DISPLAY ABSLOUTE VALUE INTEGER.

SYNTAX:

=======

ABS(NUM)

EX:

===

SELECT ABS(5),ABS(-5),ABS(NULL);

OLP:-- 5,5,

SQRT:

=====

IT WILL RETRUN SQRT ROOT VALUE OF A NUMBER.

SYNTAX:

========

SQRT(NUM);

EX:

===

SELECT SQRT(4),SQRT(9),SQRT(5),SQRT(NULL);

OLP:-- 2,3,2.36,

MOD:

====

THIS FUNCTION RETRUN REMAINDER.

SYNTAX

======

MOD(VALUE,DIVISON);

EX:

===

SELECT MOD(4,2),MOD(5,2);

O/P:- 0,1

ifnull():

=========
THIS FUNCTION IS USED TO REPLACE THE NULL VALUES.

SYNTAX:

-------

ifNULL(colname,REPLACE VALUE)

EX:

===

WRITE A QUERY TO DISPLAY COMM COLUMN FROM EMP

AN REPLACE NULL VALUES WITH 0;

SOL:

====

SELECT ifnull(COMM,0) FROM EMP;

CEIL:

======

THIS FUNCTION ROUND THE VALUE TO not less than current value.

SYNTAX:

========

CEIL(NUM);

EX:

===

SELECT CEIL(4.5),CEIL(5),CEIL(4.2) ;
OLP:- 5,5,5

FLOOR:

======

THIS FUNCTION ROUND THE VALUE TO Lowest POSSIBLE VALUE

SYNTAX:

-------

FLOOR(NUM);

EX:

===

SELECT FLOOR(4.5),FLOOR(4.2),FLOOR(4.7);

O/P:- 4,4,4,

pow(numer,exponen al):

======================

it will return power of an number.

select pow(3,2),pow(2,3);

o/p:- 9,8

VIEWS:

======

A VIEW IS A LOGICAL REPRESENTATION OF DATA FROM ONE OR MORE THEN ONE TABLE.

A VIEW DOES NOT CONTAIN PHYSICAL STORAGE.

A VIEW IS VIRTUAL TABLE.

View does not contain any data.

When we write select statement on view, we get the data from the table for the first me.

Advantages of MySQL Views

-------------------------

MySQL views bring the following advantages.

1) Simplify complex query

Views help simplify complex queries. If you have any frequently used complex query, you can create
a view based on it so that you can reference the view by using a simple SELECT statement instead of
typing the query all over again.

2) Make the business logic consistent

Suppose you have to repeatedly write the same formula in every query. Or you have a query that
has complex business logic. To make this logic consistent across queries, you can use a view to store
the calcula on and hide the complexity.

3) Add extra security layers

A table may expose a lot of data including sensi ve data such as personal and banking informa on.

By using views and privileges, you can limit which data users can access by exposing only the
necessary data to them.

For example, the table employees may contain SSN and address informa on, which should be
accessible by the HR department only.

To expose general informa on such as first name, last name, and gender to the General
Administra on (GA) department, you can create a view based on these columns and grant the users
of the GA department the view, not the en re table employees .
TYPES OF VIEW:

==============

1)SIMPLE VIEW

2)COMPLEX VIEW

SIMPLE VIEW:

============

1) A VIEW IS CREATED BASED ON SINGLE TABLE.

2)IN SIMPLE VIEW DOESNOT CONTAIN FUNCTIONS OR GROUP BY CLAUSES.

3)WE CAN PERFORM DML OPERATION ON SIMPLE VIEW.

SYNTAX:

========

CREATE OR REPLACE VIEW VIEWNAME AS SELECT * [SPECIFIC COLUMNS]

FROM TABLENAME;

EX:

===

CREATE A VIEW ON EMP TABLE IT CONTAINS THREE COLUMNS

EMPNO,ENAME,SAL

SOL:

====

CREATE or replace VIEW EMP_V1 AS SELECT EMPNO,ENAME,SAL FROM EMP;

TO DISPLAY THE VIEW OUT PUT:

=============================

SELECT * FROM VIEWNAME;

ex:

====

select * from emp_v1;

To List all views from a database:

------------------------------------

SELECT

TABLE_SCHEMA,

TABLE_NAME,

TABLE_TYPE

FROM

informa on_schema.TABLES

WHERE

TABLE_SCHEMA LIKE '501a' AND TABLE_TYPE LIKE 'VIEW';

or

to display only the code from the view:

----------------------------------------

show create view viewName;

to drop a view:
===============

drop view viewname;

COMPLEX VIEW:

=============

A VIEW IS CREATED BASED ON MORE THAN ONE TABLE(JOINS).

A COMPLEX VIEW CAN CONTAIN FUNCTIONs OR GROUP BY CLAUSE.

A DML OPERATION PERFROMED ON COMPLEX VIEW INDIRECTLY.

SYNTAX:

=======

CREATE OR REPLACE VIEW VIEWNAME AS SELECT * FROM TWOTABLES;

EX:

===

CREATE COMPLEX VIEW TO STORE EQUI JOIN OUTPUT OF DEPT AND EMP TABLE.

SOL:

====

CREATE OR REPLACE VIEW EMP_DEPT_EQ AS

SELECT E.EMPNO,E.ENAME,E.SAL,D.DEPTNO,D.DNAME FROM

DEPT D JOIN EMP E ON (D.DEPTNO=E.DEPTNO);

Read Only View(MySQL WON'T SUPPORT):

====================================

We can restrict DML opera on views by crea ng read only view.

Ex:

Create or replace view v3 As select empno, ename, sal, deptno from emp with read only;

With Check Op on View::

====================

These views will allow DML opera on only when where condi on is sa sfied.

Ex:

Create or replace view Test_V12

As select empno, ename, sal, deptno from emp Where deptno = 30 With check op on;

ex:

insert into test_v12 values(7143,'RAJESH',5000,30) //valid

insert into test_v12 values(7323,'RAJESH',5000,10) //invalid

ALter a view:

--------------

Alter view viewName as select * from tableName;

ex:

-----

Alter view v1 as select empno,ename,job from emp;

Different between simple and complex views?

Simple view Complex view

1.Created by using only one table. 1. Created by using mul ple tables.

2.DML opera ons are allowed. 2. DML opera ons are not allowed.

3.Should not be created using 3. Can be created using arithme c arithme c opera ons or
func ons opera ons or func ons or or group by clauses.

group by clauses.

Exercises:

----------

salesman

---------

create below table.

salesman_id | name | city | commission

-------------+------------+----------+------------

5001 | James Hoog | New York | 0.15

5002 | Nail Knite | Paris | 0.13

5005 | Pit Alex | London | 0.11

5006 | Mc Lyon | Paris | 0.14

 5007 | Paul Adam | Rome | 0.13

5003 | Lauson Hen | San Jose | 0.12

create table salesman(salesman_id int,name varchar(20),city varchar(20),commission float)

insert into salesman values (5001,'James Hoog','New York',0.15),

(5002,'Nail Knite','Paris',0.13),

(5005,'Pit Alex','London',0.11),

(5006,'Mc Lyon','Paris',0.14), ;

(5007,'Paul Adam','Rome',0.13),(5003,'Lauson Hen','San Jose',0.15);

1. From the following table, create a view for those salespeople who belong to the city of New York.

2. From the following table, create a view for all salespersons. Return salesperson ID, name, and city.

3. From the following table, create a view to locate the salespeople in the city 'New York'.

4. From the following table, create a view to count the number of unique customers, compute the
average and the total purchase amount of customer orders by each date.

tables:

--------

Sample table: customer

customer_id | cust_name | city | grade | salesman_id

-------------+----------------+------------+-------+-------------

3002 | Nick Rimando | New York | 100 | 5001

3007 | Brad Davis | New York | 200 | 5001

3005 | Graham Zusi | California | 200 | 5002

 3008 | Julian Green | London | 300 | 5002

3004 | Fabian Johnson | Paris | 300 | 5006

3009 | Geoff Cameron | Berlin | 100 | 5003

3003 | Jozy Al dor | Moscow | 200 | 5007

3001 | Brad Guzan | London | | 5005

create table customer(customer_id int,cust_name varchar(20),city varchar(10),grade int,salesman_id

int);

insert into customer values(3002,'Nick Rimando','New York',100,5001);

insert into customer values(3007,'Brad Davis','New York',200 ,5001);

insert into customer values (3005,'Graham Zusi','California',200,5002);

insert into customer values (3008,'Julian Green',' London',300,5002);

insert into customer values (3004,'Fabian Johnson','Paris',300,5006);

insert into customer values (3009,'Geoff Cameron','Berlin',100,5003);

insert into customer values (3003,'Jozy Al dor','Moscow',200,5007);

insert into customer values (3001,' Brad Guzan','London',null,5005);

orders:

---------

ord_no purch_amt ord_date customer_id salesman_id

---------- ---------- ---------- ----------- -----------

70001 150.5 2012-10-05 3005 5002

70009 270.65 2012-09-10 3001 5005

70002 65.26 2012-10-05 3002 5001

70004 110.5 2012-08-17 3009 5003

70007 948.5 2012-09-10 3005 5002

70005 2400.6 2012-07-27 3007 5001

70008 5760 2012-09-10 3002 5001

sol:

----

create or replace view v_2 as select ord_date,count(dis nct(customer_id)) as uniqueCustomer,

avg(purch_amt) as avg,sum(purch_amt) as sum from orders group by ord_date ;

Subqueries:

------------

Subqueries () are used to get the result based on unknown values.

An SQL subquery is nothing but a query inside another query. We use a subquery to fetch data from
two tables. A subquery is o en also referred to as an inner query, while the statement containing a
subquery is also called an outer query or outer select.
There are some rules which are followed while using subqueries.

They are:

A subquery must be enclosed in parentheses.

Subqueries that return over one row can only be used with mul ple value operators such as the IN
,All or Any operator.

We can nest subqueries up to 32 levels.

They are different type.

1.Single Row subquery

2.Mul ple Row subquery

3.Mul ple column subquery

4.Co−related subquery

5.Scalar subquery

6.Inline view

*Single Row Subquery:

---------------------

When subquery returns one row (1 value). It is called Single RowSubquery.

Ex: write a query to display details are having salary > 'ALLENS' sal ?

Select * from emp where sal > (select sal from emp where ename = 'ALLEN');
internally execu on:

----------------------

select * from emp wher sal > 1600

Note:

-----

Subqueries are executed first and then parent query is executed by using the result of sub query.

Level Two query:

----------------

Select * from emp where job = (select job from emp where ename = 'ALLEN') AND job = (select job
from emp where ename = 'BLAKE');

0/p=0;

Select * from emp where job = (select job from emp where ename = 'ALLEN') AND sal = (select sal
from emp where ename = 'ALLEN');

Level Three query(nested sub-query):

------------------------------------

Select * from emp where sal > (select sal from emp Where ename = (select ename from emp Where
empno = 7499));

Note:

-----

The above query is three level query. Sub query can be nested upto 32 levels.
ALL

----

SELECT * FROM EMP WHERE SAL>ALL(1000,2000,3000);

OR

SELECT * FROM EMP WHERE SAL>1000 AND SAL>2000 AND SAL>3000;

ANY

----

SELECT * FROM EMP WHERE SAL>ANY(1000,2000,3000);

OR

SELECT * FROM EMP WHERE SAL>1000 OR SAL >2000 OR SAL>3000;

**Mul ple Row Subquery:

------------------------

When subquery returns mul ple rows. It is called mul ple row salary.

Note: we should use mul ple row operators with mul ple row subqueries.

They are three mul ple row operators.

1.IN

2.ANY

3.ALL
All:

----

Select * from emp Where sal > ALL(Select sal from emp Where deptno = 30);

Any:

-----

Select * from emp where sal > ANY(select sal from emp where deptno = 30);

internally execu on:

------------------------

select * from emp where sal > ANY(1600,1250,1250,2850);

*IN:

-----

Select * from emp where ename IN(select ename from emp where deptno = 30);

Select * from emp where sal IN(select sal from emp where deptno = 30);

*MULTIPLE COLUMN SUBQUERY:

--------------------------

When subquery return more then one column. It is called mul ple column subquery.

We should use in operator with mul ple column subqueries.

Select * from emp where(job,sal) IN(select job, sal from emp where deptno = 30);

Exercise:

---------

write a subquery to find second highest salary emp table.

sol:

----

select max(sal) from emp where sal < (select max(sal) from emp);

Co-RELATED SUBQUERY:

--------------------

When subquery is executed in rela on to parent query, it is called co−related subquery.

*write a query to display all the rows who are having salary greater than AVG salary his department?

Ex:

----

Select * from emp e where sal > (select AVG(sal) from emp where deptno = e.deptno);
Execu on:

----------

The above example is a co−related subquery.

In co−related subquery, parent query is executed first and then subquery is executed in rela on to
result of parent query(parenet table alias name).

SCALAR subquery:

---------------

when we use subquery in the select clause. It is called as Scalar subquery.

Scalar subquery are also called sub select.

write a query to display following output?

Deptno Dname Loc sumsal

10 Accoun ng New York 8750

20 Research Dallas 10875

30 Sales Chicago 14400

40 Opera ons Boston −−−−−−

Example:

--------

Select deptno, dname, loc, (Select sum(sal) from emp where deptno = d.deptno) Sum_sal from dept
d;
*from clause subquery or INLINE VIEW:

--------------------------------------

When a subquery is used in from clause. It is called INLINE view or inline subsquery.

Ex:

------

select dept.deptno, subquery1.total_amt from dept, ( select emp.deptno, Sum(emp.sal) total_amt

from emp group by deptno) subquery1 WHERE subquery1.deptno = dept.deptno;

Exists and Not Exists:

----------------------

Exists:

------

In the previous sec on, we used IN to link the inner query and the outer query in a
subquerystatement.

IN is not the only way to do so -- one can use many operators such as >, <, or =.
EXISTS is a special operator that we will discuss in this sec on.

EXISTS simply tests whether the inner query returns any row. If it does, then the outer query
proceeds. If not, the outer query does not execute, and the en re SQL statement returns nothing.

The syntax for EXISTS is:

SELECT "column_name1"

FROM "table_name1"

WHERE EXISTS

( SELECT *

FROM "table_name2"

WHERE [Condi on] )

Example:

SELECT ename, deptno FROM emp WHERE EXISTS

(SELECT * FROM emp WHERE sal >3500 )

Exercises On Subqueries:

-------------------------

1. Write a query to display the employee name and hiredate for all

employees in the same department as Blake. Exclude Blake.

sol:

----

select ename,hiredate from emp where deptno =(select deptno from emp where ename ='BLAKE')
and ename !='BLAKE';

2.Create a query to display the employee number and name for all employees who earn

more than the average salary. Sort the results in descending order of salary.
sol:

---

select empno,ename from emp where sal >(select avg(sal) from emp ) order by sal desc;

3.Write a query to display the employee number and name for all

employees who work in a department with any employee whose name contains a T.

sol:

----

SELECT empno, ename FROM emp WHERE deptno IN ( SELECT deptno

FROM emp WHERE ename LIKE '%T%' );

4.Display the employee name, department number, and job tle for all

employees whose department loca on is Dallas.

sol:

---

select ename,deptno, job from emp where deptno=(select deptno from dept where loc='Dallas')

5.Display the employee name and salary of all employees who report to King.

select ename,sal from emp where mgr in (select empno from emp where ename='KING');

dense_rank():

--------------

This func on assigns a rank to each row within a par on.

The ranks are assigned in a consecu ve manner, if there two values are equal then they will be
assigned the same rank, and the next rank value will be one greater than the previous rank assigned.

DENSE_RANK() On Sal column:

---------------------------

SELECT empno, ename, sal, DENSE_RANK() OVER (ORDER BY sal) my_rank FROM emp;

Based on each department you need assign DENSE rank using PARTITION BY:

----------------------------------------------------------------

SELECT empno, ename, sal,deptno ,

DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal) dens_rank

FROM emp;

Rank:

-----

This func on assigns a rank to each row within a par on that has gaps.

In this case, ranks are assigned in a non-consecu ve manner i.e if there two records have the same
value then they will be assigned the same rank, and the next rank value will be the previous rank plus
the number of duplicates.

Examples:

---------

SELECT empno, ename, sal, RANK() OVER (ORDER BY sal) my_rank FROM emp;

Based on each department you need assign rank using PARTITION BY:

----------------------------------------------------------------

SELECT empno, ename, sal, deptno,RANK() OVER (PARTITION BY deptno ORDER BY sal) my_rank
FROM emp;

Exercise:
---------

write query to get second highest salary using subquery and Dense_rank().

sol:

---

select dis nct sal from (select empno,ename,sal,dense_rank() over (order by sal desc) as dnk from
emp) dnkemp

where dnkemp.dnk=2;

INDEXES:

========

Index is a database object ,index is a pointer which

locates the phsyical data of a table.

we can create index explicity on columns,

so that we can increase the performance of the sql statement query.

TYPES OF INDEXES:

======================================

1)SIMPLE INDEX

2)COMPLEX INDEX OR COMPOSITE INDEX

SIMPLE INDEX:

=============

SIMPLE INDEX IS A INDEX WHICH IS CREATED BASED ON SINGLE COLUMN.

SYNTAX:

=======

CREATE INDEX INDEXNAME ON TABLENAME(COLUMNANME)

EX:

===

Before Index created:

---------------------

SELECT * FROM employees WHERE hire_date > '2000-01-01';

create index:

--------------

CREATE INDEX i_hire_date ON employees(hire_date);

A er Index created:

---------------------

SELECT * FROM employees WHERE hire_date > '2000-01-01';

NOTE:

======

WE NEED TO CREATE INDEX ON COLUMNS,THAT COLUMNS

FREQUENTLY USED IN WHERE CLAUSE.

WHEN CREATE INDEX THE TABLE WILL BE SCANNED BASED ON INDEX

RANGE SCAN.
COMPLEX or composite index:

---------------------------

A COMPLEX INDEX IS CREATED BASED ON MORE THAN ONE COLUMN.

SYNTAX:

========

CREATE INDEX INDEXNAME ON TABLENAME(COL1,COL2...,COL32);

Ex:

---

Before Index created:

---------------------

SELECT * FROM employees WHERE first_name = 'Georgi'AND last_name = 'Facello';

create index:

-------------

CREATE INDEX i_composite ON employees(first_name, last_name);

a er index created:

--------------------

SELECT * FROM employees WHERE first_name = 'Georgi'AND last_name = 'Facello';

3)Unique index:

----------------

Syntax:

--------

create unique index indexname on tablename(columns);

it will create the indexes on columns as well as it will create the unique constraints on

columns(duplicates not allowed).

ex:

---

create unique index infy_fn_ln_uq on info_1(first_name,last_name);

select * from info_1 where first_name='smith' and last_name='joe';

explain select * from info_1 where first_name='smith' and last_name='joe';

it will executes the select query based on index scan

try to insert duplicates in info_1 table.

insert into info_1 values(4,'smith','joe','sa',5000);

insert into info_1 values(4,'smith','joe','sa',5000);//duplicates entery not allowed

to list indexes on table from database

=======================================

show index from tablename from databasename;

Ex:

show index from emp from parul;

to drop index:

--------------

DROP INDEX index_name ON table_name

or

alter table tablename drop index indexname;

SET OPERATORS:

==============

UNION

UNIONALL

Rules for set operators:

=======================

1. Number of columns used in the query should match.

2. column data type should match for the queries in set operators.

UNION:

======

THIS OPERATEOR UNION RETRIVES THE OUTPUT OF TWO QUERIES

AND IT REMOVES DUPLICATE RECORDS.

CREATE TABLE DEPT_U1 AS SELECT * FROM DEPT;

CREATE TABLE DEPT_U2 AS SELECT * FROM DEPT;

INSERT SOME RECORDS INTO ABOVE TWO TABLES.

EX:

====

SELECT * FROM DEPT_U1

UNION

SELECT * FROM DEPT_U2;

---IT WILL REMOVE DUPLICATES RECORDS.

UNION ALL:

==========

SELECT * FROM DEPT_U1

UNION ALL

SELECT * FROM DEPT_U2;

NOTE:

=====

IT WILL DISPLAY ALL THE DUPLICATE RECORDS FROM TWO TABLES

select ename from emp

-> union

-> select dname from dept;

Variable:

=========

A variable allows a programmer to store data temporarily during the execu on of code.

syntax:

-------

set @variablename=ini alize the value;

display the data from variable:

-------------------------------

select @variablename;

Ex:

===

set @name='Sumit deshpade'; --declare and ini alize the variable

select @name; --display the variable

+----------------+

| @name |

+----------------+

| Sumit deshpade |

+----------------+

1 row in set (0.01 sec)

mysql> set @dno=20;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from emp where deptno=@dno;

Case- WHEN-END

===============

to check mul ple condi ons

syntax:

-------

case columnname

when condi on then result

when conditon then result

[else]

end;

Ex:

----

mysql> select empno,ename,case sal

-> when 5000 then 'highest salary'

-> when 3000 then 'low salary'

-> else 'daily wages'

-> end as salary from emp;

mysql> select empno,ename,sal,case

when sal>=5000 then 'highest salary'

when sal>2000 and sal <=3000 then 'low salary'

else 'daily wages'

end as status from emp;

176(leet code) second highest salary:

============================

select case

when count(salary)=0 then null

else salary

end

as SecondHighestSalary from (select salary,dense_rank() over (order by salary desc) as

rnk from employee) re

where re.rnk=2

or

select if(count(salary)=0 ,null,salary)

as SecondHighestSalary from (select salary,dense_rank() over (order by salary desc) as

rnk from employee) re

where re.rnk=2

or

select max(salary)

as SecondHighestSalary from (select salary,dense_rank() over (order by salary desc) as

rnk from employee) re

where re.rnk=2

if condi on:

-------------

syntax:

-------

if (expr1,exp2,exp3) --> if expr1 is true then it will return exp2,if not then it exp3;

ex:

---

select if(10>5,'greater','lesser than 5')

select sal,if(sal>=5000,'high salary','low salary') from emp;

ifnull() or NVl() or coalesce() --to replace the null with some values .

Locking Tables in mysql:

------------------------

You can restrict the access to records of the tables in MYSQL by locking them. These locks are used to
keep other sessions away from modifying the tables in the current session.

MySQL sessions can acquire or release locks on the table only for itself. To lock a table using the
MySQL LOCK TABLES Statement you need have the TABLE LOCK and SELECT privileges.

These locks are used to solve the concurrency problems. There are two kinds of MYSQL table locks −

READ LOCK − If you apply this lock on a table the write opera ons on it are restricted. i.e., only the
sessions that holds the lock can write into this table.

WRITE LOCK − This lock allows restricts the sessions (that does not possess the lock) from performing
the read and write opera ons on a table.

Syntax:

-------

Following is the syntax of the MySQL LOCK TABLES Statement −

LOCK TABLES table_name [READ | WRITE];

Unlocking Tables in MySQL

------------------------

Once the client session is done using/accessing a MySQL table, they must unlock the table for other
client sessions to use it. To do so, you can use the MySQL UNLOCK TABLE statement. This will release
the table un l other sessions lock it again.
Syntax:

-------

Following is the syntax of the MySQL UNLOCK TABLES Statement −

UNLOCK TABLES;

How to create a user:

---------------------------

syntax:

-------

CREATE USER 'username'@'host' IDENTIFIED [WITH] BY 'password';

Grant Privileges:

-----------------

GRANT ALL PRIVILEGES ON * . * TO 'new_user'@'localhost';

ex:

---

CREATE USER 'raj'@'localhost' IDENTIFIED BY 'raj';

GRANT ALL PRIVILEGES ON * . * TO 'raj'@'localhost';

to list all users:

-------------------

select user,host,account_locked from mysql.user;

to drop the user:

------------------

syntax:

-------

drop user 'username'@'localhost';

ex:

----

drop user 'lucky'@'localhost' ;

select user,host,account_locked from mysql.user;

to lock the user:

-----------------

syntax:

alter user 'username'@'localhost' account lock;

Ex:

----

alter user 'sarath'@'localhost' account lock;

cmd> mysql -u sarath -p

Enterpasswor:*****

will you get the error

to check whether user is locked or not :

-----------------------------------------

SELECT user, host, account_locked FROM mysql.user

WHERE user = 'sarath' AND host = 'localhost';

to lock unlockthe user:

-----------------

alter user 'username'@'localhost' account unlock;

Ex:

---

alter user 'sarath'@'localhost' account unlock;

cmd> mysql -u sarath -p

Enterpasswor:*****

Procedure

=========

A procedure (o en called a stored procedure) is a collec on of pre-compiled SQL statements stored

inside the database.

It is a subprogram in the regular compu ng language.

A procedure always contains a name, parameter lists, and SQL statements.

We can invoke the procedures by using triggers, other procedures and applica ons such as Java,
Python, PHP, etc.

Stored Procedure Features

-------------------------

Stored Procedure increases the performance of the applica ons. Once stored procedures are
created,

they are compiled and stored in the database.

Stored procedure reduces the traffic between applica on and database server. Because the
applica on has to

send only the stored procedure's name and parameters instead of sending mul ple SQL statements.

Stored procedures are reusable and transparent to any applica ons.

A procedure is always secure.The database administrator can grant permissions to

applica ons that access stored procedures in the database without giving any permissions on the
database tables.

syntax:

-------

DELIMITER && or DELIMITER //

CREATE PROCEDURE procedure_name [[IN | OUT | INOUT] parameter_name datatype [, parameter

datatype]) ]

BEGIN

Declara on_sec on /*to declare local variables using delcare keyword*/

Executable_sec on

END && or //

DELIMITER ;

The procedure syntax has the following parameters:

Parameter Name Descrip ons

-------------- -----------------------------------------------

procedure_name It represents the name of the stored procedure.

parameter It represents the number of parameters. It can be one or

more than one.
Declara on_sec on It represents the declara ons of all variables.

Executable_sec on It represents the code for the func on execu on or sql

queries.

MySQL procedure parameters

==========================

IN: These types of parameters are assigned the values while calling the stored procedure and

the value cannot be modified or overwri en inside the stored procedure but only referenced and

used by the stored procedure.

OUT: These are the parameters that can be assigned the values and overridden in the stored
procedure

but cannot be referenced by it.

IN OUT: These types of parameters are assigned the values while calling the stored procedure and

the value can be modified or overwri en inside the stored procedure as well as

referenced and used by the stored procedure.

to call the procedure:

======================

syntax:

-------

call procedurename(if any parameter);

Ex:1

-----
Create a procedure to store select query to display the data from emp table.

sol:

===

Delimiter //

create procedure ex1()

begin

select * from emp;

end //

Delimiter ; /*here restore the delimiter ; back*/

to call procedure:

===================

call ex1();

to display all the procedure from database:

===========================================

show procedure status where db='database';

ex:

===

to drop procedure:

==================

drop procedure databasename.procedure_name

or
use database;

drop procedure procedure_name;

Ex2:

====

create a procedure named as ex2 and to store the query to display the emp records

where depnto 10,20 or 30 using in parameter

sol:

====

Delimiter //

create procedure ex2(in dno decimal)

begin

select * from emp where deptno=dno;

end //

delimiter ;

to call procedure:

==================

call ex2(10);

call ex2(20);

call ex2(30);

Ex3:

----

create a procedure named as ex3 to store max(sal) into out parameter from emp table.
sol:

====

Delimiter //

create procedure ex3(out ms decimal)

begin

select max(sal) into ms from emp;

end //

delimiter ;

to call procedure with out parameter

=====================================

call ex3(@s); /*when call the procedure it will return out parameter or variable
value,then we need to store that value into a variable).*/

select @s ;

Exercise:

---------

write a procedure to display the ename(out) of empno 7566(IN);

sol:

====

delimiter //

create procedure ex4(in eno int,out en varchar(15))

begin
select ename into en from emp where empno=eno;

end //

delimiter ;

-----------------

call ex4(7566,@n);

select @n;
Ex:

====

write a procedure to perfrom sum of two numbers(two -IN) and return sum(OUT)

sol:

----

delimiter //

create procedure ex_sum(IN a int,IN b int,OUT c int)

begin

set c=a+b;

end //

delimiter ;

----------------

call ex_sum(10,30,@st)

select @st

Ex4:

-----

write a procedure to get ename of empno=7566 and display it.

sol:

----

delimiter //
create procedure ex_en(in eno decimal,out en varchar(10))

begin

select ename into en from emp where empno=eno;

end //

delimiter ;

---------------

call ex_en(7566,@vn);

select @vn

ex4:

====

create procedure named as ex4 to store max(sal) int to outparameter from emp

where deptno 10 or 20 or 30 using in,out paramter

sol:

----

delimiter //

create procedure ex4(in dno decimal,out ms decimal)

begin

select max(sal) into ms from emp where deptno=dno;

end //
delimiter ;

to call procedure:

==================

call ex4(10,@tn);

select @tn;

call ex4(20,@tn);

select @tn;

call ex4(30,@tn);

select @tn;

Ex5:

----

create a procedure named as ex5 to store max(sal) from emp

where deptno 10 or 20 30 using INOUT parameter

sol:

----

Delimiter //

create procedure ex5(inout v_n decimal)

begin

select max(sal) into v_n from emp where deptno=v_n;

end //

delimiter ; /*to restore delimiter from // to ;*/

to call the procedure:

======================
set @n=10; /*it will act as input parameter */

call ex5(@n);

select @n;

select @n; /*it will act as output parameter*/

--------------

set @n=20

call ex5(@n);

select @n;

Ex6:

----

Create a procedure to declare local variable

ini alize and display it.

sol:

---

delimiter //

create procedure ex6()

begin

declare a int;

declare b varchar(10); /*declara on of variables */

declare c float;
set a=10;

set b='welcome'; /*ini aliza on variable using set keyword*/

set c=300;

select a,b,c; /*display*/

end //

delimiter ;

to call the procedure:

----------------------

call ex6();

condi onal or control statement:

=================================

if:

---

if condi on then

statement-1;

end if;

if-else:

--------

syntax:

-------

if condi on then

statement-1;
else

statement-2;

end if;

Ex:

---

create a procedure to compare two integer variables

display which is greater using in parameter.

sol:

-----

delimiter //

create procedure ex7(in a int,in b int)

begin

declare msg varchar(20);

if a > b then

set msg='a is greater than b';

else

set msg='b is greater than a';

end if;

select msg;

end //

delimiter ;
sol:

===

Delimiter //

create procedure ex7(in a int,in b int)

begin

declare msg varchar(30);

if a>b then

set msg='a is greater than b';

else

set msg='b is greater than a';

end if;

select msg;
end //

delimiter ;

to call procedure:

------------------

call ex7(10,5) ;

call ex7(1,5) ;

Ex:

---

if-else-if:

-----------

syntax:

-------

if condi on then

statement-1;

elseif condi on then

statement-2;

elseif condi on then

statement-3;

else

statement-4;

end if;
ex:

---

create a procedure to compare three integer variables

display which is greater using in parameter and if-else-if.

sol:

---

Delimiter //

create procedure ex8(in a int,in b int,in c int)

begin

declare msg varchar(30);

if a>b and a>c then

set msg='a is greater than b,c';

elseif b>c and b>a then

set msg='b is greater than a,c';

else

set msg='c is greater than a,b';

end if;
select msg;

end //

delimiter ;

to call procedure:

==================

call ex8(10,5,1);

call ex8(5,10,2);

cal ex8(5,1,7);

Exercises:

=========

1)create a procedure named as ex9 to perform sum of two number

using in parameters.

sol:

----

delimiter //

create procedure ex_9(in n1 int,in n2 int)

begin

declare res int;

set res=n1+n2;
select res;

end //

delimiter ;

to call procedure:

------------------

call ex_9(10,10);

2)create a procedure named as ex10 to perform sum of two number

using in parameters and out parameter

sol:

----

delimiter //

create procedure ex10(in n1 int,in n2 int,out res int)

begin

set res=n1+n2;

end //

delimiter ;

to call procedure:

==================

call ex10(10,30,@rs);
select @rs;

Func on:

---------

In MySQL, a func on is a stored program that you can pass parameters into and then return a value.

Just as you can create func ons in other languages, you can create your own func ons in MySQL.

Syntax

-------

The syntax to create a func on in MySQL is:

--------------------------------------------

DELIMITER //

CREATE FUNCTION func on_name [ (parameter datatype [, parameter datatype]) ]

RETURNS return_datatype

DETERMINISTIC

BEGIN

declara on_sec on

executable_sec on

return datatype;

END;
DELIMITER //

func on_name

--------------

The name to assign to this func on in MySQL.

parameter

----------

One or more parameters passed into the func on. When crea ng a func on,

all parameters are considered to be IN parameters (not OUT or INOUT parameters)

where the parameters can be referenced by the func on but can not be overwri en by the func on.

return_datatype

----------------

The data type of the func on's return value.

declara on_sec on

--------------------

The place in the func on where you declare local variables.

executable_sec on

------------------

The place in the func on where you enter the code for the func on.

DETERMINISTIC:

--------------

it means the func on will return the same values if the same arguments are supplied to it.

to call func on:

==================

select func on_name(if any parameters pass the value);

Ex:

---

create a func on named as addi on which takes three parameters

and perform sum of three numbers and return it

sol:

----

delimiter //

create func on addi on(a int,b int,c int)

returns int

DETERMINISTIC

begin

return (a+b+c);

end //

delimiter ;

to call the func on:

----------------------

select addi on(10,20,30); //it will return 60;

or

---

create table test1(a int,b int,c int);

insert into test1 values(10,30,50);

select addi on(a,b,c) from test1;

to drop func on:

-----------------

drop func on func on_name;

note:

-----

func on name should be userdefined name.

(Don't use predefined func on names like string func ons,date func ons,number func ons etc).

Ex:

---

create a func on which returns the max(sal) from emp table.

sol:

----

delimiter //

create func on ms() /*default fun on*/

returns decimal
DETERMINISTIC

begin

declare v_m decimal;

select max(sal) into v_m from emp;

return v_m;

end //

delimiter ;

to call the func on:

=====================

select ms() as maximumSal;

Exercise:

---------

create a func on to display welcome 'ename' from emp table

when pass a ename column it should print welcome smith...etc

sol:

====

delimiter //

create func on concat_en(n varchar(10))

returns varchar(20)
DETERMINISTIC

begin

declare en varchar(20);

set en=concat('welcome ',n);

return en;

end //

delimiter ;

to call the func on:

=====================

select concat_en(ename) from emp;

select concat_en(' Rajukumar');

TRIGGERS:

=========
A Trigger in MySQL is a special kind of stored opera on that gets invoked automa cally when an
event has

occurred in the database.

It is a database object which is related to a table in the database and becomes ac ve when a defined
MySQL

statement is ini ated on the table.

These DML (Data Manipula on Language) execu on opera ons can be INSERT, DELETE, UPDATE and
triggers

can be called before or a er these events.

A trigger is a set of ac ons that are run automa cally when a specified change opera on

(SQL INSERT, UPDATE, or DELETE statement) is performed on a specified table.

Triggers are useful for tasks such as enforcing business rules, valida ng input data, and keeping an
audit trail.

For example when a row is inserted to a table or when any columns are modified, a trigger can be
fired.

Mostly, triggers can are made to run whenever any altera ons are done to the data of a table.

Triggers are simply a SQL code to run before or just a er any DML ac on events on a par cular table
in a database.

Syntax

-------

CREATE

[DEFINER = user] TRIGGER trigger_name

trigger_ me trigger_event

ON tbl_name
FOR EACH ROW

trigger_body

trigger_ me: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

Parameters

------------

DEFINER clause:

---------------

Iden fies the MySQL account that is used for access at trigger ini a on me.

trigger_name:

-------------

The name of all triggers should be unique inside a schema.

trigger_ me:

-------------

Defines the trigger ac on me which can be either before or a er any row affected.

trigger_event:

--------------

Specifies the type of opera on to ac vate the trigger.

tbl_name:

---------

The table name should be of a permanent table not a temporary or a view table to associate a
trigger.

trigger_body:
-------------

It is used to display a statement when the trigger is fired.

The compound statement construct BEGIN … END can be used to complete many statements.

Types of Trigger in MYSQL

=========================

Two types of triggers are defined by the SQL standard:

Row-Level Trigger:

=================

It is executed when each row is affected by inser on, upda on and dele on ac ons on a table.

Like if you have a table and 50 rows are inserted, updated or deleted, then automa cally the trigger

is also invoked for 50 mes.

Statement-Level Trigger:

======================

This trigger is invoked only once for a transac on regardless of the number of rows inserted,
updated, or deleted.

note:

=====

Row-Level Trigger is supported in MySQL but not Statement-Level Trigger.

PSEUDO COLUMNS:

===============

NEW:- IT REPRESENT NEW VALUES FOR A COLUMNS OF A TABLE

OLD :- IT REPRESNT OLD VALUES FOR A COLUMNS OF A TABLE.

full syntax:

============

delimiter //

create trigger trigger_name

{before/a er}

[insert or update or delete] on table_name

for each row

begin

statment; or logic;

end //

delimiter //

Example:

========

1) create a table named as emp13(empno,ename,sal)

2) create a trigger before insert into emp13 covert the ename into capital le er.

3) perform mul ple insert to check trigger is fired or not?

sol:

----

1)create table emp13(empno int(4),ename varchar(10),sal decimal(7));

2)

delimiter //

create trigger trg_emp_13

before insert on emp13

for each row

begin

set New.ename=upper(New.ename);

end //

delimiter ;

3)

insert into emp13 values(101,'ramu',3000);

Example:

--------

1)create table emp14(empno int,ename varchar(10),sal decimal(10,2));

2)create table messag(msg varchar(30));

trigger:

--------

delimiter //

create trigger trg_emp_14

before insert on emp14

for each row

begin

insert into messag values('insert trigger fired on emp14');

end //

3) insert the record into emp14

insert into emp14 values(101,'smith',4000),(102,'rahu',3000);

Example:

----------

write a trigger on emp15 table before insert into empno column append PU_ to empno.

insert into emp15 values(5010,'smith',3000);

select * from emp15;

PU_5010 smith 3000

sol:

----

1)create table emp15(empno varchar(10),ename varchar(10),sal decimal);

2)

delimiter //
create trigger trg_emp_15

before insert on emp15

for each row

begin

set New.empno=concat('PU_',New.empno);

end //

delimiter ;

3)

insert into emp15 values('5010','smith',3000);

select * from emp15

Exercise:

=========

1)create table studentdetails(sid int(4),sname varchar(10),m1 int(3),m2 int(3),m3 int(3),total int(5));

2)create trigger on studentdetails before insert to table it should calculate sum of m1+m2+m3 and
store in total column

sol:

----

1) create table studentdetails(sid int(4),sname varchar(10),m1 int(3),m2 int(3),m3 int(3),total int(5));

2)

delimiter //
create trigger trg_sd

before insert on studentdetails

for each row

begin

set New.total=New.m1+New.m2+New.m3;

end //

delimiter ;

3) insert into studentdetails (sid,sname,m1,m2,m3) values (1,'Rahul',60,60,70);

select * from studentdetails;

1 Rahul 60 60 70 190

execute insert

==============

mysql> insert into studentdetails (sid,sname,m1,m2,m3) values

-> (101,'rahul',60,70,80);

Query OK, 1 row affected (0.01 sec)

mysql> select * from studentdetails;

+------+-------+------+------+------+-------+

| sid | sname | m1 | m2 | m3 | total |

+------+-------+------+------+------+-------+

| 101 | rahul | 60 | 70 | 80 | 210 |

+------+-------+------+------+------+-------+

1 row in set (0.00 sec)

Example:

--------

bank aud ng

1)create a table emp_base(empno,ename,sal): --original table

sol:

create table emp_base(empno int(4),ename varchar(10),sal int(8));

insert into emp_base(1001,'smith',3000);

2)create a backup table named as --to maintain history

emp_base_update(empno,n_ename,o_ename,n_sal,o_sal,operat,ondate):

sol:

create table emp_base_update_bkup(empno int(4),n_ename varchar(10),

o_ename varchar(10),n_sal int(8),o_sal int(8),opera varchar(10),ef_ondate date);

3)create a trigger before update an record in emp_base table insert that record into
emp_base_update(history,new value).

sol:

----

delimiter //

create trigger trg_adut_emp_base

before update on emp_base

for each row

begin

insert into emp_base_update (empno,n_ename,o_ename,n_sal,o_sal,opera,ef_ondate) values

(old.empno,new.ename,old.ename,new.sal,old.sal,'update',current_date());

end //

delimiter ;

Execute:

--------

mysql> update emp_base set ename='mar n',sal=5000 where empno=1001;

mysql> select * from emp_base;

+-------+--------+------+

| empno | ename | sal |

+-------+--------+------+

| 1001 | mar n | 5000 |

+-------+--------+------+

1 row in set (0.00 sec)

mysql> select * from emp_base_update;

+-------+---------+---------+-------+-------+--------+------------+

| empno | n_ename | o_ename | n_sal | o_sal | opera | ef_ondate |

+-------+---------+---------+-------+-------+--------+------------+

| 1001 | mar n | smith | 5000 | 3000 | update | 2021-03-05 |

+-------+---------+---------+-------+-------+--------+------------+

1 row in set (0.00 sec)

Exercise:

--=======

1)create a table emp_base(empno,ename,sal)

create table emp_base(empno int(4),ename varchar(10),sal int(8));

insert five records.

2)create a backup table named as emp_base_delete(empno,ename,opera,ondate);

create table emp_base_delete( empno int(4),ename varchar(10),sal int(8),opera

varchar(10),ef_ondate data);

3) create a trigger before delete a record from emp_base,the deleted record should backup into
emp_base_delete table.

sol:

----

delimiter //

create trigger trg_adut_emp_del

before delete on emp_base

for each row

begin

insert into emp_base_delete(empno,ename,sal,opera,ef_ondate) values

(old.empno,old.ename,old.sal,'delete',current_date());
end //

delimiter ;

Execute:

========

mysql> delete from emp_base where empno=1005;

Query OK, 1 row affected (0.01 sec)

mysql> select * from emp_base;

+-------+--------+------+

| empno | ename | sal |

+-------+--------+------+

| 1001 | john | 6000 |

| 1002 | laxmi | 5000 |

| 1003 | ramesh | 2000 |

| 1004 | raj | 1000 |

+-------+--------+------+

4 rows in set (0.00 sec)

mysql> select * from emp_base_delete;

+-------+-------+------+--------+------------+

| empno | ename | sal | opera | ef_ondate |

+-------+-------+------+--------+------------+

| 1005 | sumit | 4000 | delete | 2021-03-05 |

+-------+-------+------+--------+------------+

1 row in set (0.00 sec)

mysql>
to drop a trigger:

==================

drop trigger trigger_name;

to list all trigger on table:

=============================

show triggers from database_name where `table`='tablename'

here we need to use back quote(`)

ex:

---

show triggers from parul where `table`='emp_base';

or

show triggers;

MySQL DATE func ons

---------------------

MySQL supports a bunch of date u lity func ons that we can use to handle DATE efficiently.

NOW()

------

This date func on returns the current date and me of the running server instance.

-- Print current date and me in MySQL

SELECT NOW();
DATE()

--------

This date func on extracts and returns the date part from the given DATETIME value.

-- Print date part from current date and me in MySQL

SELECT DATE(NOW());

CURDATE()

----------

It is a simple date func on that fetches the current date of the system running the MySQL instance.

-- Print the current date in MySQL

SELECT CURDATE();

DATE_FORMAT():

--------------

Some mes you need to display a date in a user-defined style. For example, you want to show the
month first, then the date, and the year in the last.

-- Print the current MySQL date in a user-defined format

SELECT DATE_FORMAT(DATE(NOW()), '%m_%d_%Y') Styled_date;

DATEDIFF():

------------

You may want to count the difference between the two dates.

Therefore, you can use the DATEDIFF() func on.

-- Print the difference between two dates

SELECT DATEDIFF('2019-08-04','2019-08-01') diff;

Note:

-----

The DATEDIFF() func on would subtract the second date argument from the first and return the diff
in days.

i.e 3

DATE_ADD():

-------------

It enables you to add any of the days, weeks, months, or years to a given date. Check the below
example.

-- Adding days, weeks, months, and years using DATE_ADD()

SELECT

'2019-08-04' ACTUAL,

DATE_ADD('2019-08-04', INTERVAL 1 DAY) 'Added 1 day',

DATE_ADD('2019-08-04', INTERVAL 1 WEEK) 'Added 1 week',

DATE_ADD('2019-08-04', INTERVAL 1 MONTH) 'Added 1 month',

DATE_ADD('2019-08-04', INTERVAL 1 YEAR) 'Added 1 year';

similary we have DATE_SUB()

EXTRACT():

----------

The EXTRACT func on will extract a part of a date from a specified date value.
EXTRACT(unit FROM date)

It uses the same unit specifiers as other date func ons such as DATE_ADD and DATE_SUB.

SELECT EXTRACT(DAY FROM '2020-08-04');

SELECT EXTRACT(MONTH FROM '2020-08-04');

SELECT EXTRACT(YEAR FROM '2020-08-04');

SELECT EXTRACT(YEAR_MONTH FROM '2020-08-04');

Specifier Descrip on

--- -----------

%a Abbreviated weekday name (Sun..Sat)

%b Abbreviated month name (Jan..Dec)

%c Month, numeric (0..12)

%D Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)

%d Day of the month, numeric (00..31)

%e Day of the month, numeric (0..31)

%f Microseconds (000000..999999)

%H Hour (00..23)

%h Hour (01..12)
%I Hour (01..12)

%i Minutes, numeric (00..59)

%j Day of year (001..366)

%k Hour (0..23)

%l Hour (1..12)

%M Month name (January..December)

%m Month, numeric (00..12)

%p AM or PM

%r Time, 12-hour (hh:mm:ss followed by AM or PM)

%S Seconds (00..59)

%s Seconds (00..59)

%T Time, 24-hour (hh:mm:ss)

%U Week (00..53), where Sunday is the first day of the week; WEEK() mode 0

%u Week (00..53), where Monday is the first day of the week; WEEK() mode 1

%V Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with
%X
%v Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with
%x

%W Weekday name (Sunday..Saturday)

%w Day of the week (0=Sunday..6=Saturday)

%X Year for the week where Sunday is the first day of the week, numeric,
four digits; used with %V

%x Year for the week, where Monday is the first day of the week, numeric, four digits;

used with %v

%Y Year, numeric, four digits

%y Year, numeric (two digits)

%% A literal % character

%x x, for any “x” not listed above

Common Table Expression (CTE) in MySQL:

------------------------------------

A Common Table Expression (CTE) is a temporary named result set that is defined within the scope of
a single SQL query.

It allows you to write complex queries in a more readable and maintainable manner, by breaking
them down into smaller, logical steps.

CTEs can be used in MySQL with the "WITH" keyword, followed by the name of the CTE and the
SELECT statement that defines it. Once a CTE is defined, it can be referenced mul ple mes within
the same query.
One of the main benefits of using CTEs is that they allow you to create recursive queries, which are
queries that reference themselves.

This can be useful for tasks such as genera ng hierarchical data or finding the shortest path between
nodes in a graph.

CTEs can also be used to simplify the syntax of complex joins, to create derived tables that can be
used within other queries, or to improve query performance by reducing the number of mes that a
subquery needs to be executed

MySQL CTE Syntax

------------------

The basic syntax for crea ng a Common Table Expression (CTE) in MySQL is as follows:

WITAH cte_name(

SELECT column1, column2, ...

FROM table_name

WHERE condi on

SELECT *

FROM cte_name;

Syntax:mul ple CTEs within the same query, by separa ng each CTE defini on with a comma. For
example:

WITH cte1 AS (

SELECT column1, column2, ...

FROM table1

WHERE condi on1

),

cte2 AS (

SELECT column3, column4, ...

 FROM table2

WHERE condi on2

SELECT *

FROM cte1

JOIN cte2 ON cte1.column1 = cte2.column3;

In this example, two CTEs are defined: "cte1" and "cte2". The CTEs are then referenced in the main
query by joining them on a common column and selec ng all columns from both CTEs.

MySQL CTE Examples

------------------

An example of how Common Table Expressions (CTEs) can be used in MySQL:

Suppose you have a sales table with columns "product_name", "sales_date", and "revenue".

You want to calculate the total revenue for each product over the past 30 days, as well as the
percentage change in revenue compared to the previous 30-day period. You can use a CTE to
calculate these metrics:

The following commands can be used to create the table:

CREATE TABLE sales (

product_name VARCHAR(50),

sales_date DATE,

revenue DECIMAL(10, 2));

The following commands can be used to insert data into the table:

INSERT INTO sales (product_name, sales_date, revenue) VALUES

('Product A', '2023-03-01', 1000),

('Product A', '2023-03-15', 1500),

 ('Product A', '2023-03-30', 2000),

('Product B', '2023-03-01', 800),

('Product B', '2023-03-15', 1200),

('Product B', '2023-03-30', 1600),

('Product C', '2023-03-01', 500),

('Product C', '2023-03-15', 750),

('Product C', '2023-03-30', 1000);

Query:

-------

WITH sales_last_30_days AS (

SELECT product_name, SUM(revenue) AS total_revenue

FROM sales

WHERE sales_date BETWEEN '2023-03-10' AND '2023-04-09'

GROUP BY product_name

),

sales_previous_30_days AS (

SELECT product_name, SUM(revenue) AS total_revenue

FROM sales

WHERE sales_date BETWEEN '2023-02-08' AND '2023-03-09'

GROUP BY product_name

SELECT

s.product_name,

s.total_revenue,

(s.total_revenue - p.total_revenue) / p.total_revenue * 100 AS revenue_change

FROM sales_last_30_days s

JOIN sales_previous_30_days p ON s.product_name = p.product_name;

Explana on:

------------

This CTE first calculates the total revenue for each product over the past 30 days and stores the result
in a CTE called "sales_last_30_days".

It then calculates the total revenue for each product over the previous 30-day period and stores the
result in a CTE called "sales_previous_30_days".

The main query joins these two CTEs on the "product_name" column and calculates the percentage
change in revenue.

More Advanced MySQL CTE Example

-----------------------------

An example of more advanced Common Table Expressions (Recursive CTEs) in MySQL:

Suppose you have a database with a table called "sales" that contains sales data for a company. The
"sales" table has columns for "order_date", "product", "quan ty", and "revenue".

You want to create a report that shows the total revenue generated by each product, broken down
by month. However, you also want to include a column that shows the percentage of total revenue
generated by each product, based on the revenue generated by all products.

The following commands can be used to create the table:

CREATE TABLE sales1 (

order_date DATE,

product VARCHAR(50),

quan ty INT,

revenue DECIMAL(10,2)

);
INSERT INTO sales1 (order_date, product, quan ty, revenue) VALUES

('2023-01-01', 'Product A', 10, 1000.00),

('2023-01-15', 'Product B', 5, 500.00),

('2023-02-01', 'Product A', 8, 800.00),

('2023-02-15', 'Product C', 3, 300.00),

('2023-03-01', 'Product A', 12, 1200.00),

('2023-03-15', 'Product B', 7, 700.00);

Query:

-----

WITH monthly_sales AS (

SELECT

DATE_FORMAT(order_date, '%Y-%m') AS month,

product,SUM(revenue) AS revenue

FROM sales1 GROUP BY month,product

),

total_sales AS (

SELECT

month,SUM(revenue) AS total_revenue

FROM monthly_sales GROUP BY month

SELECT

monthly_sales.month,

monthly_sales.product,

monthly_sales.revenue,

(monthly_sales.revenue / total_sales.total_revenue)*100 AS percentage

FROM

monthly_sales

JOIN total_sales ON monthly_sales.month = total_sales.month

ORDER BY monthly_sales.month,percentage DESC;

Benefits of Using CTE

-------------------

Here are some of the benefits of using Common Table Expressions (CTEs) in MySQL:

Simplify complex queries:

-----------------------

CTEs can be used to break down complex queries into smaller, more manageable parts. By defining
CTEs, you can avoid wri ng complex subqueries mul ple mes, which can make your code easier to
read and understand.

Improve query performance:

-------------------------

CTEs can help improve query performance by reducing the number of database scans required to
execute a query. By defining a CTE, you can create a temporary table that can be used to store
intermediate results, which can reduce the number of scans required to retrieve data.

Reuse code:

----------

CTEs can be used to create modular SQL code that can be reused across mul ple queries. By defining
a CTE at the beginning of a query, you can create a self-contained module that can be used in other
queries, making your code more flexible and adaptable.

Traverse hierarchical data:

-------------------------

CTEs can be used to traverse hierarchical data structures, such as organiza onal charts or product
categories. By defining a recursive CTE, you can traverse the hierarchy and perform complex
calcula ons on the data.
Simplify debugging:

------------------

CTEs can help simplify debugging by breaking a complex query into smaller parts. By defining CTEs,
you can analyze each part of the query individually, making it easier to iden fy and fix errors.

Improve code readability:

------------------------

CTEs can improve the readability of your SQL code by providing a clear and concise way to define
temporary named result sets. By using descrip ve names for your CTEs, you can make your code
easier to read and understand.

Locking Tables in mysql:

------------------------

You can restrict the access to records of the tables in MYSQL by locking them. These locks are used to
keep other sessions away from modifying the tables in the current session.

MySQL sessions can acquire or release locks on the table only for itself. To lock a table using the
MySQL LOCK TABLES Statement you need have the TABLE LOCK and SELECT privileges.

These locks are used to solve the concurrency problems. There are two kinds of MYSQL table locks −

READ LOCK − If you apply this lock on a table the write opera ons on it are restricted. i.e., only the
sessions that holds the lock can write into this table.

WRITE LOCK − This lock allows restricts the sessions (that does not possess the lock) from performing
the read and write opera ons on a table.

Syntax:

-------

Following is the syntax of the MySQL LOCK TABLES Statement −

LOCK TABLES table_name [READ | WRITE];

Unlocking Tables in MySQL

------------------------

Once the client session is done using/accessing a MySQL table, they must unlock the table for other
client sessions to use it. To do so, you can use the MySQL UNLOCK TABLE statement. This will release
the table un l other sessions lock it again.

Syntax:

-------

Following is the syntax of the MySQL UNLOCK TABLES Statement −

UNLOCK TABLES;

How to create a user:

---------------------------

syntax:

-------

CREATE USER 'username'@'host' IDENTIFIED [WITH] BY 'password';

Grant Privileges:

-----------------

GRANT ALL PRIVILEGES ON * . * TO 'new_user'@'localhost';

ex:

---

CREATE USER 'raj'@'localhost' IDENTIFIED BY 'raj';

GRANT ALL PRIVILEGES ON * . * TO 'raj'@'localhost';

to list all users:

-------------------

select user,host,account_locked from mysql.user;

to drop the user:

------------------

syntax:

-------

drop user 'username'@'localhost';

ex:

----

drop user 'lucky'@'localhost' ;

select user,host,account_locked from mysql.user;

to lock the user:

-----------------

syntax:

alter user 'username'@'localhost' account lock;

Ex:

----

alter user 'sarath'@'localhost' account lock;

cmd> mysql -u sarath -p

Enterpasswor:*****
will you get the error

to check whether user is locked or not :

-----------------------------------------

SELECT user, host, account_locked FROM mysql.user

WHERE user = 'sarath' AND host = 'localhost';

to lock unlockthe user:

-----------------

alter user 'username'@'localhost' account unlock;

Ex:

---

alter user 'sarath'@'localhost' account unlock;

cmd> mysql -u sarath -p

Enterpasswor:*****