Keng
First of all, this author 2 years ago to learn the omission of SQL notes, due to take part in the written examination of Tencent, the content of the data agencies and compile and database, just want to change the laptop, I do not want to kill it in the hard drive, feel good, so it has been shared, mainly the first part of a simple introduction of SQL language, The second part of simple practice, is purely similar to the record, easy to understand, I hope to learn the principles of the database students have some help!
Main content:
Review
Simple Practice
Review
We all know that SQL is a structured query language, is the standard language of relational databases, is a comprehensive, powerful and concise and easy to learn, it is set-level data query (Quest), data manipulation (information manipulation), data definition Definition), Data control in one:
The SQL language contains 4 sections:
※ Data Definition Language (DDL), for example: CREATE, DROP, ALTER, and other statements
※ Data Manipulation Language (DML), for example: Insert (insert), UPDATE (modify), delete (delete) statement
※ Data Query Language (DQL), for example: SELECT statement
※ Data Control Language (DCL), such as: GRANT, REVOKE, COMMIT, rollback and other statements
The SQL language includes three main programming language categories: Data definition Language (DDL), Data Manipulation language (DML), and Data Control Language (DCL)
Now let's talk about several basic concepts of SQL:
Basic table: A table of independent existence, a relationship that corresponds to a basic table
Internal mode: The logical structure of the stored file forms the internal schema of the relational database
Views: tables exported from one or several basic tables, he is a virtual table
Simple Practice
Build a database, including "Student table", "Teacher's Table", "Classroom Table", "Professional Table", "Curriculum", "Student Personal Status Table", "score sheet" seven sheets
The following tables are the tables we use for this practice: building tables with SQL statements:
Create a database cc
Copy Code code as follows:
Create DATABASE cc;
Use CC;
Create a student table named Tb_student
Copy Code code as follows:
Tb_student's student table
CREATE TABLE Tb_student (
Stunum Char (7) Primary key,--school number
Stuname Char (8) NOT NULL,--Name
Stusex char (2) Check (Stusex in (' Female ', ' Male ')),--gender
Stubirthday smalldatetime NOT null--student Birth date
Stuspec Char (6) NOT null--professional
Stuscore Numeric (4,1)--admissions results
Stuloan char (2) Check (Stuloan in (' Yes ', ' no ') not NULL,--whether the loan
)
Create a teacher's table named Tb_teacher
Tb_teacher Teacher's Table
Copy Code code as follows:
CREATE TABLE Tb_teacher (
Teacher Code char (7) Primary key,--teacher code
Name Char (8) NOT null--teacher name
Sex char (2) Check (Teasex in (' Female ', ' Male ')),--Teacher sex
Date of birth datetime NOT null--teacher Birthdate
Title char (6),--title
)
Create a classroom table named Tb_renke
Tb_renke's Classroom Table
Copy Code code as follows:
CREATE TABLE Tb_renke (
Course Code char (5),--Course Code
Teacher Code char (7),--Teacher code
)
Create a professional table called Tb_major
Copy Code code as follows:
Tb_major's Professional Watch
Create Tabletb_major (
Professional name char (8) Primary key,--professional name
Owner Char (8) NOT NULL,--owner
)
Create a schedule named Tb_course
Copy Code code as follows:
Tb_course's Timetable
CREATE TABLE Course (
Course Code char (5) primary key,--course Code
Course name char (NOT NULL)--course Name
Zhou Co int,--When Zhou Co
credits int,--credits
)
Create a personal table of students named Tb_studentinfo
Tb_studentinfo Student's personal situation chart
Copy Code code as follows:
CREATE TABLE Tb_studentinfo (
School Number char (7),--School number
Identity card Char Unique,--identity card number
Native Place Char (8),--Native place
Home Address text, home address
Telephone char (30),--Telephone
Specialty text,--Specialty
Reward text,--Rewards
Disposition char (100),--disciplinary
)
Create a score list called Tb_grade
Tb_grade's score sheet
Copy Code code as follows:
CREATE TABLE Tb_grade (
School Number char (7),--School number
Course Code char (5),--Course Code
Peacetime numeric (3,1),--usually
Midterm numeric (3,1),--interim
Final numeric (3,1)--end of term
)
Now insert records into each table
Insert data for student table Tb_student
Copy Code code as follows:
Tb_student
INSERT into tb_student values (' 9607039 ', ' Deng Ying Ying ', ' female ', ' 1978-6-6 ', ' foreign trade ', 666.6, ' yes ');
INSERT into tb_student values (' 9907002 ', ' Nu Wenxian ', ' female ', ' 1981-4-1 ', ' foreign trade ', 641.4, ' yes ');
INSERT into tb_student values (' 9801055 ', ' zhaodong ', ' Male ', ' 1979-11-9 ', ' Chinese ', 450, ' no ');
INSERT into tb_student values (' 9902006 ', ' and tone ', ' female ', ' 1982-6-19 ', ' math ', 487.1, ' no ');
INSERT into tb_student values (' 9704001 ', ' Kemin Min ', ' female ', ' 1978-7-22 ', ' physics ', 463, ' no ');
INSERT into tb_student values (' 9603001 ', ' Shen Qiang ', ' Men ', ' 1978-1-15 ', ' News ', 512, ' yes ');
INSERT into tb_student values (' 9606005 ', ' late big ', ' male ', ' 1976-9-3 ', ' chemistry ', 491.3, ' no ');
Insert into tb_studentvalues (' 9803011 ', ' Ouyang June ', ' female ', ' 1981-8-11 ', ' News ', 526.5, ' no ');
INSERT into tb_student values (' 9908088 ', ' Maojie ', ' Male ', ' 1982-1-1 ', ' computer ', 622.2, ' no ');
INSERT into tb_student values (' 9608066 ', ' Kanghong ', ' female ', ' 1979-9-7 ', ' computer ', 596.8, ' yes ');
INSERT into tb_student values (' 9805026 ', ' Summer ', ' Male ', ' 1980-5-7 ', ' history ', 426.7, ' no ');
INSERT into tb_student values (' 9702033 ', ' Lili ', ' Male ', ' 1979-7-7 ', ' math ', 463.9, ' no ');
Inserting data into a teacher's table Tb_teacher
Copy Code code as follows:
Tb_teacher
INSERT into tb_teacher values (' 20222 ', ' in flower ', ' female ', ' 1962-6-19 ', ' associate Professor ');
INSERT into tb_teacher values (' 20406 ', ' Zhangjian ', ' female ', ' 1946-7-16 ', ' Professor ');
INSERT into tb_teacher values (' 10429 ', ' Chiang's success ', ' Male ', ' 1959-3-12 ', ' associate Professor ');
INSERT into tb_teacher values (' 10616 ', ' years ', ' Men ', ' 1945-9-1 ', ' Professor ');
INSERT into tb_teacher values (' 20626 ', ' Sun Le ', ' female ', ' 1971-12-15 ', ' Lecturer ');
INSERT into tb_teacher values (' 10803 ', ' tie ', ' Male ', ' 1958-9-22 ', ' associate Professor ');
INSERT into tb_teacher values (' 10812 ', ' Mi King ', ' Male ', ' 1960-1-3 ', ' associate Professor ');
INSERT into tb_teacher values (' 11015 ', ' Chai Huai ', ' Male ', ' 1973-8-26 ', ' Lecturer ');
INSERT into tb_teacher values (' 11107 ', ' Fang Hua ', ' female ', ' 197**-6 ', ' Lecturer ');
INSERT into tb_teacher values (' 20836 ', ' Zhang Jing ', ' female ', ' 1974-11-15 ', ' Lecturer ');
INSERT into tb_teacher values (' 10101 ', ' Costian ', ' Male ', ' 1940-12-5 ', ' Professor ');
INSERT into tb_teacher values (' 10312 ', ' Gong wen ', ' Male ', ' 1959-3-17 ', ' associate Professor ');
INSERT into tb_teacher values (' 20506 ', ' Wu Yan ', ' female ', ' 1947-10-6 ', ' Professor ');
INSERT into tb_teacher values (' 20701 ', ' Shen Fifi ', ' female ', ' 1960-6-18 ', ' associate Professor ');
INSERT into tb_teacher values (' 10202 ', ' Liang Longlin ', ' Male ', ' 1948-6-18 ', ' Professor ');
INSERT into tb_teacher values (' 10428 ', ' Li Yang ', ' Male ', ' 1955-8-12 ', ' Professor ');
INSERT into tb_teacher values (' 10621 ', ' Guru ', ' Male ', ' 1943-11-18 ', ' Professor ');
Insert into tb_teachervalues (' 10809 ', ' Deng for the People ', ' male ', ' 1957-1-26 ', ' associate Professor ');
INSERT into tb_teacher values (' 20106 ', ' Jiangxiaogong ', ' female ', ' 1961-6-5 ', ' associate Professor ');
INSERT into tb_teacher values (' 10131 ', ' Lin Lin ', ' Male ', ' 1968-9-11 ', ' Lecturer ');
INSERT into tb_teacher values (' 10802 ', ' yang bright red ', ' male ', ' 1941-5-23 ', ' Professor ');
INSERT into tb_teacher values (' 10223 ', ' Joey ', ' Male ', ' 1970-3-8 ', ' Lecturer ');
INSERT into tb_teacher values (' 20255 ', ' Sun Lili ', ' female ', ' 1975-9-12 ', ' Lecturer ');
INSERT into tb_teacher values (' 20705 ', ' Summer Snow ', ' female ', ' 1969-10-28 ', ' Lecturer ');
INSERT into tb_teacher values (' 10712 ', ' South ', ' Male ', ' 1975-9-13 ', ' Lecturer ');
INSERT into tb_teacher values (' 10201 ', ' Shun da ', ' Male ', ' 1940-12-17 ', ' Lecturer ');
INSERT into tb_teacher values (' 20301 ', ' Gaoshan ', ' female ', ' 1965-6-19 ', ' associate Professor ');
INSERT into tb_teacher values (' 20319 ', ' Lini ', ' female ', ' 1973-4-1 ', ' Lecturer ');
INSERT into tb_teacher values (' 21025 ', ' banner ', ' Female ', ' 1972-6-6 ', ' Lecturer ');
INSERT into tb_teacher values (' 11117 ', ' Han Ming ', ' Male ', ' 1976-2-14 ', ' ta ');
INSERT into tb_teacher values (' 10503 ', ' Sun Jianguo ', ' Male ', ' 1949-10-1 ', ' Professor ');
INSERT into tb_teacher values (' 10509 ', ' Huangning ', ' Male ', ' 1956-12-23 ', ' associate Professor ');
Inserting data into a Tb_renke table
Copy Code code as follows:
Tb_renke
INSERT into Tb_renke values (' 21003 ', ' 21025 ');
INSERT into Tb_renke values (' 30211 ', ' 20255 ');
INSERT into Tb_renke values (' 30232 ', ' 10201 ');
INSERT into Tb_renke values (' 40711 ', ' 10712 ');
INSERT into Tb_renke values (' 40722 ', ' 20701 ');
INSERT into Tb_renke values (' 10101 ', ' 20106 ');
INSERT into Tb_renke values (' 11101 ', ' 11107 ');
INSERT into Tb_renke values (' 20511 ', ' 10509 ');
INSERT into Tb_renke values (' 10101 ', ' 10131 ');
Insert Intotb_renke values (' 20534 ', ' 10503 ');
INSERT into Tb_renke values (' 10712 ', ' 20705 ');
INSERT into Tb_renke values (' 20115 ', ' 20106 ');
INSERT into Tb_renke values (' 10222 ', ' 10223 ');
INSERT into Tb_renke values (' 30412 ', ' 10429 ');
INSERT into Tb_renke values (' 40316 ', ' 20319 ');
INSERT into Tb_renke values (' 40612 ', ' 20626 ');
INSERT into Tb_renke values (' 20328 ', ' 20301 ');
INSERT into Tb_renke values (' 10812 ', ' 10429 ');
INSERT into Tb_renke values (' 20801 ', ' 10803 ');
INSERT into Tb_renke values (' 30802 ', ' 10812 ');
INSERT into Tb_renke values (' 11001 ', ' 11015 ');
INSERT into Tb_renke values (' 20113 ', ' 10131 ');
INSERT into Tb_renke values (' 30416 ', ' 10428 ');
INSERT into Tb_renke values (' 20327 ', ' 10312 ');
INSERT into Tb_renke values (' 20521 ', ' 20506 ');
INSERT into Tb_renke values (' 30213 ', ' 10201 ');
INSERT into Tb_renke values (' 11101 ', ' 11117 ');
INSERT into Tb_renke values (' 10715 ', ' 20222 ');
Insert Intotb_renke values (' 20111 ', ' 10101 ');
INSERT into Tb_renke values (' 10218 ', ' 10202 ');
Insert Intotb_renke values (' 30423 ', ' 20406 ');
INSERT into Tb_renke values (' 40331 ', ' 20319 ');
Insert Intotb_renke values (' 40625 ', ' 10616 ');
INSERT into Tb_renke values (' 20314 ', ' 20301 ');
INSERT into Tb_renke values (' 10811 ', ' 20836 ');
INSERT into Tb_renke values (' 30819 ', ' 10802 ');
Inserting data into a professional table tb_major
Copy Code code as follows:
Tb_major
INSERT into tb_major values (' chemistry ', ' Guru ');
INSERT into tb_major values (' computer ', ' Deng for the People ');
INSERT into tb_major values (' software ', ' Li Ming ');
INSERT into tb_major values (' Foreign trade ', ' Shen Fifi ');
INSERT into tb_major values (' Math ', ' Liang Longlin ');
INSERT into tb_major values (' Physics ', ' Li Yang ');
Insert Intotb_major values (' Physics ', ' Wang Guoyu ');
INSERT into tb_major values (' History ', ' Wu Yan ');
INSERT into tb_major values (' Chinese ', ' Costian ');
INSERT into tb_major values (' News ', ' Gong Wen ');
Inserting data into the schedule Tb_course
Copy Code code as follows:
Tb_course
INSERT into tb_course values (' 20511 ', ' World History ', 4,4);
INSERT into tb_course values (' 10101 ', ' College Chinese ', 2,2);
INSERT into tb_course values (' 20801 ', ' Computer Basics (i) ', 4, 3);
INSERT into tb_course values (' 10218 ', ' Higher Algebra ', 4,4);
INSERT into tb_course values (' 11001 ', ' English (A) ', 6,6);
INSERT into tb_course values (' 20113 ', ' foreign literature ', 4,4);
INSERT into tb_course values (' 30416 ', ' interface technology ', 4, 3);
INSERT into tb_course values (' 20327 ', ' press editor ', 2,2);
INSERT into tb_course values (' 20521 ', ' Chinese national History ', 3,2);
INSERT into tb_course values (' 30213 ', ' Number theory ', 4,4);
INSERT into tb_course values (' 11101 ', ' Sports ', 2,2);
INSERT into tb_course values (' 21003 ', ' English (ii) ', 4,4);
INSERT into tb_course values (' 10715 ', ' higher Mathematics ', 4,4);
INSERT into tb_course values (' 20111 ', ' Ancient Chinese ', 3, 3);
INSERT into tb_course values (' 30802 ', ' computer base (ii) ', 3, 3);
INSERT into tb_course values (' 30423 ', ' EMF Theory ', 3, 3);
INSERT into tb_course values (' 40331 ', ' Communication psychology ', 2,2);
INSERT into tb_course values (' 40625 ', ' chromatography ', 2,2);
INSERT into tb_course values (' 20314 ', ' Introduction to Journalism ', 2,2);
INSERT into tb_course values (' 10811 ', ' discrete Mathematics ', 2,2);
INSERT into tb_course values (' 30819 ', ' compiler technology ', 4,4);
INSERT into tb_course values (' 20534 ', ' Chinese Modern History ', 4,4);
INSERT into tb_course values (' 10712 ', ' Political Economy ', 3, 3);
INSERT into tb_course values (' 20115 ', ' Modern Chinese ', 4,4);
INSERT into tb_course values (' 30211 ', ' conspectus statistics ', 3, 3);
INSERT into tb_course values (' 30232 ', ' Mathematical Analysis ', 2,2);
INSERT into tb_course values (' 40711 ', ' International Investment Study ', 2,2);
INSERT into tb_course values (' 40722 ', ' International commercial law ', 2,2);
INSERT into tb_course values (' 30832 ', ' algorithmic design ', 4,4);
INSERT into tb_course values (' 10812 ', ' digital circuit ', 4,4);
INSERT into tb_course values (' 10222 ', ' analytic geometry ', 2,2);
INSERT into tb_course values (' 30412 ', ' Modern Physics Experiment ', 3,2);
INSERT into tb_course values (' 40316 ', ' contemporary journalism history ', 2,2);
INSERT into tb_course values (' 40612 ', ' coordination chemistry ', 3, 3);
INSERT into tb_course values (' 20328 ', ' Modern news report ', 4,4);
Tb_studentinfo Insert data for student personal situation table
Copy Code code as follows:
Tb_studentinfo
INSERT into tb_studentinfo values (' 9607039 ', ' 530120169021101 ', ' Anhui ', ' Riverbank Cell building ', ' 5033228 ', ' singing, wrestling ', ' was named Miyoshi ', ');
INSERT into tb_studentinfo values (' 9907002 ', ' 530120170060701 ', ' Yunnan ', ' riverbank cell ', ' 5033226 ', ' Dance, basketball ', ' was named Miyoshi ', ');
INSERT into tb_studentinfo values (' 9801055 ', ' 530120171072501 ', ' Hubei ', ' White Horse Cell block ', ' 4133224 ', ' Weiqi ', ', ');
INSERT into tb_studentinfo values (' 9902006 ', ' 530120170122901 ', ' Hunan ', ' Venus Cell Building ', ' 3133218 ', ' Chess ', ' was named Miyoshi ', ');
INSERT into tb_studentinfo values (' 9704001 ', ' 530120168121101 ', ' Yunnan ', ' Jing yuan cell ', ' 2133227 ', ' volleyball, soccer ', ', ');
INSERT into tb_studentinfo values (' 9603001 ', ' 530120174050101 ', ' Yunnan ', ' Riverbank Community building ', ' 5033219 ', ' singing, dancing ', ' was named the safflower teenager ');
INSERT into tb_studentinfo values (' 9606005 ', ' 530120175040702 ', ' Jiangsu ', ' riverbank Cell block ', ' 5033123 ', ' speech ', ', ', ');
INSERT into tb_studentinfo values (' 9803011 ', ' 530120173021201 ', ' Sichuan ', ' White Horse Community Building Unit ', ' 4133124 ', ' stamp collecting ', ' won the third prize at the Philatelic Exhibition ');
INSERT into tb_studentinfo values (' 9908088 ', ' 530120172092801 ', ' Sichuan ', ' sunshine Cell Block ', ' 3133177 ', ' long distance soccer ', ' second in provincial running ', ' cheating on punishment ');
INSERT into tb_studentinfo values (' 9608066 ', ' 530120174092201 ', ' Yunnan ', ' sunshine Cell Block ', ' 3133222 ', ' Photography ', ' Award for outstanding composition ');
INSERT into tb_studentinfo values (' 9805026 ', ' 530120174110901 ', ' Guizhou ', ' sunshine Cell Block ', ' 3133189 ', ' Go ', ' won first prize in Math Contest ');
INSERT into tb_studentinfo values (' 9702033 ', ' 530120170080401 ', ' Heilongjiang ', ' sunshine Cell Block ', ' 3148212 ', ' Go ', ' won third prize in Math Contest ');
Inserting data into the score table Tb_grade
Copy Code code as follows:
Tb_grade
INSERT into Tb_grade values (' 9805026 ', ' 20801 ', 75,87,82);
INSERT into Tb_grade values (' 9702033 ', ' 30802 ', 80,89,91);
INSERT into Tb_grade values (' 9907002 ', ' 11001 ', 91,83,85);
INSERT into Tb_grade values (' 9801055 ', ' 20113 ', 70,65,55);
INSERT into Tb_grade values (' 9607039 ', ' 40711 ', 85,80,88);
INSERT into Tb_grade values (' 9907002 ', ' 10715 ', 83,90,86);
INSERT into Tb_grade values (' 9801055 ', ' 20111 ', 78,60,65);
INSERT into Tb_grade values (' 9902006 ', ' 10218 ', 75,63,52);
INSERT into Tb_grade values (' 9902006 ', ' 11001 ', 78,86,81);
INSERT into Tb_grade values (' 9704001 ', ' 30416 ', 80,90,90);
INSERT into Tb_grade values (' 9803011 ', ' 20327 ', 95,93,90);
INSERT into Tb_grade values (' 9908088 ', ' 11001 ', 90,91,87);
INSERT into Tb_grade values (' 9805026 ', ' 20521 ', 90,97,96);
INSERT into Tb_grade values (' 9702033 ', ' 30213 ', 88,69,76);
INSERT into Tb_grade values (' 9907002 ', ' 11101 ', 88,65,72);
INSERT into Tb_grade values (' 9801055 ', ' 21003 ', 70,90,84);
INSERT into Tb_grade values (' 9902006 ', ' 11101 ', 80,70,70);
INSERT into Tb_grade values (' 9803011 ', ' 21003 ', 78,84,82);
INSERT into Tb_grade values (' 9908088 ', ' 11101 ', 82,75,78);
INSERT into Tb_grade values (' 9805026 ', ' 21003 ', 83,85,84);
INSERT into Tb_grade values (' 9805026 ', ' 20511 ', 90,82,86);
INSERT into Tb_grade values (' 9702033 ', ' 30232 ', 80,84,83);
INSERT into Tb_grade values (' 9907002 ', ' 10101 ', 84,96,92);
INSERT into Tb_grade values (' 9801055 ', ' 20801 ', 76,78,60);
INSERT into Tb_grade values (' 9902006 ', ' 10101 ', 85,88,81);
INSERT into Tb_grade values (' 9704001 ', ' 30802 ', 90,87,82);
INSERT into Tb_grade values (' 9803011 ', ' 20801 ', 60,50,51);
INSERT into Tb_grade values (' 9704001 ', ' 30423 ', 80,81,85);
INSERT into Tb_grade values (' 9603001 ', ' 40331 ', 67,72,70);
INSERT into Tb_grade values (' 9606005 ', ' 40625 ', 83,85,84);
INSERT into Tb_grade values (' 9803011 ', ' 20314 ', 76,76,76);
INSERT into Tb_grade values (' 9908088 ', ' 10811 ', 82,92,89);
INSERT into Tb_grade values (' 9608066 ', ' 30819 ', 78,84,82);
INSERT into Tb_grade values (' 9805026 ', ' 20534 ', 70,90,84);
INSERT into Tb_grade values (' 9702033 ', ' 30211 ', 93,89,90);
INSERT into Tb_grade values (' 9607039 ', ' 40722 ', 90,87,82);
INSERT into Tb_grade values (' 9907002 ', ' 10712 ', 90,96,97);
INSERT into Tb_grade values (' 9801055 ', ' 20115 ', 80,82,87);
INSERT into Tb_grade values (' 9902006 ', ' 10222 ', 85,91,79);
INSERT into Tb_grade values (' 9704001 ', ' 30412 ', 78,87,90);
INSERT into Tb_grade values (' 9603001 ', ' 30316 ', 66,71,73);
INSERT into Tb_grade values (' 9606005 ', ' 40612 ', 70,78,60);
INSERT into Tb_grade values (' 9803011 ', ' 20328 ', 90,88,87);
INSERT into Tb_grade values (' 9908088 ', ' 10812 ', 80,67,83);
INSERT into Tb_grade values (' 9608066 ', ' 30832 ', 95,92,93);
INSERT into Tb_grade values (' 9908088 ', ' 10101 ', 80,82,87);
The following data query:
(1) Enquiries on the number and name of the students born after December 31, 78
Think about the unfolding
Select school number, name from Tb_student where born > ' 1979 ';
Or:
Select school number, name from Tb_student where the birth date >cast (' 1978-12-31 ' as DateTime);
(2) Enquiries about female students who scored more than 500 points and the names of male students who scored more than 600 points in enrolment
Think about the unfolding
Select name from Tb_student where (entry score >500 and gender = ' female ') or (entry score >600 and gender = ' male ');
(3) Inquire the name and home address of the student who lives in the riverbank community
Think about the unfolding
Select Home address, name from Tb_studentinfo,tb_student where (home address like ' Riverbank Community% ') and (Tb_studentinfo. School number =tb_student);
(4) Check the number of students who have not taken the course Jiangxiaogong teacher
Think about the unfolding
Select from Tb_student where is not in (select number from Tb_grade,tb_teacher,tb_renke where Tb_grade. Course code number =tb_renke. Course Code and Tb_renke. Teacher code number =tb_teacher. Teacher code and name = ' Jiangxiaogong ');
(5) Check the average grades of each student's selected course, student number, name
Think about the unfolding
Select Tb_student. Name, Tb_grade. Number, AVG (final) from Tb_student,tb_grade where tb_student. School number =tb_grade. Learning Number GROUP BY tb_ Student. Name, Tb_grade. School number;
(6) Inquire the course name and its number and name of the course that Zhaodong elective
Think about the unfolding
Select Tb_student. Name, Tb_grade. School number, course name from Tb_student,tb_grade, Tb_course where Tb_grade. School number =tb_student. Learning number and Tb_ Grade. Course Code =tb_course. Course Code and name = ' Zhaodong ';
(7) The name of the course taught by the professor
Think about the unfolding
Select Course name from Tb_course,tb_teacher, Tb_renke where tb_course. Course Code =tb_renke. Course code and tb_teacher. Teacher Code =tb_ Renke. Teacher designation and title = ' Professor '
Or:
Select Course name from Tb_course where course code in (select Course code from Tb_renke where teacher code in (select Teacher code from Tb_teacher where title = ' Professor '));
(8) Statistics on the number of students without loans
Think about the unfolding
Select COUNT (*) Number of people with no loans from Tb_student where is the loan = ' false ';
(9) The student number of the students who have the average score of three grades in each subject at normal, mid-term and final in 80.
Think about the unfolding
Select the number of grades on average in the number from the Tb_grade group by number having AVG (peacetime + midterm + final) >80;
(10) Enquiries and Maojie students to learn the same person's study number and name
Think about the unfolding
Select school number, name from Tb_student where professional in (select Professional from tb_student where name = ' Maojie ');
(11) The name of the student, the elective course number, and the result of the final grade of the student who is more than any teacher.
Think about the unfolding
Select Name, Course code, final, Tb_grade from Tb_grade,tb_student where tb_student. Learning number =tb_grade. and End >=all (select End from Tb_ grade);
(12) A new classmate, the school number is 9607001, the province certificate number is 530120169021100
Think about the unfolding
Insert into Tb_studentinfo (school number, identity card) VALUES (' 9607001 ', ' 530120169021100 ');
Insert into Tb_student (school number) VALUES (' 9607001 ');
(13) Add 10 points to the average student who has the final grade at the end of all
Think about the unfolding
Update Tb_grade Set final = end +10 where End > (select AVG (final) from Tb_grade);
(14) To inquire at least the student number of all the courses taught by Lin Lin
Think about the unfolding
Select number from Tb_grade x where NOT EXISTS (SELECT * from Tb_grade y where teacher code in (select Teacher code from Tb_teacher,tb_renke where Name = ' Lin Lin ' and tb_teacher. Teacher code =tb_renke. Teacher code name) and not EXISTS (select *from tb_grade z Where z. Course Code =y. Course Code and Z. =x. Learning number))
(15) Statistics of the number of classes per teacher
Think about the unfolding
Select Teacher code number, COUNT (*) course species from Tb_renke group by Tb_renke. Teacher Code;
(16) Enquiries about the student numbers and average grades of the 2 courses above (the average grade for the final grades of each course, only the courses that have been passed) and the output in descending order of their average grades
Think about the unfolding
Select number, AVG (end) average point from Tb_grade where End > Group by number has count (*) >2 ORDER by AVG (final) desc;
(17) Check the student number of the students without loans
Think about the unfolding
The study number from tb_student where the select number does not have a loan = ' false ';
(18) Check the number and name of the oldest student
Think about the unfolding
Select school number, name from Tb_student where born in (select min (birth) from tb_student);
Hope that this information, can help you!
