[go: up one dir, main page]
Scribd
Upload
3 views26 pages

10.sql 4

Uploaded by

Vũ Ngân Giang
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
3 views26 pages

10.sql 4

Uploaded by

Vũ Ngân Giang
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 26

BUSS 453

(SQL-Part IV)
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)
Advanced Data manipulation (DM)
Functions
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)
Advanced Data manipulation (DM)
Functions
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)
Advanced Data manipulation (DM)
Functions
fi
CREATE TABLE ORDER_(
OrderNum INTEGER PRIMARY KEY NOT NULL,
CustID INTEGER NOT NULL,
ProductID INTEGER NOT NULL,
ProductType VARCHAR(20),
Quantity INTEGER DEFAULT 1,
UnitPrice DECIMAL(5,2) NOT NULL,
OrderDate DATETIME
);

INSERT INTO ORDER_ VALUES (1, 11, 101, "Groceries", 5, 2.49, '2019-01-02');
INSERT INTO ORDER_ VALUES (2, 11, 102, "Groceries", 2, 1.49, '2019-02-03');
INSERT INTO ORDER_ VALUES (3, 11, 201, "Electronics", 1, 99.99, '2019-03-11');
INSERT INTO ORDER_ VALUES (4, 22, 201, "Electronics", 2, 99.99, '2019-04-07');
INSERT INTO ORDER_ VALUES (5, 33, 201, "Electronics", 1, 99.99, '2019-05-25');
INSERT INTO ORDER_ VALUES (6, 33, 301, "Home Improvements", 1, 199.9, '2019-07-22');
INSERT INTO ORDER_ VALUES (7, 44, 101, "Groceries", 3, 2.49, '2019-08-15');
INSERT INTO ORDER_ VALUES (8, 44, 301, "Home Improvements", 1, 199.9, '2019-09-04');
INSERT INTO ORDER_ VALUES (9, 55, 101, "Groceries", 10, 2.49, '2019-10-11');
INSERT INTO ORDER_ VALUES (10, 55, 102, "Groceries", 5, 1.49, '2019-10-11');
INSERT INTO ORDER_ VALUES (11, 55, 201, "Electronics", 1, 99.99, '2019-10-11');
INSERT INTO ORDER_ VALUES (12, 55, 301, "Home Improvements", 2, 199.99, '2020-04-02');
INSERT INTO ORDER_ VALUES (13, 55, 301, NULL, 1, 199.99, '2020-04-02');
INSERT INTO ORDER_ VALUES (14, 66, 401, NULL, 1, 199.99, '2020-04-02');
Processing Sequence of SELECT Command

- SELECT * FROM ORDER_ WHERE Quantity = 10;

- SELECT CustID, SUM(Quantity) FROM ORDER_


WHERE ProductType = “Electronics” GROUP BY CustID
HAVING SUM(Quantity) > 10
ORDER BY CustID DESC;

- FROM → WHERE → GROUP BY → HAVING → SELECT →


ORDER BY
Processing Sequence of SELECT Command

(1)
- SELECT * FROM ORDER_ WHERE Quantity = 10;

- SELECT CustID, SUM(Quantity) FROM ORDER_


WHERE ProductType = “Electronics” GROUP BY CustID
HAVING SUM(Quantity) > 10
ORDER BY CustID DESC;

- FROM → WHERE → GROUP BY → HAVING → SELECT →


ORDER BY
Processing Sequence of SELECT Command

(1) (2)
- SELECT * FROM ORDER_ WHERE Quantity = 10;

- SELECT CustID, SUM(Quantity) FROM ORDER_


WHERE ProductType = “Electronics” GROUP BY CustID
HAVING SUM(Quantity) > 10
ORDER BY CustID DESC;

- FROM → WHERE → GROUP BY → HAVING → SELECT →


ORDER BY
Processing Sequence of SELECT Command

(3) (1) (2)


- SELECT * FROM ORDER_ WHERE Quantity = 10;

