Some basic SQL statement exercises as well as your own written SQL statements, what is wrong with welcome guidance.

--One, single-table query practice
--1, enquiry < student information Form, check all basic information of student "Zhang San"
SELECT * from a_studentinfo WHERE sname = ' Zhang San ';

--2, enquiry < student information Form, check the basic information of students "Zhang San" and "John Doe"
SELECT *
From A_studentinfo
WHERE sname = ' Zhang San '
OR sname = ' John Doe ';

--3, enquiry < student information Form, check the basic information of students with the surname "Zhang"
SELECT * from A_studentinfo WHERE sname like ' Zhang% ';

--4, enquiry < student information Form, check the basic information of students whose names contain the word "four"
SELECT * from A_studentinfo WHERE sname like '% four ';

--5, enquiry < Student information Form, query name length is three words, surname "Li", and the last word is "strong" all student information.
SELECT * from A_studentinfo WHERE sname like '% li _ strong% ';

--6, enquiry < student information Form, check the basic information of the student whose surname is "Zhang" or "Li".
SELECT *
From A_studentinfo
WHERE sname like ' Zhang% '
Or sname like ' li% ';

--7, enquiry < student information Form, check the surname "Zhang" and "the province" is "Beijing" Student Information
SELECT *
From A_studentinfo
WHERE sname like ' Zhang% '
and province = ' Beijing ';

--8, enquiry < student information Form, check the "province" is the "Beijing", "Xinjiang", "Shandong" or "Shanghai" Students information

SELECT *
From A_studentinfo
WHERE province = ' Beijing '
OR province = ' Shanghai '
OR province = ' Xinjiang '
OR province = ' Shandong ';

--9, enquiry < student information Form, check the surname "Zhang", but "the province" is not "Beijing" Student Information
SELECT *
From A_studentinfo
WHERE province <> ' Beijing '
and sname like ' Zhang% ';

--10, enquiry < Student information Form, query all student information, and according to the "gender" sort, the same sex in the case according to "the province" sort, the province of the same situation and then according to "class" sort
SELECT *
From A_studentinfo
ORDER by sex, province, CLASS

--11, enquiry < Student information Form, and find out which of the different provinces the existing students are from
SELECT Province
From A_studentinfo
GROUP by province

--12, Enquiry < student elective information form; Check the student's number, course number and score for the students who did not fill in the results
SELECT Sno, CNO, score
From A_studentcourse
WHERE score is NULL

--13, Enquiry < student elective information form, check the information of all students who have completed their grades, and sort them from high to low according to "score".
SELECT Sno, CNO, score
From A_studentcourse
WHERE score is not NULL
ORDER by Score DESC

--second, the polymerization function exercises
--1, statistics < Student information table, statistics of the total number of students
SELECT COUNT (SNO)
From A_studentinfo;





--2, statistics < student information table, how many students with a statistical age greater than 20 years
SELECT COUNT (Sno) from A_studentinfo WHERE 2016-yearofbirth > 30;

--3, statistics < student information Form; Number of students enrolled from 1980 to 1982
SELECT COUNT (SNO)
From A_studentinfo
WHERE enrollment between to_date (' + ', ' yyyy ') and
To_date (' n ', ' yyyy ');

--4, Statistics < student elective information table, average scores of students with the statistics number "S001"
SELECT Sno, AVG (Score)
From A_studentcourse
GROUP by Sno
Having sno = ' S001 ';

--5, Statistics < student elective information table, the total number of students in the statistics "S001"
SELECT Sno, SUM (Score)
From A_studentcourse
GROUP by Sno
Having sno = ' S001 ';

--6, Statistics < student elective information table, the highest grade of the course with the course number "C001"
SELECT CNO, MAX (score)
From A_studentcourse
GROUP by CNO
Having cno = ' C001 ';

--7, statistics < student information Form; Find out what the maximum age is for all students
SELECT 2016-min (Yearofbirth)
From A_studentinfo

--Three, group query practice
--1, Statistics < student elective information table, statistics of the number of electives per course
SELECT COUNT (Sno), CNO
From A_studentcourse
GROUP by CNO
ORDER by CNO ASC;


--2, Statistics < student elective information table, statistics of each student's total
SELECT SUM (Score), CNO from A_studentcourse GROUP by CNO ORDER by CNO ASC;
--3, statistics < Student information tables, statistics on the number of students per gender in each class, sorted by class
SELECT COUNT (Sno), sex, CLASS
From A_studentinfo
GROUP by CLASS, sex
ORDER by CLASS ASC;
--4, Statistics < student elective information table, statistics of average scores per course, sorted by grades in descending order
SELECT AVG (Score) s, CNO from A_studentcourse GROUP by CNO ORDER by S DESC;
--5, Statistics < student elective information form, showing students with two or more courses failing the student's number
SELECT Sno
From A_studentcourse
WHERE Score < 60
GROUP by Sno
Having COUNT (SNO) > 2
--6, statistics < Student information table, statistics of the maximum age in each class
SELECT MAX (2016-yearofbirth)
From A_studentinfo
GROUP by CLASS

--Four, nested query practice
--1, using sub-query to achieve, query elective "advanced mathematics" class of all students total
SELECT SUM (Score)
From A_studentcourse
WHERE Sno in
(SELECT Sno from a_courseinfo WHERE subject = ' advanced mathematics ');

