MySQL (RDBMS->Relational DBMS)

Various DBMS available –

E.g.- MS Excel, dBase, Foxbase, Foxpro, Clipper, Dataease, Dataflex, Advanced
Revelation,
DB Vista, Quattro Pro, Google Docs, etc.

DBMS vs RDBMS
=============
-DBMS (e.g. MS Excel, FoxPro, etc)
------
a. Field
b. Record
c. File

1. Naming conventions are diffrent (Nomenclachure is diffrent)

2. Relationship between 2 files is maintained programmatically
3. More programming
4. More time is required for s/w development
5. High Network traffice
6. Procesing on Client machine
7. Slow and Expensive
8. Client-server architecture not supported
9. File level locking
10. Not suitable for multi-user
11. Distributed Database are not supported
12. No security of data
* Allows access of data to the OS
=======================================
-RDBMS(e.g. Oracle, MySQL, etc.)
------
a. Column, Attribute
b. Row, Tupple
c. Table, Relation, Entity

1. Naming conventions are diffrent (Nomenclachure is diffrent)

2. Relationship between 2 tables can be specified at the time of table createion
(e.g. Foreign key constraint)
3. Less Programming
4. Less time is required for s/w development
5. Low Network traffice
6. Procesing on Server machine
7. Faster(in terms of traffice) and Cheaper (in terms of hardware cost, network
cost,
infrastructure cost)
8. Most of the RDBMS support Client-server architecture
9. Row level locking
10. Suitable for multi-user
11. Most of the RDBMS support Distributed Database
12. Multiple levels of security:-
a. Logging in security
(e.g. username=pgdac1, password=welcome)
b. Command level security
(to issue MySQL commands)(e.g. create table, function, procedure, user, etc.)
c. Object level security
(to access the tables and other objects of users)
* Security is in-built feature of RDBMS
* OS cannot access the table data
* Distributed Database examples :-
Banking System, Railway Reservation System, Bookmyshow.com, Yatra.com
*

* INTERNALY, TABLE IS A NOT A FILE

* INTERNALY, EVERY ROW IS A FILE
===================================================================================
====

various RDBMS available:-

Infoemix
* Fastes in terms of processing speed(but should done at assembly level)

Oracle
* Most popular RDBMS because the programming is very easy
* product of Oracle Corporation
* #1 largest overall s/w company in the world
* #1 largest database s/w company in the world
* 10/10 of the top 10 companies in the world use Oracle
* largest database in the world is amazon.com (uses Oracle)
* largest Distributed database in the world is yahoo.com (uses Oracle)
* > 90% of Fortune 500 companies use Oracle
* works on 113 OS
* 63% of world commercial database market in the Client-server environment
* 86% of world commercial database market in the Internet environment

Sybase
* going down
* recently acquired by SAP

MS SQL Server
* good RDBMS from Microsoft(Limitation : Only works with Windows OS)
* 16% of world commercial database market

* character based (text based):-

Ingres
Postgres
Unify

DB2
CICS
TELON
IDMS
MS Access
Paradox
Vatcom SQL

MySQL
-----
* founded in 1995 by a Swedish company
* it's name is a combination of "My",co-founder Michael Widenius
daughter, and "SQL"
* MySQL is an open-source RDBMS
* most widely used open-source RDBMS
* part of the widely used "LAMP" open-source web application software stack
(and others "AMP"stacks)
* free-software open-source projects thst require a RDBMS use MySQL
* e.g. WordPress, Facebook, Google(through not for searches)
Joomla, Twitter, Flickr, Instagram, YouTube, etc.
* Occupies 42% of world open-source database market
* Sun Microsystems acquired MySQL in 2008
* Oracle acquired Sun Microsystems in 2010

MySQL
-----

Software development tools from MySQL

SQL
* Structured Query Language
* commonly pronounced as "Sequel"
* create, Drop, Alter
Insert, Update, Delete
Grant, Revoke, Select
* conforms to ANSI standards (e.g. 1 char=1 byte, datatypes)
* conforms to ISO standards for QA
* common for all RDBMS
* not a product of MySQL
* initially founded by IBM (1975-1977)
* now controlled by ANSI (therefore SQL is common for all RDBMS)
* in 2005, the source code of SQL rewritten using Java (100%)

MySQL Command Line Client

* MySQL client s/w
* used for running SQL commands, MySQL commands, and MySQL-PL programs
* interface with database
* character based (text based)

MySQL Workbench
* MySQL client s/w
* used for running SQL commands, MySQL commands, and MySQL-PL programs
* interface with database
* GUI based

MySQL -PL
* MySQL Programming Language
* Programming language from MySQL
* used for database programming
e.g. HRA_CALC, TAX_CALC, ATTENDANCE_CALS, etc.

MySQL Connectors
MySQL for Excel
MySQL Notifier
MySQL Enterprise Backup
MySQL Enterprise High Availability
MySQL Enterprise Encryption
MySQL Enterprise Monitor
MySQL Query Analyzer
etc.
MySQL - SQL

Common for all RDBMS:-

4 sub - divisions of SQL:-
DDL (Data Definition Language) (createe, Drop, Alter)

DML (Data Manipulation Language) (Insert , Update, Delete)

DCL (Data Control Language) (Grant, Revoke)

DQL (Data Query Language) (Select)

--------------------------------------------------------------------
Not an ANSI standards:-
Extra in MySQL RDBMS and Oracle RDBMS:-
5th component of SQL

DTL/TCL (Data Transaction Language)/(Transaction Control Language)

(Commit, Rollback, Savepoint)
DDL(Rename, Truncate)
--------------------------------------------------------------------
Extra in Oracle RDBMS:-

DML(Upsert, Merge)

Rules for tablenames, columnname & variablename:-

--------------------------------------------------
* max 30 characters
* A-Z,a-z,0-9 allowed
(e.g. EMP, emp, Emp)
* has to begin with an alphabet
(e.g. EMP2021, emp20, Emp10)
* Special characters $,#,_ allowed
(e.g. emp_2021)
* in MySQL, to use # in tablenames and columnname,
enclose it in back quotes
` ` back quotes
(e.g. `EMP#`)
* 134 reserved words not allowed(List of reserved words in MySQL documentation)

Datatypes (for MySQL)

---------------------
**Char**
(allows any character)(max 255 character)(default width is 1)
(fixed length)(wastage of HDD space)(searching and retrieval is fast)
(e.g. fixed length like AADHARNO, PANNO, ROLLNO)

**Varchar**
(allows any character)(max 65,535 character)(64Kb - 1)
(no default width)(width has to be specified)(variable length)
(conserve on HDD space)(searching and retrieval will be slow)
(e.g. NAME, ADDRESS, CITY, etc.)

Text (stored outside hr table)(stored away from the row)

Tinytext (max 255 characters)

Text (65,535 characters)

Mediumtext (max 16,777,215 characters)(16 Mb-1)

Longtext (max 4,294,967,295 characters)

* all of the above are variable length

* width does not have to specified for all of the above
* MySQL maintains a LOCATOR (HDD pointer) from the table row to the text data
* used for those column that are only for storage purposes and display purposes
* used for those column that will not be used for searching
* e.g. EXPERIENCE, RESUME,

Blob -> Binary large Object

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

Tinyblob (max upto 255 bytes of binary data)

Blob (maz upto 65,535 bytes of binary data)

Mediumblob (max upto 16,777,215 bytes of binary data)

Longblob (max upto 4,294,967,295 bytes of binary data)

* stored outside the table

* stored away from the row
* MySQL maintains a LOCATOR for the Blob data

e.g. BARCODES, QR_CODES, FINGERPRINTS, SIGNATURES, ICONS, THUMBNAILS,

PHOTOGRAPHS, SOUND, MUSIC, VIDEOS

* Blob is a Multimedia datatype

Integer Types (Exact Value):-

* Signed or Unsigned (by default it is signed)
Tinyint
Smallint
Mediumin
Integer
Bigint

Floating-point types (approximate value):-

Float

Double

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

Fixed-point Types:-

Decimal
* stores Double as a string
e.g. "653.7"
* used when it is necessary to preserve the exact precision
e.g monetary data
* max number of digits is 65
Boolean
--------
* True and False evaluate to 1 and 0 respectively
* e.g. Marital_Status boolean
* can insert True, False, 1, or 0

Date and Tie Datatypes

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

Date
* 1st Jan 1000 AD oto 31st Dec 9999 AD
* 'YYYY-MM-DD' isthe default date format in MySQL
* e.g.
'2021-11-19'
'21-11-19'
* year values from 70-99 are converted to 1970 to 1999
* year values from 00-69 are converted to 2000 to 2069
'47-08-15'
* date1 - date2 -> returns the number of days between the 2 dates

1st Jan 1000 AD -> 1

2nd Jan 1000 AD -> 2
3rd Jan 1000 AD -> 3
..
19th Nov 2021 AD -. 1,475,216 (number of days since 1st Jan 1000 AD)
* Internally date is stored as a fixed-length number and occupies 7 Bytes of
storage

Time ('hh:mm:ss' or 'HHH:MM:SS')('-838:59:59' to '838:59:59')

Datetime ('YYYY-MM-DD hh:mm:ss')

('1000-01-01 00:00:00' to '9999-12-31 23:59:59')

Year (YYYY)(1901 to 2155)

* max 4,096 columns per table provided row size<= 65,535 bytes
* no upper limit on number of rows per table provided but the max table size is
64TB

createe table emp

(
empno char(4),
ename varchar(25),
sal float,
city varchar(15),
dob date
);

; delimiter (denotes end of command)

INSERT
------
insert into emp
values('1','Amit',5000,'Mumbai','1990-01-04'); <----Easier to write
'1990-01-04' -> 'YYYY-MM-DD'
'1990-01-04' -> 'YY-MM-DD'

for char, varchar, date,time, datetime use ' '

data shoul has to match data sequence of table
OR specifi the column name

insert into emp(emono,sal,ename,city,dob)

values('2',6000,'king','Delhi','1985-04-07'); <---- Recommended
1. Readable
2. Flexible (user can enter the values in any order)
3. in future, if you alter th table, if you add a column to the table, the existing
statement will
continue to work (it will insert a null value for the new column); s/w maintenance
is reduced

To insert multiple rows into a table simultaneously:-

insert into emp values

('1','Amit',4000,'Mumbai','1990-05-04'),
('2','Priyanka',5000,'Delhi','1991-05-07'),
('3','Divesh',3000,'Mumbai','1994-03-08'),
('4','Shailesh',2500,'Delhi','1970-04-11');

insert into emp(empno,sal)values

('1',4000),
('2',5000),
('3',3000),
('4',2500);

