Transcript Chapter 3
3 Chapter 3 The Relational Model 2: SQL Concepts of Database Management, 4th Edition, Pratt & Adamski 1 3 Objectives Use simple and compound conditions in SQL Use computed fields in SQL Use built-in SQL functions Use subqueries in SQL Group records in SQL Concepts of Database Management, 4th Edition, Pratt & Adamski 2 3 Objectives Join tables using SQL Perform union operations in SQL Use SQL to update database data Use an SQL query to create a table in a database Concepts of Database Management, 4th Edition, Pratt & Adamski 3 3 Table Creation SQL CREATE TABLE Used to describe layout of a table Typical restrictions placed by DBMS Names cannot exceed 18 characters Names must start with a letter Names can contain only letters, numbers, and underscores (_) Names cannot contain spaces Concepts of Database Management, 4th Edition, Pratt & Adamski 4 3 Typical Data Types INTEGER Numbers without a decimal point SMALLINT Uses less space than INTEGER DECIMAL(p,q) P number of digits; q number of decimal places CHAR(n) Character string n places long DATE Dates in DD-MON-YYYY or MM/DD/YYYY Concepts of Database Management, 4th Edition, Pratt & Adamski 5 Simple Retrieval Figures 3.1 - 3.2 Concepts of Database Management, 4th Edition, Pratt & Adamski 3 6 SQL Query to List Part Table Figures 3.3 - 3.4 Concepts of Database Management, 4th Edition, Pratt & Adamski 3 7 SQL Query with Where Condition Figures 3.5 - 3.6 Concepts of Database Management, 4th Edition, Pratt & Adamski 3 8 SQL Comparison Operators Figure 3.7 Concepts of Database Management, 4th Edition, Pratt & Adamski 3 9 SQL Query to Find Customer 148 Figures 3.8 - 3.9 Concepts of Database Management, 4th Edition, Pratt & Adamski 3 10 SQL Query to Find All Customers in ‘Grove’ Figures 3.10 - 3.11 Concepts of Database Management, 4th Edition, Pratt & Adamski 3 11 Query to find Customers with Credit Limit Exceeding Balance Figures 3.12 - 3.13 Concepts of Database Management, 4th Edition, Pratt & Adamski 3 12 SQL Query with Compound Condition using ‘AND’ Figures 3.14 - 3.15 Concepts of Database Management, 4th Edition, Pratt & Adamski 3 13 SQL Query using ‘OR’ Figures 3.16 - 3.17 Concepts of Database Management, 4th Edition, Pratt & Adamski 3 14 SQL Query using ‘NOT’ Figures 3.18 - 3.19 Concepts of Database Management, 4th Edition, Pratt & Adamski 3 15 Query with ‘BETWEEN’ Operator Figures 3.20 - 3.21 Concepts of Database Management, 4th Edition, Pratt & Adamski 3 16 SQL Query with Computed Field Figures 3.22 - 3.23 Concepts of Database Management, 4th Edition, Pratt & Adamski 3 17 SQL Query with Computed Field and Condition Figures 3.24 - 3.25 Concepts of Database Management, 4th Edition, Pratt & Adamski 3 18 SQL Query with ‘LIKE’ Operator Figures 3.26 - 3.27 Concepts of Database Management, 4th Edition, Pratt & Adamski 3 19 SQL Query with ‘IN’ Operator Figures 3.28 - 3.29 Concepts of Database Management, 4th Edition, Pratt & Adamski 3 20 SQL Query to Sort Data Figures 3.30 - 3.31 Concepts of Database Management, 4th Edition, Pratt & Adamski 3 21 SQL Query to Sort on Multiple Fields Figures 3.32 - 3.33 Concepts of Database Management, 4th Edition, Pratt & Adamski 3 22 SQL Query to Count Records Figures 3.34 - 3.35 Concepts of Database Management, 4th Edition, Pratt & Adamski 3 23 SQL Query to Count Records and Calculate a Total Figures 3.36 - 3.37 Concepts of Database Management, 4th Edition, Pratt & Adamski 3 24 SQL Query to Perform Calculations and Rename Fields Figures 3.38 - 3.39 Concepts of Database Management, 4th Edition, Pratt & Adamski 3 25 SQL Query with Subquery Figures 3.40 - 3.41 Concepts of Database Management, 4th Edition, Pratt & Adamski 3 26 SQL Query to Group Records Figures 3.42 - 3.43 Concepts of Database Management, 4th Edition, Pratt & Adamski 3 27 SQL Query to Restrict Groups Figures 3.44 - 3.45 Concepts of Database Management, 4th Edition, Pratt & Adamski 3 28 SQL Query with ‘WHERE’ and ‘HAVING’ Clauses Figures 3.46 - 3.47 Concepts of Database Management, 4th Edition, Pratt & Adamski 3 29 SQL Query to Join Tables Figures 3.48 - 3.49 Concepts of Database Management, 4th Edition, Pratt & Adamski 3 30 Query to Restrict Records in Join Figures 3.50 - 3.51 Concepts of Database Management, 4th Edition, Pratt & Adamski 3 31 Query to Join Multiple Tables Figures 3.52 - 3.53 Concepts of Database Management, 4th Edition, Pratt & Adamski 3 32 SQL Query to Perform Union Figures 3.54 - 3.55 Concepts of Database Management, 4th Edition, Pratt & Adamski 3 33 SQL Query to Update Data Figures 3.56 - 3.57 Concepts of Database Management, 4th Edition, Pratt & Adamski 3 34 SQL Query to Delete Rows Figure 3.58 Concepts of Database Management, 4th Edition, Pratt & Adamski 3 35 SQL Query to Create New Table Figures 3.59 - 3.60 Concepts of Database Management, 4th Edition, Pratt & Adamski 3 36