So, that covers the basic set of queries, we are going to come back later in chapter 4 and cover outer joins and other kind of queries. But before we do that, I want to cover modification of the database. So, there is the delete operation, delete from instructor what does it do wipes out all instructors, all tuples from instructor, which is different from drop ping the table instructor, delete from instructor retains the table removes all tuples, if you drop the table the table itself is gone. Now, you may want to do something less drastic and delete only instructors from the finance department. Now, here is a next query which says delete all tuples in the instructor relation for those instructors who are in a department which is located in the Watson building. So, not only do we you know want to delete one department, we want to delete how many of departments are located in the Watson building. Now, if you remember the schema if you have it with you, you will notice that department has a building name. So, we are assuming that each department has a building, a department cannot have two buildings that is not important here. So, each department has one building, but more than one department can be in the same building and we want to wipe out all those departments which happened to be in the Watson building. So, we can say delete from instructor where department name in select department name from department where building is Watson. So, here is a sub query connected with an in clause which lets us choose which department which instructors to delete. Now, here is another query which is interesting delete all instructors whose salary is less than the average salary of instructors. So, how do we do this? Well, here is a simple way of writing it delete from instructor where salary is less than select average salary from instructor. Note that this is a scalar sub query because it returns a single value is used in an expression where you expect a single value the right hand side of less than would be a single value here. So, now average salary of instructor is a single value well technically the sub query returns a single tuple with a single attribute whose value is the average salary. But in SQL you can use it in a place where a single value is expected and it automatically gets the value out of the tuple in compass. So, if you are familiar with type systems in programming languages these two types are different the right side of less than is set having a single tuple with a single attribute the left side is a value, but the type is automatically cast by the less than operation the type conversion happens automatically. So, now if you did this in a naive way if you execute this in a naive way you first take the first instructor check the average salary check if the salary is less then delete that instructor. Now, get the next instructor again compute the average salary now there is a problem if you deleted the first instructor the average has changed. So, now depending on order in which you chose the instructors you may delete a completely different set of instructors that would be very bad situation you do not want that. So, in SQL the semantics of these deletes are slightly different it is not you cannot evaluate the sub query again and again after doing deletions instead conceptually what you do is first compute the average salary and find all the tuples to be deleted based on the average salary at this point no deletion is to be done the same with other thing insert and so on. First compute the set do not actually do anything after you found out which all tuples to delete you can go ahead and do the deletion at this point we are not again computing averages we have already chosen which all to delete using the original average and that is it afterwards we finish the deletion. So, that is how the semantics is defined now it should be clear that regardless of the order in which you consider tuples this does not matter we computed the average already before deleting any tuple. Now, insertion again this shows you multiple syntax variations for doing insertion the one we saw so far was insert into course values and then list the values in the standard order which was done when we created the table so this is what we saw the second one is an alternative where we say insert into course we list the attributes explicitly in whatever order we choose and then we provide the values in the same order now why is this useful because we can now go and modify the table course and add a new attribute let us say a comment or something now what happens to this insert statement the first one will fail because now course has 5 attributes but this has only 4 attributes this one would actually succeed because explicitly given the 4 attributes the 5th attribute which we have not specified will get some default value if you do nothing it gets a value null you can also declare in the table what is the default value if so it will get the default value if you do not specify it now here is another example if you want to insert a value null you can type null over here so if the total credits for a student is set to null I have to type null note that I should not put a quote if I say quote null quote that is actually the string null by just saying null means a null value especially now the insert statement can actually take a query in there and take the entire set of results of the query and insert it into a new table so here is one thing which says instruct into student select ID name department name 0 from instructor what is this doing it is turning every instructor into a student whose total credits value is 0 now this is not very meaningful but it shows you the syntax for computing the results of a query and then inserting them all at once into another relation this is related to the question which somebody had asked earlier on chat which was what if you want to insert a set of values if that set of values already in the database and I can get it by running a sub query like this this is the syntax if that set of values is outside of the database then that is when you need to use either a series of insert statements or a special bulk load facility which database dependent now again the statement here is fully evaluated before any insert happens otherwise we will run into trouble so supposing we say insert into table one select star from table one and let us say the table one does not have a primary key declaration so now if I am scanning table one and I insert a tuple and I continue scanning it to find other tuples when I come to the end I may find the tuple which I just inserted and I will insert that again and continuing the scan I will find the newly inserted tuple and I will keep inserting again and again I will go into an infinite loop inserting the same tuple over and over which is back so the SQL language definition prevents this by saying first compute the set of tuples which you want to insert get that set and then do the inserted one so you will not see the same tuples over and over of course if I had a primary key it would not matter the primary key violation would occur right away and finally updates again there are many kinds of things you can do with updates here is a simple one which gives a salary increase so it says update instructor set salary is equal to salary times 1.03 where salary greater than 100000 what is this doing it is giving a 3 percent raise of salary to all those with high salaries what about those with lower salaries less than 100000 they have chosen to give it a 5 percent raise so the salary is set to salary times 1.05 so that is how you give differential raise to different employees but this particular update is tricky supposing I flip the two statements I first did the one which updated the salary of those less than 100000 and then did the second I think what will happen supposing somebody's salary was 96 or 97000 I give that person a 5 percent raise boom the salary is now more than 100000 now if I run the this first one if I run it second it is going to find the salary is greater than 100000 and it is going to give the one more raise of 3 percent they will be very happy but others are not going to be happy seeing them getting a double raise so that is an error so it is a little risky to write multiple updates like this if you are not careful so in this particular case much better way of writing it is using a case construct a case construct is sort of like the question mark colon operator in C language and what does it do let us just look at the case construct here case when salary less than 100000 then salary star 1.5 else salary star 1.3 the case construct is returning a value now if you know the C language the case statement there is slightly different the case statement executes one of the statements in the case it does not return a value per say although it could be used to return here the case statement returns a value and returns one of these values depending on which of these conditions so if this is satisfied it returns this else that so now look in the main query update instructor set salary equal to case when this then this else this end not that this is very safe I am just doing one scan of the relation depending on which condition is satisfied I give a 5 percent raise or a 3 percent raise it is safer than the previous one it is also more efficient so that is a very useful construct and finally here are few updates which would be basically impossible unless we use scalar sub queries. So here is the squaring if you recall the schema every student had an attribute called total credits or taught under credits which is the total number of credits which they have completed successfully meaning they passed the course now if you see the sample tables which we have provided in the book you will realize as many students did that the total credits do not match and the reason this happened was we you know reduce the number of rows in the takes table to keep it small and then the total credits do not match that so there are rows which we did not put in the takes table supposing I want to update the total credits for all students based on the actual rows in the takes table I can write a query to do that how do I find the actual total credits for each student now let us just look at the sub query that should help you see what is happening to find the credits I have to join takes with course because the credit attribute is not there in the takes only the course ID is there but each course can have a different number of credits I have to join with course so takes natural joint course now takes natural joint course gives me rows for all students I wanted for a particular student in this case for the specific outer level student I am considering here so I am saying updates student s and in here where s dot ID equal to takes dot ID so first of all I am filtering out all the rows which do not correspond to this student now this student may also have taken a course and failed it I do not want to count the credits for such takes instances so I am going to say and takes dot grade not equal to f now furthermore this person may have taken the course but no grade has been allocated yet because the semester is going on so grade is not yet meaningful so grade may be null I want to eliminate those also so I am saying and takes dot grade is not null so what will be left is only those where the grade is not null and it is not so I am going to sum up those credits and the update statement is actually setting the total taught under credit for this student equal to the result of this sub query so this is a scalar sub query which is now being used to update the total credits attribute of the student I hope this was clear but there are a couple of issues here so first of all if somebody has not taken any course or maybe they took a course but it is grade is still null or they have taken a course but they have not passed it in which case what happens this sub query returns an empty set so what is someone an empty set if you recall our aggregate discussion someone an empty set is null count is 0 but some is null so the total credits for the student would be set to null it would not be set to 0 so if you want to set it to 0 what do we do we can use the following case statement you can say case when some credits is not null then some credits else 0 so that takes care of handling the case where if it was null it will be set to 0 so I hope this query is clear so that is it for now but we will take time for a few questions we have a few people with their hands raised let us try NIT var angle NIT var angle we have a question please go ahead first question is regarding deletion how to delete duplicate rows from the table how do you do this it is actually a little tricky because if you delete one row the other will also get deleted because when you say declaratively delete something whatever condition you give all the duplicates will satisfy that condition so the only way to delete duplicate rows is to first make a copy of the rows of in that relation in some other table temporary table so you can say select distinct star from this table and save it up in something else and then wipe out the contents of the table delete all the rows and then insert the remaining rows back into that table that is the only way you can actually do it in SQL now there are tricks which a few databases offer which let you access row IDs and so on which can help you this thing you know pick the minimum row ID among all rows which are equal on all the other attributes so if your database supports those features there are other ways of doing it but in the standard SQL there is no other way of doing it so if you if it does for example Postgres SQL has a row ID so I can delete everything except the minimum row ID for a particular value for the remaining thing so I will delete where the row ID is not equal to select minimum row ID from the same relation where all the attributes are equal so that that is not too hard to do does that answer your question regarding the assignment one like yesterday's assignment question number three can I ask some question regarding that okay the question goes like this find the ID name of instructors who have taught a course in computer science department even if they are themselves not from the computer science department okay so the point of this question was if you did a natural join across all of the relations involved it would make the department name of the instructor equal to the department name of the course we discussed this question during the slides for earlier in this chapter three so the question was basically to make sure you took care of that so does that answer whatever doubt you had or did you have some other doubt what is the difference between delete and truncate okay delete and truncate is not a part of standard SQL it's part of Oracle and delete you know let's you specify which rows you want to delete truncate says just wipe out all the rows of the table so it's just extra Oracle syntax now the way they executed is probably a little bit different but conceptually as far as I know I have not used the truncate very much but as far as I recall it is equivalent to just deleting all there may be some other minor differences but it's not standard SQL when we copy the structure of a database to another structure do the constraints also get copied that's a good question when you copy the structure so the question is how do you copy the structure depends on how you do that now if you you know export the schema and then reload it you can export the schema in SQL which lets you you know save all the constraints including foreign key primary key everything is saved however if you do something like you know create table as select star from another table that's a way to copy a table and create a new table with the same columns and the same types as the first table so this is supported in many database it's wasn't there in standard SQL earlier I think it is there now and it is widely supported create table as select from star from something else the problem with this is it does not preserve the constraints the newly created table does not inherit any of the primary key of foreign key constraints from the original table does that answer your question hello thank you sir okay so now let's move to some other place which still wants a question VNIT in Akpur still has its hand raised so let us see I can see you VNIT in Akpur go ahead if a query involves real class having class order by class and group by class then what was the execution plan of the query processor that means which class will execute first and why yeah that's a good question if there are multiple clauses what is the order in which they are considered so the SQL language defines the order like this first is a from clause that is central that list the relations which are involved so conceptually the first step is to take the cross product now the actual execution plan may rearrange things as long as the result is the same but to understand what the query means first take the cross product or whatever is in the from clause then apply the where clause to filter out rows which don't satisfy it of course a good implementation would actually combine these two steps by doing a join on a condition rather than just taking a cross product now once you've got a set of rows if there is a group by clause that is applied to do a grouping and then the select clause can be evaluated if the select clause has aggregates it would be one rope a group if it does not have aggregates then you know if there are no group by clause and there is an aggregate it will be just one row if there are no group by clause and no aggregates the select is applied to get whatever you have now if you had a group by you could optionally have a having clause so after you compute the aggregate in the select clause you can apply the having clause and filter out those things in the results of the select which fail the having clause and finally if there is an order by clause that is applied to order the result so that is the order in which this happened conceptually now a good query optimizer may actually mix up these steps and to get a faster plan provided the result is the same does that answer your question any other questions from VNIT thank you thank you very much let's see if anybody else has a question there is one from Amrita Bangalore sir can you explain the query find all the students who have taken all the courses offered in the biology department that slide once again okay that's a good question because many people may have been confused by that so let's go back to okay I hope you can see this slide up which says find all students who have taken all the courses offered in the biology department so first of all I hope the query itself is clear the biology department may offer multiple courses certain students may have taken all of those courses I want them I don't want those who have taken 0 or 1 or 2 but those who have taken all the courses which are offered by the biology department so this kind of a query which requires things which have the for all form that is I'm returning students such that for all courses in biology they've taken that course that is what the question says now how do I write that question since SQL does not have for all construct but it has a not exist construct I'm going to use double negation what is double negation here if you say this is true for all things it is the same as saying that does not exist the thing for which this is not true so I want to find students such that there does not exist a biology course that they have not taken so that is the basic way in which you write such queries in SQL now don't ask me why does SQL not have for all construct I don't know the answer to that the sandage bodies decide that but you can work around it although it is a little confusing at first but you'll get used to it so what we are doing is find students such that there does not exist the course in biology that the student has not taken so not exist course in biology is the first part select course ID from course where department names biology which the student has not taken so I want to remove from these courses all those which the student has taken so if I take the courses in biology and remove all the courses the student has taken what is left is the biology courses that the student has not taken if this set is empty then the not exist condition will be true and the student will be output which is correct but if this set is not empty that means there is some biology course which this student did not take then the sub query result is not empty so the not exist condition here will fail and that student will not appear in the output I hope that answered your question let me come back to you on yeah Amrita it's back to you if you have any follow-up question yes sir thank you thank you


