Data base technologies

to store data in persistent storage device, then there are 2 styles

1. file----the data is store sequentially, and accessed also sequentially.

2. database—add, delete, modify and read data, becomes very easy as compared to file
handling. hence most of places people prefer using databases, instead of files.

Types of databases

1.SQL databases

• data is stored as structured data.

• it is stored in table format
• we use SQL(structured query language) to manage data
• example—Oracle, mysql, PostgreSQL, SQL server
• these databases stores data table format, hence these are called as RDBMS
2. No SQL
• data is stored in unstructured manner,
• It is stored in json or document format
• usually used in media application
• examples : MongoDB,Cassandra,coutchbaseDB
3. GarphDB
If you need to store and show the data in graph format
examples: neo4j
4. Memory Database
• In certain project data is very small, we need faster access to data, in such cases we
use memory databases.
• These databases are usually used in Research projects.
• in these cases we need back up server also
• examples : MemDB,VoltDB etc

5. disk based data base.

• the data will be stored in the disk where the database is installed
• examples: sqlite3, Access.

In SQL database to manage data the query language is used, Structured Query Language

Types of statements

DQL data query language select

DML Data manipulation insert, delete, update
Language
DDL Data definition language create, alter, drop, truncate
DCL Data control language grant , revoke
TCL transaction control commit, rollback, savepoint
language
Uses of databases

1. reduces redundancy of data

2. sharing of data is possible.
3. availability of data is increased.
4. accessibility of the data is increased.

student

sid sname address mobile

1 Rajat Kothrud 44444
2 Rakesh Akurdi 5545454
3 Rekha Baner 55555
4 Sharmila Kothrud 55556666

To retrieve all rows

select * from student;

to find all students who stays in Kothrud

select * from student where address=’Kothrud’

Keys in databases

1. Primary key: minimal set of columns which identifies the row uniquely , are called as
primary key.
student
corse
student-marks

sid cname marks1 exam date marks2

1 java 99 97
1 c++ 88 97
2 java 55 78
2 c++ 99 34

Room

roomid customerid booking rate of mobile saleman no

date booking number
100 1 2 jan 24 5000 44444 10
100 1 4 jan 24 5000 44444 10
101 1 2 jan 24

movie management

movie screen date of seat no show time price

num num booking
1 1 1 jan 24 1
 1 1 2 nd jan 1

empno ename email mobile adharnum passport pannumer salary desg

num

2. Candidate key:

All possible minimal combinations, that may become primary key, are called as candidate key,

Out of these one chosen will become primary key

and all remaining will become alternate key.

3. unique key--- any single column, which has unique values, is called as unique key

it can contain more than one null values, but not null values will be unique.

4. foreign key--- if we want to enter valid data in a column, and for that we may need to
refer primary key of other table, or primary key of same table, then it is called as foreign
key

5. Alternate key

all candidate keys, which are not selected as primary key are called as alternate key

6. super key

any combination of the columns which identifies the uniquely is called as super key

to install mysql
https://dev.mysql.com/downloads/installer/

using emp , dept

1. to list all the employees with job clerk

select * from emp where job=’CLERK’
2. to list all employees with job clerk or sal >2000
select * from emp where job=’CLERK’ or sal >2000;
3. to list all employees with job manager and sal >2000
select * from emp where job=’CLERK’ and sal >2000;
4. To list all employees with hiredate '1982-12-09'
select * from emp

where hiredate='1982-12-09';

5. To list all employees with sal < 3000 and name =smith
select * from emp
where sal < 3000 and name=’smith’;
6. To list all employees with sal >= 1300 and <=3000
 select * from emp where sal between 1300 and 3000

operators we can use in database.

Arithmetic operators

+, -, *, / , %

Logical operators

and, or, not

Relational operators

<, >, <=, >=, !=, =

[not] between whenever you want to check range of select * from emp where
…and values, the use between…and operator, sal between 1300 and
the values we use are inclusive 3000

[not] in when you want to check multiple values select * from emp where
in single column, then use in sal in(1300,2000,3000)

is [not] null this operator will check for the null select * from emp
values where comm is null;
[not] like like operators will allow you to design select *
patterns from emp
to design patterns we use % and _ where ename like ‘A%’
% matches with 0 or more characters select *
_ matches with 1 character from emp
where ename like ‘_A%’

7. To list all employees with sal not > 1300 and <3000
select * from emp where sal not between 1300 and 3000

8. to list all employees joined in jan-81

select * from emp where hiredate between ‘1981-01-01’ and ‘1981-01-31’

9. to list all employees joined in year 1981

select * from emp where hiredate between ‘1981-01-01’ and ‘1981-12-31’

10. to list all employees with sal is either 1300, 2500 or 3000
select * from emp where sal in (1300,2500,300)
11. to list all employees working as either clerk or manager or analyst
select * from emp where job in (‘CLERK’,’MANAGER’,’ANALYST’)
12. to list all employees not working as either clerk or manager or analyst
 select * from emp where job not in ('CLERK','MANAGER','ANALYST');
13. To list all employee with comm is null
select * from emp
where comm is null;
14. To list all employees with name starts with either m or starts with J
select * from emp where ename like ‘M%’ or ename like ‘J%’
15. list all employees with name starts with A and E at second last position.
select * from emp where ename like ‘A%E_’
16. list all employees with name starts with either A or M and ends with either N or R
select * from emp where ename like ‘A%N’ or ename like ‘M%R’ or ename like 'A%R’ or
ename like 'M%N
17. list all employees with either N at second position or N at third position.
select * from emp where ename like ‘_N%’ or ename like ‘__N%’

REGEXP
. to match any one character
[a-zA-Z] it matches with alphabets
[0-9] it matches with any one digit
* matches the preceding pattern for 0 or more times
+ matches the preceding pattern for 1 or more times
? matches the preceding pattern for 0 or one times
{m} exactly m occurrences
{m,n} it matches with minimum m and maximum n occurrences
{m,} it matches with minimum m and maximum any number of
occurrences
^ it matches the pattern at the beginning of the string
$ it matches the pattern at the end of the string
[^a-z] it matches with any character except a-z
(abc|pqr|mnx) it matches with any one pattern abc or pqr or mnx

1. find all enames that contains A somewhere in the name

select * from emp where ename like ‘%A%’
select * from emp where ename REGEXP ‘A'

2. find all enames that contains A at the beginning in the name

select * from emp where ename like ‘A%’
select * from emp where ename REGEXP ‘^A'
3. find all enames that contains A at the end in the name
select * from emp where ename like ‘%A’
select * from emp where ename REGEXP ‘A$'

4. To list all employees with name starts with either m or starts with J
select * from emp where ename like ‘M%’ or ename like ‘J%’
select * from emp where ename REGEXP ‘^[MJ]’

5. list all employees with name starts with A and E at second last position.
select * from emp where ename like ‘A%E_’
select * from emp where ename regexp '^A.*E.$’
6. list all employees with name starts with either A or M and ends with either N or R
select * from emp where ename like ‘A%N’ or ename like ‘M%R’ or ename like 'A%R’ or
ename like 'M%N
select * from emp where ename regexp ‘^[AM].*[NR]$’;

7. list all employees with either N at second position or N at third position.

select * from emp where ename like ‘_N%’ or ename like ‘__N%’
select * from emp where ename regexp ‘^.N|^..N’
select * from emp where ename regexp ‘^..?N’
8. List all employees with name starts with A and ends with N or Starts with M and ends
with R
select * from emp where ename regexp ‘^A.*N$|^M.*R$’
9. List all employees with name not starting with A and ends with N or not Starts with M
and ends with R
select * from emp where ename not like ‘A%N’ or ename not like ‘M%R’
select * from emp where ename regexp ‘^[^A].*[^N]$|^[^M].*[^R]$’

10.
 to display unique values of a column
select distinct job from emp; It will display unique values
from job column
select distinct job,mgr from It will display unique
emp; combination of job and mgr

To arrange data in sorted order use order by clause

1. to arrange data in ascending order of salary

select * from emp order by sal

2. to arrange data in descending order of salary

select * from emp order by sal desc

3. to arrange data in descending order of salary, if salary is same then arrange on ename
select * from emp order by sal desc, ename

4. to arrange data in descending order of salary, if salary is same then arrange on ename in
descending order
select * from emp order by sal desc, ename desc

5. to find 2 highly paid employees

limit will retrieve first 2 rows
select * from emp order by sal desc
limit 2;
6. to find 3 rd, 4th and 5 th rowhighly paid employees
limit 2,3 will skip 1 st 2 rows and retrieve next 3 rows
select * from emp order by sal desc
limit 2,3

7. generate derived columns

derived columns are the columns which are not there in the table, but can be display by
using some formula or expression
select empno,ename,sal,sal+comm
from emp;

groupby and having clause

1. When you want to divide existing rows in multiple groups based on some column
then use group by
2. grouping can be done on more than one column
3. while filtering data, if the condition is on the existing column, then use the condition
in where clause, otherwise use it on having clause.
4. in group by, in select statement, you can use only columns on which grouping is
done. other columns cannot be used in select statement

examples
1. display sum of sal and min sal for all employees
 select sum(sal), max(sal) from emp

2. list sum of sal and max sal departmentwise

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

3. list sum of sal and min sal count, jobwise

select job,count(*), min(sal),sum(sal)
from emp
group by job
4. find sum of sal, avg sal for each mgrwise
select mgr,sum(sal),avg(sal)
from emp
group by mgr;

