1 / 13

Chapter 3 Introduction to SQL

Chapter 3 Introduction to SQL. Yonsei University 1 st Semester, 2014 Sanghyun Park. Outline. History Basic Structure Aggregate Functions Nested Subqueries Database Modification. History. IBM Sequel language developed as part of System R project at the IBM San Jose Research Laboratory

lacey-roberson
Télécharger la présentation

Chapter 3 Introduction to 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. Chapter 3Introduction to SQL Yonsei University 1st Semester, 2014 Sanghyun Park

  2. Outline • History • Basic Structure • Aggregate Functions • Nested Subqueries • Database Modification

  3. History • IBM Sequel language developed as part of System R project at the IBM San Jose Research Laboratory • Renamed Structured Query Language (SQL) • ANSI and ISO standard SQL • SQL-86, SQL-89, SQL-92 • SQL:1999 (language name became Y2K compliant) • SQL:2003 • Commercial systems offer most SQL-92 features, plus varying feature sets from later standards and special proprietary features

  4. Schema Diagram for University Database

  5. Basic Structure • SQL is based on set and relational operations with certain modifications and enhancements • A typical SQL query has the form:select A1, A2, ..., Anfrom r1, r2, ..., rmwhere P • Aisrepresent attributes • risrepresent relations • P is a predicate • This query is equivalent to the relational algebra expression: ∏A1, A2, ..., An (P (r1 x r2 x ... x rm)) • The result of an SQL query is a relation

  6. SELECT Clause • The select clause lists the attributes desired in the query result • An asterisk in the select clause denotes “all attributes” • The select clause can contain arithmetic expressions • SQL allows duplicates in relations as well as in query results • Find department names of all instructors w/o duplicates: select distinct dept_namefrom instructor • The keyword all specifies that duplicates not be removed: select alldept_namefrom instructor

  7. WHERE Clause • The where clause specifies conditions the result must satisfy • To find all instructors in the Comp. Sci. dept with salary > 80000:select namefrom instructorwhere dept_name =‘Comp. Sci’ and salary > 80000 • Comparison results can be combined using the logical connectives and, or, and not • Comparisons can be applied to results of arithmetic expressions • SQL includes a between comparison operator

  8. FROM Clause • The from clause lists the relations involved in the query • Find the Cartesian product instructor x teaches:select *from instructor, teaches • Find the course ID, semester, year and title of each course offered by the Comp. Sci. dept:select section.course_id, semester, year, titlefrom section, coursewhere section.course_id = course.course_id anddept_name = ‘Comp. Sci.’

  9. Aggregate Functions • These functions operate on the multiset of values of a column,and return a single value • avg, min, max, sum, count • Find the average salary of instructors in the Comp. Sci. dept:select avg(salary)from instructorwhere dept_name = ‘Comp. Sci’ • Find the names and average salaries of all departments whose average salary is greater than 42000:select dept_name, avg (salary)from instructorgroup by dept_namehaving avg(salary) > 42000

  10. Nested Subqueries • SQL provides a mechanism for the nesting of subqueries • A subquery is a select-from-where expression that is nested within another query • A common use of subqueries is to perform tests for set membership, set comparisons, and set cardinality • Find courses offered both in Fall 2009 and in Spring 2010:select distinct course_idfrom sectionwhere semester = ‘Fall’and year = 2009and course_id in (select course_idfrom sectionwhere semester =‘Spring’ and year =2010)

  11. Database Modification - Delete • Delete all instructors from the Finance dept:deletefrom instructorwhere dept_name = ‘Finance’ • Delete all instructors whose salary is less than the average salary of instructors:deletefrom instructorwhere salary < (select avg (salary)from instructor)

  12. Database Modification - Insert • Add a new tuple to course: insert into coursevalues(‘CS-437’, ‘Database Systems’, ‘Comp. Sci’, 4)or equivalentlyinsert into course(course_id, title, credits, dept_name)values(‘CS-437’, ‘Database Systems’, 4, ‘Comp. Sci’) • Add all instructors to the student relation with tot_creds set to 0:insert into studentselect ID, name, dept_name, 0from instructor

  13. Database Modification - Update • Increase salaries of instructors whose salary is over $100,000 by 3% and all others receive 5% raise: update instructorset salary = salary  1.03where salary > 100000; update instructorset salary = salary  1.05where salary  100000 • The order is important • Can be done better using the case statementupdateinstructorsetsalary =casewhensalary  100000 thensalary *1.05elsesalary * 1.03end

More Related