Item number SCP-601 Object Class Euclid Special Containment Procedures SCP-601 is to be confined to group humanoid containment, GHC, Suite 2 on Site 3 at all times. This suite is to be furnished and maintained, and as per standard humanoid containment procedures, with the following addenda. All furnishings and plumbing fixtures placed in GHC Suite 2 are to be provided in sets of 12. No flammable materials are to be used in the construction of furniture placed in GHC Suite 2. No two items of furniture or plumbing fixtures of like type placed in GHC Suite 2 are to be set more than 5 meters apart from one another. Audio recording equipment is to be installed throughout GHC Suite 2. All audio data is to be translated and analyzed by two personnel with level 2 clearance or higher. The floor of GHC Suite 2 is to be mopped daily, in addition to standard weekly custodial service. Meals provided to SCP-601 are not to contain meat or meat products. Following the events of experiment 601-0135, SCP-601 is to remain under 24-hour video surveillance. Should any foreign objects be observed, GHC Suite 2 is to be searched. All foreign objects are to be removed, and any missing items previously issued to SCP-601 are to be replaced. Under no circumstances is entertainment media of any kind to be introduced into GHC Suite 2, except during approved experiments. In the event of the deaths of any of the members of SCP-601, one D-class individual is to be introduced into GHC Suite 2 for each member lost. Description. SCP-601 is a group of 12 adult humans, of varying age, gender, and ethnicity. The constituent members of SCP-601 perform all actions and bodily functions in unison, in as much as is physically and anatomically possible, with no more than 0.15 seconds of delay between any two members of the collective. The source of this synchronization is unclear. No means of relaying nervous system responses between the 12 components of SCP-601 has been observed. Moreover, fMRI and EEG readings of SCP-601-1 through SCP-601-12 suggest zero brain function, despite continued and synchronous operation of all other organ systems. Proximity appears to be a significant factor in this regard. If any member of SCP-601 is taken farther than 5 meters from the current center of SCP-601, it ceases to be a member of SCP-601, and immediately responds to the effects of total brain death, as is typical for a human body. As SCP-601 will typically orient themselves to occupy the smallest space possible for a given activity, such events are rare. Although the actions taken by members of SCP-601 are generally identical, small variations may occur to facilitate successful navigation or manipulation of an environment. This behavior is most conspicuous while SCP-601 is walking. Small variations in stride length and turning radius allow each component of SCP-601 to arrive at a separate sink, bed, seat, etc. as needed. SCP-601 speaks aloud at all times, regardless of activity, pausing only to inhale, sleep, eat, or drink. The content of SCP-601's speech varies, but typically resembles a dramatic narration of any event taking place within 4 kilometers of the current center of SCP-601. The language used in this speech varies and may change at most once every 76 hours. Virtually all known and several unknown languages have been used by SCP-601, including dialects dated as early as the 5th century BCE. In the event that any of the 12 members of SCP-601 expire or become permanently incapacitated for any reason, the remaining members of SCP-601 will enter an agitated state and actively seek out the nearest able-bodied human. Henceforth, the subject. SCP-601 will physically restrain the subject with their right hands while placing their left hands on the subject's head, brainstem, solar plexus, left wrist, and right quadriceps. After approximately 30 seconds in this position, the subject will become a component of SCP-601. The collective will repeat this behavior until all 12 members have been restored. Addendum 601-1 SCP-601 was acquired by the Foundation from upon their collapse in 18. Though most of the records kept by were lost or destroyed, remaining evidence suggests that SCP-601 was in turn inherited from a prior organization. Research into possible origins of SCP-601 are ongoing. Addendum 601-2 SCP-601 presents several unusual behaviors in the presence of food and drink. When first presented with a set of beverages for each member, SCP-601 will pour several milliliters of each beverage onto the floor prior to consumption. Additionally, when presented with beef, lamb, and possibly other forms of meat as part of a meal, SCP-601 will attempt to create an open flame with any available implements, which it will use to incinerate a portion of the meat. Further investigation of the latter behavior is discouraged to avoid injury to SCP-601 and damage to the containment facility. Addendum 601-3 On 1970, SCP-601 was presented with a copy of Through the Looking Glass in the course of a regularly scheduled experiment, with no immediate reaction from or effect on the group. However, on the morning following exposure, SCP-601 was found wearing non-regulation clothing. Each member of SCP-601 was dressed in a red costume, reminiscent of a chess piece, including heavily stylized wooden masks. The individual's foundation-issued uniforms were not found in the containment suite. Audio logs indicate that upon waking that morning, SCP-601's speech consisted of an original description of the events of the novel. Upon the conclusion of this description, normal behavior resumed. Further experimentation with entertainment media requires written consent from the acting director of Site 3. Lesson complete. If you missed the previous orientation, go watch SCP-600, that guy, right now. Or for the complete course, watch this playlist.


