Lecture 8 Learning Objectives On completion of this topic, you will be able to:– Explain– Distinguish– Identify Data Manipulation in SQLBe 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 tools4Part … Continue reading “Purpose and importance of SQL | My Assignment Tutor”
Lecture 8 Learning Objectives On completion of this topic, you will be able to:– Explain– Distinguish– Identify Data Manipulation in SQLBe 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 tools4Part 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.6Objectives 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.7Objectives 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.8Objectives 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;10Objectives 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 © 200911History 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 © 200912History 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 © 200913History 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 © 200914Importance 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 © 200915Importance 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 © 200916Writing 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 © 200917Writing 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 © 2009Writing SQL Commands Adding table rows Saving table changes Listing table rows Updating table rows Restoring table contents Deleting table rowsData Manipulation Select: query data in the database Insert: insert data into a table Update: updates data in a table Delete: delete data from a tableSimple Query Select specifies which columns are to appear in the output From specifies the table(s) to be used Where filters the rows subject to some condition(2)Simple Query Group By forms groups of rows with the same column value Having filters the groups subject to some condition Order By specifies the order of the outputRetrieve all Columns and all RowsSELECT firstColumn, …,lastColumnFROM tableName;SELECT *FROM tableName;Use of DistinctSELECT DISTINCT columnNameFROM tableNameComparison Search Condition= equals is not equal to (ISO standard)!= “ “ “ “ (allowed in some dialects) is greater than= is greater than or equal toComparison Search Condition An expression is evaluated left to right Subexpressions in brackets are evaluated first NOTs are evaluated before ANDs and Ors ANDs are evaluated before ORsRange Search ConditionSELECT columnNameFROM tableNameWHERE columnName BETWEEN 20AND 30;SELECT columnNameFROM tableNameWHERE columnName >=20AND columName 10000;Pearson Education © 200950Example 6.6 Compound Comparison Search ConditionList addresses of all branch offices in London orGlasgow.SELECT *FROM BranchWHERE city = ‘London’ OR city = ‘Glasgow’;Pearson Education © 200951Example 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 © 200952Example 6.7 Range Search ConditionPearson Education © 200953Example 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 © 200971Example 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 © 200972Example 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 © 200973Example 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 © 200974SELECT 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 © 200975SELECT 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 © 200976Example 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 © 200977Example 6.17 Use of GROUP BYPearson Education © 200978Restricted 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 © 200979Example 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 © 200980Example 6.18 Use of HAVINGPearson Education © 200981Subqueries 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 © 200982Example 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 © 200983Example 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 © 200984Example 6.19 Subquery with EqualityPearson Education © 200985Example 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 © 200986Example 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 © 200987Example 6.20 Subquery with AggregatePearson Education © 200988Subquery 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
