sql query basic - aeriesconference.aeries.com/spring2017/docs/pdfs/940 sql query... ·...

SQL Query Basic Conference 2017

Session 940 - SQL Query Basic

Session Description: Create and use SELECT, UPDATE, INSERT, and DELETE queries. Will also discuss GROUP BY clause, ORDER BY clause, dual-table queries and other special SQL demands.

1. SQL Query: SELECT

SELECT Examples WHERE Clause and Basic Operators SELECT Statement Exercises Combining Conditions (AND/OR) IN and BETWEEN Operators

2. Inserting into a Table

3. Updating Records UPDATE Statement Exercises Deleting Records

4. Aggregate Functions

5. ORDER BY clause

6. Table Joins Inner Join Left Join Right Join Full Join

7. Comments In Saved Queries


SQL Query: SELECT The SELECT statement is used to query the database and retrieve data that match the criteria that you specify. Here is the structure of a simple SELECT statement: SELECT column1, column2 FROM tablename WHERE condition --WHERE clause is optional The column names that follow the SELECT keyword determine which columns will be returned in the results. You can select as many column names as you'd like, or you can use an "*" (asterisk) to select all columns. The table name that follows the keyword FROM specifies the table that will be queried to retrieve the desired results. The following are sample statements to be run in your database using the Query Editor in SQL Server Management Studio. Before clicking Execute write down your expected results. Then click Execute (or press F5 on your keyboard) and compare them to the actual results. SELECT * FROM stu

SELECT id, sc, sn, fn, ln FROM stu

The WHERE clause (optional) is used to filter which rows will be returned or displayed, based on the criteria described after the keyword WHERE. Below are some operators that can be used in the WHERE clause:

Operator Description Example = Equals stu.id = 1234500 > Greater than stu.ed > '08/01/2015' < Less than his.mk < 'F' >= Greater than or equal to stu.gp >= 4.0 <= Less than or equal to dsp.dy <= 5 <> Not equal to fre.cd <> 'F' LIKE *See note below *See note below


This statement uses the WHERE clause and will only select rows where the sex equals 'F'. SELECT * FROM stu WHERE sx = 'F' The following statement will match any student that has a birth date after September 1, 2010.

SELECT sc, sn, id, ln, fn FROM stu WHERE bd > '09/01/2010'

The following statement will match any student that has a grade level greater than or equal to 9.

SELECT sc, sn, id, ln, fn, gr FROM stu WHERE gr >= 9

The following statement will match any student that has a school code less than 995. SELECT sc, sn, id, ln, fn

FROM stu WHERE sc < 995

The following statement will match any student where the language fluency code does not equal 'E'

SELECT sc, sn, id, gr, sx, ln, fn, lf FROM stu WHERE lf <> 'E'

The LIKE pattern matching operator can also be used in the conditional selection of the WHERE clause. LIKE is a very powerful operator that allows you to select only rows that match a pattern that you specify. The percent sign "%" can be used as a wild card to match any possible characters that might appear before or after the characters specified. For example, this SQL statement will match any last name that begins with 'M'. Strings must be in single quotes. SELECT sc, sn, id, gr, sx, ln, fn FROM stu WHERE ln LIKE 'M%' The following statement will match any address city that starts with 'Eagle'. SELECT sc, sn, id, gr, sx, ln, fn, ad, cy, st, zc FROM stu WHERE cy LIKE 'Eagle%' The following statement will match any course title that ends with a space followed by the letters 'CP'. SELECT cn, co, de FROM crs WHERE co LIKE '% CP'


The following statement will match any discipline incident description that contains the phrase 'dress code' anywhere in the text. Notice that there is a percent sign before and after 'dress code'.

SELECT pid, dt, cd, co FROM ads WHERE co LIKE '%dress code%'

When checking for text/character columns that are blank, use two single quotes (e.g., WHERE mn = '') It's important to note that in SQL there is a difference between blank '' and NULL. In some cases (particularly when dealing with dates) you will need to use the keyword NULL One important thing to note with the keyword NULL is that it is not a value, and therefore cannot be evaluated by the logical operators you would typically use in other queries (<, >,=, etc.). When looking for NULL values you must use IS or IS NOT. The following statement will return student records that do not have a leave date populated.

