Learning Unit 4 Basic Structured Query Language SQL

Learning Unit 4 Basic Structured Query Language (SQL) Mount Vernon Data Systems LLC dedicated to keeping your SQL Server-powered business systems running

History of SQL IBM, 1970, San Jose Research Facility, E. F. Codd developed theory of the relational model and several SQL language prototypes “A Relational Model of Data for Large Shared Data Banks” IBM, 1974, D. D. Chamberlain developed the first SEQUEL-XRM implementation (Structured English Query Language - SEQUEL) IBM, 1977, implements System R & SEQUEL/2 (also called SQL) in research labs Oracle, 1979, released to market first commercial RDBMS based on SQL IBM, 1981, SQL/DS, IBM’s first commercial RDBMS Data General, 1982, Data General SQL/DS Sybase, 1986, SQL Server RDBMS & Transact-SQL 1993, co-development agreement with Microsoft ends, SQL Server becomes Microsoft SQL Server & Sybase Adaptive Server Enterprise both use Transact-SQL (T-SQL) as native language American National Standards Institute (ANSI) made SQL the industry standard access language for RDBMSs SQL-1, 1986 (also called SQL ‘ 86) SQL-1, revision 1, 1989 (also called SQL ‘ 89) SQL-2, 1992 (also called SQL ‘ 92) SQL-3, 1999 SQL-2003, SQL-2006, SQL-2008 (http: //en. wikipedia. org/wiki/Structured_Query_Language) © Mount Vernon Data Systems LLC 2010 All Rights Reserved 2

What is SQL? Interactive SQL An interactive 4 GL used to synchronously access data which is maintained by a relational or near-relational DBMS. Static SQL a programming language which can be embedded within a 3 GL such as VB in order to access and manipulate data from an RDBMS in asynchronous mode. Dynamic SQL is an interactive or batch programming language which can accept variables input from the user interface SQL has Data Definition Language commands (CREATE, ALTER, DROP) and Data Manipulation Language commands (ADD, DELETE, UPDATE) SQL is a set-level language a command operates on a set of records, rather than on one record at a time, as 3 GL programming languages do. SQL is a non-procedural language specify only what you want done, not how to do it is the job of the RDBMS to figure out how to get the job done © Mount Vernon Data Systems LLC 2010 All Rights Reserved 3

SQL Vocabulary (review) SQL uses terminology which was developed around the relational model of database management systems Relational Model RDBMS Terms Common Terms relation table file tuple row record attribute column field primary key, unique identifier candidate key alternate key foreign key secondary key © Mount Vernon Data Systems LLC 2010 All Rights Reserved 4

SQL Commands Data Manipulation Language (DML) allows users to retrieve, add, modify or destroy data in the database select insert, update, delete Data Definition Language (DDL) creates and maintains an SQL environment (data storage, rules & constraints) create alter drop Data Control Language (DCL) allows users to protect & control access to data by enforcing security & visibility rules grant revoke commit rollback Not all dialects of SQL are created equal. Different RDBMSs have added extensions to their specific version of the SQL language SQL Server has Transact-SQL Oracle has SQL*Plus and PL/SQL specific dialects of SQL can be more flexible & powerful than ANSI Std SQL © Mount Vernon Data Systems LLC 2010 All Rights Reserved 5

SQL Server 2005 Data Types char Fixed-length, non-Unicode character data with a length of n bytes; n is a value from 1 - 8, 000. The storage size is n bytes. varchar() Variable-length, non-Unicode character data; n is a value from 1 - 8, 000. The storage size is the actual length of data entered + 2 bytes. Can be 0 characters in length. varchar(max) Indicates that the maximum storage size for the varchar data type is 2^31 -1 bytes. nvarchar() Fixed-length Unicode character data of n characters. n is a value from 1 through 4, 000. The storage size is two times n bytes. nvarchar(max) Indicates that the maximum storage size for the nvarchar data type is 2^31 -1 bytes. text Variable-length non-Unicode data in the code page of the server and with a maximum length of 2^31 -1 (2, 147, 483, 647) characters. When the server code page uses double-byte characters, the storage is still 2, 147, 483, 647 bytes. Depending on the character string, the storage size may be less than 2, 147, 483, 647 bytes. ntext Variable-length Unicode data with a maximum length of 2^30 - 1 (1, 073, 741, 823) characters. Storage size, in bytes, is two times the number of characters entered. image Variable-length binary data from 0 through 2^31 -1 (2, 147, 483, 647) bytes. binary Fixed-length binary data with a length of n bytes, where n is a value from 1 through 8, 000. The storage size is n bytes. varbinary Variable-length binary data. n can be a value from 1 through 8, 000. max indicates that the maximum storage size is 2^31 -1 bytes. The storage size is the actual length of the data entered + 2 bytes. The data that is entered can be 0 bytes in length. datetime Represent the date and the time of day from January 1, 1753, through December 31, 9999 with an accuracy of 3. 33 milliseconds. smalldatetime Represent the date and the time of day from January 1, 1900, through June 6, 2079 with an accuracy of 1 minute. int Exact-number data types that use integer data with a value range of -2^31 (-2, 147, 483, 648) to 2^31 -1 (2, 147, 483, 647) tinyint Exact-number data types that use integer data with a value range of 0 to 255 smallint Exact-number data types that use integer data with a value range of -2^15 (-32, 768) to 2^15 -1 (32, 767) bigint Exact-number data types that use integer data with a value range o-2^63 (-9, 223, 372, 036, 854, 775, 808) to 2^63 -1 (9, 223, 372, 036, 854, 775, 807) © Mount Vernon Data Systems LLC 2010 All Rights Reserved 6

SQL Server 2005 Data Types (continued) decimal Numeric data types that have fixed precision and scale. Precision is the total number of digits stored to the left and right of the decimal point (max 38, default 18); scale is the max number of digits stored to the right of the decimal point, default = 0. numeric Numeric data types that have fixed precision and scale. Precision is the total number of digits stored to the left and right of the decimal point (max 38, default 18); scale is the max number of digits stored to the right of the decimal point, default = 0. money Data type that represents monetary or currency values, ranges from -922, 337, 203, 685, 477. 5808 to 922, 337, 203, 685, 477. 5807 smallmoney Data type that represents monetary or currency values, ranges from - 214, 748. 3648 to 214, 748. 3647 float Approximate-number data types for use with floating point numeric data with a range of - 1. 79 E+308 to -2. 23 E-308, 0 and 2. 23 E-308 to 1. 79 E+308. real Approximate-number data types for use with floating point numeric data with a range of - 3. 40 E + 38 to -1. 18 E - 38, 0 and 1. 18 E - 38 to 3. 40 E + 38. cursor A data type for variables or stored procedure OUTPUT parameters that contain a reference to a cursor sql_variant A data type that stores values of various SQL Server 2005 -supported data types, except text, ntext, image, timestamp, and sql_variant. table Is a special data type that can be used to store a result set for processing later. table is primarily used is for temporary storage of a set of rows returned as the result set of a table-valued function. timestamp Is a data type that exposes automatically generated, unique binary numbers within a database. timestamp is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. uniqueidentifier A column or local variable of uniqueidentifier data type can be initialized to a value by using the NEWID function or by converting from a string constant; used to guarantee that rows are uniquely identifiable across multiple copies of the same table in a replication environment. XML used to store XML data © Mount Vernon Data Systems LLC 2010 All Rights Reserved 7

Getting Started with Management Studio Open Microsoft SQL Server Management Studio Connect to the local server Click “New Query” from the top toolbar In the query workspace type the following: use pubs go Click “execute” from the top toolbar by default the query workspace splits into two sections • upper section – your query • lower section – the query results you should see the following message “Command(s) completed successfully. ” pubs should be listed as the working database in the top toolbar © Mount Vernon Data Systems LLC 2010 All Rights Reserved 8

DML: General SELECT Syntax The overall syntax of SQL is simple and very English-like: SELECT. . . FROM. . . WHERE. . . GROUP BY. . . • HAVING. . . ORDER BY. . . It is possible to phrase an SQL query incorrectly and have it run nonetheless… the “do what I meant, not do what I said” syndrome One of two things will happen you’ll get no data returned, which can lead to incorrect assumptions you’ll get the answer to a question you hadn’t realized you asked! © Mount Vernon Data Systems LLC 2010 All Rights Reserved 9

Select All Columns “Show me all the people in the authors table. ” select * from dbo. authors; © Mount Vernon Data Systems LLC 2010 All Rights Reserved 10

Select a Single Column “Give me a list of author last names. ” select au_lname from dbo. authors; Notice that the column heading “inherits” the attribute or column name. This is the default unless you specify a column alias. If there are duplicate values in the selection (for instance, three people with the last name of Smith), and you only want to see a single entry instead of duplicates, use the following construct: select distinct au_lname from dbo. authors; © Mount Vernon Data Systems LLC 2010 All Rights Reserved 11

Select More Than One Column “Give me a list of author first and last names. ” select au_fname, au_lname from dbo. authors; Place a comma between each entry in the column list. Columns do not have to be selected in the same order as they’re stored in the table. © Mount Vernon Data Systems LLC 2010 All Rights Reserved The order in which you select the columns determines the display order. 12

Column Aliases: Make It Look Nice “Give me a list of author first and last names. ” select au_fname AS 'First Name', au_lname AS 'Last Name' from dbo. authors; To display a different column heading from the default, specify a column alias in the SELECT clause. © Mount Vernon Data Systems LLC 2010 All Rights Reserved You can use this alternate method of specifying column aliases: select First_Name = au_fname, Last_Name = au_lname from dbo. authors; 13

Select a Row “Give me information about the sushi book. ” select title, title_id, type, price, pubdate from dbo. titles where title LIKE 'Sushi%'; Use the WHERE clause and restrict the data returned to only those rows needed or wanted. The attribute specified in the WHERE clause does not have to be present in the SELECT list. © Mount Vernon Data Systems LLC 2010 All Rights Reserved The LIKE operator does a pattern match. Use the LIKE operator when you want to match a character string, as in this case, the word “Sushi”. Character strings are always enclosed with quotes, either single or double, depending on your specific environmental setup options. SQL Server default behaviour is single quotes. 14

Select Rows With A Single Condition Using the WHERE clause, you can compare any column value to : a character string pattern match, by using quotes, as in where title like 'Sushi%'; where pubdate > '1991 -12 -31'; Depending on how your server has been configured, character string matching may be case-sensitive; in that case, ‘Sushi' and ‘sushi’ will not return the same results. an exact match of character data, using quotes where title = 'Net Etiquette‘ a numeric constant, with no quotes, as in where pub_id = 0877 © Mount Vernon Data Systems LLC 2010 All Rights Reserved 15

Logical Operators in the WHERE clause equal to Like, = not equal to NOT LIKE, !=, <> greater than or equal to >= less than < less than or equal to <= equal to any member in the following list IN (item 1, item 2, item 3, item 4) a range of values BETWEEN low_value AND high_value matches the following pattern LIKE find where the value is missing or not applicable IS NULL reverse any of the operators previously listed NOT © Mount Vernon Data Systems LLC 2010 All Rights Reserved 16

Select Using IN “Give me a list of business and psych books. ” select title, title_id, type, price, pubdate from dbo. titles where type IN ('business', 'psychology'); © Mount Vernon Data Systems LLC 2010 All Rights Reserved 17

Select Using NOT IN “What other types of books do we have besides business and psychology? ” select title, title_id, type, price, pubdate from dbo. titles where type NOT IN ('business', 'psychology'); © Mount Vernon Data Systems LLC 2010 All Rights Reserved 18

Select a Range of Values “What titles do we have in the $10 to $20 price range? ” select title, title_id, type, price, pubdate from dbo. titles where price BETWEEN $10 AND $20; The BETWEEN operator is inclusive; $10 and $20 prices will be included in the result set. The NOT BETWEEN operator is exclusive; it lets you select prices that are outside the $10 -$20 price range. © Mount Vernon Data Systems LLC 2010 All Rights Reserved 19

The Wild Card Characters The wild card character (%) takes the place of a part of the character string; it’s used in pattern matches, to find specific rows that meet less than exact search criteria. select * from authors where au_fname LIKE 'A%'; select * from authors where au_fname LIKE 'Ann%'; To match a single character in a string use the underscore (_) character. select * from authors where au_fname LIKE 'Ann_'; select * from authors where au_fname LIKE 'A_n%'; © Mount Vernon Data Systems LLC 2010 All Rights Reserved 20

NULL and NOT NULL “Are any of the titles missing a price? ” select * from dbo. titles where price IS NULL; NULL simply means that a value is unknown, is missing, or is not relevant. NULL is not the same as the integer value zero, nor is it the character value BLANK. NULL doesn't have a value. Any attribute can have a NULL assigned to it. NULL is the same for all attribute types -- character, numeric, date, etc. NULL simply indicates the absence of a value. “Get the year-to-date sales figures for each title. ” select * from dbo. titles where ytd_sales IS NOT NULL; © Mount Vernon Data Systems LLC 2010 All Rights Reserved 21

Select Multiple Conditions Using AND “List out all authors whose first name begins with the letter ‘A’ and who live in California. ” select * from authors where au_fname like 'A%' AND state = 'CA'; The AND operator adds additional criteria in the WHERE clause which all rows in the result set must meet. © Mount Vernon Data Systems LLC 2010 All Rights Reserved 22

Select Multiple Conditions Using OR “List out all authors whose first name begins with the letter ‘A’ or who live in California. ” The rows in the result set need meet only one of the OR criteria specified in the WHERE clause. © Mount Vernon Data Systems LLC 2010 All Rights Reserved select * from authors where au_fname like 'A%' OR state = 'CA'; 23

Using AND and OR Together “List out all authors whose first name begins with the letter ‘A’ and who live in California, or whose first name begins with the letter ‘M’. ” Both AND and OR operators can be used in the same WHERE clause. An AND will be evaluated before an OR. To establish precedence (which of the conditions should be evaluated together), use parentheses. © Mount Vernon Data Systems LLC 2010 All Rights Reserved select * from authors where (au_fname like 'A%' AND state = 'CA') OR au_fname like 'M%'; …significant difference in the result set when you shift the parentheses… select * from authors where au_fname like 'A%' AND (state = 'CA' OR au_fname like 'M%‘); 24

DML: the INSERT command A table must exist before you can add rows to it. . . sp_help jobs The column list and the value list must be synchronized… Whenever modifying data use explicit transaction language. . begin tran insert into jobs (job_desc, min_lvl, max_lvl) values ('test rec', 100); commit -- rollback Each data value added must be consistent with the data type of the targeted column, that is, you cannot insert a character string into a number data type. © Mount Vernon Data Systems LLC 2010 All Rights Reserved Separate data values in the value list with a comma… Enclose character strings & dates in the value list in single quotes. 25

Insert NULL You do not need to specify the column list as long as the value list is consistent with the order of the columns in a SELECT query. Whenever modifying data use explicit transaction language. . If a column has a default value (pubdate = getdate()), accept the default by not specifying it in the value list. You don’t need to specify the column list as long as the value list is consistent with the order of the columns in a SELECT query. If a column is nullable, you can simply indicate NULL in the value list. © Mount Vernon Data Systems LLC 2010 All Rights Reserved begin tran insert into titles (title_id, title, type, pub_id) values ('zzy_id', 'test title', 'y_type', '0877'); commit -- rollback The columns which have no explicit value specified in the value list will be NULL. begin tran insert into titles values ('zzz_id', 'test title', 'z_type', '0877', NULL, NULL, '2009 -09 -07'); commit -- rollback 26

Insert a Row from Another Table The INSERT command can be used to select and copy rows from one table into another… The VALUE clause has been replaced by a SELECT statement, which can define and delimit the rows and columns which will be copied over from the archive table. © Mount Vernon Data Systems LLC 2010 All Rights Reserved Whenever modifying data use explicit transaction language. . begin tran insert into sales select stor_id, ord_num, ord_date, qty, payterms, title_id from sales_archive where ord_date > '1984 -01 -01'; commit; -- rollback The source and target columns for the copy procedure must be the same data types; the column names do not have to be the same. 27

DML: the UPDATE command “Change the order date for this order – back date it by 10 days. ” You can change the value of more than one column in a update statement, just specify with a comma list in the update statement. You can include more than one update statement in a transaction, just end each update with a semi-colon. You can change all ord_date values at one time by eliminating the WHERE clause – the global search & replace. © Mount Vernon Data Systems LLC 2010 All Rights Reserved begin tran update sales set ord_date = dateadd(dd, -10, ord_date) where (stor_id = 6380 AND ord_num = 6871 AND title_id = 'BU 1032'); commit --rollback …set ord_date = dateadd(dd, -10, ord_date), payterms = 'Net 30‘… begin tran update sales set payterms = 'Net 10‘; commit -- rollback 28

DML: the DELETE command “Could you remove Marjorie Green’s book ‘The Busy Executive’s Database Guide’ from our title-author list? ” begin tran delete from dbo. titleauthor where (au_id = '213 -46 -8915' AND title_id = 'BU 1032'); commit -- rollback The WHERE clause controls the behaviour of the DELETE operation; forget the WHERE clause and all rows in the table will be deleted. begin tran delete from dbo. titleauthor; commit -- rollback You can only delete entire rows. To “delete” a column value, use the UPDATE operation. © Mount Vernon Data Systems LLC 2010 All Rights Reserved 29

DML: Joins, the Basic Concept To retrieve data from two or more tables with a single SQL query use the construct known as a JOIN. Two or more tables are JOINed together on a common column (in this case, stor_id). These JOIN columns must have the same data type and length, but do not need to have the same column name. dbo. Store dbo. Sale The JOIN is not a stored structure; the table of results is dynamically created and lasts only for the lifetime of the JOIN query. © Mount Vernon Data Systems LLC 2010 All Rights Reserved 30

Join Types INNER JOIN/Equijoin The standard equijoin; hails from the SQL ‘ 86 -’ 89 standard LEFT OUTER JOIN Outputs all rows from the first table whether or not there is a match in the second table. NULL is used to pad out missing cdata. RIGHT OUTER JOIN Outputs all rows from the second table whether or not there is a match in the first table. NULL is used to pad out missing data. FULL OUTER JOIN Outputs all rows from both tables, whether or not there is a match. NULL is used to pad out missing data. CROSS JOIN The Cartesian product, that is, a JOIN with no join phrase. © Mount Vernon Data Systems LLC 2010 All Rights Reserved 31

The Inner Join/Equijoin There are two ways to write a join, depending on which SQL standard you are following: The SQL ‘ 86, ‘ 89 standard: select p. pub_id, p. pub_name, p. city, t. title from dbo. publishers p JOIN dbo. titles t ON p. pub_id = t. pub_id ; The SQL ’ 92 and beyond standard: Table alias: a dynamical object declared in the query; it represents a table and avoids ambiguity when two tables have the same column name. select p. pub_id, p. pub_name, p. city, t. title from dbo. publishers p, dbo. titles t where p. pub_id = t. pub_id ; The term Equijoin is commonly used to indicate a situation of equality, or equivalency, between the values of the two columns which are being used to JOIN the tables. © Mount Vernon Data Systems LLC 2010 All Rights Reserved 32

Triple Join select * from dbo. authors ; select * from dbo. titleauthor; You will need to use at least one join phrase for each pair of tables you JOIN. In this case, since you are JOINing three tables you will need at least two join phrases: select a. au_fname, a. au_lname, t. title, t. type, t. price, t. ytd_sales from dbo. authors a JOIN dbo. titleauthor ta ON a. au_id = ta. au_id JOIN dbo. titles t ON ta. title_id = t. title_id; © Mount Vernon Data Systems LLC 2010 All Rights Reserved 33

Self Join To correlate rows of a table with other rows from the same table, a table can be JOINed to itself. select e. ename AS ‘Employee’, m. ename AS ‘Manager’ from dbo. employee e JOIN dbo. employee m ON e. emp_no = m. mgr_no; Table aliases must be used; a single table is referenced more than one time in the same query. A different alias name is used for each reference to the table. The table aliases used with the column names in the SELECT clause indicate which occurrence of the table to associate each column with. The column aliases used in the SELECT clause help clarify the result set. © Mount Vernon Data Systems LLC 2010 All Rights Reserved 34

The Left Outer Join Outputs all rows from the first table whether or not there is a match in the second table. NULL is used to pad out missing data. select p. pub_id, p. pub_name, p. city, t. title from dbo. publishers p LEFT OUTER JOIN dbo. titles t ON p. pub_id = t. pub_id ; © Mount Vernon Data Systems LLC 2010 All Rights Reserved 35

The Right Outer Join Outputs all rows from the second table whether or not there is a match in the first table. NULL is used to pad out missing data. select s 1. stor_id, s 1. ord_date, s 1. qty, s 2. stor_name, s 2. state from dbo. sales s 1 RIGHT OUTER JOIN dbo. stores s 2 ON s 1. stor_id = s 2. stor_id ; © Mount Vernon Data Systems LLC 2010 All Rights Reserved 36

The Full Outer Join Outputs all rows from both tables, whether or not there is a match. NULL is used to pad out missing data. select j. job_desc, e. emp_id, e. fname + ' ' + e. lname AS 'Full Name' from dbo. jobs j FULL OUTER JOIN dbo. employee e ON j. job_id = e. job_id ; © Mount Vernon Data Systems LLC 2010 All Rights Reserved 37

The Cross Join The Cartesian product, that is, a JOIN with no join phrase. select s 1. stor_id, s 1. ord_date, s 1. qty, s 2. stor_name, s 2. state from dbo. sales s 1 CROSS JOIN dbo. stores s 2 ; The total number of rows in the result set will be rt 1 * rt 2, where rt is the number of rows in a table, and the superscripts represent the tables in the cross join. © Mount Vernon Data Systems LLC 2010 All Rights Reserved 38

The Author… Michelle A. Poolet, MCIS, University of Denver Zachman Certified™ - Enterprise Architect Contributing Editor, SQL Server Magazine Adjunct faculty, University of Denver, University College President/co-founder of Mount Vernon Data Systems – database consulting company specializing in database systems, data modeling, courseware development, education/training. Michelle@Mount. Vernon. Data. Systems. com Hanging out near the beach at Kailua. Kona, Hawaii…aloha, everyone! © Mount Vernon Data Systems LLC 2010 All Rights Reserved 39