- SELECT CustID, SUM(Quantity) FROM ORDER_


WHERE ProductType = “Electronics” GROUP BY CustID
HAVING SUM(Quantity) > 10
ORDER BY CustID DESC;

- FROM → WHERE → GROUP BY → HAVING → SELECT →


ORDER BY
Processing Sequence of SELECT Command

(3) (1) (2)


- SELECT * FROM ORDER_ WHERE Quantity = 10;

(1)
- SELECT CustID, SUM(Quantity) FROM ORDER_
WHERE ProductType = “Electronics” GROUP BY CustID
HAVING SUM(Quantity) > 10
ORDER BY CustID DESC;

- FROM → WHERE → GROUP BY → HAVING → SELECT →


ORDER BY
Processing Sequence of SELECT Command

(3) (1) (2)


- SELECT * FROM ORDER_ WHERE Quantity = 10;

(1)
- SELECT CustID, SUM(Quantity) FROM ORDER_
(2) WHERE ProductType = “Electronics” GROUP BY CustID
HAVING SUM(Quantity) > 10
ORDER BY CustID DESC;

- FROM → WHERE → GROUP BY → HAVING → SELECT →


ORDER BY
Processing Sequence of SELECT Command

(3) (1) (2)


- SELECT * FROM ORDER_ WHERE Quantity = 10;

(1)
- SELECT CustID, SUM(Quantity) FROM ORDER_
(2) WHERE ProductType = “Electronics” GROUP BY CustID (3)
HAVING SUM(Quantity) > 10
ORDER BY CustID DESC;

- FROM → WHERE → GROUP BY → HAVING → SELECT →


ORDER BY
Processing Sequence of SELECT Command

(3) (1) (2)


- SELECT * FROM ORDER_ WHERE Quantity = 10;

(1)
- SELECT CustID, SUM(Quantity) FROM ORDER_
(2) WHERE ProductType = “Electronics” GROUP BY CustID (3)
(4) HAVING SUM(Quantity) > 10
ORDER BY CustID DESC;

- FROM → WHERE → GROUP BY → HAVING → SELECT →


ORDER BY
Processing Sequence of SELECT Command

(3) (1) (2)


- SELECT * FROM ORDER_ WHERE Quantity = 10;

(5) (1)
- SELECT CustID, SUM(Quantity) FROM ORDER_
(2) WHERE ProductType = “Electronics” GROUP BY CustID (3)
(4) HAVING SUM(Quantity) > 10
ORDER BY CustID DESC;

- FROM → WHERE → GROUP BY → HAVING → SELECT →


ORDER BY
Processing Sequence of SELECT Command

(3) (1) (2)


- SELECT * FROM ORDER_ WHERE Quantity = 10;

(5) (1)
- SELECT CustID, SUM(Quantity) FROM ORDER_
(2) WHERE ProductType = “Electronics” GROUP BY CustID (3)
(4) HAVING SUM(Quantity) > 10
(6) ORDER BY CustID DESC;

- FROM → WHERE → GROUP BY → HAVING → SELECT →


ORDER BY
Advanced Functions and Clauses

- COUNT, MIN, MAX, SUM, AVG (i.e., aggregate functions)


- GROUP BY
- HAVING
- DISTINCT
- ORDER BY
Aggregate Functions
COUNT - count the number of rows containing non-null values
MIN Try:
SELECT COUNT(ProductType) FROM ORDER_;
MAX SELECT COUNT(OrderNum) FROM ORDER_;
SUM → Di erent results because of null values
AVG

COUNT(columnName)
e.g., COUNT(OrderNum) counts the number of orders
often used in conjunction with DISTINCT
COUNT(DISTINCT(columnName))
e.g., COUNT(DISTINCT(ProductType) counts
the number of distinct product types

How many unique customers are there in the ORDER_ table?


ff
Aggregate Functions
COUNT
MIN - get the minimum attribute value in a speci ed column
MAX - get the maximum attribute value in a speci ed column
SUM
AVG
MIN(columnName)
e.g., MIN(OrderDate) retrieves the date when the rst order
has been made
or
MAX(columnName)
e.g., MAX(OrderDate) retrieves the date when the last order
has been made

1) What is the ID of the product in the DB with the highest unit