* ABOVE 2 COMMANDS WILL WORK IN MySQL

* ABOVE 2 COMMANDS ARE NOT SUPPORTED BY ORACLE

insert into emp(empno, sal)

values('3',5000);

* Special treatment given to null value in all RDBMS

* Null means nothing
* Null value is having ASCII value 0
* Null value is independent of datatype
* Null value occupies only 1 Byte of storage
* if row is ending with null value, then all thode colimns will not occupy any
space
* it's recommended that those columns that are likely to have large number of
null values,
should preferably be stored at the end of the table structure, to conserve on
HHD space

insert into emp

value('4','Atul'); <---- ERROR (not enough values)

insert into emp

value('4','Atul',null,null,null);

insert into emp

value('5',null,5000,null,null);
SELECT
------

select * from emp;

* metacharacter
(all columns)

To restrict columns:-

select empno, ename from emp;

select deptno, job, ename, sal, hiredata from emp;

the order of column in SELECT statement, will determine the position of column in
the output (to be written)

WHERE clause:-
---------------

select * from emp

where department no=10;

* WHERE clause is used for searching

* searching takes place in DB server HDD
* WHERE clause is used to restrict the rows
* WHERE is used to retrieve the rows from DB server HDD to server RAM

select * from emp

where department no=10;

select * from emp

where sal > 2000;

Relational Operators:-

1. >
2. >=
3. <
4. <=
5. != or <>
6. =

select * from emp

where sal > 2000 and sal < 3000;

Logical Operator:-

NOT
AND
OR

select * from emp

where deptno = 10 or sal > 2000 and sal < 3000;
select * from emp
where (deptno = 10 or sal > 2000) and sal < 3000;

select * from emp

where sal > 2000 or sal < 3000;

select * from emp

where job = 'MANAGER';

select * from emp

where job = 'manager';

In MySQL:-
Queries are case-insensitive (More user-friendly)(Less secure)

In Oracle:-
Queries are case-sensitive (Less user-friendly)(More secure)

select * from emp

where job = 'MANAGER' and job= 'CLERK';

select ename, sal from emp;

select ename, sal, sal*12 from emp;

sal*12 -> computed column (derived column)(fake column)(Pseudo Column)

* Computed columns, e.g. sal*12, are never stored in the table (it's a wastage
of HDD space);
as and when require you can SELECT sal*12 to see yearly SAl

Arithmetic Operators:-

1. ( )
2. /
3. *
4. +
5. -

select enam, sal, sal*12 from emp;

select ename, sal, sal*12 as "ANNUAL" from emp;

Alias

select ename, sal, sal*12 as "ANNUAL" from emp;

as -> ANSI SQL

as -> optional in MySQL and Oracle

select ename, sal, sal*12 as annual from emp; -> Column should createe with
Capital Letters

select ename, sal, sal*12 as "annual" from emp; -> Column should createe with
small Letters
select ename, sal, sal*12 as "Annual Salary" from emp; -> Column should
createe with given Letters

select ename, sal, sal*12 as "ANNUAL" from emp; -> best practice

select ename "EMPNAME",

sal "SALARY",
sal*12 "ANNUAL",
sal*12*0.4 "HRA",
sal*12*0.2 "DA",
sal*12+sal*12*0.4+sal*12*0.2 "NET" from emp;

select ename "EMPNAME",

sal "SALARY",
sal*12 "ANNUAL",
from emp
where annual < 500000; <- ERROR

* you cannot use alias in the WHERE clause

select ename "EMPNAME",

sal "SALARY",
sal*12 "ANNUAL",
from emp
where sal*12 < 500000;

select job from emp;

to suppress the duplicate JOBs:-

select distinct job from emp;

* when you use DISTINCT, sorting takes place internally in the server RAM

select distinct job from emp;

select distinct job, ename from emp;

select (distinct job), ename from emp; <- ERROR

when you install MySQL, 2 users are automatically createed:-

root (password has to be specified at the time of installation)

root/cdac
* has DBA privileges
* createe users, assign permission, take backups, performance
monitoring, performance tuning, etc.

mysql.sys
* most important user in MySQL
* owner of database
* startup database, shutdown database

MySQL command Line Client

1. Open MySQL Command Line Client

MySQL Workbench
1. To createe a new connection for 'root' user:-

Open MySQL Workbench

MySQL Connections (Click on (+) sign to create a new connection)

Connections Name:- Connection for root user

Connection Method:- Standard (TCP/IP) <- network protocol

Hostname:- server machine name or IP address

Port:- 3306 (Oracle -> 1521)

username:- root

Password:- Store in Vault (push button) -> click on it

Test connection (push button) -> Click on it

Click on Ok
Click on Ok

Click on Connection you createed

You will see Query window on top
You will see output window on top

Some basic commands:-

show databases;

Ctrl+Enter to execute commands;

to connect to a database:-

use <databasename>;

use mysql;

to see list of users:-

select * from user;

USER -> is a MySQL createed System table

(stores the usernames)

createe a user and default database for this user

username -> pgdac1

password -> welcome
default database -> cdacmumbaipgdacsep2021;

or

create schema cdacmumbaipgdacsep2021;

* SCHEMA IS A SYNONYM FOR DATABASE

show databases;
createe user <username> identified by <password>;

createe user pgdac1@'%' identified by 'welcome';

@'%' -> this user will be able to connect to the database from any computer

create user pgdac1@'localhost' indentified by 'welcome';

To grant permission:-

Click on server (menu at the top) ->

User and Privelage

Select the username you createed from the user AccountList on LHS

Click on Administrative Roles (tab)

Apply (push button) click

Schema Privelage tab - click

Add Entry - click
Select Schema - click

choose user from poplist

ok - click

Select "ALL" - click

Select Grant Option - check manually

click on Apply

Exit from MySQL Workbench and createe a New connection for pgdac1 user

Specify Default Schema :-

DATE : 22/11/2021
------------------

* in DBMS, data is stored in a file

* within a file, the rows are stored sequentially
* in RDBMS, table is not a file; every row is a file
* in RDBMS, the rows of the table are not stored sequentially; the rows
of a table are scattered (fragmented) all over the DB server HDD
* when you INSERT a row into a table, wherever it finds the free space in the
DB server HDD,
it will store the row there
* the reason why RDBMS does this is to speed up the INSERT statement
(considering multi-user environment)
* in a multi-user environment, if multiple users are inserting rows
simultaneously into the same table,
if the rows were to be stored sequentially, it would be very slow when you
SELECT from a table, the order of
rows in the output depends on the row address; it will always be in ascending
order of row address
* when you UPDATE a row, if the row length is increasing, then the row address
MAY change
 (it's only in the case of varchar that the row length may increase or
decrease)
* later when you SELECT from that table, you will see the rows in some other
order in the output

ORDER BY clause
* used for sorting
* sorting takes place in server RAM;

select deptno, job, ename, sal, hiredate from emp

order by ename;

select deptno, job, ename, sal, hiredate from emp

order by ename desc;

asc -> (asending) by default

desc -> (decending)

ORDER BY clause
* to make the output more presentable

select deptno, job, ename, sal, hiredate from emp

order by deptno;

select deptno, job, ename, sal, hiredate from emp

order by hiredate; <- BUSINESS INTELLIGENCE

select deptno, job, ename, sal, hiredate from emp

order by deptno, job;

select deptno, job, ename, sal, hiredate from emp

order by deptno desc, job;

select deptno, job, ename, sal, hiredate from emp

order by deptno desc, job desc;

* no upper limit on the number of colimns in ORDER BY clause

select...................
order by country, state, city;

select deptno, job, ename, sal, hiredate from emp

order by deptno, job;

select deptno, job, ename, sal, hiredate from emp

order by deptno desc, job;

select deptno, job, ename, sal, hiredate from emp

order by deptno desc, job desc;

select deptno, job, ename, sal, hiredate from emp

where deptno = 10
order by ename;

* the WHERE clause is specified BEFORE the ORDER BY clause

* ORDER BY clause is the LAST clause in SELECT statement

select ename, sal*12 from emp;

select ename, sal*12 from emp;
order by sal*12;

select ename, sal*12 annual from emp;

order by annual;

select ename, sal*12 "Annual Salary" from emp;

order by "Annual Salary";

select ename, sal*12 "Annual Salary" from emp;

order by 2;

select * from emp

where ename > 'A' and ename < 'B';

Black ename Comparision semantics:-

* when you compare 2 strings of diffrent lengths,

the shoter of the 2 strings is temporarily padded
with blank spaces onRHS such that their lengths become equal; then it will
start the comparison,
char by char based on ASCII value

select * from emp

where ename like 'A%';

Special Operator (Like)

Wildcards (used for pattern matching)
% any char and any number of char (could be 0 char)

select * from emp

where ename like 'A%' or ename like 'a%,; <- to make it case-insensitive
in Oracle

select * from emp

where ename like 'A%'; (starts with A)

select * from emp

where ename like '%A'; (ends with A)

select * from emp

where ename like '%A%'; <- Starting with A or ending with A or A
somewhere within the ENAME (names that
contains A)

select * from emp

where ename like '_ _A%'; (3rd char is A)

select * from emp

where ename like '_ _ _ _';

select * from emp

where ename like '_ _ _';

select * from emp

where ename not like 'A%'; (not start with A)
select * from emp
where sal >= 2000 and sal <= 3000; (output sal 2000 to 3000)

select * from emp

where sal between 2000 and 3000; (output sal 2000 to 3000(includes 2000&3000))

select * from emp

where sal between 2000 and 3000; (FASTER)

* BETWEEN is a Special Operator

* readymade method by the name of BETWEEN is already present in the
database in the COMPILED FORMAT; the plan etc. is ready; it directly
executes

select * from emp

where sal not between 2000 and 3000; <- exclusive

select * from emp

where hiredate between '2020-01-01' and '2020-12-31';

select * from emp

where hiredate>= '2020-01-01' and hiredate<='2020-12-31';
-----------------------------------------------------------------------------------

select * from emp

where ename between 'A' and 'F'; (output A ename & F ename)

select * from emp

where ename >= 'A' and ename <='F'; (output A ename & F ename)

select * from emp

deptno =10 or deptno = 20 or deptno =30; (output shows 10,20&30)

select * from emp

where deptno =any(10,20,30); <-FASTER

any==logical OR

select * from emp

where deptno in(10,20,30); <-FASTER

* IN operator is faster than ANY operator(ANY is overloaded, thisslows it

down); but ANY operator is more powerful
than IN Operator
* with IN operator, you can check for IN and NOT IN
* with ANY operator, you can check for =ANY, !=ANY, >ANY, >=ANY, <ANY, <=ANY
* if you want to check for equality or inequality, then use the IN operator
* if you want to check for >, >=, <, <=

