1 / 13

SQL SELECT

SQL SELECT. Getting Data from the Database. Basic Format. SELECT <fieldname>,<fieldname> FROM <tablename> WHERE <fieldname> (=, >, < , <> LIKE , IN ) <some criteria> ORDER BY <fieldname>; SELECT LastName, FirstName, Phone, City FROM Customer WHERE City=‘Seattle’

kirestin-pollard
Download Presentation

SQL SELECT

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL SELECT Getting Data from the Database

  2. Basic Format SELECT <fieldname>,<fieldname> FROM <tablename> WHERE <fieldname> (=, >, < , <> LIKE, IN) <some criteria> ORDER BY <fieldname>; SELECT LastName, FirstName, Phone, City FROM Customer WHERE City=‘Seattle’ ORDER BY LastName;

  3. Key Words • SELECT –every query that returns data starts with SELECT • FROM –determines what table(s) • WHERE –sets up the Criteria • ORDER BY –sorts by the field(s) listed Note: SELECT * FROM Customer, the * means select all columns

  4. AS, Aliasing • The names of the columns may not always be the names you wish to use in a query result. You can use the AS keyword to alias it for the result SELECT LastName AS “Last Name”, FirstName AS “First Name” FROM Customer;

  5. Calculated Fields SELECT 5 * 3 / 2; SELECT lastname + ‘, ‘ firstname AS “Customer Name”, Phone FROM Customer; SELECT OrderID, (ItemPrice * Quantity) AS “SubTotal” FROM OrderDetail WHERE OrderID=1223;

  6. ORDER BY • ORDER BY sorts by the selected field in an Ascending order by default. • You can add the DESC keyword to change the sort order to Descending • If you have multiple fields the leftmost is the primary sort, the next leftmost is the secondary sort etc. SELECT LastName, FirstName, City, phone FROM Customer ORDER BY City, LastName DESC; Note: the DESC only applies to the LastName field

  7. WHERE CRITERIA • You can use all the basic comparative operators

  8. Criteria • Number values are used without quotes • Character (text) values are quoted with single ‘’ quotes • Dates are quoted with single quotes except in Access where they put between pound # signs

  9. Between • You can use the BETWEEN keyword to look for values “between” to limiting values. • The Between includes the ends SELECT OrderID, OrderDate FROM Order WHERE OrderDate BETWEEN #1/1/2007# AND #1/31/2007#;

  10. AND OR NOT • SELECT * • FROM Customer • WHERE State = ‘WA’ • AND City = “Seattle” • OR City = “Tacoma” • AND NOT City = “Bellevue”

  11. LIKE • With the keyword LIKE in conjunction with wildcards will let you search for patterns in text fields • The wild cards are • % any string of characters • _ any one character

  12. LIKE Examples SELECT * FROM Customer WHERE LastName LIKE ‘Ch%’; SELECT * FROM Course WHERE CourseName LIKE ‘MIC12_’

  13. Nulls • You can search for Nulls with the key words IS NULL • SELECT SectionNumber, StudentID, QuarterGrade • FROM Section • WHERE SectionID=‘2830’ • AND QuarterGrade IS NULL

More Related