SELECT * FROM stu WHERE LD IS NULL SELECT Statement Exercises Write SELECT statements for the following. All examples will select from the STU table. Display the first name, last name, and city for everyone not from your city. Display the last name and mailing address for everyone whose mailing address ends with "Lane". Display the first name and phone number for everyone whose phone number is in your area code. Display all columns for everyone who is less than 5 years old as of today. Display all columns for everyone whose first name contains "John".


Combining Conditions (AND/OR) The AND operator can be used to join two or more conditions in the WHERE clause. Both sides of the AND condition must be true in order for the condition to be met and for those rows to be displayed. SELECT column1, column2

FROM tablename WHERE condition1 AND condition2 Example (includes all active students below grade 6): SELECT sc, sn, id, ln, fn, gr, sx, bd FROM stu WHERE tg = '' AND gr < 6

The OR operator can be used to join two or more conditions in the WHERE clause also. However, either side of the OR operator can be true and the condition will be met - hence, the rows will be displayed. With the OR operator, either side can be true or both sides can be true. Parentheses can be used to group AND operators and OR operators. SELECT column1, column2

FROM tablename WHERE condition1 OR condition2 Example (includes all active students with a language fluency of 'L' or 'R'): SELECT sc, sn, id, ln, fn, gr, sx, lf FROM stu WHERE tg = '' AND (lf = 'L' OR lf = 'R')

IN and BETWEEN Conditional Operators The IN conditional operator is used to test whether or not a value (stated before the keyword IN) is "in" the comma-separated list of values provided in parentheses after the keyword IN.

SELECT * FROM dsp WHERE del = 0 AND ds IN ('SUS', 'SUS-H', 'SUS-P', 'EXP', 'EXP-P')

This statement will return records from the Dispositions (DSP) table where the disposition code is equal to any of the values in the list. The list does not have to be in any particular order. The IN conditional operator can be rewritten by using multiple “equals” operators and OR operators to achieve the exact same results, albeit in a more cumbersome syntax. The example below will return the exact same results as the example above.

SELECT * FROM dsp WHERE del = 0 AND (ds = 'SUS' OR ds = 'SUS-H' OR ds = 'SUS-P' OR ds = 'EXP' OR ds = 'EXP-P')

As you can see, the IN operator is much shorter and easier to read when you are testing for more than two or three values. You can also use NOT IN to exclude a list of values from your query.


The BETWEEN conditional operator is used to test whether or not a value (stated before the keyword BETWEEN) is "between" the two values stated after the keyword BETWEEN.

SELECT sc, sn, ln, fn, id, gr FROM stu WHERE gr BETWEEN 3 and 6

This statement will returns records where the grade range is between grade 3 and grade 6 inclusive. The BETWEEN operator can be rewritten by combining a "greater than or equal to" operator and a "less than or equal to" operator. The example below will return the exact same results as the example above:

SELECT sc, sn, ln, fn, id, gr FROM stu WHERE gr >= 3 AND gr <= 6

Again, the BETWEEN operator is easier to read for this purpose. You can also use NOT BETWEEN to exclude values in a specified range. Inserting into a Table The INSERT statement is used to insert or add a row of data into a table. Here is the format of a simple INSERT statement: INSERT INTO USR (SCH, NM, RO) VALUES (994, 'EagleAdmin', 0) In the example above, the column name SCH will match up with the value 994, the column name NM will match up with the value 'EagleAdmin', and so on. When inserting, you do not have to specify a value for every column. Any column you omit will take the default value. For numeric columns, this is 0. For character columns, this is the empty string ''. For dates, the default is NULL. You must at least specify values for all columns that make up the table's "Primary Key". For student-related data, this is usually (SC, SN), (ID), (PID), or a combination of one of these with some other columns. You can use the Object Explorer in SQL Server Management Studio or run the Aeries report "Print Database Table Information" to help determine the primary key of a table. Note: All strings should be enclosed in single quotes: 'string' Updating Records The UPDATE statement is used to update or change records that match specified criteria. You'll recognize much of the format from the SELECT statement we learned about on page one. The WHERE clause for an UPDATE statement can use the same basic and advanced operators that we discussed in the section on SELECT statements. UPDATE tablename SET column1 = 'newvalue1', column2 = 'newvalue2' FROM tablename WHERE condition Example:

UPDATE stu

SET hsg = '480' FROM STU WHERE tg = '' AND sc = 993 AND gr = 8 AND ng = 9


