Scribd
Upload
2 views9 pages

MySQL Queries (1)

The document provides a comprehensive overview of MySQL aggregate functions, including COUNT, SUM, MIN, MAX, and AVG, along with examples of creating tables and inserting records. It also covers GROUP BY, HAVING, nested queries, and the use of UNION and UNION ALL, as well as creating and managing views in MySQL. The examples illustrate how to manipulate and retrieve data from multiple tables using SQL commands.

Uploaded by

moorlyre
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
2 views9 pages

MySQL Queries (1)

The document provides a comprehensive overview of MySQL aggregate functions, including COUNT, SUM, MIN, MAX, and AVG, along with examples of creating tables and inserting records. It also covers GROUP BY, HAVING, nested queries, and the use of UNION and UNION ALL, as well as creating and managing views in MySQL. The examples illustrate how to manipulate and retrieve data from multiple tables using SQL commands.

Uploaded by

moorlyre
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

(Aggregate Functions)

MySQL Count, Sum, Min, Max, Avg


create table
CREATE TABLE personal(
id INT NOT NULL UNIQUE,
name VARCHAR(50) NOT NULL,
percentage INT 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"
);

insert records
INSERT INTO personal(id,name,percentage,age,gender,phone,city)
VALUES
(1,"Ram Kumar","45","13","M","4022155","Agra"),
(2,"Sarita Kumari","56","21","F","4034421","Delhi"),
(3,"Salman Khan","62","20","M","4056221","Agra"),
(4,"Juhi Chawla","47","18","F","4022155","Bhopal"),
(5,"Anil Kapoor","74","22","M","4025221","Agra"),
(6,"John Abraham","64","21","M","4056776","Delhi"),
(7,"Shahid Kapoor","52","20","M","4022784","Agra");

COUNT
SELECT COUNT(name) FROM personal;

SELECT COUNT(*) FROM personal;

SELECT COUNT(DISTINCT city) FROM personal;

SELECT COUNT(DISTINCT city) AS Count FROM personal;

MAX
SELECT MAX(percentage) AS Percentage FROM personal;

MIN
SELECT MIN(percentage) AS Percentage FROM personal;

SELECT MIN(percentage) AS Percentage,name,city FROM personal;

SUM
SELECT SUM(percentage) AS Total FROM personal;

AVG
SELECT AVG(percentage) AS Average FROM personal;
MySQL GROUP BY & HAVING
create table "personal" and insert records
CREATE TABLE personal(
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 personal(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);

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");
GROUP BY
SELECT city,COUNT(city)
FROM personal
GROUP BY city;

SELECT [Link],COUNT([Link])
FROM personal p INNER JOIN city c
ON [Link] = [Link]
GROUP BY city;

SELECT [Link],COUNT([Link]) AS Total


FROM personal p INNER JOIN city c
ON [Link] = [Link]
GROUP BY city;

SELECT [Link],COUNT([Link]) AS Total


FROM personal p INNER JOIN city c
ON [Link] = [Link]
WHERE [Link] >= 20
GROUP BY city;

SELECT [Link],COUNT([Link]) AS Total


FROM personal p INNER JOIN city c
ON [Link] = [Link]
GROUP BY city
ORDER BY COUNT([Link]);

SELECT [Link],COUNT([Link]) AS Total


FROM personal p INNER JOIN city c
ON [Link] = [Link]
GROUP BY city
ORDER BY COUNT([Link]) DESC;

HAVING
SELECT [Link],COUNT([Link]) AS Total
FROM personal p INNER JOIN city c
ON [Link] = [Link]
GROUP BY city
HAVING COUNT([Link]) > 3
ORDER BY COUNT([Link]) DESC;
Nested Query
create table "personal" and insert records
CREATE TABLE personal(
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 personal(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);

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");

use SubQuery
SELECT name FROM personal
WHERE courses = (SELECT course_id FROM courses WHERE course_name = "MBA");

SELECT course_id FROM courses WHERE course_name = "MBA";

SELECT name FROM personal


WHERE courses IN (SELECT course_id FROM courses WHERE course_name IN ("MBA","Btech"));

Exists & Not Exists


SELECT name FROM personal
WHERE EXISTS (SELECT course_id FROM courses WHERE course_name IN ("MBA"));
SELECT name FROM personal
WHERE EXISTS (SELECT course_id FROM courses WHERE course_name IN ("Mtech"));

SELECT name FROM personal


WHERE NOT EXISTS (SELECT course_id FROM courses WHERE course_name IN ("Mtech"));

SELECT name FROM personal


WHERE NOT EXISTS (SELECT course_id FROM courses WHERE course_name IN ("MBA"));

Union and All Union

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 [Link],[Link], [Link] FROM students s


INNER JOIN city c ON [Link] = [Link]
WHERE [Link] = "Delhi"
UNION ALL
SELECT [Link],[Link],[Link] FROM lecturers l
INNER JOIN city ci ON [Link] = [Link]
WHERE [Link] = "Delhi";
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 [Link] = 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 [Link] = c.course_id
INNER JOIN city ci ON [Link] = [Link];
/* Another way to alter */
CREATE OR REPLACE VIEW studentdata
AS
SELECT id,name,course_name,cityname FROM students s
INNER JOIN courses c ON [Link] = c.course_id
INNER JOIN city ci ON [Link] = [Link];

/* rename view */
RENAME VIEW studentdata
TO studentcourse;

/* show view */
SELECT * FROM studentcourse;

/* Delete/Drop view */
DROP VIEW studentcourse;

You might also like