Vasiliev - Mastering SQL: Queries - Take It Easy
Here you can read online Vasiliev - Mastering SQL: Queries - Take It Easy full text of the book (entire story) in english for free. Download pdf and epub, get meaning, cover and reviews about this ebook. year: 2016, genre: Business. Description of the work, (preface) as well as reviews are available. Best literature library LitArk.com created for fans of good reading and offers a wide selection of genres:
Romance novel
Science fiction
Adventure
Detective
Science
History
Home and family
Prose
Art
Politics
Computer
Non-fiction
Religion
Business
Children
Humor
Choose a favorite category and find really read worthwhile books. Enjoy immersion in the world of imagination, feel the emotions of the characters or learn something new for yourself, make an fascinating discovery.
Mastering SQL: Queries - Take It Easy: summary, description and annotation
We offer to read an annotation, description, summary or preface (depends on what the author of the book "Mastering SQL: Queries - Take It Easy" wrote himself). If you haven't found the necessary information about the book — write in the comments, we will try to find it.
Vasiliev: author's other books
Who wrote Mastering SQL: Queries - Take It Easy? Find out the surname, the name of the author of the book and a list of all author's works by series.
Mastering SQL: Queries - Take It Easy — read online for free the complete book (whole text) full work
Below is the text of the book, divided by pages. System saving the place of the last page read, allows you to conveniently read the book "Mastering SQL: Queries - Take It Easy" online for free, without having to search again every time where you left off. Put a bookmark, and you can go to the page where you finished reading at any time.
Font size:
Interval:
Bookmark:
Mastering SQL: Queries Take It Easy By YuliVasiliev
Mastering SQL: Queries Take It Easy By Yuli Vasiliev Trademarked names appear in this book.Where the author was aware of those trademarks, they have been written in initialcaps, in the same way they appear in the vendor documentation. The information included to this work isbelieved to be accurate and dependable. However, due to the fact that things inthe IT world change quickly as well as the possibility of human errors, theauthor cannot guarantee the completeness or accuracy of the informationprovided in this work, and will not be responsible for possible errors,omissions, or damages caused directly or indirectly by the use of theinformation contained herein.
Table of Contents
This text provides some interestingexamples of using SQL when it comes to querying database data. The level ofcomplexity implies youre not a total beginner to SQL and have at least a basicunderstanding of how SQL queries are built. To understand and follow thesamples though, you are not supposed to be an advanced SQL user either eachexample comes with a detailed explanation of how it works, illustrating thateven complex data management tasks can be taken easy with the proper use of SQLcapabilities.
The sample queries provided in this text areimplemented with Oracle SQL and MySQL SQL dialects, meaning youll need to haveaccess either to Oracle Database or to MySQL to follow them. With someadjustments, the samples can be used in other systems, of course. In otherwords, the sample queries youll see here are not specific to a certaindatabase platform, focusing on some interesting data management tasks, ratherthan system specific ones.
In the following example, you build a non-equi-joinover a table whose rows contain information about three football teams. Thestructure of the team table used in this example is very straightforward andconsists just of two columns: team_no and team_name. You are supposed toprepare a schedule for a series of games so that each team meets the othersonce. As you might guess, using an equi-join is not an option here, because youwould end up with each team scheduled to play against itself just like in thefollowing example, assuming you have the team table already created and filledwith the data: SELECT t1.team_name team1, t2.team_name team2 FROM team t1, team t2 WHERE t1.team_no = t2.team_no TEAM1 TEAM2 ---------------------------------- IceMen IceMen Slavia Slavia Amazzo Amazzo Of course, thats not what you want to see. Incontrast, using an inequality comparison brings the desired results. All youneed to do is to replace t1.team_no = t2.team_nowith t1.team_no < t2.team_no in theabove query to get the results you need, which might look like this: TEAM1 TEAM2 ---------------------------------- IceMen Slavia IceMen Amazzo Slavia Amazzo Although, this time you have the right combination,thats not it, though.
Suppose you also have to set a date for each game. Beingan Oracle database user, you can do that with the SYSDATE function along withthe ROWNUM function. So, if you need to establish the first match in a week andthen each next day, then the query might look like this: SELECT t1.team_name team1, t2.team_name team2,SYSDATE + ROWNUM + 6 GAME_DATE FROM team t1, team t2 WHERE t1.team_no < t2.team_no Producing the following output: TEAM1 TEAM2 GAME_DATE ------------------------------------------------------------ IceMen Slavia 17-AUG-15 IceMen Amazzo 18-AUG-15 Slavia Amazzo 19-AUG-15 If you are using MySQL, the query might look likethis to produce the same output: SELECT t1.team_name team1, t2.team_name team2,INTERVAL (@rownum:=@rownum+1) + 6 DAY + CURDATE() GAME_DATE FROM team t1, team t2, (SELECT @rownum:=0) r WHERE t1.team_no < t2.team_no;
Consider the following example. Suppose you want togenerate an order total report for the following date diapason: 01-jan-15 to15-jan-15, which would include a row for each day within the specifieddiapason, whether there were orders on a given day or not. In other words, youneed to build a query that will output a row for each day of those fifteen inthe diapason. The only guaranteed way to achieve this is with the help of ajoin connecting the selection from the orders table to the desired date rangeset, which might be either generated on the fly or selected from a table. Whenusing Oracle Database, you can choose between these two options. In MySQL, youhave only the latter option.
Lets start with an example for Oracle Database. Tokeep things simple, first lets look at the query generating the required dateset. Such a query might look like this: SELECT rownum num, TO_DATE('01-jan-15', 'dd-mon-yy') + INTERVAL '1' DAY*rownum-1 date_ FROM dual CONNECT BY LEVEL <= 15; The output produced should look like this: NUM DATE_ ---------- --------- 101-JAN-15 202-JAN-15 303-JAN-15 404-JAN-15 505-JAN-15 606-JAN-15 707-JAN-15 808-JAN-15 909-JAN-15 1010-JAN-15 1111-JAN-15 1212-JAN-15 1313-JAN-15 1414-JAN-15 1515-JAN-15 15 rows selected. Alternatively, you might calculate the number ofdays to be included in the report in the CONNECT BY LEVEL clause, as follows: SELECT rownum num, TO_DATE('1-jan-15','dd-mon-yy') + (rownum - 1) date_ FROM dual CONNECT BY LEVEL <= TO_DATE('15-jan-15','dd-mon-yy') - TO_DATE('1-jan-15', 'dd-mon-yy') +1; This should result in the same output as thepreceding one. Its fairly obvious that the query discussed here can be part ofa bigger one, showing information on some activities per day within thespecified diapason. In other words, you can build a join in which the abovewill generate a virtual table of 15 rows containing the dates from thediapason, and the other query in the join will walk through a table, such asorders, retrieving those placed within the range.
For simplicity, suppose you have an orders tablecontaining some generalized information about purchase orders. Such a tablemight be created as follows: CREATE TABLE orders( referenceVARCHAR2(32), ord_dateDATE, totalNUMBER(12,2) ); Then, you can fill it with the data: INSERT INTO orders VALUES( JCHEN-20150107123336462PDT, TO_DATE(7-jan-2015,DD-MON-YYYY), ); Of course, you might have more rows inserted. Now you can issue a join that might look like this: SELECT date_, nvl(Total,0) Total FROM (SELECTTO_DATE('01-jan-2015','dd-mon-yyyy')+INTERVAL '1' DAY*rownum-1 date_ FROM dual CONNECTBY LEVEL <=15) LEFTOUTER JOIN (SELECTsum(o.Total) Total, o.ord_date date_ FROM orders o GROUP BYo.ord_date) USING(date_) ORDER BYdate_; The output should look like this: DATE_ TOTAL --------- ---------- 01-JAN-15 0 02-JAN-15 0 03-JAN-15 0 04-JAN-15 0 05-JAN-15 0 06-JAN-15 0 07-JAN-15 121 08-JAN-15 0 09-JAN-15 0 10-JAN-15 0 11-JAN-15 0 12-JAN-15 0 13-JAN-15 0 14-JAN-15 0 15-JAN-15 0 15 rows selected. Looking through the above report, you may noticethat although the only day has an order, the report shows all the days in thespecified diapason. In MySQL, there is no CONNECT BY LEVEL feature, nor anyequivalent. So, you cannot generate rows on the fly in the way you can do it inOracle Database.
Font size:
Interval:
Bookmark:
Similar books «Mastering SQL: Queries - Take It Easy»
Look at similar books to Mastering SQL: Queries - Take It Easy. We have selected literature similar in name and meaning in the hope of providing readers with more options to find new, interesting, not yet read works.
Discussion, reviews of the book Mastering SQL: Queries - Take It Easy and just readers' own opinions. Leave your comments, write what you think about the work, its meaning or the main characters. Specify what exactly you liked and what you didn't like, and why you think so.