5. find sum of sal, sum of netsal, net sal= sal+comm for each department
select deptno,sum(sal),sum(sal+ifnull(comm,0))
from emp
group by deptno

6. find sum sal, avg sal and count departmentwise, jobwise;

select deptno,job,sum(sal),sum(sal+ifnull(comm,0))
from emp
group by deptno,job

7. find how many clerks are in each department

select deptno,count(*),sum(sal)
from emp
where job=’CLERK’
group by deptno
having count(*)>2

8. display department with highest number of employees

select deptno,count(*)
from emp
group by deptno
order by count(*) desc
limit 1;
9. display sum(sal),avg(sal), deptwise, mgrr wise

select deptno,mgr,sum(sal),avg(sal),count(*)

from emp

group by deptno,mgr

order by count(*) desc

If you want to display value of aggregate function, without using group by, then use partition by

partition by clause allows you to display columns which are not involved in group by.

1. to display empno,ename,deptno,sum(sal) for each department.

Select ename,deptno,sum(sal) over (partition by deptno order by deptno)

From emp

Find all ename which has _ in it

select ename

-> from emp

-> where ename like '%\_%';

select ename

-> from emp

-> where ename REGEXP ‘_';

functions used for numeric columns.

abs(num) to convert -ve value into +ve select abs(-3)

3
pow(num,raiseto) power of the number select pow(3,2)
9
floor(num) It will remove all the digits after select floor(312.1567)
the decimal point, and gives the 312
maximum lowest number select floor(312.61567)
312
ceil(num) It will always give the next select ceil(312.1567)
minimum number 313
select ceil(312.61567)
313
round(num,precision) round will round the number upto select round(312.1567,2)
given precision 312.16
select round(312.61267,2)
312.61
truncate(num,precesion) truncate will truncate the select truncate(312.1567,2)
number upto given precision 312.15
select truncate(312.61267,2)
312.61
sqrt(num) it will display square root of given select sqrt(4)
number 2
Functions used with strings

upper(ename) convert all characters in select upper(‘asdf’)

uppercase ASDF
lower(ename) convert all the characters select lower(‘ASDF’)
in small case asdf
concat(s1,s2,s3) concatenate the strings concate(‘xxx’,’.’,’yyy’)
xxx.yyy
concat_ws(“:”,ename,job,sal) concatenate all the values concat_ws(“:”,’xxx’,’yyyy’,1234)
with separator xxx:yyy:1234
format(sal,precision) It will display number in format(312,2)
formatted manner, it will 312.00
display 1000 seperator in format(314356452,2)
the number, and the 31,43,56,452.00
number of digits after
decimal point
substr(string,startpos,number it will display number of substr(‘testing’,3,4)
of character) characters from the stin
starting position, counting
starts with 1
left(s,num of characters) it will display number of left(‘welcome’,3)
characters from left side wel
right(s,num of characters) it will display number of right(‘welcome’,3)
characters from right side ome
length(str) it will display number of length(‘xxxx’)
characters in the string 4
lpad(str,length,character) it will add characters on lpad(‘welcome’,12,’-‘)
the left side of the string so -----welcome
that the total characters
will be = length
rpad(str,length,character) it will add characters on rpad(‘welcome’,12,’-‘)
the right side of the string welcome-----
so that the total characters
will be = length
rtrim(str) it will remove trailing select rtrim(‘ hello ‘)
spaces, i.e the space on hello
the right side
trim(str) it will remove leading and select trim(‘ hello ‘)
trailing spaces hello
ltrim(str) it will remove leading select ltrim(‘ hello ‘)
spaces, i.e the spaces on hello
the left side
instr(str,s1) it will find the position of instr(‘welcome’,’el’)
the first occurrence in the 2
given string
reverse(str) reverse the string reverse(“hello”)
olleh
replace(str,searchstr,newstr) it replace all occurrence of replace(‘testing
the searchstr with newstr string’,’ing’,’aaaa’)
testaaa straaa
insert(str,pos,length,newstr) it overnights from the pos select
character character insert("welcome",2,3,"test")
onward length characters wtestome
by newstr select
insert("welcome",2,0,"test")
wtestelcome
repeate(string,count) it will print the string count repeate(“aaa”,5)
times aaaaaaaaaaaaaaa

1. find email of the employee by concatenating first 3 letters of ename, followed by .

and last 3 characters of job followed by “@mycompany.com”

select empno,ename,concat(left(ename,3),’.’,right(job,3)) email

from emp; -----better approach

select empno,ename,concat(substr(ename,1,3),’.’,substr(job,length(job)-
3,3),”@mycompany.com”)

2. display 3,4,5,6 character of job as a jobcode

select empno,ename,job,substr(job,3,4) jobcode
from emp;

3. display ename, every ename length should be 12, add extra required number of * on
the right side
select empno,ename,rpad(ename,12,’*’)
from emp;

date related functions

now() it display today’s date and time
curdate() it display today’s date
datediff(date1,date2) it displays difference between 2
dates in terms of days
date_format(date1,format) It will display the date in user
required format
Y --- will display 4 digit year
y----will display 2 digit year
M- month name in character
m-month in number
d- date in number
D- display th or st after date
b--- display months in 3 letter (jan,
feb,….)
r ---- to print time in 12 hrs (hh:mm:ss
AM/PM)
h--- to display hour
i----to display minutes
 p—to display AM/PM
date_add(date, interval n unit) it will find the date after given interval
to find the date after 2 months
date_add(curdate(),interval 2 month)
date_sub(date, interval n unit) it will find the date before given
interval
day(date) to find day from the given date
month(date) to find month from the given date
year(date) to find year from the given date
quarter(date) to find the quarter
week(date) to find the week of the date
extract(day from date) to retrieve portion of the date, this is
available in oracle also
select extract(day from curdate());
select extract(month from curdate());
------------- to retrieve month
select extract(year from curdate());
monthname(curdate) will display month name in
characters

dayname(curdate) it prints days, like Monday,

Tuesday,….
last_day(curdate()) to find last day of the give month
Str_to_date(‘22/11/1982’,’%d/%m/%y’) convert given date in mysql format
#####1982-11-22

1. to find portion of the date

select
year(curdate()),month(curdate()),day(curdate()),quarter(curdate()),week(curdate
());
if we have medicine table
(medid,mname,mfgdate,expdate)
1. to find all medicines which are manufactured 3 months before.

select medid,mname,mfgdate
from medicine
where datediff(curdate(),mfgdate)>=90

or

select medid,mname,mfgdate

from medicine
where date_sub(curdate(),interval 3 month)>=mfgdate -------better aproach

2. display expiry date of medicines if it is after 6 months 7 days from mfg date
select medid,mname,mfgdate,date_add(date_add(mfgdate,interval 6 month)
,interval 7 day)
 from medicine
3. find all employees joined in dec 1981

select empno,ename,hiredate,extract(month from hiredate)

-> from emp
-> where extract(month from hiredate)=12 and extract(year from
hiredate)=1981;
4. find date which is after 2 years, 7 months 10 days
select date_add(date_add(date_add(curdate(),interval 2 year),interval 7
month),interval 10 day)

5. find all medicines which will expire after 3 months.

select medid,mname,mfgdate,expdate
from medicine
where datediff(expdate,curdate())>=90

6. to find all employees with experience >41 years

select *, floor(datediff(curdate,hierdate)/365) experience
from emp
where floor(datediff(curdate,hierdate)/365)>=41

7. Write a query to get the distinct Thursday from hiredate in emp table.
select distinct hiredate,dayname(hiredate)
-> from emp
-> where dayname(hiredate)='Thursday';

8. find a particular string occurs how many times in the given string
“saaavaaadaaa” find aaa appears how many times in the given string
saaavaaadaaa 12
svd-----------------3
select floor((length("saaataaayaaa")-
length(replace("saaataaayaaa","aaa","")))/length("aaa"));

case statement
When you want to display some data, based on condition, then we use case
statement
in case statement all the values that you are displaying should be of same type
case when condition then o/p
when condition then o/p
else o/p end alias_name

case columnname when val1 then o/p

when val1 then o/p
when val1 then o/p
else o/p end alias_name
 example
1. if deptno=10 then display accounts, if it 20 then display sales
otherwise display purchase
select empno,ename,deptno, case deptno when 10 then "accounts"
-> when 20 then "sales"
-> else "purchase" end dname
-> from emp;
2. if comm is null or 0 then display “poor performance”
if comm>=300 and <500 then display “ok performance”
if comm>=500 and <1000 then display good performance
otherwise display “excellent performance”

select empno,ename,sal,comm,case when comm is null or comm=0 then "poor

performance"
-> when comm>=300 and comm<450 then "ok performance"
-> when comm>=450 and comm<1000 then "good performance"
-> else "excellent performance" end comment
-> from emp;

Table: Boxoffice
Movie_id Rating Domestic_sales International_sales
5 8.2 380843261 555900000
14 7.4 268492764 475066843
8 8 206445654 417277164
12 6.4 191452396 368400000
3 7.9 245852179 239163000
6 8 261441092 370001000

if rating < 3 then ‘bad’

if rating >=3 and <5 the ok
if rating>=5 and <7 then good
otherwise excellent

select movieid,rating, case when rating<3 then ‘bad’

