MYSQL Course Version: 8.

x
We can test SQL commands here Click Here- W3
Section Topics Details Comments
We can call it a collection of data
A method for accessing and manipulating that data

What are the difference between Database, DBMS, RDBMS etc

app -> DBMS -> DB

basically MySql OrcaleDB PostgreSQL etc are DBMS to interact with
DB
What is a Database?

SQL is the language to talk to our database

MySQL is a DBMS which use SQL to talk with database

Regarding the different types of DB like Oracle PostgresSQL etc

They all use SQL only.. only slight difference in syntax
They are unique in terms of functionalites the provide DBMS (file system) - Excel
MySQL vs SQL RDBMS (in table form) - MySql
Installation on Windows https://dev.mysql.com/downloads/installer/
Go to Google -> mysql download -> MySQL Community server
Download the x86 one

Double click on package

In settings you will need to allow the app
Set root password -> rootroot

Open terminal and need to set the path of sql which is

/usr/local/mysql/bin
~/.bash_profile
export PATH=${PATH}:/usr/local/mysql/bin
source ~/.bash_profile

First time access will be

mysql -u root -p or you can make an alias

Installation on MAC Although we can install WorkBench to use the GUI Version alias mysql="/usr/local/mysql/bin/mysql"
If somehow Workbench is not working, you can use the 3rd party
Intro and Installation DBGate Installation tool like this Replacement of Workbench
We can take example of
SHOW DATABASES; Employee, Students
Show databases A mysql is a DB Server which may have multiple DataBases With graphical example
For db name use
underscore or camel case
Creating Database CREATE DATABASE <db_name> student_db or AppStore
Dropping Database DROP DATABASE <db_name>
USE <db_name> to select the DB when you do show databases
How to Use Database? SELECT database(); to check which DB are you using it will show already existing databases.
SHOW TABLES;

What are tables? Collection of related data in a structured format We store data in tables.
At the time of defining the structure of our table i.e. columns name,
we do need to define the type of data
Example: Age should be a no., name should be String

Price should be decimal

Because if we need to make any calculation on price then it's not
possible using string

Most widely used are

Numeric - INT DOUBLE FLOAT DECIMAL
String - VARCHAR
Date
We can refer the link
What are datatypes We can define the limit of VARCHAR(100) https://dev.mysql.com/doc/refman/8.0/en/data-types.html
CREAT TABLE students
(
name VARCHAR(100),
age INT
);

