WebEnabled Decision Support Systems SQL Creating and Processing

Web-Enabled Decision Support Systems SQL: Creating and Processing RDBs Prof. Name Position University Name name@email. com (123) 456 -7890 1

Overview v 9. 1 Introduction v 9. 2 Types of SQL Statements v 9. 3 The Data Definition Language (DDL) v 9. 4 The Data Manipulation Language (DML) v 9. 5 The SELECT Statement v 9. 6 The INSERT Statement v 9. 7 The DELETE Statement v 9. 8 The UPDATE Statement v 9. 9 Summary v 9. 10 In-Class Assignment 2

Introduction v Structured Query Language (SQL) is a complete and widely used relational database (RDB) query language – Originally developed by IBM in the late 1970 s – Currently the industry standard – Not a complete programming language § Like Visual Basic, C/C++, or Java – SQL statements may be embedded into general purpose programming languages to create database applications v Used for management of database objects: – Creation – Manipulation – Control 3

Overview v 9. 1 Introduction v 9. 2 Types of SQL Statements v 9. 3 The Data Definition Language (DDL) v 9. 4 The Data Manipulation Language (DML) v 9. 5 The SELECT Statement v 9. 6 The INSERT Statement v 9. 7 The DELETE Statement v 9. 8 The UPDATE Statement v 9. 9 Summary v 9. 10 In-Class Assignment 4

Types of SQL Statements v We divide SQL statements into four categories based on the database operations they perform: – Data Definition Language (DDL) statements § Define data by creating, altering, and destroying database objects – Data Manipulation Language (DML) statements § Manipulate (i. e. , select, insert, update, and delete) data in the database tables – Data Control Language (DCL) statements § Configure and control the database permissions, roles, and referential integrity controls – Database Stored Procedure Language (DSPL) statements § Used for triggers, event handlers, and stored procedures in transaction processing 5

Overview v 9. 1 Introduction v 9. 2 Types of SQL Statements v 9. 3 The Data Definition Language (DDL) v 9. 4 The Data Manipulation Language (DML) v 9. 5 The SELECT Statement v 9. 6 The INSERT Statement v 9. 7 The DELETE Statement v 9. 8 The UPDATE Statement v 9. 9 Summary v 9. 10 In-Class Assignment 6

The Data Definition Language (DDL) v Data Definition Language (DDL) statements define the structure of the data in a database – Create, alter, and drop database objects like Tables, relationships, keys, and indexes. – DDL statements can perform visual steps performed in Chapter 6 and 7 to create tables, keys, indexes, and relationships. – We focus on CREATE, DROP, and ALTER DDL statements for database tables. 7

Review of Database Terminology v Tables are used to store and relate data – Two-dimensional grid § Columns: data fields § Rows: records v A primary key is an attribute or minimal set of attributes that uniquely identifies each record within the table v A foreign key is an attribute or a set of attributes in a table that acts as a primary key to the same or another table in the database v A database index is the ordered list of indexing attributes with a pointer to table rows where the actual record can be found 8

The CREATE Statement v CREATE statements create database objects – Define primary and foreign keys – Create an index on a table attribute v Syntax: 9

CREATE Statement - Example v Query: – “Create a new table, tbl. Department, to store department information. Store the department identification number, name, address, and phone number for all the departments in the university database. ” v SQL Statement: 10

SQL Field Constraints v The following tables lists a few column constraints that can be used with the CREATE TABLE SQL statement: 11

CREATE Index - Example v Query: – “Create an index (named tbl. Dept. Address. Indx) on the Address field of the department table. ” v SQL Statement: 12

The DROP Statement v DROP statements removes database objects – Table, Index v Syntax: 13

DROP Statement - Examples v Query 1: – “Remove the department table, its data, columns, and indexes. ” v SQL Statement 1: v Query 2: – “Remove only the index on the Address field of the department table. ” v SQL Statement 2: 14

The ALTER Statement v ALTER statements alter existing database objects – Addition or deletion of columns or constraints – Revision of data types and field constraints v Add Syntax: v Drop Syntax: 15

ALTER Statement – Example 1 v Query: – “Add a field for the department chairperson to the department table. Make this field a foreign key in the faculty table. ” v SQL Statement: 16

ALTER Statement – Example 2 v Query: – “Drop the Chair column and Dept. FK 2 constraint from the department table. ” v SQL Statement: 17

Overview v 9. 1 Introduction v 9. 2 Types of SQL Statements v 9. 3 The Data Definition Language (DDL) v 9. 4 The Data Manipulation Language (DML) v 9. 5 The SELECT Statement v 9. 6 The INSERT Statement v 9. 7 The DELETE Statement v 9. 8 The UPDATE Statement v 9. 9 Summary v 9. 10 In-Class Assignment 18

The Data Manipulation Language (DML) v Data Manipulation Language (DML) statements involve selection, insertion, update, and deletion operations – The core and most frequently used SQL statements – The manipulation involves selection, insertion, updates, and deletion operations and the SELECT, INSERT, UPDATE, and DELETE statements are used to perform these operations, respectively – All DML statements have equivalent Access query types 19