when rating<5 then ‘ok’
when rating<7 then ‘good’
else ‘excellent’ end status
from boxoffice
Data types in mysql
• Numeric

• Date and Time

• String Types.

Let us now discuss them in detail.

Numeric Data Types

MySQL uses all the standard ANSI SQL numeric data types, so if you're coming to MySQL from a
different database system, these definitions will look familiar to you. The following list shows
the common numeric data types and their descriptions −

• INT − A normal-sized integer that can be signed or unsigned. If signed, the allowable
range is from -2147483648 to 2147483647. If unsigned, the allowable range is from 0 to
4294967295. You can specify a width of up to 11 digits.

• TINYINT − A very small integer that can be signed or unsigned. If signed, the allowable
range is from -128 to 127. If unsigned, the allowable range is from 0 to 255. You can
specify a width of up to 4 digits.

• SMALLINT − A small integer that can be signed or unsigned. If signed, the allowable
range is from -32768 to 32767. If unsigned, the allowable range is from 0 to 65535. You
can specify a width of up to 5 digits.

• MEDIUMINT − A medium-sized integer that can be signed or unsigned. If signed, the

allowable range is from -8388608 to 8388607. If unsigned, the allowable range is from 0
to 16777215. You can specify a width of up to 9 digits.

• BIGINT − A large integer that can be signed or unsigned. If signed, the allowable range is
from -9223372036854775808 to 9223372036854775807. If unsigned, the allowable
range is from 0 to 18446744073709551615. You can specify a width of up to 20 digits.

• FLOAT(M,D) − A floating-point number that cannot be unsigned. You can define the
display length (M) and the number of decimals (D). This is not required and will default
to 10,2, where 2 is the number of decimals and 10 is the total number of digits (including
decimals). Decimal precision can go to 24 places for a FLOAT.

• DOUBLE(M,D) − A double precision floating-point number that cannot be unsigned. You

can define the display length (M) and the number of decimals (D). This is not required
and will default to 16,4, where 4 is the number of decimals. Decimal precision can go to
53 places for a DOUBLE. REAL is a synonym for DOUBLE.

• DECIMAL(M,D) − An unpacked floating-point number that cannot be unsigned. In the

unpacked decimals, each decimal corresponds to one byte. Defining the display length
(M)

Date and Time Types

The MySQL date and time datatypes are as follows −

 • DATE − A date in YYYY-MM-DD format, between 1000-01-01 and 9999-12-31. For
example, December 30th, 1973 would be stored as 1973-12-30.

• DATETIME − A date and time combination in YYYY-MM-DD HH:MM:SS format, between

1000-01-01 00:00:00 and 9999-12-31 23:59:59. For example, 3:30 in the afternoon on
December 30th, 1973 would be stored as 1973-12-30 15:30:00.

• TIMESTAMP − A timestamp between midnight, January 1st, 1970 and sometime in 2037.
This looks like the previous DATETIME format, only without the hyphens between
numbers; 3:30 in the afternoon on December 30th, 1973 would be stored as
19731230153000 ( YYYYMMDDHHMMSS ).

• TIME − Stores the time in a HH:MM:SS format.

• YEAR(M) − Stores a year in a 2-digit or a 4-digit format. If the length is specified as 2 (for
example YEAR(2)), YEAR can be between 1970 to 2069 (70 to 69). If the length is
specified as 4, then YEAR can be 1901 to 2155. The default length is 4.

String Types

Although the numeric and date types are fun, most data you'll store will be in a string format.
This list describes the common string datatypes in MySQL.

• CHAR(M) − A fixed-length string between 1 and 255 characters in length (for example
CHAR(5)), right-padded with spaces to the specified length when stored. Defining a
length is not required, but the default is 1.

• VARCHAR(M) − A variable-length string between 1 and 255 characters in length. For

example, VARCHAR(25). You must define a length when creating a VARCHAR field.

• Nvarchar stores Unicode or Non-English character data types, and it

can contain a maximum of 4000 characters. It supports ASCII values as
well as special characters. To support multiple languages, nvarchar is a
must.

• BLOB or TEXT − A field with a maximum length of 65535 characters. BLOBs are "Binary
Large Objects" and are used to store large amounts of binary data, such as images or
other types of files. Fields defined as TEXT also hold large amounts of data. The
difference between the two is that the sorts and comparisons on the stored data are
case sensitive on BLOBs and are not case sensitive in TEXT fields. You do not specify a
length with BLOB or TEXT.

• TINYBLOB or TINYTEXT − A BLOB or TEXT column with a maximum length of 255

characters. You do not specify a length with TINYBLOB or TINYTEXT.

• MEDIUMBLOB or MEDIUMTEXT − A BLOB or TEXT column with a maximum length of

16777215 characters. You do not specify a length with MEDIUMBLOB or MEDIUMTEXT.

• LONGBLOB or LONGTEXT − A BLOB or TEXT column with a maximum length of

4294967295 characters. You do not specify a length with LONGBLOB or LONGTEXT.
 • ENUM − An enumeration, which is a fancy term for list. When defining an ENUM, you are
creating a list of items from which the value must be selected (or it can be NULL). For
example, if you wanted your field to contain "A" or "B" or "C", you would define your
ENUM as ENUM ('A', 'B', 'C') and only those values (or NULL) could ever populate that
field.

constraints in the table

These are of 2 types

1. field level constraint- the constraint which you need to write

immediately after column declaration are column level constraints
2. Table level constraint--- the constraints which are based on multiple
columns, or dependent on some other column, then it is good to use
table level constraint.

constraints
Not null field level It does not allow to add null
values in the table

default field level If user does not specify any

value, then instead null
what value can be added in
the column is defined with
default constraint
salary decimal(9,2) default
10000

primary key table level It does not allow null values

and duplicate values,
so values should be unique
and not null
In a table there can be only
one primary key.

unique field level It does not allow duplicate

values, but allows any
number of null values

check table level Before entering data in the

constraint column, if you want to
 check some condition, and
if the condition is true, then
only store the data,
otherwise show error

foreign key table level while entering value in the

constraint column, if you want to refer
on delete the value of primary key of
<action> the same table or different
on table
update<action>

create table myemp(

empid int primary key,
ename varchar(30) not null,
sal float(9,2) check(sal>0),
desg varchar(10) default 'Analyst');
To delete the existing table

drop table myemp;

create table student( create table stud_marks(

sid int primary key, studid int,

sname varchar(30), cname varchar(20),

address varchar(20) marks int check(marks between 0 and 100),

) primary key(studid,cname),

constraint fk_nm foreign key(studid)

create table course(
references student(sid)
cname varchar(20) primary key,
duration int check(duration >0), on delete cascade
description varchar(20) on update cascade,

constraint fk_cnm foreign key(cname)

) references course(cname)

on delete cascade

on update cascade
 )

create table category(

cid int primary key,

cname varchar(20),

description varchar(30))

create table product(

pid int primary key,

pname varchar(20),

qty int,

catid int,

constraint fk_cid foreign key(catid) references category(cid)

on delete set null

on update cascade)

on delete (action)

on update (action)

action values.

cascade if delete from parent table, then delete matching rows from child table,
if update from parent table, then change the value is child table also
set null if delete from parent table, then set value to null in matching rows from child
table,
if update from parent table, then set value to null in matching rows in child
table
no action if on delete and on update clause is not used, then values from the parent
table can be deleted only if, the corresponding value does not exists in the
child table

DML statements

insert insert into <table> values(<values for all insert into mytable
columns>) values(12,’xxx’,’pune’)
 insert into<table>(list of columns) values( insert into mytable(id,name)
<values for specified columns>) values(13,’yyy’)
update update <table> update mytable
set fiedl1=val1,field2=val2, ……. set name=’ccc’,addr=’yyy’
where <condition> where id=12
if where cluse is not used then it will update all
the rows
delete delete from <table> delete from mytable
where <condition> where id=12
if where cluse is not used then it will delete all
the rows
to insert INSERT INTO tbl_name insert into
multiple (a,b,c) salesman(sid,sname,address)
rows in a VALUES values(11,'xxx','Pune'),
(1,2,3),
table (12,'yyy','pune'),
(4,5,6),
(7,8,9); (13,'zzzz','mumbai');

to delete the table ----drop table will delete all the rows and will also delete table from the
databse

drop table <table name>

drop table emp;

to delete all the rows from the table

truncate table <table name>---it will delete all the rows from the table, but keep empty table as
it is.

To set autocommit off

set autocommit=0;

To set autocommit on

set autocommit=1;

Trucate table

truncate table product

delete from product;

Tucate delete
it is a DDL, so it is autocommit it is dml statement, changes need to
commited
rollback is not possible rollback is possible
 we can not use where cluse where clause can be used

alter table

To change the table structure use Alter table <table name>

add column alter table mytable alter table mytable

add addr int first|after id; add addr11 varchar(20) after id

alter table mytable

-> add addr11 varchar(20)
after id,
-> add addr12 int first;
delete the column alter table <table name> alter table mytable
drop column <colname> drop column addr11
modify the existing alter table <table name> alter table mytable
column modify <existing column name> modify location varchar(50)
<modified details>
alter table mytable
modify location varchar(50)
not NULL

rename the column alter table <table name> alter table mytable
CHANGE COLUMN old_name change location mylocation
new_name varchar(50)
column_definition

add constraint alter table < table name >

add constraint <cn> foreign key
……

alter table < table name >

add primary key(id)

delete constraint alter table < table name >