SHOW TABLES; to check your tables (It will show tables under
current DB, first you need to do USE <db>

SHOW COLUMNS FROM students; (to check all the columns

and its data type ) How can we verify if the table is created?
or
Creating Tables DESC students; (same thing) From Workbench, of DbGate we can refresh and see
Dropping Tables DROP TABLE <table_name> To delete the table with all data.
-- Your comment

Comments For bulk comments, select the lines and Ctrl+/

Creating Databases & Tables
INSERT INTO students(id, name)
VALUES (101, "Rahul");

You can directly use like this

INSERT INTO students To insert the data into the table
VALUES (101, "Rahul") We can change the sequence of fields like
INSERT Basics we can give 'name' first and then id
INSERT INTO students(id, name)
INSERT Multiple Data VALUES (101, "Rahul"), (102, "Raju"), (103, "Sham");
To see the data of the data

SELECT * from <table_name>

SELECT id from students
SELECT SELECT id, name from students

CREATE TABLE PERSON

(name VARCHAR(50) NOT NULL,
aget INT NOT NULL If you set NOT NULL to a column, You can't leave the value empty
Working with NOT NULL ); while inserting the data
 Always try to use single quotes

Working with quotes In case ' is part of string then use \ In some others type of SQL, double quotes are not allowed

CREATE TABLE PERSON

(name VARCHAR(50) DEFAULT 'unnamed',
aget INT NOT NULL In case we don't provide the value of a column, it will set
);
Adding DEFAULT values We can use NOT NULL also with DEFAULT

CREATE TABLE PERSON

( id INT NOT NULL PRIMARY KEY,
name VARCHAR(50) DEFAULT 'unnamed',
aget INT NOT NULL
);

OR

CREATE TABLE PERSON Unique ID to differentiate and identify the object

( id INT NOT NULL,
name VARCHAR(50) DEFAULT 'unnamed', The PRIMARY KEY constraint uniquely identifies each
aget INT NOT NULL, record in a table.
PRIMARY KEY (id) Primary keys must contain UNIQUE values, and cannot
); contain NULL values.
Primary Keys A table can have only ONE primary key.
It will increment the value of id by 1 for every row
Auto increment id INT AUTO_INCREMENT Therefore while inserting data, you don't need to provide it's value.
CREATE TABLE employees (
Create table for employee id INT AUTO_INCREMENT,
first_name VARCHAR(255) NOT NULL,
id auto increment and primary key last_name VARCHAR(255) NOT NULL,
first name string mandatory age INT NOT NULL,
last name string mandatory current_status VARCHAR(255) NOT NULL DEFAULT 'employed',
age number mandatory PRIMARY KEY(id)
Inserting Data Activity status text default employeed );
Create
Read
Update
Introduction Delete We are talking about CRUD operations for a row
SELECT query
To read the data or content of a table
SELECT <column_name> FROM <table_name> Either we can read a single column
READ SELECT * FROM <table_name> or * for all the column
It is used to to read a row with a specific condition
SELECT * FROM students WHERE id=4; It's case insensitive
SELECT name FROM students WHERE college="Global" lower or upper case will not matter
WHERE Clause WHERE age=id; We can compare two columns as well
Aliases SELECT name AS StudentNames from students Renaming a column in the result only to make it more readable
UPDATE students
SET contact=12345
WHERE name='Raju';
UPDATE We use update query to alter the row or data in SQl
DELETE from students
WHERE name='Raju';
Used to delete a row or data of specific person
DELETE DELETE from students; Second command will delete all the rows;

CREATE TABLE employees (

emp_id INT NOT NULL AUTO_INCREMENT,
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
desig VARCHAR(30) NOT NULL DEFAULT 'Probation',
dept VARCHAR(30) NOT NULL,
salary INT NOT NULL DEFAULT 25000,
PRIMARY KEY (emp_id)
);

Create a database - bank_db

Create a table - employees INSERT INTO employees (emp_id, fname, lname, desig, dept,
salary) VALUES
emp_id (101, 'Raju', 'Rastogi', 'Manager', 'Loan', 37000),
name (102, 'Sham', 'Mohan', 'Cashier', 'Cash', 32000),
desig (103, 'Baburao', 'Apte', 'Associate', 'Loan', 25000),
dept (104, 'Paul', 'Philip', 'Accountant', 'Account', 45000),
salary (105, 'Alex', 'Watt', 'Associate', 'Deposit', 35000),
(106, 'Rick', 'Watt', 'Manager', 'Account', 65000),
emp_id column should not allow duplicate and null values (107, 'Leena', 'Jhonson', 'Lead', 'Cash', 25000),
(108, 'John', 'Paul', 'Manager', 'IT', 75000),
name column should not contain null value (109, 'Alex', 'Watt', 'Probation', 'Loan', 40000);
CRUD Basics CRUD Exercises desig column should have default value as 'Probation'
CONCAT(first_col, sec_col)

Select CONCAT(fname, 'ABC') From employees;

Select CONCAT(fname, lname) From employees;
Select CONCAT(fname, ' ', lname) From employees; To concate the two strings
Select CONCAT(fname, lname) as FullName From employees; Concate strings with value of a table
CONCAT Concate two columns
CONCAT_WS('-', fname, lname)

CONCAT_WS value will be separated by - WS is With Separator

SELECT SUBSTRING('Hello World', 1, 4);

SELECT SUBSTRING('Hello World', 7);

SELECT SUBSTRING('Hello World', -3); From starting postiion to end position

SELECT SUBSTRING(title, 1, 10) AS 'short title' FROM books; If only one no. is provided then from 7th to end

SUBSTRINGS SELECT SUBSTR(title, 1, 10) AS 'short title' FROM books; SUBSTR will also work

SELECT CONCAT
(
SUBSTRING(title, 1, 10),
'...'
) AS 'short title'
FROM books;
COMBINING STRING FUNC We can use function inside function as per the requirement
 This can be done in any editor like
Workbench (there is paint brush option)

DBGate (select code, right click and formatting)

or even VSCode (we need sql fomatting plugin) Proper fomatting is very important for readability
Formatting of SQL Command and understanding
REPLACE(str, from_str, to_str)
REPLACE('Hello', 'H', 'ABC')
This can be useful if we need to change the white space with any
REPLACE SELECT REPLACE(fname, ' ', '-') FROM Students; other character

Select reverse(fname), reverse(lname) from students;

SELECT REVERSE('Hello World');
REVERSE To reverse the characters of a string

Select char_length('Hello World');

Select char_length(fname) from students;
CHAR_LENGTH Gives no. of character
SELECT UPPER('Hello World');

SELECT LOWER('Hello World');

UPPER & LOWER SELECT UPPER(title) FROM books; To change the string in either upper or lower case
SELECT INSERT('Hello Bobby', 6, 0, 'There');

SELECT LEFT('omghahalol!', 3); - leftmost 3 char

SELECT RIGHT('omghahalol!', 4); - rightmost 4 char

SELECT REPEAT('ha', 4); - will repeat ha 4 times

STRING FUNCTIONS Other Functions SELECT TRIM(' pickle '); - will trim the white space
Select DISTINCT dept from employees;
For Query only Unique Data
SELECT DISTINCT CONCAT(fname,' ', lname) FROM employees;
Can be used with combination of columns too like
DISTINCT SELECT DISTINCT fname, lname FROM employees; full name should be same
sorting the result based on the given column
like sort with firstname or year
In case NULL is there then it will appear first

Select * from employees ORDER BY fname; Ascending by default

Select * from employees ORDER BY fname DESC; We can explicityly mention DESC for descending

We can also give Column no but it is not readable If we give more than one column then firstly First column will be
ORDER BY Select * from employees Order By 2 sorted then under that data second column will be sorte
Limit the no. of data of result
Select * from employees LIMIT 5;
If two no. are given then starts with 3 and 5 columns
LIMIT Select * from employees LIMIT 3,5; Note: data index starts with 0
when you are looking for someting like

starts with, end with, having..

'%abc%' is any no. of character in start or end and having abc

'_bc' _ is any single character and then end with bc

---- means exactly 4 char

LIKE select * from employees LIKE '%abc%' If we need to search % or _ in records then use \% similarly \_
SELECT COUNT(*) FROM employees;

SELECT COUNT(fname) FROM employees;

COUNT SELECT COUNT(DISTINCT dept) FROM employees; To count the no. of record based on given condition
It will first group all the employees based on department

then we find no. of employees in each department.

Other useful cases like:

Total salary paid in each department.
Max or Min salary in each department
GROUP BY select dept, count(emp_id) from employees group by dept;
MIN MAX select MIN(salary) from employees; To Find Min or Max values

Here is an example, first combining fname and lname of author

then groupoing all books based on as author
SELECT CONCAT(author_fname, ' ', author_lname) AS author, count no. of books
COUNT(*)
FROM books Result: Number of books written by each author.
Grouping by Multiple Col GROUP BY author;
select section, max(class) from students group by section;

SELECT author_lname, MAX(released_year), MIN(released_year)

FROM books GROUP BY author_lname;

SELECT
author_lname,
COUNT(*) as books_written,
MAX(released_year) AS latest_release,
MIN(released_year) AS earliest_release, In this example
MAX(pages) AS longest_page_count
FROM books GROUP BY author_lname; We first grouped the students based on section
MIN MAX with GROUP By Then min or max (biggest class in a section)
SELECT SUM(salary) FROM employees;

SELECT dept, COUNT(*), SUM(salary)

FROM employees
GROUP BY dept;
SUM
 SELECT AVG(salary) FROM employees;

SELECT
dept,
AVG(salary),
COUNT(*) FROM employees
AVG GROUP BY dept;
1: Find Different type of departments in database?

2: Display records with High-low salary

3: How to see only top 3 records from a table?

4: Show records where first name start with letter 'A'

AGGREGATION FUNCTION Exercises 5: Show records where length of the lname is 4 characters
VARCHAR is better
If we use CHAR(5), then even if we store a value of 3 char, sql will
add 3 whitespace to fill that space and store it, space optimization
vise it is useless

But we can use for fixed lenght values like country code IN, US, etc Reference docs
CHAR vs VARCHAR it will be faster https://dev.mysql.com/doc/refman/8.0/en/data-types.html
INT, TINYINT, BIGINT
DECIMAL(5,2) - (total no. of digit, upto decimal) like 999.99
upto 3 digits are allowed and two decimals
99.99 is right
DECIMAL 9999.99 is not
FLOAT DOUBLE Based on the decimal precision
YYYY-MM-DD
HH:MM:SS
DATE and TIME INSERT INTO people (name, birthdate, birthtime, birthdt)
DATETIME YYYY-MM-DD HH:MM:SS VALUES ('Elton', '2000-12-25', '11:00:00', '2000-12-25 11:00:00');
Built-in functions to get the time
CURDATE(), CURTIME(), NOW()

Example, if you want to insert today's date in table

we can use CURDATE() NSERT INTO people (name, birthdate, birthtime, birthdt)
CURDATE, CURTIME, NOW VALUES ('Hazel', CURDATE(), CURTIME(), NOW());

SELECT
birthdate,
DAY(birthdate),
DAYOFWEEK(birthdate),
DAYOFYEAR(birthdate)
FROM people;

SELECT
birthdate,
MONTHNAME(birthdate),
By using some functions we can get more info like YEAR(birthdate)
FROM people;
Date Function From date, if you need only Month or Year or Day

SELECT
birthtime,
HOUR(birthtime),
MINUTE(birthtime)
FROM people;

SELECT
birthdt,
MONTH(birthdt),
DAY(birthdt),
HOUR(birthdt),
MINUTE(birthdt)
FROM people;
Time Function For getting only Hour or Min

we can define the format of the date and time to be shown in the
result of the query SELECT birthdate, DATE_FORMAT(birthdate, '%a %b %D') FROM
DATE_FORMAT(birthdate, '%a %b %D') people;
DATE_FORMAT(dob, '%m/%d/%y') Result: Tue Mar 27th
DATE_FORMAT(now(), '%D %a at %T') Result: 21st Tue at 22: SELECT birthdt, DATE_FORMAT(birthdt, '%H:%i') FROM people;
28:48
SELECT birthdt, DATE_FORMAT(birthdt, 'BORN ON: %r') FROM
Formatting date people;
DATEDIFF(date1, date2) : 2 -1
We can use once after birthday when will person become 18 years
DATE_ADD(date, INTERVAL expr)
Date Math DATE_SUB(date, INTERVAL expr) DATE_ADD(2020-12-12, INTERVAL 18 YEARS)
Can also be used to store date or time or datetime format
TIMESTAMP takes little storage as compared to DATE command Can be used as data type
CREATE TABLE captions2 (
text VARCHAR(150),
created_at TIMESTAMP default CURRENT_TIMESTAMP, ON UPDATE - whenever we update the row, updated_at column will
ON UPDATE updated_at TIMESTAMP ON UPDATE CURRENT_TIMESTAMP be updated with latest timestamp
Datatypes CURRENT_TIMESTAMP ); useful when we need to keep the record when was data updated
SELECT * FROM books
!= WHERE released_year != 2017;

SELECT * FROM books

NOT LIKE WHERE title NOT LIKE '%e%'; It is just opposite to LIKE
SELECT * FROM books
Greater than WHERE pages > 500;
SELECT * FROM books
Less than or = WHERE released_year <= 1985;
SELECT title, author_lname, released_year FROM books
WHERE released_year > 2010
AND author_lname = 'Eggers'
Logical AND AND title LIKE '%novel%'; Only true when all the conditions are true
SELECT title, author_lname, released_year FROM books
WHERE author_lname='Eggers' OR
Logical OR released_year > 2010; True if any condition is true
SELECT title, released_year FROM books
BETWEEN WHERE released_year BETWEEN 2004 AND 2014; Useful when we need to find the range
 SELECT * FROM people WHERE birthtime
BETWEEN CAST('12:00:00' AS TIME)
AND CAST('16:00:00' AS TIME);
We used CAST so that DB can differentiate that it is comparing
dates or time
SELECT * FROM people WHERE HOUR(birthtime)
Comparing Dates BETWEEN 12 AND 16; although even without using CAST it will work

SELECT title, author_lname FROM books

WHERE author_lname IN ('Carver', 'Lahiri', 'Smith');

SELECT title, author_lname FROM books

WHERE author_lname NOT IN ('Carver', 'Lahiri', 'Smith'); When we need to compare in range of values
IN either in Carver or Lahiri or Smith

SELECT title, released_year,

CASE
WHEN released_year >= 2000 THEN 'modern lit'
ELSE '20th century lit'
END AS genre
FROM books;
CASE we can write multiple when also for multiple conditions
Comparison & Logical Operator IS NULL WHERE fname IS NULL;
Constraints examples are
NOT NULL
UNIQUE
Intro PRIMARY KEY
CREATE TABLE contacts (
name VARCHAR(100) NOT NULL,
phone VARCHAR(15) NOT NULL UNIQUE
UNIQUE ); To not allow any duplicates entries
CREATE TABLE users (
username VARCHAR(20) NOT NULL,
age INT CHECK (age > 0) Check during inserting the data
); helpful to not allow non-practical values like age is 0
CHECK
CREATE TABLE users2 (
username VARCHAR(20) NOT NULL,
age INT,
CONSTRAINT age_not_negative CHECK (age >= 0) We can provide meaningful name, statement for user to understand
NAMED CONSTRAINT ); in case if the check is violated

CREATE TABLE houses (

purchase_price INT NOT NULL,
sale_price INT NOT NULL,
CONSTRAINT sprice_gt_pprice CHECK(sale_price >=
purchase_price)
Multiple Column Constraint );

To add a column To add a new column

ALTER TABLE companies
ADD COLUMN employee_count INT NOT NULL DEFAULT 1; If you don't add DEFAULT value then it will be 0 for INT
ALTER If you dont mention anything then all the values will be null

To drop a column
ALTER TABLE companies DROP COLUMN phone;
To DROP A column

RENAME TABLE students TO std;

ALTER TABLE suppliers RENAME TO companies;

We can rename the Table name also

ALTER TABLE companies
RENAME RENAME COLUMN name TO company_name; We can also rename a column name

Here modifying means,, chaning datatype of a column

ALTER TABLE companies

MODIFY company_name VARCHAR(100) DEFAULT 'unknown';
For example if we need to add default values, or unique etc or
constraints
ALTER TABLE suppliers
Modifying Column CHANGE business biz_name VARCHAR(50); we can also change column name
ALTER TABLE houses
ADD CONSTRAINT positive_pprice CHECK (purchase_price >= 0);

Add or Drop Constraint ALTER TABLE houses DROP CONSTRAINT positive_pprice;

SELECT
CONSTRAINT_TYPE,CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
Constraint & ALTER Table How to check existing constraint WHERE TABLE_NAME=’contacts’;

One to One One book can have multiple reviews

One to Many Authon-Books (A book can have multiple authors, a author can have
Types of Relation Many to Many multiple books)

A one-to-one relationship in SQL can be defined with foreign keys Example: Employees table with Contact detail table
where each record in one table corresponds to one and only one one employee will only having one contact detail.
One to One record in another table.

Customers - orders

Bad Way
Making a single table -
When a customer buy something multiple times, there will be
duplicate entries of customer and messy data

Good Way
Separate tables for customer and orders with unique ids A many-to-many relationship in SQL is defined using a junction table
We can use customer_id in orders table like Foreign key that holds foreign keys referencing the primary keys of the related
One To Many tables.
 CREATE TABLE customers (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50));

