SQL Notes

~~~~~~~~~~~~~~~~~
-----------------------------------------------------------------------------------
---
Data
~~~~~~~~~~~
Collection of information

Database
~~~~~~~~~~~~~
Collection of related information stored at one place.

Table
~~~~~~
Table consists of rows and columns

Rows
~~~~~~~~
Horizontal ones

Columns
~~~~~~~~~~
Vertical ones

1 Row = 1 Record
Each data is stored in 1 Record

DBMS
~~~~~~~~~~~
Database Management System

Application or Software used to store and manage database.

Examples of DBMS
~~~~~~~~~~~~~~~~~~
1. MySQL Open Source Oracle (Sun Microsystems)
2. SQL Server Microsoft
3. Oracle Oracle
4. PostgresSQL Open Source

CRUD Operations
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
C Create CREATE
R Read SELECT
U Update UPDATE
D Delete DELETE / DROP

Features of DBMS
~~~~~~~~~~~~~~~~~
-- Interface between Database and User
-- Software to store , retrieve, define and manage data in the database.
-- Easy CRUD Operations.
-- Takes care of authentication(security), authorization, concurrency, logging,
backup, optimization etc.
-- should support connection using other applications and other programming
languages.

RDBMS
~~~~~~~~~~~~~
Relational Database Management Systems

Examples of RDBMS
~~~~~~~~~~~~~~~~~~
1. MySQL Open Source Oracle (Sun Microsystems)
2. SQL Server Microsoft
3. Oracle Oracle
4. PostgresSQL Open Source

Datatypes in MySQL
~~~~~~~~~~~~~~~~~~~~~
20 number
"twenty" string
'twenty' string

String
~~~~~~~~~
set of characters
125 ASCII Characters
either double quotes ""
or single quotes ''

Examples:
~~~~~~~~~~
"abcd!@#$"
'sql'
"mysql"
"20"

Datatypes for String

~~~~~~~~~~~~~~~~~~~~~~~~
-----------------------------------------------------------------------------------
------------------------------------------------------------
Datatype Maximum number of bytes
-----------------------------------------------------------------------------------
--------------------------------------------------------------
CHAR(10) Max 255 bytes
VARCHAR(10) Max 65, 535 bytes
-----------------------------------------------------------------------------------
---------------------------------------------------------------------

Example

CHAR(10) JOHN => 10 bytes

VARCHAR(10) JOHN => 4 bytes

CHAR When the size of charcaters is fixed

VARCHAR When the size of characters is unknown

Text
~~~~~
-----------------------------------------------------------------------------------
--------------------------------------------------------------
Text Type Maximum number of bytes
-----------------------------------------------------------------------------------
----------------------------------------------------------------
TINYTEXT 255
TEXT 65,535
MEDIUMTEXT 16,777,215
LONGTEXT 4,294,967,295
-----------------------------------------------------------------------------------
-----------------------------------------------------------------

Datatype for storing files

BLOB Binary Large Object File

BLOB Type
~~~~~~~~~~~~~
TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB

-----------------------------------------------------------------------------------
-------------------------------------------
Numbers / Numericals
~~~~~~~~~~~~~~~~~~~~~~~
1. Whole Number 1, -1
2. Decimal Number 1.0, -1.0

Whole Number
~~~~~~~~~~~~~~~~
-----------------------------------------------------------------------------------
-------------------------------------------------------------
Whole Number Type Signed Range
-----------------------------------------------------------------------------------
----------------------------------------------------------------
TINYINT -128 to 127
SMALLINT -32,768 to 32,767
MEDIUMINT -8,388,608 to 8,388,607
INT -2,147,483,648 to 2,147,483,647
BIGINT -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

-----------------------------------------------------------------------------------
----------------------------------------------------------------
specifying size of digits for INT datatype is optional INT(10) OR INT

Decimal Number
~~~~~~~~~~~~~~~~~
DECIMAL(TOTAL_DIGITS, TOTAL_DECIMAL_DIGIT)

Example
~~~~~~~~~~~~~~~~~
123.4 => DECIMAL(4, 1)
123.45 => DECIMAL(5, 2)
12345.6789 => DECIMAL(9, 4)
123456789.0 => DECIMAL(9, 0)
12345.10 => DECIMAL(7, 2)

Date
~~~~~~~~~
-----------------------------------------------------------------------------------
----------------------------------------------------
Date Datatype Default Format Allowable Values
-----------------------------------------------------------------------------------
----------------------------------------------------
DATE YYYY-MM-DD 1000-01-01 to 9999-12-31
DATETIME YYYY-MM-DD HH:MI:SS 1000-01-01 00:00:00 to 9999-12-31 23:59:59
TIMESTAMP YYYY-MM-DD HH:MI:SS 1970-01-01 00:00:00 to 2037-12-31 23:59:59
YEAR YYYY 1901 to 2155
TIME HHH:MI:SS -838:59:59 to 838:59:59
-----------------------------------------------------------------------------------
---------------------------------------------------

Example
~~~~~~~~~~~~~
DATETIME Custom Date and Time / User Specified Date and Time
e.g. Date of Birth, Train Time, Bus Time

TIMESTAMP System Date and Time

e.g. Login Time, Logout Time, Start time, End Time, Check In / Check Out, Ticket
Booking Time

To store duration we will use TIME datatype

SQL - Structured Query Language - Sequel

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Classification of SQL Statement / SQL Commands
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DDL - Data Definition Language
DML - Data Manipulation Language
DCL - Data Control Language
TCL - Transaction Control Language
DRL / DQL - Data Retrivel Language / Data Query Language

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

DDL - Data Definition Language

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE
ALTER
DROP
RENAME
ADD
MODIFY
TRUNCATE

-- comments in mysql
-- two hyphen symbols then space then comments

-- show all the databases in mysql server

SHOW DATABASES;

-- create a database monkey

CREATE DATABASE monkey;

SHOW DATABASES;

-- delete database monkey in mysql server

DROP DATABASE monkey;
SHOW DATABASES;

CREATE DATABASE monkey;

-- it will show error as database already present

CREATE DATABASE monkey;

-- it will not show error if database is already present

CREATE DATABASE IF NOT EXISTS monkey;

DROP DATABASE monkey;

-- it will show error as database does not exist

DROP DATABASE monkey;

-- it will not error if database does not exist

DROP DATABASE IF EXISTS monkey;

CREATE DATABASE IF NOT EXISTS monkey;

SHOW DATABASES;
USE monkey;

-- create a new table student

CREATE TABLE student (
name VARCHAR(50)
);

SHOW TABLES;

-- to show the structure of the table

DESCRIBE student;
DESC student;

