Database Design SQL (1)

1. Database DesignSQL (1) John Wordsworth Department of Computer Science The University of Reading J.B.Wordsworth@rdg.ac.uk Room 129, Ext 6544 Information Systems Design John Ogden & John Wordsworth

2. Lecture objectives Outline the history of SQL. Distinguish data definition, data manipulation, and data control languages. Use SQL to perform projections and selections on tables. Use the COUNT, GROUP, and ORDER BY options of SQL SELECT. Use SQL for Cartesian product and equijoin. List the column aggregate functions and their uses. Information Systems Design John Ogden & John Wordsworth

3. Overview of SQL Language invented in IBM in the 1970s to support relational databases, and now an international standard. Three aspects: A Data Definition Language A Data Manipulation Language SELECT UPDATE INSERT DELETE A Data Control Language Information Systems Design John Ogden & John Wordsworth

4. Tables used throughout the SQL slides (1) Book Information Systems Design John Ogden & John Wordsworth

5. Tables used throughout the SQL slides (2) Qty Publisher Information Systems Design John Ogden & John Wordsworth

6. SQL : DDL • Most important commands: • CREATE TABLE • CREATE INDEX • ALTER TABLE • DROP TABLE • DROP INDEX CREATE TABLE Book ( ID INTEGER NOT NULL, Title CHARACTER (35) NOT NULL, Author CHARACTER (25) NOT NULL, Subject CHARACTER (25) NOT NULL ) ; ALTER TABLE Book ADD Bind CHARACTER (1) NOT NULL; Information Systems Design John Ogden & John Wordsworth

7. SELECT syntax SELECT [ DISTINCT | ALL ] { * | column_expression [ AS newname ] [ , ... ] } FROM table_name [ alias ] [ , ... ] [ WHERE condition ] [ GROUP BY column_list ] [ HAVING condition ] [ ORDER BY column_list ] ; Information Systems Design John Ogden & John Wordsworth

8. Projection SELECT * FROM Book ; denotes the whole table Book. SELECT DISTINCT Title FROM Book ; denotes the following table: Information Systems Design John Ogden & John Wordsworth

9. Selection SELECT DISTINCT Title, Author FROM Book WHERE Subject = ‘Databases’; Information Systems Design John Ogden & John Wordsworth

10. Using COUNT and GROUP BY SELECT Subject, COUNT(*) AS SCount FROM Book GROUP BY Subject ; Information Systems Design John Ogden & John Wordsworth

11. Using ORDER BY SELECT ID, Title, Author, Bind FROM Book WHERE Subject = ‘Databases’ ORDER BY ID ; Information Systems Design John Ogden & John Wordsworth

12. Cartesian product SELECT Book.*, Qty.* FROM Book, Qty ; denotes the Cartesian product of Book and Qty, 7 columns and 64 rows. Information Systems Design John Ogden & John Wordsworth

13. Equijoin with projection SELECT Title, Bind, No FROM Book, Qty WHERE Book.ID = Qty.ID ; Information Systems Design John Ogden & John Wordsworth

14. Equijoin with selection SELECT ID, Title, Author FROM Book, Publisher WHERE Book.ID = Publisher.ID AND Publisher.Pub_Name = ‘Addison Wesley’ ORDER BY Author; Information Systems Design John Ogden & John Wordsworth

15. Equijoin with other conditions SELECT Pub_Name, SUM(Qty.No) FROM Publisher, Qty WHERE Publisher.ID = Qty.ID GROUP BY Pub_Name HAVING SUM (Qty.No) > 1 Information Systems Design John Ogden & John Wordsworth

16. Column aggregate functions COUNT: the number of elements in each group SUM: the sum of the numeric values of an attribute in a group AVG: the average of the numeric valued of an attribute in a group MAX: the greatest numeric value of an attribute in a group MIN: the least numeric value of an attribute in a group Information Systems Design John Ogden & John Wordsworth

17. Key points SQL has been developed over many years, and is now an international standard. SQL provides commands for defining databases and tables, for making queries and updates, and for creating views and setting security options. The SELECT statement is used for projection, selection, Cartesian product, and equijoin. The column aggregate functions are COUNT, SUM, AVG, MIN, MAX; they need a GROUP BY clause to fix their scope. Information Systems Design John Ogden & John Wordsworth