CREATE TABLE orders (

order_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers
(customer_id));

INSERT INTO customers (name) VALUES

('Alice Johnson'),
('Bob Smith'),
('Charlie Brown'),
('Diana Prince'),
('Edward Norton');

INSERT INTO orders (customer_id) VALUES

(1), -- Order for Alice Johnson
(2), -- Order for Bob Smith
(1), -- Another order for Alice Johnson
A foreign key is a field in one table that uniquely identifies a row of (3), -- Order for Charlie Brown
FOREIGN KEY another table, creating a link between the two tables. (4); -- Order for Diana Prince
SELECT id FROM customers WHERE last_name = 'Alice Johnson';
SELECT * FROM orders WHERE customer_id = 1;

-- To perform a (kind of useless) cross join: A cross join is a join operation that returns the Cartesian product of
SELECT * FROM customers, orders; two tables, producing all possible combinations of rows from both
CROSS JOIN tables.

SELECT * FROM customers

JOIN orders
ON orders.customer_id = customers.customer_id;

SELECT name FROM customers

JOIN orders ON orders.customer_id = customers.id; An inner join is a join operation that returns only the rows where
INNER JOIN there is a match in both tables based on a specified condition.

SELECT
first_name, last_name, SUM(amount) AS total
FROM
customers
JOIN
orders ON orders.customer_id = customers.id
GROUP BY first_name , last_name select customer_id, COUNT(amount) from orders GROUP BY
ORDER BY total; customer_id;
INNER JOIN with GROUP BY

