1 / 40

A language that doesn't affect the way you think about programming is not worth knowing .

A language that doesn't affect the way you think about programming is not worth knowing . - Alan Perlis, “Epigrams in Computing”, SIGPLAN, 1982. Database Languages. Database languages provide features for: Building a database schema Retrieving data from a database

sheedy
Download Presentation

A language that doesn't affect the way you think about programming is not worth knowing .

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. A language that doesn't affect the way you think about programming is not worth knowing. - Alan Perlis, “Epigrams in Computing”, SIGPLAN, 1982

  2. Database Languages • Database languages provide features for: • Building a database schema • Retrieving data from a database • Manipulating data in a database • Two common languages: • Query-By-Example (QBE) • Structured Query Language (SQL)(Sections 9.5-8)

  3. Moshe M. ZloofQuery-by-Example (QBE) • Introduced by IBM in 1975 • Graphical interface to SQL • Has influenced the query interfaces of other DB systems: • Paradox • Access Image from www.hp.com July, 2001

  4. Query-By-Example

  5. Edgar F. CoddRelational Algebra/Calculus • Developed from 1971-1974 • Relational Algebra - a procedural language: • Relations • Relational operators • Relational Calculus - a declarative language with equivalent power. Image from www.computer.org, July, 2001

  6. Structured Query Language

  7. The Database Schema

  8. Access Query Types • Retrieval queries: • Select queries • Modification queries: • Make-table queries • Delete queries • Update queries • Append queries

  9. Projection Queries

  10. Selection Queries

  11. Conditions

  12. Join Queries

  13. Combining Operations

  14. Sorting

  15. Grouping & Aggregate Functions

  16. Arithmetic

  17. SQL • Structured Query Language: • Specially designed for data queries and updates • Command-line based • It is the industry standard

  18. Using SQL Access Interface Jet Engine Jet DB

  19. Using SQL Java Applet Access Interface VB Application JDBC Driver ODBC Driver ODBC Driver ODBC Driver ODBC Driver Oracle Engine Jet Engine SQL Server MSDE Engine Oracle DB Jet DB SQL Server DB MSDE DB

  20. Using SQL Network Web Browser Web Server JDBC Driver ODBC Driver DB Engine DB

  21. Basic Query Types • Single-table queries • Multiple-table queries • Aggregation and Grouping • Set Operations • Database Modifications

  22. SELECT Syntax SELECT <attributes or expressions> FROM <tables> [WHERE <conditions on the attributes>] [GROUP BY <attribute list> [HAVING <conditions>] ] [ORDER BY <attribute list>]

  23. Single-Table Queries Q: Get a list of all the products. SELECT * FROM Products;

  24. The SELECT Clause 1 Q: Get names, categories and prices of all the products. SELECT name, unitPrice, category FROM Products;

  25. The SELECT Clause 2 Q: Get the total value of each product in stock. SELECT name, (unitPrice * inStock) FROM Products;

  26. The SELECT Clause 3 Q: Can SELECT return duplicates or not? SELECT category FROM Products;

  27. The SELECT Clause 4 Q: Get a list of the category types for products. SELECT DISTINCT category FROM Products;

  28. The WHERE Clause 1 Q: Get the foreign customers. SELECT firstName, lastName, country FROM Customers WHERE country <> 'USA';

  29. The WHERE Clause 2 Q: Get the Customers at 100 Main Street, New York. SELECT firstName, lastName, street, city FROM Customers WHERE street='100 Main St.' AND city='New York';

  30. The WHERE Clause 3 Q: Get the products without images. SELECT name, image FROM Products WHERE image IS NULL;

  31. The ORDER BY Clause Q: Get the Employees in alphabetical order. SELECT lastName+', '+firstName AS fullName FROM Customers ORDER BY lastName, firstName;

  32. Multiple-Table Queries Q: Get the list of products for each customer order. SELECT orderId, name, quantity FROM Products, OrderDetails WHERE id=productID;

  33. Multiple-Table Queries 2 Q: Get the names of the products and customers that order them. SELECT name, firstName, lastName FROM Products, OrderDetails, Orders, Customers WHERE Products.ID=OrderDetails.productID AND OrderDetails.orderID = Orders.ID AND Orders.customerID = Customers.ID;

  34. Grouping and Aggregation 1 Q: Count the products in each category. SELECT category, Count(category) FROM Products GROUP BY category ORDER BY Count(category) DESC;

  35. Grouping and Aggregation 2 Q: Get the categories with more than 3 products. SELECT category, Count(category) FROM Products GROUP BY category HAVING Count(category) > 3;

  36. Set Operations Q: Get the names of all suppliers and customers. SELECT name FROM Suppliers UNION SELECT firstName+’ ’+lastName FROM Customers;

  37. Inserting Data Q: Add Wile E’s alter ego to the customers list. INSERT INTO Customers(id,firstName,lastName) VALUES (14,'Carnivorous','Vulgarus');

  38. Updating Data Q: Change Carnivorous’s address. UPDATE Customers SET street = '1 Cave Lane' WHERE id = 14;

  39. Deleting Data Q: Remove Carnivorous from the Customers table. DELETE FROM Customers WHERE id = 14;

  40. Importing External Data • Frequently, data from other sources must be imported in bulk. • Approaches: • an SQL INSERT command file • a specialized import facility

More Related