Are you over 18 and want to see adult content?
More Annotations
![A complete backup of www.veinsensor.pl](https://www.archivebay.com/archive3/images/96007028-8c4c-4b34-9337-e9a23b7dca88.png)
A complete backup of www.veinsensor.pl
Are you over 18 and want to see adult content?
![A complete backup of 131458934.keywordblocks.com](https://www.archivebay.com/archive3/images/cc49e861-a989-4153-becc-08596cf8e8ab.png)
A complete backup of 131458934.keywordblocks.com
Are you over 18 and want to see adult content?
![A complete backup of markkavanagh.com](https://www.archivebay.com/archive3/images/d2a8ad84-6d8d-4674-8078-369370017057.png)
A complete backup of markkavanagh.com
Are you over 18 and want to see adult content?
![A complete backup of azscers.000webhostapp.com](https://www.archivebay.com/archive3/images/b30d5f6f-a69e-4307-99cc-fcb2c3842496.png)
A complete backup of azscers.000webhostapp.com
Are you over 18 and want to see adult content?
![A complete backup of abc12.onesignal.com](https://www.archivebay.com/archive3/images/73b40cc0-ca43-4091-a0f3-5605c14b6e9a.png)
A complete backup of abc12.onesignal.com
Are you over 18 and want to see adult content?
![A complete backup of sp-active.adsrvr.org](https://www.archivebay.com/archive3/images/beb9c7c8-dbc1-414a-8f80-8e57ceba6771.png)
A complete backup of sp-active.adsrvr.org
Are you over 18 and want to see adult content?
![A complete backup of services.runescape.com-api.top](https://www.archivebay.com/archive3/images/a23f068e-afa9-4516-8921-b3dd7bc5e83a.png)
A complete backup of services.runescape.com-api.top
Are you over 18 and want to see adult content?
![A complete backup of cb1.dev.rtb.owneriq.net](https://www.archivebay.com/archive3/images/48356192-6b63-4b38-849c-e2acfadcec0c.png)
A complete backup of cb1.dev.rtb.owneriq.net
Are you over 18 and want to see adult content?
Favourite Annotations
![A complete backup of premierenglish.com.mx](https://www.archivebay.com/archive/c46d07e1-571c-49e1-a4e9-21a9e64103fe.png)
A complete backup of premierenglish.com.mx
Are you over 18 and want to see adult content?
![A complete backup of myprincessyoyo.com](https://www.archivebay.com/archive/b30331db-24db-4e89-ad65-232ec62e0434.png)
A complete backup of myprincessyoyo.com
Are you over 18 and want to see adult content?
![A complete backup of kabinet-gosuslugi.ru](https://www.archivebay.com/archive/85ed1cae-0d60-461f-b732-ad185e931e15.png)
A complete backup of kabinet-gosuslugi.ru
Are you over 18 and want to see adult content?
![A complete backup of codeinstitute.net](https://www.archivebay.com/archive/8607bd88-069e-4a14-98bf-67917c82ffe5.png)
A complete backup of codeinstitute.net
Are you over 18 and want to see adult content?
![A complete backup of smugglerscovesf.com](https://www.archivebay.com/archive/c61b4274-dcf2-4134-b4d6-24f1d575f383.png)
A complete backup of smugglerscovesf.com
Are you over 18 and want to see adult content?
![A complete backup of mypussyiswett.tumblr.com](https://www.archivebay.com/archive/4c50c173-e20c-47cd-89ee-086716a5ebb8.png)
A complete backup of mypussyiswett.tumblr.com
Are you over 18 and want to see adult content?
Text
facility id.
POSTGRESQL EXERCISES The WHERE clause allows us to filter for the rows we're interested in - in this case, those with a membercost of more than zero, and less than 1/50th of the monthly maintenance cost. As you can see, the massage rooms are very expensive to run thanks to staffing costs! When we want to test for two or more conditions, we use AND to combine them. We can, as you might expect, use OR to test POSTGRESQL EXERCISES Question Produce a list of facilities with a total revenue less than 1000. Produce an output table consisting of facility name and revenue,sorted by revenue.
POSTGRESQL EXERCISES Answers and Discussion Show select distinct mems.firstname || ' ' || mems.surname as member, facs.name as facility from cd.members mems inner join cd.bookings bks on mems.memid = bks.memid inner join cd.facilities facs on bks.facid = facs.facid where facs.name in ('Tennis Court 2','Tennis Court 1') order by member, facility POSTGRESQL EXERCISES This solution is perhaps more robust, as it cleans out more bad formatting. select memid, regexp_replace ( telephone, ' ', '', 'g') as telephone from cd. members order by memid; Making automated use of free-formatted text data can be a chore. Ideally you want to avoid having to constantly write code to clean up the data beforeusing it
POSTGRESQL EXERCISES Answers and Discussion Show. select bks. starttime as start, facs. name as name from cd. facilities facs inner join cd. bookings bks on facs. facid = bks. facid where facs. name in ( 'Tennis Court 2', 'Tennis Court 1') and bks. starttime >= '2012-09-21' and bks. starttime < '2012-09-22' order by bks. starttime; This is anotherINNER JOIN query
POSTGRESQL EXERCISES Question The Produce a list of costly bookings exercise contained some messy logic: we had to calculate the booking cost in both the WHERE clause and the CASE. statement.. Try to simplify this calculation using subqueries. For reference, the question was: How can you produce a list of bookings on the day of 2012-09-14 which will cost the member (or guest) more than $30? POSTGRESQL EXERCISES Question How can you produce a list of bookings on the day of 2012-09-14 which will cost the member (or guest) more than $30? Remember that guests have different costs to members (the listed costs are per half-hour 'slot'), and the guest user is always ID 0. POSTGRESQL EXERCISESGETTING STARTEDEXERCISESABOUTOPTIONSJOINS AND SUBQUERIESMODIFYING DATA PGExercises provides a series of questions and explanations built on a single, simple dataset. It's designed for use as a partner to a good book or Postgres' excellent documentation. The exercises on this site range from simple select and where clauses, through joins and case statements, and on to aggregations, window functions, and recursive POSTGRESQL EXERCISES Querying data is all well and good, but at some point you're probably going to want to put data into your database! This section deals with inserting, updating, and deleting information. POSTGRESQL EXERCISES Question Produce a list of the total number of hours booked per facility, remembering that a slot lasts half an hour. The output table should consist of the facility id, name, and hours booked, sorted byfacility id.
POSTGRESQL EXERCISES The WHERE clause allows us to filter for the rows we're interested in - in this case, those with a membercost of more than zero, and less than 1/50th of the monthly maintenance cost. As you can see, the massage rooms are very expensive to run thanks to staffing costs! When we want to test for two or more conditions, we use AND to combine them. We can, as you might expect, use OR to test POSTGRESQL EXERCISES Question Produce a list of facilities with a total revenue less than 1000. Produce an output table consisting of facility name and revenue,sorted by revenue.
POSTGRESQL EXERCISES Answers and Discussion Show select distinct mems.firstname || ' ' || mems.surname as member, facs.name as facility from cd.members mems inner join cd.bookings bks on mems.memid = bks.memid inner join cd.facilities facs on bks.facid = facs.facid where facs.name in ('Tennis Court 2','Tennis Court 1') order by member, facility POSTGRESQL EXERCISES This solution is perhaps more robust, as it cleans out more bad formatting. select memid, regexp_replace ( telephone, ' ', '', 'g') as telephone from cd. members order by memid; Making automated use of free-formatted text data can be a chore. Ideally you want to avoid having to constantly write code to clean up the data beforeusing it
POSTGRESQL EXERCISES Answers and Discussion Show. select bks. starttime as start, facs. name as name from cd. facilities facs inner join cd. bookings bks on facs. facid = bks. facid where facs. name in ( 'Tennis Court 2', 'Tennis Court 1') and bks. starttime >= '2012-09-21' and bks. starttime < '2012-09-22' order by bks. starttime; This is anotherINNER JOIN query
POSTGRESQL EXERCISES Question The Produce a list of costly bookings exercise contained some messy logic: we had to calculate the booking cost in both the WHERE clause and the CASE. statement.. Try to simplify this calculation using subqueries. For reference, the question was: How can you produce a list of bookings on the day of 2012-09-14 which will cost the member (or guest) more than $30? POSTGRESQL EXERCISES Question How can you produce a list of bookings on the day of 2012-09-14 which will cost the member (or guest) more than $30? Remember that guests have different costs to members (the listed costs are per half-hour 'slot'), and the guest user is always ID 0. POSTGRESQL EXERCISES Question Produce a list of the total number of slots booked per facility. For now, just produce an output table consisting of facility id and slots, sorted by facility id. POSTGRESQL EXERCISES The FROM clause is used to build up a set of candidate rows to read results from. In our examples so far, this set of rows has simply been the contents of a table. In future we will explore joining, which allows us to create much more interesting candidates. POSTGRESQL EXERCISES Aggregation is one of those capabilities that really make you appreciate the power of relational database systems. It allows you to move beyond merely persisting your data, into the realm of asking truly interesting questions that can be used to inform decision making. This category covers aggregation at length, making use ofstandard grouping
POSTGRESQL EXERCISES INSERT INTO VALUES is the simplest way to insert data into a table. There's not a whole lot to discuss here: VALUES is used to construct a row of data, which the INSERT statement inserts into the table. It's a simple as that. You can see that there's two sections in parentheses. The first is part of the INSERT statement, and specifies the columns that we're providing data for. POSTGRESQL EXERCISES The intent of this query is to get the highest totalslots value and its associated facid(s). Unfortunately, this just won't work! In the event of multiple facids having the same number of slots booked, it would be ambiguous which facid should be paired up with the single (or scalar) value coming out of the MAX function. This means that Postgres will tell you that facid ought to be in a GROUP POSTGRESQL EXERCISES This exercise marks the introduction of subqueries. Subqueries are, as the name implies, queries within a query. They're commonly used with aggregates, to answer questions like 'get me all the details of the member who has spent the most hours on Tennis Court 1'. POSTGRESQL EXERCISES Answers and Discussion Show. select bks. starttime as start, facs. name as name from cd. facilities facs inner join cd. bookings bks on facs. facid = bks. facid where facs. name in ( 'Tennis Court 2', 'Tennis Court 1') and bks. starttime >= '2012-09-21' and bks. starttime < '2012-09-22' order by bks. starttime; This is anotherINNER JOIN query
POSTGRESQL EXERCISES name revenue; Table Tennis: 180: Snooker Table: 240: Pool Table: 270: Badminton Court: 1906.5: Squash Court: 13468.0: Tennis Court 1: 13860: Tennis Court 2: 14310 POSTGRESQL EXERCISES For this question, we need to specify the columns that we want. We can do that with a simple comma-delimited list of column names specified to the select statement. POSTGRESQL EXERCISES The main piece of new functionality in this question is the EXTRACT function. EXTRACT allows you to get individual components of a timestamp, like day, month, year, etc. We group by the output of this function to provide per-month values. An alternative, if we needed to distinguish between the same month in different years, is to make use of the DATE_TRUNC function, which truncates a date to a POSTGRESQL EXERCISESGETTING STARTEDEXERCISESABOUTOPTIONSJOINS AND SUBQUERIESMODIFYING DATA PGExercises provides a series of questions and explanations built on a single, simple dataset. It's designed for use as a partner to a good book or Postgres' excellent documentation. The exercises on this site range from simple select and where clauses, through joins and case statements, and on to aggregations, window functions, and recursive POSTGRESQL EXERCISES Querying data is all well and good, but at some point you're probably going to want to put data into your database! This section deals with inserting, updating, and deleting information. POSTGRESQL EXERCISES Question Produce a list of the total number of hours booked per facility, remembering that a slot lasts half an hour. The output table should consist of the facility id, name, and hours booked, sorted byfacility id.
POSTGRESQL EXERCISES This topic covers inner, outer, and self joins, as well as spending a little time on subqueries (queries within queries). If you struggle with these questions, I strongly recommend Learning SQL, by Alan Beaulieu, as a concise and well-written book on the subject. Produce a list of all members, along with their recommender, using no joins. POSTGRESQL EXERCISES The WHERE clause allows us to filter for the rows we're interested in - in this case, those with a membercost of more than zero, and less than 1/50th of the monthly maintenance cost. As you can see, the massage rooms are very expensive to run thanks to staffing costs! When we want to test for two or more conditions, we use AND to combine them. We can, as you might expect, use OR to test POSTGRESQL EXERCISES Answers and Discussion Show select distinct mems.firstname || ' ' || mems.surname as member, facs.name as facility from cd.members mems inner join cd.bookings bks on mems.memid = bks.memid inner join cd.facilities facs on bks.facid = facs.facid where facs.name in ('Tennis Court 2','Tennis Court 1') order by member, facility POSTGRESQL EXERCISES Answers and Discussion Show. select bks. starttime as start, facs. name as name from cd. facilities facs inner join cd. bookings bks on facs. facid = bks. facid where facs. name in ( 'Tennis Court 2', 'Tennis Court 1') and bks. starttime >= '2012-09-21' and bks. starttime < '2012-09-22' order by bks. starttime; This is anotherINNER JOIN query
POSTGRESQL EXERCISES This exercise marks the introduction of subqueries. Subqueries are, as the name implies, queries within a query. They're commonly used with aggregates, to answer questions like 'get me all the details of the member who has spent the most hours on Tennis Court 1'. POSTGRESQL EXERCISES Answers and Discussion Show. select count (*) from cd. facilities; Aggregation starts out pretty simply! The SQL above selects everything from our facilities table, and then counts the number of rows in the result set. The count function has a variety of uses: COUNT (*) simply returns the number of rows. COUNT (address) counts the number of non POSTGRESQL EXERCISES Question How can you produce a list of bookings on the day of 2012-09-14 which will cost the member (or guest) more than $30? Remember that guests have different costs to members (the listed costs are per half-hour 'slot'), and the guest user is always ID 0. POSTGRESQL EXERCISESGETTING STARTEDEXERCISESABOUTOPTIONSJOINS AND SUBQUERIESMODIFYING DATA PGExercises provides a series of questions and explanations built on a single, simple dataset. It's designed for use as a partner to a good book or Postgres' excellent documentation. The exercises on this site range from simple select and where clauses, through joins and case statements, and on to aggregations, window functions, and recursive POSTGRESQL EXERCISES Querying data is all well and good, but at some point you're probably going to want to put data into your database! This section deals with inserting, updating, and deleting information. POSTGRESQL EXERCISES Question Produce a list of the total number of hours booked per facility, remembering that a slot lasts half an hour. The output table should consist of the facility id, name, and hours booked, sorted byfacility id.
POSTGRESQL EXERCISES This topic covers inner, outer, and self joins, as well as spending a little time on subqueries (queries within queries). If you struggle with these questions, I strongly recommend Learning SQL, by Alan Beaulieu, as a concise and well-written book on the subject. Produce a list of all members, along with their recommender, using no joins. POSTGRESQL EXERCISES The WHERE clause allows us to filter for the rows we're interested in - in this case, those with a membercost of more than zero, and less than 1/50th of the monthly maintenance cost. As you can see, the massage rooms are very expensive to run thanks to staffing costs! When we want to test for two or more conditions, we use AND to combine them. We can, as you might expect, use OR to test POSTGRESQL EXERCISES Answers and Discussion Show select distinct mems.firstname || ' ' || mems.surname as member, facs.name as facility from cd.members mems inner join cd.bookings bks on mems.memid = bks.memid inner join cd.facilities facs on bks.facid = facs.facid where facs.name in ('Tennis Court 2','Tennis Court 1') order by member, facility POSTGRESQL EXERCISES Answers and Discussion Show. select bks. starttime as start, facs. name as name from cd. facilities facs inner join cd. bookings bks on facs. facid = bks. facid where facs. name in ( 'Tennis Court 2', 'Tennis Court 1') and bks. starttime >= '2012-09-21' and bks. starttime < '2012-09-22' order by bks. starttime; This is anotherINNER JOIN query
POSTGRESQL EXERCISES This exercise marks the introduction of subqueries. Subqueries are, as the name implies, queries within a query. They're commonly used with aggregates, to answer questions like 'get me all the details of the member who has spent the most hours on Tennis Court 1'. POSTGRESQL EXERCISES Answers and Discussion Show. select count (*) from cd. facilities; Aggregation starts out pretty simply! The SQL above selects everything from our facilities table, and then counts the number of rows in the result set. The count function has a variety of uses: COUNT (*) simply returns the number of rows. COUNT (address) counts the number of non POSTGRESQL EXERCISES Question How can you produce a list of bookings on the day of 2012-09-14 which will cost the member (or guest) more than $30? Remember that guests have different costs to members (the listed costs are per half-hour 'slot'), and the guest user is always ID 0. POSTGRESQL EXERCISES Simple SQL Queries Begin! This category deals with the basics of SQL. It covers select and where clauses, case expressions, unions, and a few other odds and ends. If you're already educated in SQL you will probably find these exercises fairly easy. If not, you should find them a good point to start learning for the more difficult categoriesahead!
POSTGRESQL EXERCISES This topic covers inner, outer, and self joins, as well as spending a little time on subqueries (queries within queries). If you struggle with these questions, I strongly recommend Learning SQL, by Alan Beaulieu, as a concise and well-written book on the subject. Produce a list of all members, along with their recommender, using no joins. POSTGRESQL EXERCISES Aggregation is one of those capabilities that really make you appreciate the power of relational database systems. It allows you to move beyond merely persisting your data, into the realm of asking truly interesting questions that can be used to inform decision making. This category covers aggregation at length, making use ofstandard grouping
POSTGRESQL EXERCISES Let's introduce another new concept: the LEFT OUTER JOIN.These are best explained by the way in which they differ from inner joins. Inner joins take a left and a right table, and look for matching rows based on a join condition (ON).When the condition is satisfied, a joined rowis produced.
POSTGRESQL EXERCISES Question Produce a list of the total number of slots booked per facility. For now, just produce an output table consisting of facility id and slots, sorted by facility id. POSTGRESQL EXERCISES The FROM clause is used to build up a set of candidate rows to read results from. In our examples so far, this set of rows has simply been the contents of a table. In future we will explore joining, which allows us to create much more interesting candidates. POSTGRESQL EXERCISES The intent of this query is to get the highest totalslots value and its associated facid(s). Unfortunately, this just won't work! In the event of multiple facids having the same number of slots booked, it would be ambiguous which facid should be paired up with the single (or scalar) value coming out of the MAX function. This means that Postgres will tell you that facid ought to be in a GROUP POSTGRESQL EXERCISES Question Produce a list of facilities with a total revenue less than 1000. Produce an output table consisting of facility name and revenue,sorted by revenue.
POSTGRESQL EXERCISES The most commonly used kind of join is the INNER JOIN.What this does is combine two tables based on a join expression - in this case, for each member id in the members table, we're looking for matching values in the bookings table. POSTGRESQL EXERCISES SQL's LIKE operator provides simple pattern matching on strings. It's pretty much universally implemented, and is nice and simple to use - it just takes a string with the % character matching any string, and _ matching any single character. In this case, we're looking for names containing the word 'Tennis', so putting a % on either side fits thebill.
POSTGRESQL EXERCISESGETTING STARTEDEXERCISESABOUTOPTIONSJOINS AND SUBQUERIESMODIFYING DATA PGExercises provides a series of questions and explanations built on a single, simple dataset. It's designed for use as a partner to a good book or Postgres' excellent documentation. The exercises on this site range from simple select and where clauses, through joins and case statements, and on to aggregations, window functions, and recursive POSTGRESQL EXERCISES Querying data is all well and good, but at some point you're probably going to want to put data into your database! This section deals with inserting, updating, and deleting information. POSTGRESQL EXERCISES Question Produce a list of the total number of hours booked per facility, remembering that a slot lasts half an hour. The output table should consist of the facility id, name, and hours booked, sorted byfacility id.
POSTGRESQL EXERCISES The SELECT statement is the basic starting block for queries that read information out of the database. A minimal select statement is generally comprised of select from .. In this case, we want all of the information from the facilities table. The from section is easy - we just need to specify the cd.facilities table. POSTGRESQL EXERCISES This topic covers inner, outer, and self joins, as well as spending a little time on subqueries (queries within queries). If you struggle with these questions, I strongly recommend Learning SQL, by Alan Beaulieu, as a concise and well-written book on the subject. Produce a list of all members, along with their recommender, using no joins. POSTGRESQL EXERCISES The WHERE clause allows us to filter for the rows we're interested in - in this case, those with a membercost of more than zero, and less than 1/50th of the monthly maintenance cost. As you can see, the massage rooms are very expensive to run thanks to staffing costs! When we want to test for two or more conditions, we use AND to combine them. We can, as you might expect, use OR to test POSTGRESQL EXERCISES Answers and Discussion Show select distinct mems.firstname || ' ' || mems.surname as member, facs.name as facility from cd.members mems inner join cd.bookings bks on mems.memid = bks.memid inner join cd.facilities facs on bks.facid = facs.facid where facs.name in ('Tennis Court 2','Tennis Court 1') order by member, facility POSTGRESQL EXERCISES Answers and Discussion Show. select bks. starttime as start, facs. name as name from cd. facilities facs inner join cd. bookings bks on facs. facid = bks. facid where facs. name in ( 'Tennis Court 2', 'Tennis Court 1') and bks. starttime >= '2012-09-21' and bks. starttime < '2012-09-22' order by bks. starttime; This is anotherINNER JOIN query
POSTGRESQL EXERCISES This exercise marks the introduction of subqueries. Subqueries are, as the name implies, queries within a query. They're commonly used with aggregates, to answer questions like 'get me all the details of the member who has spent the most hours on Tennis Court 1'. POSTGRESQL EXERCISES Question How can you produce a list of bookings on the day of 2012-09-14 which will cost the member (or guest) more than $30? Remember that guests have different costs to members (the listed costs are per half-hour 'slot'), and the guest user is always ID 0. POSTGRESQL EXERCISESGETTING STARTEDEXERCISESABOUTOPTIONSJOINS AND SUBQUERIESMODIFYING DATA PGExercises provides a series of questions and explanations built on a single, simple dataset. It's designed for use as a partner to a good book or Postgres' excellent documentation. The exercises on this site range from simple select and where clauses, through joins and case statements, and on to aggregations, window functions, and recursive POSTGRESQL EXERCISES Querying data is all well and good, but at some point you're probably going to want to put data into your database! This section deals with inserting, updating, and deleting information. POSTGRESQL EXERCISES Question Produce a list of the total number of hours booked per facility, remembering that a slot lasts half an hour. The output table should consist of the facility id, name, and hours booked, sorted byfacility id.
POSTGRESQL EXERCISES The SELECT statement is the basic starting block for queries that read information out of the database. A minimal select statement is generally comprised of select from .. In this case, we want all of the information from the facilities table. The from section is easy - we just need to specify the cd.facilities table. POSTGRESQL EXERCISES This topic covers inner, outer, and self joins, as well as spending a little time on subqueries (queries within queries). If you struggle with these questions, I strongly recommend Learning SQL, by Alan Beaulieu, as a concise and well-written book on the subject. Produce a list of all members, along with their recommender, using no joins. POSTGRESQL EXERCISES The WHERE clause allows us to filter for the rows we're interested in - in this case, those with a membercost of more than zero, and less than 1/50th of the monthly maintenance cost. As you can see, the massage rooms are very expensive to run thanks to staffing costs! When we want to test for two or more conditions, we use AND to combine them. We can, as you might expect, use OR to test POSTGRESQL EXERCISES Answers and Discussion Show select distinct mems.firstname || ' ' || mems.surname as member, facs.name as facility from cd.members mems inner join cd.bookings bks on mems.memid = bks.memid inner join cd.facilities facs on bks.facid = facs.facid where facs.name in ('Tennis Court 2','Tennis Court 1') order by member, facility POSTGRESQL EXERCISES Answers and Discussion Show. select bks. starttime as start, facs. name as name from cd. facilities facs inner join cd. bookings bks on facs. facid = bks. facid where facs. name in ( 'Tennis Court 2', 'Tennis Court 1') and bks. starttime >= '2012-09-21' and bks. starttime < '2012-09-22' order by bks. starttime; This is anotherINNER JOIN query
POSTGRESQL EXERCISES This exercise marks the introduction of subqueries. Subqueries are, as the name implies, queries within a query. They're commonly used with aggregates, to answer questions like 'get me all the details of the member who has spent the most hours on Tennis Court 1'. POSTGRESQL EXERCISES Question How can you produce a list of bookings on the day of 2012-09-14 which will cost the member (or guest) more than $30? Remember that guests have different costs to members (the listed costs are per half-hour 'slot'), and the guest user is always ID 0. POSTGRESQL EXERCISES Simple SQL Queries Begin! This category deals with the basics of SQL. It covers select and where clauses, case expressions, unions, and a few other odds and ends. If you're already educated in SQL you will probably find these exercises fairly easy. If not, you should find them a good point to start learning for the more difficult categoriesahead!
POSTGRESQL EXERCISES Aggregation is one of those capabilities that really make you appreciate the power of relational database systems. It allows you to move beyond merely persisting your data, into the realm of asking truly interesting questions that can be used to inform decision making. This category covers aggregation at length, making use ofstandard grouping
POSTGRESQL EXERCISES The most commonly used kind of join is the INNER JOIN.What this does is combine two tables based on a join expression - in this case, for each member id in the members table, we're looking for matching values in the bookings table. POSTGRESQL EXERCISES Let's introduce another new concept: the LEFT OUTER JOIN.These are best explained by the way in which they differ from inner joins. Inner joins take a left and a right table, and look for matching rows based on a join condition (ON).When the condition is satisfied, a joined rowis produced.
POSTGRESQL EXERCISES The FROM clause is used to build up a set of candidate rows to read results from. In our examples so far, this set of rows has simply been the contents of a table. In future we will explore joining, which allows us to create much more interesting candidates. POSTGRESQL EXERCISES Answers and Discussion Show. select count (*) from cd. facilities; Aggregation starts out pretty simply! The SQL above selects everything from our facilities table, and then counts the number of rows in the result set. The count function has a variety of uses: COUNT (*) simply returns the number of rows. COUNT (address) counts the number of non POSTGRESQL EXERCISES The intent of this query is to get the highest totalslots value and its associated facid(s). Unfortunately, this just won't work! In the event of multiple facids having the same number of slots booked, it would be ambiguous which facid should be paired up with the single (or scalar) value coming out of the MAX function. This means that Postgres will tell you that facid ought to be in a GROUP POSTGRESQL EXERCISES Question Produce a list of facilities with a total revenue less than 1000. Produce an output table consisting of facility name and revenue,sorted by revenue.
POSTGRESQL EXERCISES SQL's LIKE operator provides simple pattern matching on strings. It's pretty much universally implemented, and is nice and simple to use - it just takes a string with the % character matching any string, and _ matching any single character. In this case, we're looking for names containing the word 'Tennis', so putting a % on either side fits thebill.
POSTGRESQL EXERCISES This solution is perhaps more robust, as it cleans out more bad formatting. select memid, regexp_replace ( telephone, ' ', '', 'g') as telephone from cd. members order by memid; Making automated use of free-formatted text data can be a chore. Ideally you want to avoid having to constantly write code to clean up the data beforeusing it
POSTGRESQL EXERCISESGETTING STARTEDEXERCISESABOUTOPTIONSJOINS AND SUBQUERIESMODIFYING DATA PGExercises provides a series of questions and explanations built on a single, simple dataset. It's designed for use as a partner to a good book or Postgres' excellent documentation. The exercises on this site range from simple select and where clauses, through joins and case statements, and on to aggregations, window functions, and recursive POSTGRESQL EXERCISES Querying data is all well and good, but at some point you're probably going to want to put data into your database! This section deals with inserting, updating, and deleting information. POSTGRESQL EXERCISES Question Produce a list of the total number of hours booked per facility, remembering that a slot lasts half an hour. The output table should consist of the facility id, name, and hours booked, sorted byfacility id.
POSTGRESQL EXERCISES The SELECT statement is the basic starting block for queries that read information out of the database. A minimal select statement is generally comprised of select from .. In this case, we want all of the information from the facilities table. The from section is easy - we just need to specify the cd.facilities table. POSTGRESQL EXERCISES This topic covers inner, outer, and self joins, as well as spending a little time on subqueries (queries within queries). If you struggle with these questions, I strongly recommend Learning SQL, by Alan Beaulieu, as a concise and well-written book on the subject. Produce a list of all members, along with their recommender, using no joins. POSTGRESQL EXERCISES The WHERE clause allows us to filter for the rows we're interested in - in this case, those with a membercost of more than zero, and less than 1/50th of the monthly maintenance cost. As you can see, the massage rooms are very expensive to run thanks to staffing costs! When we want to test for two or more conditions, we use AND to combine them. We can, as you might expect, use OR to test POSTGRESQL EXERCISES Answers and Discussion Show select distinct mems.firstname || ' ' || mems.surname as member, facs.name as facility from cd.members mems inner join cd.bookings bks on mems.memid = bks.memid inner join cd.facilities facs on bks.facid = facs.facid where facs.name in ('Tennis Court 2','Tennis Court 1') order by member, facility POSTGRESQL EXERCISES Answers and Discussion Show. select bks. starttime as start, facs. name as name from cd. facilities facs inner join cd. bookings bks on facs. facid = bks. facid where facs. name in ( 'Tennis Court 2', 'Tennis Court 1') and bks. starttime >= '2012-09-21' and bks. starttime < '2012-09-22' order by bks. starttime; This is anotherINNER JOIN query
POSTGRESQL EXERCISES This exercise marks the introduction of subqueries. Subqueries are, as the name implies, queries within a query. They're commonly used with aggregates, to answer questions like 'get me all the details of the member who has spent the most hours on Tennis Court 1'. POSTGRESQL EXERCISES Question How can you produce a list of bookings on the day of 2012-09-14 which will cost the member (or guest) more than $30? Remember that guests have different costs to members (the listed costs are per half-hour 'slot'), and the guest user is always ID 0. POSTGRESQL EXERCISESGETTING STARTEDEXERCISESABOUTOPTIONSJOINS AND SUBQUERIESMODIFYING DATA PGExercises provides a series of questions and explanations built on a single, simple dataset. It's designed for use as a partner to a good book or Postgres' excellent documentation. The exercises on this site range from simple select and where clauses, through joins and case statements, and on to aggregations, window functions, and recursive POSTGRESQL EXERCISES Querying data is all well and good, but at some point you're probably going to want to put data into your database! This section deals with inserting, updating, and deleting information. POSTGRESQL EXERCISES Question Produce a list of the total number of hours booked per facility, remembering that a slot lasts half an hour. The output table should consist of the facility id, name, and hours booked, sorted byfacility id.
POSTGRESQL EXERCISES The SELECT statement is the basic starting block for queries that read information out of the database. A minimal select statement is generally comprised of select from .. In this case, we want all of the information from the facilities table. The from section is easy - we just need to specify the cd.facilities table. POSTGRESQL EXERCISES This topic covers inner, outer, and self joins, as well as spending a little time on subqueries (queries within queries). If you struggle with these questions, I strongly recommend Learning SQL, by Alan Beaulieu, as a concise and well-written book on the subject. Produce a list of all members, along with their recommender, using no joins. POSTGRESQL EXERCISES The WHERE clause allows us to filter for the rows we're interested in - in this case, those with a membercost of more than zero, and less than 1/50th of the monthly maintenance cost. As you can see, the massage rooms are very expensive to run thanks to staffing costs! When we want to test for two or more conditions, we use AND to combine them. We can, as you might expect, use OR to test POSTGRESQL EXERCISES Answers and Discussion Show select distinct mems.firstname || ' ' || mems.surname as member, facs.name as facility from cd.members mems inner join cd.bookings bks on mems.memid = bks.memid inner join cd.facilities facs on bks.facid = facs.facid where facs.name in ('Tennis Court 2','Tennis Court 1') order by member, facility POSTGRESQL EXERCISES Answers and Discussion Show. select bks. starttime as start, facs. name as name from cd. facilities facs inner join cd. bookings bks on facs. facid = bks. facid where facs. name in ( 'Tennis Court 2', 'Tennis Court 1') and bks. starttime >= '2012-09-21' and bks. starttime < '2012-09-22' order by bks. starttime; This is anotherINNER JOIN query
POSTGRESQL EXERCISES This exercise marks the introduction of subqueries. Subqueries are, as the name implies, queries within a query. They're commonly used with aggregates, to answer questions like 'get me all the details of the member who has spent the most hours on Tennis Court 1'. POSTGRESQL EXERCISES Question How can you produce a list of bookings on the day of 2012-09-14 which will cost the member (or guest) more than $30? Remember that guests have different costs to members (the listed costs are per half-hour 'slot'), and the guest user is always ID 0. POSTGRESQL EXERCISES Simple SQL Queries Begin! This category deals with the basics of SQL. It covers select and where clauses, case expressions, unions, and a few other odds and ends. If you're already educated in SQL you will probably find these exercises fairly easy. If not, you should find them a good point to start learning for the more difficult categoriesahead!
POSTGRESQL EXERCISES Aggregation is one of those capabilities that really make you appreciate the power of relational database systems. It allows you to move beyond merely persisting your data, into the realm of asking truly interesting questions that can be used to inform decision making. This category covers aggregation at length, making use ofstandard grouping
POSTGRESQL EXERCISES The most commonly used kind of join is the INNER JOIN.What this does is combine two tables based on a join expression - in this case, for each member id in the members table, we're looking for matching values in the bookings table. POSTGRESQL EXERCISES Let's introduce another new concept: the LEFT OUTER JOIN.These are best explained by the way in which they differ from inner joins. Inner joins take a left and a right table, and look for matching rows based on a join condition (ON).When the condition is satisfied, a joined rowis produced.
POSTGRESQL EXERCISES The FROM clause is used to build up a set of candidate rows to read results from. In our examples so far, this set of rows has simply been the contents of a table. In future we will explore joining, which allows us to create much more interesting candidates. POSTGRESQL EXERCISES Answers and Discussion Show. select count (*) from cd. facilities; Aggregation starts out pretty simply! The SQL above selects everything from our facilities table, and then counts the number of rows in the result set. The count function has a variety of uses: COUNT (*) simply returns the number of rows. COUNT (address) counts the number of non POSTGRESQL EXERCISES The intent of this query is to get the highest totalslots value and its associated facid(s). Unfortunately, this just won't work! In the event of multiple facids having the same number of slots booked, it would be ambiguous which facid should be paired up with the single (or scalar) value coming out of the MAX function. This means that Postgres will tell you that facid ought to be in a GROUP POSTGRESQL EXERCISES Question Produce a list of facilities with a total revenue less than 1000. Produce an output table consisting of facility name and revenue,sorted by revenue.
POSTGRESQL EXERCISES SQL's LIKE operator provides simple pattern matching on strings. It's pretty much universally implemented, and is nice and simple to use - it just takes a string with the % character matching any string, and _ matching any single character. In this case, we're looking for names containing the word 'Tennis', so putting a % on either side fits thebill.
POSTGRESQL EXERCISES This solution is perhaps more robust, as it cleans out more bad formatting. select memid, regexp_replace ( telephone, ' ', '', 'g') as telephone from cd. members order by memid; Making automated use of free-formatted text data can be a chore. Ideally you want to avoid having to constantly write code to clean up the data beforeusing it
PostgreSQL Exercises*
* Home
* Getting Started
* Exercises
* Basic
* Joins and Subqueries* Modifying data
* Aggregates
* Date
* String
* Recursive
* About
* Options
POSTGRESQL EXERCISES Welcome to PostgreSQL Exercises! This site was born when I noticed that there's a load of material out there to help people learn about SQL, but not a great deal to make it easy to learn by doing. PGExercises provides a series of questions and explanations built on a single, simple dataset. It's designed for use as a partner to a good book or Postgres' excellent documentation.
The exercises on this site range from simple select and where clauses, through joins and case statements, and on to aggregations, window functions, and recursive queries. Most people who aren't already pros should find something to test themselves with. For an introduction to the dataset, go to Getting Started , then select an exercise category from the menuand go!
Site content licensed under CC BY-SA 3.0Details
Copyright © 2024 ArchiveBay.com. All rights reserved. Terms of Use | Privacy Policy | DMCA | 2021 | Feedback | Advertising | RSS 2.0