Lab #3 Querying Data

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;