https://github.

com/sainathatheli/5APlusplus

What is a Database?

A Data Base is an Organized Collection 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 applications and Software’s, every application 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
applications – look
and feel) and the Data that we see in the application (like Facebook profile,
messages, images
and videos) are pulled from Database.

End User who interacts with the application may not know how the data is being
fetched and
where so much of information is stored. Internally all the dynamic content that we
see in the
application is fetched from Database.

Database and all its components should be designed and configured at the time of
application
development. Once the application is developed we will not be able to make changes
to the
database structure as every change will hugely affect the business application 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 / software system

is defined and that
we call it as DBMS (Data Base Management System – which performs all the operations
on the
database)

What is DBMS?

DBMS (Database Management System) is a software tool that is used to store and
manage data
in the Database.

A database management system contains a set of programs that control the creation,
maintenance, and use of a database. Like:

Adding new data to the table.

Modifying existing data.
Deleting unwanted data.
DBMS allows different user application programs to concurrently access the same
database.
Before Database and DBMS were introduced, traditional approach was to store data in
flat files
(physical files in system) but there were some disadvantages with it.

Data Redundancy and Inconsistency

Redundancy: In a flat file system, data often gets duplicated across multiple
files, leading to redundancy.

Inconsistency: Due to redundancy, it becomes difficult to ensure all copies of the

data are updated simultaneously, leading to data inconsistency.

Complex Queries: Performing complex queries to retrieve specific data requires

writing complex and inefficient code, often leading to slower performance.

Scattered Data: Data is scattered across various files, making it hard to correlate
and retrieve related data efficiently.

Difficult Integration: Integrating data from different files to produce meaningful

reports is cumbersome and error-prone.

Constraints: Enforcing data integrity constraints like foreign keys, unique keys,
and checks is challenging in flat file systems.

Security Issues
Access Control: Implementing fine-grained access control in flat file systems is
difficult, leading to potential security risks.

Data Privacy: Protecting sensitive data from unauthorized access is more complex
without a centralized control mechanism.

What is RDBMS?
A relational database management system (RDBMS) is a Database Management System
(DBMS) that is based on the relational model introduced by E. F. Codd and most
popular
databases currently in use are based on the relational database model. To put in
different
words RDBMS is built on top of in which data is stored in tables and the
relationships 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 information.
In DBMS, all details like empno, ename, job, salary, deptno, dname, location,
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, location will be stored in dept table and location, street, city,
state, phone will be stored under locations table.
Any information to be obtained is done by properly relating the ‘emp’, ‘dept’
and ‘locations’ tables.

MYSQL: [Declerative Language]

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

Sql is classified into

DDL
===
Data definition Language

DDl is used to specify the structure or schema for Objects

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

DML
===
Data manipulation 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,function...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,function...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();

creating 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,'scott',4000);

insert into employee values(104,'martin',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 existing table employee.

sol:
====

alter table employee add job varchar(20);

ex2:
===

add mutiple columns address,mobilno columns to employee;

sol:
====

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

b)using alter modifying existing 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 => All the values in
the coloum becomes NULL
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;

sol:
-----

ALTER TABLE employee ADD PRIMARY KEY (empno);

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,function,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 manipulation(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,'martin','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 multiple 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 multiple 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');

REPLACE:
========

Replace when the data is matched then replace the data

if not found then insert the data

sol:

Replace into employees values(7789,'naveen','ceo','9000','2018-10-02')

Update:
=======
update is used to update the existing record data from the table.

syntax:
=======

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

[where condition]

note:
====
If where condition 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 condition or
we can delete all the records with out condition.

syntax:
-------

delete from tablename [where condition];

note:
=====
if where condition 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 condition]

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:

===================
insert 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 condition]

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 entire table.

Constraints can be both inline [beside the coloumn] or last of the table which is
table constraint
both are same but table constaints are more radable and industry stadards

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
inserting 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 (condition).

syntax:
-------
check (condition)

create table tablename(

col1 datatype [null | not null] default value,
col2 datatype [null | not null] check(condition) ,
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(condition) );

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,'martin',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 identifies a row in the table. It is a combination 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 parenttable 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 information_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 fk

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 information_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

Cannot delete or update a parent row: a foreign key constraint fails

(`learndbms`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`cid`) REFERENCES
`course` (`cid`))

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
fk
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 information.schema

------------------------------------
you can use information.schema.