Good afternoon, folks. Steve Rhodes, coming to you live from the shores of sunny Delray Beach, Florida. This year, 1 p.m. update, and currently we have a bit of a mixed bag out here. That mixed bag has the Dow and S&P trading lower. Dow is off 261. S&P is off 17. NASDAQ, Russell, and Summars are trading higher. The NASDAQ is up 5 points. The Russell's up 5 points. The Summars are up 13 points. Trendy's are off 205. New York Stock Exchange down 14 points. Gold up 26 bucks. 18.41 is the print there. Silver's up 36 cents. 21.91. Slights recruit up 39 pennies. 109.98. Natural gas is up the penny at 8.37. Third-year Treasury up 20 ticks. She's printed out at 1.40.30. As we take a look at our nine-panel market update chart, we begin by taking a look at the ESPini price got down within one tick of the low from May the 12th. Prices back above the bottom of its daily profile. The daily profile level that you're watching there is 38.99. Spot volatility is still above its 50-day exponential moving average. Always troublesome for the S&P 500. The case of the NQ got close, but no cigar got down to 11704. 11689 is the actual low of that May 12th swing point. However, prices back inside its daily profile. The US dollar index of pulling back is trading below the bottom of its daily profile. It's trading into the center of its weekly profile. A weekly close below 102.10286 is going to suggest we could see a pullback to that rising trend line at least the bottom of the weekly profile in the 101 area. Gold is held at the bottom of its daily profile. It should target the 1861 to 1870ish level out there. That's a bearish structured daily profile. Silver on the other hand, trading with its added bullish structured daily profile. So she should be able to make her run to 2276. Lights recruit, pulled back, tested support. Support being old resistance, the top of its daily profile. That's at the 104.44 level. As long as that level holds, we could see a move up to 116. Natural gas likely targeted a nine dollar area. That's its roadsman to Mindicator top, I believe. And we have the 30-year Treasury. She's just consolidating with inside her daily profile. So that's the one o'clock wrap. We've got a few seconds left. So let me just share with you. We'll take a look at this during the Trader Zed show. So stick with us for that. But this is the charts. These are the charts. These are the level, the levels that you need to watch today. They all are coming from the 120 minute timeframe for the equity futures contracts. So I'll leave the screen up here. Those of you inside the dead that are watching this and everybody should be inside the dead. You tell me why and where are the key resistance levels for each of these See roads with TFN and what we write.


