Introduction to SQL—Topics

1. Introduction to SQL—Topics • Introduction to SQL • SQL & Programming • Categories of SQL Statements • The SELECT Query • Limiting columns • Limiting rows with WHERE • Sorting (ordering) results • Renaming the output columns with AS • Computed columns • Functions in SQL

2. What is SQL? • SQL stands for "Structured Query Language" and is a language for manipulating relational databases • Pronounced "S"-"Q"-"L" or "SEQUEL" • ANSI SQL is the 'base' version of SQL • Each manufacturer has their own dialect of SQL • Most differences are in special purpose corners of the language

3. What is SQL (cont.) • SQL is not a programming language • SQL comes with the database • Database engine interprets SQL statements, acts on them, and (if pertinent) returns a result • SQL can be entered directly in most DBMS • SQL can be embedded in a programming language and passed to the database engine • We will learn SQL and then use it in our applications

4. SQL and Programming • SQL represents a division of labor between the database and the program you write • You create the interface for input and output • You translate user needs into SQL 'behind the scenes' and pass SQL to the DBMS • DBMS executes commands • You take results (including errors!) and display them to the user through your interface • SQL can contain complex procedural code in addition to data manipulation statements

5. SQL & Programming (cont.) • You will only rarely enter SQL directly at the DBMS level except for instructional needs and testing • Most have front ends for creating and modifying database structures • In large systems the database administrator (DBA) will do the database structure creation • You will constantly use SQL in programs, though and need to be expert in its use

6. SQL & Programming (cont.) • SQL follows an 80/20 rule • You will use 20% of the language to do 80% of the necessary tasks • You need to be aware of what exists in the other 80% of the language • Sometimes you're going to have one of those 20% tasks • Many employers ask about SQL skills in interviews

7. Categories of SQL Statements • SQL statements are divided into two groups • Data definition language: Create and modify the structure of the database • Tables • Relationships • Rules • Indices • Data manipulation language • Add and delete records • Retrieve records • Update records

8. Categories of SQL Statements (cont) • Data Definition Language • CREATE TABLE statement • CREATE INDEX statement • ALTER TABLE statement • CONSTRAINT clauses • DROP statement

9. Categories of SQL Statements (cont.) • Data Manipulation Language • SELECT statement • INSERT INTO statement • UPDATE statement • DELETE statement • JOIN operations • INNER JOIN, LEFT JOIN, RIGHT JOIN, OUTER JOIN • WHERE, ORDER BY, & HAVING clauses

10. The SELECT Statement (Query) • SELECT provides the mechanism for retrieving data from the database • Can assemble data together from multiple tables into logical records • Can limit which records you retrieve • Can limit which fields you retrieve • Can perform calculations and display the results along with 'raw' data values • SELECT will be a workhorse in your database programming and you should understand it thoroughly

11. Introduction To the Query Editor

12. SELECT Statement—Simple Example SELECT SQL keyword Return all fields (columns) Note that SQLkeywords arein all capital letters SELECT *FROM Suppliers; Indicates only Supplierstable is used FROM SQL keyword indicatestables involved in the query This query returns all fields from all records in the Suppliers table

13. SELECT Statement—Exercise • Change active database toNorthWind • Enter the query below andclick Execute or press "F5" SELECT * FROM Products

14. Query Results Pane Editingwindow Results PaneRecordsreturned (Ctrl-R will eliminate the results pane after a query has run)

15. The SELECT Clause—Limiting Output Columns SELECT CompanyName, Phone, FaxFROM Suppliers; • SELECT keyword may be followed by a list of fields or other expressions • Only these fields will be returned in the result set! • Fields may be specified in any order (very important)

16. Exercises • Enter and run the following queries • Write the query to return just Customer company names and countries SELECT ProductID, ProductName, UnitsInStock FROM Products SELECT ProductName, ProductID, UnitsInStock FROM Products

17. The WHERE Clause—Limiting Records • The WHERE clause establishes a condition that can be tested to be either True or False for any record • Usually tests value of one or more fields • Only the records whose values are TRUE for the specified WHERE clause criteria are returned in the result set SELECT CompanyName, Phone, FaxFROM SuppliersWHERE Country = 'USA';