The syntax is as follows

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

select COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME, REFERENCED_TABLE_NAME

from information_schema.KEY_COLUMN_USAGE
where TABLE_NAME = 'yourTableName';

ex:
====

select COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME, REFERENCED_TABLE_NAME

from information_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 information_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:
==============
In some situation we have long table names in our database to use this long table
names we need to use that long names instend we can create a simple short temporary
name to that long name called Allias

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;

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

to run script file:

===================
source <absoultepathof the scriptfile.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 multiple 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 multiple tables .

join are used to create temporary a relation between the tables

based on common column.

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

join conditions are n-1.

join are performed based on a common column datatype.

Mysql supports following the types of joins

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

Inner join

Left 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);

left Join:
---------
left outer join fetches the matched records from both tables and
non-matched records from the left table.

syntax:
------
select specific_columns from table1 alias name left [outer] join table2
aliasename

on(table1.common_column_name=table2.common_column_name);

note:
----
Here table1 act as left 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 left 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 left join and right join
------------------------------------------------------------------------

select d.deptno,d.dname,d.loc,e.empno,e.ename,e.sal,e.deptno
from dept d left 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 selection 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;

Table:
------

select e.name as employee_name,

-> m.name as manager_name
-> from employee as e
-> left join employee as m
-> on e.manager_id=m.id;
+---------------+--------------+
| employee_name | manager_name |
+---------------+--------------+
| Alice | NULL |
| Bob | Alice |
| Charlie | Alice |
| David | Bob |
| Eve | Bob |
| Frank | Charlie |
| Grace | Charlie |
+---------------+--------------+
7 rows in set (0.00 sec)

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 condition in a statement in MySQL.
Below are the different types of operators used in MySQL.

1. Arithmetic Operators
------------------------
In MySQL, arithmetic operators are used to perform the arithmetic operations as
described below.

Arithmetic Operators in MySQL

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

Operator Description
Example
+ Addition of two operands
a + b
– Subtraction of right operand from the left operand
a – b
* Multiplication of two operands
a * b
/ Division of left operand by the right operand
a / b
% Modulus – the remainder of the division of left a % b
operand by the right

Comparison Operators or relation operators

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

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

Comparison Operators in MySQL

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

Operator Description
-------- ------------
> If the value of left operand is greater than that of the value of the right
operand,
the condition becomes true; if not then false.
< If the value of left operand is less than that of a value of the right
operand,
the condition becomes true; if not then false.

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

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

>= If the value of left operand is greater than or equal to the right operand,
the condition becomes true;
if not then false.

<= If the value of left operand is less than or equal to the right operand, the
condition becomes true;
if not then false.

<> If the values of two operands are not equal, the condition 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 operations.

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 negation 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 functions

===========================================
An aggregate function performs a calculation on multiple 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 multiple records and group the result
by one or more column.
It is generally used in a SELECT statement.

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

SYNTAX:
=======

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

exp1,expr2][ORDER BY COLUMNS];

note:
----
The expressions that are not encapsulated within an aggregate function 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;

+--------+----------+
| deptno | SUM(sal) |
+--------+----------+
| 10 | 92000.00 |
| 20 | 80000.00 |
| 30 | 95000.00 |
| 40 | 30000.00 |
+--------+----------+

Or

BY using joins
==============

select d.dname ,sum(e.sal) from emp as e join dept as d on (d.deptno = e.deptno)

group by e.deptno ;

+------------+------------+
| dname | sum(e.sal) |
+------------+------------+
| Accounting | 92000.00 |
| Research | 80000.00 |
| Sales | 95000.00 |
| Operations | 30000.00 |
+------------+------------+

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,sum(sal) as total_sum,
count(empno) from emp group by deptno;

+--------+-----------+--------------+
| deptno | total_sum | count(empno) |
+--------+-----------+--------------+
| 10 | 92000.00 | 2 |
| 20 | 80000.00 | 2 |
| 30 | 95000.00 | 2 |
| 40 | 30000.00 | 1 |
+--------+-----------+--------------+

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;

select deptno,job,sum(sal),count(ename) as total_sum

