1 / 24

Lab #3 Querying Data

Lab #3 Querying Data. Delete and update. Delete a record Update a record. DELETE FROM vet_appt WHERE vet_appt_id = 3;. UPDATE animal SET’ gender = “MS” WHERE animal_id = 3. What would happen if you didn’t include a where clause with a delete or update record command?.

zamora
Download Presentation

Lab #3 Querying Data

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. Lab #3Querying Data

  2. Delete and update • Delete a record • Update a record DELETE FROM vet_appt WHERE vet_appt_id = 3; UPDATE animalSET’ gender= “MS” WHERE animal_id = 3 What would happen if you didn’t include a where clause with a delete or update record command?

  3. Query Data From Database • We use the SELECT statement to retrieve information from the database. SELECT attribute/s FROM table/s WHERE (condition)

  4. Simple Select List all the record s (rows) and all attributes (columns) in table: * - WILDCARD SELECT * FROM tableName; Select * from animal

  5. Select Attributes/Fields SELECT fieldNameA,fieldNameB FROM tableName; The order in which you list the attributes will be the order the attributes are displayed.

  6. SELECT flname, ffnameFROM faculty;

  7. SELECT ffname, flnameFROM faculty;

  8. Select Distinct • SELECT distinctsclass FROM student; • SC • -- • SR • JR • SO • FR  • SELECT sclassFROM student; • SC • -- • SR • SR • JR • SO • SO • FR 

  9. Filtering Data: The WHERE Clause SELECT fieldName FROM tableName WHERE fieldName [some condition]; • Returns only those records that match the condition

  10. Example 1: Number SELECT sfname, slname, fid FROM student WHERE fid = 1;

  11. Example 2: String SELECT sfname, slname FROM student WHERE sclass= 'SO'; Remember Values in strings are Case sensitive

  12. Comparison Operators … A and B - Intersect A OR B –UNION: all of A (including yellow, gray and purple) all of B (including aqua, and purple and gray) C and B

  13. Logical Operators - OR SELECT fieldNameA, fieldNameB FROM tableName WHERE attribute = X or attribute = Y; Must match EITHER Select client_fname, client_lname, add1, city, state, zip From vet_client Where state = ‘NJ’ OR state = ‘PA’ ;

  14. Arithmetic Operators – Rules of Precedence • Arithmetic operators and rules of precedence Table 5.5

  15. Logical Operators – AND SELECT fieldNameA, fieldNameB FROM tableName WHERE attribute = X AND attribute = Y; Must match BOTH Select client_fname, client_lname, add1, city, state, zip From vet_client Where state = ‘NJ’ AND state= ‘PA’ ; Would this work?

  16. Logical Operators – AND Select client_fname, client_lname, add1, city, state, zip From vet_client Where city= ‘Philadelphia’ AND state= ‘PA’ ;

  17. Comparison Operators

  18. EXAMPLES of Comparison Operator SELECT appt_date, appt_type, temperatureFROM vet_apptWHERE (temperature >= 102 ;SELECT appt_date, appt_type,temperatureFROM vet_apptWHERE appt_type in (1, 2, 3)’

  19. Logical Operators – AND SELECT fieldNameA, fieldNameB FROM tableNameWHERE criteria Select client_fname, client_lname, add1, city, state, zip From vet_client Where state = ‘NJ’ AND fieldNameA = ‘PA’ ; Would this work?

  20. Wildcard Characters _ : 1 character %: multiple characters SELECT client_lnameFROM vet_client WHERE client_lname LIKE ‘_emple’; SELECT client_lnameFROM vet_client WHERE client_lname LIKE ‘%d%

  21. NULL/NOT NULL Operator SELECT student_fname, student_lname, FROM student WHERE student_mname IS NULL SELECT student_fname, student_lname, FROM student WHERE student_mname IS NOT NULL

  22. Sorting the Output: ORDER BY SELECT client_lname, client_fname ORDER BY client_lnamedesc; Can Nest order ASC is default

  23. Example: SELECT bldg_code, room, capacity FROM location WHERE capacity >= 40 Order by capacity DESC, bldg_code ASC ; BLDG_CODE ROOM CAPACITY -------------------------------------------------------- SP 101 150 BUS 211 55 BUS 105 42 SP 202 40

  24. To Summarize SELECT (distinct)fieldName/s FROM tableName WHERE fieldName [some condition] ORDER BYfieldName ASC/DESC;

More Related