Introduction to Structured Query Language SQL

1
Chapter 2
Introduction to Structured Query Language (SQL)?
2
Contents
A. Storage Management Problem B. Solution
3
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 name
• Product type
• Quantity which is currently available in storage
• Product Price
• Manufacturer
• Each manufacturer has its name and address.
• Each manufacturer produces one or more products.
• For example data of some products, prefer to next
  slide.

4

• Example 1
• Product name 512 GB DDRAM
• Product type RAM
• Quantity 10
• Price 30
• Manufacturer name Yuki
• Manufacturer address Tokyo

• Example 2
• Product name 1 GB DDRAM
• Product type RAM
• Quantity 10
• Price 40
• Manufacturer name Yuki
• Manufacturer address Tokyo

5

• 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.
• 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.

6

• 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 DDRAM. Display information of all
  products which its names must contain in
  following set BOXDG, 256GB DDRAM.
• Suppose there are some products have name is
  BOXDG and 256 DDRAM. Display information of
  product which its names must NOT contain in
  following set BOXDG,256GB 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.

7

• Suppose there are some products has name includes
  G3 letters Display information of product.
  These product names must include G3 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

8

• 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.

9
B. Solution

• Logical Design
• Physical Design
• Creating DB and populating data into the DB
• SQL Statement Solutions

10
1. Logical Design
Following the problem description and data
examples, we have logical design of Product.
1
11
2. Physical Design
12
Creating DB and populating data into the DB

• This is an exercise for you.
• The following are sample data.

13
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, 256GB
DDRAM. 4.9. Display product has name is NOT in
set BOXDG, 256GB DDRAM. 4.10. Display
product has 30 lt price lt100. 4.11. Display
product has name starts with GA letter. 4.12.
Display product has name includes G3 letter.
14
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.
15
4.1. Display PRODUCT NAME and QUANTITY
SELECT Name, Quantity FROM PRODUCTS
16
4.2. Display all column in PRODUCT table
Selecting All Columns The Asterisk ()
Keyword SELECT FROM PRODUCTS
17
4.3. Display unique PRODUCT TYPE
SELECT DISTINCT Type FROM PRODUCTS
18
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
19
4.5. Display all products are RAM
SELECT FROM PRODUCTS WHERE Type 'RAM
20
4.6. Display products are RAM and have quantity
greater than 20
SELECT FROM PRODUCTS WHERE Type 'RAM AND
Quantity gt 20
21
4.7. Display products are RAM or have quantity
greater than 5
SELECT FROM PRODUCTS WHERE Type 'RAM OR
Quantity gt 5
22
4.8. Display product has name is in set BOXDG,
256GB DDRAM
SELECT FROM PRODUCTS WHERE Name IN
('BOXDG, '256GB DDRAM')
23
4.9. Display product has name is NOT in set
BOXDG, 256GB DDRAM
SELECT FROM PRODUCTS WHERE Name NOT IN
('BOXDG,'256GB DRAM')
24
4.10. Display product has 30 lt price lt100
Solution 1 SELECT FROM PRODUCTS WHERE
Price BETWEEN 30 AND 100 Solution 2
SELECT FROM PRODUCTS WHERE Price gt
30 AND Price lt 100
25
4.11. Display product has name starts with GA
letter
SELECT FROM PRODUCTS WHERE Name LIKE
'GA'
26
4.12. Display product has name includes G3
letter
SELECT FROM PRODUCTS WHERE Name LIKE
'G3'
27
4.13. Display number of rows in PRODUCT table
SELECT COUNT() as numRows FROM PRODUCTS
28
4.14. Display SUM of quantity
SELECT SUM(Quantity) as sumQuantity FROM
PRODUCTS
29
4.15. Display AVERAGE of price
SELECT AVG(Price) as averagePrice FROM
PRODUCTS
30
4.16. Display MAXIMUM of price
SELECT MAX(Price) as maxPrice FROM PRODUCTS
31
4.17. Display MINIMUM of price
SELECT MIN(Price) as minPrice FROM
PRODUCTS
32
4.18. Display Value of each product
Note VALUE quantity price SELECT Name,
Quantity Price as Value FROM PRODUCTS
33
4.19. Display PRODUCT NAME, PRODUCT TYPE in format
SELECT Name ' is ' Type as detailName FROM
PRODUCTS
34
4.20. Display SUM of quantity of each PRODUCT TYPE
SELECT Type, SUM(Quantity) as
totalQuantity FROM PRODUCTS GROUP BY Type
35
4.21. Display SUM of quantity of each PRODUCT
TYPE and Sum is greater than 30
SELECT Type, SUM(Quantity) as totalQuantity FROM
PRODUCTS GROUP BY Type HAVING SUM(Quantity)
gt 30
36
4.22. Display product of Shiro manufacturer
SELECT Products.Name as ProductName,
Manufacturers.Name as ManufacturerName FROM
PRODUCTS, MANUFACTURERS WHERE Products.Man_ID
Manufacturers.Man_ID AND
Manufacturers.Name 'Shiro
37
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')

38
References

• Database Processing Chapter 2
• Fundamentals, Design and Implementation
• David M. Kroenke

39
?