from emp group by deptno ,job;
+--------+----------+----------+-----------+
| deptno | job | sum(sal) | total_sum |
+--------+----------+----------+-----------+
| 10 | Manager | 50000.00 | 1 |
| 20 | Analyst | 45000.00 | 1 |
| 30 | Salesman | 40000.00 | 1 |
| 20 | Clerk | 35000.00 | 1 |
| 40 | Clerk | 30000.00 | 1 |
| 30 | Manager | 55000.00 | 1 |
| 10 | Analyst | 42000.00 | 1 |
+--------+----------+----------+-----------+

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

NOTE : WHEN GROUPING THE TABLE WE CANNOT USE WHERE CLAUSE

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

The HAVING clause is often used with the GROUP BY clause to filter groups based on
a specified condition.

NOTE:
=====

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

syntax:
-------
SELECT exp1,expr2,aggregate_function(expr) FROM TABLNAME [WHERE][GROUP BY
exp1,expr2]
[Having condition][ORDER BY
COLUMNS];

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

by using where in group by clause

Error:
======

You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'where sal > 8750' at line 1

select deptno ,sum(sal) from emp group by deptno where sal > 8750;

sol:
---

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

+--------+----------+
| deptno | sum(sal) |
+--------+----------+
| 10 | 92000.00 |
| 20 | 80000.00 |
| 30 | 95000.00 |
| 40 | 30000.00 |
+--------+----------+
4 rows in set (0.00 sec)

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

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

String Functions:
------------------

MySQL string functions that allow you to manipulate character string data
effectively.

1. ASCII(str)
--------------
Returns the ASCII value of the leftmost 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 starting at position 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 position 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 leftmost and rightmost len characters from the string str.

Ex:
--
mysql> select left('hello',3);
+-----------------+
| left('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 left(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 function.

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 after trimming white spaces from left, 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-sensitive.

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 starting from position pos,
till 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,exponential):
======================
it will return power of an number.

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

o/p:- 9,

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

VIEWS IN SQL

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

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 time.

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 calculation and hide the complexity.

3) Add extra security layers

A table may expose a lot of data including sensitive data such as personal and
banking information.

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 information, which
should be accessible by the HR department only.

To expose general information such as first name, last name, and gender to the
General Administration (GA) department, you can create a view based on these
columns and grant the users of the GA department the view, not the entire 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
information_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 operation views by creating read only view.
Ex:

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

With Check Option View::

====================
These views will allow DML operation only when where condition is satisfied.
Ex:
Create or replace view Test_V12
As select empno, ename, sal, deptno from emp Where deptno = 30 With check option;

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 multiple tables.
2.DML operations are allowed. 2. DML operations are not allowed.
3.Should not be created using 3. Can be created using arithmetic arithmetic
operations or functions operations or functions 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 Altidor | 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 Altidor','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(distinct(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 often 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 multiple 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.Multiple Row subquery
3.Multiple 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 execution:
----------------------
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
----

Wrong one because any is used with subqueries

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;

**Multiple Row Subquery:

------------------------
When subquery returns multiple rows. It is called multiple row salary.

Note: we should use multiple row operators with multiple row subqueries.

They are three multiple 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 execution:
------------------------
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 multiple column subquery.
We should use in operator with multiple column subqueries.

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

+-------+----------+----------+---------+--------+
| empno | ename | sal | job | deptno |
+-------+----------+----------+---------+--------+
| 1001 | John | 50000.00 | Manager | 10 |
| 1007 | Jennifer | 42000.00 | Analyst | 10 |
+-------+----------+----------+---------+--------+

Exercise:
---------
write a subquery to find second highest salary emp table.

sol:
----
select max(sal) from emp where sal < (select max(sal) from emp);
+-----------------------+
| second_highest_salary |
+-----------------------+
| 50000.00 |
+-----------------------+

Co-RELATED SUBQUERY:
--------------------
When subquery is executed in relation 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?

the average sal should be less than his sal

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

+-------+-------+----------+---------+--------+
| empno | ename | sal | job | deptno |
+-------+-------+----------+---------+--------+
| 1001 | John | 50000.00 | Manager | 10 |
| 1002 | Mary | 45000.00 | Analyst | 20 |
| 1006 | James | 55000.00 | Manager | 30 |
+-------+-------+----------+---------+--------+

Execution:
----------
The above example is a co−related subquery.
In co−related subquery, parent query is executed first and then subquery is
executed in relation 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 Accounting New York 8750
20 Research Dallas 10875
30 Sales Chicago 14400
40 Operations 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 section, 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 section.

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 entire SQL
statement returns nothing.

The syntax for EXISTS is:

SELECT "column_name1"
FROM "table_name1"
WHERE EXISTS
( SELECT *
FROM "table_name2"
WHERE [Condition] )

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 sal 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;

+-------+-------+
| empno | ename |
+-------+-------+
| 1006 | James |
| 1008 | Blake |
| 1001 | John |
| 1002 | Mary |
+-------+-------+

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%' );

+-------+--------+
| empno | ename |
+-------+--------+
| 1003 | Robert |
| 1006 | James |
+-------+--------+

4.Display the employee name, department number, and job title for all
employees whose department location is Dallas.
sol:
---

+-------+--------+---------+
| ename | deptno | job |
+-------+--------+---------+
| Mary | 20 | Analyst |
| Lisa | 20 | Clerk |
+-------+--------+---------+

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

5.Display the employee name and id of all employees who report to Charile.

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

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

dense_rank():
--------------
==============================================
This function assigns a rank to each row within a partition.

The ranks are assigned in a consecutive 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 function assigns a rank to each row within a partition that has gaps.

In this case, ranks are assigned in a non-consecutive 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 distinct 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);

After 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);