Mendel proposed that you inherit a gene either from your mother or from your father, but not a mixture of both. In our genetic crosses video, we looked at what happens when yellow pea pod and green pea pod parents are crossed and represented the offspring in genetic crosses. In this video we are going to look at mono hybrid crosses using punnett squares. A mono hybrid cross is a study of the inheritance of one characteristic such as the pea pod color. A punnett square is a diagram that is used to predict the outcome of a particular cross. It is used by biologists to determine the probability of an offspring having a particular genotype. The genotype represents the genes of the organism and is shown by two letters. A capital letter represents the dominant form of a gene or allele and a lower case letter represents the recessive form of the gene. So the dominant yellow pea pod is shown by capital Y and the recessive green pea pod by little y. Now batch our punnett squares. We start with an entry grid like this. If we crossed a heterozygous yy and a recessive yy parent together, we enter the first parent's genotype here. Split them and enter one y in the first column and the other y in the second column. And the second parent, yeah, doing the same thing. One y in the top row and the second y in the bottom row. It doesn't matter which parent you could wear, both are correct. So these are the parent's genotypes and these are going to be the possible offspring genotypes. Let's fill in the gaps. I'll do the first one for you. Dominant y and recessive y gives yy. Can you complete the rest of the punnett square? Pause the video, have a think and click play when you're ready to check. Did you get it right? Make sure you always have two letters in each of the four boxes. We have two yy genotypes which means 50% of these offspring would have the dominant yellow p-pods and we have two yy genotypes. So 50% of these offspring would have the recessive green p-pods. We could write this as one-to-one yellow green phenotypic ratio. Let's look at another example. In the Cystic Vibrosis video we've discovered that Cystic Vibrosis is caused by a mutation on a recessive gene. You can be a carrier but not suffer from the condition. Let's have a look at the probability of offspring being Cystic Vibrosis sufferers when two carrier parents with the CC genotype mate. Can you complete the Punnett square? Pause the video, work it out and click play when you're ready. Did you get it right? So there is a 25% chance that the offspring of two carrier parents will be sufferers of Cystic Vibrosis. We can represent this in a one-to-one ratio. See how easy Punnett squares are for showing probability of offspring genotypes? But remember these are just probabilities. Recall these the expected results. So whilst we could expect a 25% of offspring of two Cystic Vibrosis carriers to suffer from the disease it is a probability. The actual results may be different. Two carrier parents could potentially have four children that are all non-carrier CC genotypes. Before we finish maybe some of you have been wondering why we are allowed to just split up the parental genotype into two separate letters. This is because during meiosis the cell division that produces sperm and egg cells the chromosomes are separated into two halves. So the dominant C is pulled one way to form one sperm or egg cell and the recessive C of the other chromosome is pulled the other way to form a different sperm or egg cell. So half the sex cells will have the dominant C and the other half will have the recessive C. So this C represents half of the sex cells and this C represents the other half of the sex cells. So that is Punnett squares very useful for looking at the probability of genotypes of offspring. We will look at co-dominants and sex determination in different videos.


Smells like army Yes, army's 13-year losing streak finally comes to an end It treat her stuff that cadet alert alert army West Point Truder alert Get the black knight off my ship now web Shoot to three shoot to three I sir from USS Tennessee. Here's the 13 more years Go Navy be an army


So tell me more about the leadership retreat. When was it? What outcomes were you looking for? What challenges did you encounter? I just got back from the retreat. It was very successful and there were positive team outcomes. I am confident that I could replicate the same for your company as this is not the first retreat for which I've been leader. And a challenge? It was difficult to get buy-in at first, but I have Myers-Briggs training, so I understand people and how they best work together in teams and as individuals. Great. Thank you so much. Do you have any questions for me? Just one. What is the time frame for the final decision? You will hear from us within 10 days. We are vetting final candidates. Thank you so much for this opportunity to come in. I look forward to hearing from you. I think she would be perfect actually. Her skills and interests align with our goals and she will bring exceptional experience that we currently don't have on the team. She also strikes me as a very proactive and positive worker. I have to check her references, but first I want to do a quick virtual scan. Let's make sure we aren't hiring someone who is wanted for some crime. Let me Google her. She probably has a LinkedIn profile. Let's see if she has been endorsed for any special skills. Okay, no LinkedIn. Well, that's a little strange, but she does have a Twitter account. Let's see what she says and who she follows. Okay, looks like she's pretty confident that she's getting this position. Well, looks like our search continues. Had this person been more vigilant about her online persona, she might in fact have had a new position. Perhaps in the future she will be more savvy about the connections between her personal life and her public social persona, and she will reflect on how she wishes to appear to others, most especially prospective employers. Where did she go wrong? What different decisions might she have made to accomplish her goal of getting this job? In this module, we will explore issues and considerations related to online persona and personal privacy, both key components to one's social identity.


