CIS 451: SQL

1. CIS 451: SQL Dr. Ralph D. Westfall February, 2009

2. What Is SQL? • Structured Query Language • standard data access language for relational databases (RDBMS) • RDBMS dominate market • Access, Oracle, Sybase, DB2, SQL Server, etc.

3. SQL Standardization Advantages • greater productivity • reduced training costs • application portability • less dependence on specific vendors • easier integration of separate systems adapted from McFadden & Hoffer, 1994

4. Can Put SQL Into Programs • "embedded" SQL can go into code in • C, C++, C# • Java • Perl, Tcl • COBOL, FORTRAN, Ada • Visual Basic.NET • ASP.NET

5. SQL Command Types • data manipulation language (DML) works with records and fields • get raw data from table(s) in database(s) • get summary data (total, average, count, etc.) • add, change, or delete records • data definition language (DDL) works with tables • create, change, or delete tables • create or delete indexes

6. SQL Command Parts (DML) • manipulative – tell what to do • SELECT … FROM – get records • INSERT INTO … – add records • DELETE FROM … – removes records • UPDATE … SET … – change records

7. SQL Command Parts - 2 • options declarations – tell where and how the action will occur • WHERE – sets conditions (like an IF clause) • ORDER BY – sets sort order e.g., ascending • GROUP BY – useful when combining data e.g., total or average for each separate item, such as sales by salespersons or countries

8. SQL Command Parts - 3 • parameter declarations • optional • at start (before manipulative part) • contain values passed into the query • like parameters in a subprocedure

9. SQL Select Command • SELECT [field list] FROM [table list} SELECT * FROM Customer • get all fields from Customer table SELECT Name, Address FROM Customer • get only Name and Address fields SELECT [Zip Code] FROM Customer • use square brackets if space(s) in a name

10. Sorting SELECT * FROM Sales ORDER BY Date • sorted ascending (from earliest to latest) SELECT Name, City FROM Students _ ORDER BY City • sorted ascending (alphabetically: A to Z)

11. Sorting - 2 SELECT Product, Price FROM Sales _ ORDER BY Price DESC, Quantity • Price sorted descending (highest first) • when Price is same, then sorted ascending by Quantity (lowest to highest) Product Price [Quantity] carrot 0.29 [7] radish 0.27 [2] beet 0.27 [3] corn 0.25 [1]

12. "Filter" Conditions • tell which records to include in results • WHERE logical-expression test-value WHERE Count < 10 • 6 comparison operators in SQL < less than <= less than or equal = equal <> not equal > greater >= greater than or equal

13. Filter Conditions - 2 • special "predicates" WHERE Name LIKE 'Ng*' WHERE State IN ('CA', 'NY') WHERE Count BETWEEN 1 AND 5

14. Wild Cards in Filter Conditions • * one or more characters: s* so, solitude • ? single character: st?r star, stir • # single digit: #123 9123 7123 0123 • [start-end] characters: [b-d]ot bot cot dot • [letters]: [cfhms]at cat fat hat mat sat • ! not: b[!au]ll bell bill boll (not ball or bull) • combination: [mt]o?n* moonlight tornado

15. Getting Calculated Fields • calculation operators used on fields SELECT Quantity * Price FROM Orders SELECT FirstName & ' ' & LastName … 'concatenates names together with space • system creates name for above examples • can specify name for new field SELECT FirstName & ' ' & LastName _ AS WholeName FROM Students

16. Getting Calculated Fields - 2 • functions used on fields • Avg (average), Count, Min (smallest), Max (largest), Sum (total), etc. SELECT Sum(Quantity) FROM Orders SELECT Max(Quantity * Price) _ FROM Orders

17. Grouping to Summarize • can use functions with GROUP BY to get summary values SELECT State, Sum(Quantity) _ FROM Orders GROUP BY State • gets totals: 1 record for each state with data SELECT State, City, Sum(Quantity) _ FROM Orders GROUP BY State, City • total for each city with data, by state

18. Creating Table from a Query SELECT Name, Phone INTO Locals _ FROM Clients WHERE City='Pomona' • creates a new table named Locals with selected data from other table

19. Using Parameters PARAMETERS strCity String; _ SELECT * FROM Clients _ WHERE City=strCity • put Parameters at start, ending with ; • passed in variable value is substituted into query

20. Selecting from Multiple Tables • need to identify • fields • tables • relationship between tables SELECT Students.Name, Class, Grade _ FROM Students, Courses _ WHERE Students.SSNo = Courses.SID • can identify tables for all fields if > 1 table

21. Multiple Table Relationships • inner join • at least 1 field matches in both databases SELECT Students.Name, Class, Grade _ FROM Students, Courses _ Students INNER JOIN Courses ON Students.SSNo = Courses.SID • to get a record, a value for SSNo in Students table must be same as a value for SID in Courses table

22. Multiple Tables - 2 • inner join (continued) • results table • contains data from both tables in each row • WHERE clause can also create an inner join, if it matches fields in separate tables WHERE Students.SSNo = Courses.SID

23. Multiple Tables - 3 • outer join • gets all records, even if don't match on the field • MS Access allows outer joins on only 1 side • tables could get very large if outer join on both • left join – all records from left table, matching records from other (right) table • right join – all records from right table

24. Multiple Tables - 4 • one-sided (left or right) outer join • syntax SELECT Employees.Name, Orders.Product FROM Employees LEFT JOIN Orders ON Employees.Emp_ID=Orders.Emp_ID • results show all employees (left table), even ones who haven't ordered any products yet (from W3Schools)

25. SQL Insert Command • INSERT INTO Product _ • (ProdID, Product, Price, InStock) _ VALUES ('0008', 'snark', 6.99, 120) • adds a record • first set of parentheses enclose field names • second set of parentheses enclose field values • have to be in exactly the same order • ProdID is '0008' … Instock is 120

26. SQL Update Command • UPDATE Product SET Instock = 20 _ • WHERE ProdID = '0001' • updates value of Instock field to 20 for the record whose key value (ProdID field) is 0001

27. SQL Delete Command • DELETE FROM Product _ • WHERE ProdID = '0008' • deletes the record from Product table whose key value (ProdID field) is 0008

28. Data Definition Commands • CREATE TABLE Students (SID LONG, _ FirstName TEXT (15), LastName TEXT _ (20), City TEXT (20)) • creates a new table • need to provide table name, fields, and data types/field lengths

29. Data Definition Commands - 2 • ALTER TABLE Students _ • ADD COLUMN Street TEXT (30), Zip SINGLE • adds columns (fields) • identify table, provide column (field) name(s) and data types/field length(s)

30. Data Definition Commands - 3 • DROP TABLE VAXComputers • deletes table • need to identify table name

31. Data Definition Commands - 4 • CREATE INDEX SIndex _ • ON Students (SID) • creates an index on the SID field in table Students • CREATE INDEX CIndex _ • ON Circulation (BorrowerNo, BookNo) • creates an index on two fields DROP INDEX CIndex • deletes index

32. Indexes • sorted table of locations of keys to records • faster access to data • slower updates

33. SQL Exercises • online exercises at SQLCourse.com • exercises start: slide 2 ("Select Statement") • modify each exercise slightly • switch order of fields • other changes: fields selected, = vs. <> • paste each of your queries, followed by its results from SQL Interpreter, into a text file • then paste it into a Discussion Board message