Web-Enabled Decision Support Systems

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

Overview • 9.1 Introduction • 9.2 Types of SQL Statements • 9.3 The Data Definition Language (DDL) • 9.4 The Data Manipulation Language (DML) • 9.5 The SELECT Statement • 9.6 The INSERT Statement • 9.7 The DELETE Statement • 9.8 The UPDATE Statement • 9.9 Summary • 9.10 In-Class Assignment

Introduction • Structured Query Language (SQL) is a complete and widely used relational database (RDB) query language • Originally developed by IBM in the late 1970s • 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 • Used for management of database objects: • Creation • Manipulation • Control

Types of SQL Statements • 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

The Data Definition Language (DDL) • 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.

Review of Database Terminology • Tables are used to store and relate data • Two-dimensional grid • Columns: data fields • Rows: records • A primary key is an attribute or minimal set of attributes that uniquely identifies each record within the table • 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 • A database index is the ordered list of indexing attributes with a pointer to table rows where the actual record can be found

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

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

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

CREATE Index - Example • Query: • “Create an index (named tblDeptAddressIndx) on the Address field of the department table.” • SQL Statement:

The DROP Statement • DROP statements removes database objects • Table, Index • Syntax:

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

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

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

ALTER Statement – Example 2 • Query: • “Drop the Chair column and DeptFK2 constraint from the department table.” • SQL Statement:

The Data Manipulation Language (DML) • 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

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

Projection: The SELECT Clause • 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 • Syntax:

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

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

Join: The FROM Clause • 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 • Syntax:

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

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

Selection: The WHERE Clause • 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 • Syntax:

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

Using the BETWEEN Clause Operator • The BETWEENclause operator is used in conjunction with the logical AND operator to specify a range of values • 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.” • SQL Statement:

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

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

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

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

Sorting: The ORDER BY Clause - Example • 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.” • SQL Statement:

Grouping: The GROUP BY Clause • 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: • Syntax:

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

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

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

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

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

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

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

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

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

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

The UPDATE Statement – Example 1 • 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.” • SQL Statement:

Web-Enabled Decision Support Systems