Chapter 2 Introduction to Structured Query Language SQL

Chapter 2 Introduction to Structured Query Language (SQL) Slide 1

Contents A. Storage Management Problem B. Solution Slide 2

A. Storage Management Problem Due to manage the product in storage, Algo company needs a database which enables accessibility efficiently. In their system, product has following information: Product ID Product name Product type Quantity which is currently available in storage Product Price Manufacturer Each manufacturer has its ID, name and address. Each manufacturer produces one or more products. For example data of some products, prefer to next slide. Slide 3

Example 1: Slide 4 Product ID: PRO 1 Product name: 512 MB DDRAM Product type: RAM Quantity: 10 Price: 30 $ Manufacturer ID: YUK Manufacturer name: Yuki Manufacturer address: Tokyo Example 2: Product ID: PRO 2 Product name: 1 GB DDRAM Product type: RAM Quantity: 10 Price: 40 $ Manufacturer ID: YUK Manufacturer name: Yuki Manufacturer address: Tokyo

• Display information of product name and quantity of all products. • Display all information of all products. • Display product type in product table. However, this result must be unique. • Display all information of all products. Present result sorted by product name in descending order and then quantity in ascending order. • Suppose there are some products which have type is RAM. Display information of all products which have product type is RAM and quantity greater than 20. Slide 5

• Suppose there are some products which have type is RAM. Display information of all products which have product type is RAM or has quantity greater than 5. • Suppose there are some products have name is “BOXDG” and 256 MB DDRAM. Display information of all products which its names must contain in following set: [“BOXDG”, “ 256 MB DDRAM”]. • Suppose there are some products have name is “BOXDG” and 256 MB DDRAM. Display information of product which its names must NOT contain in following set: [“BOXDG”, “ 256 MB DDRAM”]. • Display information of all products. Price of these product must be greater than or equals 30 and less than or equals 100. • Suppose there are some products have name start with ‘GA’. Display information of product which its names must be started with ‘GA’ letters. Slide 6

• Suppose there are some products has name includes “G 3” letters Display information of product. These product names must include ‘G 3’ letters. • Display number of all products in storage. • Display sum of quantity of all products. • Display Average of price of all products. • Display Maximum price of all products. • Display Minimum price of all products. • Display value of each product in storage. The function of value is: VALUE = quantity * price Slide 7

• Display information of product name and product type. The format of this result is: “PRODUCT_NAME” is "PRODUCT_TYPE“ • There are currently has two TYPE of product includes: Main board and RAM. Display the sum of quantity for each TYPE of product. • Display product type which has Sum of quantity greater than 30. • Suppose there are some products which were produced by Shiro manufacturer. Display name and manufacturer name of the product which its manufacturer is Shiro. • Suppose there are some products which were produced by Shiro manufacturer and Sanno manufacturer. Display name of product which its manufacturer is Yuki and Sanno. Slide 8

B. Solution 1. 2. 3. 4. Slide 9 Logical Design Physical Design Creating DB and populating data into the DB SQL Statement Solutions

1. Logical Design Following the problem description and data examples, we have logical design of Product. Slide 10

2. Physical Design Slide 11

Creating DB and populating data into the DB • This is an exercise for you. • The following are sample data. Slide 12

4. SQL Statement Solutions 4. 1. Display PRODUCT NAME and QUANTITY. 4. 2. Display all column in PRODUCT table. 4. 3. Display unique PRODUCT TYPE. 4. 4. Display PRODUCT NAME in descending order, QUANTITY in ascending order. 4. 5. Display all products are RAM. 4. 6. Display products are RAM and have quantity greater than 20. 4. 7. Display products are RAM or have quantity greater than 5. 4. 8. Display product has name is in set [“BOXDG”, “ 256 MB DDRAM”]. 4. 9. Display product has name is NOT in set [“BOXDG”, “ 256 MB DDRAM”]. 4. 10. Display product has 30 <= price <=100. 4. 11. Display product has name starts with “GA” letter. 4. 12. Display product has name includes “G 3” letter. Slide 13

4. SQL Statement Solutions 4. 13. Display number of rows in PRODUCT table. 4. 14. Display SUM of quantity. 4. 15. Display AVERAGE of price. 4. 16. Display MAXIMUM of price. 4. 17. Display MINIMUM of price. 4. 18. Display Value of each product. 4. 19. Display PRODUCT NAME, PRODUCT TYPE in format. 4. 20. Display SUM of quantity of each product type. 4. 21. Display SUM of quantity of each product type and Sum is greater than 30. 4. 22. Display product of Shiro manufacturer. 4. 23. Display product of Yuki and Sanno manufacturers. Slide 14