Left join means, everything from Left table (table which we kept first
SELECT in the query)
first_name, last_name, order_date, amount
FROM In this case customers
customers
LEFT JOIN Difference?
orders ON orders.customer_id = customers.id; Suppose there are few customers who never placed any orders
those also will be reflected in the result which is not the case in
LEFT JOIN INNER JOIN

SELECT
first_name,
last_name,
IFNULL(SUM(amount), 0) AS money_spent
FROM
customers
LEFT JOIN
orders ON customers.id = orders.customer_id
GROUP BY first_name , last_name;
LEFT JOIN with GROUP BY

SELECT
first_name, last_name, order_date, amount
FROM
customers
RIGHT JOIN
orders ON customers.id = orders.customer_id;
RIGHT JOIN

CREATE TABLE customers (

id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(50) When we create relation between the tables ex
); customers and orders

CREATE TABLE orders ( if you try to delete a customer data which is refferenced in orders
id INT PRIMARY KEY AUTO_INCREMENT, table, you will not be able to do that
order_date DATE,
amount DECIMAL(8 , 2 ), For that purpose, you need to use
customer_id INT, ON DELETE CASCADE
FOREIGN KEY (customer_id)
REFERENCES customers (id) Now when we try to delete any customer data
ON DELETE CASCADE it's associated orders will also be deleted
);
ON DELETE CASCADE
SELECT
first_name,
IFNULL(AVG(grade), 0) AS average,
CASE
WHEN IFNULL(AVG(grade), 0) >= 75 THEN 'passing'
ELSE 'failing'
END AS passing_status
FROM
students
LEFT JOIN
papers ON students.id = papers.student_id
GROUP BY first_name
Relationships and Joins ORDER BY average DESC;
One: MANY Exercise
 Students
