Database Application SAK 3408 Chapter 3 Query Language

Database Application SAK 3408 Chapter 3 Query Language

What is SQL? SQL or Structured Query Language is an English-like language used to create and manipulate databases. SQL is an ANSI (American National Standards Institute) standard for accessing database systems. SQL statements are used to retrieve and update data in a database. SQL works with database programs like MS Access, DB 2, Informix, MS SQL Server, Oracle, Sybase, etc. With SQL, we can query a database and have a result set returned.

Types of Statements Three types of statements: Data Definition Language (DDL) Data Manipulation Language (DML) Data Control Language (DCL) Data Definition Language creates and manipulates the structure, delete or define indexes of table in database Data Manipulation Language manipulates data in the table Data Control Language determines who is allowed to what within the database

SQL Grammar SQL statements are always terminated by a semi-colon (; ) SELECT CUSTOMER_ID FROM CUSTOMER;

SQL Grammar SQL statements can be entered on a single line or split across multiple lines (preferred) SELECT CUSTOMER_ID, CUSTOMER_NAME, CREDIT_LINE FROM CUSTOMER WHERE CREDIT_LINE > 1000000 AND CUSTOMER_ID IN [1000, 2000];

SQL Grammar SQL statements are not case sensitive; however data is. Se. LEct * f. ROm REQue. ST w. HEr. E a. Cc. T_s. T In [‘GA’, ’WA’, ’NJ’]; Returns data for all requests where the state is either GA, WA or NJ. No records would have been returned if the where clause had been: w. HEr. E a. Cc. T_s. T In [‘ga’, ’w. A’, ’Nj’];

Qualifying a Field Name When the same field name occurs in two or more tables used in a single SQL statement, you must qualify the field name much like you would include the last name to avoid confusion if two people have the same first name. Tablename. fieldname Customer. last_name

Data Definition Language (DDL) Used to create and modify database objects Create Drop Alter

Data Manipulation Language (DML) Used to create, modify and retrieve data Insert Select Update Delete

Data Control Language (DCL) Used to control database privileges Grant Revoke

DDL - Creating a Table Use the Create keyword and specify: table name field (column) name(s) field type constraints primary key - unique identifier for a record foreign key - establishes relationship check - value must be in the specified list not null - must have a value unique - value must be unique

Table with Primary Key Only CREATE TABLE room( room. ID number, bldg char(1) CHECK (bldg IN ('A', 'B')), room. No varchar 2(10), max. Capacity number, stylevarchar 2(15) CHECK (style IN ('LECTURE', 'LECTURE/LAB', 'OFFICE')), CONSTRAINT room_pk PRIMARY KEY (room. ID)); Constraint_name Constraint_type Constraint_attributes

Table with Foreign Key CREATE TABLE faculty( faculty. ID number, lname varchar 2(30) NOT NULL, fname varchar 2(20) NOT NULL, dept varchar 2(5), office. ID number, phone varchar 2(15), email varchar 2(75) UNIQUE, rank char(4) CHECK (rank IN ('INST', 'ASOC', 'ASST', 'FULL', 'SENR')), CONSTRAINT faculty_pk PRIMARY KEY (faculty. ID), CONSTRAINT faculty_fk FOREIGN KEY (office. ID) REFERENCES room(room. ID));

Table with Compound Primary Key create table participation( event. ID number, member. ID number, constraint participation_pk primary key (event. ID, member. ID), constraint participation_event_fk foreign key (event. ID) references event(event. ID), constraint participation_member_fk foreign key (member. ID) references member(member. ID));

DDL - Altering a Table Use the Alter and Add/Modify keywords and specify: table name new or existing field name(s) field type alter table invoice add(sent_dt date); alter table invoice modify (invoice_nbr varchar 2(5));

DDL - Removing a Table Use the Drop keyword and specify: table name drop table invoice; If this table is referenced by a foreign key, use the cascade constraints clause drop table invoice cascade constraints;

Indexes Conceptually similar to book index Increases data retrieval efficiency Automatically assigns record numbers Used by DBMS, not by users Fields on which index built called Index Key Have a sorted order Can guarantee uniqueness Can include one or more fields

Indexes – Syntax CREATE UNIQUE INDEX cust_num_ind ON customer(Name); CREATE INDEX Customer. Name ON Customer (Customer. Num); CREATE INDEX Credit. Limit. Rep_ind ON Customer(Credit. Limit, Rep. Num); DROP INDEX Rep. Bal;