4. 1. Display PRODUCT NAME and QUANTITY SELECT Name, Quantity FROM PRODUCTS; Slide 15

4. 2. Display all column in PRODUCT table Selecting All Columns: The Asterisk (*) Keyword SELECT * FROM PRODUCTS; Slide 16

4. 3. Display unique PRODUCT TYPE SELECT DISTINCT Type FROM PRODUCTS; Slide 17

4. 4. Display PRODUCT NAME in descending order, QUANTITY in ascending order Sort Order: Ascending and Descending SELECT * FROM PRODUCTS ORDER BY Name DESC, Quantity ASC; Slide 18

4. 5. Display all products are RAM SELECT * FROM PRODUCTS WHERE Type = 'RAM‘; Slide 19

4. 6. Display products are RAM and have quantity greater than 20 SELECT * FROM PRODUCTS WHERE Type = 'RAM‘ AND Quantity > 20; Slide 20

4. 7. Display products are RAM or have quantity greater than 5 SELECT * FROM PRODUCTS WHERE Type = 'RAM‘ OR Quantity > 5; Slide 21

4. 8. Display product has name is in set [“BOXDG”, “ 256 MB DDRAM”] SELECT * FROM PRODUCTS WHERE Name IN ('BOXDG‘, '256 MB DDRAM'); Slide 22

4. 9. Display product has name is NOT in set [“BOXDG”, “ 256 MB DDRAM”] SELECT * FROM PRODUCTS WHERE Name NOT IN ('BOXDG‘, '256 MB DDRAM'); Slide 23

4. 10. Display product has 30 <= price <=100 Solution 1: SELECT WHERE Solution 2: SELECT WHERE Slide 24 * FROM PRODUCTS Price BETWEEN 30 AND 100; * FROM PRODUCTS Price >= 30 AND Price <= 100;

4. 11. Display product has name starts with “GA” letter SELECT * FROM PRODUCTS WHERE Name LIKE 'GA%'; Slide 25

4. 12. Display product has name includes “G 3” letter SELECT * FROM PRODUCTS WHERE Name LIKE '%G 3%'; Slide 26

4. 13. Display number of rows in PRODUCT table SELECT COUNT(*) as num. Rows FROM PRODUCTS; Slide 27

4. 14. Display SUM of quantity SELECT SUM(Quantity) as sum. Quantity FROM PRODUCTS; Slide 28

4. 15. Display AVERAGE of price SELECT AVG(Price) as average. Price FROM PRODUCTS; Slide 29

4. 16. Display MAXIMUM of price SELECT MAX(Price) as max. Price FROM PRODUCTS; Slide 30

4. 17. Display MINIMUM of price SELECT MIN(Price) as min. Price FROM PRODUCTS; Slide 31

4. 18. Display Value of each product Note: VALUE = quantity * price SELECT Name, Quantity * Price as Value FROM PRODUCTS; Slide 32

4. 19. Display PRODUCT NAME, PRODUCT TYPE in format SELECT Name + ' is ' + Type as detail. Name FROM PRODUCTS; Slide 33

4. 20. Display SUM of quantity of each PRODUCT TYPE SELECT Type, SUM(Quantity) as total. Quantity FROM PRODUCTS GROUP BY Type; Slide 34

4. 21. Display SUM of quantity of each PRODUCT TYPE and Sum is greater than 30 SELECT Type, SUM(Quantity) as total. Quantity FROM PRODUCTS GROUP BY Type HAVING SUM(Quantity) > 30; Slide 35

4. 22. Display product of Shiro manufacturer SELECT Products. Name as Product. Name, Manufacturers. Name as Manufacturer. Name FROM PRODUCTS, MANUFACTURERS WHERE Products. Man_ID = Manufacturers. Man_ID AND Manufacturers. Name = 'Shiro‘; Slide 36

4. 23. Display product of Yuki and Sanno manufacturers Solution 1: SELECT Name FROM PRODUCTS WHERE Man_ID IN (SELECT Man_ID FROM MANUFACTURERS WHERE Name IN ('Sanno', 'Yuki')); Solution 2: SELECT P. Name FROM PRODUCTS P, MANUFACTURERS M WHERE P. Man_ID = M. Man_ID AND M. Name IN ('Sanno', 'Yuki'); Slide 37

References Database Processing – Chapter 2 Fundamentals, Design and Implementation David M. Kroenke Slide 38

? Slide 39