1 / 16

Introduction to Structured Query Language (SQL)

Introduction to Structured Query Language (SQL). Origins & history. Early 1970’s – IBM develops Sequel as part of the System R project at its San Hose Research Lab; 1986 - ANSI & ISO publish the standard SQL-86;

adrianv
Download Presentation

Introduction to Structured Query Language (SQL)

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. Introduction to Structured Query Language (SQL)

  2. Origins & history Early 1970’s – IBM develops Sequel as part of the System R project at its San Hose Research Lab; 1986 - ANSI & ISO publish the standard SQL-86; 1987 – IBM publishes its own “standard” SQL called Systems Architecture Database Interface (SAA-SQL); 1989 – SQL-89 published by ANSI (extended version of SQL-86); 1992 – SQL-92 published with better support for algebraic operations; 1999 – SQL-1999 published with support for typing, stored procedures, triggers, BLOBs etc. SQL-92 remains the most widely implemented standard – and most database vendors also provide their own (proprietary) extensions.

  3. Components of SQL The SQL language has several parts: • Data-definition language (DDL). The SQL DDL provides commands for defining relation schemas, deleting relations, and modifying relation schemas. • Interactive data-manipulation language (DML). The SQL DML includes a query language based on both the relational algebra and the tuple relational calculus. It includes also commands to insert tuples into, delete tuples from, and modify tuples in the database. • View definition. The SQL DDL includes commands for defining views. • Transaction control. SQL includes commands for specifying the beginning and ending of transactions. • Embedded SQL and dynamic SQL. Embedded and dynamic SQL define how SQL statements can be embedded within general-purpose programming languages, such as C, C++, Java, PL/I, Cobol, Pascal, and Fortran. • Integrity. The SQL DDL includes commands for specifying integrity constraints that the data stored in the database must satisfy. Updates that violate integrity constraints are disallowed. • Authorization. The SQL DDL includes commands for specifying access rights to relations and views.

  4. SQL Example (1) • The Supplier-and-Parts Database sp s p

  5. SQL Example (2) • Project the columns renamed columns: SELECT sname FROM s SELECT sname AS Supplier, status * 5 AS 'Status times Five' FROM s computed columns: SELECT sname, status * 5 FROM s

  6. SQL Example (3) • Restrict the rows SELECT * FROM s WHERE city=‘London’ complex condition: SELECT * FROM s WHERE city=‘London’ OR status = 30

  7. SQL Example (4) • Restrict & Project SELECT city FROM s WHERE sname='smith' OR status='20' remove duplicate rows: SELECT DISTINCT city FROM s WHERE sname='smith' OR status='20'

  8. SQL Example (4) Group By and Having • Use the ‘GROUP BY’ clause to aggregate related rows SELECT city, SUM(status) AS 'Total Status' FROM s GROUP BY city Use the ‘HAVING’ clause to restrict rows aggregated with ‘GROUP BY’ SELECT city, SUM(status) AS 'Total Status' FROM s GROUP BY city HAVING SUM(status) > 30

  9. SQL Functions • SQL provides a wide range of predefined functions to perform manipulation of data. • Four types of functions arithmetic (sqrt(), log(), mod(), round() …) date (sysdate(), month(), dayname() …) character (length(), lower(), upper()…) aggregate (min(), max(), avg(), sum() …)

  10. Joins (1) • The m-f Database m f

  11. Joins (3) • Product (or Cartesian Product) SELECT * FROM m, f Synonymous with the CROSS JOIN, hence: SELECT * FROM m CROSS JOIN f; would return the same result. This is not very useful but is the basis for all other joins.

  12. Joins (4) • Natural join Joins tables using some shared characteristic – usually (but not necessarily) a foreign key. SELECT * FROM m,f WHERE m.age = f.age

  13. Joins (5) • Inner joins The previous example, besides being a natural join, is also an example of an inner join. An inner join retrieves data only from those rows where the join condition is met. SELECT * FROM m,f WHERE m.age > f.age

  14. Joins (6) • Outer joins Unmatched rows can be included in the output using as outer join. Left outer join: SELECT * FROM m LEFT OUTER JOIN f ON m.age = f.age Right outer join: SELECT * FROM m RIGHT OUTER JOIN f ON m.age = f.age

  15. Joins (7) • Self Join Special case of the inner join – here the table employee shows employees and their managers. Ruth manages Joe who manages Tom, Dick and Harry. Show who manages who by name: SELECT E1.emp_name AS Employee, E2.emp_name AS ManagerFROM employee AS E1INNER JOIN employee AS E2 ON E1.mgr_id = E2.emp_id

  16. Bibliography / Readings / Home based activities Bibliography • An Introduction to Database Systems (8th ed.), C J Date, Addison Wesley 2004 • Database Management Systems, P Ward & G Defoulas, Thomson 2006 • Database Systems Concepts (4th ed.), A Silberschatz, H F Korth & S Sudarshan, McGraw-Hill 2002 Readings • Introduction to SQL’ McGraw-Hill/Osbourne (handout) Home based activities • Ensure you download xampp and install on home PC or laptop (if you have a slow home internet connection – download to data key or CD here at UWE) • Copy the SQL Workbook onto your data key or CD. • Import the tables from the SQL Workbook into your home MySQL DB. Begin working through some of the query examples in the workbook using PHPMyAdmin.

More Related