Querying Data from Database: Simple Guide

1. Lab #3Querying Data

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

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

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

5. SELECT flname, ffnameFROM faculty;

6. SELECT ffname, flnameFROM faculty;

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

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

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

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

11. 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

12. 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’ ;

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

14. 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?

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

16. Comparison Operators

17. 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)’

18. 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?

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

20. 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_mnameIS NOT NULL

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

22. 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

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

24. Delete and update • Delete a record • Update a recrod DELETE FROM vet_appt WHERE vet_appt_id = 3; UPDATE animalSET’ gender; = “MS” WHERE animal_id = 3