08-24-09SQL = DDL, DML and DCLHistory of SQLSQL StandardBNF Notation for SQLSELECT all columns, all rowsSelect specific columns, all rowsDistinct clauseComparison SearchesCalculated Fields & as clauseSet MembershipJoinUnary Relationship in SQL (Join with 1 Table)Left Outer JoinUnary Relationship (cont.)Pattern MatchingNullORDER BYAggregate FunctionsGroup ByHavingGroup by with HavingSubquery with equalitySQL MisconceptionsSlide 25Slide 26Homework # 2Hw. 2 - Part IIIEnd of LectureCs8630, Dr. Guimaraes08-24-09 ClassWill Start Momentarily…CS8630 Database AdministrationDr. Mario GuimaraesSQL – DML (SELECT)Cs8630, Dr. GuimaraesSQL = DDL, DML and DCLCs8630, Dr. GuimaraesHistory of SQL•In 1974, Chamberlin (IBM San Jose Laboratory) defined language called ‘Structured English Query Language’ (SEQUEL).•A revised version, SEQUEL/2, in 1976 and changed to SQL for legal reasons.•Still pronounced ‘see-quel’, though official pronunciation is ‘S-Q-L’. •IBM subsequently produced a prototype DBMS called System R, based on SEQUEL/2. •In late 70s, ORACLE and INGRES (UC at Berkeley) were first RDBMS based on SQL.•In 1987, ANSI and ISO published an initial standard for SQL.•In 1992, first major revision to ISO standard occurred, referred to as SQL2 or SQL/92.•In 1999, SQL3 was released with support for object-oriented data management.Cs8630, Dr. GuimaraesSQL Standard•SQL statement consists of reserved words and user-defined words.–Reserved words are a fixed part of SQL and must be spelt exactly as required and cannot be split across lines. –User-defined words are made up by user and represent names of various database objects such as relations, columns, views.•Most components of an SQL statement are case insensitive, except for literal character data.Cs8630, Dr. GuimaraesBNF Notation for SQL- Upper-case letters represent reserved words.- Lower-case letters represent user-defined words.- | indicates a choice among alternatives.- Curly braces indicate a required element.- Square brackets indicate an optional element.- … indicates optional repetition (0 or more).SELECT [DISTINCT | ALL] {* | [columnExpression [AS newName]] [,...] }FROM TableName [alias] [, ...][WHERE condition][GROUP BY columnList] [HAVINGcondition][ORDER BY columnList]Cs8630, Dr. GuimaraesSELECT all columns, all rowsList full details of all staff.SELECT staffNo, fName, lName, address, position, sex, DOB, salary, branchNoFROM Staff;•Can use * as an abbreviation for ‘all columns’:SELECT *FROM Staff;Cs8630, Dr. GuimaraesSelect specific columns, all rowsProduce a list of salaries for all staff, showing only staff number, first and last names, and salary. What relational algebra operation is this ?SELECT staffNo, fName, lName, salaryFROM Staff;Cs8630, Dr. GuimaraesDistinct clauseList the property numbers of all properties that have been viewed.SELECT propertyNoFROM Viewing;SELECT DISTINCT propertyNoFROM Viewing;Cs8630, Dr. GuimaraesComparison SearchesList addresses of all branch offices in London or Glasgow.SELECT *FROM BranchWHERE city = ‘London’ OR city = ‘Glasgow’;1) What Relational Algebra Operation are we doing ?2) Does it matter the order of the Where clause ?Cs8630, Dr. GuimaraesCalculated Fields & as clauseProduce a list of monthly salaries for all staff, showing staff number, first and last names, and salary details.SELECT staffNo, fName, lName, salary/12FROM Staff;SELECT staffNo, fName, lName, salary/12 AS monthlySalaryFROM Staff;Cs8630, Dr. GuimaraesSet MembershipList all managers and supervisors.SELECT staffNo, fName, lName, positionFROM StaffWHERE position IN (‘Manager’, ‘Supervisor’); =SELECT staffNo, fName, lName, position FROM Staff WHERE position=‘Manager’ OR position=‘Supervisor’;How would the answer change if we replace the IN with a NOT IN ?Cs8630, Dr. GuimaraesJoinList names of all clients who have viewed a property along with any comment supplied. SELECT c.clientNo, fName, lName, propertyNo, commentFROM Client c, Viewing vWHERE c.clientNo = v.clientNo;•Only those rows from both tables that have identical values in the clientNo columns (c.clientNo = v.clientNo) are included in result.Cs8630, Dr. GuimaraesUnary Relationship in SQL(Join with 1 Table)Empno Ename Job Mgr7369 Smith Clerk 79027499 Allen Salesman76987521 Ward Salesman76987902 Ford Analyst 75667698 Blake Manager 7839…Empno Ename Job Mgr7369 Smith Clerk 79027499 Allen Salesman76987521 Ward Salesman76987902 Ford Analyst 75667698 Blake Manager7839…EMP e EMP mSelect e.ename, m.enameFrom emp e, emp mWhere e.mgr = m.empnoList name of employees and their managersCs8630, Dr. GuimaraesLeft Outer JoinList branches and properties that are in same city along with any unmatched branches. SELECT b.*, p.*FROM Branch1 b LEFT JOIN PropertyForRent1 p ON b.bCity = p.pCity;SELECT b.*, p.*FROM Branch1 b, PropertyForRent1 p WHERE b.bCity (+) = p.pCity;Cs8630, Dr. GuimaraesUnary Relationship (cont.)Empno Ename Job Mgr7369 Smith Clerk 79027499 Allen Salesman76987521 Ward Salesman76987902 Ford Analyst 75667698 Blake Manager 7839…Empno Ename Job Mgr7369 Smith Clerk 79027499 Allen Salesman76987521 Ward Salesman76987902 Ford Analyst 75667698 Blake Manager 7839…EMP eEMP mSelect e.ename, m.enameFrom emp e, emp mWhere e.mgr = m.empnoEname Mgr NameSmith FordAllen BlakeWard Blake…INPUTINPUToutputList name of employees and their managersCs8630, Dr. GuimaraesPattern MatchingFind all owners with the string ‘Glasgow’ in their address. SELECT clientNo, fName, lName, address, telNoFROM PrivateOwnerWHERE address LIKE ‘%Glasgow%’;Cs8630, Dr. GuimaraesNullList details of all viewings on property PG4 where a comment has not been supplied.•There are 2 viewings for property PG4, one with and one without a comment. •Have to test for null explicitly using special keyword IS NULL:SELECT clientNo, viewDateFROM ViewingWHERE propertyNo = ‘PG4’ AND comment IS NULL;May also use IS NOT NULLWarning:Never use = NULL or NOT = NULL !!Cs8630, Dr. GuimaraesORDER BYList salaries for all staff, arranged in descending order of salary.SELECT staffNo, fName, lName, salaryFROM StaffORDER BY salary DESC;Cs8630, Dr. GuimaraesAggregate Functions•ISO standard defines five aggregate functions:COUNT returns number of