CREATE TABLE students (
s_id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL
);

Courses
CREATE TABLE courses (
c_id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
fee NUMERIC NOT NULL
);

Enrollment
CREATE TABLE enrollment (
enrollment_id SERIAL PRIMARY KEY,
s_id INT NOT NULL,
c_id INT NOT NULL,
enrollment_date DATE NOT NULL,
FOREIGN KEY (s_id) REFERENCES students(s_id),
FOREIGN KEY (c_id) REFERENCES courses(c_id)
);

============================================
INSERT INTO Students (name) VALUES
('Raju'),
('Sham'),
('Alex');

INSERT INTO courses (name, fee)

VALUES
('Mathematics', 500.00),
('Physics', 600.00),
('Chemistry', 700.00);

INSERT INTO enrollment (s_id, c_id, enrollment_date)

VALUES
(1, 1, '2024-01-01'), -- Raju enrolled in Mathematics In order to create the Many to Many relationship, we need to create
(1, 2, '2024-01-15'), -- Raju enrolled in Physics a separate table called junction table
(2, 1, '2024-02-01'), -- Sham enrolled in Mathematics
(2, 3, '2024-02-15'); -- Sham enrolled in Chemistry ex we have a students and courses table
(3, 3, '2024-03-25'); -- Sham enrolled in Chemistry we will create a junction table called enrollment where we keep the
record which student choosen which course etc
How to Create Many to Many tables

