SQL often referred to as Structured Query Language, is a database computer language designed for managing data in relational database management systems (RDBMS), and originally based upon relational algebra. Its scope includes data query and update, schema creation and modification, and data access control. SQL was one of the first languages for Edgar F. Codd's relational model in his influential 1970 paper, "A Relational Model of Data for Large Shared Data Banks" and became the most widely used language for relational databases.
Basic structure of an SQL query
General Structure
SELECT ...... FROM ...... WHERE ......
SELECT [ALL / DISTINCT] expr1 [AS col1], expr2 [AS col2] ;
FROM tablename WHERE condition
a.The query will select rows from the source tablename and output the result in table form.
b.Expressions expr1, expr2 can be :
• a column, or
• an expression of functions and fields.
c. And col1, col2 are their corresponding column names in the output table.
List all the student records.
SELECT * FROM student
List the names and house code of 1A students.
SELECT name, hcode, class FROM student ;WHERE class="1A"
List the residential district of the Red House members.
SELECT DISTINCT dcode FROM student ;WHERE hcode="R"
List the names and ages (1 d.p.) of 1B girls.
SELECT name, ROUND((DATE( )-dob)/365,1) AS age ;FROM student WHERE class="1B" AND sex="F"
List the names, id of 1A students with no fee remission.
SELECT name, id, class FROM student ; WHERE class="1A" AND NOT remission
Grouping
SELECT ...... FROM ...... WHERE condition ;GROUP BY groupexpr [HAVING requirement]
Group functions:
COUNT( ), SUM( ), AVG( ), MAX( ), MIN( )
-groupexpr specifies the related rows to be grouped as one entry. Usually it is a column.
-WHERE condition specifies the condition of individual rows before the rows are group. HAVING requirement specifies the condition involving the whole group.
List the number of students of each class.
SELECT class, COUNT(*) FROM student ;GROUP BY class
List the average Math test score of each class.
SELECT class, AVG(mtest) FROM student ;GROUP BY class
List the number of girls of each district.
SELECT dcode, COUNT(*) FROM student ;WHERE sex="F" GROUP BY dcode
List the max. and min. test score of Form 1 students of each district.
SELECT MAX(mtest), MIN(mtest), dcode ;FROM student ; WHERE class LIKE "1_" GROUP BY dcode
List the average Math test score of the boys in each class. The list should not contain class with less than 3 boys.
SELECT AVG(mtest), class FROM student ;WHERE sex="M" GROUP BY class ;HAVING COUNT(*) >= 3
Display Order
SELECT .... FROM ... WHERE .... GROUP BY ..... ;ORDER BY colname ASC / DESC
List the boys of class 1A, order by their names.
SELECT name, id FROM student; WHERE sex="M" AND class="1A" ORDER BY name
List the 2A students by their residential district
SELECT name, id, class, dcode FROM student ;WHERE class="2A" ORDER BY dcode
List the number of students of each district (in desc. order).
SELECT COUNT(*) AS cnt, dcode FROM student ; GROUP BY dcode ORDER BY cnt DESC
List the boys of each house order by the classes. (2-level ordering)
SELECT name, class, hcode FROM student ;WHERE sex="M" ORDER BY hcode, class
Output
List the students in desc. order of their names and save the result as a database file name.dbf.
SELECT * FROM student ;ORDER BY name DESC INTO TABLE name.dbf
Print the Red House members by their classes, sex and name.
SELECT class, name, sex FROM student ;WHERE hcode="R" ; ORDER BY class, sex DESC, name TO PRINTER
Union, Intersection and Difference of Tables
The two clubs want to hold a joint party. Make a list of all students. (Union)
SELECT * FROM bridge ; UNION ;SELECT * FROM chess ;ORDER BY class, name INTO TABLE party
Print a list of students who are members of both clubs. (Intersection)
SELECT * FROM bridge ;WHERE id IN ( SELECT id FROM chess ) ; TO PRINTER
Make a list of students who are members of the Bridge Club but not Chess Club. (Difference)
SELECT * FROM bridge ;WHERE id NOT IN ( SELECT id FROM chess ) ;
INTO TABLE diff
SQL Aggregate Functions
What are SQL Aggregate Functions?
The SQL Aggregate Functions are functions that provide mathematical operations. The functions include:
count() - counts a number of rows
sum() - compute sum
avg() - compute average
min() - compute minimum
max() - compute maximum
Why Use SQL Aggregate Functions?
The SQL Aggregate Functions are useful when mathematical operations must be performed on all or a grouping of values.
How To Use SQL Aggregate Functions
SQL Aggregate Functions are used as follows. If a grouping of values is needed also include the GROUP BY clause.
Use a column name or expression as the parameter to the Aggregate Function. The parameter, '*', represents all rows.
SQL Aggregate Functions Syntax
SELECT
FROM
GROUP BY
SQL Aggregate Functions Example
The following example Aggregate Functions are applied to the employee_count of the branch table. The region_nbr is the level of grouping.
Here are the contents of the table:
Table: BRANCH
branch_nbr branch_name region_nbr employee_count
108 New York 100 10
110 Boston 100 6
212 Chicago 200 5
404 San Diego 400 6
415 San Jose 400 3
This SQL Statement with aggregate functions is executed:
SELECT region_nbr, count(branch_nbr), sum(employee_count), min(employee_count),
max(employee_count), avg(employee_count)
FROM dbo.branch
GROUP BY region_nbr
ORDER BY region_nbr
Here is the result.
region_nbr count
(branch_nbr) sum (employee_count) min
(employee_count) max
(employee_count) avg
(employee_count)
100 2 16 6 10 8
200 1 5 5 5 5
400 2 9 3 6 4
Comparison
expr IN ( value1, value2, value3)
expr BETWEEN value1 AND value2
expr LIKE "%_"
List the students who were born on Wednesday or Saturdays.
SELECT name, class, CDOW(dob) AS bdate ;FROM student ;WHERE DOW(dob) IN(4,7)
List the students who were not born in January, March, June, September.
SELECT name, class, dob FROM student ;WHERE MONTH(dob) NOT IN (1,3,6,9)
List the 1A students whose Math test score is between 80 and 90 (incl.)
SELECT name, mtest FROM student ;WHERE class="1A" AND ;mtest BETWEEN 80 AND 90
List the students whose names start with "T".
SELECT name, class FROM student ;WHERE name LIKE "T%"
List the Red house members whose names contain "a" as the 2nd letter.
SELECT name, class, hcode FROM student ;WHERE name LIKE "_a%" AND hcode="R"
No comments:
Post a Comment