select * from emp

where city in('Mumbai','Delhi');

select * from emp

where city not in('Mumbai','Delhi');
-----------------------------------------------------------------------------------

* IN operator is supported by MySQL and Oracle

* ANY operator is supported by oracle directly, but not supported by MySQL
directly
select * from emp where deptno =any(10,20); (not supported by MySQL)

* ANY operator works in MySQL provided it is used with sub-query

* in MySQL, you will have to use the IN operator

DDL -> create, drop

DML -> insert, update, delete
DQL -> select
(select *, select col1, select col2,....., WHERE clause, Relational, Logical,
Arithmetic operator, Computed column...)

UPDATE
------

update emp
set sal=10000
where empno = 1;
(for empno 1 we set sal = 10000)

update emp
set sal=sal + sal*0.4
where empno = 1;
(for empno 1 we set sal = sal+sal*0.4)

update emp
set sal=10000, city = 'Nasik'
where empno = 1;
(for empno 1 we set sal = 10000)

update emp
set sal=10000
where city = Mumbai;
(for employee who having there city as Mumbai we set sal = 10000)

update emp
set sal=10000, city='Nasik'
where city = Mumbai;
(for employee who having there city as Mumbai we set sal = 10000)

* you can UPDATE multiple rows and multiple columns simultaneously,

but only 1 table at a time
* seperate UPDATE command would be needed for every table

update emp
set sal=10000
(for all employee we set sal = 10000)

DELETE
-------

delete from emp

where empno = 1;
(we delete empno 1 from emp table)
delete from emp
where city = 'Mumbai';
(we delete empl from emp table)

drop table emp; (Delete the whole table having name emp)

drop table emp, dept, customers;

* you cannot specify WHERE clause with DROP table

***********************************************************************************
TRANSACTION PROCESSING
***********************************************************************************

* commite will save all the DML changes since the last committed state
* when the user issues a commit, it is known as end of transaction
* Commit will make the Transaction permanent

commit work;

or

commit;

Total Work Done = T1+T2+T3.....+Tn;

* Transaction is a unit of Work