SELECT student_name, COUNT(course_name) FROM students

JOIN
student_course ON student_course.student_id=students.id
JOIN
courses ON student_course.course_id=courses.id
How to use JOINS and Group by GROUP BY student_name; This is just an example, kindly change the column name while using
with MANY TO MANY it.

SELECT student_name, SUM(fees) FROM students

JOIN
student_course ON student_course.student_id=students.id
JOIN
courses ON student_course.course_id=courses.id
GROUP BY student_name; This is just an example, kindly change the column name while using
MANY:MANY Total Fees paid by EACH Student it.

CREATE VIEW inst_info AS

SELECT student_name, course_name, fees FROM students
JOIN
Creating Virtual Tables student_course ON student_course.student_id=students.id After creating VIEW we can check by using
as a short cut or to JOIN SHOW TABLES;
practice performaing actions courses ON student_course.course_id=courses.id;
Views are just virtual tables.

For Deleting VIEW DROP VIEW <view_name>

CREATE OR REPLACE VIEW <view_name> AS

your new query

ALTER VIEW <view_name> AS

your new query
For Altering or Modifying VIEW
https://dev.mysql.com/doc/refman/8.0/en/view-updatability.html

To modify or delete data from virtual tables

there are some rules given in above document

Altering or Deleting Details of CREATE VIEW my_view2 AS But there is one more way, create a VIEW from the result of existing
Virtual Tables SELECT * FROM my_view1; view
VIEWS

