What is SQL Query language for structural databases

What is SQL? • Query language for structural databases (esp. RDB) Structured Query Language • Originated from Sequel 2 by Chamberlin et al (1976) at IBM’s San Jose Research Lab. • ANSI (American National Standards Institute) published an SQL standard in 1986 1/18/00 CSE 711 data mining

Role of SQL? • Data Definition Language (DDL) • Interactive data manipulation language (DML) • Embeded data manipulation language • View definition • Integrity • Transaction Control 1/18/00 CSE 711 data mining

Information Retrieval of SQL? select A 1, A 2, . . . , An from r 1, r 2, . . . , rm where p • The select clause is used to list the attributes desired in the result of a query (specifies the columns) • The from clause lists the relations to be scanned in the evaluation of the expression (specifies the tables) • The where clause consists of a predicate involving attributes of the relations that appear in the from clause. (specifies the rows) 1/18/00 CSE 711 data mining

Column Manipulation of SQL? • Choosing Columns SELECT fname, salary FROM employee • Rearranging the Order of Columns SELECT salary, fname FROM employee • Insert Literals SELECT fname, ‘Salary: ’, salary FROM employee • Changing Column Headings SELECT First_name = fname, salary FROM employee 1/18/00 CSE 711 data mining

Manipulating Data of SQL? • Manipulating Numerical Data (Arithmetic op. , Math. Func. ) SELECT fname, (salary * 1. 1) FROM employee • Manipulating Character Data (String func. ) SELECT SUBSTRING(fname, 1, 1), salary FROM employee • Manipulating Datetime Data SELECT fname, birthdate, DATEDIFF(year, birthdate, getdate()) FROM employee • System Functions SELECT ‘database’ = db_name(), ‘user’ = user_name(), login = suser_name() 1/18/00 CSE 711 data mining

Choosing Rows of SQL? • Based on Comparisons SELECT fname, (salary * 1. 1) FROM employee WHERE state = ‘NY’ • Based on Ranges SELECT fname, (salary * 1. 1) FROM employee WHERE birthdate BETWEEN ‘ 1/1/1950’ and ‘ 12/31/1970’ • Based on Lists SELECT fname, salary FROM employee WHERE major IN (‘CS’, ‘CSE’, ‘ECE’, ‘IS’) 1/18/00 CSE 711 data mining

More on Choosing Rows of SQL? • Eliminating Duplicates • Based on Character Strings SELECT DISTINCT city FROM authors SELECT fname, lname FROM professor WHERE lname LIKE ‘%hari%’ • Based on Unknown values SELECT fname FROM employee WHERE salary IS NULL • Sorting SELECT fname, lname, salary FROM employee ORDER BY salary DESC • Based on Several Search Arguments SELECT fname, salary FROM employee WHERE (major LIKE ‘CS%’, OR state =‘NY’) AND (salary > 25, 000) 1/18/00 CSE 711 data mining

Aggregate Functions of SQL? • Generate Summary Values avg, count, max, min, sum, etc. (New “Group by” and “Having” clauses) SELECT title_id, copies_sold = SUM(qty) FROM sales GROUP BY title_id HAVING SUM(qty) > 30 1/18/00 CSE 711 data mining

etc. about SQL? • Joining Tables (Inner Joins, Cross Joins, Outer Joins, Joins with More than Two Tables, Self Joins) • Nesting SELECT Statements • Subqueries • Select Into • UNION Operator 1/18/00 CSE 711 data mining