Be extremely cautious when running UPDATE statements. An accidental execution or unintended results can end in hours of data cleanup.

Here's one example of how an UPDATE statement can go horribly wrong: UPDATE stu SET ln = 'Williams' FROM stu WHERE id = 1234 AND ln = 'Williams III' AND sc = 994 OR sc = 990 Look for the error in the example above. It appears the intended operation would be to remove the 'III' from both of this student's records since he is probably concurrently enrolled schools 994 and 990. The results can be catastrophic

Because of missing parentheses, every student in school 990 has their last name changed to Williams! To avoid undesired results such as this, always substitute in a SELECT statement to view the data set that your UPDATE statement's condition will affect. SELECT * --UPDATE stu SET ln = 'Williams' FROM stu WHERE id = 1234 AND ln = 'Williams III' AND (sc = 994 OR sc = 990) UPDATE Statement Exercises Write UPDATE statements to: Change one student's telephone number. Change all active students' Next School to 994 if their current school is 993 and their Next Grade is 9. Before and after each update, issue a SELECT statement to verify your data set and then your changes.

Deleting Records The DELETE statement is used to delete records or rows from the table. Eagle strongly suggests that you DO NOT delete any records from your database without first contacting Support for guidance. Using the DELETE statement could be detrimental to your district's database. The statement below would delete ALL student records from your STU table. DELETE FROM stu


It is strongly suggested by Eagle Software that the DELETE statement should not be used to delete records. Records to be deleted should ONLY be “tagged” in the DEL column. For example:

UPDATE stu SET del = 1 FROM stu WHERE sc = 990 and sn = 1234 This statement would only flag a student record as deleted, and the record would no longer display from the Student Data form. This "logical delete" is a safe guard so that if a record is tagged incorrectly, the flag can be changed back and no record is permanently deleted. Note: Unlike working within Aeries, while working within SQL Server Management Studio (SSMS), query results will display all “DEL-tagged” records. In order not to return DEL-Tagged records, include DEL = 0 in all WHERE clauses. Example (returns all students in grades 5 and below only if the STU record is not DEL-tagged): SELECT sc, id, ln, fn, gr, sx, bd FROM stu WHERE del = 0 AND gr <= 5 Aggregate Functions

Function Description Example MIN Returns the smallest value in a given column MIN(stu.bd) MAX Returns the largest value in a given column MAX(stu.gp)

SUM Returns the sum of the numeric values in a given column

SUM(dsp.dy)

AVG Returns the average value of a given column AVG(his.cc) COUNT (*) Returns the total number of rows in a query COUNT(*)

COUNT([ColumnName]) Returns the number of rows in a given column that have values (not NULL)

COUNT(ed)

Aggregate functions are used to compute against a "returned column of numeric data" from your SELECT statement. They basically summarize the results of a particular column of selected data. We are covering these here since they are used by the next topic, "GROUP BY". This statement will return a single result containing the average amount in the FEE table. SELECT AVG(am) FROM fee This statement returns the number of rows in the stu table. SELECT COUNT(*) FROM stu GROUP BY clause GROUP BY is an optional clause, which allows you to display the results of a query grouped by an aggregate function. For example the average percentage mark each student receives on their Gradebook assignments. SELECT sc, sn, avg(pc) FROM gbs WHERE del = 0 AND sc = 994 GROUP BY sc,sn


ORDER BY clause ORDER BY is another optional clause, which allows you to display the results of a query in a sorted order (either ascending order or descending order) based on the columns specified after ORDER BY. SELECT column1

FROM tablename ORDER BY column-list ASC or DESC

ASC = Ascending Order – (default if none is specified) DESC = Descending Order This statement returns results sorted by grade in descending order, then by last name (ascending), then by first name (ascending):

SELECT sc, sn, fn, ln, gr FROM stu ORDER BY gr DESC, ln, fn

Table Joins Up until this point all our sample queries have been useful to view, aggregate or change data within one table. It is time to introduce one of the more beneficial features of relational database systems - the "JOIN". It's the "JOIN" that makes relational database systems "relational". Joins allow you to link data from two or more tables together into a single query result--from one single SELECT statement.

SELECT columns FROM table1 JOIN table2 WHERE condition

