330 likes | 471 Views
CIS 451: SQL. Dr. Ralph D. Westfall February, 2009. What Is SQL?. Structured Query Language standard data access language for relational databases (RDBMS) RDBMS dominate market Access, Oracle, Sybase, DB2, SQL Server, etc. SQL Standardization Advantages. greater productivity
E N D
CIS 451: SQL Dr. Ralph D. Westfall February, 2009
What Is SQL? • Structured Query Language • standard data access language for relational databases (RDBMS) • RDBMS dominate market • Access, Oracle, Sybase, DB2, SQL Server, etc.
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
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
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
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
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
SQL Command Parts - 3 • parameter declarations • optional • at start (before manipulative part) • contain values passed into the query • like parameters in a subprocedure
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
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)
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]
"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
Filter Conditions - 2 • special "predicates" WHERE Name LIKE 'Ng*' WHERE State IN ('CA', 'NY') WHERE Count BETWEEN 1 AND 5
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
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
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
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
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
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
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
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
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
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
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)
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
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
SQL Delete Command • DELETE FROM Product _ • WHERE ProdID = '0008' • deletes the record from Product table whose key value (ProdID field) is 0008
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
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)
Data Definition Commands - 3 • DROP TABLE VAXComputers • deletes table • need to identify table name
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
Indexes • sorted table of locations of keys to records • faster access to data • slower updates
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