SQL class notes-aggregate functions

Source: Internet
Author: User


2017.11.13

Aggregation functions:
Number of tuples in count (*) statistics
Count (attribute name) counts the number of column values in a column//non-statistical null value
Sum calculates the sum of a column of values (this column must be a numeric row)
AVG calculates the average of a column (this column must be a numeric row)
Max asks for the maximum value in a column value
Min to find the smallest value in a column value
Tuples: Each piece of data

Example 1: The average age of a computer professional student
Select AVG (SAGE) as average age from student where sdept= ' computer '

Example 2: How many courses are taught in the School of statistics?
Select COUNT (*) from course
Select COUNT (CNO) from course

Example 3: Statistics on the number of students who have paid tuition
Select COUNT (*) From course--all records are counted: The hand-paid is counted.
Select COUNT (Fees) from course--statistics on the number of students handed over, not to pay the statistics

Example 4: How to remove duplicate items and then count:
Such as: Query the number of students who have enrolled in the course
Select COUNT (Distinct sno) from SC
Note: Use distinct to avoid repeated calculation of student numbers
(This structure is not available in Access, in SQL server2008)

Ex: Query the highest score, the lowest score and the average score, (the query column attention is renamed as: Highest score, lowest score, average score,
Results are stored in the new table ABC)
Select MAX (grade) as highest score, min (grade) Lowest score, AVG (grade) as average split into ABC from SC

ORDER BY clause:

Example 5: Sort the course in ascending order by credit:
SELECT * FROM Course ORDER BY credits
SELECT * FROM Course ORDER BY ccredit Desc (Descending)

Example 6: Query the number and results of students who have enrolled in 2 courses, and the results are sorted in descending order of grades:
Select CNO, Sno,grade from SC where cno=2 order by grade DESC

Example 7: Inquiry Electronics, computer professional students name, professional name, results by professional name Ascending, the name descending order:
The order by can be sorted by following multiple fields:
A1 represents the first field, A2 represents the second field
1.order by a1,a2 desc--a1 Ascending, A2 descending
2.order by A1 asc,a2 desc--a1 Ascending, A2 descending ASC can be omitted
3.order by A1 desc,a2 desc--A1,A2 Descending
Select Sname as name, sdept as system name from student where sdept in (' Electronic ', ' computer ') Order BY sdept,sname Desc

GROUP BY clauses (statistics after grouping):

Example 8: Check the number of elective courses:
Select Cno,count (*) from SC GROUP by CNO

Example 9: Statistics of how many men and women are born:
Select Ssex,count (ssex) as number from student group by Ssex

Example 10: Total score for students:
Select Sno,sum (grade) as total score from SC GROUP by Sno

Having clauses (tuples that have been grouped, not for the entire table, usually after group by):

Example 11: A student who is in the class of 2 or more, and the average score:
Select Sno, AVG (grade) as average from SC GROUP by SNO have Count (*) >=2

Example 12: A student who has a class of more than 2 and has passed the grade, and has a statistical average score:
Select Sno,avg (grade) as average from SC where grade>=60 GROUP by SNO have Count (*) >=2-This is the wrong wording
Select Sno,avg (grade) as average from SC GROUP by SNO have Count (*) >=2 and min (grade) >=60--Right

The table used by the----is the blog file "Student table"

Practice:

--First, in the store sales database "Customer" write the SQL code to query:
Use Customer
Go

--1, check customer name and contact information.
Select name, contact from customer

--2, query all product categories (the results cannot have duplicate product categories).
Select DISTINCT product category from product

--3, check the product name between 100 and 200.
Select Product name from product where unit price >100 and price <200

--4, inquire the product name of the footwear with unit price above 300.
Select Product name from product where commodity category = ' shoes ' and price >300

--5 the customer number that purchased the c101,d201 or E301 product number (the customer number cannot be duplicated in the result).
Select distinct customer number from purchase where product number in (' C101 ', ' D201 ', ' E301 ')

--6, the name of the customer and the contact information of the surname Wang.
Select name, contact from customer where name like ' Wang% '

--7, inquires the name and contact information of the customer except the surname Liu.
Select name, contact from customer where name isn't like ' Liu '


--Two. In the students database (given), complete the following query with a SQL query: (write SQL statement)
Use students
Go
--1, query examination results have failed the student's school number.
Select Sno from SC where grade<60

--2, sort the courses in ascending order of credits.
SELECT * FROM Course ORDER by Ccredit

--3, check the number of the top 3 students in the 2nd course.
Select Top 3 sno from SC where cno=2 order by grade DESC

--4, check the number of elective courses.
Select Cno,count (*) as number from SC GROUP by CNO

--5, statistics of the number of male and female students each have how many people.
Select Ssex,count (ssex) as number from student group by Ssex

--6, ask each student's total score.
Select Sno,sum (grade) as total score from SC GROUP by Sno

--7, the students who have chosen more than 2 courses and have passed the grade, and the average score.
Select Sno,avg (grade) as average from SC GROUP by SNO have Count (*) >=2 and min (grade) >=60

--8, query the number of students who took 2 courses, the course number and results, the results of the query in descending order by grade.
Select Sno,cno,grade from SC where cno=2 order by grade DESC

--9, professional names and numbers of professionals with less than 2 professional enquiries.
Select Sdept,count (*) as number from student group by Sdept have COUNT (sdept) <2

--10, query the name of female students, professional names, results by name in ascending order, professional name descending.
Select Sname as name, sdept as professional name from student where Ssex in (' Woman ') Order by sname,sdept Desc

SQL class notes-aggregate functions

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.