SQL Part II Yong Choi School of Business

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

SQL Examples – Aggregate Functions • Example 18: Save as example 18 – How many parts (count number of records) are in item class HW? – Use of “count” command – Count all records: count(*) – No idea? Try to figure out manually!

Example 18 SQL Query to Count Records

Example 18 SELECT count(*) FROM Part WHERE Class=_______;

SQL Examples – Aggregate Functions • Example 19: Save as example 19 – Find the number of customers and the total of their balances.

Example 19 SQL Query to Count Records and Calculate a Total

Example 19 SELECT count(*), Sum(______) FROM Customer;

SQL Examples – Aggregate Functions • Example 20: Save as example 20 – Find the total number of customers and the total of their balances. Change the column names for the number of customers and the total of their balances to Customer. Count and Balance. Total. – Assign column name using “AS” command

Example 20 SQL Query to Perform Calculations and Rename Fields

Example 20 SELECT count(*) AS ______, Sum(Balance) AS _______ FROM Customer;

SQL Examples – Nested Query • A query inside another query – A inside query (sub-query) is evaluated first. It is common to enclose sub-query in parentheses for readability!! • Example 21: Save as example 21 – List the order number for each order from the order line table for a part located in warehouse 3. That is, we are looking for list of Order. Num that is associated with warehouse 3. – No idea? Try to figure out manually!

Example 21 SQL Query with Subquery

Example 21 SELECT Order. Num FROM Order. Line WHERE Part. Num IN (SELECT ______ FROM ______ WHERE ______);

SQL Examples - Grouping • Use GROUP BY clause – ONLY grouping, NOT sorting (usually associated with ORDER BY clause) • Example 22: Save as example 22 – For each sales rep, list the rep number, the number of customers assigned to each rep, and the average balance of the rep’s customers. – Rename the count of the number of customers and the average of the balances to Num. Of. Customers and Average. Balance

Example 22 SQL Query to Group Records # of customers are grouped by each sales rep #

Example 22 SELECT Rep. Num, Count(_____) AS Num. Of. Customer, Avg(Balance) AS Avg. Balance FROM Customer GROUP BY ______;

SQL Examples – Grouping (con’t) • Example 23: Save as example 23 – For each sales rep with fewer than four customers, list the rep number, the number of customers assigned to the rep, and the average balance of the rep’s customers. Rename the count of the number of customers and the average of the balances to Num. Of. Customers and Average. Balance. – Use of “Having” command. – Count, Rep. with < 4 customers (group)

Example 23 SQL Query to Restrict Groups 1) # of customers are grouped by each sales rep # 2) Apply the condition to the group

Example 23 • where to put “Having” command? SELECT Rep. Num, count(*) AS Num. Customer, Avg(Balance) AS Average. Balance FROM Customer GROUP BY Rep. Num;

SQL Examples – Grouping (con’t) • Use of Where and Having clauses together – “Where” command must be stated first • Example 23 -1: Save as example 23 -1 – Exactly same as example 23. Except, only groups with fewer than three records and customers with credit limit of less than $10, 000 must be included.

Example 23 -1 SQL Query with ‘WHERE’ and ‘HAVING’ Clauses

Example 23 -1 • Where to put “Where” command? SELECT Rep. Num, count(*) AS Num. Customer, Avg(Balance) AS Average. Balance FROM Customer GROUP BY Rep. Num HAVING Count(*)<3;

Exercise Question 8 from chapter 6 • How many students are enrolled on Database and Networking? (Hint: use Section. No for each class so you can determine the answer from the semester of 2008)? • Which courses were taught in the first semester of 2008 (I-2008) but not in the second semester of 2008 (II-2008)? (Note: There is no indication of the second semester of 2008 (II-2008))?

Matching Field • Try below SQLs using Matching_DB on the class website • To identify missing “Rep. Num, ” make sure to review each table first! SQL 1 SELECT Customer. Rep. Num, Customer. Name, Rep. Num, Last. Name, First. Name FROM Customer, Rep; SQL 2 SELECT Customer. Rep. Num, Customer. Name, Rep. Num, Last. Name, First. Name FROM Customer, Rep Where Customer. Rep. Num=Rep. Num;

Inner Join Example • For each customer who placed an order, what is the customer’s name and order number? The best way to find out match customers with their orders is Including Custumer. ID from both tables 25

Different Type of SQL Joins • (INNER) JOIN: Returns records that have matching values in both tables • LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table • RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table • FULL (OUTER) JOIN: Return all records when there is a match in either left or right table • Also, review SQL Joins in the textbook chapter 7 27

SQL Examples – Joining Tables • Example 24: Save as example 24 – List the number and name of each customer together with the number, last name, and first name of the sales rep who represents the customer. If a customer does not have a Rep. , then he/she must not be included. – That is, look for matching Rep. Num from both tables

Example 24 SQL Query to Join Tables

Example 24 SELECT Customer. Num, Customer. Name, Rep. Num, Last. Name, First. Name FROM Customer, Rep Where _______. Rep. Num =_____. Rep. Num; SELECT Customer. Num, Customer. Name, Rep. Num, Last. Name, First. Name FROM Customer INNER JOIN [Rep] ON ____. Rep. Num=____. Rep. Num;

SQL Examples – Joining Tables (con’t) • Use of multiple tables with a compound condition • Example 25: Save as example 25 – List the number and name of each customer whose credit limit is $10, 000 together with number, last name, and first name of the sales rep who represents the customer.

Example 25 Query to Restrict Records in Join

Example 25 SELECT Customer. Num, Customer. Name, Rep. Num, Last. Name, First. Name FROM Customer, Rep WHERE Rep. Num=Customer. Rep. Num AND ____________;