price?
2) What is the order number, sales amount, and order date of
the latest order?
fi
fi
fi
Aggregate Functions
COUNT
MIN
MAX
SUM - get the sum of all values for a given column
AVG
SUM(column)
e.g., SUM(Quantity) returns the total number of
products ordered

ex) What is the total sales amount?


By product IDs?
By product types?
By both product types and IDs?
Aggregate Functions
COUNT
MIN
MAX
SUM
AVG - get the average of all values for a given column

AVG(column)
e.g., AVG(Quantity) returns the average number of products
ordered in each order

ex) What is the average amount of sales for each order?


By customer?
GROUP BY

SELECT columns
FROM tableName
GROUP BY columns;

- This command groups rows with the same values in a column to


produce summary (or aggregated) information
- Mostly used in conjunction with at least one aggregate function (e.g.,
SUM, AVG)
- Multiple columns can be applied for GROUP BY

ex) - What is the sum of the order quantity made by each customer?
- Further sort the above information by product type.
HAVING
SELECT columns
FROM tableName
GROUP BY columns
HAVING conditions with aggregate values;

- WHERE cannot handle conditions containing aggregate values and aliases


- SELECT CustID, Sum(Quantity*UnitPrice) FROM ORDER_ WHERE Sum(Quantity*UnitPrice) > 100 GROUP BY CustID;
SELECT CustID, Sum(Quantity*UnitPrice) FROM ORDER_ GROUP BY CustID HAVING Sum(Quantity*UnitPrice) > 100;
- SELECT CustID, ProductType AS PT FROM ORDER_ WHERE PT = ‘Electronics’; vs
SELECT CustID, ProductType AS PT FROM ORDER_ HAVING PT = ‘Electronics’;

- HAVING can be used with or without GROUP BY (works as a post- lter as opposed to WHERE which works as a pre-
lter)
- SELECT CustID FROM ORDER_ HAVING ProductType = ‘Electronics’; vs. SELECT CustID FROM ORDER_ WHERE
ProductType = ‘Electronics’; vs. SELECT CustID, ProductType FROM ORDER_ HAVING ProductType = ‘Electronics’;

- Multiple conditions are possible (e.g., SELECT CustID FROM ORDER_ GROUP BY CustID HAVING Count(CustID) > 2
AND SUM(Quantity) > 10;)

ex) - What is the order frequency of the product type “Groceries”?


- What are the IDs of customers with the total order quantity larger than 5
and with the total order amount larger than $500?
- What is the total sales amount for each customer who purchased electronics products?
fi
fi
ORDER BY

SELECT columns
FROM tableName
ORDER BY columnList [ASC|DESC];
ORDER BY

SELECT columns
FROM tableName
ORDER BY columnList [ASC|DESC];
default
ORDER BY

SELECT columns
FROM tableName
ORDER BY columnList [ASC|DESC];
default
When multiple columns are input,
they are applied in sequence
(need to specify ASC|DESC for each column)
ORDER BY

SELECT columns
FROM tableName
ORDER BY columnList [ASC|DESC];
default
When multiple columns are input,
they are applied in sequence
(need to specify ASC|DESC for each column)

- e.g., SELECT * FROM ORDER_; vs. SELECT * FROM ORDER_ ORDER BY OrderNum DESC
- Combined with the LIMIT command, you can easily retrieve the highest or lowest value in a
column
e.g., SELECT * FROM ORDER_ ORDER BY Quantity DESC LIMIT 1;

ex)
- List distinct IDs of the customers in a descending order based on their average purchase amount
- List the ID of the customer with the highest average purchase amount
- List the ID of the customer with top 3 average purchase amount

You might also like