* when the issue the commit, it depends on the logical scope of Work
(to be decided by user)
* you can Rollback to Savepoint
* Savepoint is a point within the Work
(it's similar to a Bookmark)
* Savepoint is a sub-unit of transaction
* when you Rollback or Commit, the intermediate Savepoint will be
cleared; if you want to use them again then you will have to reissue
them in some new work
* YOU CANNOT COMMIT TO A SAVEPOINT
* Commit will save the all the DMl changes since the last committed state
* you can only Rollback sequentially (bottom to top)
* Within a transaction, you can have 2 Savepoint with the same name;
the latest Savepoint overwrites the older Savepoint; the Older Savepoint no
longer
exits

* To try out Rollback, Commit, and Savepoint in MySQL Workbench:-

Click on Query (menu at the top) -> Auto-commit transaction -> uncheck it

* Rollback will undo all the DML changes since the last committed state
* only the DML commands are affected by Rollback and commit
* any DDL command, it automatically commits
* when you exit from SQL*Plus (Oracle client s/w), it automatically commits
* any kind of power failure, network failure, system failure, PC rebbot, window
close,
end of task, etc.; in all such cases your last uncommitted Transaction, it
automatically
Rolled back in MySQL and Oracle
update emp set sal = 10000;

delete from emp;

* UPDATE and DELETE commands without WHERE clause will not be allowed
in MySQL Workbench

To try out the above commands in MySQL Workbench:-

Click on Edit (menu at the top) -> Preferences -> SQL Editor -> Safe Updates
(Checkbox at the bottom) -> uncheck it -> click on OK

DATE : 23/11/2021
------------------
Read and Write Consistency:-
-----------------------------
in a multi-user environment, when you Select from a table, you can view only
the committed data of other users plus (union) the changes made by you.
There is a separate NEW TABLE for every user (It's private to each user)

* when you Update or Delete a row, that row is automatically locked for other
users
* ROW LOCKING IS AUTOMATIC IN MySQL AND ORACLE
* when you UPDATE or DELETE a row, that row becomes READ_ONLY for other users
* other users can SELECT from that tablr; they will view the old data before
your changes
* other users can INSERT rows into that table
* other users can UPDATE/DELETE "other" rows from that table
* no other user can UPDATE or DELETE your locked row, till you have issued a
Rollback or Commit
* Locks are automatically released when you rollback or commit

OPTMISTIC ROW LOCKING MACHANISM OF MySQL -> automatic row locking

PESTISTIC ROW LOCKING -> you manually lock the rows in advance BEFORE issuing
UPDATE or DELETE

* To lock the rows manually, you have to use SELECT statement with the FOR
UPDATE clause

e.g.

select * from dept for update;

select * from emp
where deptno=10
for update;

select * from dept for update;

select * from emp
where deptno=10
for update nowait;

--> if row is available, then it will acquire the lock

if row is unavailable, then it will wait in the Request for the
specified time period; accordingly it will acquire the lock or abort the
operation
--> if row is available, then it will acquire the lock
if row is unavailable, then it will abort the operation immediately

select * from dept for update;

select * from emp
where deptno=10
for update wait 60; <- seconds

* LOCKS ARE AUTOMATICALLY RELEASED WHEN YOU ROLLBACK OR COMMIT

Click on Query (menu at the top) -> New tab to current server -> Click on it

* now you have 2 query window to try out locking

To try out Row locking in MySQL Workbench, if you get stuck in the Request
Queue, to abort the operation:-

Click on Query (menu at the top) -> Click on Stop

Manual row locking in MySQL

* WAIT and NOWAIT options are not available in MySQL

Functions
---------
Character Functions
-------------------
1. Routine that Returns a value (return statement)
2. You can parameters/arguments to a function
3. Functions can be overloaded

concatenate -> to join

concat (str1,str2)

select concat(fname,lname) from emp;

Output:-
ArunPurun
TarunArun
SirunKirun
NutanPurun

In Oracle:-

concat (str1,str2)

select concat(concat(fname,' '),lname) from emp;

Output:-
Arun Purun
Tarun Arun
Sirun Kirun
Nutan Purun

* max upto 255 levels for function within function

(common for all RDBMS)(this limit of SQL can be exceeded with the help
of Views)

In MySQL:-
concat(str1,str2,str3,....,strn)
select concat(fname,' ',lname) from emp;
Output:-
Arun Purun
Tarun Arun
Sirun Kirun
Nutan Purun

select concat('Mr. ',fname,' ',lname) from emp;

Output:-
Mr. Arun Purun
Mr. Tarun Arun
Mr. Sirun Kirun
Mr. Nutan Purun

UPPER
-----

select upper(fname) from emp;

ARUN
TARUN
SIRUN
NUTAN

update emp set fname = upper(fname);

In Oracle:-

Solution for Case-insensitive query in Oracle:-

select * from emp where upper(fname)='ARUN';

select lower(fname) from emp;

arun
tarun
sirun
nutan

select emp set fname=lower(fname);

In Oracle:-

Solution for Case-insensitive query in Oracle:-

select * from emp where lower(fname)='arun';

select initcap(fname) from emp; <- not available in MySQL

Arun
Tarun
Sirun
Nutan

select lpad(ename,25,' ') from emp;

Output:-
put 25 blank spaces to the left hand side

select lpad(ename,25,'*') from emp;

Output:-
put 25 * to the left hand side

Uses:-
a. Right justification
b. Billing Receipt
c. Cheque printing

select rpad(ename,25,' ') from emp;

Output:-
put 25 blank spaces to the right hand side

select rpad(ename,25,'*') from emp;

Output:-
put 25 * to the right hand side

Uses:-
a. Left justification of numeric data
b. to convert varchar to char (convert variable length to fixed-length)
c. Billing Receipt
d. Cheque printing

Q. Nested lpad & rpad

select ltrim(ename) from emp;

Output:-
trim blank spaces from left hand side

Arun Purun
Tarun Arun
Sirun Kirun
Nutan Purun

Uses:-
a. Left justification

select rtrim(ename) from emp;

Output:-
trim blank spaces from right hand side

Uses:-
a. to convert char to varchar (convert fixed-length length to variable)

DATE : 24/11/2021
------------------
Number Functions
=================

select round(sal) from emp;

Output:- Round of the sal

select round(sal,1) from emp;

Output:- Round of the sal upto one digit after decimal

select round(sal,2) from emp;

Output:- Round of the sal upto two digit after decimal
------------------------------------------------------

select truncate(sal,0) from emp;

select truncate(sal,1) from emp;
select truncate(sal,2) from emp;
select truncate(sal,-2) from emp;
------------------------------------------------------

select ceil(sal) from emp; (ceiling)

select floor(sal) from emp;

select truncate(3.6,0),floor(3.6),truncate(-3.6,0),floor(-3.6) from dual;

select sign(-15) from dual;

Output +1 or -1
if x>0 then
return 1;
elseif x < 0 then
return -1;
else
return 0;
end if

1. check if num is +ve or -ve

2. sign (a-b) <- to find the greater number

select mod(9,5) from dual;

Output:- returns remainder;

select sqrt(81) from dual;

Output:- return squar root

select power(10,3) from dual;

Output:- return 1000

select power(1000,1/3) from dual; <- cub root

Output:- return 10 root

select abs(-10) from dual; <- Absolute value

Output:- 10

sin(x)
cos(x)
tan(x)

ln(y)
log(n,m)

Date Function
-------------

1. 'YYYY/MM/DD'
2. '1000/01/01' to '9999/12/31'
3. date1-date2
4. internally date is stored as fixed-length number
(number of days since 1st Jan 1000 AD)
5. 7 Bytes
6. date and time is stored together
7. default value for time is 12 am midnight
8. 1970 is the cut-off year

select sysdate() from dual; <-returns server data and time when the statement
executed
+---------------------+
| sysdate() |
+---------------------+
| 2021-11-24 15:17:08 |
+---------------------+

'2021-11-24' -> 1234567

'2021-11-24 12:00:00' -> 1234567.5
'2021-11-24 06:00:00' -> 1234567.5

'2021-11-24 15:15:16'
* returns the DB server data and time

select now() from dual; <-returns server data and time when the statement
began to executed
+---------------------+
| now() |
+---------------------+
| 2021-11-24 15:18:32 |
+---------------------+

select sysdate(), now(), sleep(10), sysdate(), now() from dual;

+---------------------+---------------------+-----------+---------------------
+---------------------+
| sysdate() | now() | sleep(10) | sysdate() |
now() |
+---------------------+---------------------+-----------+---------------------
+---------------------+
| 2021-11-24 15:22:59 | 2021-11-24 15:22:59 | 0 | 2021-11-24 15:23:09 |
2021-11-24 15:22:59 |
+---------------------+---------------------+-----------+---------------------
+---------------------+
1 row in set (10.01 sec)

sysdate()- date and time display

now()- used to maintain logs of operation
(e.g. insert, update, delete, etc.)

select adddate(sysdate(),1) from dual;

+----------------------+
| adddate(sysdate(),1) |
+----------------------+
| 2021-11-25 15:26:02 |
+----------------------+
show date after 24 hrs

select adddate(sysdate(),2) from dual;

+----------------------+
| adddate(sysdate(),2) |
+----------------------+
| 2021-11-26 15:27:28 |
+----------------------+
show date after 48 hrs
select adddate(sysdate(),-1) from dual;
+-----------------------+
| adddate(sysdate(),-1) |
+-----------------------+
| 2021-11-23 15:28:46 |
+-----------------------+
show date before 24 hrs

select datediff(sysdate(),hiredate) from emp; <-returns number of days between

the 2
dates

select datediff(sysdate(),odate) as 'Difference' from orders;

+------------+
| Difference |
+------------+
| 11375 |
| 11375 |
| 11375 |
| 11375 |
| 11375 |
| 11374 |
| 11374 |
| 11373 |
| 11372 |
| 11372 |
+------------+

select date_add(odate,interval 2 month) as 'Next 2 Month' from orders;

+--------------+
| Next 2 Month |
+--------------+
| 1990-12-03 |
| 1990-12-03 |
| 1990-12-03 |
| 1990-12-03 |
| 1990-12-03 |
| 1990-12-04 |
| 1990-12-04 |
| 1990-12-05 |
| 1990-12-06 |
| 1990-12-06 |
+--------------+

select date_add(odate,interval -2 month) as 'Last 2 Month' from orders;

+--------------+
| Last 2 Month |
+--------------+
| 1990-08-03 |
| 1990-08-03 |
| 1990-08-03 |
| 1990-08-03 |
| 1990-08-03 |
| 1990-08-04 |
| 1990-08-04 |
| 1990-08-05 |
| 1990-08-06 |
| 1990-08-06 |
+--------------+

select date_add(odate,interval 1 Year) as 'Next 1 year' from orders;

+-------------+
| Next 1 year |
+-------------+
| 1991-10-03 |
| 1991-10-03 |
| 1991-10-03 |
| 1991-10-03 |
| 1991-10-03 |
| 1991-10-04 |
| 1991-10-04 |
| 1991-10-05 |
| 1991-10-06 |
| 1991-10-06 |
+-------------+

select date_add(odate,interval -1 Year) as 'Last 1 year' from orders;

+-------------+
| Last 1 year |
+-------------+
| 1989-10-03 |
| 1989-10-03 |
| 1989-10-03 |
| 1989-10-03 |
| 1989-10-03 |
| 1989-10-04 |
| 1989-10-04 |
| 1989-10-05 |
| 1989-10-06 |
| 1989-10-06 |
+-------------+

select last_day(odate) from orders; <- this function is available only in

MySQL
and Oracle
+-----------------+
| last_day(odate) |
+-----------------+
| 1990-10-31 |
| 1990-10-31 |
| 1990-10-31 |
| 1990-10-31 |
| 1990-10-31 |
| 1990-10-31 |
| 1990-10-31 |
| 1990-10-31 |
| 1990-10-31 |
| 1990-10-31 |
+-----------------+

select dayname(sysdate()) as 'Present day' from dual;

+-------------+
| Present day |
+-------------+
| Wednesday |
+-------------+
select upper(dayname(sysdate())) as 'Present day' from dual;
+-------------+
| Present day |
+-------------+
| WEDNESDAY |
+-------------+

select substr(dayname(sysdate()),1,3) as 'Present day' from dual;

+-------------+
| Present day |
+-------------+
| Wed |
+-------------+

select addtime('2021-01-15 11:00:00','1') from dual;

+------------------------------------+
| addtime('2021-01-15 11:00:00','1') |
+------------------------------------+
| 2021-01-15 11:00:01 |
+------------------------------------+

addtime('2021-01-15 11:00:00','1:30:45') from dual;

+------------------------------------------+
| addtime('2021-01-15 11:00:00','1:30:45') |
+------------------------------------------+
| 2021-01-15 12:30:45 |
+------------------------------------------+

select addtime('2021-01-15 11:00:00.000000','1:30:45.123456') from dual;

+--------------------------------------------------------+
| addtime('2021-01-15 11:00:00.000000','1:30:45.123456') |
+--------------------------------------------------------+
| 2021-01-15 12:30:45.123456 |
+--------------------------------------------------------+

MySQL - List function

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

* independent of datatype

select * from emp where comm=null;

* any comparision done with null, return null

Pessimistic query -> searching for null values

select sal+comm from emp;

* any operation done with null, returns null

select sal+ifnull (comm,0) from emp;

select ifnull(sal,0)+ifnull(comm,0) from emp;

ifnull(comm,0)
ifnull(comm,100)
ifnull(city,'Mumbai')
ifnull(orderdate,'2021-04-01')
select greatest(sal,3000) from emp;

Uses:-
a. used to set a lower limit on some value
e. g. Bonus = 10% sal, Min Bonus=300

select greatest(sal*0.1, 300) from emp;

greatest(val1, val2, val3, ...,val255)

greatest(num1,bum2,num3)
greatest('str','str2','str3')
greatest(date1,date2,date3)

set x=greatest(a,b,c,d);

Case Expression:-

select case
when deptno =10 then 'Training'
when deptno =20 then 'Exports'
when deptno =30 then 'Sales'
else 'Others'
end "DEPTNAME"
from emp;

DEPTNAME
--------
Training
Training
Exports
Sales
Others

Uses:-
a. Encoding and Decoding

select case
when deptno =10 then 'Ten'
when deptno =20 then 'Twenty'
when deptno =30 then 'Thirty'
end "DEPTNAME"
from emp;

DEPTNAME
--------
Ten
Ten
Twenty
Thirty

Environment Functions:-

select user() from dual;

**********************************************************************************
EMP
EMPNO ENAME SAL DEPTNO JOB MGR
1 Arun 8000 1 M 4
2 ALI 7000 1 C 1
3 Kirun 3000 1 C 1
4 Jack 9000 2 M
5 Thomas 8000 2 C 4

M=MANAGER
C=CLERK

Group/Aggregate function

Single Row function

* Will operate on 1 row at 1 time

select sum(sal) from emp;

Output:- 35000

select * from bank_trans;

+--------+---------+--------+
| name | pasword | amount |
+--------+---------+--------+
| aditi | 123 | 23000 |
| yogi | 123 | 23000 |
| modi | 122 | 790000 |
| yogita | 123 | 25005 |
| patil | 123 | 75000 |
+--------+---------+--------+

select sum(amount) as 'Sum of Amount' from bank_trans;

+---------------+
| Sum of Amount |
+---------------+
| 936005 |
+---------------+

Assumtion last row SAL is null:-

select sum(sal) from emp; <- NULL VALUES ARE NOT COUNTED BY
GROUP Function
27000

select sum(ifnull(sal,0)) from emp; <- Output is same as above but it's
(if null) not required
27000

select avg(sal) from emp;

27000/4 -> 6750

select avg(ifnull(sal,0))from emp;

27000/5 -> 5400

select avg(Amount) from bank_trans;

+-------------+
| avg(Amount) |
+-------------+
| 187201 |
+-------------+
select min(sal) from emp;
3000

select min(Amount) from bank_trans;

+-------------+
| min(Amount) |
+-------------+
| 23000 |
+-------------+

select min(ifnull(sal,0)) from emp;

0

select max(sal) from emp;

9000

select max(Amount) from bank_trans;

+-------------+
| max(Amount) |
+-------------+
| 790000 |
+-------------+

select max(ifnull(sal,0)) from emp;

0

select count(sal) from emp;

4

select count(Amount) from bank_trans;

+---------------+
| count(Amount) |
+---------------+
| 5 |
+---------------+

select sum(sal) from emp when deptno=1;

18000

select sum(amount) from bank_trans where pasword=123;

+-------------+
| sum(amount) |
+-------------+
| 146005 |
+-------------+

COUNT-QUERY (counting the number of query hits):-

select count(*) from emp where sal>7000;

select max(sal)/min(sal) from emp;

9000/3000 -> 3

select sum(sal)/count(*) from emp;

27000/5

select avg(ifnull(sal,0)) from emp; <- function within function (slower)

27000/5
 sum(column)
avg(column)
min(column) min(sal),min(ename),min(hiredate)
max(column) max(sal),max(ename),max(hiredate)
count(column) count(sal),count(ename),count(hiredate)
count(*)
stddev(column)
variance(column)

Assumtion last row SAL is 8000:-

SUMMARY REPORT:-

select count(*),min(sal),max(sal),sum(sal),avg(sal) from emp;

COUNT(*) MIN(SAL) MAX(SAL) SUM(SAL) AVG(SAL)

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

DATE-25/11/2021
---------------

GROUP BY clause (V. important)

used for grouping

WHERE clause _> used for searching

ORDER BY clause -> used for sorting
FOR UPDATE -> used for locking the rows

select sum(sal) from emp;

Output:- 35000

select sum(sal) from emp where deptno=1;

Output:- 18000

sum(sal) deptno, sum(sal) from emp;

group by deptno;

DEPTNO SUM(SAL)
------ --------
1 18000
2 17000

1. Rows retrieved from DB server HDDD to Server RAM

2. Sorting deptwise
3. Grouping depteise
4. Summation deptwise

Rule #1
Besides the group function, whichever column is present in SELECT clause, it has
to be present in GROUP BY clause

Rule #2
Whichever column is present in GROUP BY clause, iy may be or may not be presentin
select
clause

select deptno, count(*) from emp group by deptno;

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

* WHERE clause is specified BEFORE the GROUP BY clause

* WHERE clause is used for searching
* searching takes place in DB server HDD
* WHERE clause is used restrict the row
* WHERE clause is used for retrieve the rows from DB server HDD to server
RAM

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

Output:- DEPTNO JOB SUM(SAL)

* no upper limit on the number of columns in GROUP BY clause

select...................
group by country, state, city;

* if you have large number of column in GROUP BY clause, then the SELECT
statement will be slow (that much sorting has to take place)

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

select onum, odate, sum(amt) from orders group by cnum, snum;

+------+------------+----------+
| onum | odate | sum(amt) |
+------+------------+----------+
| 3001 | 1990-10-03 | 1116.85 |
| 3003 | 1990-10-03 | 767.19 |
| 3002 | 1990-10-03 | 1900.10 |
| 3005 | 1990-10-03 | 5160.45 |
| 3009 | 1990-10-04 | 1713.23 |
| 3007 | 1990-10-04 | 1385.70 |
| 3008 | 1990-10-05 | 14614.88 |
+------+------------+----------+

select job,

* the position of columns in SELECT clause, and the order of columns in

GROUP BY clause need not be the same
* the position of columns in SELECT clause, will determine the position of
columns in the output (this you will write as per user requirement)
* the order of columns in GROUP BY clause will determine the sorting order,
the grouping order, the summation order, and hence the speed of processing

select ...........
group by city, country, district, state; <- Slow

select ...........
group by country, state, district, city; <- fast

select deptno, sum(sal) from emp

group by deptno
having sum(sal)>17000;

1. Rows retrieved from DB server HDDD to Server RAM

2. Sorting deptwise
3. Grouping depteise
4. Summation deptwise
5. HAVING clause
6. ORDER BY clause

* ORDER BY clause is LAST clause

select...... from ....

where ......
group by .....
having ......
order by .......;

In Oracle:-

select deptno, sum(sal) from emp

group by deptno;

select sum(sal) from emp

group by deptno;

select max(sum(sal)) from emp group by deptno; <- NESTING OF GROUP FUNCTION IS
ALLOWED ONLY
IN ORACLE RDBMS; NOT
SUPPORTED IN
ANY OTHER RDBMS

In MySQL:-

select sum(sal) from emp group by deptno;

select max(sum_sal) from (select sum(sal)) sum_sal from emp

group by deptno) as tempp;

**********************************************************************************
JOINS
-----

EMP
EMPNO ENAME SAL DEPTNO JOB MGR
1 Arun 8000 1 M 4
2 ALI 7000 1 C 1
3 Kirun 3000 1 C 1
4 Jack 9000 2 M
5 Thomas 8000 2 C 4

DEPT
DEPTNO DNAME LOC
1. TRN MUM
2. EXP DEL
3. MKTG CAL

create table
DATA REDUNDANCY -> UNNECESSARY DUPLICATION OD DATA

NORMALISATION is known as how to design a table.

* all the data is not stored in one table; data is stored in multiple
tables
* to view/combine the columns of 2 or more tables, then you will have to write
a JOIN
dept -> driving table
emp -> driven table

select dname, ename from emp,dept where dept.deptno=emp.deptno;

DNAME ENAME
------ ------
TRN ARUN
TRN ALI
TRN KIRUN
EXP JACK
EXP THOMAS

select cname, amt from customers,orders where customers.cnum=orders.cnum;

+----------+---------+
| cname | amt |
+----------+---------+
| Cisneros | 18.69 |
| Hoffman | 767.19 |
| Pereira | 1900.10 |
| Liu | 5160.45 |
| Cisneros | 1098.16 |
| Giovanmi | 1713.23 |
| Grass | 75.75 |
| Clemens | 4723.00 |
| Grass | 1309.95 |
| Clemens | 9891.88 |
+----------+---------+

select cname, amt from customers,orders where orders.cnum=customers.cnum;

+----------+---------+
| cname | amt |
+----------+---------+
| Cisneros | 18.69 |
| Hoffman | 767.19 |
| Pereira | 1900.10 |
| Liu | 5160.45 |
| Cisneros | 1098.16 |
| Giovanmi | 1713.23 |
| Grass | 75.75 |
| Clemens | 4723.00 |
| Grass | 1309.95 |
| Clemens | 9891.88 |
+----------+---------+

select dname, ename from dept,emp where dept.deptno=emp.deptno; <- SLOW

select dname, ename from emp,dept where dept.deptno=emp.deptno; <- FAST

* IN ORDER FOR YOUR JOIN TO WORK FASTER, PREFERABLY THE DRIVING TABLE
SHOULD BE TABLE WITH LESSER NUMBER OF ROWS

select dname, ename from emp,dept where dept.deptno=emp.deptno;

select dname, ename from emp,dept where dept.deptno=emp.deptno order by 1;

select dname, loc, ename, job, sal from emp,dept where dept.deptno=emp.deptno order
by 1;
select * from emp,dept where dept.deptno=emp.deptno order by 1;

* The common column that is present in both the tables, that column's
name need not be the same in both the tables;

select dname, loc, ename, job, sal from emp, dept where dept.x=emp.y order by 1;

select snum, onum, amt, odate, cnum, cname, city, rating from orders,customers
where orders.cnum=customers.cnum order by 1;
ERROR 1052 (23000): Column 'snum' in field list is ambiguous

select customers.snum, onum, amt, odate,customers.cnum, cname, city, rating from

orders,customers where orders.cnum=customers.cnum order by 1;
+------+------+---------+------------+------+----------+----------+--------+
| snum | onum | amt | odate | cnum | cname | city | rating |
+------+------+---------+------------+------+----------+----------+--------+
| 1001 | 3003 | 767.19 | 1990-10-03 | 2001 | Hoffman | London | 100 |
| 1001 | 3008 | 4723.00 | 1990-10-05 | 2006 | Clemens | London | 100 |
| 1001 | 3011 | 9891.88 | 1990-10-06 | 2006 | Clemens | London | 100 |
| 1002 | 3005 | 5160.45 | 1990-10-03 | 2003 | Liu | San Jose | 200 |
| 1002 | 3007 | 75.75 | 1990-10-04 | 2004 | Grass | Berlin | 300 |
| 1002 | 3010 | 1309.95 | 1990-10-06 | 2004 | Grass | Berlin | 300 |
| 1003 | 3009 | 1713.23 | 1990-10-04 | 2002 | Giovanmi | Rome | 200 |
| 1004 | 3002 | 1900.10 | 1990-10-03 | 2007 | Pereira | Rome | 100 |
| 1007 | 3001 | 18.69 | 1990-10-03 | 2008 | Cisneros | San Jose | 300 |
| 1007 | 3006 | 1098.16 | 1990-10-03 | 2008 | Cisneros | San Jose | 300 |
+------+------+---------+------------+------+----------+----------+--------+

select customers.snum, cname, onum, amt, odate,customers.cnum, city, rating from

orders,customers where orders.cnum=customers.cnum order by 1;
+------+----------+------+---------+------------+------+----------+--------+
| snum | cname | onum | amt | odate | cnum | city | rating |
+------+----------+------+---------+------------+------+----------+--------+
| 1001 | Hoffman | 3003 | 767.19 | 1990-10-03 | 2001 | London | 100 |
| 1001 | Clemens | 3008 | 4723.00 | 1990-10-05 | 2006 | London | 100 |
| 1001 | Clemens | 3011 | 9891.88 | 1990-10-06 | 2006 | London | 100 |
| 1002 | Liu | 3005 | 5160.45 | 1990-10-03 | 2003 | San Jose | 200 |
| 1002 | Grass | 3007 | 75.75 | 1990-10-04 | 2004 | Berlin | 300 |
| 1002 | Grass | 3010 | 1309.95 | 1990-10-06 | 2004 | Berlin | 300 |
| 1003 | Giovanmi | 3009 | 1713.23 | 1990-10-04 | 2002 | Rome | 200 |
| 1004 | Pereira | 3002 | 1900.10 | 1990-10-03 | 2007 | Rome | 100 |
| 1007 | Cisneros | 3001 | 18.69 | 1990-10-03 | 2008 | San Jose | 300 |
| 1007 | Cisneros | 3006 | 1098.16 | 1990-10-03 | 2008 | San Jose | 300 |
+------+----------+------+---------+------------+------+----------+--------+

select dept.deptno, dept.dname, dept.loc, emp.empno, emp.ename, emp.job, emp.sal

from emp, dept
where dept.deptno=emp.deptno order by 1;

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

DEPTNO SUM(SAL)
------ -------
1 18000
2 17000

select dname, sum(sal) from emp, dept where dept.deptno=emp.deptno group by name;
DNAME SUM(SAL)
----- --------
select upper(dname), sum(sal) from emp, dept where dept.deptno=emp.deptno group by
upper(dname) having sum(sal)>10000 order by 1;

Types of JOINS(5):-
1. Equijoin
Join based on equality condition
shows matching rows of both the tables

Uses:-
a. view the columns of both the tables
e.g. DNAME and ENAME, CUSTONAME and ORDER_DETAILS, etc.
this is the most frequently used join (>90%)
and hence it is also known as Natural join

select dname, ename from emp, dept where dept.deptno=emp.deptno;

2. Inequijoin (Non-Equijoin)
join based on inequality condition
shows non-matching rows of both the tables

Uses:-
a.

select dname, ename from emp, dept where dept.deptno != emp.deptno;

3. Outerjoin
join with (+) sign
shows matching rows of both the tables and non-matching rows of "OUTER" table
OUTER table -> table which is on outer side of (+) sign
OUTER table -> table which is on opposite side of (+) sign
a. Half outerjoin ((+) sign is on any one side, i.e. LHS or RHS )
i. Right ii. Left
b. Full outerjoin

Uses:-
Master -Detail child (Parent-child report)

select dname, ename from emp, dept where dept.deptno = emp.deptno (+);

DNAME ENAME
----- -----
TRN Arun
TRN Ali
TRN Kirun
EXP Jack
EXP Thomas

select dname, ename from emp, dept where dept.deptno (+) = emp.deptno ;

DNAME ENAME
----- -----
TRN Arun
TRN Ali
TRN Kirun
EXP Jack
EXP Thomas
null Scott

FULL outerjoin

* shows matching rows of both the tables

plus
non-matching rows of both the tables
* based on nested Do-While loop

select dname, ename from emp, dept where dept.deptno = emp.deptno (+);
union
select dname, ename from emp, dept where dept.deptno (+) = emp.deptno ;

DNAME ENAME
----- -----
TRN Arun
TRN Ali
TRN Kirun
EXP Jack
EXP Thomas
MKTG null
null Scott

* (+) sign for Outerjoin is support only Oracle RDBMS

* (+) sign for Outerjoin is not supportby any other RDBMS

ANSI syntax for Full Outerjoin:-

* supported by all RDBMS except MySQL

select dname, ename from emp full outer join dept on (dept.deptno = emp.deptno);
--------------------------------------------------------------------------------

ANSI syntax for Right Outerjoin:-

* supported by all RDBMS including MySQL

select dname, ename from emp right outer join dept on (dept.deptno = emp.deptno);
--------------------------------------------------------------------------------

ANSI syntax for Left Outerjoin:-

* supported by all RDBMS including MySQL

select dname, ename from emp left outer join dept on (dept.deptno = emp.deptno);
--------------------------------------------------------------------------------

To achieve Full outerjoin in MySQL:-

select dname, ename from emp right outer join dept on (dept.deptno = emp.deptno);
union
select dname, ename from emp left outer join dept on (dept.deptno = emp.deptno);
---------------------------------------------------------------------------------

INNER JOIN
* by default every join is an Inner join putting a (+) sign is what makes it in
Outerjoin

* DO NOT MENTION THIS IN INTERVIEWS UNLESS EXPLICITLY ASK BY INTERVIWER

DATE-26/11/2021
---------------

1. Equijoin
2. Inequijoin
3. Outerjoin

select dname, ename from emp, dept

where ...;

4. Cartesian join
* join without a WHERE condition
* every row of driving table is combined with each and every row of
driven table
* cross product of two tables hence also known as Cross join

Uses:-

a. used for printing purposes

e.g. in STUDENTS table you have all the students names; in SUBJECTS
table
you have all the subjects names; in the University when you are
printing the marksheet
every students name is combine with ecah and every subject name; you
will require
a Cartesian join

dept -> driving table

emp -> driven table

select dname, ename from emp, dept; <- FAST (lesser the input/output between
the
server HDD and
server RAM, the faster
it will execute)

select dname, ename from emp, dept; <- SLOW (more the input/output between
the
server HDD and
server RAM, the slower
it will execute)

DNAME ENAME
----- -----
TRN Arun
TRN Ali
TRN Kirun
TRN Jack
TRN Thomas
EXP Arun
EXP Ali
EXP Kirun
EXP Jack
EXP Thomas
MKTG Arun
MKTG Ali
MKTG Kirun
MKTG Jack
MKTG Thomas

5. Self join

* join a table to itself

* used when parent column and child column both are present in the the
same table
* based on Recursion
* slowest join

select a.ename, b.ename from emp b, emp a where a.mgr = b.empno;

select * from EMP;

+-------+--------+------+--------+------+------+
| EMPNO | ENAME | SAL | DEPTNO | JOB | MGR |
+-------+--------+------+--------+------+------+
| 1 | Arun | 8000 | 1 | M | 4 |
| 2 | Ali | 7000 | 1 | C | 1 |
| 3 | Kirun | 3000 | 1 | C | 1 |
| 4 | Jack | 9000 | 2 | M | NULL |
| 5 | Thomas | 8000 | 2 | C | 4 |
+-------+--------+------+--------+------+------+
5 rows in set (0.00 sec)

mysql> select a.ename, b.ename from emp b, emp a where a.mgr = b.empno;
+--------+-------+
| ename | ename |
+--------+-------+
| Arun | Jack |
| Ali | Arun |
| Kirun | Arun |
| Thomas | Jack |
+--------+-------+

select dname, ename from emp, dept where dept.deptno=emp.deptno;

Right

select dname, ename from emp e, dept d where dept.deptno=emp.deptno;

WRONG
Don't use alias unnesseserely
* when you specify an alias for tablename, a copy of the table is brought into
the server
RAM
* Do not specify an alias for tablename unnessesareiy, because not only will
your
SELECT statement of other users
* Specify an alias for tablename only if you are writing a self-join

* Cartesian join is the fastest join because there is no WHERE clause, and
hence
no searching in involved

Joining 3 or more tables:-

+-------+--------+------+--------+------+------+
| EMPNO | ENAME | SAL | DEPTNO | JOB | MGR |
+-------+--------+------+--------+------+------+
| 1 | Arun | 8000 | 1 | M | 4 |
| 2 | Ali | 7000 | 1 | C | 1 |
| 3 | Kirun | 3000 | 1 | C | 1 |
| 4 | Jack | 9000 | 2 | M | NULL |
| 5 | Thomas | 8000 | 2 | C | 4 |
+-------+--------+------+--------+------+------+

+--------+-------+------+
| DEPTNO | DNAME | LOC |
+--------+-------+------+
| 1 | TRN | MUM |
| 2 | EXP | DEL |
| 3 | MKTG | CAL |
+--------+-------+------+

+--------+-------+
| DEPTNO | DHEAD |
+--------+-------+
| 1 | Arun |
| 2 | Jack |
+--------+-------+

select dname, ename, dhead from emp, dept, depthead where depthead.deptno =
dept.deptno
and dept.deptno=emp.deptno;

+-------+--------+-------+
| dname | ename | dhead |
+-------+--------+-------+
| TRN | Arun | Arun |
| TRN | Ali | Arun |
| TRN | Kirun | Arun |
| EXP | Jack | Jack |
| EXP | Thomas | Jack |
+-------+--------+-------+

Types of Relationships:-

1 : 1 (DEPT : DEPTHEAD) or (DEPTHEAD : DEPT)

1 : Many (DEPT : EMP) or (DEPTHEAD : EMP)
Many : 1 (EMP : DEPT) or (EMP : DEPT)
Many : Many (EMP : PROJECTS) or (PROJECTS : EMP)

* INTERSECTION TABLE is required for Many : Many Relationship

select clientname, project_details, ename from projects_emp, emp, projects

where projects.projectno = projects_emp.projectno and emp.empno =
projects_emp.empno
order by 1, 2, 3;

***********************************************************************************
**
Sub-queries (V.V.Imp)

* also known as Nested queries (Query within Query)

Display the ENAME who is receving sal = min(sal):-

select ename from emp <- Main query (parent)

where sal =
(select min(sal) from emp;) <- Sub-query (child)(inner)

select ename from emp where sal = (select min(sal) from emp);
+-------+
| ename |
+-------+
| Kirun |
+-------+

select ename,sal from emp where sal=(select min(sal) from emp);

+-------+------+
| ename | sal |
+-------+------+
| Kirun | 3000 |
+-------+------+

select ename from emp where sal = (select min(sal) from emp where deptno =
(select...));

* JOIN IS FASTER THAN SUB-QUERY

because when you write a join you solve the problem using 1 select
statement, when you write sub-query you required 2 more SELECT statement
the more the number of select statement

select max(sal) from emp where sal < (select max(sal) from emp);
+----------+
| max(sal) |
+----------+
| 8000 |
+----------+

select ename, max(sal) from emp where sal < (select max(sal) from emp);
+-------+----------+
| ename | max(sal) |
+-------+----------+
| Arun | 8000 |
+-------+----------+

select * from emp where deptno = (select deptno from emp where ename='Thomas');
+-------+--------+------+--------+------+------+
| EMPNO | ENAME | SAL | DEPTNO | JOB | MGR |
+-------+--------+------+--------+------+------+
| 4 | Jack | 9000 | 2 | M | NULL |
| 5 | Thomas | 8000 | 2 | C | 4 |
+-------+--------+------+--------+------+------+

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

select * from emp where job = (select job from emp where ename='Arun');
+-------+-------+------+--------+------+------+
| EMPNO | ENAME | SAL | DEPTNO | JOB | MGR |
+-------+-------+------+--------+------+------+
| 1 | Arun | 8000 | 1 | M | 4 |
| 4 | Jack | 9000 | 2 | M | NULL |
+-------+-------+------+--------+------+------+

select * from emp where job = (select job from emp where ename='Kirun');
+-------+--------+------+--------+------+------+
| EMPNO | ENAME | SAL | DEPTNO | JOB | MGR |
+-------+--------+------+--------+------+------+
| 2 | Ali | 7000 | 1 | C | 1 |
| 3 | Kirun | 3000 | 1 | C | 1 |
| 5 | Thomas | 8000 | 2 | C | 4 |
+-------+--------+------+--------+------+------+

Using sub-queries with DML commands:-

In Oracle:-

delete from emp where deptno=(select deptno from emp where ename = 'Thomas');

update emp set sal = 10000 where job = (select job from emp where ename = 'Kirun');

ABOVE 2 COMMANDS WILL NOT WORK IN MySQL

In MySQL, you cannot UPDATE or DELETE from a table from which are currently
SELECTing:-
Solution for MySQL:-

delete from emp where deptno = (select tempp.deptno from (select deptno from emp
where ename = 'Thomas') as tempp);

update emp set sal = 10000 where job = (select tempp.job from(select job from
emp where ename = 'Kirun') as tempp);

Multi-row sub-queries:-
* Sub-query returns multiple rows

Display all the rows that are receving a SAL equal to any of the Managers:-

select * from emp where sal =any (select sal from emp where job = 'M');
ANY=LOGICAL OR

+-------+--------+------+--------+------+------+
| EMPNO | ENAME | SAL | DEPTNO | JOB | MGR |
+-------+--------+------+--------+------+------+
| 1 | Arun | 8000 | 1 | M | 4 |
| 4 | Jack | 9000 | 2 | M | NULL |
| 5 | Thomas | 8000 | 2 | C | 4 |
+-------+--------+------+--------+------+------+

select * from emp where sal in (select sal from emp where job = 'M');
+-------+--------+------+--------+------+------+
| EMPNO | ENAME | SAL | DEPTNO | JOB | MGR |
+-------+--------+------+--------+------+------+
| 1 | Arun | 8000 | 1 | M | 4 |
| 4 | Jack | 9000 | 2 | M | NULL |
| 5 | Thomas | 8000 | 2 | C | 4 |
+-------+--------+------+--------+------+------+

To exclude the Managers:-

select * from emp where job != 'M' and sal in (select sal from emp where job =
'M');
+-------+--------+------+--------+------+------+
| EMPNO | ENAME | SAL | DEPTNO | JOB | MGR |
+-------+--------+------+--------+------+------+
| 5 | Thomas | 8000 | 2 | C | 4 |
+-------+--------+------+--------+------+------+

select * from emp where sal in (select min(sal) from emp where job = 'M');
+-------+--------+------+--------+------+------+
| EMPNO | ENAME | SAL | DEPTNO | JOB | MGR |
+-------+--------+------+--------+------+------+
| 1 | Arun | 8000 | 1 | M | 4 |
| 5 | Thomas | 8000 | 2 | C | 4 |
+-------+--------+------+--------+------+------+

select * from emp where sal >= (select min(sal) from emp where job = 'M');
+-------+--------+------+--------+------+------+
| EMPNO | ENAME | SAL | DEPTNO | JOB | MGR |
+-------+--------+------+--------+------+------+
| 1 | Arun | 8000 | 1 | M | 4 |
| 4 | Jack | 9000 | 2 | M | NULL |
| 5 | Thomas | 8000 | 2 | C | 4 |
+-------+--------+------+--------+------+------+

To make it work faster:-

1. Join is faster than sub-query; therefore use join wherever possible
2. Try to reduced the number of levels from sub-queries
3. Try to reduced the number of rows returned by sub-query

-----------------------------------------------------------------------------------
----
Assumption 3rd row SAL is 13000:-

select sal from emp where sal > (select sal from emp where job = 'M');

select sal from emp where sal > all (select sal from emp where job = 'M');

select sal from emp where sal > all (select max(sal) from emp where job = 'M'); <-
FASTER

-----------------------------------------------------------------------------------
----
Using sub-query in the HAVING clause:-
In Oracle:-

Display the DNAME that is having max(sum(sal)):-

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

DEPTNO SUM(SAL)
------ --------
1 18000
2 17000

select sum(sal) from group by deptno;

SUM(SAL)
--------
18000
17000

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

MAX(SUM(SAL))
-------------
18000

select deptno, sum(sal) from emp group by deptno having sum(sal) =

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

DEPTNO SUM(SAL)
------ --------
1 18000

select dname, sum(sal) from emp, dept where dept.deptno = emp.deptno

group by dname having sum(sal) =
(select max(sum(sal)) from emp group by deptno);

DNAME SUM(SAL)
------ --------
TRN 18000

In MySQL:-
----------

Display the DNAME that is having max(sum(sal)):-

select sum(sal) from emp group by deptno;

SUM(SAL)
--------
18000
17000

select max(sum_sal) from (select sum(sal) sum_sum from emp group by deptno) as
tempp;

max(sum_sal)
------------
18000

select deptno, sum(sal) from emp group by deptno having sum(sal) =

(select max(sum_sal) from (select sum(sal) sum_sal from emp group by deptno )as
tempp);

DEPTNO SUM(SAL)
------ --------
1 18000

select dname, sum(sal) from emp, dept where dept.deptno=emp.deptno group by dname
having sum(sal) = (select max(sun_sal) from (select sum(sal) sum_sal from emp)
group by deptno)as tempp);