Customer Table with Record Numbers Figure 4. 10

Customer Table Index on Customer. Num Figure 4. 11

Table Indexes on Credit. Limit, Rep. Num Figure 4. 12

Indexes PROs Faster/more efficient data retrieval CONs Requires additional space Increased overhead

Data Manipulation Language (DML) Used to create, modify and retrieve data Insert Select Update Delete

DML - Adding Data to a Table Use the Insert keyword and specify: table name field names - optional values for each field insert into customer values(‘Teplow’, ’MA’, 23445. 67); OR insert into customer (last_name, state_cd, sales) values (‘Teplow’ , ’MA’, 23445. 67);

DML - Updating Data in a Table Use the Update and Set keywords and specify: table name field name(s) where clause (optional) update inventory set price = price*1. 05; update inventory set price = price*1. 05 where product_id = 'P 103';

DML-Deleting Records Use the Delete From keywords and specify: table name where clause (optional) delete from customer; delete from customer where sales < 10000;

Parts of a DML Select Statement Select From Where (optional) Order By (optional) Group By (optional) Having (optional)

SELECT Which fields do you want retrieved? * is used to select all fields in the table

FROM Which table(s) are these fields in?

Two Sample Tables

Select - Simplest Show everything in a single table SELECT * FROM customer; Returns LAST_NAME --------Teplow Abbey Porter Martin Laursen Bambi Mc. Graw STATE_CD -------MA CA CA CA NJ SALES --------23445. 67 6969. 96 6989. 99 2345. 45 34. 34 1234. 55 123. 45

Select Statement - Simple SELECT last_name, state_cd FROM customer; Returns LAST_NAME --------Teplow Abbey Porter Martin Laursen Bambi Mc. Graw STATE_CD ------MA CA CA CA NJ

WHERE - Optional Use to: specify how to join two tables restrict the data returned

SQL Operators Logical Operators and or Comparison Operators = != or <> Like In Between equality inequality string search list of values range of values

Select Statement - Equals SELECT * FROM customer WHERE state_cd = ‘CA’; Returns LAST_NAME --------Abbey Porter Martin Laursen Bambi ST -CA CA CA SALES ----6969. 96 6989. 99 2345. 45 34. 34 1234. 55

Select Statement – Not Equals SELECT * FROM customer WHERE state_cd <> ‘CA’; Returns LAST_NAME -------Teplow Mc. Graw ST -MA NJ SALES -----23445. 67 123. 44

Select Statement - Like SELECT last_name, state_cd, sales FROM customer WHERE upper(state_cd) LIKE ‘%A’; Returns LAST_NAME -------Teplow Abbey Porter Martin Laursen Bambi ST -MA CA CA CA SALES --------23445. 67 6969. 96 6989. 99 2345. 45 34. 34 1234. 55

Select Statement - In SELECT last_name, state_cd FROM customer WHERE state_cd IN (’MA’, ’NJ’); Returns LAST_NAME ------Teplow Mc. Graw ST -MA NJ

Select Statement – Between SELECT * FROM customer WHERE sales BETWEEN 6500 AND 25000; Returns LAST_NAME -------Teplow Abbey Porter ST -MA CA CA SALES -----23445. 67 6969. 96 6989. 99

Select Statement – Multiple Conditions Using OR SELECT * FROM customer WHERE state_cd <> ‘CA’ OR sales BETWEEN 6500 AND 25000; Returns LAST_NAME -------Teplow Abbey Porter Mc. Graw ST -MA CA NJ SALES -----23445. 67 CA 6969. 96 6989. 99 123. 44

Select Statement – Multiple Conditions Using AND SELECT * FROM customer WHERE state_cd <> ‘CA’ AND sales BETWEEN 6500 AND 25000; Returns LAST_NAME -------Teplow ST -MA SALES -----23445. 67

Select Statement – Calculated Field SELECT last_name, sales*05 as Tax FROM customer; Returns LAST_NAME -----Teplow Abbey Porter Martin Laursen Bambi SALES TAX -------23445. 67 1172. 28 6969. 96 348. 50 6989. 99 349. 50 2345. 45 117. 27 34. 34 1. 71 1234. 55 61. 73

ORDER BY - Optional Used to specify sorting order Can sort by multiple fields in ascending or descending order

Select Statement - Sorting select state_name, last_name, sales from customer, state where customer. state_cd = state_cd and state_cd in (‘CA’, ’MA’, ’NJ’) order by state_name; Returns STATE_NAME ---------California Massachusetts New Jersey LAST_NAME SALES -------------Abbey 6969. 96 Porter 6989. 99 Teplow 23445. 67 Mc. Graw 123. 45