drop constraint <cn>
rename table alter table <old table name> alter table mytable
rename to <new table name> rename to mytable11

ALTER TABLE User

ADD CONSTRAINT userProperties

FOREIGN KEY(properties)

REFERENCES Properties(ID)
create owner table to store (ownerid, oname,address)

create vehicle table to store(vid,vname, model,company,ownerid,sid)

ownerid is a foreign key references owner table (ownwerid)

sid is a foreign key references salesman table (sid)

create a table salesman to store (sid, sname, address)

create table owner( create table vehicle(

ownerid int primary key , vid int primary key ,
oname varchar(20), vname varchar(20),
oaddress varchar(20)) model varchar(20),
company varchar(20),
create table salesman( ownerid int ,
sid int primary key, sid int ,
sname varchar(20), constraint f_ownerid foreign key (ownerid)
saddrerss varchar(20)) references owner(ownerid)
on delete set null
on update cascade ,
constraint f_sid foreign key (sid) references
salesman(sid)
on delete set null
on update cascade );

1. to list all employees from dept 10

select deptno,empno,ename
from emp
where deptno=10;
2. to list all employees who are working in smith’s department

select *
from emp
where deptno=( select deptno
from emp
where ename=’smith’
);

nested queries
1. simple nested query
when output of one query is dependent on output of another query, then we use nested
query,
outer query is called as parent query, and inner query is called s child query
if the child query is not dependent on parent query data, then it is called as simple
query.
2. co related query

when output of one query is dependent on output of another query, then we use nested
query,

outer query is called as parent query, and inner query is called s child query

if the child query is dependent on parent query data, then it is called as co-related query.

examples
1. to list all employees with sal < avg(sal) of departmrnt 10?
select * from emp
where sal < (select avg(sal)
from emp
where deptno=10)
2. display all employees who are working in ward’s department
select * from emp
where deptno=(select deptno
from emp
where ename=’ward’)
3. to list all employees with sal < avg sal of wards department
select *
from emp
where sal <(select avg(sal)
from emp
where deptno=(select deptno
from emp
where ename=’ward’))

4. to display all employees with sal > smith’s sal and jones sal
select *
from emp
where sal > all(select sal
from emp
where ename in(‘smith’,’jones’))

5. to display all employees with sal > either smith’s sal or jones sal
select *
from emp
where sal > any(select sal
from emp
where ename in(‘smith’,’jones’))

6. to display all employees who are working either smith’s dept or jones dept
 select *
from emp
where deptno in (select deptno

from emp

where ename in(‘smith’,’jones’))

7. to find all employees with sal < avg sal of its own department

select *

from emp e

where sal <(select avg(sal)

from emp m

where m.deptno=e.deptno)

8. to find all employees whose sal < its own mgr sal
select *
from emp e
where sal <(select sal
from emp m
where m.empno=e.mgr)

9. list all employees who are working on accounting department

select *

from emp e

where deptno=(select deptno

from dept

where dname=’Accounting’)

10. display all departments in which no employees are there

select *
from dept d
where not exists (select *
from emp e
where e.deptno=d.deptno)

11. display all employees who are not mgr of any employee
select *
from emp e
where not exists (select *
from emp m
where m.mgr=e.empno)

12. list all faculties who are not assigned to any course
 select * from faculty f
where not exists (select * from course c where f.fid=c.fid)
13. list all faculties who are assigned to some course
select * from faculty f
where exists (select * from course c where f.fid=c.fid)

14. list all rooms which are not assigned to any course.
select * from room r
where not exists (select * from course c where r.rid=c.rid)

15. list all courses for which no room is assigned

select *
from course
where rid is null
16. update sal of smith to jones sal+2000
update emp
set sal=(select sal from emp where ename=’jones’)+2000
where ename=’smith’

17. delete all records who are working in either smiths dept or jones dept
delete from emp
where deptno in (select deptno
from (select * from emp ) e
where e.ename in (‘smith’,’jones’))
18. create table emp_10
as
(select * from emp
where deptno=10)

19. create table emp_10

as
(select * from emp
where 1=2)
20. find all employees with sal > smith’s sal and sal < ward’s sal
select * from emp
where sal between (select sal from emp where ename=’smith’) +1and (select sal
from emp where ename=’ward’)-1

21 . list all employees who are working in jones dept and sal >2000

select * from emp

where deptno=(select deptno from emp where ename=’jones’) and sal>2000

Joins in table

1. When you want to display values from more than one table then use joins
2. if we are joining n tables then minimum n-1 join conditions are needed

Types of joins
cross join when every row in the table emp is joined with every row from
other table, then it is called as crossjoin

inner join If we add join condition in the join query, then it is called as
1. equi join inner join
2. non equi join 1.if the condition is based on = sign then it is called as equi
3. self join join
2. if the condition is based on operator other than = then, it is
called as non equijoin
3. If in inner join we combine a table with itself, then it is
called as self join

outer join when you want to retrieve matching as well as non matching
1. left outer join rows from multiple tables then use outer join
2. right outer 1. if we want nonmatching rows from the table which is
join on the left side in from clause, then use left outer join
3. full outer join 2. if we want nonmatching rows from the table which is
on the right side in from clause, then use right outer
join
3. if we want nonmatching rows from both side tables
then use full outer join, to use full outer join we need
to write union query in mysql.

1. to display empno, deptno,dname for all employees

select select
e.empno,e.deptno,d.dname e.empno,e.deptno,d.dname
from emp e inner join dept d
from emp e, dept d
on e.deptno=d.deptno;

where e.deptno=d.deptno;

2. display all employees and their manager names

 select select
e.empno,e.ename,e.mgr,m.e e.empno,e.ename,e.mgr,m.empno,m.ename
mpno mgrno. m.ename from emp e inner join emp m on
mgrname e.mgr=m.empno;
from emp e, emp m
where e.mgr=m.empno;

3. to display empno, deptno,dname for all employees with sal>2000

select select
e.empno,e.deptno,d.dname e.empno,e.deptno,d.dname
from emp e inner join dept d on
from emp e, dept d
e.deptno=d.deptno
where sal>2000;
where e.deptno=d.deptno
and sal>2000;

4. to display empno, deptno,dname for all employees with deptno is either 10 or 20

select select empno,e.deptno,dname

empno,e.deptno,dname from emp e inner join dept d
on e.deptno=d.deptno
from emp e,dept d
where e.deptno in (10,20)

where e.deptno=d.deptno
and e.deptno in (10,20)

5. to display empno,ename,sal, and grade

select empno,ename,sal,grade,losal,hisal
from emp e, salgrade s
where sal between losal and hisal;

6. to display courses name along with room name

 select cid,cname,c.rid,r.rid,rname
from course c, room r
where c.rid=r.rid;
7. to display courses name along with faculty name
select cid,cname,c.fid,fname
from course c,faculty f
where c.fid=f.fid;
8. list courses, with room name and faculty name

select cid,cname,fname,rname

from course c,room r,faculty f

where c.rid=r.rid and c.fid=f.fid

select select cid,cname,c.rid,r.rid,rname,fname

cid,cname,c.rid,r.rid,rname from course c inner join room r on c.rid=r.rid
inner join faculty f on c.fid=f.fid
from course c, room
r,faculty f

where c.rid=r.rid and

c.fid=f.fid;

9. to list all employee name and department name , along with departments which do
not have any employee

select select
empno,ename,e.deptno,d.dept empno,ename,e.deptno,d.dept
no,dname no,dname

from dept d left join emp e on from emp e right join dept d on
e.deptno=d.deptno; e.deptno=d.deptno;
10. find all the employees name and department name, also display employees for
whom no dept is assigned, and also display departments in which no employee is
there?
select empno,ename,e.deptno,d.deptno,d.dname
from emp e left join dept d on e.deptno=d.deptno
union
select empno,ename,e.deptno,d.deptno,d.dname
from emp e right join dept d on e.deptno=d.deptno;

11. to display course name and faculty name, for all courses, and also display faculty
names which are not assigned to any course
select cid,cname,fname
from course c right join faculty f on c.fid=f.fid;

12. to display course name and room name, for all courses, and also display room
names which are not assigned to any course
select cid,cname,rname
from course c right join room r on c.rid=r.rid;
13. to display course name and faculty name, for all courses, and also display faculty
names which are not assigned to any course, and also display courses for which no
faculty is assigned.
select cid,cname,fname
from course c right join faculty f on c.fid=f.fid
union
select cid,cname,fname
from course c left join faculty f on c.fid=f.fid;
14. to display all employees, who are not assigned to any department, and all
departments in which no employees are assigned
select e.empno,e.ename,e.deptno,d.deptno,dname
from emp e left join dept d on e.deptno=d.deptno
where d.dname is null
union
select e.empno,e.ename,e.deptno,d.deptno,dname
from emp e right join dept d on e.deptno=d.deptno
where e.ename is null;
15. to display course name for which no faculty is assigned and display all faculty
names which are not assigned to any course,

select cid,cname,fname

from course c left join faculty f on c.fid=f.fid

 where f.fname is null

union

select cid,cname,fname

from course c right join faculty f on c.fid=f.fid

where c.cname is null;

16. find all employees for which no dept is assigned and all department for which no
employee is assigned
select empno,ename,e.deptno,d.deptno,d.dname
from emp e right join dept d on e.deptno=d.deptno
where e.ename is null
union
select empno,ename,e.deptno,d.deptno,d.dname
from emp e left join dept d on e.deptno=d.deptno
where d.dname is null;

