Summary of sql statements

Exercise 1:

//Create a Category Table:
create table category(
cid int primary key auto_increment,
cname varchar(10),
cdesc varchar(31)
);
insert into category values(null,'Mobile Digital','Electronic product'),
(null,'Shoes, boots, suitcases and bags','Jiangnan Leather Factory'),
(null,'Cigarette and liquor','Erguotou'),
(null,'Yogurt','Woaha'),
(null,'Snacks','Spicy strips');

//Create a merchandise list:
create table product(
pid int primary key auto_increment,
pname varchar(10),
price double,
pdate timestamp.
cno int
);
insert into product values
(null,'millet',998,null,1),
(null,'Hammer',2888,null,1),
(null,'Adivon',99,null,2),
(null,'Old Village Chief',88,null,3),
(null,'Strong wine',35,null,3),
(null,'Jenny Bakery',1,null,4),
(null,'Weilong Spicy Bar',1,null,5),
(null,'Wangwang cake',1,null,5);

Query:
select ...
from...
First execute from to execute select

Table aliases:

select p.pname,p.price from product as p;

Column aliases:

select pname as commodity name, price as commodity price from product;

Check all prices:

select price from product;

Duplicate removal:

select distinct price from product;

select Operational Query: Operate only on query results

select *,price*1.5 as discount from product;

where keyword for conditional query

select * from product where price > 60;

<>: Not equal to standard sql statements
!=: Not equal to, non-standard sql statements

select * from product where price <> 88;

Inquiry price 10 to 100:

select * from product where price > 10 and price <100;

between...and...

select * from product where price between 10 and 100;

Logical operation and or not

like: Fuzzy Query
_ Represents a character
% Represents multiple characters

Find out all the items with the name of the cake

select * from product where pname like '%cake%';

Search for all the bears under the second name

select * from product where pname like '_The bear%';

In gets a value in a range
Find out all the items in Classification ID 1, 4, 5

select * from product where cno in (1,4,5);

Sort query: order by keyword
asc: ascend ascending (default)
desc: descend descending
1. Search all commodities and rank them by price.

select * from product order by price ;
select * from product order by price desc;

2. The query name contains small items, sorted in ascending order of prices.

select * from product where pname like '%Small%' order by price asc;

Aggregation function:
sum(): sum
avg(): average
count(): Statistics
max(): maximum
min(): Minimum

1. Get the sum of commodity prices.

select sum(price) from product;

2. Get the average of commodity prices

select avg(price) from product;

3. Number of goods obtained

select count(*) from product;

Note: where condition cannot be followed by aggregation function
4. Find out all goods whose prices are higher than the average.

select * from product where price >(select avg(price) from product);

Grouping: group by
1. Grouping according to cno field and counting the number of goods after grouping

select cno,count(*) from product group by cno;

2. According to the cno grouping, the average price of each group was calculated by grouping, and the average price of each group was more than 60.

select cno,avg(price)  from product group by cno having avg(price)>60;	

having keywords can aggregate functions after grouping
where keywords cannot aggregate functions before grouping

- Writing order
– S...F...W...G...H...O

- Execution order

F..W..G..H..S..O

from.. where.. group by.. having.. select.. order by

Exercise 2:

Employee Information Sheet

CREATE TABLE emp(
	empno INT,
	ename VARCHAR(50),
	job VARCHAR(50),
	mgr	INT,
	hiredate DATE,
	sal	DECIMAL(7,2),
	comm DECIMAL(7,2),
	deptno INT
) ;

INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
INSERT INTO emp values(7981,'MILLER','CLERK',7788,'1992-01-23',2600,500,20);

Departmental Information Table

CREATE TABLE dept(
	deptno		INT,
	dname		varchar(14),
	loc		varchar(13)
);

INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept values(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept values(30, 'SALES', 'CHICAGO');
INSERT INTO dept values(40, 'OPERATIONS', 'BOSTON');

Basic Query

--Information for all employees
select e.*,d.dname,d.loc from emp e inner join dept d on d.deptno = e.deptno;

--Employee information with salaries greater than or equal to 1000 and less than or equal to 2000
select e.*,d.dname,d.loc from emp e inner join dept d on d.deptno = e.deptno and e.sal>=1000 
and e.sal<=2000;

--Find out all department numbers from the employee list
select distinct e.deptno from emp e;

--Query out the name with A Initial employee information
select e.*,d.dname,d.loc from emp e inner join dept d on d.deptno = e.deptno and e.ename like 'A%';

--Query out the second letter of the name is L Employee Information
select * from emp where ename like '_L%';

--Search for information about employees without bonuses
select * from emp where comm is null;

--Average wages of all employees
select avg(sal) as average wage from emp;

--Total wages of all employees
select sum(sal) as The sum from emp;

--Number of all employees
select count(*) from emp;

--Maximum wage
select max(sal) from emp;

--Minimum wage
select min(sal) from emp;

--Employee Information on Maximum Wage
select * from emp where sal = (select max(sal) from emp)

--Minimum Wage Employee Information
select * from emp where sal = (select min(sal) from emp)

Group query

- Average wages per department
 select deptno,avg(sal) as average wage from emp group by deptno;

Subquery

-- single-row subqueries(> < >= <= = <>)
	-- Search for information about employees whose average salary is higher than Department 10
	select * from emp where sal >(select avg(sal) from emp where  deptno=10);
	
-- Multi-line sub-query(in  not in any all)    >any  >all
	-- Search for information about employees who are paid more than any other employee in Department 10
	select * from emp where sal >(select max(sal) from emp where  deptno=10);
	
-- Multiple-column subqueries(Less practical use)   in
	-- Employee information with the same name as Department 10	
select * from emp where deptno<>10 and (ename,job) in (select ename,job from emp where deptno=10)
		
-- Select Connector Query
	-- Get the employee's name and department's name
	select e.ename , d.dname from emp e,dept d where e.deptno = d.deptno
	
-- from Subsequent Subquery
	-- query emp Manager Information in Table
	select * from emp where job='MANAGER';
	
-- where Connector Query
	-- Information on all employees whose salary is higher than the average salary of Department 10
	select * from emp where sal>(select avg(sal) from emp where deptno=10)
	
-- having Subsequent Subquery
	-- Which departments have their average wages higher than those of Section 30?	
	select deptno, avg(sal) from emp group by deptno having avg(sal)
	 >(select avg(sal) from emp where deptno=30)
	
-- wages>JONES wages
select * from emp where sal >(select sal from emp where ename='JONES')

-- Query and SCOTT Employees in the same department
select * from emp where deptno = (select deptno from emp where ename='SCOTT')

-- Employee information for employees whose wages are higher than those of the owner of Section 30
select * from emp where sal >(select max(sal) from emp where deptno =30)

A Comprehensive Case of SQL Query

--Query out information about employees who are above the average salary of their department
select * from emp e where sal > (select avg(sal) from emp where e.deptno = deptno)

--List the people working in Dallas,People who earn more than the average salary in New York
select * from emp where deptno =(select deptno from dept where loc='DALLAS') 
and sal>(select avg(sal) from emp where deptno=(select deptno from dept where loc='NEW YORK'))

--Find out all the information about the highest-paid employees in each department
select * from emp e where sal =(select max(sal) from emp where deptno=e.deptno)