-- to delete table
DROP TABLE student;

SHOW TABLES;

-- it will not throw error if the table is not available. it will show only warning
DROP TABLE IF EXISTS student;

PRIMARY KEY === UNIQUE + NOT NULL

-- add primary key to column in table

-- adding primary key at the last after declaring all the coumns
CREATE TABLE student (
id INT,
name VARCHAR(50),
PRIMARY KEY(id)
);
DESCRIBE student;
DROP TABLE student;

-- adding primary key directly to the column in table

CREATE TABLE student (
id INT PRIMARY KEY,
name VARCHAR(50)
);
DESCRIBE student;
DROP TABLE student;

-- add NOT NULL condition to column

CREATE TABLE student (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
DESCRIBE student;
DROP TABLE student;

-- add DEFAULT condition to column

CREATE TABLE student(
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
city VARCHAR(50) DEFAULT 'bangalore'
);
DESCRIBE student;
DROP TABLE student;

-- add UNIQUE condition to column

CREATE TABLE student(
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
city VARCHAR(50) DEFAULT "bangalore",
aadhaar BIGINT UNIQUE
);
DESCRIBE student;
DROP TABLE student;

NOT NULL + UNIQUE === PRIMARY KEY

-- column having NOT NULL and UNIQUE will automatically become PRIMARY KEY
CREATE TABLE student (
name VARCHAR(50) NOT NULL,
city VARCHAR(50) DEFAULT "bangalore",
aadhaar BIGINT NOT NULL UNIQUE
);
DESCRIBE student;
DROP TABLE student;

-- table will have only one primary key

CREATE TABLE student (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
city VARCHAR(50) DEFAULT 'bangalore',
aadhaar BIGINT NOT NULL UNIQUE
);
DESCRIBE student;
DROP TABLE student;

-----------------------------------------------------------------------------------
-------------------------------------------------
-- TASK 1
-- create customer table
+---------------+-----------------+----------+----------+-------------+---------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-----------------+----------+----------+-------------+---------+
| id | int | NO | PRI | NULL |
|
| name | varchar(50) | NO | | NULL | |
| mobile | int | YES | UNI | NULL |
|
| gender | char(1) | YES | | M |
|
+--------------+------------------+---------+----------+--------------+--------+

-- create customer table

CREATE TABLE customer (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
mobile INT UNIQUE,
gender CHAR(1) DEFAULT "M"
);
DESCRIBE customer;
DROP TABLE customer;

-----------------------------------------------------------------------------------
-------------------------------------------
-- TASK 2
-- create person table

+-------------+------------------+-------+-------+---------------+---------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+-------+-------+---------------+---------+
| id | int | NO | PRI | NULL | |
| name | varchar(50) | NO | | NULL | |
| aadhaar | bigint | YES | UNI | NULL | |
| city | varchar(50) | YES | | mysore | |
| state | varchar(50) | YES | | karnataka | |
| country | varchar(50) | YES | | india | |
+------------+------------------+--------+-------+----------------+-------+

-- by default AUTO_INCREMENT starts at 1

-- to change the starting number of AUTO_INCREMENT
ALTER TABLE student
AUTO_INCREMENT = 101;

DROP TABLE student;

-- add CHECK condition to column

CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
city VARCHAR(50) DEFAULT "bangalore",
aadhaar INT UNIQUE,
age INT CHECK(age > 17)
);
DESCRIBE student;
DROP TABLE student;

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

-- ALTER -- RENAME

-- add new column

-- change column datatype
-- change column name
-- delete column
-- change table name

SHOW TABLES;

CREATE TABLE student(

id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
city VARCHAR(50) DEFAULT "bangalore",
aadhaar INT UNIQUE,
age INT CHECK(age > 17)
);
DESCRIBE student;

-- add new column

ALTER TABLE student
ADD COLUMN state VARCHAR(50);
DESCRIBE student;

-- change column datatype

ALTER TABLE student
MODIFY COLUMN state CHAR(50);
DESCRIBE student;

-- change column name

ALTER TABLE student
RENAME COLUMN state TO student_state;
DESCRIBE student;

-- delete column
ALTER TABLE student
DROP COLUMN student_state;
DESCRIBE student;

-- change table name

RENAME TABLE student TO stu;
SHOW TABLES;

RENAME TABLE stu TO student;

SHOW TABLES;

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

-- TRUNCATE
~~~~~~~~~~~~~~~~
-- Only the data or all the records will be deleted and Table structure will not be
deleted

TRUNCATE TABLE student;

TRUNCATE student;
SHOW TABLES;
DESCRIBE student;

-----------------------------------------------------------------------------------
-----------------------------------------------------------
-- DML - Data Manipulation Language

-- INSERT
-- UPDATE
-- DELETE

-----------------------------------------------------------------------------------
-----------------------------------------------------
-- INSERT
~~~~~~~~~~~~~

INSERT INTO student

(name, age)
VALUES
("surya", 40);

-- to show all the columns / fields from the table

SELECT *
FROM student;

-- when inserting data without specifying column names

-- 1. enter data for all the fields. Value count should match with column count.
-- 2. chances of data getting inserted in wrong columns.
-- 3. insert data in the same order of columns in table.

-- inserting without specifying column names

INSERT INTO student
VALUES
(2, "dhanush", "mysore", 123456, 50);

SELECT *
FROM student;

-- chances of data getting inserted in wrong columns

INSERT INTO student
VALUES
(3, "mumbai", "shreyas", 987654, 29);

SELECT *
FROM student;

INSERT INTO student

(city, age, name)
VALUES
("mandya", 23, "dharshan");

SELECT *
FROM student;

-- inserting multiple set of values

INSERT INTO student
(name, city, age)
VALUES
("preetam", "hassan", 24),
("ramya", "chennai", 30),
("divya", "malur", 21),
("rajnikanth", "mangalore", 18)
;

SELECT *
FROM student;
-- it will throw error as check constraint is violated
INSERT INTO student
(name, city, age)
VALUES
("katappa", "dholakpur", 17);

-- to show specific column from the table

SELECT name, city
FROM student;

-- ORDER BY
-- Default is ascending
-- ASC ascending
-- DESC descending

-- Ascending Order
SELECT *
FROM student
ORDER BY name;

-- Descending Order
SELECT *
FROM student
ORDER BY name DESC;

SELECT city, name, age

FROM student
ORDER BY city;

SELECT city, name, age

FROM student
ORDER BY city, name DESC;

DROP TABLE student;

-- Constraints in MYSQL
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- NOT NULL
-- AUTO_INCREMENT
-- UNIQUE
-- DEFAULT
-- CHECK
-- PRIMARY KEY
-- FOREIGN KEY

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