--2, using sub-query to achieve, statistics < students elective information table, showing the student number of "S001" students in their various subjects, the highest score of the corresponding course number and score
--thinking: If the student has two courses with a maximum score of 100, what will be the result of the inquiry?
SELECT CNO, Score
From A_studentcourse
Where score = (SELECT MAX (score) from A_studentcourse WHERE sno = ' S001 ')
and sno = ' S001 ';
--3, using sub-query to achieve, query 2 class elective "database technology" class of all students ' achievements and
SELECT SUM (Score)
From A_studentcourse
Where Sno in (SELECT Sno from a_studentinfo WHERE CLASS = 2)
and CNO = (SELECT cno from a_courseinfo WHERE subject = ' database technology ');
--4, with sub-query implementation, Query 3 class "Zhang San" students "test management" results
SELECT Score
From A_studentcourse
where cno = (SELECT cno from a_courseinfo WHERE subject = ' Test Management ')
and Sno = (SELECT sno
From A_studentinfo
WHERE CLASS = 3
and sname = ' Zhang San ');
--Five, join query practice
--1, query "Zhang San" of the examination results, required to display the name, course number and results
SELECT S.sname, Sc.cno, Sc.score
From A_studentinfo S
JOIN A_studentcourse SC
On s.sno = Sc.sno
WHERE s.sname = ' Zhang San ';
--2, query "Zhang San" of the examination results, which department did not record examination results, required to display the name, course number and results
SELECT S.sname, Sc.cno, Sc.score
From A_studentinfo S
JOIN A_studentcourse SC
On s.sno = Sc.sno
WHERE s.sname = ' Zhang San '
and Sc.score is NULL;
--3, query "Zhang San" of the course results, require display name, course name and results
SELECT S.sname, C.subject, Sc.score
From A_studentinfo S
JOIN A_studentcourse SC
On s.sno = Sc.sno
JOIN A_courseinfo C
On sc.cno = C.cno
WHERE s.sname = ' Zhang San ';
--4, Query 3 class "Zhang San" of "test management" results, required to display names, scores
SELECT S.sname, Sc.score
From A_studentinfo S
JOIN A_studentcourse SC
On sc.sno = S.sno
JOIN A_courseinfo C
On sc.cno = C.cno
WHERE c.subject = ' Test Management '
and S.sname = ' Zhang San ';
--5, all classes of boys enrolled before 2000, the average grade of each class test
SELECT S.class, Sc.cno, AVG (Sc.score)
From A_studentinfo S
JOIN A_studentcourse SC
On sc.sno = S.sno
WHERE s.sex = ' Male '
and S.enrollment < To_date (' a ', ' yyyy ')
GROUP by Sc.cno, S.class;

--Six, outer join query
--Query "Li Strong" all the course results, and show the number, name, course number and results, no record of the academic number includes: (' S009 ', ' S010 ', ' S011 ')
--1, using left Join
SELECT S.sno, S.sname, Sc.score
From A_studentinfo S
Left JOIN A_studentcourse SC
On sc.sno = S.sno
WHERE s.sname = ' Lee Strong '
--2, using RIGHT Join
SELECT S.sno, S.sname, Sc.score
From A_studentcourse SC
Right JOIN A_studentinfo S
On sc.sno = S.sno
WHERE s.sname = ' Lee Strong '

SELECT * from A_courseinfo;


--Seven. Supplementary improvement
--1, query "Zhang San" than "Wang San" early admission years
Select (select S.enrollment from A_studentinfo s WHERE sname = ' Zhang San ')-
Enrollment)/365
From A_studentinfo S
WHERE sname = ' Wang San ';
--2, the age of the class and the class in which each person is older than 20 years, and the age of each class is more than 60 years old.
SELECT CLASS, SUM (2016-yearofbirth)
From A_studentinfo
WHERE (2016-yearofbirth) > 20
GROUP by CLASS
Having SUM (2016-yearofbirth) > 60

/*3, calculate the amount of inventory remaining for each product
Table 1, for product stock list, product name Name[char (10)], product quantity Amount[int]
Table 2, Product shipping list, product name Name[char (10)], number of products Amount[int]
Business logic: Table 1 Storage of the total quantity of products, table 2 stores each product per shipment, such as product a purchase is 100, shipped 3 times, each time 10, 20, 30, then a product inventory is 40

Table A1 | Table A2
-------------------|---------------------------
Name Amount | Name Amount
A 100 | A 10
B 100 | A 20
| A 30
| B 10
| B 40*/

CREATE TABLE Aa1 (name CHAR (ten), amout number) CREATE TABLE AA2 (name
CHAR (10),
Amout number)

INSERT
Into Aa1 VALUES (' A ', 100);

INSERT into Aa2 VALUES (' A ', 10);
INSERT into Aa2 VALUES (' a ', 20);
INSERT into Aa2 VALUES (' a ', 30);

SELECT DISTINCT A.name, (A.amout-(SELECT SUM (amout) from AA2) remaining
From Aa1 A
JOIN Aa2 b
on a.name = B.name;

Some basic SQL statement exercises as well as your own written SQL statements, what is wrong with welcome guidance.