CIS 375Web App Dev II SQL Introduction n
CIS 375—Web App Dev II SQL
Introduction n n SQL (Structured _______ Language) is an ANSI standard language for accessing databases. SQL can execute queries, retrieve data, insert new records, delete records, and update records in a database. SQL works with database programs like MS Access, DB 2, Informix, MS SQL Server, Oracle, Sybase, etc. A database contains one or more ______. Each table is identified by a name and contains records (rows) and fields (columns). Besides W 3 Schools, you can also try 2 http: //www. sqlcourse. com/ (optional).
SQL Queries, DML, DDL n n Suppose a table named Persons has fields SSN, Last. Name, First. Name, Age, Address, City and has ten records. An SQL query: n n n SELECT Last. Name FROM Persons Provides a list of all ten last names in the table SQL also has a DML (Data ______ Language) to update, insert, and delete records. SQL also has a DDL (Data _____ Language) to do things like create and delete tables, and to create table indexes (____ keys). For example, SSN would be an index in Persons. 3
SQL: The SELECT Statement n n The SELECT statement specifies ____ of data to be returned from a table. Examples of SQL SELECT statements for the table Persons. n n SELECT Last. Name, First. Name FROM Persons (use a “, ” to separate column names) SELECT * FROM Persons (* means “all”) Executing an SQL statement produces a ____ set of data. Some database programs require a “; ” after each SQL statement, but many don’t. 4
SQL: The WHERE Clause n n The WHERE clause specifies _____ of data to be returned from a table. Example of SQL WHERE clause. n n n SELECT * FROM Persons WHERE City=‘Springfield’ (returns all fields for records where City is Springfield) Use quotes around text, but not around numbers. Other operators include <>, >, <, >=, <=, _____, and LIKE. n n SELECT Last. Name, First. Name FROM Persons WHERE City BETWEEN ‘A’ AND ‘M’ SELECT * FROM Persons WHERE Last. Name LIKE ‘A%’ (returns all last names beginning with A) 5
SQL: AND, OR, NOT n n AND and OR are used to join conditions in a _______ clause. Examples: n n n SELECT * FROM Persons WHERE Last. Name = ‘Johnson’ AND First. Name = ‘Richard’ SELECT * FROM Persons WHERE (First. Name='Tove' OR First. Name='Stephen') AND Last. Name='Svendson' You can also use the keyword _____ with many different kinds of SQL statements: n n SELECT * FROM Persons WHERE NOT (City=‘Berlin' OR City=‘London') SELECT Last. Name, First. Name FROM Persons WHERE City 6 NOT BETWEEN ‘A’ AND ‘M’
SQL: SELECT DISTINCT n n n A SELECT statement could possibly result in multiple values. Use DISTINCT to avoid this possibility. Example: n n n Suppose you want a list of all distinct cities in the Persons table. SELECT DISTINCT City FROM Persons Without DISTINCT, the city of Springfield, for example, might appear several times in the list (for each person who lives in Springfield). 7
SQL: ORDER BY n n ORDER BY is used for _____ results of a query. Example: n n Sort by multiple columns: n n SELECT * FROM Persons ORDER BY Last. Name, First. Name Sort in descending or ascending order: n n SELECT Last. Name, First. Name FROM Persons ORDER BY Last. Name SELECT * FROM Persons ORDER BY City DESC, Last. Name ASC You can test your SQL skills with a real database at http: //www. w 3 schools. com/sql_tryit. asp. 8
SQL: INSERT INTO Statement n To insert a new row into a table: n n INSERT INTO Persons VALUES (‘Bush', ‘George W. ', 56, ‘ 1600 Pennsylvania Ave. ', ‘Washington, D. C. ') To insert only certain values in certain columns of a new row: n INSERT INTO Persons (Last. Name, Address) VALUES (‘Hood', ‘Sherwood Forest') 9
SQL: UPDATE Statement n To modify data in an existing row: n n UPDATE Persons SET First. Name = ‘Rick' WHERE Last. Name = ‘Johnson‘ Update several columns in a row: n UPDATE Persons SET Address = 'Stien 12', City = 'Stavanger' WHERE Last. Name = 'Rasmussen' 10
SQL: DELETE Statement n To delete one or more rows from a table: n n DELETE FROM Persons WHERE Last. Name = 'Rasmussen' To delete all rows: n DELETE * FROM Persons 11
SQL: COUNT Statement n To count all rows in a table (‘*’ means no ____ specified): n n Using COUNT with a WHERE clause: n n SELECT COUNT(*) FROM Persons WHERE Age>20 When a column is specified, the null entries are not counted: n n SELECT COUNT(*) FROM Persons SELECT COUNT(Age) FROM Persons To count distinct entries in a particular column: n SELECT COUNT(DISTINCT City) FROM Persons 12
- Slides: 12