-- give name "lion" for CHECK CONSTRAINT

CREATE TABLE student (
id INT PRIMARY KEy AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
city VARCHAR(50) DEFAULT "bangalore",
salary INT,
CONSTRAINT lion CHECK(salary > 10000)
);
DESCRIBE student;

INSERT INTO student

(name, city, salary) VALUES
("kiran", "bangalore", 15000),
("smruti", "kumta", 20000),
("aparna", "kolar", 25000),
("srivathsa", "kanakapura", 30000),
("pruthvi", "shimoga", 35000),
("naveed", "shimoga", 40000),
("sachin", "shimoga", 45000),
("tanush", "hosadurga", 50000),
("pavan sai", "bagepalli", 55000),
("nishan", "mysore", 60000),
("kaushik", "udupi", 65000),
("srinivas", "bangalore", 70000),
("rahul choudhary", "bangalore", 75000),
("dhanush", "tumkur", 80000),
("swamy b. c.", "channagiri", 85000),
("manjunath b. h.", "shimoga", 90000),
("chandan", "hubli", 95000),
("narendara", "channagiri", 100000),
("narasimhulu", "anantapur", 105000),
("aditya", "mangalore", 110000),
("nuthan gowda", "tumkur", 115000),
("dayasagar", "davangere", 120000),
("amar", "bidar", 125000),
("ajay", "gulbarga", 130000),
("prajwal", "mysore", 135000),
("srinath r", "chikkaballapur", 45000)
;
SELECT *
FROM student
ORDER BY name, city;

-- clauses
-- WHERE clause
-- filter the rows

Mathematical Operators
~~~~~~~~~~~~~~~~~~~~~~~~~
< Less Than
> Greater Than
<= Less Than Or Equal To
>= Greater Than Or Equal To
= Equal To
<> Not Equal To
!= Not Equal To

Logical Operators
~~~~~~~~~~~~~~~~~~~~~
AND Logical And Operator
OR Logical Or Operator
NOT Logical Not Operator

BETWEEN Between certain range of numbers

IN Multiple possible values for a column
LIKE Search for a pattern

-- less than operator

SELECT *
FROM student
WHERE salary < 50000;

-- greater than operator

SELECT *
FROM student
WHERE salary > 50000;

-- equal to operator
SELECT *
FROM student
WHERE salary = 50000;

SELECT *
FROM student
WHERE city = "shimoga";

-- less than or equal to operator

SELECT *
FROM student
WHERE salary <= 50000;

-- greater than or equal to operator

SELECT *
FROM student
WHERE salary >= 50000;

-- not equal to operator

SELECT *
FROM student
WHERE salary <> 50000
ORDER BY salary;

-- not equal to operator

SELECT *
FROM student
WHERE salary != 50000
ORDER BY salary;

-- IN

-- search for students from bangalore and mysore

SELECT *
FROM student
WHERE city IN ("bangalore", "mysore")
ORDER BY city, name;

-- Logical Operator NOT

-- NOT
-- NOT(TRUE) === FALSE
-- NOT(FALSE) === TRUE

-- NOT IN
-- search for students who are not from bangalore and mysore
SELECT *
FROM student
WHERE city NOT IN ("bangalore", "mysore")
ORDER BY city, name;
-- NOT
SELECT NOT 0;
SELECT NOT 1;

-- output => NULL

SELECT NOT (NOT NULL);

-- output => NULL

SELECT NOT NULL;

-- alias using AS keyword

-- AS
SELECT NOT (NOT NULL) AS output;
SELECT NOT (NOT NULL) AS "output";
SELECT NOT (NOT NULL) AS "sun shine";
SELECT NOT (NOT NULL) AS sun_shine;

TASK
-- show all the students who are not from bangalore using NOT keyword and without
using IN keyword
SELECT *
FROM student
WHERE NOT city = "bangalore";

-- alias using AS keyword

SELECT name AS "student name"
FROM student;

-- alias without using AS keyword

SELECT name "student name"
FROM student;

-- Logical Operator AND

-- left and right conditions should be from different columns and not from same
columns

-- left condition AND right condition

-- true AND true => output
-- true AND false => empty set
-- false AND true => empty set
-- false AND false => empty set

-- output will come

SELECT *
FROM student
WHERE name = "naveed" AND city = "shimoga";

-- empty set
SELECT *
FROM student
WHERE city = "bangalore" AND city = "mysore";

-- empty set
SELECT *
FROM student
WHERE name = "naveed" AND city = "bangalore";

-- Logigal Operator OR
-- left condition OR right condition
-- true OR true => output
-- true OR false => output
-- false OR true => output
-- false OR false => empty set

-- output will come

SELECT *
FROM student
WHERE name = "naveed" OR city = "shimoga";

-- output will come

SELECT *
FROM student
WHERE name = "sachin" OR name = "kiran";

-- empty set
SELECT *
FROM student
WHERE name = "sql" OR name = "java";

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

-- BETWEEN
-- between certain range of numbers
-- BETWEEN -- AND
-- BETWEEN for numbers - should compare between from lowest number to highest
number
-- BETWEEN for characters - should compare from A to Z (alphabetically)

-- output will come

SELECT *
FROM student
WHERE salary BETWEEN 40000 and 80000
ORDER BY salary;

-- empty set
SELECT *
FROM student
WHERE salary BETWEEN 80000 AND 40000
ORDER BY salary DESC;

SELECT *
FROM student
ORDER BY city;

-- it will not take values which starts with "u"

SELECT *
FROM student
WHERE city BETWEEN "a" AND "u"
ORDER BY city;

-- empty set
SELECT *
FROM student
WHERE city BETWEEN "u" AND "a"
ORDER BY city;

-- if we want "u" we have to include the next character which is "v"

SELECT *
FROM student
WHERE city BETWEEN "a" AND "v"
ORDER BY city;

SELECT *
FROM student
WHERE city BETWEEN "gulbarga" AND "mysore"
ORDER BY city;

SELECT *
FROM student
WHERE city BETWEEN "mysore" AND "gulbarga"
ORDER BY city;

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

-- LIKE
-- Wild Card Search

-- 1. % percentage symbol
-- 2. _ underscore symbol

-- % percentage symbol
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- zero or more unknown characters
-- multiple unknown characters
-- length of characters is also not known

-- _ underscore symbol
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- one single unknown character

USAGE of LIKE
~~~~~~~~~~~~~~

-- search for city whose starting character is "b"

SELECT *
FROM student
WHERE city LIKE "b%";

-- search for city whose last character is "e"

SELECT *
FROM student
WHERE city LIKE "%e";

