SQL Part I Yong Choi School of Business

SQL – Part I Yong Choi School of Business CSU, Bakersfield

Study Objectives • Understand the basic commands and functions of SQL • Learn how SQL is used for data manipulation (to add, modify, delete, and retrieve data) • Learn how to use SQL to query a database to extract useful information • Learn how SQL is used for data administration (to create tables, indexes, and views) • Practice SQL 2

Brief History of SQL • Standard Query Language (SQL) is the relational model’s standard language. • The original version of SQL was developed at IBM's San Jose Research Laboratory. This language, originally called Sequel. The Sequel language has evolved since then, and its name has changed to SQL (Structured Query Language). • In 1986, the American National Standards Institute (ANSI) published an SQL standard. – In 1992, work was completed on a significantly revised version of the SQL standard (SQL-92). 3

Introduction to SQL • SQL is relatively easy to learn – SQL commands set has a basic vocabulary of less than 100 words. • SQL is a nonprocedural language. So, it is much easier to use. – Its user merely commands what is to be done without having to worry about how it's to be done. – Procedural language: COBOL, C, or Pascal. • See SQL by “Restaurant” DB on the class website 4

Basic Structure of SQL Queries • • • SELECT <attribute list> FROM <table list> WHERE <condition> GROUP BY < grouping attribute(s)> HAVING <group condition> ORDER BY <attribute list> 5

The SELECT and FROM Statement • The SELECT statement is used to select data from a table. The tabular result is stored in a result table (called the result set). The FROM statement is used to select tables. • Syntax: – SELECT column_name(s) – FROM table_name • To select all columns from a table, use a * symbol instead of column names: – SELECT * FROM table_name 6

The WHERE Statement • To conditionally select data from a table, a WHERE clause can be added to the SELECT statement. • Syntax: – SELECT column – FROM table – WHERE column operator value 7

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 8

Semicolon after SQL Statements? • Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server. • MS Access and SQL Server do not require to put a semicolon after each SQL statement, but other database SQLs force you to use it such as Oracle. So, you must use a semicolon for this class. – Access SQL commands are not case sensitive (including table and attribute names) but try to follow exact names for better readability. – Download SQL data file form the class web site. 9

Customer. Num Customer. Name Street City State Zip Balance 148 Al's Appliance and Sport 2837 Greenway Fillmore FL 33336 $6, 550 Credit. Limit $7, 500 Rep. Num 20 Order. Line Order. Num Part. Num 21608 AT 94 Num. Ordered Quoted. Price 11 $21. 95 Orders Order. Num 21608 Order. Date 10/20/2003 Customer. Num 148 Part. Num Description AT 94 Iron On. Hand 50 Class Warehouse HW 3 Price $24. 95 Rep. Num Last. Name First. Name Street City State Zip 20 Kaiser Valerie 624 Randall Grove FL 33321 Commission $20, 542. 50 Rate 0. 05 10

Example 2 • Example 2: Save as example 2 – List the number, name, and balance of all customers. 11

Example 2 12

Example 2 SELECT Customer. Num, Customer. Name, Balance FROM Customer; 13

Example 3 • Example 3: Save as example 3 – List the complete Part table. – Use of “ * “ 14

Example 3 15

Example 3 SELECT * FROM Part; 16

Oracle 7. 0 SQL Example SQL> select * from employee; EMP_ID EMP_LNAME EMP_FNAME EMP_SALARY EMP_DEPT_NO ---------- ------ 1 Kim John 1000 100 2 Johnson Steve 1200 100 3 Jonson Paul 1100 200 4 Lee Jim 1100 200 5 Basinger Jon 1300 6 Stone Sharon 1000 6 rows selected. 17

SQL Example – WHERE clause • Example 4: Save as example 4 – List the name of every customers with $10, 000 credit limit. • Credit limit must be equal to $10000 18

Example 4 SQL Query with Where Condition 19

Example 4 SELECT Customer. Name FROM Customer WHERE Credit. Limit=10000; 20

SQL Example – WHERE clause • Example 5: Save as example 5 – Find the name of customer 148. 21

Example 5 SQL Query to Find Customer 148 22

Example 5 SELECT Customer. Name FROM Customer WHERE Customer. Num=“ 148”; 23

SQL Comparison Operators For WHERE clause NOT Warehouse =‘ 3’ LIKE: LIKE ‘a*’, LIKE ‘*s’, Like ‘*Oxford*’ (NOT) BETWEEN 45000 AND 78000 (NOT) IN (123, 345) 24

SQL Examples • Example 6: Save as example 6 – Find the customer name for every customer located in the city of Grove 25