Notice how each of the student related tables have “school number” and "student number" in common? These columns, which contain a unique student number in the same school, can be used to JOIN two tables. Using the two tables STU and ATT, let's say you would like to select an elementary school number, the student's name, and all of the dates the student has an all-day absence code. Here is an example of a JOIN statement to accomplish this:

SELECT stu.sc, stu.sn, stu.ln, stu.fn, att.dt, att.al FROM stu INNER JOIN att ON stu.sn = att.sn AND stu.sc = att.sc WHERE stu.del = 0 AND stu.sc = 990 AND stu.sn = 1 AND att.del = 0 AND att.al > '' ORDER by stu.ln, stu.fn, att.dt


This particular JOIN is known as an INNER JOIN. This is the most common type of JOIN that you will see or use.

Notice each of the columns is always preceded with the table name and a period. This isn't always required however it IS good practice so that you won't confuse which columns go with what tables. It is required if the column names are the same between the two tables. We recommend always using the "table.column" syntax when using joins. Another type of join is an OUTER join which can be a LEFT, a RIGHT or a FULL OUTER join. These particular joins can help assess missing records. LEFT JOIN A left join will produce results for all records (matched or not matched) in the left table comparing it to records in the right table. Records not matched in the right table will have null values.

SELECT stu.sc, stu.sn, stu.id, cse.di, cse.ed FROM stu LEFT JOIN cse ON stu.id = cse.id

RIGHT JOIN The reverse of a left join is the right join. A right join will produce all records (matched or not matched) in the right table comparing it to records in the left table. Records not matched in the left table will have null values.

SELECT stu.sc, stu.sn, stu.id, his.yr, his.te, his.st, his.cn, his.mk FROM stu RIGHT JOIN his ON stu.id = his.pid

FULL JOIN A FULL JOIN (or, equivalently, FULL OUTER JOIN) produces all records in both the left and right tables. Records matching will have data all the way across. Records unmatched in either table will produce NULL values. Note that all records in both tables will return.

SELECT stu.sc, stu.sn, stu.id, stu.gr, tst.* FROM stu full join tst on stu.id = tst.pid


Comments in Saved Queries When a query is saved a comment can also be added. To add a comment type two dashes then type your comment. If your comment carries to the next line start that line with two dashes also. After you have entered your comment click on the Save icon to save your query as a .sql file. --Review students without an SSD record SELECT stu.sc, stu.sn, stu.id, stu.cu, ssd.tgc, ssd.tgn FROM stu left join ssd on stu.sc = ssd.sc and stu.sn = ssd.sn WHERE ssd.sn IS NULL The following queries combine many of the statements and functions reviewed above. --First, determine which students were delete-tagged, but still have Gradebook records. --Review the STU.DTS (date-time stamp field) to see when they were delete tagged. --Review GBS.DTS to determine when the last grade book was added/changed. SELECT stu.id, stu.sn, stu.sc, stu.del, stu.dts, gbs.sc, gbs.sn, gbs.gn, gbs.del, gbs.dts FROM stu RIGHT JOIN gbs ON stu.sc = gbs.sc AND stu.sn = gbs.sn WHERE stu.del = 1 OR stu.del IS NULL --Second, compare the GBS and GBU tables. Determine if they really need to be delete tagged. --The WHERE clause contains the Student number (SN) and School code (SC) SELECT gbs.*, gbu.* FROM gbs INNER JOIN gbu ON gbs.sc = gbu.sc AND gbs.sn = gbu.sn WHERE gbs.sc = YYY AND gbs.sn = XXXX --Third, delete tag the GBS record with student number XXXX UPDATE gbs SET gbs.del = 1 WHERE gbs.sc = YYY AND gbs.sn = XXXX --Fourth, delete tag the GBU record with student number XXXX UPDATE gbu SET gbu.del = 1 WHERE gbu.sc = YYY AND gbu.sn = XXXX --Run these two queries if you still receive errors on database cleanup in ADMINCS --Fifth, delete all delete-tagged GBS records from the database --Add “AND SN = XXXX AND SC = YYY” if you want to delete each student record individually

DELETE GBS WHERE DEL = 1

--Lastly, delete all delete-tagged GBU records from the database --Add “AND SN = XXXX AND SC = YYY” if you want to delete each student record individually

DELETE GBU WHERE DEL = 1

sql query basic - aeriesconference.aeries.com/spring2017/docs/pdfs/940 sql query... ·...

Documents