-- search for city whose starting character is "b" and ending character is "e"
SELECT *
FROM student
WHERE city LIKE "b%e";

-- search for city which contains kk

SELECT *
FROM student
WHERE city LIKE "%kk%";
-- search for the city whose characters are unknown and also the length of
characters is unknown
SELECT *
FROM student
WHERE city LIKE "%";

-- search for name whose second character from start and sixth character from end
is "h"
SELECT *
FROM student
WHERE name LIKE "_h_____";

-- search for name whose third character from the last is "u"
SELECT *
FROM student
WHERE name LIKE "%u__";

-- search for name having only 6 characters

SELECT *
FROM student
WHERE name LIKE "______";

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

-- UPDATE
~~~~~~~~~~~~~~~~~~~

-- update the city of the specific student

UPDATE student
SET city = "goa"
WHERE name = "sachin";

SELECT *
FROM student
WHERE name = "sachin";

-- DELETE
~~~~~~~~~~~~~

-- delete the data of student sachin

DELETE
FROM student
WHERE name = "sachin";

-- empty set
SELECT *
FROM student
WHERE name = "sachin";

-- caution
-- do not forget to use WHERE clause in case of UPDATE and DELETE operations

-----------------------------------------------------------------------------------
-------------------------------------------------------------
-- table branch
-- table employee
-- create table branch
CREATE TABLE branch (
branch_id INT PRIMARY KEY AUTO_INCREMENT,
branch_name VARCHAR(50) UNIQUE NOT NULL
);

DESCRIBE branch;

-- by default AUTO_INCREMENT starts at 1

-- to change the starting number of AUTO_INCREMENT to 101
ALTER TABLE branch
AUTO_INCREMENT = 101;

INSERT INTO branch

(branch_name)
VALUES
("bangalore"),
("mysore"),
("hubli"),
("mangalore"),
("hassan"),
("davangere"),
("bellary"),
("bidar"),
("dharwad"),
("kolar"),
("raichur"),
("belgaum"),
("hampi"),
("tumkur"),
("mandya"),
("shimoga"),
("udupi")
;

SELECT *
FROM branch
ORDER BY branch_name;

SELECT *
FROM branch
ORDER BY branch_id;

-- create table employee

-- with check condition name "panda" for salary greater than 10000
CREATE TABLE employee (
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(50) NOT NULL,
job_desc VARCHAR(50) DEFAULT "NO DEPT",
salary INT,
CONSTRAINT panda CHECK (salary > 10000),
br_id INT,
FOREIGN KEY (br_id) REFERENCES branch (branch_id)
);

DESCRIBE employee;
DROP TABLE employee;

-- create table employee

-- with foreign key name "dinosaur"
-- with check condition name "panda" for salary greater than 10000
CREATE TABLE employee (
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(50) NOT NULL,
job_desc VARCHAR(50) DEFAULT "NO DEPT",
salary INT,
CONSTRAINT panda CHECK (salary > 10000),
br_id INT,
CONSTRAINT dinosaur FOREIGN KEY (br_id) REFERENCES branch (branch_id)
);

DESCRIBE employee;
DROP TABLE employee;

-- create table employee

-- with foreign key name "dinosaur"
-- with check condition name "panda" for salary greater than 10000
CREATE TABLE employee (
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(50) NOT NULL,
job_desc VARCHAR(50) DEFAULT "NO DEPT",
salary INT CONSTRAINT panda CHECK (salary > 10000),
br_id INT,
CONSTRAINT dinosaur FOREIGN KEY (br_id) REFERENCES branch (branch_id)
);

DESCRIBE employee;
DROP TABLE employee;

-- create table employee without column br_id and FOREIGN KEY

CREATE TABLE employee (
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(50) NOT NULL,
job_desc VARCHAR(50) DEFAULT "NO DEPT",
salary INT CHECK ( salary > 10000)
);

DESCRIBE employee;

-- add column br_id

ALTER TABLE employee
ADD COLUMN br_id INT;

DESCRIBE employee;

-- add FOREIGN KEY with name "gorilla"

ALTER TABLE employee
ADD CONSTRAINT gorilla FOREIGN KEY (br_id) REFERENCES branch (branch_id);

DESCRIBE employee;

-- delete FOREIGN KEY by using name "gorilla"

-- delete CONSTRAINT by using name "gorilla"
ALTER TABLE employee
DROP CONSTRAINT gorilla;

DESCRIBE employee;
-- add foreign key without CONSTRAINT or without name
ALTER TABLE employee
ADD FOREIGN KEY (br_id) REFERENCES branch (branch_id);

DESCRIBE employee;

-- server assigned FOREIGN KEY name

-- employee_ibfk_1

-- pattern the mysql server follows to give name for FOREIGN KEY
-- tableName_ibfk_serial

-- delete FOERIGN KEY by using server assigned name "employee_ibfk_1"

ALTER TABLE employee
DROP FOREIGN KEY employee_ibfk_1;

DESCRIBE employee;

-- insert data into table employee

INSERT INTO employee
(emp_name, job_desc, salary, br_id)
VALUES
("kiran r. s.", "ADMIN", 15000, 101),
("siddaram", "MANAGER", 20000, 108),
("naveed n.", "CEO", 25000, 116),
("sachin b. m.", "HR", 30000, 116),
("nishan s. m.", "SALES", 35000, 102),
("srinath r.", "ENGINEER", 40000, 101),
("aparna", "ENGINEER", 45000, 110),
("rakshitha", "HR", 50000, 116),
("rahul choudhary", "MANAGER", 55000, 101),
("kaushik", "SALES", 60000, 117),
("srinivas c.", "ADMIN", 65000, 101),
("srivathsa", "ENGINEER", 70000, 101),
("pavan sai", "MANAGER", 75000, 101),
("tanush", "SALES", 80000, 116),
("debneet", "HR", 85000, 101),
("swami b. c.", "ENGINEER", 90000, 106),
("narendra", "MANAGER", 95000, 106),
("chandan", "ENGINEER", 100000, 103),
("vinayak", "ADMIN", 105000, 112),
("adithya", "MANAGER", 110000, 104),
("ajay", "HR", 115000, 103),
("dayasagar", "ENGINEER", 120000, 106),
("nuthan gowda", "SALES", 125000, 114),
("rakshath", "CTO", 130000, 102),
("narasimhulu", "ADMIN", 135000, 101)
;
SELECT *
FROM employee
ORDER BY emp_name, job_desc;

SELECT *
FROM employee
ORDER BY job_desc, emp_name;

-----------------------------------------------------------------------------------
-------------------------------------------------
ONLINE RESOURCES
~~~~~~~~~~~~~~~~~~~~~
-- [Link]
-- [Link]
-- [Link]
-- [Link]
-- [Link]

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

