What is SQL? continued The basic SQL language is actually quite small, and relatively easy to learn and use. It consists of six basic statement types (select, insert, update, delete, create, and drop) that can be conveniently grouped into the three categories shown below. SQL STATEMENTS BY CATEGORY [CATEGORY _ [STATEMENT PURPOSE _ Query SELECT Display rows of one or more tables Maintenance [INSERT ‘Add rows to a table UPDATE Change rows in a table DELETE Remove rows from a table Definition CREATE ‘Add tables, indices, views DROP Remove tables, indices, views Query statements The SELECT statement, which has many different forms, is used to formulate all queries. SELECT statements ‘ask questions’ whose answers may be found in, or derived from, one or more of the tables in a database. tenance statements The INSERT statement, which has two different forms, is used to add new rows to a table (one or more at a time). The UPDATE statement is used to modify existing rows, while the DELETE statement is used to remove obsolete rows. Definition statements The CREATE statement, which also has several different forms, is used to define new tables, indices, and views in a database. Various forms of the DROP statement are used to remove these components when they are no longer required.What is SQL? continued A SAL statement is a collection of clauses, keywords, and parameters that perform a particular function. In the examples below, each clause is shown on a separate line; keywords appear in all uppercase letters, parameters in all lowercase letters. QUERY STMT. ©] [MAINTENANCE STMT DEFINITION STMT SELECT columns DELETE CREATE FROM table FROM table INDEX index WHERE comparisons WHERE comparisons ON table ( columns ) Clauses The various clauses of SQL statements are named after their initial words: the SELECT clause, for example, or the FROM clause. SQL allows multiple clauses per line, but most SQL programmers use separate lines for clarity and ease of editing. Keywords SQL reserves a small number of words, called keywords, for specific purposes. Keywords can be entered in upper, lower, or mixed-case letters; they are shown here in all uppercase. Keywords may not be used as table or column names Parameters Parameters are the ‘variable’ parts of each clause. When formulating a SQL statement, you insert the appropriate column names, table names, and other such values in place of the lowercase parameters shown above.BASIC QUERIES Selecting All Columns & Rows Selecting Specific Columns Selecting Specific Rows Sorting Rows Eliminating Duplicate RowsSelecting All Columns & Rows The simplest kind of query displays all the columns and all the rows of a single table. An asterisk is entered in the SELECT clause (to indicate that all columns should be included), and the table name is specified in the FROM clause, like so: [SQL STATEMENT JOB THLE $s Scientist SELECT* > E Entertainer FROM JOBS w Writer 0 Instructor SQLSTATEMENT COUNTRY [RELIGION | PERCENT| Germany [Protestant 45 SELECT * » Germany _|Catholic 37 FROM RELIGIONS England Catholic 30 England | Anglican 70 All queries have SELECT and FROM clauses The FROM clause follows the SELECT clause The asterisk (* ) means ‘all columns' The table name is specified in the FROM clause Columns and rows appear in arbitrary orderSelecting All Columns & ROWS. continues The general syntax of a SQL SELECT statement appears below. Keywords and examples are shown in uppercase letters, while parameters appear in lowercase. SYNTAX SUMMARY CLAUSE _}PARAMETERS. ‘EXAMPLE SELECT * FROM table JOBS Exercises cb Ask the instructor for a brief tutorial in the use of the classroom database. Select all the columns and all the rows of the PERSONS table. . Explore the COUNTRIES table using a SELECT statement. Take a look at the ERRORS table. . Inspect the ARMIES table. Extra Credit 6, i Most self-respecting databases have a table called SYSCOLUMNS. Take a peek. Choose any other table from the SYSCOLUMNS table and display it.Selecting Specific Columns To select specific columns, we enter a list of columns in the SELECT statement (instead of an asterisk). Each column name is separated from the others by a comma and optional spaces. The columns are displayed in the order listed. SQLSTATEMENT, [TITLE Scientist SELECT TITLE » Entertainer FROM JOBS Writer Instructor SQUSTATEMENT. THLE JOB Scientist Ss SELECT TITLE, JOB g > Entertainer [E FROM JOBS Writer Ww Instructor 1 [SQUSTATEMENT JOB. TITLE JOB. s Scientist s SELECT *, JOB > E Entertainer [E FROM JOBS Ww Writer w I Instructor [I Column names are listed in the SELECT clause Column names are separated by commas Columns appear in the order listed Rows appear in arbitrary order 1Selecting Specific Columns continued The revised syntax of a SQL SELECT statement appears below. Note that it has been expanded to include the column list parameter in the SELECT clause. SYNTAX SUMMARY CLAUSE PARAMETERS. EXAMPLE. SELECT * * col list LANGUAGE, COUNTRY FROM table JOBS Exercises 1, Select only the JOB column from the JOBS table. 2. Show the NAME and BDATE columns from the PERSONS table. 3. The RELIGIONS table contains three columns — COUNTRY, RELIGION, and PERCENT. Display all of these columns, but show them in reverse order. Extra Credit 4, Show all the columns of the COUNTRIES table, but put the COUNTRY column on both ends (so we can line ‘em up with a ruler). Do this first without using an asterisk, then do it again with one.Selecting Specific Rows Rows in a table are identified by the values they contain. It is therefore important to understand the different categories of values that SQL supports, and the appropriate ‘syntax for entering each kind of value in query statements. VALUE SUMMARY CATEGORY [DESCRIPTION EXAMPLES: NUMERIC. positive values 3, +12 negative values -7, -1024000 decimal values 3.141519, -.96 NON- single words ‘Chamberlin’, ‘SELECT NUMERIC, multiple words ‘We love SQL’, 'The LORD is good to me! single quotes “10 O'Clock’, 'I don"t know! DATE ‘yyyy-mm-dd' format '1996-01-01', '1996-12-31' Numeric values are entered in the normal way Numeric values are specified in queries as one or more of the following characters: +-0123456789. The sign is optional, but must appear first. Only one decimal point is allowed. Note that commas, dollar signs, and percent signs are not allowed in numeric values. Non-numeric values are enclosed in quotes Non-numeric values, called strings, are entered inside of single quote marks. Use two consecutive single quote marks inside a string to represent a single quote. Date values are entered in 'yyyy-mm-dd' format While the SQL standard lacks a uniform representation of dates, the format shown above is supported in most SQL dialects. Dates must be enclosed in single quotes. 13Selecting Specific Rows continued A comparison is a phrase that consists of a column name, a comparison operator, and a value. All comparisons yield a result of either true or false. Comparisons are used to specify which rows of a table should be included in the result of a query. COMPARISON OPERATORS OPERATOR _ [MEANING EXAMPLE S Equal to NAME = 'EINSTEIN' <> Not equal to BDATE <> '1944-05-02" < Less than POP < 100000 <= Less than or equal to _ [NAME <='O"Grady’ > Greater than AREA > 999 >= Greater than or equal to | BDATE >= '1962-06-19" Acolumn name is specified on the left The column name can be entered in upper, lower, or mixed-case letters. In this text, columns names are shown in uppercase. Note that even though column names are non-numeric, they are not enclosed in quotes. A value is specified on the right Values must be entered appropriately for their value type — that is, numbers as they would normally be written, strings in single quotes, and dates in the 'yyyy-mm-dd’ format. Values should be of the same type as columns to which they are compared. An operator is specified in the middle Comparison operators are placed between column names and values. Spaces on either side of a comparison operator are allowed, but are not required. However, spaces are not allowed between symbols in multiple symbol operators ( <>, <=, >= ). 14Selecting Specific Rows continued Comparisons are specified in the WHERE clause of a SQL SELECT statement. The WHERE clause must immediately follow the FROM clause. The result table includes all the rows of the source table where the comparison is true. ‘SQL STATEMENT COUNTRY. AREA| USA 3679192 SELECT COUNTRY, AREA Canada 3849674 FROM COUNTRIES China 3696100 WHERE AREA > 3000000 Brazil 3286500 Russia 6592800 SQUSTATEMENT 3 NAME COUNTRY Rand Russia SELECT NAME, COUNTRY Tolstoy Russia FROM PERSONS Chekhov _ [Russia WHERE COUNTRY = 'RUSSIA’ Babel Russia SQL STATEMENT NAME BDATE Dante 1265-03-21 ‘SELECT NAME, BDATE Shikabu_|1000-09-03 FROM PERSONS Augustino [0354-04-30 WHERE BDATE < 1300-01-01" Magnus 1193-12-05 Paul 0013-06-01 The WHERE clause follows the FROM clause Comparisons are entered in the WHERE clause Rows where the comparison is true are displayedSelecting Specific Rows continued The current syntax of a SQL SELECT statement appears below. Note the addition of the WHERE clause. A summary of SQL's comparison operators is also included. SYNTAX SUMMARY CLAUSE |PARAMETERS. EXAMPLE SELECT o Z col list LANGUAGE, COUNTRY FROM table JOBS WHERE col oper value AREA > 3000000 OPERATOR — [MEANING 2 EXAMPLE © = Equal to NAMI INSTEIN' <> Not equal to BDATE <> '1944-05-02' < Less than POP < 100000 <= Less than or equal to NAME <= 'O"Grady’ > Greater than AREA > 999 >= Greater than or equal to | BDATE >= '1962-06-19" Exercises 1, Display all columns and rows in the COUNTRIES table. Now show only countries whose area is less than 30 square miles. There should be 5 rows in the result. 2. Show the name and country of everyone in the PERSONS table. Now limit the result to people who were born in Canada. The final result table contains 6 rows. 3. Show the name and birth date of people born after 1964-01-01. There are 9 rows. Extra Credit 4. What is the birth date of the person named O'Toole?Sorting Rows To sort the rows of a result table, use the ORDER BY clause. This optional clause must be the last clause in a query. Column names, column positions in the SELECT clause, and the keyword DESC (descending) can be entered as parameters. SQUSTATEMENT (COUNTRY AREA Monaco 1 SELECT COUNTRY, AREA Vatican City 7 FROM COUNTRIES 7 [Nauru 8 ORDER BY AREA Tuvalu 9 San Marino 24 SQL STATEMENT z COUNTRY AREA| Monaco 1 SELECT COUNTRY, AREA Vatican City 1 FROM COUNTRIES 1 Nauru 8 ORDER BY 2 Tuvalu 9 San Marino 24 SOL STATEMENT COUNTRY AREA Russia 6592800 SELECT COUNTRY, AREA Canada 3849674 FROM COUNTRIES China 3696100 ORDER BY AREA DESC USA 3679192 Brazil 3286500 The ORDER BY clause must be last Acolumn name or position can be specified DESC indicates a descending sortSorting Rows continued Rows in a result table can be ordered based on the values in more than one column by entering a column list in the ORDER BY clause. The left-to-right order of the columns indicates the major-to-minor sort sequence. SQL STATEMENT S LANGUAGE POP| Afrikaans 1651545 SELECT LANGUAGE, POP Albanian’ 3413904 | FROM COUNTRIES ‘Amharic 55979018 ORDER BY LANGUAGE Arabic 549338 Arabic 65359623 Arabic 533916 Arabic 20643769 ‘SQL STATEMENT e LANGUAGE POP Afrikaans 1651545 SELECT LANGUAGE, POP Albanian 3413904 | FROM COUNTRIES ‘Amharic 55979018 ORDER BY LANGUAGE, POP DESC. Arabic 65359623 Arabic 30120420 Arabic 29168848 Arabic 28539321 A column list is allowed in the ORDER BY clause Column names are separated by commas Columns are listed in major-to-minor sequenceSorting Rows continued The current syntax of a SQL SELECT statement appears below. Note the addition of the ORDER BY clause. The square brackets indicate that DESC is optional. SYNTAX SUMMARY CLAUSE. | PARAMETERS EXAMPLE SELECT a . col list LANGUAGE, COUNTRY FROM table JOBS: WHERE col oper value AREA > 3000000 ORDER BY col [ DESC ] list LANGUAGE DESC, COUNTRY. pos [ DESC J list 4 DESC, 3 Exercises 4. Show all columns of the PERSONS table for people from Ireland. There are 6 such people. Now show them in alphabetical order. 2. Display all columns of the COUNTRIES table for German-speaking countries. The result table contains 4 rows. Now sort by GNP, largest on top. 3. Display the country, job, and name of all Italians in the database, sorted by name within job. There are 10 rows in the result table. Extra Credit 4, Which country has the largest military budget? Which has the smallest? Which has the most troops? the most tanks? ships? planes? 19Eliminating Duplicate Rows Typically, tables do not contain duplicate rows. Queries, however, may result in duplicate rows when a subset of the columns is selected. The keyword DISTINCT is used in the SELECT clause to prevent the display of duplicate rows in a result table. [SQL STATEMENT iF RELIGION: Catholic SELECT RELIGION Catholic FROM RELIGIONS Catholic WHERE PERCENT = 100, Catholic ORDER BY RELIGION E> Eastem Or... Lutheran Muslim Muslim Muslim Sunni Mus... SQL STATEMENT if fe RELIGION Catholic ‘SELECT DISTINCT RELIGION Eastem Or... FROM RELIGIONS > Lutheran WHERE PERCENT = 100 Muslim ORDER BY RELIGION Sunni Mus... DISTINCT is entered after the keyword SELECT DISTINCT eliminates duplicate rows DISTINCT may only be entered once in a query 20Eliminating Duplicate Rows continued The current syntax of a SQL SELECT statement appears below. Note the addition of the optional keyword DISTINCT in the SELECT clause. SYNTAX SUMMARY ‘CLAUSE PARAMETERS [EXAMPLE SELECT - * [DISTINCT } col fist LANGUAGE, COUNTRY FROM table JOBS WHERE col oper value AREA > 3000000 ORDER BY col [ DESC ] fist LANGUAGE DESC, COUNTRY pos [ DESC } list 4 DESC, 3 Exercises 1. Display just the JOB column from the PERSONS table. There are 402 rows (don't bother counting them). Now eliminate the duplicates. Only 7 should remain. 2. Display an alphabetical list of languages where the literacy rate is less than 30 percent. Now eliminate the duplicates. The final result should have 9 rows. 3. List the countries that have produced at least one scientist, sorted by country. Now remove duplicates. Only 10 rows should remain. Extra Credit 4. Make an alphabetical list of religions practiced by less than five percent of their country's population. Eliminate any duplicate rows. 7 rows should remain. 21ADVANCED OPERATORS The LIKE Operator The AND Operator The BETWEEN Operator The OR Operator The IN Operator The IS NULL Operator Precedence and Negation 22The LIKE Operator The LIKE operator is used to find values that match a pattern, Patterns are always entered in quotes. A percent symbol is used to represent zero or more unknown characters; an underscore represents a single unknown character. SQL STATEMENT i NAME COUNTRY. Zola France SELECT NAME, COUNTRY J |Zimbalist_ [USA FROM PERSONS Zwingli___|Sweden WHERE NAME LIKE '2%' Lone ee NAME [COUNTRY Einstein |Germany SELECT NAME, COUNTRY FROM PERSONS, WHERE NAME LIKE 'EINST__N'; ‘SQL STATEMENT NAME COUNTRY Einstein [Germany SELECT NAME, COUNTRY Springsteen|USA FROM PERSONS - Steinbeck |USA WHERE NAME LIKE '%ST__N%! Silverstein [USA LIKE finds values that match a pattern Percent ( % ) represents zero or more characters Underscore (_ ) represents one character 23The LIKE Operator continued The current syntax of a SQL SELECT statement appears below. Note the addition of the LIKE operator in the WHERE clause, and the % and _ characters in the example. SYNTAX SUMMARY | CLAUSE PARAMETERS EXAMPLE SELECT * * [DISTINCT] col list LANGUAGE, COUNTRY FROM table JOBS WHERE col oper value AREA > 3000000 col LIKE pattern NAME LIKE '%ST__N%' ORDER BY col [ DESC jiist LANGUAGE DESC, COUNTRY pos [ DESC Jiist 1 DESC, Exercises 4. Select all countries that include ‘guinea’ in their name. You should find 4 rows. 2. Display all columns for people with the letter z as the second character in their name. There are 2 such individuals. 3. Show all columns for persons born on July 15. There are 6 such people. Extra Credit 4. Find all people with an apostrophe in their name. There are 3 such people. 5. Make a list of religions that contain the word ‘orthodox’ in them. Sort the list, and make sure there are no duplicates. The result contains 10 rows. 24The AND Operator The AND operator is used to combine two comparisons, creating a compound comparison. The keyword AND is placed between the two comparisons. Both comparisons must evaluate to true for the compound comparison to be true. AND GNP <= 2000 [SQL STATEMENT. NAME BDATE. ‘Anne 1950-08-15 SELECT NAME, BDATE Albert |1934-06-06 FROM PERSONS Achebe _|1930-11-16 WHERE NAME LIKE 'A%' Archer __ [1947-08-25 AND BDATE >= '1900-01-01' Azimov 1920-08-22 Andrews [1935-10-01 SQL STATEMENT [COUNTRY GNP) Eritrea 1700 SELECT COUNTRY, GNP Guyana 1400 FROM COUNTRIES Jamaica 1500 WHERE GNP >= 1000 Suriname 1470 AND combines two comparisons AND is placed between two comparisons Both comparisons must evaluate to true 25The AND Operator continued The current syntax of a SQL SELECT statement appears below. Note the addition of the AND operator in the WHERE clause. SYNTAX SUMMARY CLAUSE [PARAMETERS [EXAMPLE SELECT 2 a [DISTINCT] [col fist LANGUAGE, COUNTRY FROM table JOBS WHERE col oper value AREA > 3000000 col LIKE pattern NAME LIKE '%ST__N%' mpr AND cmpr NAME LIKE '%ST__N%' AND JOB = ORDER BY col [ DESC LANGUAGE DESC, COUNTRY pos [ DESC jist 1 DESC, 3 Exercises 1. List all columns for scientists from Germany. You should find 7 scientists. 2. Display all columns for countries with a GNP less than three billion, and a literacy rate less than 40 percent. Remember that GNP is stored in millions of dollars. Sort the result by GNP. The result table contains 7 rows. 3. Show the country name and literacy rate of countries where the literacy rate is between 55 and 60 percent. There are 7 rows. Extra Credit 4. Show all columns for English people born in the 16th century, sorted by name. The result table contains 4 rows. 5. Show all columns for the eight armies that have more than 100,000 troops, less than 1,000 tanks, less than 1,000 ships, and less than 1,000 planes. 26The BETWEEN Operator ‘Some compound comparisons using the AND operator can be more conveniently expressed using the BETWEEN operator. BETWEEN compares each column value with a range of values. The range always includes the end points. SQL STATEMENT COUNTRY | LITERACY] Equatorial. 55 SELECT COUNTRY, LITERACY Guatemala 55 FROM COUNTRIES Congo 57 WHERE LITERACY >= 55 Algeria 57 ‘AND LITERACY <= 60 Ghana 60 ORDER BY LITERACY Iraq 60 Palau 60 [SQL STATEMENT COUNTRY. | LITERACY] Equatorial.. 55 SELECT COUNTRY, LITERACY Guatemala 55 FROM COUNTRIES Congo 57 WHERE LITERACY BETWEEN 55 AND 60 Algeria 57 ORDER BY LITERACY Ghana 60 Iraq 60 Palau 60 BETWEEN compares each column value to a range The range includes both end points The second value must be greater than the first 27The BETWEEN Operator continued The current syntax of a SQL SELECT statement appears below. Note the addition of the BETWEEN operator in the WHERE clause. SYNTAX SUMMARY CLAUSE [PARAMETERS -_ | EXAMPLE SELECT = * [DISTINCT } col list LANGUAGE, COUNTRY FROM, table JOBS WHERE col oper value AREA > 3000000 col LIKE pattern NAME LIKE '%ST__N%' empr AND cmpr NAME LIKE '%ST__N%' AND JOB = col BETWEEN i AND j LITERACY BETWEEN 55 AND 60 ORDER BY col { DESC ] fist LANGUAGE DESC, COUNTRY pos [ DESC 1 DESC, 3 Exercises 1. Using the BETWEEN operator, list all columns for countries with areas greater than or equal to five and less than or equal to 75 square miles. There are 5 rows. 2. Select all columns for countries having between 100,000 and 200,000 people. There are 6 rows in the result. 3. Select all columns for armies whose equipment includes over 300 but less than 400 planes. There are 9 such armies. Extra Credit 4. Select all columns for armies with at least 300,000 troops, whose budget is between 10 and 100 billion dollars. Remember that BUDGET is in millions of dollars. There are 4 such armies. 28The OR Operator The OR operator is used to combine two comparisons, creating a compound comparison. The keyword OR is placed between the two comparisons. Either or both comparisons must evaluate to true for the compound comparison to be true. SQL STATEMENT NAME __|BDATE. Luther 1483-06-15 SELECT NAME, BDATE Calvin 1509-06-04 FROM PERSONS » WHERE NAME = 'LUTHER' OR NAME ='CALVIN' SQL STATEMENT 3 COUNTRY [LANGUAGE Fiji English SELECT COUNTRY, LANGUAGE USA English FROM COUNTRIES Ghana English WHERE COUNTR' OR COUNTRY = OR COUNTRY = ‘FIJI’ OR combines two comparisons OR is placed between two comparisons Either or both comparisons must evaluate to true 29The OR Operator continued The current syntax of a SQL SELECT statement appears below. Note the addition of the OR operator in the WHERE clause. SYNTAX SUMMARY CLAUSE PARAMETERS. EXAMPLE SELECT * 2 [DISTINCT] _[col list LANGUAGE, COUNTRY FROM table JOBS WHERE, col oper value AREA > 3000000 col LIKE pattem NAME LIKE '%ST__N%! ompr AND cmpr NAME LIKE "4ST, AND JOB ='S* col BETWEEN i AND j LITERACY BETWI 5 AND 60. empr OR empr NAME = 'LUTHER' OR NAME = 'CALVIN' ORDER BY — [ool [ DESC J fist LANGUAGE DESC, COUNTRY pos [ DESC J list 1 DESC, 3 Exercises 1. Display all columns in the ARMIES table for Israel and Iraq. 2. Show all columns for people who were born on either 1835-10-19 or 1917-02-06. There are 4 such people... or are there? 3. List names and birth dates for Poe, Hugo, and Dahl. Extra Credit 4. How many people in the database were born in the 13th or 15th century? 30The IN Operator Some compound comparisons using the OR operator can be more conveniently expressed using the IN operator. IN compares each column value with a list of values. The list is enclosed in parentheses; the values are separated with commas. WHERE NAME IN ('POE’, 'HUGO., ‘DAHL' ) SQL STATEMENT NAME BDATE Hugo 1802-08-05 SELECT NAME, BDATE Dahi 1916-09-01 FROM PERSONS Poe 1809-04-09 WHERE NAME = ‘POE’ OR NAME = 'HUGO’ OR NAME = 'DAHL' SQL STATEMENT NAME BDATE Hugo 1802-08-05 SELECT NAME, BDATE Dahl 1916-09-01 FROM PERSONS Poe 1809-04-09 IN compares each column value to a list The list is enclosed in parentheses Values in the list are separated by commas 31The IN Operator continued The current syntax of a SQL SELECT statement appears below. Note the addition of the IN operator in the WHERE clause. ‘SYNTAX SUMMARY (CLAUSE PARAMETERS. 7 [EXAMPLE SELECT * 7 [DISTINCT] col list LANGUAGE, COUNTRY FROM table JOBS WHERE col oper value AREA > 3000000 col LIKE pattem NAME LIKE '%ST__N%' empr AND cmpr NAME LIKE '%ST__N%%' AND JOB ='S' col BETWEEN i AND LITERACY BETWEEN 55 AND 60 ompr OR. cmpr NAME = 'LUTHER' OR NAME = ‘CALVIN’ col IN ( value list ) NAME IN ( ‘POE’, 'HUGO’, 'DAHL') ORDER BY coi { DESC J list LANGUAGE DESC, COUNTRY pos [ DESC J list 4 DESC, 3 Exercises 4. Show all columns for Einstein, Galilei, and Newton, using IN. 2. Display all columns for countries with a literacy rate of 20, 40, or 60 percent. There are 5 such countries. 3. List the names and countries of all scientists in the database who are from Germany, Austria, or Italy. There are 11 rows in the result table. Extra Credit 4. Find all people from Germany, Austria, or Italy who are writers, entertainers, or business leaders. There are 11. Official job identifiers are in the JOBS table. 32The IS NULL Operator A null value is a missing entry in a column. Null means ‘unknown’ or ‘does not apply. Nulls are neither blanks nor zeros (two nulls are not necessarily equal, and you cannot do arithmetic with nulls). The IS NULL operator locates rows with null values. WHERE COUNTRY = 'IRAN' AND BDATE IS NULL SQL STATEMENT z COUNTRY’ POP Monaco + SELECT COUNTRY, POP FROM COUNTRIES WHERE POP IS NULL [SQLSTATEMENT NAME BDATE Darius - SELECT NAME, BDATE Cyrus - FROM PERSONS i> Nulls are missing values Nulls are not the same as blanks Nulls are not the same as zeros IS NULL locates null values 33The IS NULL Operator continued The current syntax of a SQL SELECT statement appears below. Note the addition of the IS NULL operator in the WHERE clause. SYNTAX SUMMARY CLAUSE PARAMETERS. EXAMPLE SELECT 7 7 [ DISTINCT] col list LANGUAGE, COUNTRY FROM table JOBS WHERE col oper value AREA > 3000000 col LIKE pattern NAME LIKE "%ST__N%' cmpr AND cmpr. NAME LIKE '%ST__N%' AND JOB = col BETWEEN i AND j LITERACY BETWEEN 55 AND 60 mpr OR cmpr NAME ='LUTHER' OR NAME = ‘CALVIN’ col IN { value list ) NAME IN ( ‘POE’, 'HUGO’, DAHL") col IS NULL POP IS NULL ORDER BY col [ DESC J list LANGUAGE DESC, COUNTRY pos [ DESC ] fist 1 DESC, 3 Exercises 1. Show all columns for countries whose GNP is unknown. There is 1 such country. 2. Display all columns for entertainers whose gender is null. There are 2 such. 3. Select all columns for Israelis whose birth date is unknown. There are 9 of them. Extra Credit 4. How many countries have an area less than 10 square miles and a GNP greater than 250 million? How many countries have an area less than 10 square miles and a GNP less than or equal to 250 million? Given those results, how many countries would you guess have an area less than 10 square miles? 34Precedence and Negation Parentheses are used to indicate precedence — the order in which comparisons are evaluated. SQL evaluates comparisons enclosed in parentheses first. The keyword NOT is used to negate, or reverse, the result of a comparison. SQL STATEMENT. JOB |NAME 5 ‘Avogadro SELECT JOB, NAME s Fermi FROM PERSONS s Galilei WHERE COUNTRY = ITALY" w Boccaccio AND ( JOB ='S' OR JOB = 'W ) w Dante ORDER BY JOB, NAME Ww Petrarca SQL STATEMENT [JOB NAME E Fabio SELECT JOB, NAME M Epiphani FROM PERSONS M Ptolemio WHERE COUNTRY = ITALY’ T Augustino AND NOT ( JOB ='S' OR JOB ='W') ORDER BY JOB, NAME Comparisons in parentheses are evaluated first NOT is placed in front of a comparison NOT reverses the result of a comparison 35Precedence and Negation continued The current syntax of a SQL SELECT statement appears below. Note the addition of parentheses and the keyword NOT in the WHERE clause. ‘SYNTAX SUMMARY CLAUSE PARAMETERS: EXAMPLE SELECT 7 7 { DISTINCT] col fist LANGUAGE, COUNTRY table JOBS col oper value AREA > 3000000 col LIKE pattern NAME LIKE '%ST__N%' empr AND cmpr NAME LIKE '%ST__N%' AND JOB = 'S’ col BETWEEN i AND j LITERACY BETWEEN 55 AND 60 ompr OR cmpr UTHER' OR NAME = ‘CALVIN’ cot IN (value list) NAME IN ( POE’, ‘HUGO’, 'DAHL' } col 1S NULL POP IS NULL (compound empr ) (JOB = 'S'OR JOB ='W') ORDER BY LANGUAGE DESC, COUNTRY pos [ DESC J list 4 DESC, 3 Exercises 1. Display all columns for Germans who are either theologians or business leaders. Sort the result by name. There are 3 rows. 2. Modify the previous query to find all Germans who are not business leaders or theologians. The result contains 11 rows. Extra Credit 3. How many armies, not including the USA and Russia, have a military budget greater than 30 billion dollars? There are... 36EXPRESSIONS Arithmetic Expressions Expressions in Other Clauses Column Aliases 37Arithmetic Expressions An arithmetic expression is a phrase formed with operands (numeric values and/or column names) and arithmetic operators (shown below). Arithmetic expressions are evaluated by SQL and replaced with the appropriate numeric value. ARITHMETIC OPERATORS (OPERATOR! [MEANING EXAMPLE + Add 2+2 - ‘Subtract BDATE - 365 e Multiply POP * 1.25 7 Divide PERCENT / 100 O Precedence 24(4/2) Operands can be numeric values or column names In an expression, valid operands include numbers, column names, and other expressions. When a column is used in an expression, the values in that column must be numeric or valid dates. Only addition and subtraction are valid with dates. Operators are specified between operands ‘An arithmetic operator is placed between its operands, in the usual way. While spaces are not required on either side of arithmetic operators, spaces are included in the examples above for readability. Expressions in parentheses are evaluated first Arithmetic expressions that are enclosed in parentheses are evaluated by SQL first. The results are then combined with other expressions in the same statement. The placement of parentheses can significantly affect the value of the expression. 38Arithmetic Expressions continued Along with the asterisk and column names, expressions can be specified in the column list of a SELECT clause. SQL inserts a calculated column in the result table at the given position. The new column name defaults to the expression itself. 'SQLSTATEMENT COUNTRY | POP/AREA| Austria 246.66 SELECT COUNTRY, POP / AREA Germany 590.15 FROM COUNTRIES > Liechtenst... 494.41 WHERE LANGUAGE = 'GERMAN' Switzerland 444.47 ORDER BY COUNTRY SQL STATEMENT COUNTRY GNP*1.1 Austria 147840 SELECT COUNTRY, GNP * 1.1 Germany 1464100 FROM COUNTRIES » Liechtenst... 693 WHERE LANGUAGE = 'GERMAN' Switzerland 164010, ORDER BY COUNTRY Expressions are specified in the SELECT clause Expressions produce new columns in the result Expressions are calculated for each row 39Arithmetic Expressions continued The current syntax of a SQL SELECT statement appears below. Note the addition of expressions in the SELECT clause. The arithmetic operators are also summarized. SYNTAX SUMMARY CLAUSE ___ |PARAMETERS. |EXAMPLE SELECT * 7 {DISTINCT } col list LANGUAGE, COUNTRY expr list POP / AREA FROM tabie JOBS WHERE comparisons AREA > 3000000 ORDER BY col { DESC ] list LANGUAGE DESC, COUNTRY pos [ DESC } list 1 DESC, 3 OPERATOR [MEANING [EXAMPLE + Add 2+2 - ‘Subtract BDATE - 365 i Multiply POP * 1.25 i Divide PERCENT / 100 0 Precedence 2+(4/2) Exercises 1. If 20% of Canadians moved to the USA, how many new Americans would that be? 2. How much money is spent per trooper in the USA? in China? 3. Calculate the total number of military vehicles (tanks plus ships plus planes) ‘owned by the United States. Extra Credit 4. 45% of our military budget is spent on $125 hammers. How many do we own? 40Expressions in Other Clauses Since SQL treats expressions in SELECT statements as ‘virtual columns' filled with calculated values, we can use expressions in place of column names in both the WHERE and ORDER BY clauses, as illustrated in the examples below. SQL STATEMENT = COUNTRY | POP/AREA| Maldives 2272.26 SELECT COUNTRY, POP / AREA Malta 3029.58 FROM COUNTRIES > Singapore | 11702.29 WHERE POP / AREA > 2000 Bahrain 2148.97 Bangladesh| 2235.70 ‘SQL STATEMENT COUNTRY | POP/AREA| Singapore | 11702.29 SELECT COUNTRY, POP / AREA Malta 3029.58 FROM COUNTRIES Maldives 2272.26 WHERE POP / AREA > 2000 Bangladesh] 2235.70 ORDER BY POP / AREA DESC. Bahrain 2148.97 Expressions can be used in the WHERE clause Expressions can be used in the ORDER BY clause aExpressions in Other Clauses continues The current syntax of a SQL SELECT statement appears below. Note the addition of expressions in the WHERE and ORDER BY clauses. SYNTAX SUMMARY CLAUSE _— | PARAMETERS | EXAMPLE SELECT 7 * { DISTINCT } col list LANGUAGE, COUNTRY expr list POP / AREA FROM table JOBS WHERE comparisons AREA > 3000000 expr oper value POP / AREA > 300 ORDER BY col { DESC J list LANGUAGE DESC, COUNTRY pos [ DESC Jiist 1 DESC, 3 ‘expr [ DESC ] list POP / AREA DESC Exercises 1. Display the country, population, area, and the population density (POP/AREA) for countries whose population density is less than seven people per square mile. Put the highest population density on top. The result table contains 7 rows. 2. Show the country, population, literacy rate, and number of literate people for all countries with over 100 million literates. To find the number of iterates, divide the literacy rate by 100 and multiply the result by population. There are 7 rows. Extra Credit 3. Let's assume that all armies spend 30 percent of their budget on ashtrays, and that the average military ashtray costs $650. Display each country and the number of ashtrays per soldier, but only if the number of ashtrays per soldier is greater than 10. Put the largest count on top. There are 9 such countries. 42Column Aliases The keyword AS can be used in the SELECT clause to define a column alias — a user-assigned name for a column. Column aliases can be specified for any column, but they are most frequently used to give meaningful names to calculated columns. ‘SQL STATEMENT. COUNTRY | POP/AREA Singapore | 11702.29 SELECT COUNTRY, POP / AREA Malta 3029.58 FROM COUNTRIES > Maldives 2272.26 WHERE POP / AREA > 2000 Bangladesh| 2235.70 ORDER BY POP / AREA DESC Bahrain 2148.97 SQL STATEMENT : : [COUNTRY | DENSITY. Singapore | 11702.29 SELECT COUNTRY, Malta 3029.58 POP / AREA AS DENSITY Maldives 2272.26 FROM COUNTRIES Bangladesh] 2235.70 WHERE DENSITY > 2000 Bahrain 2148.97 ORDER BY DENSITY DESC Column aliases are defined in the SELECT clause Aliases can be used in the WHERE clause Aliases can be used in the ORDER BY clause AS is optional in some dialects 43Column Aliases continued The current syntax of a SQL SELECT statement appears below. Note the addition of the keyword AS and column aliases in the SELECT clause. SYNTAX SUMMARY CLAUSE: PARAMETERS: EXAMPLE SELECT - * [DISTINCT ] col [ AS alias ] list LANGUAGE AS LANG, COUNTRY expr [ AS alias ] list POP / AREA AS DENSITY FROM table JOBS WHERE comparisons AREA > 3000000 expr oper value POP / AREA > 300 ORDER BY col [ DESC jlist LANGUAGE DESC, COUNTRY pos { DESC jlist 1 DESC, 3 expr [ DESC ] list POP / AREA DESC Exercises 1. Show the country, population, literacy rate, and number of literate people for all countries with over 100 million literates. Calll the calculated column READERS, and use this alias wherever appropriate. There are 7 rows in the result. 2. Using a column alias, display the country, population, GNP, and GPP (short for gross personal product = GNP * 1000000 / POP) for countries whose GPP is over 20,000. Sort by GPP descending. The result contains 9 rows. Extra Credit 3. It is proposed that the nations of the world reduce their troops by 20% and apply the savings to nuclear power plants. An average trooper costs $20,000. How much will each country contribute? Call the calculated column FISSION_FUND, put the largest on top, and eliminate countries contributing less than two billion. 44FUNCTIONS Statistical Functions Grouping Functions in Other Clauses 45Statistical Functions A statistical function is a built-in program that accepts a parameter and returns a summary value. The parameter may be either a column name or an expression. The five statistical functions supported by standard SQL are shown in the following table. STATISTICAL FUNCTIONS FUNCTION — [MEANING EXAMPLE COUNT() [Count all rows COUNT (*) ‘Count non-null rows | COUNT (JOB ) ‘Count unique rows COUNT ( DISTINCT JOB ) SUM () Total value ‘SUM (POP) MIN() ‘Smallest value MIN (POP ) MAX() Largest value MAX (POP ) AVG () ‘Average value ‘AVG (POP / AREA ) Statistical functions accept parameters Parameters are either column names or expressions. Parameters must always be enclosed in parentheses. Note that spaces inside and outside the parentheses are optional, but are shown in the examples above for readability. Statistical functions return summary values SUM and AVG can only be used with columns that contain numeric values. COUNT, MIN, and MAX can be used with any type of column. All statistical functions operate ona single column or expression. COUNT accepts a variety of parameters COUNT("*) produces a count of rows. COUNT(column) produces a count of rows where the specified column contains non-null values. COUNT(DISTINCT column) produces a count of unique, non-null values in the given column. 46Statistical Functions continued If a SELECT clause contains nothing but statistical functions, SQL displays grand totals for the query. The resulting table contains one row, with one column for each statistical function. Column aliases may be used to rename the column(s). SQUSTATEMENT i AVG(POP) 16025475 SELECT AVG (POP ) FROM COUNTRIES WHERE LANGUAGE = ‘ENGLISH’ SQL STATEMENT % se LOWEST) HIGHEST 16661 | 263814032 SELECT MIN ( POP ) AS LOWEST, MAX ( POP ) AS HIGHEST E> FROM COUNTRIES WHERE LANGUAGE = ‘ENGLISH’ Only functions are specified in the SELECT clause The result contains only one row The result contains one column for each function Column aliases may be assigned 47Statistical Functions continued The current syntax of a SQL SELECT statement appears below. Note the addition of functions in the SELECT clause. A summary of statistical functions is also shown. SYNTAX SUMMARY CLAUSE: PARAMETERS [EXAMPLE SELECT Z 7 [DISTINCT } col [AS alias } list LANGUAGE AS LANG, COUNTRY ‘expr [ AS alias J list POP / AREA AS DENSITY func [AS alias ] list MIN (POP ) AS LOWEST FROM table JOBS WHERE comparisons AREA > 3000000 ORDER BY col { DESC J list LANGUAGE DESC, COUNTRY pos { DESC] list 1 DESC, 3 expr [| DESC ] list POP / AREA DESC [FUNCTION — [MEANING [EXAMPLE COUNT() [Count all rows ‘COUNT (*) Count non-null rows [COUNT (JOB ) Count unique rows COUNT (DISTINCT JOB ) ‘SUM() Total value ‘SUM (POP) MIN () Smallest value MIN (POP ) MAX() Largest value MAX ( POP ) AVG() Average value AVG (POP /AREA) Exercises 1. Find the total number of troops in the ARMIES table. You should get 17,846,400. 2. Show the minimum, maximum, and average literacy rates for French-speakers. The minimum is 18%, the maximum is 100%, and the average is 51.38%: 3. Find a count of countries (190) and a count of distinct languages (79) in one query. 48Grouping If a SELECT clause contains column names and functions, SQL displays subtotals. The specified columns must also be listed in the GROUP BY clause. SQL divides the table into groups, calculates subtotals for each, and displays one row per group. SQL STATEMENT JOB TOTAL B 28 SELECT JOB, E 113 COUNT (*) AS TOTAL M 36 FROM PERSONS > R 14 WHERE GENDER = 'MALE" s 28 GROUP BY JOB T 13 ORDER BY JOB Ww 99 SQU STATEMENT JOB. COUNTRY TOTAL B England 1 SELECT JOB, COUNTRY, B France 1 COUNT (*) AS TOTAL B Germany 2 FROM PERSONS > |B USA 24 WHERE GENDER = ‘MALE’ E Austria 1 GROUP BY JOB, COUNTRY E Belgium 1 ORDER BY JOB, COUNTRY IE Canada 5 E England 44 Group columns are specified in the SELECT clause Group columns are repeated in the GROUP BY clause GROUP BY follows the FROM and WHERE clauses 49continued Grouping The current syntax of a SQL SELECT statement appears below. Note the addition of the GROUP BY clause following the WHERE clause. SYNTAX SUMMARY CLAUSE PARAMETERS: EXAMPLE — SELECT * ie [DISTINCT] col [ AS alias ] list JOB, COUNTRY expr [AS alias J fist POP / AREA AS DENSITY, func [AS alias ] list MIN (POP ) AS LOWEST FROM table JOBS WHERE comparisons AREA > 3000000 GROUP BY col list JOB, COUNTRY ORDER BY col { DESC J list LANGUAGE DESC, COUNTRY pos [ DESC Jiist 1 DESC, 3 expr [ DESC ] list POP / AREA DESC. Exercises 1. Display each language and the total number of people that speak it. Limit your result to the following languages: Hebrew, Spanish, English, and French. 2. List the minimum, maximum, and average literacy rates for the above languages. 3. In a single query, calculate the number of males and females in each of the following countries: Canada and France. Sort by gender within country. Extra Credit 4. List the number of people of each gender that do each job. Do not include people whose gender is unknown. Sort the result by gender within job. The result table should contain 12 rows. 50