Overview v 9. 1 Introduction v 9. 2 Types of SQL Statements v 9. 3 The Data Definition Language (DDL) v 9. 4 The Data Manipulation Language (DML) v 9. 5 The SELECT Statement v 9. 6 The INSERT Statement v 9. 7 The DELETE Statement v 9. 8 The UPDATE Statement v 9. 9 Summary v 9. 10 In-Class Assignment 20

The SELECT Statement v SELECT statements are by far the most frequently used SQL statements – Used for a wide variety of purposes v Structure: Mandatory Optional, but must appear in this order 21

Projection: The SELECT Clause v The basic SELECT statement presented below projects the selected table columns as a query output – SELECT clause § Specify the columns of query output – FROM clause § List the tables involved in the query design to select columns from v Syntax: 22

Projection: The SELECT Clause - Examples v Query 1: – “Project the student name, email, address, and phone fields from the student table. ” v SQL Statement 1: v Query 2: – “Project all columns from the student table. ” v SQL Statement 2: 23

Hands-On Tutorial: Running DML Statements in the Access Environment v How-to: Write, Execute, and Test SQL Statements in Access 1. Click the Create Query in Design View option in the Database Window to open a new query in the Design View. 2. Click the Close button in the Show Table dialog box without adding any tables to open the SQL View. 3. Write the SQL statement and click the Run (!) button on the toolbar to execute the query. Running DML SQL Statements in the Access Environment 24

Join: The FROM Clause v Join systematically draws data from multiple tables in order to provide a unified view of data in individual tables – A powerful and frequently used operation – We join tables in SQL by specifying table names (separated by commas) in the FROM clause v Syntax: 25

Join: The FROM Clause - Example v Query: – “Select the contact information and department name for faculty members. ” v SQL Statement: 26

Renaming Query Tables and Fields Using the Keyword “AS” v To resolve conflicts, identify columns by prefixing tables’ names: v If table names are long or complex, use the keyword AS to rename: 27

Selection: The WHERE Clause v The WHERE clause is an optional clause of the SELECT statement used to specify selection criteria – Often used to specify the selection condition of an inner join § Usually equate the primary key and foreign key values – Can include following operators: § Arithmetic Operators: =, <, <=, >, >= § Clause Operators: BETWEEN, LIKE, IN § Logical Operators: AND, OR, NOT v Syntax: 28

Selection: The WHERE Clause - Example v Query: – “Select the contact information and department name of the faculty members who joined the university after 1995 and whose salary exceeds $75, 000. ” v SQL Statement: Join Clause Filter Clauses 29

Using the BETWEEN Clause Operator v The BETWEEN clause operator is used in conjunction with the logical AND operator to specify a range of values v Query: – “Select the contact information and department name of the faculty members whose salary is between $65, 000 and $75, 000 and who joined the university after 1995. ” v SQL Statement: 30

Using the LIKE Clause Operator v The LIKE clause is widely applied in search criteria – Often used with the wildcard character: (*) v Query: – “List the contact information for all the industrial engineering students who have taken computer- or database-related courses. ” v SQL Statement: 31

Using the IN Clause Operator v The IN clause is often used for nested or sub queries – Specifies the inner query to look in v Query: – “List the contact information for all the distinct industrial engineering students who have taken computer- or database-related courses. ” v SQL Statement: 32

Using the Keyword DISTINCT v We can obtain distinct records using the keyword DISTINCT in the SELECT clause – Prevents duplicate records in output recordset v SQL Statement: 33

Sorting: The ORDER BY Clause v We can use the ORDER BY clause to sort the output records based on one or more attributes in ascending (ASC) or descending (DESC) order v Syntax: 34

Sorting: The ORDER BY Clause - Example v Query: – “Select the contact information and department name of the faculty members whose salary is between $65, 000 and $75, 000. List the output, first in ascending order of their joining date and then in descending order of their salary. ” v SQL Statement: 35

Grouping: The GROUP BY Clause v A GROUP BY clause combines similar records and treats them as a single unit or group – For use with aggregate functions: § MIN, MAX: Provides the minimum or maximum value § SUM, AVG: Calculates the sum or average value § COUNT: Finds the count of records – Aggregate function syntax: v Syntax: 36

Grouping: The GROUP BY Clause - Example v Query: – “Count the number of students in each department in the department table. ” v SQL Statement: 37

Using the HAVING Clause v The HAVING clause is always used with the GROUP BY clause to specify the filtering criteria for group values – Similar to the WHERE clause v Syntax: 38

Using the HAVING Clause - Example v Query: – “List the departments in the College of Engineering that have enrollments of 100 or more students. ” v SQL Statement: 39

Overview v 9. 1 Introduction v 9. 2 Types of SQL Statements v 9. 3 The Data Definition Language (DDL) v 9. 4 The Data Manipulation Language (DML) v 9. 5 The SELECT Statement v 9. 6 The INSERT Statement v 9. 7 The DELETE Statement v 9. 8 The UPDATE Statement v 9. 9 Summary v 9. 10 In-Class Assignment 40