DATE-27/11/2021
---------------
Correlated Sub-query (using the EXISTS operator)
* THIS IS THE EXCEPTION WHEN SUB-QUERY IS FASTER THAN JOIN
* EXISTS in a Special Operator

Display the DNAMEs that contains employee

Soln #1:-

select deptno from emp;

+--------+
| deptno |
+--------+
| 1 |
| 1 |
| 1 |
| 2 |
| 2 |
+--------+

select distinct deptno from emp;

+--------+
| deptno |
+--------+
| 1 |
| 2 |
+--------+

select dname from dept where deptno =any (select distinct deptno from emp);
+-------+
| dname |
+-------+
| TRN |
| EXP |
+-------+

select dname from dept where deptno in (select distinct deptno from emp);
+-------+
| dname |
+-------+
| TRN |
| EXP |
+-------+

select dname from dept where deptno not in (select distinct deptno from emp);
+-------+
| dname |
+-------+
| MKTG |
+-------+

Soln #2:-

select dname from emp, dept where dept.deptno = emp.deptno;

+-------+
| dname |
+-------+
| TRN |
| TRN |
| TRN |
| EXP |
| EXP |
+-------+

select distinct dname from emp, dept where dept.deptno = emp.deptno;

+-------+
| dname |
+-------+
| TRN |
| EXP |
+-------+

