Learn Mysql Part II
Click here to visit MySql Part I
Filter by major and name
CREATE TABLE student(
student_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) ,
major VARCHAR(20)
);
INSERT INTO student(name,major) VALUES("Alan Walder","Computer Science");
INSERT INTO student(name, major) VALUES("Vin", "Biology");
INSERT INTO student(name, major) VALUES("Michael", "Physics");
INSERT INTO student(name, major) VALUES("Amy", "Com Sc");
SELECT name, major FROM student WHERE major = "Biology" OR name = "Amy";
MySQL Operator.
< ------------- Less than
> -------------Greater than
<= -----------Less than equal to
>= -----------Greater than equal to
= -------------Equal to
< > -----------Not Equal to
AND ----------and operator
OR-------------or operator
* --------------- identify all from the table.
Show all the row those are not Physics
CREATE TABLE student(
student_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) ,
major VARCHAR(20)
);
INSERT INTO student(name,major) VALUES("Alan Walder","Computer Science");
INSERT INTO student(name, major) VALUES("Vin", "Biology");
INSERT INTO student(name, major) VALUES("Michael", "Physics");
INSERT INTO student(name, major) VALUES("Amy", "Com Sc");
-- show all the row that are not Physics
SELECT * FROM student WHERE major <> "Physics";
Show student id that are less than 3
CREATE TABLE student(
student_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) ,
major VARCHAR(20)
);
INSERT INTO student(name,major) VALUES("Alan Walder","Computer Science");
INSERT INTO student(name, major) VALUES("Vin", "Biology");
INSERT INTO student(name, major) VALUES("Michael", "Physics");
INSERT INTO student(name, major) VALUES("Amy", "Com Sc");
-- Show student id that are less than 3
SELECT * FROM student WHERE student_id < 3;
Show student that are less than equal to 3 and name not equal to Vin
CREATE TABLE student(
student_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) ,
major VARCHAR(20)
);
INSERT INTO student(name,major) VALUES("Alan Walder","Computer Science");
INSERT INTO student(name, major) VALUES("Vin", "Biology");
INSERT INTO student(name, major) VALUES("Michael", "Physics");
INSERT INTO student(name, major) VALUES("Amy", "Com Sc");
-- Show student that are less than equal to 3 and name not equal to Vin
SELECT * FROM student WHERE student_id <= 3 AND name <> "Vin";
-- Show student that are less than equal to 3 and name not equal to Vin
SELECT * FROM student WHERE student_id <= 3 AND name <> "Vin";
Show particular name from the table
CREATE TABLE student(
student_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) ,
major VARCHAR(20)
);
INSERT INTO student(name,major) VALUES("Alan Walder","Computer Science");
INSERT INTO student(name, major) VALUES("Vin", "Biology");
INSERT INTO student(name, major) VALUES("Michael", "Physics");
INSERT INTO student(name, major) VALUES("Amy", "Com Sc");
-- show particular name
SELECT * FROM student WHERE name IN ("Vin","Amy","Alan Walder");
-- show particular name
SELECT * FROM student WHERE name IN ("Vin","Amy","Alan Walder");
Show particular major from the table.
CREATE TABLE student(
student_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) ,
major VARCHAR(20)
);
INSERT INTO student(name,major) VALUES("Alan Walder","Computer Science");
INSERT INTO student(name, major) VALUES("Vin", "Biology");
INSERT INTO student(name, major) VALUES("Michael", "Physics");
INSERT INTO student(name, major) VALUES("Amy", "Com Sc");
-- show particular major
SELECT * FROM student WHERE major IN ("Biology","Physics");
-- show particular major
SELECT * FROM student WHERE major IN ("Biology","Physics");
Show particular major where student id is greater than 2
CREATE TABLE student(
student_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) ,
major VARCHAR(20)
);
INSERT INTO student(name,major) VALUES("Alan Walder","Computer Science");
INSERT INTO student(name, major) VALUES("Vin", "Biology");
INSERT INTO student(name, major) VALUES("Michael", "Physics");
INSERT INTO student(name, major) VALUES("Amy", "Com Sc");
-- show particular major where student id is greater than 2
SELECT * FROM student WHERE major IN ("Biology","Physics") AND student_id > 2;
-- show particular major where student id is greater than 2
SELECT * FROM student WHERE major IN ("Biology","Physics") AND student_id > 2;
Company Database Into
Creating Company Database
Creating employee table
CREATE TABLE employee(
emp_id INT PRIMARY KEY,
first_name VARCHAR(40),
last_name VARCHAR(40),
birth_day DATE,
sex VARCHAR(1),
salary INT,
super_id INT,
branch_id INT
);
Creating branch table
CREATE TABLE branch(
branch_id INT PRIMARY KEY,
branch_name VARCHAR(40),
mgr_id INT,
mgr_start_date DATE,
FOREIGN KEY(mgr_id) REFERENCES employee(emp_id) ON DELETE SET NULL
);
Add branch id as a foreign key to the employee table.
ALTER TABLE employee
ADD FOREIGN KEY(branch_id)
REFERENCES branch(branch_id)
ON DELETE SET NULL;
ALTER TABLE employee
ADD FOREIGN KEY(super_id)
REFERENCES employee(emp_id)
ON DELETE SET NULL;
Creating client table
CREATE TABLE client(
client_id INT PRIMARY KEY,
client_name VARCHAR(40),
branch_id INT,
FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE SET NULL
);
Create works_with table
CREATE TABLE works_with(
emp_id INT,
client_id INT,
total_sales INT,
PRIMARY KEY(emp_id, client_id),
FOREIGN KEY(emp_id) REFERENCES employee(emp_id) ON DELETE CASCADE,
FOREIGN KEY(client_id) REFERENCES client(client_id) ON DELETE CASCADE
);
Create branch supplier table
CREATE TABLE branch_supplier (
branch_id INT,
supplier_name VARCHAR(40),
supply_type VARCHAR(40),
PRIMARY KEY(branch_id, supplier_name),
FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE CASCADE
);
All the employee inserted into the corporate branch
-- Corporate
INSERT INTO employee VALUES(100, 'David', "Wallace", '1967-11-17', 'M', 250000,NULL,NULL);
INSERT INTO branch VALUES(1,'Corporate',100,'2006-02-09');
UPDATE employee SET branch_id = 1 WHERE emp_id = 100;
INSERT INTO employee VALUES(101, 'Jan', 'Levinson','1961-05-11','F',11000,100,1);
All the employee inserted into the Scranton branch
-- Scranton
INSERT INTO employee VALUES(102, 'Michael', 'Scott', '1964-03-15', 'M', 75000, 100, NULL);
INSERT INTO branch VALUES(2, 'Scranton', 102, '1992-04-06');
UPDATE employee
SET branch_id = 2
WHERE emp_id = 102;
INSERT INTO employee VALUES(103, 'Angela', 'Martin', '1971-06-25', 'F', 63000, 102, 2);
INSERT INTO employee VALUES(104, 'Kelly', 'Kapoor', '1980-02-05', 'F', 55000, 102, 2);
INSERT INTO employee VALUES(105, 'Stanley', 'Hudson', '1958-02-19', 'M', 69000, 102, 2);
All the employee inserted into the stamford
-- Stamford
INSERT INTO employee VALUES(106, 'Josh', 'Porter', '1969-09-05', 'M', 78000, 100, NULL);
INSERT INTO branch VALUES(3, 'Stamford', 106, '1998-02-13');
UPDATE employee
SET branch_id = 3
WHERE emp_id = 106;
INSERT INTO employee VALUES(107, 'Andy', 'Bernard', '1973-07-22', 'M', 65000, 106, 3);
INSERT INTO employee VALUES(108, 'Jim', 'Halpert', '1978-10-01', 'M', 71000, 106, 3);
inserted into Branch Supplier
-- BRANCH SUPPLIER
INSERT INTO branch_supplier VALUES(2, 'Hammer Mill', 'Paper');
INSERT INTO branch_supplier VALUES(2, 'Uni-ball', 'Writing Utensils');
INSERT INTO branch_supplier VALUES(3, 'Patriot Paper', 'Paper');
INSERT INTO branch_supplier VALUES(2, 'J.T. Forms & Labels', 'Custom Forms');
INSERT INTO branch_supplier VALUES(3, 'Uni-ball', 'Writing Utensils');
INSERT INTO branch_supplier VALUES(3, 'Hammer Mill', 'Paper');
INSERT INTO branch_supplier VALUES(3, 'Stamford Lables', 'Custom Forms');
inserted into client table
-- CLIENT
INSERT INTO client VALUES(400, 'Dunmore Highschool', 2);
INSERT INTO client VALUES(401, 'Lackawana Country', 2);
INSERT INTO client VALUES(402, 'FedEx', 3);
INSERT INTO client VALUES(403, 'John Daly Law, LLC', 3);
INSERT INTO client VALUES(404, 'Scranton Whitepages', 2);
INSERT INTO client VALUES(405, 'Times Newspaper', 3);
INSERT INTO client VALUES(406, 'FedEx', 2);
inserted into works_with table
-- WORKS_WITH
INSERT INTO works_with VALUES(105, 400, 55000);
INSERT INTO works_with VALUES(102, 401, 267000);
INSERT INTO works_with VALUES(108, 402, 22500);
INSERT INTO works_with VALUES(107, 403, 5000);
INSERT INTO works_with VALUES(108, 403, 12000);
INSERT INTO works_with VALUES(105, 404, 33000);
INSERT INTO works_with VALUES(107, 405, 26000);
INSERT INTO works_with VALUES(102, 406, 15000);
INSERT INTO works_with VALUES(105, 406, 130000);
More Basics Queries
Find all employee
-- find all employee
SELECT * FROM employee;
Find all clients
-- find all clients
SELECT * FROM client;
Find all employees ordered by salary
-- Find all employees ordered by salary
SELECT * FROM employee ORDER BY salary;
Find all employees ordered by salary with Descending order.
-- Find all employees ordered by salary with Descending order.
SELECT * FROM employee ORDER BY salary DESC;
Find all employees ordered by sex then name.
-- Find all employees ordered by sex then name.
SELECT * FROM employee ORDER BY sex, first_name, last_name;
Find the first 5 employee in the table
-- Find the first 5 employee in the table
SELECT * FROM employee LIMIT 5;
Find the first and last names of all employees
-- Find the first and last names of all employees
SELECT first_name, last_name FROM employee;
Find the forename and surnames names of all employees
-- Find the forename and surnames names of all employees
SELECT first_name, last_name FROM employee;
Find the forename and surnames names of all employees
-- Find the forename and surnames names of all employees
SELECT first_name AS forename, last_name AS surname FROM employee;
Find out all the different genders
-- Find out all the different genders
SELECT DISTINCT sex FROM employee;
Find out all the different branch id
-- Find out all the different branch id
SELECT DISTINCT branch_id FROM employee;
SQL Functions
Count: Find how many employee, supervisors, manager and more using count function.
Find the number of emplyees
-- Find the number of emplyees
SELECT COUNT(emp_id) FROM employee;
Find how many employees are supervisor
-- Find how many employees are supervisor
SELECT COUNT(super_id) FROM employee;
Find the number of female employees born after 1970
-- Find the number of female employees born after 1970
SELECT COUNT(emp_id) FROM employee WHERE sex = 'F' AND birth_day > '1970-01-01';
Find the average of all employee's salaries
-- Find the average of all employee's salaries
SELECT AVG(salary) FROM employee;
Find the average salary from all the employee's who are male.
-- Find the average salary from all the employee's who are male.
SELECT AVG(salary) FROM employee WHERE sex = 'M';
Find the sum of all employee salary
-- Find the sum of all employee's salary
SELECT SUM(salary) FROM employee;
Find how many Male and Female into the company. (Aggregation)
-- Find how many Male and Female into the company.
-- This is called agregation
SELECT COUNT(sex), sex FROM employee GROUP BY sex;
Find the total sales of each salesman
-- Find the total sales of each salesman
SELECT SUM(total_sales), emp_id FROM works_with GROUP BY emp_id;
Find how much money each client actually spent with the brand.
-- Find how much money each client actually spent with the brand.
SELECT SUM(total_sales), client_id FROM works_with GROUP BY client_id;
WildCards
WildCard is a way of defining different pattern, that we want to map specific data.
Find any client's who are an LLC
-- Find any client's who are an LLC
SELECT * FROM client WHERE client_name LIKE '%LLC'
% -----> it show any character come after % .
Find any branch suppliers who are in the label business.
-- Find any branch suppliers who are in the label business
SELECT * FROM branch_supplier WHERE supplier_name LIKE '%Label%';
%Label% -----------> it will show any character that match 'Label'
Find any employee born in October.
-- Find any employee born in October
SELECT * FROM employee WHERE birth_day LIKE '____-10%';
_ ---------> Underscore represent only one character.
'____-10%' -------> it give us all the date that end in october.
Find any clients who are schools
-- Find any clients who are schools
SELECT * FROM client WHERE client_name LIKE '%school%';
Union
Union: Union is basically special SQL operator which you can use to combine the result multiple select statement into one.
Find a list of employee and branch names.
-- Find a list of employee and branch names.
SELECT first_name FROM employee
UNION
SELECT branch_name FROM branch;
Find a list of all clients and branch suppliers names .
-- Find a list of all clients and branch suppliers names
SELECT client_name FROM client
UNION
SELECT supplier_name FROM branch_supplier;
Find a list of all clients and branch suppliers names and branch id.
-- Find a list of all clients and branch suppliers names
SELECT client_name, branch_id FROM client
UNION
SELECT supplier_name, branch_id FROM branch_supplier;
Find a list of all money spent or earned by the company.
-- Find a list of all money spent or earned by the company
SELECT salary FROM employee
UNION
SELECT total_sales
FROM works_with;
Join
Join: Join is use to combine rows from two or more tables base on a related column between them.
Join is useful for combining information from different table into a single result to findout specific information between the database.
Find all branches and the names of their managers.
-- Find all branches and the names of their managers.
SELECT employee.emp_id, employee.first_name, branch.branch_name
FROM employee
JOIN branch
ON employee.emp_id = branch.mgr_id;
Nested Queries
Nested Queries: Nested Queries is a basically a queries where we gonna using multiple select statement in order to get specific peace of information.
Find names of all employees who have sold over 30,000 to a client. (showing an error fix it )
-- Find names of all employees who have sold over 30,000 to a client.
SELECT employee.first_name, employee.last_name
FROM employee
WHERE employee.emp_id IN (
SELECT works_with.emp_id FROM works_with WHERE works_with.total_sales > 30000;
);
Find all clients who are handled by the branch that Michael Scott manages. Assume you know Michael's id.
-- Find all clients who are handled by the branch that Michael Scott manages.
Assume you know Michael's id.
SELECT client.client_name FROM client WHERE client.branch_id = (
SELECT branch.branch_id FROM branch WHERE branch.mgr_id = 102
LIMIT 1
);
On Delete
Need to learn On Delete.
Triggers
Triggers: Triggers is basically block of SQL code which we can write. Which will define a certain action that should happen when a certain operation get perform on database.