BUSS 453

(SQL-Part III)
Structured Query Language (SQL)

Data de nition (DD)

- Purpose: (i) Create table structures (create, alter, and drop)
(ii) Perform data manipulation (insert, update, and
delete)
(iii) Query data (select)
Data manipulation (DM)
fi
Structured Query Language (SQL)

Data de nition (DD)

- Purpose: (i) Create table structures (create, alter, and drop)
(ii) Perform data manipulation (insert, update, and
delete)
(iii) Query data (select)
Data manipulation (DM)
fi
Structured Query Language (SQL)

Data de nition (DD)

- Purpose: (i) Create table structures (create, alter, and drop)
(ii) Perform data manipulation (insert, update, and
delete)
(iii) Query data (select)
Data manipulation (DM)
fi
Querying Rows from a Table

- The ability of ef ciently using programming languages

consist two things: (1) knowing language syntaxes; (2)
applying logical thinking to solve a speci c problem

- How would you retrieve the following information?

- Top 100 selling products
- The IDs of top 10 Salespersons with the highest
revenue generation
fi
fi
Sample Database

ORDER_(OrderNum, CustID, ProductID, ProductType, Quantity, UnitPrice)

CREATE TABLE ORDER_(

OrderNum INTEGER PRIMARY KEY,
CustID INTEGER NOT NULL,
ProductID INTEGER NOT NULL,
ProductType VARCHAR(20) NOT NULL,
Quantity INTEGER DEFAULT 1,
UnitPrice DECIMAL(5,2) NOT NULL
);

INSERT INTO ORDER_ VALUES (1, 11, 101, 'Groceries', 5, 2.49);

INSERT INTO ORDER_ VALUES (2, 11, 102, 'Groceries', 2, 1.49);
INSERT INTO ORDER_ VALUES (3, 11, 201, 'Electronics', 1, 99.90);
INSERT INTO ORDER_ VALUES (4, 22, 201, 'Electronics', 2, 99.90);
INSERT INTO ORDER_ VALUES (5, 33, 201, 'Electronics', 1, 99.90);
INSERT INTO ORDER_ VALUES (6, 33, 301, 'Home Improvements', 1, 199.90);
INSERT INTO ORDER_ VALUES (7, 44, 101, 'Groceries', 3, 2.49);
INSERT INTO ORDER_ VALUES (8, 44, 301, 'Home Improvements', 1, 199.90);
INSERT INTO ORDER_ VALUES (9, 55, 101, 'Groceries', 10, 2.49);
INSERT INTO ORDER_ VALUES (10, 55, 102, 'Groceries', 5, 1.49);
INSERT INTO ORDER_ VALUES (11, 55, 201, 'Electronics', 1, 99.90);
INSERT INTO ORDER_ VALUES (12, 55, 301, 'Home Improvements', 2, 199.90);
Querying Rows from a Table

SELECT column_list
FROM table
WHERE [condition_list];
Column + equality or inequality symbols + value
(e.g., gender = “male”; note that case matters for
text)
- When * is used instead of a list of column names, the
Will
query returns information on all the columns in a table
come
- Additional conditions: GROUP BY, HAVING, ORDER BY back
- Aggregate functions: COUNT, MIN, MAX, SUM, AVG to this
later
ex) Retrieve the following from the ORDER table:
1. Information on grocery sales
2. Ids, quantities and unit prices of the
electronics products sold
3. Customers who have purchased items
more expensive than $100
Querying Rows from a Table - Comparison Operators

= Equal to
< Less than
<= Less than or equal to
> Greater than
>= Greater than or equal to
!= Not equal to

- Useful when de ning conditions

- Works not only for numbers but also for characters and dates
e.g., Try:
- SELECT ProductType FROM ORDER_ WHERE
ProductType < “H”;
- SELECT ProductType FROM ORDER_ WHERE
ProductType < “h”;
(In other DBMSs such as SQLite, the above two commands should produce different results,
but in MySQL, uppercase and lowercase letters are not differentiated, and thus the same
result will be returned)
- SELECT ProductType FROM ORDER_ WHERE
ProductType < “hp”;
- ALTER TABLE ORDER_ MODIFY ProductType VARCHAR(20)
CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
fi
Querying Rows from a Table - Comparison Operators