* if you have a join, along with distinct to make it work faster, use
Correlated
sub-query (use the EXISTS operator)

Soln #3:-

select dname from dept where exists (select deptno from emp where
dept.deptno=emp.deptno);
+-------+
| dname |
+-------+
| TRN |
| EXP |
+-------+

* 1st the main query is executed

* for every row return by main query, it will run the sub-query once
* the sub-query returns a boolean TRUE or FALSE value back to main query
* if sub-query returns a True value, then main query is eventually executed for
that row
* if sub-query returns a FALSE value, then main query not executed for that row

* unlike earlier, we do not use DISTINCT here; this speeds up

* unlike a traditional join, the number of full table scans is reduced; this
further
speeds it up

select dname from dept where not exists (select deptno from emp where
dept.deptno=emp.deptno);
+-------+
| dname |
+-------+
| MKTG |
+-------+

Set Operators
-------------
* based on set theory

EMP1 EMP2
EMPNO ENAME EMPNO ENAME
1 A 1 A
2 B 2 B
3 C 4 D
5 E

select empno, ename from emp1

union
select empno, ename from emp2;

EMPNO ENAME
----- ------
1 A
2 B
3 C
4 D
5 E

union -> will combine the output of both the SELECT statements and it will suppress
the
duplicates

EMP1 EMP2
EMPNO1 ENAME EMPNO2 ENAME
1 A 1 A
2 B 2 B
3 C 4 D
5 E