Overview Select Statements continued Table Joins Distinct Group By Having Decode function Sequence numbers Subqueries Insert Update Delete

Select - Table Join SELECT state_name, last_name, sales FROM customer, state WHERE customer. state_cd = state_cd; Returns STATE_NAME -------California Massachusetts New Jersey LAST_NAME --------Abbey Porter Teplow Mc. Graw SALES -------6969. 96 6989. 99 23445. 67 123. 45

Table Joins Process of combining data from two or more tables, normally using primary and/or foreign keys. Basic types of joins: Equijoin (Equal or Inner Join) Left join (Outer Join) Right join

Equi Joins Most common type of join Look for records which have matching values of the join fields

Join Processing Table joins are done by matching the first record from the primary table’s first record with each record in the secondary table, then matching the second record in the primary table with each record in the secondary table. Continue until each record from the primary table has been combined with each record from the secondary table. This is called a Cartesian product.

Join Processing - cont Oracle then goes through the Cartesian product, discarding combined records that have non-matching join fields. The remaining records are returned.

Cartesian Product Example Select ename, emp. deptno, dname FROM emp, dept Note: This example is only showing a few fields from the first three employees but all fields and all records are used to create the Cartesian product. The Cartesian product has 56 rows (14 employees * 4 depts)

Cartesian Product Example cont

Cartesian Product Example cont.

Left Joins Used when you may not have matching data in the secondary table, but still want to include the data you have in the primary table. NOTE: The primary table is normally listed on the left side of the equation, the secondary on the right side.

Left Join Example Create a brand new dept (deptno=50), but there are no employees in that dept yet Select dname, deptno, emp. deptno, ename FROM dept, emp WHERE + deptno = emp. deptno;

Right Joins Not as common as left joins. Used when you may have data in the secondary table with no matching data in the primary table.

Right Join Example Assign an employee to a brand new dept, deptno=50, but there’s no record in dept yet Select ename, deptno, dname FROM emp, dept WHERE + emp. deptno = deptno;

Advanced Select - Distinct Used to omit duplicate values in a select statement Select distinct(student_id) From course_section Where term = '1001';

DISTINCT – example SELECT Category FROM Animal; Category Fish Dog Fish Cat Dog Fish Dog Bird Dog Fish Cat Dog SELECT DISTINCT Category FROM Animal; Category Bird Cat Dog Fish

Advanced Select- Group By Clause Allow you to group data together for summary calculations avg (column) count (*) max (column) min (column) sum (column)

Advanced Select- Group By Clause Group by clause must be included in a select statement that uses a group function (count, sum, min, max, and avg) All fields in the select clause which are not part of a group function must be included in the Group By clause.

Advanced Select- Group By Clause avg (column) 1 select deptno, avg(sal) 2 from scott. emp 3* group by deptno; DEPTNO AVG(SAL) ---------------10 2916. 6667 20 2175 30 1566. 6667

Advanced Select - Having Clause Allows you to specify conditions for a record set instead of a single record Must have a group by clause 1 2 3 4* select deptno, avg(sal) from scott. emp group by deptno having avg(sal) > 2000 DEPTNO AVG(SAL) ---------------10 2916. 6667 20 2175

Where vs Having Where - a constraint on individual records. Having - a constraint on a group of records. They can be used together in the same SQL statement Select ename From emp Where upper(loc) = 'NEW YORK' Group by deptno Having avg(sal) > 2000

Subquery Characteristics Can be used in the SELECT, CREATE, INSERT, UPDATE and DELETE statements Can be used as part of a condition in the WHERE clause Can be used in multiple AND or OR predicates of the same SQL statement

Subquery Characteristics - Cont Is enclosed in parenthesis and must appear on the right in a WHERE clause condition May or may not retrieve data from a table used in the main, or outer, SQL statement in which it’s embedded. Cannot include an ORDER BY clause

Subquery Characteristics - Cont The number of rows returned by the subquery must match the number expected by the main query The number of columns returned must also match the number expected

Combining Subqueries Multiple subqueries can be used to check for more than one condition in a statement. Same or different types can be nested. NOTE: Nested subqueries are executed from the most deeply nested to the least deeply nested subquery.