SELECT The HAVING clause is used in the SELECT statement with the
title, GROUP BY clause.
AVG(rating), It filters rows from the result set after grouping and aggregation
COUNT(rating) AS review_count operations have been performed.
FROM full_reviews The HAVING clause operates on aggregated values (e.g., the result
GROUP BY title HAVING COUNT(rating) > 1; of SUM, COUNT, AVG, etc.) and filters groups based on these
HAVING aggregated values.

SELECT * FROM inst_info;

SELECT student_name, SUM(fees) FROM inst_info
GROUP BY student_name WITH ROLLUP;

SELECT student_name, AVG(fees) FROM inst_info

GROUP BY student_name WITH ROLLUP;
While printing add a extra row
to display SELECT course_name, COUNT(student_name) FROM inst_info
GROUP BY GROUP BY course_name WITH ROLLUP;
ROLLUP SUM, AVG, COUNT of whole table
An SQL statement or a set of SQL Statement that can be stored on
What is Stored Routine database server which can be call no. of times.
There are two types of SR
Stored procedure
Types User Defined Functions
 #We can drop if exist already
DROP PROCEDURE IF EXISTS p_name;

#Temp changing delimiter

DELIMITER $$

CREATE PROCEDURE p_name()

BEGIN
SELECT * FROM employees
LIMIT 10;
END$$ For calling

#Again changing Delimiter call db_name.p_name();

Stored Procedure DELIMITER ; call p_name;

DELIMITER $$

CREATE procedure get_empid(IN p_fname VARCHAR(50))

BEGIN
Select emp_id from employees
where fname=p_fname;
END$$

DELIMITER ;
SP with parameters

DELIMITER $$

CREATE procedure get_sum(

IN p_fname VARCHAR(50), OUT p_sum DECIMAL(10,2))
BEGIN
Select SUM(salary) INTO p_sum from employees;
END$$
set @p_sum = 0;
DELIMITER ; call bank_db.get_sum('Raju', @p_sum);
SP with OUTPUT Parameter select @p_sum;
DELIMITER $$