= Equal to
< Less than
<= Less than or equal to
> Greater than
>= Greater than or equal to
!= Not equal to

- Useful when de ning conditions

- Works not only for numbers but also for characters and dates
e.g., Try:
- SELECT ProductType FROM ORDER_ WHERE
ProductType < “H”;
- SELECT ProductType FROM ORDER_ WHERE
ProductType < “h”;
(In other DBMSs such as SQLite, the above two commands should produce different results,
but in MySQL, uppercase and lowercase letters are not differentiated, and thus the same
result will be returned)
- SELECT ProductType FROM ORDER_ WHERE
ProductType < “hp”;
- ALTER TABLE ORDER_ MODIFY ProductType VARCHAR(20)
CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
fi
Querying Rows from a Table - Arithmetic Operators

+ Add
- Subtract
* Multiply
/ Divide
^ Raise to the power of

- Useful when deriving calculated columns or conditions

- e.g., 1) SELECT column1, column2, column1*column2
FROM table1; (derived attribute vs. calculated column)
2) SELECT column1, column2
FROM table1 WHERE column3*column4 > 100;

ex) 1. For the orders from the home improvements department,

customer ID and order amount
2. Orders with their amount larger than $100
Querying Rows from a Table - Alias

- Used for renaming columns, especially ones

derived using special operators (e.g., *, /, +, -,
etc.)
- Syntax: SELECT column_old AS column_new
FROM table;

ex) Of the orders from the home improvements

department, retrieve customer ID and order
amount information and rename the retrieved
columns as “ID” and “OAmount”
Querying Rows from a Table - Logical Operators

- Used for declaring multiple conditions

- AND, OR, and NOT

ex) 1. Retrieve sales information of the

electronics department with each sales
amount larger than $150
2. Retrieve sales information of the
electronics or home improvement
departments with each sales amount larger
than $150
Querying Rows from a Table - Special Operators

- Used for declaring special conditions

- BETWEEN: check whether values are within a range (syntax:
column_name BETWEEN value1 and value2)
- IS (NOT) NULL: check whether values are null (syntax:
column_name IS NULL)
- IN: check whether values match a value within a value list
(syntax: column_name IN (value1, value2, …, valueN))

ex) 1. Retrieve sales information with sales amount larger than

$100 and smaller than $200 (Two ways to do this)
2. Retrieve sales information without a customer ID
3. Retrieve rows whose sales amounts belong to either $99.9
or $24.9 (Two ways to do this)
Querying Rows from a Table - Special Operators

- Used for declaring special conditions

- BETWEEN: check whether values are within a range (syntax:
column_name BETWEEN value1 and value2)
- IS (NOT) NULL: check whether values are null (syntax:
NULL is column_name
not equal to anything
IS NULL)
- IN: check(It is not a value)
whether values match a value within a value list
→column_name
(syntax: Always produceIN FALSE
(value1, value2, …, valueN))
when used with equality or
inequality signs
ex) 1. Retrieve sales information with sales amount larger than
$100 and smaller than $200 (Two ways to do this)
2. Retrieve sales information without a customer ID
3. Retrieve rows whose sales amounts belong to either $99.9
or $24.9 (Two ways to do this)
Querying Rows from a Table - Special Operators

- Used for declaring special conditions

- BETWEEN: check whether values are within a range (syntax:
column_name BETWEEN value1 and value2)
- IS (NOT) NULL: check whether values are null (syntax:
column_name IS NULL)
- IN: check whether values match a value within a value list
(syntax: column_name IN (value1, value2, …, valueN))

ex) 1. Retrieve sales information with sales amount larger than

$100 and smaller than $200 (Two ways to do this)
2. Retrieve sales information without a customer ID
3. Retrieve rows whose sales amounts belong to either $99.9
or $24.9 (Two ways to do this)