-- DISTINCT
-- it will not show the duplicate values

-- to show all the job description

SELECT job_desc
FROM employee
ORDER BY job_desc;

-- DISTINCT
-- it will not show the duplicate values
SELECT DISTINCT job_desc
FROM employee
ORDER BY job_desc;

-- function
-- block of code to perform specific task and it will run only when it is called.

-- syntax
-- function_name ()
-- function_name (argument1, argument2)

-- SQL, JavaScript, Python, CSS -- Function

-- Java -- Method
-- Microsoft Applications -- Sub Routine

Types of Functions in MySQL

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- Aggregate Functions
-- String Functions
-- Date Functions

-- Aggregate Functions
~~~~~~~~~~~~~~~~~~~~~~~
-- COUNT()
-- AVG()
-- SUM()
-- MAX()
-- MIN()

-----------------------------------------------------------------------------
-- COUNT()
-- Count of

-- count the total number of employees

SELECT COUNT(emp_name)
FROM employee;
-- count with alias (having space in between alias name)
SELECT COUNT(emp_name) AS "Total no of Employees"
FROM employee;

-- count with alias (not having space in between alias name)

SELECT COUNT(emp_name) AS total_no_of_employees
FROM employee;

-- count all job description without duplicates

SELECT COUNT(DISTINCT job_desc)
FROM employee;

-- count total number os rows in table

SELECT COUNT(*)
FROM employee;

SELECT COUNT(*) AS total

FROM employee;

-- count the total no of managers

SELECT COUNT(job_desc)
FROM employee
WHERE job_desc = "MANAGER";

-- count the total no of managers with alias

SELECT COUNT(job_desc) AS "Total no of Managers"
FROM employee
WHERE job_desc = "MANAGER";

-- write query to get total no of employees whose salary is greater than 30000
-- show column heading as EMP_SALARY_MORE_THAN_30000
SELECT COUNT(salary) AS EMP_SALARY_MORE_THAN_30000
FROM employee
WHERE salary > 30000;

-- Average
-- AVG()

-- average salary of all the employees

SELECT AVG(salary)
FROM employee;

-- average salary of all the managers

SELECT AVG(salary)
FROM employee
WHERE job_desc = "MANAGER";

-- Addition
-- SUM()

-- total salary of all the employees

SELECT SUM(salary)
FROM employee;

-- total salary of all the HR

SELECT SUM(salary)
FROM employee
WHERE job_desc = "HR";
-- Maximum
-- MAX()

-- maximum salary of all the employees

SELECT MAX(salary)
FROM employee;

-- maximum salary of ADMIN

SELECT MAX(salary)
FROM employee
WHERE job_desc = "ADMIN";

-- Minimum
-- MIN()

-- minimum salary of all the employees

SELECT MIN(salary)
FROM employee;

-- minimum salary of engineer

SELECT MIN(salary)
FROM employee
WHERE job_desc = "ENGINEER";

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

-- String Functions
~~~~~~~~~~~~~~~~~~~~
-- UCASE()
-- LCASE()
-- CHAR_LENGTH()
-- CONCAT()
-- FORMAT()
-- LEFT(string, n)
-- RIGHT(string, n)

-----------------------------------------------------------------------------------
----------------------------------
-- UCASE()
-- converts to uppercase

SELECT UCASE("sun shine");

-- convert all employee names to uppercase

SELECT UCASE(emp_name)
FROM employee;

-- convert all employee names to uppercase

SELECT UCASE(emp_name), job_desc
FROM employee
ORDER BY emp_name;

-- LCASE()
-- converts to lowercase

SELECT LCASE("SUN SHINE");

-- convert all job description to lowercase

SELECT LCASE(job_desc)
FROM employee;

-- convert all job description using LCASE with DISTINCT and ORDER BY
SELECT DISTINCT LCASE(job_desc)
FROM employee
ORDER BY LCASE(job_desc);

-- CHAR _LENGTH()
-- counts the length of characters

SELECT CHAR_LENGTH("sql");

-- count the length of characters in each employee name and show in separate column
SELECT emp_name, CHAR_LENGTH(emp_name)
FROM employee
ORDER BY emp_name;

-- Concatenation
-- joining / combining one or more characters
-- joining words or strings together
-- CONCAT()

SELECT CONCAT("sun", "shine");

SELECT CONCAT("sun", " ", "shine");

-- join "Rs." with salary column and show next to employee name
SELECT emp_name, CONCAT("Rs.", salary)
FROM employee
ORDER By emp_name;

-- with alias
SELECT emp_name, CONCAT("Rs.", salary) AS salary
FROM employee
ORDER By emp_name;

-- show only employee name and salary

-- join "Mr. / Mrs." before employee name and
-- join "Rs." before salary
SELECT CONCAT("Mr. / Mrs.", emp_name), CONCAT("Rs.", salary)
FROM employee
ORDER By emp_name;

-- with alias
SELECT CONCAT("Mr. / Mrs.", emp_name) AS name, CONCAT("Rs.", salary) AS salary
FROM employee
ORDER By emp_name;

-- FORMAT()

SELECT FORMAT(1000000000000000000000000, 2);

SELECT FORMAT(1000000000000000000000000, 0);

SELECT emp_name, FORMAT(salary, 2)

FROM employee;

SELECT emp_name, FORMAT(salary, 0)

FROM employee;
-- using CONCAT() and FORMAT() together with alias
SELECT emp_name AS name, CONCAT("Rs. ", FORMAT(salary, 0)) AS salary
FROM employee;

-- LEFT(string, n)
-- from the given string take no of characters from the left

SELECT LEFT("sunshine", 3);

-- take only 3 characters from left of job description

SELECT LEFT(job_desc, 3)
FROM employee;

-- using DISTINCT with LEFT

SELECT DISTINCT LEFT(job_desc, 3)
FROM employee;

-- RIGHT(string, n)
-- from the given string take no of characters from the right

SELECT RIGHT("sunshine", 5);

-- take only 2 characters from the right of job description

SELECT RIGHT(job_desc, 2)
FROM employee;

-- using DISTINCT with RIGHT

SELECT DISTINCT RIGHT(job_desc, 2)
FROM employee;

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

-- DATE Functions
~~~~~~~~~~~~~~~~~~~
-- NOW()
-- CURDATE()
-- DATE()
-- DATE_FORMAT
-- DATEDIFF()
-- DATE_ADD()

-- add new column "hire_date"

ALTER TABLE employee
ADD COLUMN hire_date DATE;

DESCRIBE employee;

SELECT *
FROM employee
ORDER BY emp_name;