after 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_1 AS SELECT * FROM DEPT;

CREATE TABLE DEPT_2 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;
INTERSECTION:
=============
-> common values in two tables
in sql there is no keyword like intersection

so we use emulate

Variable:
=========
A variable allows a programmer to store data temporarily during the execution of
code.

syntax:
-------
set @variablename=initialize the value;

display the data from variable:

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

select @variablename;

Ex:
===
set @name='Sumit deshpade'; --declare and initialize 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 multiple conditions

syntax:
-------
case columnname
when condition 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 condition
=======================================================
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 operations 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 operations 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 until 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:*****
=======================================================

Procedures

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

A procedure (often called a stored procedure) is a collection of pre-compiled SQL

statements stored inside the database.
It is a subprogram in the regular computing language.

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

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

Stored Procedure Features

-------------------------
Stored Procedure increases the performance of the applications. Once stored
procedures are created,
they are compiled and stored in the database.

Stored procedure reduces the traffic between application and database server.
Because the application has to
send only the stored procedure's name and parameters instead of sending multiple
SQL statements.

Stored procedures are reusable and transparent to any applications.

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

applications 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
Declaration_section /*to declare local variables using delcare keyword*/
Executable_section
END && or //
DELIMITER ;

The procedure syntax has the following parameters:

Parameter Name Descriptions

-------------- -----------------------------------------------
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.

Declaration_section It represents the declarations of all

variables.

Executable_section It represents the code for the function

execution 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 overwritten 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 overwritten 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
initialize and display it.

sol:
---
delimiter //
create procedure ex6()
begin
declare a int;
declare b varchar(10); /*declaration of variables */
declare c float;

set a=10;
set b='welcome'; /*initialization variable using set keyword*/
set c=300;

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

end //

delimiter ;
to call the procedure:
----------------------
call ex6();
conditional or control statement:
=================================
if:
---
if condition then
statement-1;
end if;

if-else:
--------

syntax:
-------

if condition 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 condition then
statement-1;
elseif condition then
statement-2;
elseif condition 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;

Function:
---------
In MySQL, a function is a stored program that you can pass parameters into and then
return a value.

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

Syntax
-------

The syntax to create a function in MySQL is:

--------------------------------------------
DELIMITER //

CREATE FUNCTION function_name [ (parameter datatype [, parameter datatype]) ]

RETURNS return_datatype

DETERMINISTIC

BEGIN

declaration_section

executable_section
return datatype;
END;

DELIMITER //

function_name
--------------
The name to assign to this function in MySQL.

parameter
----------
One or more parameters passed into the function. When creating a function,
all parameters are considered to be IN parameters (not OUT or INOUT parameters)
where the parameters can be referenced by the function but can not be overwritten
by the function.

return_datatype
----------------
The data type of the function's return value.

declaration_section
--------------------
The place in the function where you declare local variables.

executable_section
------------------
The place in the function where you enter the code for the function.

DETERMINISTIC:
--------------
it means the function will return the same values if the same arguments are
supplied to it.

to call function:
==================

select function_name(if any parameters pass the value);

Ex:
---
create a function named as addition which takes three parameters
and perform sum of three numbers and return it

sol:
----

delimiter //
create function addition(a int,b int,c int)
returns int

DETERMINISTIC

begin

