Lecture 8 Be able to use manipulation and querying tools Data manipulation: query languages; visual tools;typical tasks eg for database maintenance, inserts,updates and amendments Queries and reporting: query languages and query byexample (QBE); formatting; functions/formulae; reportwriting tools3Part 1 – Objectives Purpose and importance of SQL. How to retrieve data from database usingSELECT and:– Use compound … Continue reading “use manipulation and querying tools | My Assignment Tutor”
Lecture 8 Be able to use manipulation and querying tools Data manipulation: query languages; visual tools;typical tasks eg for database maintenance, inserts,updates and amendments Queries and reporting: query languages and query byexample (QBE); formatting; functions/formulae; reportwriting tools3Part 1 – Objectives Purpose and importance of SQL. How to retrieve data from database usingSELECT and:– Use compound WHERE conditions.– Sort query results using ORDER BY.– Use aggregate functions.– Group data using GROUP BY and HAVING.– Use subqueries.Part 1 – Objectives– Join tables together.– Perform set operations (UNION, INTERSECT,EXCEPT). How to update database using INSERT,UPDATE, and DELETE.5Objectives of SQL Ideally, database language should allow user to:– create the database and relation structures;– perform insertion, modification, deletion ofdata from relations;– perform simple and complex queries. Must perform these tasks with minimal usereffort and command structure/syntax must beeasy to learn. It must be portable.6Objectives of SQL SQL is a transform-oriented language with 2major components:– A DDL for defining database structure.– A DML for retrieving and updating data. Until SQL:1999, SQL did not contain flow ofcontrol commands. These had to be implementedusing a programming or job-control language, orinteractively by the decisions of user.7Objectives of SQL SQL is relatively easy to learn:– it is non-procedural – you specify whatinformation you require, rather than how to getit;– it is essentially free-format.Objectives of SQL Consists of standard English words:1) CREATE TABLE Staff(staffNo VARCHAR(5),lName VARCHAR(15),salary DECIMAL(7,2));2) INSERT INTO Staff VALUES (‘SG16’, ‘Brown’,8300);3) SELECT staffNo, lName, salaryFROM StaffWHERE salary > 10000;9Objectives of SQL Can be used by range of users including DBAs,management, application developers, and othertypes of end users. An ISO standard now exists for SQL, making itboth the formal and de facto standard languagefor relational databases.Pearson Education © 200910History of SQL In 1974, D. Chamberlin (IBM San JoseLaboratory) defined language called ‘StructuredEnglish Query Language’ (SEQUEL). A revised version, SEQUEL/2, was defined in1976 but name was subsequently changed to SQLfor legal reasons.Pearson Education © 200911History of SQL Still pronounced ‘see-quel’, though officialpronunciation is ‘S-Q-L’. IBM subsequently produced a prototype DBMScalled System R, based on SEQUEL/2. Roots of SQL, however, are in SQUARE(Specifying Queries as Relational Expressions),which predates System R project.Pearson Education © 200912History of SQL In late 70s, ORACLE appeared and was probably firstcommercial RDBMS based on SQL. In 1987, ANSI and ISO published an initial standard forSQL. In 1989, ISO published an addendum that defined an‘Integrity Enhancement Feature’. In 1992, first major revision to ISO standard occurred,referred to as SQL2 or SQL/92. In 1999, SQL:1999 was released with support for objectoriented data management. In late 2003, SQL:2003 was released.Pearson Education © 200913Importance of SQL SQL has become part of application architecturessuch as IBM’s Systems Application Architecture. It is strategic choice of many large and influentialorganizations (e.g. X/OPEN). SQL is Federal Information Processing Standard(FIPS) to which conformance is required for allsales of databases to American Government.Pearson Education © 200914Importance of SQL SQL is used in other standards and eveninfluences development of other standards as adefinitional tool. Examples include:– ISO’s Information Resource Directory System(IRDS) Standard– Remote Data Access (RDA) Standard.Pearson Education © 200915Writing SQL Commands SQL statement consists of reserved words and userdefined words.– Reserved words are a fixed part of SQL and mustbe spelt exactly as required and cannot be splitacross lines.– User-defined words are made up by user andrepresent names of various database objects suchas relations, columns, views.Pearson Education © 200916Writing SQL Commands Most components of an SQL statement are caseinsensitive, except for literal character data. More readable with indentation and lineation:– Each clause should begin on a new line.– Start of a clause should line up with start ofother clauses.– If clause has several parts, should each appearon a separate line and be indented under startof clause.Pearson Education © 200917Writing SQL Commands Use extended form of BNF notation:– 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).Pearson Education © 200918Literals Literals are constants used in SQL statements. All non-numeric literals must be enclosed insingle quotes (e.g. ‘London’). All numeric literals must not be enclosed inquotes (e.g. 650.00).Pearson Education © 200919SELECT StatementSELECT [DISTINCT | ALL]{* | [columnExpression [AS newName]] [,…] } FROM[WHERE[GROUP BYTableName [alias] [, …]condition]columnList] [HAVING condition] [ORDER BY columnList]Pearson Education © 200920SELECT StatementFROM Specifies table(s) to be used.WHERE Filters rows.GROUP BY Forms groups of rows with samecolumn value.HAVING Filters groups subject to somecondition.SELECT Specifies which columns are toappear in output.ORDER BY Specifies the order of the output.Pearson Education © 200921SELECT Statement Order of the clauses cannot be changed. Only SELECT and FROM are mandatory.Pearson Education © 200922Example 6.1 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;Pearson Education © 200923Example 6.1 All Columns, All RowsPearson Education © 200924Example 6.2 Specific Columns, All RowsProduce a list of salaries for all staff, showing onlystaff number, first and last names, and salary.SELECT staffNo, fName, lName, salaryFROM Staff;Pearson Education © 200925Example 6.2 Specific Columns, All RowsPearson Education © 200926Example 6.3 Use of DISTINCTList the property numbers of all properties thathave been viewed.SELECT propertyNoFROM Viewing;Pearson Education © 200927Example 6.3 Use of DISTINCT Use DISTINCT to eliminate duplicates:SELECT DISTINCT propertyNoFROM Viewing;Pearson Education © 200928Example 6.4 Calculated FieldsProduce list of monthly salaries for all staff,showing staff number, first/last name, and salary.SELECT staffNo, fName, lName, salary/12FROM Staff;Pearson Education © 200929Example 6.4 Calculated Fields To name column, use AS clause:SELECT staffNo, fName, lName, salary/12AS monthlySalaryFROM Staff;Pearson Education © 200930Example 6.5 Comparison Search ConditionList all staff with a salary greater than 10,000.SELECT staffNo, fName, lName, position, salaryFROM StaffWHERE salary > 10000;Pearson Education © 200931Example 6.6 Compound Comparison Search ConditionList addresses of all branch offices in London orGlasgow.SELECT *FROM BranchWHERE city = ‘London’ OR city = ‘Glasgow’;Pearson Education © 200932Example 6.7 Range Search ConditionList all staff with a salary between 20,000 and30,000.SELECT staffNo, fName, lName, position, salaryFROM StaffWHERE salary BETWEEN 20000 AND 30000; BETWEEN test includes the endpoints of range.Pearson Education © 200933Example 6.7 Range Search ConditionPearson Education © 200934Example 6.7 Range Search Condition Also a negated version NOT BETWEEN. BETWEEN does not add much to SQL’sexpressive power. Could also write:SELECT staffNo, fName, lName, position, salaryFROM StaffWHERE salary>=20000 AND salary 350;Pearson Education © 200952Example 6.14 Use of COUNT(DISTINCT)How many different properties viewed in May ‘04?SELECT COUNT(DISTINCT propertyNo) AS myCountFROM ViewingWHERE viewDate BETWEEN ‘1-May-04’AND ‘31-May-04’;Pearson Education © 200953Example 6.15 Use of COUNT and SUMFind number of Managers and sum of theirsalaries.SELECT COUNT(staffNo) AS myCount,SUM(salary) AS mySumFROM StaffWHERE position = ‘Manager’;Pearson Education © 200954Example 6.16 Use of MIN, MAX, AVGFind minimum, maximum, and average staffsalary.SELECT MIN(salary) AS myMin,MAX(salary) AS myMax,AVG(salary) AS myAvgFROM Staff;Pearson Education © 200955SELECT Statement – Grouping Use GROUP BY clause to get sub-totals. SELECT and GROUP BY closely integrated:each item in SELECT list must be single-valuedper group, and SELECT clause may only contain:– column names– aggregate functions– constants– expression involving combinations of the above.Pearson Education © 200956SELECT Statement – Grouping All column names in SELECT list must appear inGROUP BY clause unless name is used only in anaggregate function. If WHERE is used with GROUP BY, WHERE isapplied first, then groups are formed fromremaining rows satisfying predicate. ISO considers two nulls to be equal for purposesof GROUP BY.Pearson Education © 200957Example 6.17 Use of GROUP BYFind number of staff in each branch and theirtotal salaries. SELECTbranchNo,COUNT(staffNo) AS myCount, SUM(salary) AS mySumFROM StaffGROUP BY branchNoORDER BY branchNo;Pearson Education © 200958Example 6.17 Use of GROUP BYPearson Education © 200959Restricted Groupings – HAVING clause HAVING clause is designed for use with GROUPBY to restrict groups that appear in final resulttable. Similar to WHERE, but WHERE filtersindividual rows whereas HAVING filters groups. Column names in HAVING clause must alsoappear in the GROUP BY list or be containedwithin an aggregate function.Pearson Education © 200960Example 6.18 Use of HAVINGFor each branch with more than 1 member ofstaff, find number of staff in each branch andsum of their salaries.SELECT branchNo,COUNT(staffNo) AS myCount,SUM(salary) AS mySumFROM StaffGROUP BY branchNoHAVING COUNT(staffNo) > 1ORDER BY branchNo;Pearson Education © 200961Example 6.18 Use of HAVINGPearson Education © 200962Subqueries Some SQL statements can have a SELECTembedded within them. A subselect can be used in WHERE andHAVING clauses of an outer SELECT, where itis called a subquery or nested query. Subselects may also appear in INSERT,UPDATE, and DELETE statements.Pearson Education © 200963Example 6.19 Subquery with EqualityList staff who work in branch at ‘163 Main St’.SELECT staffNo, fName, lName, positionFROM StaffWHERE branchNo =(SELECT branchNoFROM BranchWHERE street = ‘163 Main St’);Pearson Education © 200964Example 6.19 Subquery with Equality Inner SELECT finds branch number for branchat ‘163 Main St’ (‘B003’). Outer SELECT then retrieves details of all staffwho work at this branch. Outer SELECT then becomes:SELECT staffNo, fName, lName, positionFROM StaffWHERE branchNo = ‘B003’;Pearson Education © 200965Example 6.19 Subquery with EqualityPearson Education © 200966Example 6.20 Subquery with AggregateList all staff whose salary is greater than the averagesalary, and show by how much.SELECT staffNo, fName, lName, position,salary – (SELECT AVG(salary) FROM Staff) As SalDiffFROM StaffWHERE salary >(SELECT AVG(salary)FROM Staff);Pearson Education © 200967Example 6.20 Subquery with Aggregate Cannot write ‘WHERE salary > AVG(salary)’ Instead, use subquery to find average salary(17000), and then use outer SELECT to find thosestaff with salary greater than this:SELECT staffNo, fName, lName, position,salary – 17000 As salDiffFROM StaffWHERE salary > 17000;Pearson Education © 200968Example 6.20 Subquery with AggregatePearson Education © 200969Subquery Rules ORDER BY clause may not be used in asubquery (although it may be used in outermostSELECT). Subquery SELECT list must consist of a singlecolumn name or expression, except forsubqueries that use EXISTS. By default, column names refer to table name inFROM clause of subquery. Can refer to a tablein FROM using an alias.Pearson Education © 2009Lab SQL Server