-- update hire_date for all employees to 2025-01-01

UPDATE employee
SET hire_date = "2025-01-01";
SELECT *
FROM employee
ORDER BY emp_name;

-- update hire_date for all the managers to 2024-01-01

UPDATE employee
SET hire_date = "2024-01-01"
WHERE job_desc = "MANAGER";

SELECT *
FROM employee
WHERE job_desc = 'MANAGER'
ORDER BY emp_name;

-- Date Functions

-- NOW()
-- returns current system date and time

SELECT NOW();

-- CURDATE()
-- returns current system date

SELECT CURDATE();
SELECT CURDATE() AS date;

-- DATE(given_date)
~~~~~~~~~~~~~~~~~~~~~~~~

-- DATE(NOW())
-- returns current system date

SELECT DATE(NOW());
SELECT DATE(NOW()) AS date;

-- DATE_FORMAT(date, custom_format)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- changes date to custom date format

-- lower case "y" will return 2 digits of year

SELECT DATE_FORMAT(CURDATE(), "%d-%m-%y") AS date;

-- upper case "Y" will return 4 digits of year

SELECT DATE_FORMAT(CURDATE(), "%d-%m-%Y") AS date;

-- upper case "M" will return name of the month

SELECT DATE_FORMAT(CURDATE(), "%d-%M-%Y") AS date;

-- lower case "b" will return three characters of the month

SELECT DATE_FORMAT(CURDATE(), "%d-%b-%Y") AS date;

-- change the date separator to colon :

SELECT DATE_FORMAT(CURDATE(), "%d:%b:%Y") AS date;

-- difference between 2 dates

-- DATEDIFF(date1, date2)
SELECT DATEDIFF(CURDATE(), "2025-12-31") AS days;
SELECT DATEDIFF("2025-12-31", CURDATE()) AS days;
SELECT DATEDIFF("2025-12-31", "2025-04-14") AS days_left_in_this_year;

-- adding to the date

- DATE_ADD(date, INTERVAL 1 DAY / WEEK / MONTH / YEAR)

-- add one day to the current date

SELECT DATE_ADD(CURDATE(), INTERVAL 1 DAY) AS "after 1 day";

-- add 60 days to the current date

SELECT DATE_ADD(CURDATE(), INTERVAL 60 DAY) AS "after 60 days";

-- add one week to the given date

SELECT DATE_ADD("2005-04-15", INTERVAL 1 WEEK) AS "after 1 week";

-- add one month to the given date

SELECT DATE_ADD("2003-01-26", INTERVAL 1 MONTH) AS "after 1 month";

-- add one year to the given date

SELECT DATE_ADD("2001-12-15", INTERVAL 1 YEAR) AS "after 1 year";

-- GROUP BY

SELECT job_desc
FROM employee
GROUP BY job_desc;

-- count the no of employees in each department

-- aggregate function with GROUP BY
SELECT job_desc, COUNT(emp_name)
FROM employee
GROUP BY job_desc;

-- HAVING

-- WHERE will filter the rows

-- HAVING will filter the group

-- WHERE with GROUP BY

SELECT job_desc, COUNT(emp_name)
FROM employee
WHERE job_desc = "MANAGER"
GROUP BY job_desc;

-- HAVING with GROUP BY

SELECT job_desc, COUNT(emp_name)
FROM employee
GROUP BY job_desc
HAVING COUNT(emp_name) > 4;

-- WHERE & HAVING with GROUP BY

SELECT job_desc, COUNT(emp_name)
FROM employee
WHERE salary > 50000
GROUP BY job_desc
HAVING COUNT(emp_name) > 2;
-----------------------------------------------------------------------------------
--------------------------------------------------------------------------
-- INDEX

-- show all the indexes in the table employee

SHOW INDEX
FROM employee;

-- create index called as "octopus" on the column emp_name

CREATE INDEX octopus
ON employee(emp_name);

SHOW INDEX
FROM employee;

-- delete index using name "octopus"

ALTER TABLE employee
DROP INDEX octopus;

SHOW INDEX
FROM employee;

-- add index without giving custom name

ALTER TABLE employee
ADD INDEX(emp_name);

SHOW INDEX
FROM employee;

-- delete index using the column name given by mysql server

ALTER TABLE employee
DROP INDEX emp_name;

SHOW INDEX
FROM employee;

-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
-
-- ON DELETE

-- ON DELETE SET NULL

-- ON DELETE CASCADE

-----------------------------------------------------------------------------------
------------------------------------------------
-- ON DELETE SET NULL
~~~~~~~~~~~~~~~~~~~~~~~~

DROP TABLE employee;

SHOW TABLES;

CREATE TABLE employee (

id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(50) NOT NULL,
job_desc VARCHAR(50) DEFAULT "NO DEPT",
salary INT,
CONSTRAINT pig CHECK(salary > 10000),
aadhaar BIGINT UNIQUE,
hire_date DATE,
br_id INT,
CONSTRAINT buffalo FOREIGN KEY(br_id) REFERENCES branch(branch_id) ON DELETE SET
NULL
);

DESCRIBE employee;

-- insert data into table employee

INSERT INTO employee
(emp_name, job_desc, salary, br_id)
VALUES
("kiran r. s.", "ADMIN", 15000, 101),
("siddaram", "MANAGER", 20000, 108),
("naveed n.", "CEO", 25000, 116),
("sachin b. m.", "HR", 30000, 116),
("nishan s. m.", "SALES", 35000, 102),
("srinath r.", "ENGINEER", 40000, 101),
("aparna", "ENGINEER", 45000, 110),
("rakshitha", "HR", 50000, 116),
("rahul choudhary", "MANAGER", 55000, 101),
("kaushik", "SALES", 60000, 117),
("srinivas c.", "ADMIN", 65000, 101),
("srivathsa", "ENGINEER", 70000, 101),
("pavan sai", "MANAGER", 75000, 101),
("tanush", "SALES", 80000, 116),
("debneet", "HR", 85000, 101),
("swami b. c.", "ENGINEER", 90000, 106),
("narendra", "MANAGER", 95000, 106),
("chandan", "ENGINEER", 100000, 103),
("vinayak", "ADMIN", 105000, 112),
("adithya", "MANAGER", 110000, 104),
("ajay", "HR", 115000, 103),
("dayasagar", "ENGINEER", 120000, 106),
("nuthan gowda", "SALES", 125000, 114),
("rakshath", "CTO", 130000, 102),
("narasimhulu", "ADMIN", 135000, 101);

SELECT *
FROM employee
ORDER BY emp_name, job_desc;

DELETE
FROM branch
WHERE branch_id = 101;

SELECT *
FROM employee
ORDER BY emp_name, job_desc;