Example 6 26

Example 6 SELECT Customer. Name FROM Customer WHERE City = “Grove”; 27

SQL Examples • Example 7: Save as example 7 – List the number, name, credit limit, and balance for customers with credit limits that exceed their balances. 28

Example 7 29

Example 7 SELECT Customer. Num, Customer. Name, Credit. Limit, Balance FROM Customer WHERE Credit. Limit>Balance; 30

SQL Examples – Compound Conditions • Example 8: Save as example 8 – List the description of all parts that are located in warehouse 3 and for which there are more than 20 units on hand. 31

Example 8 SQL Query with Compound Condition using ‘AND’ 32

Example 8 SELECT Description FROM Part WHERE Warehouse=“ 3” AND On. Hand>20; 33

SQL Examples – Compound Conditions • Example 9: Save as example 9 – List the descriptions of all parts that are located in warehouse 3 or for which there are more than 20 units on hand. 34

Example 9 SQL Query using ‘OR’ 35

Example 9 SELECT Description FROM Part WHERE Warehouse=“ 3” OR On. Hand>20; 36

SQL Examples • Example 10: Save as example 10 – List the description of all parts that are not in warehouse 3. – Use “NOT” (i. e. , where NOT A = 100; ) 37

Example 10 SQL Query using ‘NOT’ 38

Example 10 SELECT Description FROM Part WHERE NOT Warehouse=“ 3”; 39

SQL Examples • Example 11: Save as example 11 – List the number, name, and balance of all customers with balances greater than or equal to $1, 000 and less than or equal to $5, 000. – (NOT) BETWEEN 45000 AND 78000 40

Example 11 Query with ‘BETWEEN’ Operator 41

Example 11 SELECT Customer. Num, Customer. Name, Balance FROM Customer WHERE Balance BETWEEN 1000 AND 5000; • Also Try below, what happens? WHERE Balance NOT BETWEEN 1000 AND 5000; 42

SQL Examples – Computed Field • Computed field can involve: – addition(+), subtraction(-), Multiplication(*), or division (/) • Example 12: Save as example 12 – List the number, name and available credit for all customers. – Available. Credit = Credit. Limit-Balance – Use “AS” for assigning a new field name 43

Example 12 SQL Query with Computed Field 44

Example 12 SELECT Customer. Num, Customer. Name, Credit. Limit-Balance AS Available. Credit FROM Customer; 45

SQL Examples – Computed Field • Computed field can involve: – addition(+), subtraction(-), Multiplication(*), or division (/) • Example 13: Save as example 13 – List the number, name, and available credit for all customers with credit limits that exceed their balances. 46

Example 13 SQL Query with Computed Field and Condition 47

Example 13 SELECT Customer. Num, Customer. Name, Credit. Limit-Balance AS Available. Credit FROM Customer WHERE Credit. Limit > Balance; 48

SQL Examples – LIKE and IN • Example 14: Save as example 14 – List the number, name, and complete address of every customer located on a street that contain the letters “Oxford. ” • Customer names begin with A: Like A* • Customer names end with B: Like *B • Fine exact customer last name: like “*Choi*” 49

Example 14 SQL Query with ‘LIKE’ Operator 50

Example 14 SELECT Customer. Num, Customer. Name, Street, City, State, Zip FROM Customer WHERE Street LIKE “*Oxford*”; 51

SQL Examples – LIKE and IN • Example 15: Save as example 15 – List the number, name, and credit limit for every customer with a credit of $7, 500, $10, 000, or $15, 000. – IN (7500, 10000, 15000); 52

Example 15 SQL Query with ‘IN’ Operator 53

Example 15 SELECT Customer. Num, Customer. Name, Credit. Limit FROM Customer WHERE Credit. Limit IN (7500, 10000, 15000); 54

SQL Examples • Default value of ORDER BY: ascending – Ascending: do not need to specify • Example 16: Save as example 16 – List the number, name, and credit limit of all customers. Sort the customers by name in ascending order. 55

Example 16 SQL Query to Sort Data 56

Example 16 SELECT Customer. Num, Customer. Name, Credit. Limit FROM Customer ORDER BY Customer. Name; 57

SQL Examples • Default value of ORDER BY: ascending • Example 17: Save as example 17 – List the number, name, and credit limit of all customers. Sort the customers by name (minor) in ascending order within credit limit (major) in descending order. – Name must be sorted within Credit. Limit 58

Example 17 SQL Query to Sort on Multiple Fields 59

Example 17 SELECT Customer. Num, Customer. Name, Credit. Limit FROM Customer ORDER BY Credit. Limit DESC, Customer. Name; 60