select empno1, ename from emp1

union
select empno2, ename from emp2;

select empno1, ename from emp1 where ....

union
select empno2, ename from emp2 group by .....;

select empno1, ename from emp1

union
select empno2, ename from emp2 order by 1;

EMPNO1 ENAME
----- ------
1 A
2 B
3 C
4 D
5 E

select empno1, ename from emp1

union all
select empno2, ename from emp2 order by 1;

EMPNO1 ENAME
----- ------
1 A
1 A
2 B
2 B
3 C
4 D
5 E

select empno1, ename from emp1

intersect
select empno2, ename from emp2 order by 1;

EMPNO1 ENAME
----- ------
1 A
2 B

intersect -> will return what is common in both the SELECT statement and the
duplicate
are suppressed

select empno1, ename from emp1

minus
select empno2, ename from emp2 order by 1;

EMPNO1 ENAME
----- ------
3 C

minus -> will return what is present in the 1st SELECT statement and not present in
the 2nd
SELECT statement, and the duplicates are suppressed

* max upto 255 SELECT statements (this limit of SQL can be exceed using Views)

select job from emp where deptno=10

minus
select job from emp where deptno=20;

JOB
----
PRESEDENT

select job from emp where deptno=10

minus
select job from emp where deptno=20;

JOB
----
PRESEDENT

union, union all -> supported by all RDBMS

intersect, minus -> not supported by MySQL

* Multiple SELECT statement with set operators; brackets for changing the
precedence
-->> not supported by MySQL

Pseudo Columns
* fake columns (virtual columns)

a. Computed columns (e.g. ANNUAL = sal*12)

b. Expressions (e.g. NET_EARNINGS = sal+comm)
c. Function-based columns (e.g TOTAL)

RDBMS supplies Pseudo column:-

ROWID (Row Identifier)
* ROWID is row address
* ROWID is the actual physical memory location in the DB server HD where that
row is stored
* ROWID is fixed-length excryped string of 18 characters
* when you select from a table, the order of rows in the output depends on the
row
address (it will always be in ascending order of ROWID) (searching is
sequential)

NO two rows of any table in the entire DB servers HD can have the same ROWID
ROWID words as an Unique Identifier for every row in the database

YOU CAN USE ROWID TO UPDATE OR DELETE THE DUPLICATE ROWS

select rowid, ename, sal from emp;

delete from emp where rowid = 'AAAAAAAASSSSmmmmAAAAAcgAAAA'

When you UPDATE a row, if the row length is increasing, then the ROWID MAY change

ROWID is used internally by MySQL:-

1. To distinguish between 2 rows in the database
2. For row locking
3. To manage the Indexes
4. To manage the cursors
5. Row management
6. etc.

* In Oracle, feature of ROWID is available and you can view it

* In MySQL, feature of ROWID is available but you cannot view it

ALTER TABLE (DDL Command)

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

EMP
EMPNO ENAME SAL
1 Scott 3000
2 King 5000

* rename a table

rename table tableName to newTableName;

rename table emp to employee;

* RENAME is a DDL command(Auto-commit)

* add a column

alter table tableName add columName datatype;

alter table emp add gst float;

* bydefault add column at LAST and bydefault value in it is NULL

* drop a column

alter table tableName drop column columName;

alter table emp drop column gst;

* increase width of column

 alter table tableName modify columName datatype(size);
alter table emp modify ename varchar(20);

Indirectly:-
* reduced width of column

In MySQL:-
alter table tableName modify columName datatype(size);
alter table emp modify ename varchar(15); <- data will get truncated

In Oracle:-
alter table emp modify ename varchar(15); <- ERROR

* you can reduced the width provided the contents are null

alter table emp add x varchar(25);

update emp set x=ename, ename=null;
alter table emp modify ename varchar(20);

/* DATA TESTING ON X COLUMN, CHECK THE names <= 20 CHARACTERS */

update emp set ename = x;
alter table emp drop column x;

* ABOVE SOLUTION WILL WORK IN MySQL ALSO AND SHOULD BE IMPLEMENTTED

* change datatype of column

alter table emp modify empno char(4);

* copy rows from one table to another table

insert into emp select * from emp2;

to copy certain rows only:-

insert into emp select * from emo2 where where deptno = 10;

* copy a table (for testing purposes)

create table emp_copy as select * from emp;

to copy certain rows only:-

create table emp_copy as select * from emp where deptno=10;

to copy certain columns only:-

create table emp_copy as select empno, ename from emp;

* copy only structure of table

to copy only the structure of table

Method #1:-

create table emp_struct as select * from emp;

delete from emp_struct;

commit;

Method #2:-

create table emp_struct as select * from emp;

truncate table emp_struct; <- truncate will delete all the rows and commit
also

Method #3:-

create table emp_struct as select from emp where 1=2;

#DELETE
* Comman for all RDBMS
* DML command & will delete the rows
* Requires commit
* Rollback possible
* DELETE from emp where

#TRUNCATE
* Extra command MySQL & Oracle
* DDL command
* Will delete ALL the rows & commit
* Auto commit
* Rollback not possible
* Truncate table emp
* Where condition not possible

* rename a column

create table emp_copy as select empno, ename, sal salary from emp;

drop table emp;

rename table emp_copy to emp;

* change position of columns in table structure

(because of null values,for storage considerations)

rename a column

create table emp_copy as select sal, ename, empno from emp;

drop table emp;

rename table emp_copy to emp;

############# SCREEN SHOT ##############

#DELETE
-------
* DELETE triggers on table will execute

#TRUNCATE
* DELETE

When you truncate EMP table, if you want to retain the rows of deptno 10:-
create table emp_copy as select * from where deptno = 10;

truncate table emp;

insert into emp select * from emp_copy;

drop table emp_copy;

DATE-28/11/2021
---------------
INDEXES
-------

EMP
EMPNO ENAME SAL DEPTNO
5 A 5000 1
4 A 6000 1
1 C 7000 1
2 D 9000 1
3 E 8000 2

* present in all RDBMS, all DBMS, and some progamming language also
* to speed up the searching operations (for faster access)
* to speed up the SELECT statement with a WHERE clause
* indexes are automatically invoked by MySQL and when required
* indexes are automatically updated by MySQL for all your DML operations
* no upper limit on the number of indexes per table
* larger the number of indexes, the slower would be the DML operations
* you cannot index text and blob
* duplicate value are not stored in an index
* null values are not stored in an index
* if you have 2 or more INDEPENDENT columns in the WHERE clause then create;
seperate indexes for each column; MySQL will use both the indexes as and when
required