17. find all faculties which are not assigned to any course and find all rooms which are
not assigned to any course

select c.cid,c.cname,f.fid,f.fname,null rid,null rname

from course c right join faculty f on c.fid=f.fid

where c.cname is null

union

select c.cid,c.cname,null,null,r.rid,r.rname

from course c right join room f on c.rid=r.rid

where c.cname is null

Table: Movies
Id Title Director Year Length_minutes Release date
1 Toy Story John Lasseter 1995 81
2 A Bug's Life John Lasseter 1998 95
3 Toy Story 2 John Lasseter 1999 93
4 Monsters, Inc. Pete Docter 2001 92
5 Finding Nemo Andrew Stanton 2003 107
6 The Incredibles Brad Bird 2004 116

movieid is primary key

year >1970

length_minutes>15 min and < 240 mins

Release_date >1990-12-31 default ‘1991-01-01’

create table movie(movieid int primary key,

title varchar(20),

director varchar(20),

year int chek(year>1990),

length_min int check(length_min between 15 and 240)

release_date date check(release_date>’1990-12-31’) default ‘1991-01-01’)

insert into movie values(1,'Toy story','John Lasseter',1995,81,'1995-03-02');

insert into movie values(2,'A Bugs Life','John Lasseter',1998,95,'1998-03-02');

Table: Boxoffice
Movie_id Rating Domestic_sales International_sales
5 8.2 380843261 555900000
14 7.4 268492764 475066843
8 8 206445654 417277164
12 6.4 191452396 368400000
3 7.9 245852179 239163000
6 8 261441092 370001000
9 8.5 223808164 297503696

movie(mid, title, director, year, length_in_min, releasedate)

boxoffice(movieid, rating, domestic_sales, international_sales)

1. display movieid, title, rating and domestic_sales of all the movies.

select m.mid,m.title,b.rating,b.domestic_sales
from movie m,boxoffice b
where m.mid=b.movieid
2. display movieid, title, rating and domestic_sales of all the movies in which rating > 4
and domestic_sale is > international_sale.
select m.mid,m.title,b.rating, b.domestic_sales
from movie m,boxoffice b
where m.mid=b.movieid and b.rating>4 and b.domestic_sale> b.international_sale;
3. display all movies for which no rating is assigned
select * from movie m
where not exists (select * from boxoffice b where b.movieid=m.mid and rating is not
null)
4. display all movie names with rating < average rating and director name starts with J

select * from movie

where director like ‘J%’ and mid in (select movieid
from boxoffice where rating<(select avg(rating) from boxoffice))

5. display all movies which are released in jan

select * from movies where month(realeasedate)=1;

Views in myasql

create view mgr10

as
select * from emp where deptno=10;

to restrict the dml operation via view use with check option

create view mgr10

-> as

-> select * from emp

-> where deptno=10

-> with check option;

 to stop DML operations on view, use with read only, but with read only works in oracle
and not in mysql

create view mgr10

as

select * from emp

where deptno=10

with read only

When you want to provide limited access to the existing data, then we create views

views are of 2 types

1. views
a. for view no separate memory is allocated for storing data, it only stores the base
query.
b. only base query will be stored, and any statement on the view will use base query
to get the data, because of that we always get UpToDate data in view
c. if view contains all not null columns of the single base table and if it is not read
only view, then we can use all DML operation(insert, delete, update) on the view
d. if the view is based on joins, aggregate functions, group by statement or union of
multiple queries, then by default the views are readonly

select * from emp e

where sal<(select avg(sal) from emp m where m.mgr=e.mgr)

create a view fac_room

as
select cid,cname,fid,fname,null,null
from course c right join faculty f on f.fid=c.fid
where c.cname is null
union
select cid,cname,null,null,rid,rname
from course c right join room r on r.rid=c.rid
where c.cname is null

uses of views:
1. Hide complexity of the queries ( joins, aggregate functions nested queries)
2. To give restricted access to few columns or rows from tables
3. Hide table names, to increase the security of data.

2. Materialized view
 a. Views for which the first time the base query will get executed and then the
output will be stored in a temporary table in RAM, within the session, the data will
be retrieved from the RAM
b. you may not get uptodate data in materialized view.
c. When your data is history data/ non changeable data, then use materialized view

create materialized view myview

as

select * from emp;

to drop view

drop view myview

to get the 3rd highest salary

select *

-> from emp s

-> where 2=(select count(*)

-> from (select distinct sal from emp) e

-> where e.sal>s.sal)

-> order by sal;

to get 3 topmost salaried employee

select *

-> from emp s

-> where 3>select count(*)

-> from (select distinct sal from emp) e

-> where e.sal>s.sal)

-> order by sal;

to get 3 bottommost salaried employee

select *

-> from emp s

-> where 3>select count(*)

-> from (select distinct sal from emp) e

-> where e.sal<s.sal)

-> order by sal;

indexes in mysql

Indexes are of 2 types

1. clutsered index-→
a. there is only one clustered index,
b. it is stored along with table data,
c. it does not require extra space to store index file
d. the data is stored in the sorted order based on clustered index
2. non clusterd index→
• it is stored outside the table,
• there can be any number of non clustered index files,
• every index file stores, key and the position in the table

Types of indexes in mysql

1. primary key index- when we add primary key constraint in the table, this index is
automatically created, data in the table is sorted based on this index
2. unique index- When you add unique constraint in the table, unique index is
automatically gets creates, it does not allow to add duplicate not null values in the
column.
if unique constraint is not assigned in the table, but if you create unique index then also
duplicate values will not be allowed in the column
3. full text index---usually it is used on tex/tinytex/mediumtext/longtext columns, in which
we need to search jargons, then use full text indexes, usually these are used in search
engines.
4. regular index---if you create a index on a table, then it is regular index
5. spatial index---- if the geolocationof data is stored in the table, then we use spatial
indexes.
6. descending index---while creating indexes if you use desc keyword then those indexes
are called as descending indexes.

to create index

create index sal_idx

on emp(sal)

to drop index

drop index sal_idx

to list all indexes available on the table

SHOW INDEXES FROM customers

to find which indexes are used by the query or to suggest using of which indexes in query

EXPLAIN SELECT *

FROM

customers

WHERE

contactFirstName LIKE 'A%

 OR contactLastName LIKE 'A%';

write a command to create descending index on job, if job is same then arrange the data on sal

create index idx_job

on emp(job desc,sal)

MySQL Engine INNODB supports ACID property

A—automicity-→ every transaction will get executed as a single unit

begin transaction

withdraw amt form source

deposit amt in the dsestination

end transaction

c—consistency---correctness of data will be there after every transaction completes