CREATE FUNCTION get_sum(p_fname VARCHAR(50))

RETURNS VARCHAR(50)
DETERMINISTIC NO SQL READS SQL DATA · DETERMINISTIC – it states that the function will always return
identical result given the same input
BEGIN
DECLARE v_max INT; · NO SQL – means that the code in our function does not contain
DECLARE v_name VARCHAR(50); SQL (rarely the case)
Select MAX(salary) INTO v_max from employees;
Select fname into v_name from employees · READS SQL DATA – this is usually when a simple SELECT
where salary=v_max; statement is present

return v_name;
END$$
set @p_sum = 0;
DELIMITER ; call bank_db.get_sum('Raju', @p_sum);
STORED ROUTINES USER-DEFINED FUNCTIONS select @p_sum;

select
ROW_NUMBER() OVER () row_no,
emp_id,
fname,
salary,
for each row from a query, perform SUM(salary) OVER (ORDER BY emp_id) sum
a calculation using rows related to from employees;
that row.
Parition by
can be used to calculate based on grouping

MAX(salary) OVER (PARTITION BY dept) MAX in this example, it will first group the dept then find the max salary

SUM(salary) OVER (ORDER BY emp_id) sum It will be performing a rolling SUM

It will rank the salary

highgest salary will be rank 1
and lowest will be so on

RANK() OVER(ORDER BY salary DESC) ranking if there are two emp with same salary, their rank will be same
We can combine the windows
operation like OVER(PARTITION BY dept ORDER BY salary DESC)

Only difference is with RANK() is

If two students have 400 marks and third student have 350 marks
RANK() will rank like std1 - 1st rank, std2 - 1st rank, and 3rd student
- 3rd rank

DENSE_RANK() will rank the 3rd student as 2nd rank

DENSE_RANK() OVER(ORDER BY salary DESC) ranking

SELECT
emp_no,
department,
salary,
salary - LAG(salary) OVER(ORDER BY salary DESC) as
salary_diff
FROM employees;

SELECT
emp_no,
department,
salary,
salary - LAG(salary) OVER(PARTITION BY department ORDER
BY salary DESC) as dept_salary_diff
WINDOWS Function LEAD & LAG Funciton FROM employees;
 CREATE TRIGGER trigger_name
What is Trigger? AFTER INSERT ON table_name
FOR EACH ROW
A trigger is a stored procedure in BEGIN
SQL that automatically executes in -- Trigger logic here
response to specific events on a END;
particular table or view.

How to check the existing trigger?

SHOW TRIGGERS WHERE `Table` = 'employees';

How to delete the existing trigger?

DROP TRIGGER database_name.trigger_name;
Triggers
Example:

WITH AvgSal AS (
SELECT
dept, AVG(salary) AS avg_salary FROM employees
GROUP BY
dept
)

SELECT
e.emp_id, e.fname, e.dept, e.salary,
What is CTE a.avg_salary
FROM
CTE (Common Table Expression) is employees e In short, we are first executing a query, get the result and then using
a temporary result set that you can JOIN it in another query.
define within a query to simplify AvgSal a ON e.dept = a.dept
complex SQL statements. WHERE Once CTE has been created it can only be used once. It will not be
CTE e.salary > a.avg_salary; persisted.

How to add index?

CREATE INDEX i_name
ON employees(salary, emp_id);

How to see index?

What is index SHOW INDEX from
employees FROM emp_db;
Creating and using indexes in Specially useful when dealing with large set of data.
MySQL is a powerful way to How to remove index?
optimize the performance of a ALTER TABLE employees If we created index based on fname let's say, whenever you
Index database. DROP INDEX i_name; execture query based on fname it will be much faster.
Backup
mysqldump -u root -p mydatabase >
mydatabase_backup.sql

Import
mysql -u root -p mydatabase <
mydatabase_backup.sql

Note: While importing with above

method, you must have the db with
Import & Export Database that name already else create one.