COMPOSITE INDEX -> combine 2 or more INTER-DEPENDENNT columns in the single index

INDEX KEY -> Column or set of column on whose basis the index has been created

* you can combine upto 32 columns in the composite index

Conditions when an index should be created:-

* if SELECT statement has a WHERE clause

* if SELECT statement retrieves < 25% of table data
* Primary key and Unique columns should always be indexed
* common columns in join operations should always be indexed

create index IndexName on tableName(ColumnName);

create index i_emp_empno on emp(emono);

create index i_emp_empno on emp(ename);
create index i_emp_empno on emp(sal);

select * from emp where empno=1;

select * from emp where ename='A';
select * from emp where sal=5000;
to see which all indexes are created for a particular table:-

show indexes from emp;

to see all the index on all the tables in the database:-

use information_schema;
select * from statistics;

* Statistics is a system table

* by default all indexes are in ascending order

(common for all RDBMS)

create index i_emp_empno on emp(empno desc);

create index i_emp_onum on emp(onum desc);

to drop the index:-

drop index i_emp_empno on emp;

create unique index i_emp_empno on emp(empno);

-->> performs one extra function; it won't allow the user to INSERT duplicate
values
for EMPNO

Types of Indexes:-
1. Normal Index
2. Unique Index
3. Clusterd Index etc.

Privileges:-
------------
Grant and Revoke

pgdac1_mysql> grant select on emp to scott;

pgdac1_mysql> grant insert on emp to scott;
pgdac1_mysql> grant update on emp to scott;
pgdac1_mysql> grant delete on emp to scott;

pgdac1_mysql> grant select, insert on emp to scott;

pgdac1_mysql> grant all on emp to scott;

pgdac1_mysql> grant select on emp to scott,user2,user3;

pgdac1_mysql> grant select, insert on emp to scott,user2,user3;

pgdac1_mysql> revoke select on emp from scott;

to see the granted and received permission:-

select * from information_schema

samdehadrai@yahoo.com
contact@sameerdehadrai.com
Mob : 9820134740

DATE-29/11/2021
---------------+

EMP
EMPNO ENAME SAL DEPTNO
1 A 5000 1
2 B 6000 1
3 C 7000 1
4 D 9000 2
5 E 8000 2

CONSTRAINTS
------------
* limitations/restriction imposed on a table

PRIMARY KEY (primary column)

* column or set of columns that uniquely identifies a row
* duplicate values are not allowed (has to be unique)
* null values are not allowed (is a mandotory compulsory)
* it's recommended that every table should have a Primary key
(helps from a long-term prespective)
* purpose of primary key is row uniqueness (with the help of primary key you
can distinguish between 2 rows of a table)
* text and blob cannot be primary key
* unique index automatically created
* COMPOSITE PRIMARY KEY -> combine 2 or more columns together to server the
purpose
of primary key
* can combine upto 32 columns in a composite primary key
* if you have a composite primary key, then composite unique index
automatically created
* YOU CAN HAVE ONLY 1 PRIMARY KEY CONSTRAINT PER TABLE

CONDIDATE KEY -> is not a constraint

CONDIDATE KEY -> is a definition
CONDIDATE KEY -> besides the primary column, any other column in the table that can
also serve the purpose of primary key, is a good candidate for primary key, is
known
as Candidate key

Steps for identifying Primary key:-

1. Key element willbe Primary key of your table
2. If you cannot identify some element, then try for composite primary key
3. If you cannot identify composite primary key, then add an extra column to the
table
to serve the purpose of primary key

SURROGATE KEY -> is not a constraint

SURROGATE KEY -> is a definition
SURROGATE KEY -> if you add an extra column to the table to server the purpose of
Primary
key, then such a Primary key is known as Surrogate key (then char datatype is
recommended)

create table emp(empno char(4) primary key, ename varchar(25), sal float, deptno
int);
mysql> insert into emp values ('5','F',5000,2); <- ERROR

* all constraint are at server level (you may perform the DML operations using
any
front-end s/w, the constraints will always be valid)
* internally a constraint is a MySQL created function; it performs the
validations

select * from information_schema.table_constraints;

select * from information_schema.table_constraints where table_schema =
'cdacmumbaipgdacsep2021'

select * from information_schema.key_column_usage where table_name = 'emp';

* unique index automatically created

show indexes from emp;

to drop the constraint:-

alter table emp drop primary key;

to add the constraint:-

alter table emp add primary key(deptno, empno);

Constraints are of 2 types:-

1. Column level constraint (specified on 1 column)

2. Table level constraint (specified on 2 or more columns) (composite)(has to
be specified
at the end of the structure)

NOT NULL
---------

* null values are not allowed (is a mandatory column)

* duplicate values are allowed (unlike primary key)
* you can have any number of not null constraints per table (unlike primary
key)
* you cannot have a composite not null constraint

create table emp(empno char(4), ename varchar(25) not null, sal float not null,
deptno int);

* in MySQL, nullability is a part of the datatype

to see the not null columns:-

describe tableName;
desc tableName;

to add the not null constraint afterwards, to an existing table:-

alter table emp modify ename varchar(25) not null;

do drop the not null constraint:-

alter table emp modify ename varchar(25) null;

Solution for candidate key columns:-

not null constraint + unique index
* with the help of above, you can "indirectly" have multiple Primary key
constraint in a
table

ALTERNATE KEY -> is not a constraint

ALTERNATE KEY -> is a definition
ALTERNATE KEY -> for the candidate key column, if you specify a not null constraint
and you create an unique index, then it becomes an alternative to Primary key; then
such a Candidate key is known as ALTERNATE KEY

SUPER KEY -> is not a constraint

SUPER KEY -> is a definition
SUPER KEY -> if you have an Alternate key in the table, then the primary key column
is known as Super key

UNIQUE
-------
* duplicate values are not allowed (similar to primary key)
* null values are allowed (you can specify any number of null values)
* text and blob cannot be unique
* unique index is created automatically
* you can combine upto 32 columns in a composite unique
* YOU CAN HAVE ANY NUMBER OF UNIQUE CONSTRAINTS

create table emp(empno char(4), ename varchar(25) not null, sal float not null,
deptno int, mob_no char(15) unique, <- Column level constraint
unique(deptno,empno)); <- table level constraint

select * from information_schema.table_constraints;

select * from information_schema.table_constraints where table_schema =

'cdacmumbaipgdacsep2021';

select * from information_schema.key_column_usage where table_name = 'emp';

show index from emp;

drop index mob_no on emp;

drop index deptno on emp;

alter table emp add unique(mob_no);

or
alter table emp add constraint u_emp_mob_no unique(mob_no);

constraint u_emp_mob_no -> this is the constraint name

constraint u_emp_mob_no -> optional

* column level constraint can be specified at table level (at the end of the
structure
), but a table level composite constraint can never be specified at column level
* column level constraint can be specified at table level (at the end of the
structure)
except for the not null constraint, which is always a column level constraint, and
therefore
the syntax will not support specifying it at table level (at the end of structure)
 EMP DEPT
=== =====
EMPNO ENAME SAL DEPTNO MGR DEPTNO DNAME LOC
----- ----- --- ------ --- ------ ----- ---
1 A 5000 1 1 1 TRN
MUM
2 B 6000 1 1 2 EXP
DEL
3 C 7000 1 1 3 MKTG CAL
4 D 9000 2 2
5 E 8000 2 2
6 F 9000 2 2

Primary key, Not null, Unique

FOREING KEY (Foreign column) (column that has been derived from elsewhere)
* Column or set of columns that references a columnor set of columns of some
table
* Foreign key constraint is specified on child column (not the parent column)
* parent column has to be Primary key or UNIQUE
(this is pre-requisite for Foreign key)
* Foreign key (child column) will allow duplicate values (unless specified
otherwise)
* text and blob cannot be Foreign key
* index for the child column is not created automatically over here
(if you want the index then you will have to create it manually)
* Foreign key (child column) may reference column of same table
(known as Self-reference)

create table dept(deptno int primary key, dname varchar(15), loc varchar(10));

create table emp(empno char(4) primary key, ename varchar(25), sal float, deptno
int,
mgr char(4), constraint fk_emp_deptno foreing key(deptno), references deptno,
constraint fk_emp_mgr foreing key(mgr) reference emp(empno));

constraint fk_emp_deptno -> optional

constraint fk_emp_mgr -> optional

select * from information_schema.table_constraints;

select * from information_schema.table_constraints

where table_schema = 'cdacmumbaipgdacsep2021';

select * from information_schema.key_column_usage where table_name = 'emp';

to drop the constraint:-

alter table emp drop foreing key fk_emp_deptno;

* you can delete the parent row provided child rows don't exist

1) delete from emp where deptno=2;

2) delete from dept where deptno=2;

create table emp(empno char(4) primary key, ename varchar(25), sal float, deptno
int,
mgr char(4), constraint fk_emp_deptno foreing key(deptno) on delete cascade,
references deptno,
constraint fk_emp_mgr foreing key(mgr) reference emp(empno));

ON DELETE CASCADE -> if you delete the parent row then MySQL will automatically
delete
the child rows also delete the child rows also

ON UPDATE CASCADE -> if you update the parent column then MySQL will update the
child rows also

delete from emp where empno=1;

* avoid on delete cascade in the event of self-referencing, you may delete more
rows than expected
* it's safer to use on delete cascade across 2 tables

CHECK constraint
----------------

create table emp

(empno int auto_increment primary key,
ename varchar(25) check(ename = upper(ename)),
sal float default 7000
check (sal between 5001 and 1999999),
deptno int,
status char(1) default 'T'
check(status in('T','P','R')),
comm float not null,
mob_no char(15) unique,
check(sal+comm < 3000000),
constraint fk_emp_deptno(deptno) references dept(deptno)
);

* used for validations (used for checking purposes)

e.g. delydate >= orderdate, age>21, etc

* if you specifysome value,then it will take that value, if nothing is

specified,
then it will take DEFAULT value
* DEFAULT is nota constraint
* DEFAULT is a clause that you can use with CREATE TABLE
* to make use of DEFAULT value and AUTO_INCREMENT, use the following
INSERT statement:-

insert into emp(ename, deptno, comm,mob_no) values(................);

* Relation operators
* Logical operators
* Arithmetic operators
* Special operators
e.g. BETWEEN, in, like, etc.
* all single-row functions
e.g. upper, substr, etc.

DATE-30/11/2021
----------------