18. The WHERE Clause—Limiting Records (cont.) • String and Date literal values must be delimited • SQL Server uses single quotes as delimiters for all data types needing delimiters • Some databases use different delimiters • String values in single or double quotes • Date values in pound signs (#) or single quotes • Numeric literals do not use delimiters

19. The WHERE Clause—Compound Tests • The WHERE Clause can contain complex tests • Entire test must evaluate to True for record to be displayed • Use logical operators • NOT • AND or OR • Parenthetical grouping • <> (notequal), =, <, <=, >, >= • Follows the same precedence rules as compound logical tests in VB programming SELECT *FROM SuppliersWHERE Country = 'USA' OR Country = 'Germany';

20. Exercises • List all of the customers in Germany • List all of the products where the units in stock are less than the reorder point • List all products in Category 2 • List all products in Category 2 where the units in stock is less than 20 but do not list the product if it has been discontinued (Discontinued = 1 indicates the product has been discontinued)

21. Some Interesting Tricks • Run each of these queries: SELECT 4 SELECT 4, 'Test Value', ProductName FROM Products SELECT * FROM Products WHERE 4 = 4

22. Reordering Query Output with ORDER BY • Run these four queries: SELECT ProductID, ProductName, UnitPrice FROM Products SELECT ProductID, ProductName, UnitPrice FROM Products ORDER BY ProductName SELECT ProductID, ProductName, UnitPrice FROM Products ORDER BY ProductName DESC SELECT ProductID, ProductName, UnitPrice FROM Products ORDER BY SupplierID

23. Reordering Query Output with ORDER BY (cont) SELECT ProductID, ProductName, SupplierID FROM Products SELECT ProductID, ProductName, SupplierID FROM Products ORDER BY SupplierID SELECT ProductID, ProductName, SupplierID FROM Products ORDER BY SupplierID, ProductName

24. Aliasing Column Names with AS SELECT CustomerID, CompanyName FROM Customers SELECT CustomerID, CompanyName AS 'Company Name' FROM Customers SELECT CustomerID, CompanyName AS Company FROM Customers SELECT CustomerID, CompanyName AS Company Name FROM Customers Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'Name'.

25. Calculated Columns SELECT ProductID, ProductName, UnitsInStock * UnitPrice AS 'Value' FROM Products ORDER BY Value SELECT LastName, FirstName, LastName + ', ' + FirstName AS 'Employee Name' FROM Employees Always provide a column name to a calculated column with the AS expression

26. Functions • SQL Server has many intrinsicfunctions that may be used inSQL statements • We will be mixing these inwith examples throughout thelab • Browsing these on your owncan pay off for you • Pay special attention to dateand time functions!!!

27. Some Date Functions in Action SELECT GetDate() SELECT DatePart(mm, GetDate()) SELECT DateName(mm, GetDate()) SELECT OrderID, OrderDate, ShippedDate, DateDiff(dd, OrderDate, ShippedDate) AS 'Fulfillment Time' FROM Orders WHERE ShippedDate IS NOT NULL • List all of the order information where the orders took more than five days to ship

28. Commenting SQL Statements Block comment/* … */ CREATE PROCEDURE [dbo].[up_Order_Invoice_OrderInfo] /********************************************************** SP: up_Order_Invoice Takes an OrderID as a parameter and returns all data needed to create an invoice for the order except order details Must work with the SP up_Order_Invoice_Details to create all order invoice information. Created by Dr. Larry West, MIS Department, UCF Last Modified: 9 January 2007 **********************************************************/ --Create parameter @OrderID int AS --Execute the query SELECT CUSTOMERS.*, ORDERS.*, EMPLOYEES.LastName, EMPLOYEES.FirstName FROM CUSTOMERS, ORDERS, EMPLOYEES WHERE ORDERS.OrderID = @OrderID AND ORDERS.CustomerID = CUSTOMERS.CustomerID AND ORDERS.EmployeeID = EMPLOYEES.EmployeeID Single line comment-- (two hyphens)