So, I won't start with the obvious question, who's heard of PostgreSQL? But I will ask, who's ever written a patch for PostgreSQL anywhere on your laptop? It doesn't need to have been submitted. Who's ever edited the PostgreSQL source code for any reason? Come on, Peter, I know you have. One, two, three, four, is that? Is that time? Did you get started? Cool. OK, my name's Simon Riggs, and I'm going to talk to you today about PostgreSQL's development. The PostgreSQL project is an advanced database server. I hope you got that bit. It's open source licensed, which means a couple of things. It means you can use the code, but it also means you can contribute the code back. And that's one of the things that I'm going to talk to you about today is contributing back. So, it's been around for 25-plus years. Different people count the start date from different times, just to confuse us. But it's been around for more than 20 years, right, so a long time. Now, sometime in the past, we only had a couple of hundred thousand lines of code, but it's actually been going up by quite a considerable number of lines of code every release, so that now we have more than a million lines of code in the PostgreSQL repository. It is written in highly portable C. Now, it's also very structured C, so if you're used to some modern languages where you're used to lots of libraries and structure, then that's exactly what you're going to get from PostgreSQL. There's an open engineering process, but it's also a very carefully managed development process. This is a slide that I'll return to a couple of times. New contributors are very welcome. The purpose of me giving this talk today is to encourage you, all of you, to contribute in some way to the development of PostgreSQL. Why? Because software that is not maintained is dead software. So everybody needs to contribute to that to make sure that it stays alive. Now, you might think that there are so many contributors and so many developers that you personally couldn't possibly make a difference. Well, Peter over there has been contributing longer than I have. I've been contributing for 12 years and it's been mostly a very happy process. I've got a lot out of it personally. What I would point out is that not all of the people that have been contributing contribute for that length of time. In fact, it's a bit like the NFL in that there's an awful lot of people passing through and only a couple of guys staying there for like a decade. So we definitely need your support. There are some senior people that have stopped contributing over time and you need to get involved. I can admit to being slightly over 50. So I think you can do the math on that and work out how many years it's going to be before I stop contributing. So there needs to be people coming in at the bottom end to make that work. Peter's sitting there thinking, that's fine. I've got like 80 years left before I'm that old. So one of the things that I'm going to talk about is what the development process is. And you may have heard or there may be sort of stories in the bar about it being very difficult to get a patch accepted into postgres. And well, you know, yes, it is. But that's because people don't really understand that it's a complex process. And it's not easy to just simply contribute to that. It's the same thing as saying how many people have submitted a successful law through Congress. It's not many. But does that mean that new laws are not worth trying to pass? Well, perhaps a different subject, but hopefully contributing to postgres is a lot easier than getting a bill through Congress. If you are just interested in being a developer, that's easy. Because all you need to do is type git clone and then the address. And then you can clone that onto your laptop. If you don't know what a command line is, then I think we're probably going to lose each other over the course of this talk somewhat. Then all you need to do is edit one of the files and do the normal dance of configure make making still. Now that makes you a developer. That does not make you a contributor. A contributor is somebody that actually takes something that they've done and gives it to the postgres project. So there's a lot of people that are developers and not as many that are contributors. So what I'd like you to be is a contributor. Now, if you don't want to give back to the project, you can publish your work under some paper and submit it to some conference or other. And you can get a patent or just merely a publication. So there's lots of people in the world that contribute research, but don't actually publish that back to the postgresql project. Now, there are also some that do. So the research projects on the left there, the Axel forecast and SPGIST projects are examples of research projects that were funded by governments and have put work back into the postgres project. And then the ones on the right, and I'm not meaning to be petty about it, but just to say that there's a lot of research going on that doesn't necessarily flow directly back into the project, but it's contributing ideas. And it also means that postgres is one of the most popular projects in the world for doing database research because the source code is easily accessible and it actually makes sense. So one of the most recent ones was the Axel project, analytics on extremely large data. We contributed features for security, performance and things like visual analytics. There's also a lot of development funded by companies and that's all different types of companies. Obviously, I haven't listed every single company on there. Some are not there because I've just missed them off. Some are not there because they don't actually contribute. And so what I would like to mention to you is please do check that the people that you're working with actually contribute to the project rather than just claim to be experts in the particular topic that we're discussing. Some companies like NTT, the largest telecommunications company in the world, contribute very significantly to postgresql development. And then there's lots of other different types of companies involved as well. Who controls the project? Well, some people might lead you to believe that they control the project or in fact it's controlled by a single company or entity. But the truth is it's a multi-company, multinational project where people literally from all over the world contribute. So if you go to Japan, there's a very large user group there with full-time staff members that they're employed by the user group to organize things. And actually, it's a bigger organization than it is in America or Europe even. So there's actually a very wide interest in postgresql wherever you go in the world, whether that be South America or Europe or lots of different places. One of the difficulties for people to understand is when you say it's an international project, some people just don't kind of get what that means. That means like, oh right, so the docs are in Italian or something, right? No, no, if you go to Italy, there's a huge user group in Italy and in Japan and there's lots of interest. There's people submitting translations, there's people submitting patches. There's a lot of activity all across the world. There are, of course, alternate versions of postgres and sometimes that work has been forked and sometimes that's been spooned, meaning that there's some sort of tracking release going on. I'm very pleased to note that there is not a major fork of postgres in an open-source sense. Some other open-source communities have managed to sort of split up over time and go lots of different ways and actually I'm very happy to say that the postgres project has always managed to avoid such difficulties and even though there are sometimes difficulties and disagreements, there are major strides taken to keep the community together so that there is just a single postgres QL that everybody uses and that actually is one of the greatest strengths, I think, that the project has is its community spirit and its teamwork. So how is the project organized? Well, almost, well I'll say, all of the commits to the code repository are managed by a small group of people, the committers. Now in some software projects, you get a very large number of committers involved and in fact it's kind of a low-level badge of merit to become a committer in some projects. Now the postgres project, unashamedly, does not do that. It does take a long time to become a committer and to some people that is a negative aspect Well what we think is that it's difficult to write and maintain the database software in the shape it is now and actually it requires significant experience, both experience in terms of work as a team but in terms of code contributions. So actually the status of committer is not granted easily to people. Now that does not mean that the people that contribute their time or the code to postgres are not worthy of merit and actually there's a separate merit system in postgres where contributors are mentioned on a specific web page and we go to great lengths to put individual credits in particular places so if you do manage to get a patch accepted it will have your name against it for all time in the release notes. So the project does go out of its way to recognise people. Now that's a good point because there's unfortunately always a factor involved in contributing in the thought that is somebody contributing just simply to get their name in lights and if that's the thought that's motivating you it's probably going to lead to quite a shallow contribution and as a result is probably going to lead to your work getting rejected. So if you make a full and deep contribution then it will more likely be accepted. In fact I remember Peter's probably going to say, why do you keep mentioning my name? But my first patch I thought this is brilliant, it really works, it does everything and then actually it was Peter that came along and said well you should design it like this and it should have all these bits hanging off it and I was like oh no I can't possibly do that so I spent a week sort of deciding whether to commit suicide or not because my work had been criticised publicly and all this kind of stuff and actually what you've got to realise is that it's absolutely nothing of that sort there is always discussion about any idea that comes forth and whatever you thought about your own ideas or contributions there's always something somebody else can do to add to them to make them better and it does require a little bit of patience perhaps maturity to see that that's the case okay and I don't mind saying there's nothing I've thought of in this project that hasn't been improved by somebody else saying some comment I've never ever contributed something and it just went straight through without improvement obviously it went through, it receives many comments but I'm saying I never had an idea that was not improved through feedback from somebody else okay now that is a stark difference to the way that things work in companies where almost all of the code that you write is never inspected by anybody your boss just says did you finish it yet and he doesn't bother to like check the code or even you know doesn't hardly check with the QI guide to see whether it works you know it's just did you finish it yeah so the post-credits process is significantly different from most software development and that's one of the reasons why it's harder to get your code accepted because we've got very high quality standards and if you're not used to that and I have to say when I arrived I was not used to that then you will falter you're gonna have a big problem with the process but if you understand that it's a peer review process where people are actually going to look at every single line of code that you wrote and comment on it and they've got good comments because these are clever people now why are they clever people if you're a post-credits developer does that make you clever no that's not true at all it's just that all across the world there's a lot of people involved and so just by by chance by randomness you're going to get a load of people many of them will be cleverer than you okay that's the way the world works so don't be put off when you get lots of comments back on your code it's part of the process and it is well intended okay and if you play with that correctly it's going to end up both a good thing for you and a good thing for the project so let's have a look more about the way that the project's organized because if you understand the way the project's organized then you're going to stand a better chance of getting your contributions accepted and I'll say again we definitely want your contributions so there's a release team, a security team, a server monitoring and management team and a core team that provides oversight now the the main process for code contributions is something called a commit fest which I will talk about in terms of the actual release process so the the current stable release is 9.5.2 the current release that's in development is 9.6 we work to approximately an annual release cycle and each release takes just slightly longer than that because we overlap the releases slightly there's a maintenance release every three months and maintenance releases may be triggered by extreme bugs so if you're developing something you need to develop it in a way that follows the process but also in a way that follows the timing of the release itself if you submit something a week after freeze it's going to wait a whole year okay and as some people do they think oh well that's fine I'll submit it the week before freeze and then it gets rejected and then they have to wait a whole year okay so if you're too clever with the process then you're going to wait around a long time so the thing to do is engage with the process work with it in its timing and then you'll be successful so the development schedule is we tend to have a meeting once a year where we chew through a bunch of stuff and then we have these things called commit fest that are incremental phases within the development cycle so each year we'll get this cycle happening and now we each year we tweak it slightly but it's kind of roughly this each time so there's four commit fest and then we move through the main part of the release process each commit fest has a separate commit fest manager and then there's a release management team that guides us through the remainder of the process some of these things are new some of these things have been around for a while so what the hell is a commit fest well what we used to do in the old days was there's a big queue of patches and people took stuff out of the patch queue and committed it and what we found was that if well for whatever reason your patch may have lingered on the patch queue for like two years without anyone looking at it so what we decided was that we were gonna have a process where we would collect all the patches and then every commit fest we'd clear the whole patch queue okay now that's almost successful but there are some things that get pushed back from commit fest to commit fest where people can't really decide what to do so we do our best to clear things each commit fest so everybody likes to develop their own code and yet all code must be reviewed prior to commit so obviously the math is that if everything needs to be reviewed then every code contribution needs to be matched by one review contribution so if you wish to be a code contributor you need to understand that you probably need to commit as much review time as you do development time okay that's the math now some people get it some people don't now early developers don't tend to get it but I'm trying to get the message through to everybody that that's what you need to do so if you submit a patch you probably need to review somebody else's patch as well okay quid pro quo now the commit fest manager will run the process smoothly and they do so via a commit fest application which is shown there now I think over time some developers move through to a greater number of reviews and basically after quite a long period of doing just development I've kind of realized that I needed to change my game and I've started doing a lot more reviews now rather than a lot of personal development not because I wasn't successful at development it's just that the situation is that we need reviews just as much as we need development and there's unfortunately a lot of people that are focused on the development side of things so we need more review so the development statistics are both encouraging and discouraging we have four commit fests a year which means that we have around 250 features or patches in every release now you may say well how do I judge against that well Oracle was heard to say that they had 300 features in their latest release now the good news is that that means that if Oracle release every other year and we're doing 250 features a year then PostgreSQL has got 200 features per release now that'll be 100 features per release more than Oracle does on average so PostgreSQL is developing more features each release than Oracle is so that's a good number to take to people when you say how is it that the PostgreSQL project can be doing what it's doing it's because we've got a lot of people involved and they're contributing a lot of work but that doesn't mean we don't need more okay so we could actually accelerate this process if we had more reviewers and more contributors now I am happy to say that the 9.6 release has been better than normal and we've had a lot more reviews so we actually got more through in this release now the success rate for patches depends we can discuss exactly what we mean by success but what I can say to you is that if you submit a patch and you expect that patch to get committed you have got approximately 0% chance of that being true almost every single patch goes through about 3 different versions and if it's a big feature you can expect it to go 10, 20, in some cases 30 versions of the patch before it finally gets committed and some features have taken more than 5 years to get through the process completely so some might say that's hard some might say that's a hard quality bar but the point is everybody appreciates ProScres' quality and its feature set and it's hard to come up with something that is genuinely worthy of adding to that okay so please understand that so I don't know if I got through to you before but the project is looking for new contributors we welcome contributors who work for companies or whether you're doing it in your spare time it doesn't matter a lot of people started doing stuff in their spare time and then went into it more professionally there's an awful lot of people in the world that have got a few hours to spare and the skills to contribute so if you're a ProScres user you might consider helping out in one of the commit fests maybe you could persuade your boss to spend Friday afternoon doing some review work or some other kinds of contribution so we're looking for open source contributors of all different kinds one of the most valuable things you can do is submit good bug reports now I would guess that about 10% of bug reports would be classified as good good as in the sense of it mentioned a bug in a way that is reproducible by the developers if somebody said I had this funny error message and then it went away okay then thank you for that if you submit a script and all of the preconditions to recreate that bug then that means we can run that script and we can fix that bug straight away the analysis time for bug reports far outweighs the bug fixed time in most cases so it's actually when you pay a company for support you're mostly paying for the analysis time not necessarily the bug fixed time obviously you need skill to do the bug fix but you need time to do the analysis so if you submit a vague bug report to an open source list you can be pretty sure that nobody's going to spend a lot of time on that so people pay attention to people that submit good bug reports so if you're the type of person that submits a very vague bug report and then you argue with the person that's trying to help you you can be pretty much assured that you'll be ignored the next time you submit a bug report so please contribute bug reports they're particularly important they're almost as important as new feature designs and requests but new feature requests are useful and good if you're thinking about them in a particular way I remember that somebody submitted a bug report once that they wanted an un-vacuum and I remember we all laughed and there was lots of joking around on the thread and then somebody else phrased not on that thread but on a separate thread they said what we'd actually like to be able to do is roll the database system back to a previous point in time so that we could recover missing data and when it's phrased in that way it's the same feature request but one feature request got rounds of laughter and the other one came out with a that's a good idea we should think about doing that so if you spend some time thinking about what your feature request is think about some of the problems and then describe it neatly to people then you'll actually get a lot more traction then obviously the guy that submitted the un-vacuum request probably left thinking that you know the Postgres project was full of ignorant savages that laughed at his brilliant ideas who knows, anyway so the project structure is that there's a core to Postgres but then there's lots of other projects surrounding it so it may be that if you wanted to contribute you could contribute via one of the drivers or perhaps by writing an extension and in many cases that's a lot easier to do so some people decide that they want to be a core code contributor and really that's in some sense is the wrong way round you need to have a feature that you'd like to build before you can become a contributor in a particular area so you need to know where you're going to discuss things because all of the open source development happens on mailing lists some of it happens person to person but most of it is happening in the public eye on particular mailing lists so you need to go to particular places now if you turn upon hackers asking for help with a SQL command you will be mostly ignored and told to go somewhere else if you come up and start discussing code then people will be interested in what you're saying and will listen and you can progress from there so there's lots of different places to discuss what it is you want to talk about so please try to get that right in the sense of if you go to the wrong place and discuss a great idea you may well be ignored whereas if you discuss it in the correct place people may well listen all of the contributions to Postgres are tested on a build farm so one of the things that you need to appreciate is that any code you do write will need to be portable and if you say well I can't be bothered to make it work on Windows then that's probably going to lead to an automatic rejection of your ideas so you need to be even-handed open and aware that things need to run in lots of different ways the test coverage of Postgres is not all green as you can see so one of the contributions that you might make is submitting new tests that allow us to test different parts of the server you might also learn from this which parts of the code are new which types of things are worthy of being extended anyway some further information if you want to submit a patch make sure you discuss it in the right place discuss it on hackers first if you write a patch first and then submit it that's usually considered the wrong way around and it's got more chance of rejection depends how you phrase it if you say I've put together this prototype of an idea I wonder if anybody's interested in this direction of research and thinking could we could you help me to make this into a full feature then you may get some traction with that you need to basically to agree away forwards with uh... interested people and that could be anybody some developers have been there a long time and some people think that you need to get agreement from certain developers before you can move forwards uh... obviously you need to get agreement from everybody before you can move forwards it's what we call the consensus process so there isn't agreement from a small number of people you need to get a agreement from everybody now obviously that means you need to approach things uh... in uh... an open way uh... but you also need to explain things to people if you are incredibly terse in your descriptions of how your new feature works or or you or you just simply don't give enough information then you'll get no interest and effectively your patch will be ignored so uh... assuming you get interest in your ideas and you show a willingness to develop those ideas then you can write them and publish and you will then need to wait for review uh... and uh... as i've said there uh... you need to uh... be prepared to be very patient patience does not mean sitting there waiting for two years without a reply what i mean is uh... if somebody appears in your mind that they are hostile to your ideas bearing mind that it may be just in your mind because somebody has written down on an email that will not be possible that does not mean uh... actually i personally dislike you i think most of what you've said is rubbish and i never want to see you again it doesn't mean that at all in many cases you could imagine that the person saying that has a kind of kindly interest in what you're doing and he's kind of leaning over you and going yeah i like your ideas but that's probably not going to work and if you imagine that that the responses you get are helpful and interested you will then respond to them well and come forward with new versions you can usually tell how old somebody is by uh... the hostility of their reply and uh... sometimes you know uh... even minor comments are perceived as as negative uh... which is a shame you will need to listen to what people say about your work uh... and the other way around is you need to give good review criticism now it's you know it's easier said than done and sometimes people phrase things in a way that uh... leads to hostility uh... but that's okay you know it's so it's it's easy to apologize if you said the wrong wrong thing some people find apology uh... particularly difficult but actually it's quite easy you just say i'm sorry did did we misunderstand each other there let's let's move on uh... developing the changes is the easy bit you can do that on planes trains and at home uh... so there's always time for development if you're the sort of developer that uh... considers compilation and testing uh... to be uh... you know a slight nuisance uh... actually i've hacked up a good patch uh... but i haven't yet complied it to see if it will work uh... then obviously our relationship is not going to go far please think about validating your changes uh... you need to include documentation anybody that believes that's documentation is the thing that you do last in a software project has not written good software because if you write down what the software is supposed to do okay that means that all of the people that you're working with read that uh... commentary understand what it's doing they can comment on the design and they can also help you test it if you haven't written anything down about your patch it's extremely difficult to i work out what it's supposed to do uh... how it does it uh... see uh... can we test it to check that it does that even though i say that we all laugh regularly every commit first i read about five or ten patches i'm trying to work out what the patch actually does and i can't work it out you know there's like pages of code sometimes there's comments in the code you know saying like well i was time to call that function or but there's nothing in there says why did you do it this way you know why did you write the code like that why didn't you add it to the jdbc driver for example an explanation of why you added that code there an explanation of what this thing actually does why are you trying to do it i know that it might be crystal clear to you that you say like oh well uh... this adds some catalog function or something yes but why you know what is the purpose of what you're trying to achieve so one of the biggest uh... and most stupid ways that you can get your patch rejected is to design something write it but then just describe the design so poorly that we go about six months batting the patch backwards and forwards before somebody goes oh you're trying to do that we already do that over here and then your patches rejected instantly on the basis of its uh... duplicate functionality now that's just a stupid waste of your time so please don't do that uh... it's not because the post-credits project is particularly anti new contributions far from it we want that contribution but you need to help other people understand that your contribution is a good one so uh... over the development uh... of your patch uh... you will need to uh... maintain different versions of that patch and i recommend that you use some form of repository uh... for that development because very probably at some point you will extend your work and then go oh no that's rubbish i have to backtrack okay so the likelihood that you're going to reuse some of the work that you uh... did earlier in your project is quite high uh... so make sure you keep all the different versions of your code uh... and also make sure that uh... other people have got access to those other versions because somebody might see your new version say actually now that you've done that i can see the way you had it originally was actually the best way please could you go back to version fourteen uh... and uh... and use that use the code from there uh... obviously other people are developing things at the same time so uh... you're going to need to rework your patch rebasing it uh... on a repo or changing it in many times in response to uh... to other commits in the uh... code base so a lot of the work these days happens by the commit fest application and uh... you will see that uh... you need to submit uh... a patch uh... the patch has a particular status and once it's been reviewed it may be passed back to you so it'll go through needs review back to waiting on author and then if people finally agree it will be set to ready for committer where a committer will then swoop in and uh... in a lot of cases they don't commit the patch because that may well be the first time uh... that one of the committers has seen that work and they may find problems that earlier reviewers did not see being set to ready for committer actually just a later stage in the process but it's still no guarantee that it's going to be accepted if your work does not have a reviewer find one don't just sit there and wait uh... for your work to be reviewed uh... you need to work with other developers and it's a common thing to do to swap reviews obviously if you're going to rate each other's work golden and uh... things like that then that is going to be spotted and uh... effectively there at those reviewed comments will be ignored so you need to work with somebody that's going to give you honest critical reviews the work that you're doing uh... and if you can't uh... find somebody you can write to people that you've not met say hello i'm working on a patch could you do this uh... unfortunately i do get quite a lot of emails saying could you mentor me as a developer and i have to say well i'm sorry i'm uh... fairly busy uh... so probably no uh... the patch review process is available on the wiki uh... there's lots of information on the wiki and you need to read it and understand it uh... there are various types of review that take place on a patch so you may think that just because somebody's gone through your patch line by line and found no problems with it but therefore it is ready for commit uh... one of the big things i find is that the design of things uh... is actually uh... fairly poor and that we wouldn't want to accept that particular design the code's great the design is not so there's various levels at which things could be accepted or rejected so have a read of that and understand the different types of review so overall uh... the development process is about achieving an end goal uh... we're interested in moving forward uh... the postgres project it is not a demonstration of your technical coding prowess this is not a step on your career ladder uh... it might become so uh... but if that's the approach that you're going to take then you're probably not going to be as successful uh... as if you actually think about the needs of other people uh... so what i can tell you is if you want to have something accepted uh... focus on real needs so that you've got a good problem analysis you've got a good design uh... for how you can solve that problem uh... and then good project management of your own contribution make sure you respond to people in a timely manner make sure you're responding in a uh... in a polite and useful manner so we're looking really for a brilliant execution so this is not some like uh... reality tv show some cookery uh... cookery show where like one person gets asked to leave the program after every commit fest it's not it's not a game right we're interested in improving the software and if you want to be a contributor we want you to be a you to be a contributor then it's possible for everybody to to get involved so think about the project management aspects of this as well uh... so if that that might mean that you need to plan to spend every monday night on it for three months or something in order to get it to flow correctly so so think about all of those aspects as well when you develop uh... something it's a database and as you might expect it's all about persistence it will take some time to get your work accepted but we want to see that work in postgres okay uh... i ask you uh... very much to pay forwards if you've used postgres if you've benefited from it in your company uh... or your career uh... then do something to contribute back to the project uh... that helped you in that way uh... and remember that uh... the postgres project is a team and uh... that we're interested in having everybody work together as a team in order to make solid contributions uh... to postgres ql so uh... i'm gonna leave you uh... now uh... gonna pass over in a minute to uh... peter that's gonna talk about testing uh... but just uh... the final message is really that myself and many other people would like to see your contributions to the project uh... and uh... if you can see your way clear to adding to the project in some way shape or form uh... that would be very much appreciated thank you very much


What is going on guys? Welcome back to the channel and I appreciate you being here. Today I bring this top 5 Destiny 2 WTF Moments of the Week. Now if you guys do enjoy the video, leaving a like really does help me out and if you have anything amazing to send in, send it into the email address linked within the top of the video description. Well let's get straight into this top 5 and in at number 5 we have a clip sent in by Bladings. Well if only this was legit people, look no eververse, no clip tuck, but as we know people this ain't ever going to happen. Let's stop dreaming here. In at number 4 we have a clip sent in by Goodsons2 who has found this shrawl who I believe is playing musical statues. What the heck is going on here? We have a clip sent in by Notterbutter1. Now we've heard of the leaked DLC details talking about new enemies called Frames, some people said it's fake, some people said it might be legit. But listen and I can actually hear Hawthorne here talking about Frames too. In at number 2 we have a clip sent in by Killer7BS. Haha, this dude got shot on by the Traveller. Amazing. In at number 1 we have a clip sent in by GianlucoF. Now this is how you beat a public event in 3 seconds. Damn! But guys that is the end of top 5 WTF moments of the week. If you enjoyed the video leave a like, rule does help me out. Thanks as always for stopping by and hopefully people I will see you on that next one.