1 / 39

Chapter 2

Chapter 2. Introduction to Structured Query Language (SQL) ‏. Contents. A. Storage Management Problem B. Solution. A. Storage Management Problem. Due to manage the product in storage, Algo company needs a database which enables accessibility efficiently.

sari
Download Presentation

Chapter 2

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

  4. Example 1: • Product ID: PRO1 • 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: PRO2 • Product name: 1 GB DDRAM • Product type: RAM • Quantity: 10 • Price: 40 $ • Manufacturer ID: YUK • 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 256MB DDRAM. Display information of all products which its names must contain in following set: [“BOXDG”, “256MB DDRAM”]. • Suppose there are some products have name is “BOXDG” and 256MB DDRAM. Display information of product which its names must NOT contain in following set: [“BOXDG”,“256MB 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.

  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”, “256MB DDRAM”]. 4.9. Display product has name is NOT in set [“BOXDG”, “256MB 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 “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 > 20;

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

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

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

  24. 4.10. Display product has 30 <= price <=100 Solution 1: SELECT * FROM PRODUCTS WHERE Price BETWEEN 30 AND 100; Solution 2: SELECT * FROM PRODUCTS WHERE Price >= 30 AND Price <= 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 ' + Typeas 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) > 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. ?

More Related