Examples of SQL query statements

Today is nothing special learning SQL query statement, personal feeling is too important, so I looked for an example on the Internet to practice their own, the feeling learned a lot, below to share with you the SQL query Statement example operation.

First of all, I have built 5 tables, respectively, as follows:

(a) Student table STUDENTS:

(b) Results table enrolls:

(c) Curriculum COURSES:

(d) Teachers ' table TEACHERS:

(e) Classroom table teaching:

Here are the SQL statements for some queries, from simple to complex

Table Operations

1, according to The STUDENTS table, the establishment of a student only contains the number, name, age of the female students table.

View Actions

2. Create a view FACULTY that includes only the teacher's number, name and age . ( the ORDER by clause cannot be included in the view definition )

3. generate a view from the student's table, curriculum, and timetable grade_table , which includes the student's name, course name and grade

Index operations

4, in the student table by the school number to establish the index

5. Delete Index

Single-table operation

6. Find out The course number and course name of 3 credits

7. Find out The student's school number, name and age in the age group, and sort by age in ascending order. (ASC (Ascending) or DESC (Descending) declares the sort by default, ascending.) )

8. Find out the name and gender of the students who are younger than the age and are of Hunan or Hubei. (The conditional comparison operator =, the and logical operators and (with), and also the operators you can use are: > (greater than), >= (greater than or equal), <= (less than equals), <> (not equal to) , not (non), OR (or) and so on.

predicate like can only be associated with strings, often " List of names > Like pattern " the format. Special characters "_" and "%" as wildcard characters.

predicate inch indicates that the specified property should match a value in the following collection (the set of values in parentheses or the result of a query clause), which is actually a series of OR (or) is abbreviated. The predicate Not in indicates that the specified property does not match a value in the subsequent collection.

predicate between is a " included in ... .. among " the meaning. )

Multi-table operation

9. Find out The name of the student who scored the grade. (sub-query)

Or you can use non-subqueries:

10. Find out The names of students whose grades are above

Note: Here to write in, cannot write = or >

or use the Any keyword to write =:

Or you can use non-subqueries:

11 . Check the student name and the course name and results of all students. (Connection query)

12, find out the hometown of Shanxi or Hebei, the results for The students above the name, place of origin and performance. (When constructing a multi-table join query command, you must follow two rules.) First, the number of join conditions is exactly 1 less than the number of tables (if there are three tables, there are two connection conditions ) ; second, if the primary key in a table is made up of multiple columns, You must have a join condition for each column in this primary key (with a few exceptions)

Note: "Hebei" and "Shanxi" to use "," separated, if the "or" Query results are incorrect

Or, you can also use "or" to write:

13. Find out the name, course name and result of the female students who are above the grade of the course. ( sub-query in FROM clause)

or direct query:

Use of expressions and functions

14. Check the hours of each course. A credit represents 16 hours (an arithmetic expression consists of an arithmetic operator +,-, * , a column name, or a numeric constant.) )

15, Find out the minimum age of teachers. (intrinsic: The SQL standard uses only COUNT , SUM , AVG , MAX , MIN function, called the aggregation function ( Set Function ). The result of the count function is the total number of statistics for that column, and the SUM function sums the values of the column statistics, AVG The function computes the average value of the column statistic, and themax function asks for the maximum value of the column, and the min function asks for the minimum value of the column. )

16. The number of students is less than or equal to the age of five. (statistics)

17 . Find out the average scores of students and the number of courses they have learned

18 . Find out the names of students over the average age

19. find out the average results of each course, grouped by course number, and only choose the results of the course of more than 3 students . ( GROUP by and having

GROUP by clause groups a table by the same values on a specified column (or columns), and then sets the action for each set of data.

GROUP by clauses always follow the WHERE clause after the WHERE when the clause defaults, it is followed by the from after the clause.

having clauses are commonly used to control the query of rows after a cluster is computed. )

20 . Check the number and name of the students who have not selected any courses. (When a subquery involves a column from an external query, it is called a correlated subquery ( correlated subquery) .) Related subqueries are used to exist test predicates EXISTS and not EXISTS , and all , any ( SOME ) and so on. )

Note: The students and enrolls two tables cannot be written on the back of the child statement, only the enrolls table can be written, otherwise the result of the query is incorrect;

21. Find out which courses only the boys selected

Or it can be written like this:

22, asked to give a list of students, the place of origin, the form of the student's hometown province, but also some other students of the province of origin.

If you only ask which province you can write:

23. find out the names of all the students who have enrolled in all courses

Some students above the SQL statement may not understand, the following is a link, point Open has a detailed explanation, to understand helpful https://zhidao.baidu.com/question/258791372.htmlqbl=relate_question_4 &word=%ca%fd%be%dd%bf%e2%b5%c4not%20exist

Today, the first to write so much, after the encounter of other supplements, not to be continued ...

---restore content ends---

Examples of SQL query statements