SQL
/* 01 - In Mac , connect database */--
-- open terminal type
mysql -u root -p
--enter password */
qwerty123
/* 02 - Showing database List */
SHOW DATABASES;
/* 03 - Create database */
CREATE DATABASE rajat;
/* 04 - Select database */
-- syntax, USE Data Base Name
USE rajat;
/* 05 - Create table and column name in database */
-- syntax - CREATE TABLE table ( column name datatype);
CREATE TABLE raj5(
id INT NOT NULL UNIQUE,
nam VARCHAR(50) NOT NULL,
age INT NOT NULL CHECK(age >= 18),
gender VARCHAR(1) NOT NULL,
phone VARCHAR(10) NOT NULL UNIQUE,
city VARCHAR(15) NOT NULL DEFAULT 'Agra'
);
/* 06 - INSERT data in table one by one */
-- syntax - INSERT INTO table (column1, column2) VALUES (value1, value2);
INSERT INTO raj5 ( id, nam, age, gender, phone, city)
VALUES ( 1,"Ram Kumar", "23", "M", "0001","Meerut" );
--or
INSERT INTO raj5 ( id, nam, age, gender, phone, city)
VALUES ( 2,"Meera Khan", "22", "F", "0002","Delhi" );
--or
INSERT INTO raj5 ( id, nam, age, gender, phone, city)
VALUES ( 3,"Anil Kapoor", "21", "M", "0003","Mumbai" );
/* 07 - INSERT multiple data */
-- syntax - INSERT INTO table (column1) VALUES (value1), (value2), (value3);
INSERT INTO raj5 ( id, nam, age, gender, phone, city)
VALUES
(4,"Ram Kumar","23","M","0004","Agra"),
(5,"Sarita Kumari","21","F","0005","Mawana"),
(6,"Salman Khan","20","M","0006","Agra"),
(7,"Juhi Chawla","28","F","0007","Bhopal"),
(8,"Anil Kapoor","22","M","0008","Agra"),
(9,"John Abraham","21","M","0009","Delhi");
-- CHECK FOR NOT NULL, gender value not inserted
INSERT INTO raj5( id, nam, age, gender, phone, city)
VALUES (10,"SSarita","18","4015155","Agra"); -- -error column count doesnot match value count
-- DEFAULT, city emter meerut, Already Defined
INSERT INTO raj5(id, nam, age, gender, phone)
VALUES(11,"Rajat Singh", "20", "M", "49873214"); -- -error column count doesnot match value count
-- UNIQUE, Duplicate phone number
INSERT INTO raj5(id, nam, age, gender, phone, city)
VALUES(12,"Juhi Chawla","21","F","0009","Agra"); -- -ERROR 1062 (23000): Duplicate entry '0009' for key 'raj5.phone'
-- Conditon Check, age
INSERT INTO raj5(id, nam, age, gender, phone, city)
VALUES(13,"John","17","F","4017755","Agra"); -- ERROR 3819 (HY000): Check constraint 'raj5_chk_1' is violated.
/* 08 - Select Data from tabel */
-- select all column
SELECT * FROM raj5;
-- select name,phone FORM column
SELECT nam, phone FROM raj5;
-- where clause (conditional base search)
-- Condition meet
SELECT * FROM raj5 WHERE gender = "F";
SELECT * FROM raj5 WHERE age<20;
SELECT * FROM raj5 WHERE city != "Agra"; --NOT
SELECT * FROM raj5 WHERE city = "Agra";
SELECT Id, Student Name FROM raj5 WHERE city <> "Agra"; --NOT
SELECT * FROM raj5 WHERE age >= 18 AND age <= 21;
SELECT * FROM raj5 WHERE age <= 20 AND gender = "M";
SELECT * FROM raj5 WHERE (city = "Bhopal" OR city = "Agra") AND gender = "M";
SELECT * FROM raj5 WHERE NOT (city = "Bhopal" OR city = "Agra");
SELECT * FROM raj5 WHERE NOT age >= 20;
-- IN & NOT IN
SELECT * FROM raj5 WHERE age IN(18,21); --find only 18 & 21
SELECT * FROM raj5 WHERE age IN(18,21,19); --
SELECT * FROM raj5 WHERE age NOT IN(18,21,19);
SELECT * FROM raj5 WHERE city IN("Delhi","Bhopal");
-- BETWEEN, AND
SELECT * FROM raj5 WHERE age BETWEEN 18 AND 20; --INCLUDE 18 & 22
SELECT * FROM raj5 WHERE age NOT BETWEEN 18 AND 20;
SELECT * FROM raj5 WHERE id BETWEEN 2 AND 5;
SELECT * FROM raj5 WHERE id NOT BETWEEN 2 AND 5;
SELECT * FROM raj5 WHERE nam BETWEEN "a" AND "k";
SELECT * FROM raj5 WHERE nam BETWEEN "anil" AND "kamal"; -- first characters
-- LIKE, %, _,
SELECT * FROM raj5 WHERE nam LIKE "s%"; -- start from word s
SELECT * FROM raj5 WHERE nam LIKE "ram%"; -- start from ram
SELECT * FROM raj5 WHERE nam LIKE "%am%"; -- am in between, last first , no matter
SELECT * FROM raj5 WHERE nam LIKE "r%" OR nam LIKE "s%"; -- starts with r or s
SELECT * FROM raj5 WHERE nam NOT LIKE "r%";
SELECT * FROM raj5 WHERE BINARY nam LIKE "r%"; -- only small case
SELECT * FROM raj5 WHERE nam LIKE "%r"; -- value end with chracter r
SELECT * FROM raj5 WHERE nam LIKE "r%r"; -- like ram kumar
SELECT * FROM raj5 WHERE nam LIKE "_am%"; -- only have 1 character at starting, and am at end of that word
SELECT * FROM raj5 WHERE nam LIKE "__m%"; -- only have 2 characters at starting of character m, and no character at end
SELECT * FROM raj5 WHERE nam LIKE "r_m%"; -- start with =r, one character in middle, end with =m
-- Regular Expression ' ', ^, $, [],
SELECT * FROM raj5 WHERE nam REGEXP 'ra'; -- return the world which contain ra,middle last or start
SELECT * FROM raj5 WHERE nam REGEXP '^ra'; -- starting form ra
SELECT * FROM raj5 WHERE nam REGEXP 'an$'; -- end with an word
SELECT * FROM raj5 WHERE nam REGEXP 'ram|kapoor|khan'; -- multiple search
SELECT * FROM raj5 WHERE nam REGEXP 'ram|poor|khan'; -- same as above
SELECT * FROM raj5 WHERE nam REGEXP '^ram|poor|^khan'; -- starting with ram, and poor word, and starting with khan
SELECT * FROM raj5 WHERE nam REGEXP '^ram|poor|khan$'; -- check khan at the end
SELECT * FROM raj5 WHERE nam REGEXP '[xt]'; -- check each value
SELECT * FROM raj5 WHERE nam REGEXP '[rm]a'; -- combination of ra and ma
SELECT * FROM raj5 WHERE nam REGEXP 'r[am]'; -- combination ra and rm
SELECT * FROM raj5 WHERE nam REGEXP '^[rs]'; -- start with r or s
SELECT * FROM raj5 WHERE nam REGEXP '[rs]$'; -- end with r or s
SELECT * FROM raj5 WHERE nam REGEXP '[a-b]r'; -- range with r end ( ar,br)
-- ORDER BY
SELECT * FROM raj5 ORDER BY nam; -- alphabetic order
SELECT * FROM raj5 ORDER BY nam DESC; -- descending order
SELECT * FROM raj5 WHERE city = "Agra" ORDER BY nam DESC; -- WHERE, ORDER BY, DESC
SELECT * FROM raj5 WHERE city = "Agra" ORDER BY nam; -- WHERE, ORDER BY, DESC
SELECT * FROM raj5 ORDER BY age; -- order by using numbers
SELECT * FROM raj5 ORDER BY city;
SELECT * FROM raj5 ORDER BY nam,city; -- multiple order
-- DISTINCT ( show only single apperence values , no duplicate )
SELECT DISTINCT city FROM raj5;
SELECT DISTINCT age FROM raj5;
SELECT DISTINCT age FROM raj5 ORDER BY age;
-- null
SELECT * FROM raj5 WHERE city IS NULL; -- it doesnot means empty
SELECT * FROM raj5 WHERE nam IS NULL;
SELECT * FROM raj5 WHERE nam IS NOT NULL;
-- LIMIT & OFFSET (NEED TO SEE THE DATA IN LIMITED FORM )
SELECT * FROM raj5 LIMIT 2; -- starting two values
SELECT * FROM raj5 WHERE city = "Agra" LIMIT 3; -- limited to 3 result
SELECT * FROM raj5 WHERE city = "Agra" ORDER BY nam LIMIT 3;
-- Offset
SELECT * FROM raj5 LIMIT 3, 2; -- leave starting 3 entry and then only show 2 entry
SELECT * FROM raj5 LIMIT 6, 3;
SELECT * FROM raj5 LIMIT 0, 3;
/* 09 - ALIAS , AS make it more readable */
-- syntax
-- SELECT ID AS CustomerID FROM table;
-- SELECT CustomerID ID FROM tabel; AS optional
-- create column
CREATE TABLE raj5(
id INT NOT NULL UNIQUE,
nam VARCHAR(50) NOT NULL,
age INT NOT NULL CHECK(age >= 18),
gender VARCHAR(1) NOT NULL,
phone VARCHAR(10) NOT NULL UNIQUE,
city VARCHAR(15) NOT NULL DEFAULT 'Agra'
);
-- enter the data
INSERT INTO raj5 ( id, nam, age, gender, phone, city)
VALUES
(14,"Kumar","23","M","0014","Agra"),
(25,"Sarita","21","F","0015","Mawana"),
(16,"swati","30","F","0016","Doon"),
(17,"guddi","28","F","0017","Mussoorie"),
(18,"Anil Kapoor","22","M","0018","Agra"),
(19,"shilpi","21","F","0019","Rishikesh");
-- for making it more readible
SELECT id AS "Student Id",nam AS "Student Name",phone AS "Phone Number" FROM raj5 WHERE city = "Agra";
SELECT id AS "Student Id",nam AS "Student Name" FROM raj5 WHERE city = "Agra"; --if two word ,then use double quotes
/* 10 - count, max, min, sum, avg */
-- COUNT
SELECT COUNT(nam) FROM raj5; -- count name only
SELECT COUNT(*) FROM raj5;
SELECT COUNT(DISTINCT city) FROM raj5; -- count only unique city
SELECT COUNT(DISTINCT city) AS Count FROM raj5; -- (Alis) give name to the heading
-- MAX
SELECT MAX(age) AS age FROM raj5;
-- MIN
SELECT MIN(age) AS AGE FROM raj5;
-- SUM
SELECT SUM(age) AS Total FROM raj5;
-- AVG
SELECT AVG(age) AS Average FROM raj5;
/* 11 - Update */
-- syntax - update table set value where condition meet
UPDATE raj5 SET phone = "4055555" WHERE id = 1; -- update phoen whre id = 1
UPDATE raj5 SET age = 66 WHERE id = 2;
UPDATE raj5 SET age = 55 WHERE id IN (2,4); -- select id 2 and 4 only
UPDATE raj5 SET age = 19; -- cant chage the whole table value in safe mode
/* 12 - commit and rollback */
-- first commit it . otherwise every data will delete, before commit , roll back not posssible
-- if not working , SET autocommit = 0;
SET autocommit = 0;
-- Example-1
SELECT * FROM raj5;
UPDATE raj5 SET percentage = 60 WHERE id = 2;
ROLLBACK; -- ( successfully roll back the entry )
-- Example-2
SELECT * FROM raj5;
UPDATE raj5 SET percentage = 60 WHERE id = 2;
COMMIT;
ROLLBACK; -- ( can not roll back the entry )
-- Example-3
SELECT * FROM raj5;
UPDATE raj5 SET percentage = 50 WHERE id = 2;
SELECT * FROM raj5; -- just for check
COMMIT;
ROLLBACK; -- not effect
UPDATE raj5 SET percentage = 00 WHERE id = 2;
ROLLBACK; -- rollback
/* 13 - Delete row */
DELETE FROM raj5 WHERE id = 37;
DELETE FROM raj5 WHERE gender = 'F'; -- can not delete you are using a safe mode
DELETE FROM raj5 WHERE age > 20; -- can not delete you are using a safe mode
DELETE FROM raj5; -- delete the entir database, but not the databasse name
/* 14 - Delete Column */
ALTER TABLE Customers
DROP COLUMN ContactName;
/* 15 - Foregin Key in one go */
-- sql14.png
-- create table "city"
CREATE TABLE city(
cid INT NOT NULL AUTO_INCREMENT, -- id of the table auto-increment
cityname VARCHAR(50) NOT NULL,
PRIMARY KEY (cid) -- must at last
);
-- insert records in "city" table
INSERT INTO city(cityname) -- id is auto incremental
VALUES('Agra'),
('Delhi'),
('Bhopal'),
('Jaipur'),
('Noida');
-- create table "raj5" and insert records
CREATE TABLE raj5(
id INT NOT NULL,
nam VARCHAR(50) NOT NULL,
percent INT NOT NULL,
age INT NOT NULL,
gender VARCHAR(1) NOT NULL,
city INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (city) REFERENCES City (cid) -- foreign key = save another table value( must have primary key ) in this column
-- reference key = other table_name & columan_name (where to match)
);
-- FOREIGN KEY (table name) = make city columan as foreign key
INSERT INTO raj5(id, nam, percent, age, gender, city) -- manually store the foregin key
VALUES
(1,"Ram Kumar","45","23","M",1),
(2,"Sarita Kumari","56","21","F",2),
(3,"Salman Khan","62","20","M",1),
(4,"Juhi Chawla","47","18","F",3),
(5,"Anil Kapoor","74","22","M",1),
(6,"John Abraham","64","21","M",2),
(7,"Shahid Kapoor","52","20","M",1);
/* 16 - Foregin Key one by one by one */
-- create table "city1" and insert record
CREATE TABLE city1(
cid INT NOT NULL AUTO_INCREMENT,
cityname VARCHAR(50) NOT NULL,
PRIMARY KEY (cid)
);
--insert
INSERT INTO city1(cityname)
VALUES('Agra'),
('Delhi'),
('Bhopal'),
('Jaipur'),
('Noida');
-- create table "raj6" and insert records
CREATE TABLE raj6(
id INT NOT NULL,
nam VARCHAR(50) NOT NULL,
percent INT NOT NULL,
age INT NOT NULL,
gender VARCHAR(1) NOT NULL,
city INT NOT NULL,
PRIMARY KEY (id)
);
-- insert
INSERT INTO raj6(id, nam, percent, age, gender, city)
VALUES
(1,"Ram Kumar","45","23","M",1),
(2,"Sarita Kumari","56","21","F",2),
(3,"Salman Khan","62","20","M",1),
(4,"Juhi Chawla","47","18","F",3),
(5,"Anil Kapoor","74","22","M",1),
(6,"John Abraham","64","21","M",2),
(7,"Shahid Kapoor","52","20","M",1);
-- add foregin key to the raj6 tabel
ALTER TABLE raj6
ADD FOREIGN KEY (city)
REFERENCES City1(Cid);
/* 17 - INNER JOIN : Basic */
--pic
-- SYNTAX :
SELECT column FROM table1 INNER JOIN table2
ON table1.column_name = table2.column_name;
-- Example - 1
--student
CREATE TABLE student(
student_id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
PRIMARY KEY (student_id)
);
INSERT INTO student(name)
VALUES('Alice'),
('Bob'),
('Charlie');
-- courses
CREATE TABLE courses(
course_id INT NOT NULL,
student_id INT NOT NULL,
course_name VARCHAR(50) NOT NULL
);
INSERT INTO courses(course_id, student_id, course_name)
VALUES(101,1,'Math'),
(102,2,'Science'),
(103,3,'English'),
(104,1,'History');
-- inner join
SELECT student.name, courses.course_name
FROM student
INNER JOIN courses ON student.student_id = courses.student_id;
/* 18 - LEFT JOIN & RIGHT JOIN : Basic */
-- SYNTAX :
SELECT column FROM table1 INNER JOIN table2
ON table1.column_name = table2.column_name;
-- Example - 1
--student
CREATE TABLE student(
student_id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
PRIMARY KEY (student_id)
);
INSERT INTO student(name)
VALUES('Alice'),
('Bob'),
('Charlie'),
('David');
--courses
CREATE TABLE courses(
course_id INT NOT NULL,
student_id INT NOT NULL,
course_name VARCHAR(50) NOT NULL
);
INSERT INTO courses(course_id, student_id, course_name)
VALUES(101,1,'Math'),
(102,2,'Science'),
(103,3,'English'),
(104,1,'History'),
(105,5,'Hindi');
-- left join
SELECT student.name, courses.course_name
FROM student
LEFT JOIN courses ON student.student_id = courses.student_id;
-- right join
SELECT student.name, courses.course_name
FROM student
RIGHT JOIN courses ON student.student_id = courses.student_id;
/* 19 - INNER JOIN : MORE */
-- create table jcity and insert data
CREATE TABLE jcity(
cid INT NOT NULL AUTO_INCREMENT,
cityname VARCHAR(50) NOT NULL,
PRIMARY KEY (cid)
);
-- insert
INSERT INTO jcity(cityname)
VALUES('Agra'),
('Delhi'),
('Bhopal'),
('Jaipur'),
('Noida');
-- create table doon and insert data
CREATE TABLE doon(
id INT NOT NULL,
nam VARCHAR(50) NOT NULL,
perce INT NOT NULL,
age INT NOT NULL,
gender VARCHAR(1) NOT NULL,
city INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (city)
REFERENCES jcity (cid)
);
-- insert
INSERT INTO doon(id, nam, perce, age, gender, city)
VALUES
(1,"Ram Kumar","45","13","M",1),
(2,"Sarita Kumari","56","21","F",2),
(3,"Salman Khan","62","20","M",1),
(4,"Juhi Chawla","47","18","F",3),
(5,"Anil Kapoor","74","22","M",1),
(6,"John Abraham","64","21","M",2),
(7,"Shahid Kapoor","52","20","M",1);
-- inner join
SELECT * FROM doon INNER JOIN jcity -- tabel1, tabel2
ON doon.city = jcity.cid; -- tabel1 column = tabel2 column
-- inner join + alias
SELECT * FROM doon Dd INNER JOIN jcity Cc
ON Dd.city = Cc.cid;
-- multiple table
SELECT Dd.id, Dd.nam, Dd.perce, Dd.age, Dd.gender, Cc.cityname
FROM doon Dd INNER JOIN jcity Cc
ON Dd.city = Cc.cid;
-- Where clause
SELECT Dd.id, Dd.nam, Dd.perce, Dd.age, Dd.gender, Cc.cityname
FROM doon Dd INNER JOIN city Cc
ON Dd.city = Cc.cid
WHERE Cc.cityname = "Agra";
-- Order by
SELECT Dd.id, Dd.nam, Dd.perce, Dd.age, Dd.gender, Cc.cityname
FROM doon Dd INNER JOIN city Cc
ON Dd.city = Cc.cid
WHERE Cc.cityname = "Agra"
ORDER BY Dd.nam;
-- join only( instead of innerjoin)
SELECT Dd.id, Dd.nam, Dd.perce, Dd.age, Dd.gender, Cc.cityname
FROM doon Dd JOIN city Cc
ON Dd.city = Cc.cid
WHERE Cc.cityname = "Agra"
ORDER BY Dd.nam;
/* 20 - LEFT JOIN & RIGHT JOIN : MORE */
--LEFT JOIN (show all first table, & comman values form both tables)
SELECT * FROM doon LEFT JOIN jcity
ON doon.city = jcity.cid;
-- using alias
SELECT * FROM doon Dd LEFT JOIN jcity Cc
ON Dd.city = Cc.cid;
-- selected column
SELECT Dd.id, Dd.nam, Dd.perce, Dd.age, Dd.gender, Cc.cityname
FROM doon Dd LEFT JOIN jcity Cc
ON Dd.city = Cc.cid;
-- where
SELECT Dd.id, Dd.nam, Dd.perce, Dd.age, Dd.gender, Cc.cityname
FROM doon p LEFT JOIN jcity Cc
ON p.city = Cc.cid
WHERE gender = "M";
-- order by
SELECT Dd.id, Dd.nam, Dd.perce, Dd.age, Dd.gender, Cc.cityname
FROM doon Dd LEFT JOIN jcity Cc
ON Dd.city = Cc.cid
WHERE gender = "M"
ORDER BY name;
--RIGHT JOIN ( show all second table value with comman values form both table )
SELECT * FROM doon RIGHT JOIN jcity
ON doon.city = city.cid;
SELECT * FROM doon Dd RIGHT JOIN jcity Cc
ON Dd.city = Cc.cid;
SELECT Dd.id, Dd.nam, Dd.perce, Dd.age, Dd.gender, Cc.cityname
FROM doon Dd RIGHT JOIN jcity Cc
ON Dd.city = Cc.cid;
/* 21 - CROSS_JOIN */
-- first value of first table pair with all the value of second table, same process start with second value of first table ans so on
-- sql25.png
--CROSS JOIN
SELECT Dd.id,Dd.nam,Cc.cityname
FROM doon Dd CROSS JOIN jcity Cc;
-- alias
SELECT Dd.id, Dd.nam AS Name, Cc.cityname AS City
FROM doon Dd CROSS JOIN jcity Cc;
SELECT Dd.id, Dd.nam AS Name, Cc.cityname AS City
FROM doon Dd , jcity Cc;
/* 22 - JOIN MULTIPLE TABLES */
-- create table "city2" and insert records --
CREATE TABLE city2(
cid INT NOT NULL AUTO_INCREMENT,
cityname VARCHAR(50) NOT NULL,
PRIMARY KEY (cid)
);
INSERT INTO city2(cityname)
VALUES('Agra'),
('Delhi'),
('Bhopal'),
('Jaipur'),
('Noida');
-- create table "courses" and insert records --
CREATE TABLE courses(
course_id INT NOT NULL AUTO_INCREMENT,
course_name VARCHAR(50) NOT NULL,
PRIMARY KEY (course_id)
);
INSERT INTO courses(course_name)
VALUES('Btech'),
('BCA'),
('MBA');
-- create table "tOne1" and insert records --
CREATE TABLE tOne1(
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
percentage INT NOT NULL,
age INT NOT NULL,
gender VARCHAR(1) NOT NULL,
city INT NOT NULL,
courses INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (city) REFERENCES City (cid),
FOREIGN KEY (courses) REFERENCES Courses (course_id)
);
-- insert
INSERT INTO tOne1(id, name, percentage, age, gender, city, courses)
VALUES
(1,"Ram Kumar","45","13","M",1,1),
(2,"Sarita Kumari","56","21","F",2,2),
(3,"Salman Khan","62","20","M",1,1),
(4,"Juhi Chawla","47","18","F",3,1),
(5,"Anil Kapoor","74","22","M",1,3),
(6,"John Abraham","64","21","M",2,2),
(7,"Shahid Kapoor","52","20","M",1,3);
-- Join Multiple Tables --
-- syntax tabel1 inner join tabel2 ON cond. inner join table3 ON cond.
SELECT * FROM tOne1 Tt INNER JOIN city Cc
ON Tt.city = Cc.cid;
-- pic sql20.png
SELECT * FROM tOne1 Tt INNER JOIN city Cc
ON Tt.city = Cc.cid
INNER JOIN courses Cr
ON Tt.courses = Cr.course_id;
SELECT Tt.id, Tt.name, Tt.percentage, Tt.age, Tt.gender, Cc.cityname, Cr.course_name
FROM tOne1 Tt INNER JOIN city Cc
ON Tt.city = Cc.cid
INNER JOIN courses Cr
ON Tt.courses = Cr.course_id;
SELECT Tt.id, Tt.name, Tt.percentage, Tt.age, Tt.gender, Cc.cityname, Cr.course_name
FROM tOne1 Tt INNER JOIN city Cc
ON Tt.city = Cc.cid
INNER JOIN courses Cr
ON Tt.courses = Cr.course_id
WHERE Cc.cityname = "Agra";
/* 23 - GROUP BY & HAVING : Basic */
-- employees table
CREATE TABLE employees(
employees_id INT NOT NULL,
name VARCHAR(50) NOT NULL,
department VARCHAR(50) NOT NULL,
salary INT NOT NULL
);
INSERT INTO employees(employees_id, name, department, salary)
VALUES(1,'Alice','HR',5000),
(2,'Bob','IT',6000),
(3,'Charlie','HR',5500),
(4,'David','IT',7000),
(5,'Eve','Finance',4500);
-- query goupby
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;
// result
department total_salary
'HR', '10500'
'IT', '13000'
'Finance', '4500'
-- having
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING SUM(salary) > 10000;
// result
department total_salary
'HR', '10500'
'IT', '13000'
-- You can use WHERE to filter rows before grouping and HAVING to filter groups after aggregation.
SELECT department, SUM(salary) AS total_salary
FROM employees
WHERE salary > 4500
GROUP BY department
HAVING SUM(salary) > 10000;
// result
department total_salary
'HR', '10500'
'IT', '13000'
-- course table
CREATE TABLE courses(
course_id INT NOT NULL AUTO_INCREMENT,
course_name VARCHAR(50) NOT NULL,
PRIMARY KEY (course_id)
);
INSERT INTO courses(course_name)
VALUES('Btech'),
('BCA'),
('MBA');
-- tOne table
CREATE TABLE tOne(
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
percentage INT NOT NULL,
age INT NOT NULL,
gender VARCHAR(1) NOT NULL,
city INT NOT NULL,
courses INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (city) REFERENCES City (cid),
FOREIGN KEY (courses) REFERENCES Courses (course_id)
);
INSERT INTO tOne(id,name,percentage,age,gender,city,courses)
VALUES
(1,"Ram Kumar","45","13","M",1,1),
(2,"Sarita Kumari","56","21","F",2,2),
(3,"Salman Khan","62","20","M",1,1),
(4,"Juhi Chawla","47","18","F",3,1),
(5,"Anil Kapoor","74","22","M",1,3),
(6,"John Abraham","64","21","M",2,2),
(7,"Shahid Kapoor","52","20","M",1,3);
-- group city id and their count
SELECT city,COUNT(city)
FROM tOne
GROUP BY city;
-- group city name and their count
SELECT c.cityname,COUNT(p.city)
FROM tOne p INNER JOIN city c
ON p.city = c.cid
GROUP BY city;
-- use above with alias
SELECT c.cityname,COUNT(p.city) AS Total
FROM tOne p INNER JOIN city c
ON p.city = c.cid
GROUP BY city;
-- above with WHERE
SELECT c.cityname,COUNT(p.city) AS Total
FROM tOne p INNER JOIN city c
ON p.city = c.cid
WHERE p.age >= 20
GROUP BY city;
-- above ORDER BY
SELECT c.cityname,COUNT(p.city) AS Total
FROM tOne p INNER JOIN city c
ON p.city = c.cid
GROUP BY city
ORDER BY COUNT(p.city);
-- above ORDER BY and DESC
SELECT c.cityname,COUNT(p.city) AS Total
FROM tOne p INNER JOIN city c
ON p.city = c.cid
GROUP BY city
ORDER BY COUNT(p.city) DESC;
-- HAVING (use for applying conditions on the table came after the group by options)
SELECT c.cityname,COUNT(p.city) AS Total
FROM tOne p INNER JOIN city c
ON p.city = c.cid
GROUP BY city
HAVING COUNT(p.city) > 3
ORDER BY COUNT(p.city) DESC;
/* 24 - GROUP BY & HAVING : MORE */
-- city table
CREATE TABLE city(
cid INT NOT NULL AUTO_INCREMENT,
cityname VARCHAR(50) NOT NULL,
PRIMARY KEY (cid)
);
INSERT INTO city(cityname)
VALUES('Agra'),
('Delhi'),
('Bhopal'),
('Jaipur'),
('Noida');
-- course table
CREATE TABLE courses(
course_id INT NOT NULL AUTO_INCREMENT,
course_name VARCHAR(50) NOT NULL,
PRIMARY KEY (course_id)
);
INSERT INTO courses(course_name)
VALUES('Btech'),
('BCA'),
('MBA');
-- tOne table
CREATE TABLE tOne(
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
percentage INT NOT NULL,
age INT NOT NULL,
gender VARCHAR(1) NOT NULL,
city INT NOT NULL,
courses INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (city) REFERENCES City (cid),
FOREIGN KEY (courses) REFERENCES Courses (course_id)
);
INSERT INTO tOne(id,name,percentage,age,gender,city,courses)
VALUES
(1,"Ram Kumar","45","13","M",1,1),
(2,"Sarita Kumari","56","21","F",2,2),
(3,"Salman Khan","62","20","M",1,1),
(4,"Juhi Chawla","47","18","F",3,1),
(5,"Anil Kapoor","74","22","M",1,3),
(6,"John Abraham","64","21","M",2,2),
(7,"Shahid Kapoor","52","20","M",1,3);
-- group city id and their count
SELECT city,COUNT(city)
FROM tOne
GROUP BY city;
-- group city name and their count
SELECT c.cityname,COUNT(p.city)
FROM tOne p INNER JOIN city c
ON p.city = c.cid
GROUP BY city;
-- use above with alias
SELECT c.cityname,COUNT(p.city) AS Total
FROM tOne p INNER JOIN city c
ON p.city = c.cid
GROUP BY city;
-- above with WHERE
SELECT c.cityname,COUNT(p.city) AS Total
FROM tOne p INNER JOIN city c
ON p.city = c.cid
WHERE p.age >= 20
GROUP BY city;
-- above ORDER BY
SELECT c.cityname,COUNT(p.city) AS Total
FROM tOne p INNER JOIN city c
ON p.city = c.cid
GROUP BY city
ORDER BY COUNT(p.city);
-- above ORDER BY and DESC
SELECT c.cityname,COUNT(p.city) AS Total
FROM tOne p INNER JOIN city c
ON p.city = c.cid
GROUP BY city
ORDER BY COUNT(p.city) DESC;
-- HAVING (use for applying conditions on the table came after the group by options)
SELECT c.cityname,COUNT(p.city) AS Total
FROM tOne p INNER JOIN city c
ON p.city = c.cid
GROUP BY city
HAVING COUNT(p.city) > 3
ORDER BY COUNT(p.city) DESC;
/* 25 - SUBQUERY WITH EXISTS & NOT EXISTS */
-- ex-1 Use SubQuery
SELECT name FROM tOne
WHERE courses = (SELECT course_id FROM courses WHERE course_name = "MBA");
-- ex-2
SELECT course_id FROM courses WHERE course_name = "MBA";
-- ex-3
SELECT name FROM tOne
WHERE courses IN (SELECT course_id FROM courses WHERE course_name IN ("MBA","Btech"));
-- Exists & Not Exists
SELECT name FROM tOne
WHERE EXISTS (SELECT course_id FROM courses WHERE course_name IN ("MBA"));
SELECT name FROM tOne
WHERE EXISTS (SELECT course_id FROM courses WHERE course_name IN ("Mtech"));
SELECT name FROM tOne
WHERE NOT EXISTS (SELECT course_id FROM courses WHERE course_name IN ("Mtech"));
SELECT name FROM tOne
WHERE NOT EXISTS (SELECT course_id FROM courses WHERE course_name IN ("MBA"));
/* 26 - UNION & UNION ALL : Basics */
-- students_2023
CREATE TABLE students_2023(
student_id INT NOT NULL,
name VARCHAR(50) NOT NULL
);
INSERT INTO students_2023(student_id,name)
VALUES
(1,"Alice"),
(2,"Bob"),
(3,"Charlie");
-- students_2023
CREATE TABLE students_2024(
student_id INT NOT NULL,
name VARCHAR(50) NOT NULL
);
INSERT INTO students_2024(student_id,name)
VALUES
(2,"Bob"),
(4,"David");
-- UNION : combine and no duplicate
SELECT student_id, name
FROM students_2023
UNION
SELECT student_id, name
FROM students_2024;
-- UNION ALL : combine and duplicate
SELECT student_id, name
FROM students_2023
UNION ALL
SELECT student_id, name
FROM students_2024;
/* 27 - UNION & UNION ALL : MORE */
-- create table "students" and insert records --
CREATE TABLE students(
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
age INT NOT NULL,
gender VARCHAR(1) NOT NULL,
city INT NOT NULL,
courses INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (city) REFERENCES City (cid),
FOREIGN KEY (courses) REFERENCES Courses (course_id)
);
INSERT INTO students(id,name,age,gender,city,courses)
VALUES
(1,"Ram Kumar","19","M",1,1),
(2,"Sarita Kumari","22","F",2,2),
(3,"Salman Khan","20","M",1,1),
(4,"Juhi Chawla","18","F",3,3),
(5,"Anil Kapoor","22","M",1,3),
(6,"John Abraham","21","M",2,2),
(7,"Shahid Kapoor","20","M",1,1);
-- create table "city" and insert records --
CREATE TABLE city(
cid INT NOT NULL AUTO_INCREMENT,
cityname VARCHAR(50) NOT NULL,
PRIMARY KEY (cid)
);
INSERT INTO city(cityname)
VALUES('Agra'),
('Delhi'),
('Bhopal'),
('Jaipur'),
('Noida');
-- create table "courses" and insert records --
CREATE TABLE courses(
course_id INT NOT NULL AUTO_INCREMENT,
course_name VARCHAR(50) NOT NULL,
PRIMARY KEY (course_id)
);
INSERT INTO courses(course_name)
VALUES('Btech'),
('BCA'),
('MBA');
-- create table "lecturers" and insert records --
CREATE TABLE lecturers(
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
age INT NOT NULL,
gender VARCHAR(1) NOT NULL,
city INT NOT NULL,
courses INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (city) REFERENCES City (cid),
FOREIGN KEY (courses) REFERENCES Courses (course_id)
);
INSERT INTO lecturers(id,name,age,gender,city,courses)
VALUES
(1,"Raj Kapoor","37","M",1,2),
(2,"Sadhna","39","F",4,3),
(3,"Ram Kumar","38","M",2,1),
(4,"Salim Khan","45","M",3,2),
(5,"Nagma","42","F",2,1);
-- UNION & UNION ALL --
SELECT * FROM students
UNION
SELECT * FROM lecturers;
SELECT name FROM students
UNION
SELECT name FROM lecturers;
SELECT name FROM students
UNION ALL
SELECT name FROM lecturers;
SELECT name,age FROM students
UNION ALL
SELECT name,age FROM lecturers;
SELECT name,age FROM students WHERE gender ="M"
UNION ALL
SELECT name,age FROM lecturers WHERE gender ="M";
SELECT name,age FROM students WHERE gender ="M"
UNION ALL
SELECT name,age FROM lecturers WHERE gender ="F";
SELECT name,age FROM students WHERE city =2
UNION ALL
SELECT name,age FROM lecturers WHERE city = 2;
SELECT name,age FROM students
WHERE city = (SELECT cid FROM city WHERE cityname = "Delhi")
UNION ALL
SELECT name,age FROM lecturers
WHERE city = (SELECT cid FROM city WHERE cityname = "Delhi");
SELECT s.name,s.age, c.cityname FROM students s
INNER JOIN city c ON s.city = c.cid
WHERE c.cityname = "Delhi"
UNION ALL
SELECT l.name,l.age,ci.cityname FROM lecturers l
INNER JOIN city ci ON l.city = ci.cid
WHERE ci.cityname = "Delhi";
/* 28 - IF & CASE STATEMENT : Basics */
-- perform conditional logic within your queries
-- create sales
CREATE TABLE sales (
sale_id INT PRIMARY KEY,
amount DECIMAL(10, 2),
sale_date DATE
);
INSERT INTO sales (sale_id, amount, sale_date)
VALUES
(1, 1500, '2024-01-15'),
(2, 3000, '2024-01-20'),
(3, 500, '2024-02-15'),
(4, 2500, '2024-02-20');
-- query
SELECT sale_id, amount,
IF(amount > 2000, 'High', 'Low') AS amount_level
FROM sales;
// result
sale_id, amount, amount_level
'1', '1500.00', 'Low'
'2', '3000.00', 'High'
'3', '500.00', 'Low'
'4', '2500.00', 'High'
-- case
-- The CASE statement is more flexible and can handle multiple conditions.
SELECT sale_id, amount,
CASE
WHEN amount <= 1000 THEN 'Low'
WHEN amount > 1000 AND amount <= 2000 THEN 'Medium'
WHEN amount > 2000 THEN 'High'
ELSE 'Unknown'
END AS amount_level
FROM sales;
-- Running the Aggregation with CASE Example
SELECT
COUNT(CASE WHEN amount <= 1000 THEN 1 END) AS low_sales,
COUNT(CASE WHEN amount > 1000 AND amount <= 2000 THEN 1 END) AS medium_sales,
COUNT(CASE WHEN amount > 2000 THEN 1 END) AS high_sales
FROM sales;
/* 29 - IF & CASE STATEMENT : MORE */
-- create table "students" and insert records --
CREATE TABLE students(
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
percentage INT NOT NULL,
age INT NOT NULL,
gender VARCHAR(1) NOT NULL,
city INT NOT NULL,
courses INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (city) REFERENCES City (cid),
FOREIGN KEY (courses) REFERENCES Courses (course_id)
);
INSERT INTO students(id,name,percentage,age,gender,city,courses)
VALUES
(1,"Ram Kumar","45","19","M",1,1),
(2,"Sarita Kumari","85","22","F",2,2),
(3,"Salman Khan","29","20","M",1,1),
(4,"Juhi Chawla","47","18","F",3,3),
(5,"Anil Kapoor","74","22","M",1,3),
(6,"John Abraham","64","21","M",2,2),
(7,"Shahid Kapoor","120","20","M",1,1);
-- IF Clause --
SELECT id,name,percentage,
IF(percentage >= 33,"Pass","Fail") AS Result
FROM students;
-- CASE Clause --
SELECT id,name,percentage,
CASE
WHEN percentage >= 80 AND percentage <=100 THEN "Merit"
WHEN percentage >= 60 AND percentage <=80 THEN "Ist Division"
WHEN percentage >= 45 AND percentage <=60 THEN "IInd Division"
WHEN percentage >= 33 AND percentage <=45 THEN "IIIrd Division"
WHEN percentage < 33 THEN "Fail"
ELSE "Not Correct %"
END AS Grade
FROM students;
UPDATE students SET
percentage = (CASE id
WHEN 3 THEN 39
WHEN 7 THEN 62
END)
WHERE id IN (3,7);
/* 30 - ARITHMETIC FUNCTIONS */
-- create table "students" and insert records --
CREATE TABLE students(
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
percentage INT NOT NULL,
age INT NOT NULL,
gender VARCHAR(1) NOT NULL,
city INT NOT NULL,
courses INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (city) REFERENCES City (cid),
FOREIGN KEY (courses) REFERENCES Courses (course_id)
);
INSERT INTO students(id,name,percentage,age,gender,city,courses)
VALUES
(1,"Ram Kumar","45","19","M",1,1),
(2,"Sarita Kumari","85","22","F",2,2),
(3,"Salman Khan","29","20","M",1,1),
(4,"Juhi Chawla","47","18","F",3,3),
(5,"Anil Kapoor","74","22","M",1,3),
(6,"John Abraham","64","21","M",2,2),
(7,"Shahid Kapoor","120","20","M",1,1);
-- Arithmetic Functions --
SELECT 5 + 6;
SELECT 5 + 6 AS Total;
SELECT 5 - 6 AS Total;
SELECT 5 * 6 AS Total;
SELECT 15 / 6 AS Total;
SELECT 15 DIV 6 AS Total;
SELECT 15 % 6 AS Total;
SELECT 15 MOD 6 AS Total;
SELECT id,name,percentage FROM students;
SELECT id,name,(percentage + 5) FROM students;
SELECT id,name,(percentage + 5) AS "NEW Percentage" FROM students;
SELECT id,name,(percentage * 5) AS "NEW Percentage" FROM students;
SELECT PI();
SELECT ROUND(4.51);
SELECT ROUND(4.49);
SELECT ROUND(-4.49);
SELECT ROUND(-4.55);
SELECT ROUND(1234.987);
SELECT ROUND(1234.987,2);
SELECT CEIL(1.23);
SELECT CEIL(1.56);
SELECT CEIL(4.23);
SELECT FLOOR(4.56);
SELECT FLOOR(4.40);
SELECT POW(2,2);
SELECT POW(2,3);
SELECT POW(4,3);
SELECT SQRT(16);
SELECT SQRT(4);
SELECT SQRT(5);
SELECT ROUND(SQRT(5));
SELECT RAND();
SELECT RAND() * 100;
SELECT ROUND(RAND() * 100);
SELECT FLOOR(7 + (RAND() * 6));
SELECT FLOOR(1 + (RAND() * 5));
SELECT id,name,percentage,RAND() FROM students;
SELECT id,name,percentage FROM students ORDER BY RAND();
SELECT ABS(-56);
SELECT ABS(-56.25);
SELECT ABS(56.25);
SELECT SIGN(25);
SELECT SIGN(0);
SELECT SIGN(-25);
SELECT SIGN(-3.25);
/* 31 - STRING FUNCTIONS */
-- create table "students" and insert records --
CREATE TABLE students(
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
percentage INT NOT NULL,
age INT NOT NULL,
gender VARCHAR(1) NOT NULL,
city INT NOT NULL,
courses INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (city) REFERENCES City (cid),
FOREIGN KEY (courses) REFERENCES Courses (course_id)
);
INSERT INTO students(id,name,percentage,age,gender,city,courses)
VALUES
(1,"Ram Kumar","45","19","M",1,1),
(2,"Sarita Kumari","85","22","F",2,2),
(3,"Salman Khan","29","20","M",1,1),
(4,"Juhi Chawla","47","18","F",3,3),
(5,"Anil Kapoor","74","22","M",1,3),
(6,"John Abraham","64","21","M",2,2),
(7,"Shahid Kapoor","120","20","M",1,1);
-- String Functions --
SELECT id, UPPER(name) AS Name , percentage
FROM students;
SELECT id, UCASE(name) AS Name , percentage
FROM students;
SELECT id, LOWER(name) AS Name , percentage
FROM students;
SELECT id, LCASE(name) AS Name , percentage
FROM students;
SELECT id, name,CHARACTER_LENGTH(name) AS Characters
FROM students;
SELECT id, name,CHAR_LENGTH(name) AS Characters
FROM students;
SELECT id, name,LENGTH(name) AS Characters
FROM students;
SELECT id, CONCAT(name, " " ,percentage) AS Name
FROM students;
SELECT id, CONCAT(name, " - " ,percentage) AS Name
FROM students;
SELECT CONCAT("Yahoo","Baba","Youtube","Channel") AS Name;
SELECT CONCAT_WS("Yahoo","Baba","Youtube","Channel") AS Name;
SELECT CONCAT_WS(" - ","Baba","Youtube","Channel") AS Name;
SELECT " Yahoo Baba " AS Name;
SELECT LTRIM(" Yahoo Baba ") AS Name;
SELECT " Yahoo Baba " AS Name;
SELECT RTRIM(" Yahoo Baba ") AS Name;
SELECT TRIM(" Yahoo Baba ") AS Name;
SELECT POSITION("Baba" IN "Yahoo Baba") AS Name;
SELECT POSITION("Baba" IN "Yahoo Baba Baba") AS Name;
SELECT POSITION("a" IN "Yahoo Baba Baba") AS Name;
SELECT INSTR("Yahoo Baba Baba","a") AS Name;
SELECT INSTR("Yahoo Baba Baba","Baba") AS Name;
SELECT INSTR("Yahoo Baba Baba","hoo") AS Name;
SELECT LOCATE("hoo","Yahoo Baba Baba") AS Name;
SELECT LOCATE("a","Yahoo Baba Baba") AS Name;
SELECT LOCATE("a","Yahoo Baba Baba",3) AS Name;
/* 32 - STRING FUNCTIONS - II */
SELECT SUBSTRING("Yahoo Baba",3);
SELECT SUBSTRING("Yahoo Baba",3) AS Name;
SELECT SUBSTRING("Yahoo Baba",5) AS Name;
SELECT SUBSTRING("Yahoo Baba",3,6) AS Name;
SELECT SUBSTRING("Yahoo Baba",-6,3) AS Name;
SELECT SUBSTR("Yahoo Baba",-6,3) AS Name;
SELECT MID("Yahoo Baba",-6,3) AS Name;
SELECT SUBSTRING_INDEX("www.yahoobaba.net",".",1) AS Name;
SELECT SUBSTRING_INDEX("www.yahoobaba.net",".",2) AS Name;
SELECT SUBSTRING_INDEX("www.yahoobaba.net","o",2) AS Name;
SELECT LEFT("Yahoo Baba",3) AS Name;
SELECT LEFT("Yahoo Baba",5) AS Name;
SELECT RIGHT("Yahoo Baba",5) AS Name;
SELECT RIGHT("Yahoo Baba",3) AS Name;
SELECT RPAD("Yahoo Baba",20,"-") AS Name;
SELECT RPAD("Yahoo Baba",20,"ABC") AS Name;
SELECT LPAD("Yahoo Baba",20,"*") AS Name;
SELECT SPACE(100) AS Name;
SELECT REVERSE("Yahoo Baba") AS Name;
SELECT REPLACE("Yahoo Baba","Baba","Wow") AS Name;
SELECT REPLACE("Yahoo Baba Yoo Baba","Baba","Wow") AS Name;
SELECT STRCMP("Yahoo Baba","yahoo baba") AS Name;
SELECT STRCMP("Yahoo Baba","yahoo ") AS Name;
SELECT STRCMP("Yahoo ","yahoo baba") AS Name;
SELECT FIELD("a","X","a","k") AS Name;
SELECT FIELD("a","X","A","k") AS Name;
SELECT FIELD(5,0,1,2,3,4,5) AS Name;
SELECT FIELD("ram","Ram","Mohan","Shyam") AS Name;
SELECT FIND_IN_SET("ram","Ram,Mohan,Shyam") AS Name;
SELECT FIND_IN_SET("Mohan","Ram, Mohan, Shyam") AS Name;
SELECT FIND_IN_SET("Mohan","Ram,Mohan,Shyam") AS Name;
SELECT FORMAT(255.3568,2) AS Value;
SELECT FORMAT(255.3568,3) AS Value;
SELECT HEX("Yahoo Baba") AS Value;
/* 33 - DATE FUNCTIONS */
SELECT CURRENT_DATE();
SELECT CURDATE();
SELECT SYSDATE();
SELECT NOW();
SELECT DATE("2019-10-15 09:34:21") AS DATE;
SELECT MONTH("2019-10-15 09:34:21") AS DATE;
SELECT MONTHNAME("2019-10-15 09:34:21") AS DATE;
SELECT YEAR("2019-10-15 09:34:21") AS DATE;
SELECT QUARTER("2019-10-15 09:34:21") AS DATE;
SELECT QUARTER("2019-03-15 09:34:21") AS DATE;
SELECT DAY("2019-10-15 09:34:21") AS DATE;
SELECT DAYOFMONTH("2019-10-15 09:34:21") AS DATE;
SELECT DAYNAME("2019-06-15 09:34:21") AS DATE;
SELECT DAYOFWEEK("2019-03-15 09:34:21") AS DATE;
SELECT DAYOFYEAR("2019-06-15 09:34:21") AS DATE;
SELECT WEEK("2019-06-15 09:34:21") AS DATE;
SELECT WEEKDAY("2019-03-15 09:34:21") AS DATE;
SELECT YEARWEEK("2019-06-15 09:34:21") AS DATE;
SELECT LAST_DAY("2019-02-15 09:34:21") AS DATE;
SELECT EXTRACT(MONTH FROM "2019-03-15 09:34:21") AS DATE;
SELECT EXTRACT(DAY FROM "2019-03-15 09:34:21") AS DATE;
SELECT EXTRACT(YEAR FROM "2019-03-15 09:34:21") AS DATE;
SELECT EXTRACT(WEEK FROM "2019-03-15 09:34:21") AS DATE;
SELECT EXTRACT(HOUR FROM "2019-03-15 09:34:21") AS DATE;
SELECT EXTRACT(MINUTE FROM "2019-03-15 09:34:21") AS DATE;
SELECT EXTRACT(SECOND FROM "2019-03-15 09:34:21") AS DATE;
SELECT EXTRACT(MICROSECOND FROM "2019-03-15 09:34:21") AS DATE;
SELECT EXTRACT(HOUR_MINUTE FROM "2019-03-15 09:34:21") AS DATE;
/* 34 - DATE FUNCTIONS - II */
SELECT ADDDATE("2019-06-15",INTERVAL 10 DAY) AS Date;
SELECT ADDDATE("2019-06-15",INTERVAL 50 DAY) AS Date;
SELECT ADDDATE("2019-06-15",INTERVAL 1 MONTH) AS Date;
SELECT ADDDATE("2019-06-15",INTERVAL 1 WEEK) AS Date;
SELECT ADDDATE("2019-06-15",INTERVAL 1 YEAR) AS Date;
SELECT ADDDATE("2019-06-15",INTERVAL 1 QUARTER) AS Date;
SELECT ADDDATE("2019-06-15",INTERVAL 12 HOUR) AS Date;
SELECT ADDDATE("2019-06-15",INTERVAL 24 HOUR) AS Date;
SELECT ADDDATE("2019-06-15",INTERVAL 500 MINUTE) AS Date;
SELECT ADDDATE("2019-06-15",INTERVAL 5000 MINUTE) AS Date;
SELECT DATE_ADD("2019-06-15",INTERVAL 5000 MINUTE) AS Date;
SELECT MAKEDATE(2019,3);
SELECT MAKEDATE(2016,3);
SELECT SUBDATE("2019-06-15",INTERVAL 1 DAY) AS Date;
SELECT SUBDATE("2019-06-15",INTERVAL 10 DAY) AS Date;
SELECT SUBDATE("2019-06-15",INTERVAL 10 MONTH) AS Date;
SELECT DATEDIFF("2019-06-15","2019-02-10") AS Date;
SELECT DATEDIFF("2019-06-15","2019-06-01") AS Date;
SELECT DATEDIFF("2019-06-15","2018-06-01") AS Date;
SELECT TO_DAYS("2019-06-15") AS Date;
SELECT FROM_DAYS("685000") AS Date;
SELECT PERIOD_ADD("685000",5) AS Date;
SELECT PERIOD_DIFF("685000","695000") AS Date;
SELECT DATE_FORMAT("2019-06-15","%Y") AS Date;
SELECT DATE_FORMAT("2019-06-15","%d/%b/%Y") AS Date;
SELECT DATE_FORMAT("2019-06-15","%d-%c-%Y") AS Date;
SELECT DATE_FORMAT("2019-06-15","%d/%b/%y") AS Date;
SELECT DATE_FORMAT("2019-06-15","%d-%c-%y, %W") AS Date;
SELECT DATE_FORMAT("2019-06-15 02:30:50:20","%d-%c-%y, %h:%i") AS Date;
SELECT STR_TO_DATE("July 10 2019","%M %d %Y") AS Date;
/* 35 - TIME FUNCTIONS */
SELECT CURRENT_TIME();
SELECT CURTIME();
SELECT CURRENT_TIMESTAMP();
SELECT LOCALTIME();
SELECT LOCALTIMESTAMP();
SELECT TIME("2019-06-15 13:15:20") AS Time;
SELECT HOUR("2019-06-15 13:15:20") AS Time;
SELECT MINUTE("2019-06-15 13:15:20") AS Time;
SELECT SECOND("2019-06-15 13:15:20") AS Time;
SELECT MICROSECOND("2019-06-15 13:15:20") AS Time;
SELECT TIMEDIFF("18:10:11","13:15:20") AS Time;
SELECT TIMEDIFF("14:10:11","13:15:20") AS Time;
SELECT ADDTIME("2019-06-15 05:30:20.000001","5:5.000003") AS Time;
SELECT ADDTIME("2019-06-15 05:30:20.000001","2:10:5.000003") AS Time;
SELECT ADDTIME("2019-06-15 05:30:20.000001","5 2:10:5.000003") AS Time;
SELECT SUBTIME("2019-06-15 05:30:20.000001","5 2:10:5.000003") AS Time;
SELECT MAKETIME(16,15,20) AS Time;
SELECT TIMESTAMP("2019-06-15","13:15:20") AS Time;
SELECT TIME_FORMAT("13:15:20","%H") AS Time;
SELECT TIME_FORMAT("13:15:20","%H %i %s") AS Time;
SELECT TIME_FORMAT("13:15:20","%H-%i-%s") AS Time;
SELECT TIME_FORMAT("13:15:20","%H-%i-%s %p") AS Time;
SELECT SEC_TO_TIME("1") AS Time;
SELECT SEC_TO_TIME("5454") AS Time;
/* 36 - ALTER */
-- create table "students" and insert records --
CREATE TABLE students(
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
percentage INT NOT NULL,
dob DATE NOT NULL,
age INT NOT NULL,
gender VARCHAR(1) NOT NULL,
city INT NOT NULL,
courses INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (city) REFERENCES City (cid),
FOREIGN KEY (courses) REFERENCES Courses (course_id)
);
INSERT INTO students(id,name,percentage,dob,age,gender,city,courses)
VALUES
(1,"Ram Kumar","45","2000-05-10","19","M",1,1),
(2,"Sarita Kumari","85","1997-02-03","22","F",2,2),
(3,"Salman Khan","29","1999-11-12","20","M",1,1),
(4,"Juhi Chawla","47","2001-07-16","18","F",3,1),
(5,"Anil Kapoor","74","1997-01-03","22","M",1,3),
(6,"John Abraham","64","1998-08-10","21","M",2,2),
(7,"Shahid Kapoor","62","1999-12-08","20","M",1,3);
-- create table "courses" and insert records --
CREATE TABLE courses(
course_id INT NOT NULL AUTO_INCREMENT,
course_name VARCHAR(50) NOT NULL,
PRIMARY KEY (course_id)
);
INSERT INTO courses(course_name)
VALUES('Btech'),
('BCA'),
('MBA');
-- ALTER Examples --
ALTER TABLE students
ADD Email varchar(255);
ALTER TABLE students
MODIFY Email varchar(255)
AFTER name;
ALTER TABLE students
MODIFY Email INT(10);
ALTER TABLE students
ADD UNIQUE (Email);
ALTER TABLE students
CHANGE Email Email_id carchar(255);
ALTER TABLE students
DROP COLUMN Email_id;
ALTER TABLE students
RENAME studentss;
ALTER TABLE studentss
RENAME students;
ALTER TABLE courses
AUTO_INCREMENT = 4;
/* 37 - DROP & TRUNCATE */
-- create table "students" and insert records --
CREATE TABLE students(
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
age INT NOT NULL,
gender VARCHAR(1) NOT NULL,
city INT NOT NULL,
courses INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (city) REFERENCES City (cid),
FOREIGN KEY (courses) REFERENCES Courses (course_id)
);
INSERT INTO students(id,name,age,gender,city,courses)
VALUES
(1,"Ram Kumar","19","M",1,1),
(2,"Sarita Kumari","22","F",2,2),
(3,"Salman Khan","20","M",1,1),
(4,"Juhi Chawla","18","F",3,3),
(5,"Anil Kapoor","22","M",1,3),
(6,"John Abraham","21","M",2,2),
(7,"Shahid Kapoor","20","M",1,1);
-- create table "city" and insert records --
CREATE TABLE city(
cid INT NOT NULL AUTO_INCREMENT,
cityname VARCHAR(50) NOT NULL,
PRIMARY KEY (cid)
);
INSERT INTO city(cityname)
VALUES('Agra'),
('Delhi'),
('Bhopal'),
('Jaipur'),
('Noida');
-- create table "courses" and insert records --
CREATE TABLE courses(
course_id INT NOT NULL AUTO_INCREMENT,
course_name VARCHAR(50) NOT NULL,
PRIMARY KEY (course_id)
);
INSERT INTO courses(course_name)
VALUES('Btech'),
('BCA'),
('MBA')
('BA')
('BCOM');
-- DROP & TRUNCATE --
TRUNCATE TABLE courses;
DROP TABLE courses;
/* 38 - VIEW */
--create table "students" and insert records --
CREATE TABLE students(
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
percentage INT NOT NULL,
dob DATE NOT NULL,
age INT NOT NULL,
gender VARCHAR(1) NOT NULL,
city INT NOT NULL,
courses INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (city) REFERENCES City (cid),
FOREIGN KEY (courses) REFERENCES Courses (course_id)
);
INSERT INTO students(id,name,percentage,dob,age,gender,city,courses)
VALUES
(1,"Ram Kumar","45","2000-05-10","19","M",1,1),
(2,"Sarita Kumari","85","1997-02-03","22","F",2,2),
(3,"Salman Khan","29","1999-11-12","20","M",1,1),
(4,"Juhi Chawla","47","2001-07-16","18","F",3,1),
(5,"Anil Kapoor","74","1997-01-03","22","M",1,3),
(6,"John Abraham","64","1998-08-10","21","M",2,2),
(7,"Shahid Kapoor","62","1999-12-08","20","M",1,3);
-- create table "courses" and insert records --
CREATE TABLE courses(
course_id INT NOT NULL AUTO_INCREMENT,
course_name VARCHAR(50) NOT NULL,
PRIMARY KEY (course_id)
);
INSERT INTO courses(course_name)
VALUES('Btech'),
('BCA'),
('MBA');
-- create table "city" and insert records --
CREATE TABLE city(
cid INT NOT NULL AUTO_INCREMENT,
cityname VARCHAR(50) NOT NULL,
PRIMARY KEY (cid)
);
INSERT INTO city(cityname)
VALUES('Agra'),
('Delhi'),
('Bhopal'),
('Jaipur'),
('Noida');
-- VIEW Examples --
-- Create view --
CREATE VIEW studentdata
AS
SELECT id,name,course_name FROM students s
INNER JOIN courses c ON s.courses = c.course_id;
-- show view --
SELECT * FROM studentdata;
-- Alter view --
ALTER VIEW studentdata
AS
SELECT id,name,course_name,cityname FROM students s
INNER JOIN courses c ON s.courses = c.course_id
INNER JOIN city ci ON s.city = ci.cid;
-- Another way to alter --
CREATE OR REPLACE VIEW studentdata
AS
SELECT id,name,course_name,cityname FROM students s
INNER JOIN courses c ON s.courses = c.course_id
INNER JOIN city ci ON s.city = ci.cid;
-- rename view --
RENAME TABLE studentdata
TO studentcourse;
-- show view --
SELECT * FROM studentcourse;
-- Delete/Drop view --
DROP VIEW studentcourse;
/* 39 - important queries */
-- show weather a column is auto-incremented or not */
SHOW COLUMNS FROM your_table_name LIKE 'your_column_name';
--If the column is auto-incremented, you'll see auto_increment in the Extra column.
-- make existing column, auto-incremented
ALTER TABLE your_table_name
MODIFY COLUMN book_id INT AUTO_INCREMENT PRIMARY KEY;
-- change existing table name by another name
RENAME TABLE current_table_name TO new_table_name;
-- change existing column name by column name
ALTER TABLE table_name
CHANGE old_column_name new_column_name data_type;
-- delete the foreign key from a COLUMN
ALTER TABLE record
DROP FOREIGN KEY your_foreign_key_name;
--Replace your_foreign_key_name with the actual name of your foreign key constraint.
If you don't know the name of the foreign key, you can usually find it by querying the information_schema database:
SELECT constraint_name
FROM information_schema.key_column_usage
WHERE table_name = 'record' AND column_name = 'record_state_i';
-- above will show the name and type of constraint ( in my case, constraint_name = record_ibfk_2 )
-- for remove the constraint
ALTER TABLE record
DROP FOREIGN KEY your_foreign_key_name;
-- find the column ,if it has some foreign key constraint or not */
SELECT
COLUMN_NAME,
CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
TABLE_SCHEMA = 'kbpro' AND
TABLE_NAME = 'record' AND
COLUMN_NAME = 'record_gword_i';
-- in my case the result is as below
-- COLUMN_NAME CONSTRAINT_NAME REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME
-- record_gword_i record_ibfk_2 gword gword_id
-- add an column to existing table */
-- syntax
ALTER TABLE database-name.column-name
ADD COLUMN new-column-name data-type;
-- Example
ALTER TABLE kbpro.record
ADD COLUMN record_user_i INT;
-- add foregin key to exsisting column */
-- syntax
ALTER TABLE table-1
ADD FOREIGN KEY (column-name-of-table-1) REFERENCES table-2 (column-name-of-table-2);
-- Example
ALTER TABLE Student
ADD FOREIGN KEY (City) REFERENCES City(Cid);
-- ADD MULTIPLE JOIN TO SAME TABLE */
$sql = "SELECT *
FROM
$tableName
INNER JOIN
state ON $tableName.edustate = state.state_id
INNER JOIN
class ON $tableName.educlass = class.class_id
INNER JOIN
book ON $tableName.edubook = book.book_id
INNER JOIN
artical ON $tableName.eduartical = artical.artical_id
ORDER BY $columnid DESC LIMIT {$offset},{$limit}";
-- sample books tBLES FOR PRACTICE */
INSERT INTO book (book_n )
VALUE ("In Search of Lost Time by Marcel Proust" ),
("Ulysses by James Joyce" ),
("Don Quixote by Miguel de Cervantes" ),
("One Hundred Years of Solitude by Gabriel Garcia Marquez" ),
("The Great Gatsby by F. Scott Fitzgerald" ),
("Moby Dick by Herman Melville" ),
("War and Peace by Leo Tolstoy" ),
("Hamlet by William Shakespeare" ),
("The Odyssey by Homer" ),
("Madame Bovary by Gustave Flaubert" );
-- sample state */
INSERT INTO state (state_n )
VALUE ("Bihar" ),
("Chhattisgarh" ),
("Goa" ),
("Gujarat" ),
("Haryana" ),
("Himachal Pradesh" ),
("Jharkhand" ),
("Karnataka" ),
("Maharashtra" ),
("Mizoram" );
-- sample class */
INSERT INTO class (class_n )
VALUE ("first" ),
("second" ),
("third" ),
("fourth" ),
("fifth" ),
("sixth" ),
("seventh" ),
("eighth" ),
("ninth" ),
("tenth" );
-- sample artical */
INSERT INTO artical (artical_n )
VALUE ("Cinderella" ),
("Beauty and the Beast." ),
("Rapunzel" ),
("Snow White and the Seven Dwarfs." ),
("Little Red Riding Hood." ),
("Jack and the Beanstalk." ),
("Sleeping Beauty." ),
("Puss in Boots." ),
("Hansel and Gretel" ),
("Frog Prince" );
-- sample groupword */
INSERT INTO gword (gword_n, gword_Pid, gword_cid )
VALUE ("cat",1,2 ),
("dog",3,4 ),
("abb",3,5 ),
("add",4,4 ),
("app",2,2 ),
("bee",5,34 ),
("cab",22,43 ),
("ass",12,23 ),
("mic",22,52 ),
("boat",31,36 );