-----------------------------------------------------------------------------------
---------------------------------------------------------------------------
-- ON DELETE CASCADE
~~~~~~~~~~~~~~~~~~~~~

DROP TABLE employee;

CREATE TABLE employee (
id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(50) NOT NULL,
job_desc VARCHAR(50) DEFAULT "NO DEPT",
salary INT,
CONSTRAINT pig CHECK(salary > 10000),
aadhaar BIGINT UNIQUE,
hire_date DATE,
br_id INT,
CONSTRAINT buffalo FOREIGN KEY(br_id) REFERENCES branch(branch_id) ON DELETE
CASCADE
);

DESCRIBE employee;

INSERT INTO branch

(branch_id, branch_name)
VALUES
(101, "bangalore");

-- insert data into table employee

INSERT INTO employee
(emp_name, job_desc, salary, br_id)
VALUES
("kiran r. s.", "ADMIN", 15000, 101),
("siddaram", "MANAGER", 20000, 108),
("naveed n.", "CEO", 25000, 116),
("sachin b. m.", "HR", 30000, 116),
("nishan s. m.", "SALES", 35000, 102),
("srinath r.", "ENGINEER", 40000, 101),
("aparna", "ENGINEER", 45000, 110),
("rakshitha", "HR", 50000, 116),
("rahul choudhary", "MANAGER", 55000, 101),
("kaushik", "SALES", 60000, 117),
("srinivas c.", "ADMIN", 65000, 101),
("srivathsa", "ENGINEER", 70000, 101),
("pavan sai", "MANAGER", 75000, 101),
("tanush", "SALES", 80000, 116),
("debneet", "HR", 85000, 101),
("swami b. c.", "ENGINEER", 90000, 106),
("narendra", "MANAGER", 95000, 106),
("chandan", "ENGINEER", 100000, 103),
("vinayak", "ADMIN", 105000, 112),
("adithya", "MANAGER", 110000, 104),
("ajay", "HR", 115000, 103),
("dayasagar", "ENGINEER", 120000, 106),
("nuthan gowda", "SALES", 125000, 114),
("rakshath", "CTO", 130000, 102),
("narasimhulu", "ADMIN", 135000, 101);

DELETE
FROM branch
WHERE branch_id = 101;

SELECT *
FROM employee
ORDER BY emp_name, job_desc;

-----------------------------------------------------------------------------------
-------------------------------------------------------------
-- insert data of few employees without br_id
INSERT INTO employee
(emp_name, job_desc, salary)
VALUES
("MAHALAKSHMI", "TESTER", 40000),
("DURAIMURUGAN", "DATA ANALYST", 100000),
("POOVARASAN", "DEVELOPER", 200000);

-- JOIN
-- Join is used to combine rows from one or more tables
-- Rows are combined based on related columns between tables
-- Joins combine rows horizontally.

-- Types of Joins
~~~~~~~~~~~~~~~~~~~~
-- INNER JOIN / LEFT INNER JOIN
-- LEFT JOIN / LEFT OUTER JOIN
-- RIGHT JOIN / RIGHT OUTER JOIN
-- FULL OUTER JOIN
-- CROSS JOIN

-----------------------------------------------------------------------------------
--------------------------------------------
-- INNER JOIN / LEFT INNER JOIN
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- left table -- employee
-- right table -- branch

SELECT *
FROM employee
INNER JOIN branch
ON employee.br_id = branch.branch_id;

-----------------------------------------------------------------------------------
---------------------------------------
-- LEFT JOIN / LEFT OUTER JOIN
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- left table -- employee
-- right table -- branch

SELECT *
FROM employee
LEFT OUTER JOIN branch
ON employee.br_id = branch.branch_id;

-----------------------------------------------------------------------------------
---------------------------------------
-- RIGHT JOIN / RIGHT OUTER JOIN
-- left table -- employee
-- right table -- branch

SELECT *
FROM employee
RIGHT OUTER JOIN branch
ON employee.br_id = branch.branch_id;

-----------------------------------------------------------------------------------
---------------------------------
-- FULL OUTER JOIN
-- not supported in mysql server

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

-- CROSS JOIN
-- left table -- employee
-- right table -- branch

SELECT *
FROM employee
CROSS JOIN branch;

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

-- UNION
~~~~~~~~~~~~~~~~
-- rows are combined vertically

-- create a new table client

CREATE TABLE client (
client_id INT PRIMARY KEY AUTO_INCREMENT,
client_city VARCHAR(50)
);

DESCRIBE client;

-- inserting values in table client

INSERT INTO client
(client_id, client_city)
VALUES
(102, "mysore"),
(103, "hubli"),
(104, "mangalore"),
(105, "hassan"),
(106, "davangere"),
(107, "bellary"),
(108, "bidar"),
(109, "dharwad"),
(110, "kolar"),
(101, "bangalore"),
(130, "raichur"),
(115, "tumkur"),
(120, "mandya"),
(125, "shimoga");

SELECT *
FROM client
ORDER BY client_id;

SELECT *
FROM branch
ORDER BY branch_id;

-- UNION
-- return unique values
SELECT *
FROM client
UNION
SELECT *
FROM branch;

-- UNION ALL
-- return duplicate rows also
SELECT *
FROM client
UNION ALL
SELECT *
FROM branch;

-- sub query
-- nested queries
-- query within a query
-- query inside query
-- outer query ( inner query )
-- use the output of inner query as input for outer query

-- PROBLEM 1 :
~~~~~~~~~~~~~~~~~
-- return employee name whose salary is more than minimum salary

-- return minimum salary of employee

SELECT MIN(salary)
FROM employee;

-- return employee name whose salary is more than 20000 (minimum salary)
SELECT emp_name
FROM employee
WHERE salary > 20000;

-- return employee name whose salary is more than minimum salary

SELECT emp_name
FROM employee
WHERE salary > (
SELECT MIN(salary)
FROM employee
);

-- TASK 1

-----------------------------------------------------------------------------------
------------------------------
-- create table subject
-- PRIMARY KEY -- subject_id
+----------------------+------------------+-------+------+------------
+-------------------------------------+
| Field | Type | Null | Key | Default | Extra
|
+----------------------+-------------------+--------+------+-----------
+------------------------------------+
| subject_id | int | NO | PRI | NULL |
auto_increment |
| subject_name | varchar(50) | YES | UNI| NULL |
|
+----------------------+-------------------+--------+-----+------------
+------------------------------------+
-- SOLUTION:
CREATE TABLE subject(
subject_id INT PRIMARY KEY AUTO_INCREMENT,
subject_name VARCHAR(50) UNIQUE
);
DESCRIBE subject;

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