Subquery - Example Show all customers who have placed an order SELECT CUSTOMER_NAME FROM CUSTOMER_T WHERE CUSTOMER_ID IN (SELECT DISTINCT CUSTOMER_ID FROM ORDER_T); Subquery is embedded in parentheses. In this case it returns a list that will be used in the WHERE clause of the outer query

Subquery for Calculation Which cats sold for more than the average sale price of cats? Assume we know the average price is $170. Usually we need to compute it first. SELECT Sale. Animal. ID, Animal. Category, Sale. Animal. Sale. Price FROM Animal INNER JOIN Sale. Animal ON Animal. ID = Sale. Animal. ID WHERE ((Animal. Category="Cat") AND (Sale. Animal. Sale. Price>170)); SELECT Sale. Animal. ID, Animal. Category, Sale. Animal. Sale. Price FROM Animal INNER JOIN Sale. Animal ON Animal. ID = Sale. Animal. ID WHERE ((Animal. Category="Cat") AND (Sale. Animal. Sale. Price> ( SELECT AVG(Sale. Price) FROM Animal INNER JOIN Sale. Animal ON Animal. ID = Sale. Animal. ID WHERE (Animal. Category=“Cat”) ) ) );

Using IN with a Sub-query List all customers who bought items for cats. SELECT Customer. Last. Name, Customer. First. Name, Sale. Item. ID FROM (Customer INNER JOIN Sale ON Customer. ID = Sale. Customer. ID) INNER JOIN Sale. Item ON Sale. ID = Sale. Item. Sale. ID WHERE (Sale. Item. ID In (SELECT Item. ID FROM Merchandise WHERE Category="Cat") );

Using NOT IN with a Sub-query Which animals have not been sold? Start with list of all animals. Subtract out list of those who were sold. SELECT Animal. ID, Animal. Name, Animal. Category FROM Animal WHERE (Animal. ID Not In (SELECT Animal. ID From Sale. Animal));

Subqueries vs Table Joins Always use subqueries instead of table joins when possible. Subqueries are significantly less resource intensive. Table joins are only required when the Select clause contains fields from multiple tables.

UNION, INTERSECT, EXCEPT A B T 1 C List the name of any employee who has worked for both the East and West regions. T 2 SELECT EID, Name FROM Employee. East INTERSECT SELECT EID, Name FROM Employee. West

UNION Operator SELECT EID, Name, Phone, Salary, ‘East’ AS Office FROM Employee. East UNION SELECT EID, Name, Phone, Salary, ‘West’ AS Office FROM Employee. West EID 352 876 372 Name Jones Inez Stoiko Phone 3352 8736 7632 Salary 45, 000 47, 000 38, 000 Office East 890 361 Smythe Kim 9803 7736 62, 000 73, 000 West Offices in Los Angeles and New York. Each has an Employee table (East and West). Need to search data from both tables. Columns in the two SELECT lines must match.

Reflexive Join SQL SELECT Employee. EID, Employee. Name, Employee. Manager, E 2. Name FROM Employee INNER JOIN Employee AS E 2 ON Employee. Manager = E 2. EID Employee EID 115 462 523 765 Name. . . Sanchez Miller Hawk Munoz Manager 765 115 886 Result EID 115 462 523 Name Sanchez Miller Hawk Manager 765 115 Name Munoz Sanchez Need to connect a table to itself. Common example: Employee(EID, Name, . . . , Manager) A manager is also an employee. Use a second copy of the table and an alias.

CASE Function Not available in Microsoft Access. It is in SQL Server and Oracle. Select Animal. ID, CASE WHEN Date()-Date. Born < 90 Then “Baby” WHEN Date()-Date. Born >= 90 AND Date()-Date. Born < 270 Then “Young” WHEN Date()-Date. Born >= 270 AND Date()-Date. Born < 365 Then “Grown” ELSE “Experienced” END FROM Animal; Used to change data to a different context. Example: Define age categories for the animals. Less than 3 months Between 3 months and 9 months Between 9 months and 1 year Over 1 year

Inequality Join Accounts. Receivable Categorize by Days Late 30, 90, 120+ Three queries? New table for business rules AR(Transaction. ID, Customer. ID, Amount, Date. Due) Late. Category(Category, Min. Days, Max. Days, Charge, …) Month Quarter Overdue 30 90 120 9999 3% 5% 10% SELECT * FROM AR INNER JOIN Late. Category ON ((Date() - AR. Date. Due) >= Late. Category. Min. Days) AND ((Date() - AR. Date. Due) < Late. Category. Max. Days)