Last Lecture Enrolment • Find a list of names of sID mCode SQL SELECT III any students who are 1 G52ADS enrolled on at least one 2 G52ADS module alongside 5 G51DBS ‘Evans’ 5 G51PRG Database Systems 5 G51IAI Michael Pound 4 G52ADS 6 G51PRG 6 G51IAI Last Lecture Last Lecture SELECT * FROM Enrolment E1, Enrolment E2 SELECT * FROM WHERE E1.mCode = E2.mCode; Enrolment E1 INNER JOIN Enrolment E2 USING (mCode) WHERE E2.sID = (SELECT sID FROM Student sID mCode sID mCode WHERE sName = ‘Evans’); 1 G52ADS 1 G52ADS 2 G52ADS 1 G52ADS 5 G51DBS 1 G52ADS 5 G51PRG 1 G52ADS sID mCode sID 5 G51IAI 1 G52ADS 1 G52ADS 4 4 G52ADS 1 G52ADS 2 G52ADS 4 6 G51PRG 1 G52ADS 4 G52ADS 4 6 G51IAI 1 G52ADS 1 G52ADS 2 G52ADS 2 G52ADS 2 G52ADS Last Lecture This Lecture • More SQL SELECT SELECT sID, sName FROM Student • ORDER BY WHERE sID IN • Aggregate functions (SELECT DISTINCT E1.sID FROM Enrolment E1 INNER JOIN Enrolment E2 • GROUP BY and HAVING USING (mCode) • UNION WHERE E2.sID = (SELECT sID FROM Student • Further reading WHERE sName = ‘Evans’)) • The Manga Guide to Databases, Chapter 4 AND sID <> (SELECT sID FROM Student WHERE sName = ‘Evans’); • Database Systems, Chapter 6 1
SQL SELECT Overview ORDER BY SELECT • The ORDER BY clause SELECT <columns> FROM <tables> sorts the results of a [DISTINCT | ALL] <column-list> WHERE <condition> query FROM <table-names> ORDER BY <cols> • You can sort in ascending [WHERE <condition>] [ASC | DESC] (default) or descending [GROUP BY <column-list>] order • Multiple columns can be [HAVING <condition>] given [ORDER BY <column-list>] • You cannot order by a column which isn’t in the ([] optional , | or ) result ORDER BY ORDER BY SELECT * FROM Grades SELECT * FROM Grades ORDER BY Mark ORDER BY Code ASC, Grades Grades Mark DESC Name Code Mark Name Code Mark John DBS 56 John DBS 56 Name Code Mark Name Code Mark John IAI 72 John IAI 72 James PR2 35 Mary DBS 60 Mary DBS 60 Mary DBS 60 James PR1 43 John DBS 56 James PR1 43 James PR1 43 Jane IAI 54 John IAI 72 James PR2 35 James PR2 35 John DBS 56 Jane IAI 54 Jane IAI 54 Jane IAI 54 Mary DBS 60 James PR1 43 John IAI 72 James PR2 35 Constants and Arithmetic Aggregate Functions • As well as columns, a SELECT Mark / 100 • Aggregate functions • Aggregate functions FROM Grades SELECT statement can compute summaries of • COUNT : The number of also be used to data in a table rows SELECT Salary + Bonus • SUM : The sum of the • Select constants • Most aggregate FROM Employee entries in the column functions (except • Compute arithmetic COUNT (*)) work on a • AVG : The average entry expressions SELECT 1.175 * Price single column of in a column AS ‘Price inc. VAT’ • Evaluate functions numerical data • MIN , MAX : The FROM Products • Often helpful to use an • Again, it’s best to use an minimum and maximum alias when dealing with SELECT ‘Constant’ AS Text entries in a column alias to name the result expressions or functions FROM <table> 2
COUNT SUM, MIN/MAX and AVG SELECT SELECT Count Total COUNT(*) AS Count SUM(Mark) AS Total Grades Grades 6 320 FROM Grades FROM Grades Name Code Mark Name Code Mark John DBS 56 John DBS 56 SELECT SELECT John IAI 72 COUNT(Code) John IAI 72 Count Best MAX(Mark) AS Best AS Count Mary DBS 60 Mary DBS 60 6 72 FROM Grades FROM Grades James PR1 43 James PR1 43 James PR2 35 James PR2 35 SELECT Jane IAI 54 Jane IAI 54 SELECT COUNT(DISTINCT Code) Count Mean AVG(Mark) AS Mean AS Count 4 53.33 FROM Grades FROM Grades Aggregate Functions Example Modules • You can combine SELECT • Find John’s average Code Title Credits MAX(Mark) – MIN(Mark) aggregate functions mark, weighted by the DBS Database Systems 10 AS Range using arithmetic credits of each module GRP Group Project 20 FROM Grades PRG Programming 10 Grades SELECT Name Code Mark SUM(Mark*Credits) Grades John DBS 56 / SUM (Credits) MAX(Mark) = 72 Name Code Mark John IAI 72 AS ‘Final Mark’ Range John DBS 56 FROM Modules, Grades Mary DBS 60 37 WHERE Modules.Code=Grades.Code John IAI 72 MIN(Mark) = 35 James PR1 43 AND Grades.Name = ‘John’ Mary DBS 60 James PR2 35 Jane IAI 54 GROUP BY GROUP BY • Sometimes we want to • The GROUP BY clause • Every entry in <cols1> apply aggregate achieves this should be in <cols2>, be functions to groups of a constant, or be an SELECT <cols1> rows aggregate function SELECT <cols1> FROM <tables> • You can have WHERE • Example, find the FROM <tables> GROUP BY <cols2> and ORDER BY average mark of each GROUP BY <cols2> student individually clauses as well as a GROUP BY clause 3
GROUP BY GROUP BY SELECT Name, • Find the total value of Sales Grades AVG(Mark) AS Average the sales for each Month Department Value Name Code Mark FROM Grades department in each March Fiction 20 John DBS 56 GROUP BY Name March Travel 30 month John IAI 72 March Technical 40 • Can group by Month Mary DBS 60 April Fiction 10 Name Average then Department or James PR1 43 April Fiction 30 John 64 Department then Month James PR2 35 April Travel 25 Mary 60 • Same results, but Jane IAI 54 April Fiction 20 James 39 produced in a different May Fiction 20 Jane 54 order May Travel 50 GROUP BY GROUP BY Rules • GROUP BY works • For example: SELECT Month, Department, SELECT Month, Department, SUM (Value) AS Total SUM (Value) AS Total slightly differently in FROM Sales FROM Sales MySQL than in other SELECT ID, Name, GROUP BY Month, Department GROUP BY Department, Month DBMSs. AVG(Mark) Month Department Total Month Department Total • Usually, every column FROM Students April Fiction 60 April Fiction 60 you name in your April Travel 25 March Fiction 20 GROUP BY SELECT statement, must March Fiction 20 May Fiction 20 also appear in your ID, Name March Technical 40 March Technical 40 GROUP BY clause. Apart March Travel 30 May Technical 50 from those in Aggregate May Fiction 20 April Travel 25 functions. May Technical 50 March Travel 30 GROUP BY Rules GROUP BY Rules • The MySQL extension means you do not need • In MySQL, for • Despite this, you should convenience, you are follow the ISO standard to GROUP BY every column you’re SELECTing. allowed to break this where possible It also means you don’t have to SELECT a rule. • Avoids problems if you column even if it’s in your GROUP BY clause: use a different DBMS in • You are allowed to the future GROUP BY a column SELECT artID, artName, • Can lead to peculiar that won’t appear in AVG(cdPrice) output where multiple the output table values get output as one FROM Artist NATURAL JOIN CD GROUP BY artID; 4
GROUP BY Rules GROUP BY Rules • What’s the best way? Instead of: • Be careful though, relaxed rules means you might SELECT artName, AVG(cdPrice) get peculiar output if you’re not careful: FROM Artist NATURAL JOIN CD GROUP BY artID SELECT cdTitle, AVG(cdPRICE) Try: FROM Artist NATURAL JOIN CD SELECT artName, Average GROUP BY artID; FROM (SELECT artID, artName, AVG(cdPrice) AS Average cdTitle AVG(cdPrice) FROM Artist NATURAL JOIN CD For Lack of a Better Name 11.49 GROUP BY artID, artName) AS SubTable; Version 9.99 The Resistance 10.99 HAVING WHERE and HAVING • HAVING is like a WHERE SELECT Name, • Think of a query being • WHERE refers to the AVG(Mark) AS Average clause, except that it rows of tables, so processed as follows: FROM Grades only applies to the cannot make use of • Tables are joined GROUP BY Name results of a GROUP BY aggregate functions • WHERE clauses HAVING AVG(Mark) >= 40 query • HAVING refers to the • GROUP BY clauses and Name Average • It can be used to select aggregates groups of rows, and so John 64 • Column selection groups which satisfy a cannot use columns Mary 60 • HAVING clauses given condition which are not in the Jane 54 • ORDER BY GROUP BY or an aggregate function UNION UNION • UNION, INTERSECT and • They all combine the • Find, in a single query, Grades EXCEPT results from two select the average mark for Name Code Mark statements each student and the • These treat the tables as Jane IAI 52 sets and are the usual average mark overall • The results of the two John DBS 56 set operators of union, selects should have the John IAI 72 intersection and same columns and data James PR1 43 difference James PR2 35 types • We’ll be concentrating Mary DBS 60 on UNION 5
Recommend
More recommend