I ----isolation---- every transaction will get executed in isolation,`

intermediate changes are visible only to the user who is performing the transaction,

these changes will visible to other users, when the transaction is commited

D- durability------It will show the performance for longer period of time

Transaction control language

commit, rollback, savepoint

emp

10 records

commit

insert 2

update 1

commit

delete 1

rollback
10 records

commit

insert-3

update 2

savepoint A

delete 1

insert-3

update 2

savepoint B

insert-2

delete 1

rollback to B

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

12 rows

insert -3

delete 1

create table

insert- 4

delete 1

rollback

window functions

row_numer() It assigns a unique sequential number to each row, starting with 1,

according to the ordering of rows within the window partition.

select empno,ename,sal,row_number() over (order by sal desc) rownum

from emp e ----- it wil generate only one window in table and assign
numbers 1,2,3,4…..
select empno,ename,sal,row_number() over (partition by deptno order by
sal desc) rownum
from emp e ----- it wil generate one window for each department in table
and assign numbers 1,2,3,4….. in each window
rank() The rank() function will assign the same rank to the same
values i.e. which are not distinguishable by ORDER BY. Also, the
next different rank will not start from immediately next number but
there will be a gap i.e. if 4th and 5th employees have the same
salary then they will have the same rank 4, and 6th employee
which has a different salary will have a new rank 6.
 dense_rank() The dense_rank function is similar to the rank() window
function i.e. same values will be assigned the same rank, but the
next different value will have a rank which is just one more than
the previous rank, i.e. if 4th and 5th employee has the same
salary then they will have the same rank but 6th employee, which
has different salary will have rank 5,

row_number, rank, dense_rank,lag, lead,first_row

to find highly paid employee in each department

select *

from (SELECT empno,ename,sal,deptno,row_number()

over( partition by deptno ORDER BY sal DESC) AS rn,

dense_rank() over (partition by deptno order by sal desc

) drn from emp) e

where e.drn=1;

lead function will give you the next value within frame, lag function give you previous value with
frame.

select empno,ename,sal,lead(sal,1) over (order by sal) leaddata, lag(sal,2) over (order by sal)
lagdata,first_value(sal) over (order by sal)

-> from emp;

PL_SQL—(Procedural language -Structured Query Language)

procedure any block of code , which has business logic is called as procedure
function any block of code , which has business logic, and returns one values, is
called as functions.
these can be used in select clause and where clause in SQL
triggers any block of code , which has business logic, and gets called on some
users action automatically, then it is called as trigger
exception While executing procedures if any error occurs, we use exceptions
cursors When you want to traverse through all rows one by one, and perform
some action on each row, on by one, then use cursors

Types of parameters to the procedure

in these are read only parameters.

these are default parameters.
these are used to send the input value to the procedure
out these are write only parameters
these are used to get the output from the procedure
 inout these are both read and write parameters, we can send the value to the procedure,
and inside procedure we may change the value of the parametr

Why we use PL SQL

1. we can hide table names from the developer of the middleware application, which
increases the security of the database.
2. For a particular task, if we need to execute many queries, then we may wrap these
queries in a procedure, and call the procedure from middleware application, once,
execute all the queries, complete the task and go back, this will reduce the network
traffic, also improves performance efficiency of the middleware application. so it
reduces the interaction between middleware program and database.
3. If any of the query is complex, then we may hide the query inside the procedure
4. Procedures will also reduce the network traffic.

delimiter //

create procedure <procedure name>(parameters…)

begin

declaration of variable;

statement1;

statement2;

end//

delimiter ;

call <procedurename>();

1. to insert record into dept table

delimiter //
create procedure insertdept(in did int,dnm varchar(20),dloc varchar(20))
begin
insert into dept values(did,dnm,dloc);
end//
delimiter ;

call insertdept(10,’admin’,’pune’)

2. write a procedure to accept eid, sal and job from user as i/p and update sal and job of
the employee in emp table
delimiter //
create procedure updateemp(eid int,esal float(9,2),ejob varchar(20))
begin
update emp
set sal=esal,job=ejob
where empno=eid;
end//
delimiter ;
 call updateemp(7902,6666,’QA’);

3. write a procedure finddata, to get sal and comm of the employee

delimiter //
create procedure findjob(eid int,out esal float(9,2),out ecomm float(9,2))
begin
select sal,comm into esal,ecomm
from emp
where empno=eid;
end//

delimiter ;

call findjob(7902,@s,@c)
select @s,@c

in above example, select … into statement can be used only inside pl sql blocks, the
select query should return single row as output. number of column names before into
and number of variables after into should be same.
@s and @c are session variables. these variables will remain available till the time you
logout.
4. write a procedure to find number of employees and maximum netsalary for the given
department.
net sal =sal+comm
delimiter //
create procedure findemp(in edid int, out cnt int,out maxsal float(9,2))
begin
select count(*),max(sal+ifnull(comm,0)) into cnt,maxsal
from emp
where deptno=edid;
end//
delimiter ;

call findemp(10,@c,@ms)

5. write a procedure which will accept a number and increment a number by 10

delimiter //
create procedure incrementnum(inout cnt int)
begin
set cnt=cnt+10;
select cnt;
end//
delimiter ;

set @c=5
call incrementnum(@c)
select @c;

6. write a procedure to display all employees in given department and sal >1500.
 delimiter //
create procedure getempdata(in edid int,in esal float(9,2))
begin
select *
from emp
where deptno=edid and sal>esal;
end//
delimiter ;

call getempdata(10,1500);

7. write a procedure to find all employees along with dname with sal>2000
delimiter //
create procedure findempdetails(esal float(9,2))
begin
select empno,ename,sal,e.deptno,dname
from emp e,dept d
where e.deptno=d.deptno and sal >esal;
end//
delimiter ;

call findempdetails(2000);

8. display feedback based on comm

if comm is null or 0 then display “poor performance”
if comm <=300 then display ‘ok performance”
if com >301 and <=500 then display good performance
else display excellent performance.

if condition then if condition then

statements; statements;
else elseif condition then
statements statements
end if; else
statements
end if;

delimiter //
create procedure getRemark(eid int,out remark varchar(50))
begin
declare vcomm float(9,2) default 0;
select comm into vcomm
from emp
where empno=eid;
if vcomm is null or vcomm=0 then
set remark=’poor performance’;
elseif vcomm<= 300 then
set remark=’ok performance’;
 elseif vcomm<= 500 then
set remark=’good performance’;
else
set remark=’excellent performance’;
end if;
end//

end//
delimiter ;

9. write a procedure to find netsal of the given employee and find the remark, if
netsal <1000 “less”
if >=1000 and <2000 then ‘ok’
if netsal >=2000 and < 3000 then ‘good’
otherwise better
display remark inside the procedure

netsal= sal+comm

delimiter //
create procedure findNetsal(eid int,out remark varchar(50))
begin
declare vsal,vcomm,vnetsal float(9,2);
select sal,comm into vsal,vcomm
from emp
where empno=eid;

set vnetsal=vsal+ifnull(vcomm,0);

if vnetsal<1000 then
set remark ='less';

elseif vnetsal<2000 then

set remark='ok';

elseif vnetsal<3000 then

set remark='good';

else
set remark='better';

end if;
select eid,vsal,vcomm,vnetsal,remark;
end//

delimiter ;
10. write a procedure getdiscount to find discount % and discounted amount from product
table for the given product
if price < 50 then 3%
if price >=50 and <80 7%
if price >=80 and < 100 8%
otherwise 12%
display pid,pname,price,discount percentage and discount amount

delimiter //
create procedure getdiscount(dpid int, out discount float(4,2))
begin

declare vpname varchar(20) default '';

declare vprice float(9,2);
select pname, price into vpname,vprice
from product
where pid=dpid;

if vprice<50 then
set discount=0.03;
elseif vprice<80 then
set discount=0.07;
elseif vprice <100 then
set discount=0.08;
else
set discount=0.12;

end if;
select dpid , vpname,vprice,vprice-(vprice*discount),discount;

end//
delimiter ;
In PLSQL there are 3 loops

While expression do This is top tested loop, will repeat statements till the
Statements condition is true
End while;

REPEAT This is bottom tested loop, will repeat statements until the
statements; given condition is false
UNTIL expression
END REPEAT

Label1:Loop This is infinite loop , will continue execution till leave

If condition then statement gets executed, leave statement is same as break
Leave Label1 statement, it forcefully stops the loop.
End if
endloop In this loop you may use iterate statement, it is similar to
continue statement in java,
It will transfer the control to the beginning of the loop.

1. Write a procedure which accepts start and stop values and display all numbers between
start and stop
2. Example displaydata(10,20) o/p 10,11,12,13,14,15……20

Delimiter //

Delimiter //

Create procedure displaydata(in start int,stop int)

Begin

Declare cnt int;

Declare str varchar(100) default '';

Set cnt=start;

While cnt<=stop do

set str=concat(str,cnt,',');

Set cnt=cnt+1;

End while;

set str=substr(str,1,length(str)-1);

Select str;

End//

Delimiter ;Delimiter ;

3. Write a procedure to accept a number from user and display its factorial
Delimiter //
Create procedure displayfactorial(in num int,out fact int)
Begin
 Declare start int default 1;
Set fact=1;
While start<=num do
Set fact=fact*start;
Set start=start+1;
End while;
End//

Using repeat until loop

1. Write a procedure which accepts start and stop values and display all numbers between
start and stop(use repeat …until loop)

Delimiter //

Create procedure displaydatarepeat(in start int, in stop int)

Begin

Declare cnt int default start;

Declare str varchar(100) default '';

Repeat

Set str=concat(str,cnt,',');

Set cnt=cnt+1;

Until cnt > stop

End repeat;

Set str=substr(str,1,length(str)-1);

Select str;

End//

Delimiter ;

2. Write a procedure to find factorial of a number(repeat until)

Delimiter //
Create procedure displayfactorialrepeat(in num int, out fact int)
Begin
Declare start int default 1;
Set fact=1;
Repeat
Set fact=fact*start;
Set start=start+1;
Until start>num
End repeat;
Select fact;
End//

Delimiter ;

Loop …endloop
3. Write a procedure which accepts start and stop values and display all numbers between
start and stop(use loop …end loop)
Delimiter //
Create procedure displaydataloop(in start int,in stop int)
Begin
Declare str varchar(100) default '';
Declare cnt int default start;
L1:Loop
Set str=concat (str,cnt,',');
Set cnt=cnt+1;
If cnt>stop then
Leave l1;
End if;
End loop;
Set str=substr(str,1,length(str)-1);
Select str;

End//
Delimiter ;
4. Write a procedure to find factorial of a number using loop …end loop;
Delimiter //
Create procedure displayfactorialloop(in num int,out fact int)
Begin
Declare start int default 1;
Set fact=1;
L1:loop
Set fact=fact*start;
Set start=start+1;
If start >num then
Leave l1;
End if;
End loop
Select fact;
End//

Cursors

Cursors are used to read the data from the table row by row, and process it

Step by step procedure to use cursor

1. Declare cursor.
2. declare continue handler to stop the loop
3. open the cursor.
4. fetch the row from the cursor.
5. check whether reached to last row leave the loop
6. process the row.
7. goto step 4
8. once come out of the loop then close the cursor.

elimiter //
create procedure displayallemp()

begin

declare vset,vempno int default 0;

declare vname varchar(20);

declare empcur cursor for select empno,ename from emp;

declare continue handler for NOT FOUND set vset=1;

open empcur;

lable1: loop

fetch empcur into vempno,vname;

if vset=1 then

leave lable1;

end if;

select vempno,vname;

end loop;

close empcur;

end//

delimiter ;
Cursors

Cursors are used to read the data from the table row by row, and process it

Step by step procedure to use cursor

1. Declare cursor.
2. declare continue handler to stop the loop
3. open the cursor.
4. fetch the row from the cursor.
5. check whether reached to last row leave the loop
6. process the row.
7. goto step 4
8. once come out of the loop then close the cursor.

delimiter //

create procedure displayallemp()

begin

declare vset,vempno int default 0;

declare vname varchar(20);

declare empcur cursor for select empno,ename from emp;

declare continue handler for NOT FOUND set vset=1;

open empcur;

lable1: loop

fetch empcur into vempno,vname;

if vset=1 then

leave lable1;

end if;

select vempno,vname;

end loop;

close empcur;

end//

delimiter ;

2. write a procedure to display all the employees whose sal < avg sal of its own

department

delimiter //
create procedure displayempbyavg()
begin
declare vset,vempno,vdeptno int default 0;
declare vename,vjob varchar(20);
declare vsal,vavgsal float(9,2);
 declare empcur cursor for select empno,ename,job,sal,deptno from emp;
declare continue handler for NOT FOUND set vset=1;
open empcur;
label1:loop
fetch empcur into vempno,vename,vjob,vsal,vdeptno;
if vset=1 then
leave label1;
end if;
select avg(sal) into vavgsal
from emp
where deptno=vdeptno;
if vsal<vavgsal then
select vempno,vename,vjob,vsal,vdeptno,vavgsal;
end if;
end loop;
close empcur;
end//
delimiter ;

to write functions in mysql, we need to set a global variable

set GLOBAL log_bin_trust_function_creators=1;

write a function to calculate experience of employee

create function calculateexp(ehiredate date) returns int

-> begin

-> declare vexp int;

-> set vexp=floor(datediff(curdate(),ehiredate)/365);

-> return vexp;

-> end//

to call the function

select empno,ename,hiredate,calculateexp(hiredate)

-> from emp;

write a function to generateemail

email should be 3 rd to 6 th character from ename, followed by . and 1 st 3 characters of

job, followed by @muycompany.com

delimiter //

create function generateemail(enm varchar(20),ejob varchar(20)) returns varchar(50)

begin
 declare vemail varchar(50);

set vemail=concat(substr(enm,3,4),'.',left(ejob,3),'@mycompany.com');

return vemail;

end//

delimiter ;

To write trigger

Triggers are used for data analysis purpose or for security purpose.

1. create table to store trigger data

create table dept_audit(

-> did int,

-> dname varchar(20),

-> old_dname varchar(20),

-> newloc varchar(20),

-> oldloc varchar(20),

-> username varchar(20),

-> chang_date date,

action varchar(20));

delimiter //
create trigger updatedepttr before update
on dept
for each row
insert into dept_audit values(old.deptno,new.dname,
old.dname,new.loc,old.loc,user(),curdate(),’update’);

end//

delimiter ;

create trigger insertdepttr before insert

on dept
for each row
insert into dept_audit values(new.deptno,new.dname,
null,new.loc,null,user(),curdate(),'insert');

create trigger deletedepttr after delete

on dept
for each row
 insert into dept_audit values(old.deptno,null,
old.dname,null,old.loc,user(),curdate(),'delete');

In trigger we get 2 special variables old and new

insert delete update

old null data existing data in the table
new data null the record after changes are done

Exception handling
declare <exception-action> handler <exception> <statements>

exception action can be either continue/ exit

exception are of 3 types

• SQLEXCEPTION
• error code
• NOT FOUND

delimiter //

create procedure inserdept(did int, edname varchar(20),edloc varchar(20))

begin

declare exit handler for SQLEXCEPTION select 'error occured';

insert into dept values(did,edname,edloc);

select did,edname,edloc,'duplicate entry';

end//

delimieter ;

Internally mysql stores data in tablespace,

tablespace contains many files

these files are divided into 3 types

1. control file--→table and database metadata is stored is stored in control file

2. data file-→ data is stored in data files.
3. redolog files--→ redo log files are use for roll back and commit

Normalization

1. 1NF, 2NF, 3NF, BCNF

 acid custid cname balance mobile email gender type date
1000 100 Kishori 33333 454645 aa@gmail F Saving 1 jan
20
1001 100 Kishori 66666 5555 aa@gmail F current 1 jan
21
1003 100 Kishori 33333 5555 aa@gmail F demat 1 jan
20
1004 200 Rajan 55555 45454 r@gmail M saving 1 jan
19
null 201 Revati 5666 ww@gmail F

Revati came to bank for enquiry, but she did not open the account, so no acid is there , hence
we will not be able to add her entry in the table to retain customer information, this is called
insertion anamoly

If kishori changes her phone number for a/c 1000, it will not get reflected in other accounts, this
is called as updation anamoly

if rajan closes the account, then bank will loose customer information along with account
information, it is called as deleteion anamoly.’

acid custid balance type date

1000 100 33333 Saving 1 jan
20
1001 100 66666 current 1 jan
21
1003 100 33333 demat 1 jan
20
1004 200 55555 saving 1 jan
19
null 201

custid cname mobile email gender

100 Kishori 6666 aa@gmail F
200 Rajan 45454 r@gmail M
201 Revati 5666 ww@gmail F

1NF

According to the E.F. Codd, a relation will be in 1NF, if each cell of a relation contains
only an atomic value. This normal form states that an attribute of a relation cannot
hold multiple values. It should hold only single-valued attributes. Values stored in
an attribute should be of the same domain.
 Stud Sna cid Cname Fid Fname Email mar
id me ks
1 Djh 100 Database 1 Kishori abc@gmail.com,wert@rediff.c 99
om
1 Djh 101 Java 2 Madhura abc@gmail.com,wert@rediff.c 99
om
2 ettty 100 Database 1 Kishori eee@gmail.com,wwww@yah 98
oo.com,rrr@rediff.com
2 ettty 102 Data 2 Ganesh Eee11@gmail.com,wwww123 98
structure @yahoo.com

1 Djh 100 Database 1 Kishori abc@gmail.com, 99

1 Djh 100 Database 1 Kishori wert@rediff.com 99
1 Djh 101 Java 2 Madhura wert@rediff.com
1 Djh 101 Java 2 Madhura abc@gmail.com 99
2 ettty 100 Database 1 Kishori eee@gmail.com,wwww@yah 98
oo.com,rrr@rediff.com
2 ettty 102 Data 2 Ganesh Eee11@gmail.com,wwww123 98
structure @yahoo.com

2NF

According to the E.F. Codd, a relation is in 2NF, if it satisfies the following conditions:
• A relation must be in 1NF.
• And the candidate key in a relation should determine all non-prime attributes
or no partial dependency should exist in the relation.

Prime attributes: The attributes which are used to form a candidate key are called
prime attributes.
Non-Prime attributes: The attributes which do not form a candidate key are called
non-prime attributes.
Partial Dependency: If a non-prime attribute can be determined by the part of the
candidate key in a relation, it is known as a partial dependency. Or we can say that,
if L.H.S is the proper subset of a candidate key and R.H.S is the non-prime attribute,
then it shows a partial dependency.
Example of partial Dependency: Suppose there is a relation R with attributes A,
B, and C.

prime attributes

sid, cid

non prime attributes

sname, cname, fid, fname, email,marks

sid--→ sname,email,

cid -→ cname

sid+cid→ marks, fid, fname, marks

 student_course

Stud cid Fid Fname mar

id ks
1 100 1 Kishori 99
1 101 2 Madhura 99
2 100 1 Kishori 98
2 102 2 Ganesh 98

student

Stud Sna Email

id me
1 Djh abc@gmail.com
1 Djh wert@rediff.com
2 ettty eee@gmail.com, ,
2 ettty wwww@yahoo.com
2 ettty rrr@rediff.com

Course

cid Cname
100 Database
101 Java
102 Data
structure

3NF

Third Normal Form (3NF)

According to the E.F. Codd, a relation is in third normal form (3NF) if it satisfies the
following conditions:
• A relation must be in second normal form (2NF).
• And there should be no transitive functional dependency exists for non-
prime attributes in a relation.
Third Normal Form is used to achieve data integrity and reduce the duplication of
data.
A relation is in 3NF if and only if any one of the following conditions will satisfy for
each non-trivial functional dependency X→ Y:
1. X is a super key or candidate key
2. And, Y is a prime attribute, i.e., Y is a part of candidate key.
Transitive Dependency: If X → Y and Y→ Z are two functional dependencies, X →
Z is called as a transitive functional dependency.

prime attribute-→non prime --→ nonprime

the following table is not in 3NF

student_course
 Stud cid Fid mar
id ks
1 100 1 99
1 101 2 99
2 100 1 98
2 102 3 98

Fid Fname
1 Kishori
2 Madhura
3 Ganesh

Stud Sna
id me
1 Djh
2 ettty

Stud Email
id
1 abc@gmail.com
1 wert@rediff.com
2 eee@gmail.com, ,
2 wwww@yahoo.com
2 rrr@rediff.com

Boyce-Codd Normal Form (BCNF)

Boyce-Codd Normal Form (BCNF) is the advance version of the third normal form
(3NF) that’s why it is also known as a 3.5NF
According to the E.F. Codd, a relation is in Boyce-Codd normal form (3NF) if it
satisfies the following conditions:
• A relation is in 3NF.
• And, for every functional dependency, X → Y, L.H.S of the functional
dependency (X) be the super key of the table.
In this example, we have a relation R with three columns: Id, Subject, and Professor. We
have to find the highest normalization form, and also, if it is not in BCNF, we have to
decompose it to satisfy the conditions of BCNF.

Id Subject Professor

101 Java Mayank

101 C++ Kartik

102 Java Sarthak

103 C# Lakshay

104 Java Mayank

Interpreting the table:

• One student can enroll in more than one subject.

o Example: student with Id 101 has enrolled in Java and C++.
• Professor is assigned to the student for a specified subject, and there is always a
possibility that there can be multiple professors teaching a particular subject.

Finding the solution:

• Using Id and Subject together, we can find all unique records and also the other
columns of the table. Hence, the Id and Subject together form the primary key.
• The table is in 1NF because all the values inside a column are atomic and of the same
domain.
• We can't uniquely identify a record solely with the help of either the Id or the Subject
name. As there is no partial dependency, the table is also in 2NF.
• There is no transitive dependency because the non-prime attribute i.e., Professor, is not
deriving any other non-prime attribute column in the table. Hence, the table is also
in 3NF.
• There is a point to be noted that the table is not in BCNF (Boyce-Codd Normal Form).

Why is the table not in BCNF?

As we know that each professor teaches only one subject, but one subject may be taught by
multiple professors. This shows that there is a dependency between the subject & the
professor, and the subject is always dependent on the professor (professor -> subject). As
we know that the professor column is a non-prime attribute, while the subject is a prime
attribute. This is not allowed in BCNF in DBMS. For BCNF, the deriving attribute
(professor here) must be a prime attribute.

How to satisfy BCNF?

In Example 3, we will decompose the table into two tables: the Student table and
the Professor table to satisfy the conditions of BCNF.
Student Table
P_Id S_Id Professor

1 101 Mayank

2 101 Kartik

3 102 Sarthak

4 103 Lakshay

5 104 Mayank

Professor Table
Professor Subject

Mayank Java

Kartik C++

Sarthak Java

Lakshay C#

Mayank Java

Professor is now the primary key and the prime attribute column, deriving the subject
column. Hence, it is in BCNF.

Proj Proj Proj Empno Ename Grade Sal Proj Alloc

Code Type Desc scale Join Date Time

001 APP LNG 46 JONES A1 5 12/1/1998 24

001 APP LNG 92 SMITH A2 4 2/1/1999 24

001 APP LNG 96 BLACK B1 9 2/1/1999 18

004 MAI SHO 72 JACK A2 4 2/4/1999 6

004 MAI SHO 92 SMITH A2 4 5/5/1999 6

It is in 1NF
is it in 2NF

to check partial dependency

proj code--→ proj type, project description

empno-→ename, grade,sal

projcode+empno-→ joining date, allocation time

project

Proj Proj Proj

Code Type Desc

001 APP LNG

001 APP LNG

001 APP LNG

004 MAI SHO

004 MAI SHO

employee

Empno Ename Grade Sal

scale

46 JONES A1 5

92 SMITH A2 4

96 BLACK B1 9

72 JACK A2 4

92 SMITH A2 4

proj emp

Proj Empno Proj Alloc

Code Join Date Time

001 46 12/1/1998 24

001 92 2/1/1999 24

001 96 2/1/1999 18

004 72 2/4/1999 6

004 92 5/5/1999 6

following table is not in 3 NF

employee

Empno Ename Grade Sal

scale

46 JONES A1 5

92 SMITH A2 4

96 BLACK B1 9

72 JACK A2 4

92 SMITH A2 4

empno-→grade-→ salary scale

Empno Ename Grade

46 JONES A1

92 SMITH A2

96 BLACK B1

72 JACK A2

92 SMITH A2

grade

Grade Sal

scale

A1 5

A2 4

B1 9

A2 4

A2 4

one-one any one side key can be added into another side
one- many, many- add key of one side into many side table as a foreign key
one
many-many create new table and add primary key of both sides

ER diagram(Entity Relation diagram)

 ORDE DAT ITEMN QT PRIC CI CNAM EMAIL AM SI SNAM LI L
R ID E O Y E D E T D E D NAME
1 10 100 3 300 10 Kishor aa@d 300 S1 xxx 1 delhi
APR 0 i kj 0
1 10 200 4 10 kishor aa@d 300 S1 xxx 2 Mumb
APR 0 i kj 0 ai
2 11a 100 4 200 10 Revati r@wej 500 S2 yyy 1 Delhi
pr 1 0
2 11 200 10 Revati
apr 1

Is it in 1NF------yes
Is it in 2NF

1. It should be in 1 NF ----yes
2. Check for partial dependency
Prime attribute ----- orderno, item no
Orderno---→order date,cname,cno,email,orderamt,salespersonid,salespersonname,
Itemno
orderno, item no---→qty,price,locationid, location name

order
(Orderno,order date,cname,cno,email,orderamt,salespersonid,salespersonname

Cno--→cname,email
Salesperson id-→sname
Order_item
(orderno, item no,qty,price,locationid, location name

Check for 3NF

(Orderno,order date,cname,cno,email,orderamt,salespersonid,salespersonname
Is it in 3 NF ---no
Cno--→cname,email
Salesperson id-→sname

Customer
(cno,cname,email)
Salesman
(Salesperson id,sname)

order
(Orderno,order date, cno, ,orderamt,salespersonid)
Location(location id,lname)
Order_item
(orderno, item no,qty,price,locationid)
 Types of models

Conceptual model
If you draw ER diagram with entity name and relation

Logical model
In conceptual model if you add list of attribute then it is logical model

Physical model
In logical model if you define data types of each attribute, primary key,foreign key
customer(cno,cname,mobile)

to add a column in customer table

alter table customer

add email varchar

to delete column from customer table

alter table customer

drop email varchar

Data types collection

1. list
a. duplicate values are allowed
b. it is represented as []
c. it is ordered collection, hence indexing is possible.
d. It is mutable
add in the list at the update student set hobbies=hobbies+['trekking']
end where sid=1;
add in the list at the update student set hobbies=['trekking']+hobbies
begining where sid=1;
delete from the list update student set hobbies-['trekking']
where sid=1;
overwrite the list 1st update student set hobbies[1]='trekking'
index position with where sid=1;
some value

2. set
a. it is a collection of unique values
b. it is represented using {}
c. It is unordered, and hence no indexing is possible
d. It is mutable
add value in the set update customer

set brands=brands+{'a','b'}

where cno=1;

delete value from the update customer

set
set brands=brands-{'a','b'};

where cno=1;
 delete all values update customer
from the set
set brands={}

where cno=1;

assign or overwrite update customer

value in the set
set brands={‘puma’,’bata’}

where cno=1;

3. map
a. it allows to store key-value pair
b. keys should be unique
c. data is stored in {}
d. values can be retrieved by using keys.
add value in the map update student set
marks=marks +{'java':98}
where sid=1;
delete all values from the map update student set marks={}
where sid=1;
delete some key-value pair update student set
marks=marks -{'java','python'}
where sid=1;
assign or overwrite value in the update student set
map marks[‘perl’]=100
where sid=1;

4. tuple
a. duplicates are allowed
b. it is ordered collection, so indexing is possible
c. tuples are immutable
d. it uses ()
e. these are fixed length data
alter table student add degree tuple<text,text,int>;

cqlsh:iacsd0324> update student set degree=('Mtech','PU',89)

where sid=1;

create table test1( id int, name varchar,data list<tuple<int,text>>,data2 map<text,list<text>>)

-----create custom data types

create type address(street text,zipcode text,city text)

create table suppiler(sid,sname,saddr adress)

create table customer1(sid,sname,saddr list<FROZEN<address>>)

insert into supplier(sid,sname,saddr)

values(11,’xxxx’,{‘street’:’baner’,’zipcode’:’11111’,city:’Pune’})

---add new field in the type

alter type address add bldgnm text;

---to rename the field in the type

alter type address rename bldgnm to bname

In Cassandra we can execute batch operation

begin batch

insert into customer(cno,cname,brands,mobile,billamt) values(111,'xx',{'a','b'},'2222',44444)

insert into customer(cno,cname,brands,mobile,billamt) values(112,'yy',{'x','y'},'2222',66666)

delete mobile from customer where cno=1

apply batch

create index idxname om customer(billamt)

-------to insert data in json format

insert into customer JSON '

{"cno":12,"cname":"dfsd","brands":["a","b"],"mobile":"34567",

"billamt":4567}';
In mongodb data can be stored as document, stored in BSON format

{empno:123,

ename:'Rajan',

desg:'manager',

dept:{depetno:1,dname:'xxx'},

project:[{name:'x',duration:4},

{name:'y',duration:2},

{name:'x',duration:60}],

hobbies:['reading','dancing'],

married:True

to access project duration at 1 st index position

project.1.duration

to access hobbies at 1 st index position

hobbies.1

to install mongodb

1. download mongodb setup

a. community server version 6.0

to download mongo db

https://www.mongodb.com/try/download/community

b. to download mongo shell

https://www.mongodb.com/try/download/shell

c. to download mongodb commandline tools

https://www.mongodb.com/try/download/database-tools

step 1

open command prompt to start the server---start the server

c:\system32> mongod –dbpath e:\data7

open another command prompt----to start the client

c:\system32>mongosh

to import data
open third command prompt----to load data

c:\system32> mongoimport --db iacsd0324 --collection restaurent --file

D:\mongodb\JsonFiles\samplerestaurent.json

c:\system32> mongoimport --db iacsd0324 --collection movie --file

D:\mongodb\JsonFiles\movie.json

to export the data

c:\system32> mongoexport --db iacsd0324 --collection movie --out

D:\mongodb\JsonFiles\moviedata.json