return (a+b+c);

end //
delimiter ;

to call the function:

----------------------
select addition(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 addition(a,b,c) from test1;

to drop function:
-----------------
drop function function_name;

note:
-----
function name should be userdefined name.
(Don't use predefined function names like string functions,date functions,number
functions etc).

Ex:
---
create a function which returns the max(sal) from emp table.

sol:
----

delimiter //

create function ms() /*default funtion*/

returns decimal

DETERMINISTIC

begin

declare v_m decimal;

select max(sal) into v_m from emp;

return v_m;

end //

delimiter ;

to call the function:

=====================
select ms() as maximumSal;

Exercise:
---------
create a function to display welcome 'ename' from emp table
when pass a ename column it should print welcome smith...etc

sol:
====
delimiter //

create function 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 function:

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

select concat_en(ename) from emp;

select concat_en(' Rajukumar');

TRIGGERS:
=========

A Trigger in MySQL is a special kind of stored operation that gets invoked

automatically when an event has
occurred in the database.

It is a database object which is related to a table in the database and becomes

active when a defined MySQL
statement is initiated on the table.

These DML (Data Manipulation Language) execution operations can be INSERT, DELETE,
UPDATE and triggers
can be called before or after these events.

A trigger is a set of actions that are run automatically when a specified change
operation
(SQL INSERT, UPDATE, or DELETE statement) is performed on a specified table.
Triggers are useful for tasks such as enforcing business rules, validating 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 alterations are done to the data
of a table.

Triggers are simply a SQL code to run before or just after any DML action events on
a particular table in a database.

Syntax
-------

CREATE
[DEFINER = user] TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name
FOR EACH ROW
trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

Parameters
------------
DEFINER clause:
---------------
Identifies the MySQL account that is used for access at trigger initiation time.

trigger_name:
-------------
The name of all triggers should be unique inside a schema.

trigger_time:
-------------
Defines the trigger action time which can be either before or after any row
affected.

trigger_event:
--------------
Specifies the type of operation to activate 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 insertion, updation and deletion
actions on a table.
Like if you have a table and 50 rows are inserted, updated or deleted, then
automatically the trigger
is also invoked for 50 times.

Statement-Level Trigger:
======================
This trigger is invoked only once for a transaction 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/after}

[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 letter.
3) perform multiple 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 audting

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='martin',sal=5000 where empno=1001;

mysql> select * from emp_base;

+-------+--------+------+
| empno | ename | sal |
+-------+--------+------+
| 1001 | martin | 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 | martin | 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 functions

---------------------
MySQL supports a bunch of date utility functions that we can use to handle DATE
efficiently.

NOW()
------
This date function returns the current date and time of the running server
instance.

-- Print current date and time in MySQL

SELECT NOW();

DATE()
--------
This date function extracts and returns the date part from the given DATETIME
value.

-- Print date part from current date and time in MySQL

SELECT DATE(NOW());

CURDATE()
----------
It is a simple date function that fetches the current date of the system running
the MySQL instance.

-- Print the current date in MySQL

SELECT CURDATE();

DATE_FORMAT():
--------------
Sometimes 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() function.

-- Print the difference between two dates

SELECT DATEDIFF('2019-08-04','2019-08-01') diff;

Note:
-----
The DATEDIFF() function 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 function 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 functions 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 Description
--- -----------
%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 multiple times 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 generating 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 times that a subquery needs to be executed

MySQL CTE Syntax

------------------
The basic syntax for creating a Common Table Expression (CTE) in MySQL is as
follows:

WITAH cte_name(
SELECT column1, column2, ...
FROM table_name
WHERE condition
)
SELECT *
FROM cte_name;

Syntax:multiple CTEs within the same query, by separating each CTE definition with
a comma. For example:
WITH cte1 AS (
SELECT column1, column2, ...
FROM table1
WHERE condition1
),
cte2 AS (
SELECT column3, column4, ...
FROM table2
WHERE condition2
)
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 selecting 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;

Explanation:
------------
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",
"quantity", 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),
quantity INT,
revenue DECIMAL(10,2)
);

INSERT INTO sales1 (order_date, product, quantity, 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 writing complex subqueries multiple times, 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 multiple
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 organizational
charts or product categories. By defining a recursive CTE, you can traverse the
hierarchy and perform complex calculations 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 identify 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 descriptive 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 operations 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 operations 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 until 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:*****