SQL Examples – Joining Tables (con’t) • Example 26: Save as example – For every order, list the order number, order date, customer number, and customer name. In addition, for each order line within the order, list the part number, description, number ordered, and quoted price. Make sure that everything is matched. In other words, any “unmatched” records must not be included.

Example 26 Query to Join Multiple Tables

Example 26 SELECT Orders. Order. Num, Orderdate, Customer. Num, Customer. Name, Part. Num, Description, Num. Ordered, Quoted. Price FROM Orders, Customer, Order. Line, Part WHERE Customer. Num=Orders. Customer. Num AND Orders. Order. Num=Order. Line. Order. Num AND Order. Line. Part. Num=Part. Num;

SQL Examples – Union • The union of two tables is a table containing all rows that are in either the first table, the second table, or both tables. – Two tables involved in union must have same structure. • Example 27: Save as example 27 – List the number and name of all customers that are either represented by sales rep 35 OR that currently have orders on file, OR both. – Any “unmatched” records must not be included.

Example 27 SQL Query to Perform Union Red: Currently have orders on file Blue: Represented by sales rep 35 Green: Both

Example 27 SELECT Customer. Num, Customer. Name FROM Customer WHERE Rep. Num='35' UNION SELECT Customer. Num, Customer. Name FROM Customer, Orders WHERE Customer. Num=Orders. Customer. Num;

Three Basic Functions by SQL And Their Basic SQL Commands 1. Data definition (last topic) through the use of CREATE 2. Data manipulation (next topic) through INSERT, UPDATE, and DELETE 3. Data querying (we are done with this) through the use of SELECT AND MANY OTHERS, which is the basis for all SQL queries.

SQL - Data Manipulation • Possible with Access – UPDATE – INSERT – DELETE • Only possible with enterprise level DBMS – COMMIT – ROLLBACK

SQL - Data Manipulation (con’t) • UPDATE command makes data entry corrections UPDATE Project SET Prjt. Locat = 'Bellaire', Dept. Num = 5 WHERE Prjt. Num = 10; UPDATE Employee SET Salary = Salary * 1. 1 WHERE Branch = 'Lincoln';

SQL - Data Manipulation (con’t) • INSERT command add new data to a table INSERT INTO Employee (SSN, Last. Name, First. Name) VALUES ('Richard', 'Marini', '43433'); • DELETE command removes table row – DELETE FROM Employee – WHERE Last. Name = 'Brown';

SQL - Data Manipulation (con’t) • COMMIT command store data on the secondary memory permanently • ROLLBACK command restores database back to previous condition if COMMIT hasn’t been used

SQL Examples - Data Manipulation • Example 28: Save as example 28 – Update the street address of customer 524 to 1445 Rivard – First, review the current street address of customer 524 (838 Ridgeland)

Example 28 UPDATE Customer SET Street = '1445 Rivard' WHERE Customer. Num='524';

SQL Examples - Data Manipulation • Example 29: Save as example 29 – Add a new sales rep to the Rep table. Her number is 16, her name is Sharon Rands, and her address is 826 Raymond, Altonville, FL 32543. She has not yet earned any commission, but her commission rate is 5%(0. 05). – First, review the “Rep” table

Example 29 INSERT INTO Rep VALUES ('16', 'Rands', 'Shron', '826 Raymond', 'Altonville', 'FL', '32543', 0, 0. 05);

SQL Examples - Data Manipulation • Example 30: Save as example 30 – Delete any row in the Orderline table in which the part number is BV 06 – First, review the part number BV 06 (Order. Num 21617)

Example 30 DELETE * FROM Order. Line WHERE Part. Num='BV 06';

SQL Examples – Creating a New Table Using a Existing Table • Example 31: save as example 31 – Create a new table named Small. Cust, consisting of all fields from the Customer table and those rows in which the credit limit is less than or equal to $7, 500. SELECT INTO Name of table to create FROM WHERE

Example 31 SQL Query to Create New Table

Example 31 SELECT * INTO Small. Cust FROM Customer WHERE Credit. Limit<=7500;

SQL - Data Definition I • Create a database structure to hold all the database tables; MS Access ONLY can create tables • Usually, only a DBA can create a new database structure SQL syntax for creating a database structure: CREATE SCHEMA AUTHORIZATION <creator>; Example: CREATE SCHEMA AUTHORIZATION JONES;

SQL - Data Definition II • Specify a new relation by giving it a name and specifying each of its attributes. • Each attribute is given a name, a data type to specify its values, and some constraints on the attribute. • Syntax: CREATE TABLE <table name>;

SQL Example – Data Definition • Example 32: Save as example 32 – Create a table call “CSUB” that contains following fields: • • Emp. ID Number (vs. Number(9) or Num(9)) Last. Name Char(20) First. Name Char(20) Street Char(30) City Char(20) State Char(2) Phone Number

Example 32 (con’t) Using Access Create table CSUB (Emp. ID Number, Last. Name Char(20), First. Name Char(20), Street Char(30), City Char(20), State Char(2), Phone Number); • Insert following values: – – – – Emp. ID: 123456789 Last. Name: your lastname First. Name: your firstname Street: 9001 Stockdale Hgwy City: Bakersfield State: CA Phone: 6616656691

Example 32 INSERT INTO CSUB VALUES ('987654321', 'Choi', 'Yong', '9001 Stockdale', 'Bakersfield', 'CA', '123456789');

Using Oracle CREATE TABLE EMPLOYEE (FNAME VARCHAR(15) NOT NULL, LNAME VARCHAR(15) NOT NULL, SSN CHAR(9) BDATE, SEX CHAR, SALARY DECIMAL(10, 2), SUPERSSN CHAR(9), DEPTNO PRIMARY KEY (SSN), INT NOT NULL, FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN), FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER) );