Chapter 3 Structured Query Language (SQL) 3 Database Systems: Design, Implementation, and Management 4th Edition Peter Rob & Carlos Coronel Introduction to SQL Structured Query Language (SQL) is the relational database model’s standard language. 3 SQL enable users to Create database and table structure (Data definition) Perform various types of data manipulation Query the database to extract useful information In this section of the class, we want to focus on Querying the database. Some Common SQL Data Types Data Type Format Numeric NUMBER(L,D) 3 INTEGER SMALLINT DECIMAL(L,D) Character CHAR(L) VARCHAR(L) Date DATE Basic Data Management Listing the Table Contents with No 3 Condition (s) List all data in product table (* in SQL means all attributes (fields) in a table) SELECT * FROM PRODUCT; Choosing columns (Instead of using *, you may include all or part field names) SELECT P_CODE, P_DESCRIPT, P_INDATE, P_ONHAND, P_MIN, P-PRICE, P_DISCOUNT, V_CODE FROM PRODUCT; Figure 3.4 3 The Contents of the PRODUCT Table Queries Partial Listing of Table Contents with Condition (s) Generic Syntax SELECT <column(s)> FROM <table name> 3 WHERE <conditions>; An example: SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344; Figure 3.5 Specifying condition (s) with character or numeric fields In the previous slide, we used the following: SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344; 3 The above SQL is correct if v_code (vendor_code) is defined as number. If V-Code is defined as character the correct SQL statement is: SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = ‘21344’; Notice that the use of single quotation to quote v_code. Queries Mathematical Operators 3 Table 3.4 Queries SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <> 21344; 3 Figure 3.7 Queries SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE FROM PRODUCT WHERE P_PRICE <= 10; 3 Figure 3.8 More about where The WHERE clause can contain several conditions linked by AND or OR. Select * from customer 3 where state =‘MO’ and city=‘Cape Girardeau’ This query lists all customers who lives in Cape Girardeau, MO. Notice that since state and city is character field, we use quotation. Select * from friend where height > 5 and weight <150 This query lists all friends whose height is taller than 5 feet and whose weight is less than 150 pounds. Notice that since height and weight are numerical fields, no quotation is necessary. Queries Using Mathematical Operators on Character Attributes 3 SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE FROM PRODUCT WHERE P_CODE < ‘1558-QWI’; Figure 3.9 Queries Logical Operators: AND, OR, and NOT SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE 3 FROM PRODUCT WHERE V_CODE = 21344 OR V_CODE = 24288; Figure 3.11 Queries SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE P_PRICE < 50 AND P_INDATE > ‘07/15/1999’; 3 Figure 3.12 Queries Special Operators BETWEEN - used to define range limits. 3 IS NULL - used to check whether an attribute value is null LIKE - used to check for similar character strings. IN - used to check whether an attribute value matches a value contained within a (sub)set of listed values. EXISTS - used to check whether an attribute has a value. In effect, EXISTS is the opposite of IS NULL. Queries Special Operators BETWEEN is used to define range limits. 3 SELECT * FROM PRODUCT WHERE P_PRICE BETWEEN 50.00 AND 100.00; SELECT * FROM PRODUCT WHERE P_PRICE > 50.00 AND P_PRICE < 100.00; Queries Special Operators LIKE is used to check for similar character strings. SELECT * FROM VENDOR 3 WHERE V_CONTACT LIKE ‘John%’; The above SQL lists all vendors whose name contains Smith such as Johns, Johnson, or Johnny SELECT * FROM VENDOR WHERE V_CONTACT LIKE ‘%John%’; This SQL lists all vendors whose name contains Smith in the middle such as McJohnson, OhJohnney, etc. Of course, I made up these fictional names for instructional purposes. Queries Special Operators IN is used to check whether an attribute value matches a value contained within a (sub)set of listed values. 3 SELECT * FROM PRODUCT WHERE V_CODE IN (21344, 24288); To do the same, we could have written as: SELECT * FROM PRODUCT WHERE V_CODE = 21344 or V_CODE = 24288; EXISTS is used to check whether an attribute has value. DELETE FROM PRODUCT WHERE P_CODE EXISTS; SELECT * FROM PRODUCT WHERE V_CODE EXISTS; More Complex Queries and SQL Functions Ordering a Listing ORDER BY <attributes> 3 SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM PRODUCT ORDER BY P_PRICE; The above SQL lists output order by P_price in an ascending order ORDER BY <attributes> desc SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM PRODUCT ORDER BY P_PRICE desc; This SQL lists output order by P_price in an descending order MAX and MIN Function Output Examples 3 Some Basic SQL Numeric Functio FUNCTION OUTPUT COUNT The number of rows containing the specified attribute. 3 MIN MAX AVG The minimum attribute value encountered. The maximum attribute value encountered. The arithmetic mean (average) for the specified attribute. SUM The sum of all values for a selected attribute. Table 3.6 COUNT Function Output Examples 3 Figure 3.24 MAX and MIN Function Output Examples 3 Figure 3.25 AVG Function Output Examples 3 Figure 3.26 SQL to query information from multiple tables The next slide gives you an example of querying from multiple tables. 3 The basic command syntax (select from where) are to be used. More Complex Queries and SQL Functions Joining Database Tables 3 SELECT PRODUCT.P_DESCRIPT, PRODUCT.P_PRICE, VENDOR.V_NAME, VENDOR.V_CONTACT, VENDOR.V_AREACODE, VENDOR.V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE; Table 3.7 Creating Links Through Foreign Keys Querying from multiple tables SELECT Statements The SELECT statement from the previous slide shows that the field name consists 3 of two parts connected by dot (.)such as PRODUCT.P_DESCRIPT. The first part of the field name is table name. The second part is field (attribute) name. This is interpreted as P_DESCRIPT field in PRODUCT table. FROM Statements The FROM statement list all tables we are using. WHERE Statements Where statement specifies which field is link field. In the following example, these two tables are linked by V_Code field. 3 WHERE PRODUCT.V_CODE = VENDOR.V_CODE; Query 13 asks you to list several fields from two tables without any additional conditions. Therefore, you have to only specify the link field as shown above. Query 14 asks you list several fields from two tables with additional condition, which is credit limit is 10000. In this case, you have to add one more condition in addition to the link field conditions.