The INSERT Statement v INSERT statements are DML statements that insert one or more records into database tables v Syntax: 41

The INSERT Statement - Example v Query: – “Insert information about the new department, “Digital Institute”, into the department table. ” v SQL Statement: 42

The INSERT Statement – More Examples v Query 1: – “Insert information about the new department, setting the Phone to NULL. ” v Query 2: – “Insert information about multiple new departments from tbl. Download. ” 43

Overview v 9. 1 Introduction v 9. 2 Types of SQL Statements v 9. 3 The Data Definition Language (DDL) v 9. 4 The Data Manipulation Language (DML) v 9. 5 The SELECT Statement v 9. 6 The INSERT Statement v 9. 7 The DELETE Statement v 9. 8 The UPDATE Statement v 9. 9 Summary v 9. 10 In-Class Assignment 44

The DELETE Statement v DELETE statements are DML statements that delete one or more records from database tables – Specify the deletion criteria using a WHERE clause v Syntax: 45

The DELETE Statement – Example 1 v Query: – “Delete all last year (senior) industrial engineering students. ” v SQL Statement: 46

The DELETE Statement – Example 2 v Query: – “Delete all last year (senior) industrial engineering students. ” § Delete records in conjunction with a sub query v SQL Statement: 47

Overview v 9. 1 Introduction v 9. 2 Types of SQL Statements v 9. 3 The Data Definition Language (DDL) v 9. 4 The Data Manipulation Language (DML) v 9. 5 The SELECT Statement v 9. 6 The INSERT Statement v 9. 7 The DELETE Statement v 9. 8 The UPDATE Statement v 9. 9 Summary v 9. 10 In-Class Assignment 48

The UPDATE Statement v UPDATE statements are DML statements that update one or more rows of a table – Specify the update criteria using a WHERE clause v Syntax: 49

The UPDATE Statement – Example 1 v Query: – “Raise the salary field in the faculty table by 5% for all the faculty members who joined the university before 1995 and who earn less than $70, 000. ” v SQL Statement: 50

The UPDATE Statement – Example 2 v Query: – “Raise the salary field in the faculty table by 5% for all the faculty members who joined the university before 1995 and who earn less than $70, 000. ” § Update records in conjunction with a sub query v SQL Statement: 51

Overview v 9. 1 Introduction v 9. 2 Types of SQL Statements v 9. 3 The Data Definition Language (DDL) v 9. 4 The Data Manipulation Language (DML) v 9. 5 The SELECT Statement v 9. 6 The INSERT Statement v 9. 7 The DELETE Statement v 9. 8 The UPDATE Statement v 9. 9 Summary v 9. 10 In-Class Assignment 52

Summary v We divide SQL statements into four categories: – – v Data Definition Language (DDL) statements Data Manipulation Language (DML) statements Data Control Language (DCL) statements Database Stored Procedure Language (DSPL) statements The SELECT statement features two clauses: – SELECT clause § Specify the columns of query output – FROM clause § List the tables involved in the query design to select columns from 53

Summary (cont. ) v We can use the ORDER BY clause to sort the output records based on one or more attributes in ascending (ASC) or descending (DESC) order v We obtain distinct records using the keyword DISTINCT in the SELECT clause v A SELECT clause can have those fields without aggregate functions grouped in the GROUP BY clause v A HAVING clause can have those fields that either appear in the GROUP BY clause or are used with aggregate functions in the SELECT clause 54

Summary (cont. ) v CREATE, DROP, and ALTER statements are used to create, delete, and update database objects such as tables and indexes v INSERT statements insert one or multiple records into database tables v DELETE statements delete one or more records from database tables – We specify the deletion criteria using a WHERE clause v UPDATE statements update one or more rows of database tables – We specify the update criteria using a WHERE clause 55

Overview v 9. 1 Introduction v 9. 2 Types of SQL Statements v 9. 3 The Data Definition Language (DDL) v 9. 4 The Data Manipulation Language (DML) v 9. 5 The SELECT Statement v 9. 6 The INSERT Statement v 9. 7 The DELETE Statement v 9. 8 The UPDATE Statement v 9. 9 Summary v 9. 10 In-Class Assignment 56

In-Class Assignment v Write SQL statements for the following queries based on the university database: – Query 1: § “Create a new faculty table, tbl. Faculty. Assume appropriate fields and their data types, including a primary key field. The table must have a Dept. ID as a foreign key referring to the department table, tbl. Department. Also, create an index on the primary key of the faculty table. ” – Query 2: § “Display the number of faculty members working for each department in the department table. ” – Query 3: § “Delete all those faculty members who have been at the university for 15 years (use Joining. Date fields) and who are earning less than $50, 000 (use Salary field). ” 57

Additional Links v Refer SQL: The Complete Reference by James R Groff and Paul N. Weinberg for additional details on SQL. v Also refer Access 2003 Bible by Cary N. Prague, Michael R. Irwin, and Jennifer Reardon. v Also see: http: //msdn. microsoft. com/ for useful tips on SQL and Access. 58