-- TASK 2
-- create table customer
-- PRIMARY KEY -- customer_id
+-------------------------+------------------+-------+-------+---------
+-----------------------------+
| Field | Type | Null | Key | Default | Extra
|
+-------------------------+-------------------+-------+-------+----------
+----------------------------+
| customer_id | int | NO | PRI | NULL |
auto_increment |
| customer_name | varchar(50) | YES | | NULL |
|
| customer_city | varchar(50) | YES | | NULL |
|
+--------------------------+-------------------+-------+-------+----------
+----------------------------+
-- SOLUTION:
CREATE TABLE customer (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
customer_name VARCHAR(50),
customer_city VARCHAR(50)
);
DESCRIBE customer;
-----------------------------------------------------------------------------------
-----------------------------------------------

-- TASK 3
-- create table institute
-- PRIMARY KEY -- institue_id
-- FOREIGN KEY -- customer_id (customer table)
-- FOREIGN KEY -- subject_id (subject table)
+-----------------------+----------------+------+-------+---------
+------------------------+
| Field | Type | Null | Key | Default | Extra
|
+-----------------------+----------------+------+-------+---------
+------------------------+
| institute_id | int | NO | PRI | NULL | auto_increment
|
| customer_id | int | YES | MUL | NULL |
|
| subject_id | int | YES | MUL | NULL |
|
+------------------------+---------------+-------+-------+----------
+-----------------------+
-- SOLUTION 1:
CREATE TABLE institute (
institute_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT,
subject_id INT,
CONSTRAINT cat FOREIGN KEY(customer_id) REFERENCES customer(customer_id),
CONSTRAINT dog FOREIGN KEY(subject_id) REFERENCES subject(subject_id)
);
DESCRIBE institute;

-- SOLUTION 2:
CREATE TABLE institute (
institute_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT,
FOREIGN KEY(customer_id) REFERENCES customer(customer_id),
subject_id INT,
FOREIGN KEY(subject_id) REFERENCES subject(subject_id)
);
DESCRIBE institute;

-----------------------------------------------------------------------------------
-----------------------------------------------------
-- EXISTS
~~~~~~~~~~~~~~
-- used with sub-query

PROBLEM:
~~~~~~~~~~~~
-- find the details of branches containing atleast one manager

-- return details of employees who are managers

SELECT *
FROM employee
WHERE job_desc = "MANAGER";

-- return the details of branches

SELECT branch_id, branch_name
FROM branch;

SELECT *
FROM employee, branch
WHERE job_desc = "MANAGER"
AND
employee.br_id = branch.branch_id;

ANSWER:
~~~~~~~~~~~~~~~~~
SELECT branch_id, branch_name
FROM branch
WHERE EXISTS (
SELECT *
FROM employee
WHERE job_desc = "MANAGER"
AND
employee.br_id = branch.branch_id
);

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

-- ANY
~~~~~~~~~~~~

-- PROBLEM:
~~~~~~~~~~~~~~
-- write a query to find info of branches in which any employee gets salary more
than 50000

-- return details of employees who gets salary more than 50000

SELECT *
FROM employee
WHERE salary > 50000;

-- return branch id of employees who gets salary more than 50000

SELECT br_id
FROM employee
WHERE salary > 50000;

-- return details of branches

SELECT *
FROM branch;

ANSWER:
~~~~~~~~~~~~~~~~~~~
SELECT *
FROM branch
WHERE branch_id = ANY (
SELECT br_id
FROM employee
WHERE salary > 50000
);

-----------------------------------------------------------------------------------
------------------------------
-- ALL
~~~~~~~~~~~~~

-- PROBLEM:
~~~~~~~~~~~~~~~~~~
-- write a query to find details of employees who are not working in mysore and
davangere

-- return details of employees

SELECT *
FROM employee;

-- return branch id of mysore and davangere

SELECT branch_id
FROM branch
WHERE branch_name IN ("mysore", "davangere");

-- return branch id of other cities except mysore and davangere

SELECT branch_id
FROM branch
WHERE branch_name NOT IN ("mysore", "davangere");

ANSWER 1:
~~~~~~~~~~~~~~~~
SELECT *
FROM employee
WHERE br_id <> ALL (
SELECT branch_id
FROM branch
WHERE branch_name IN ("mysore", "davangere")
);

ANSWER 2:
~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT *
FROM employee
WHERE br_id = ANY (
SELECT branch_id
FROM branch
WHERE branch_name NOT IN ("mysore", "davangere")
);

ANSWER 3:
~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT *
FROM employee
WHERE EXISTS (
SELECT branch_id
FROM branch
WHERE branch_name NOT IN ("mysore", "davangere")
AND
employee.br_id = branch.branch_id
);

ANSWER 4:
~~~~~~~~~~~~~~~~
SELECT *
FROM employee
WHERE br_id IN (
SELECT branch_id
FROM branch
WHERE branch_name NOT IN ("mysore", "davangere")
);

--------------------------------------------------------------------------------
PROBLEM:
~~~~~~~~~~~~~~~~~
-- write a query to find branch info of employees whose job description is sales
SELECT *
FROM branch
WHERE branch_id = ANY (
SELECT br_id
FROM employee
WHERE job_desc = 'sales'
);

ANSWER 2:
~~~~~~~~~~~~~~~~~~~~~~~~~~
FROM branch
WHERE branch_id IN (
SELECT br_id
FROM employee
WHERE job_desc = 'sales'
);
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
----

VIEW
~~~~~~
-- Cretae View FISH to return left outer join of employee and branch

Create View fish as SELECT *

FROM employee
LEFT OUTER JOIN branch
ON employee.br_id = branch.branch_id;

SELECT *
FROM fish;

-- Only emp_name, branch_name from view fish

Select emp_name, branch_name
FROM fish;

DROP view fish;

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

Stored Procedure
~~~~~~~~~~~~~~~
A stored procedure is a prepared SQL code that you can save, so the code can be
reused over and over again.

-- Semi Colon (;) is referred as Delimiters.

-- Change the Delimiters to some other symbol

Delimiter $$

CREATE PROCEDURE donkey()

BEGIN
Select * from Employee;
END $$

-- Change the Delimiters to semi colon

Delimiter ;

-- Call the PROCEDURE

call donkey();

-- Delete the PROCEDURE

DROP PROCEDURE donkey;
DROP PROCEDURE IF EXISTS donkey;

-- Advantages of Stored Procedure

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- It reduces network traffic.
-- Centralized Business logic
-- It is secure.

-- Disadvantages of Stored Procedure

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- It uses more resources.
-- It is hard to maintain.
-- Difficult to troubleshoot.