Re: [sqlite] How to Verify (know that they are correct) Queries in SQLite GUI Manager Firefox add-on?
The question is ambiguous does he want all the Fred Smiths or just a specific Fred Smith? For example, do we have a case of mistaken identity? How many Fred Smiths are out there? Or does he want All the records for a specific person named Fred Smith? If it is a specific person, is there a (unique) ID number for the person (a primary key)? If there is an ID number you can query for that specific number. On the other hand if they want all the "Fred Smiths"; do they want Frederick Smith or Fredrica Smith or some named Theodore Fredrick Smith, but goes by Fred? Hope this helps, Jim Callahan On Dec 7, 2014 9:24 PM, "Dwight Harvey" < dharv...@students.northweststate.edu> wrote: > I am a student with no Tech or IT background what so ever. > > I am new to Databases and IT in general. > > I am taking an accelerated class in database basics and within the last > three weeks I just learned what databases were. > > I know very little and Databases are complex and intimidating. > > I figured out how to run queries but I don't know if they are > correct/accurate, as in what I requested from the 'RUN' results? > > How do you 'VERIFY' your query results? > > > My instructor wants me to explain how do I KNOW that the records are > accurate. Here is an example of what is expected in the assignment... > > *VERIFICATION: *What is verification? Each time you retrieve data, you > should ask yourself, "How do I know I selected the correct data?". For > example, if you were asked to pull all records written by an author named > Fred Smith, your query might be based on last name equal to Smith. > However, if you might get records for someone with the first name of Fred, > Mary and Ginger. What would you do to insure you are pulling only Fred? > The person who has requested the data will always want assurance from you > that you are 100% positive you pulled the correct records. Look at the > records returned and always as yourself, did I pull the correct records? > How would I verify it? > > "Capture each query, number of records returned and *explain your > validation of the query.*" Example: > > /** First query 1. List all employees **/ > SELECT dbo.Firstname, dbo.Lastname > FROM dbo.employees > --records returned: 24 > *--Validation: I did a quick listing of top 200 records and 4 were > returned*. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to Verify (know that they are correct) Queries in SQLite GUI Manager Firefox add-on?
You didn't provide the data set, so it'll be a guess. You'll need to look at the data and do some mental work on comparing what you expect versus what your query result comes back with, and prove what is in the database is what your query is EXACTLY asking for. (Returning on last names of Smith?) In the case of your query, your table apparently has 24 records or rows. Your validation compared to the query is incorrect (Unless the exercise is to take the given validation result and you provide the full query, in which case, you WILL have to look at the raw data). You're validation statement should also show what you queried for. Saying you queried 200 records (Which can be true) and got 4 results doesn't show what the query is asking for, which isn't enough information. Things to look out for; - Assuming a larger table, if you queried for 200 rows, who's to say that row 201-205 didn't include your conditional? In this case, this validation statement IS valid, but since searching 200 of 24 rows is going to give you only 24 rows anyways, the other 176 rows don't exist. This statement WON'T be a valid check the moment you hit 201 rows unless you provide additional sorting and possibly conditionals. - What statement was used to return just 4 results? To try and ease the intimidation off, you can compare the Structured Query Language to act like pulling data out of any kind of spreadsheet. At the base of any database, the lowest level of usable and desirable information is stored in tables. Thinking of just a single table, each row going across is a group of data, or using HSL (Human Structured Language), a row in a table is comparable to being a sentence. Each field/cell/column is a piece of data relevant to that row in some way, or for HSL, a field/cell/column is a word in a sentence. At the most basic intro level, in SQL, the SELECT statement tells the engine "I want this data", WHERE says "But whatever is returned has to meet this criteria", and ORDER BY means "I want the results ordered by these conditions and/or values in the specified field". IE: SELECT dbo.Firstname, dbo.Lastname from dbo.Employees where dbo.Lastname='Smith' order by dbo.FirstName; (dbo = Microsoft stuffs. I'm assuming you're using SQL2k8 and this relates to Northwind? If this question doesn't makes sense to you, ignore my question) If you break down what a database is down to its elements, the concepts of what a database IS is really easy. Once you start building your own tables and inserting your own data from scratch, you'll start to see how elementary getting basic results are and how the pieces fit together. The tricky part is the knowing how to take what you've got in your head, seeing the results you want in your minds eye, and converting it to a standard language. At the level you're at, all SQL engines have the same syntax. Later on you're get into nuances of the language between engines, but you can treat that like to spoken languages like Polish and Ukrainian, or US English to UK English -- Pretty close to the same thing, but, different accents and words to get the same thing or idea. On a personal note, honestly, I do see that there is merit to being provided a database and then have students try to work with that data, but, sometimes to some people, that'd be like building a house without a foundation. The whole Microsoft Northwind database (At least back in SQL2K) is nice to play with, but being told "Here is your table, here's the language, get me results" doesn't jive for the people who don't see what a constitutes a table, cell, row, or column actually is. (Just for comedy relief) For me, when I have to think about different things, I sometimes THINK in SQL. So something like "When I was in Toronto last month, how many parking lots did I pass that cost more than $20/day?" and (sadly) I'll start seeing flashbacks of each parking lot I remember, and quite often the wife will have to slap me out of that coma due to so many results. A query like that would be [ select ParkingLotImage from Locations_Details_ParkingLot where Cost>20 and month(SeenDate)=month(date(now(),"-1 month")) and City="Toronto" ] -- And yes, SQL Fanatics, I can normalize that further to Locations.DetailID = Details.DetailsID and and have "ParkingLot" as a result in Details, but I've only got so much memory storage and get distracted easily, I'll EASILY get corrupted. ;) On Sun, Dec 7, 2014 at 9:24 PM, Dwight Harvey < dharv...@students.northweststate.edu> wrote: > I am a student with no Tech or IT background what so ever. > > I am new to Databases and IT in general. > > I am taking an accelerated class in database basics and within the last > three weeks I just learned what databases were. > > I know very little and Databases are complex and intimidating. > > I figured out how to run queries but I don't know if they are > correct/accurate, as in what I requested from the 'RUN' results? > > How do you 'VERIFY' your
Re: [sqlite] How to Verify (know that they are correct) Queries in SQLite GUI Manager Firefox add-on?
First, you have to "verify" that the database itself is sufficiently normalized and that it is anomoly free. This is a purely algebraic / mathematical operation and is a pre-requisite to any further verification. If the database is not properly normalized, or has other anomolous behaviour, then it may be quite impossible to proceed further. Second, you mathematically / algabraically verify that the query you have "asked" is in fact the question you were supposed to be asking (for example, as in your example, "select * from table where surname='Smith'" is an obviously incorrect phrasing of the problem "select the records where the surname is smith and the forename is fred" and cannot return the required result except by divine intervention) and that when it is applied as a transformation against the database model (verification 1) it has "no choice" but to return the desired result. Third, you can execute the query to test that the database implementation is working as it should and that the results returned are those that are expected given the data contained in the database and the query. This is the only step that requires anything more than pencil and paper. --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. >-Original Message- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of Dwight Harvey >Sent: Sunday, 7 December, 2014 19:25 >To: sqlite-users@sqlite.org >Subject: [sqlite] How to Verify (know that they are correct) Queries in >SQLite GUI Manager Firefox add-on? > >I am a student with no Tech or IT background what so ever. > >I am new to Databases and IT in general. > >I am taking an accelerated class in database basics and within the last >three weeks I just learned what databases were. > >I know very little and Databases are complex and intimidating. > >I figured out how to run queries but I don't know if they are >correct/accurate, as in what I requested from the 'RUN' results? > >How do you 'VERIFY' your query results? > > >My instructor wants me to explain how do I KNOW that the records are >accurate. Here is an example of what is expected in the assignment... > > *VERIFICATION: *What is verification? Each time you retrieve data, you >should ask yourself, "How do I know I selected the correct data?". For >example, if you were asked to pull all records written by an author named >Fred Smith, your query might be based on last name equal to Smith. >However, if you might get records for someone with the first name of >Fred, >Mary and Ginger. What would you do to insure you are pulling only Fred? >The person who has requested the data will always want assurance from you >that you are 100% positive you pulled the correct records. Look at the >records returned and always as yourself, did I pull the correct records? >How would I verify it? > >"Capture each query, number of records returned and *explain your >validation of the query.*" Example: > >/** First query 1. List all employees **/ >SELECT dbo.Firstname, dbo.Lastname >FROM dbo.employees >--records returned: 24 >*--Validation: I did a quick listing of top 200 records and 4 were >returned*. >___ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to Verify (know that they are correct) Queries in SQLite GUI Manager Firefox add-on?
On 8 Dec 2014, at 2:24am, Dwight Harveywrote: > I know very little and Databases are complex and intimidating. > > I figured out how to run queries but I don't know if they are > correct/accurate, as in what I requested from the 'RUN' results? > > How do you 'VERIFY' your query results? I bet if you read over your notes or your textbook you'll find that your instructor explained how to do this sometime during your instruction. But without knowing what you instructor intended you to do we might be able to take some guesses. If you could list every row of the tables in your query you could look down them yourself and see what you think the result of the query should be. So you can do that, just use SELECT * FROM dbo.employees Obviously in real life tables get huge and it's not practical to do this. That's why when you test your code you make up small dummy tables to check that things are working correctly. And you make up appropriate sample data to test two kinds of errors: false positive: returning a row you didn't mean to return false negative: not returning a row you should have returned Good luck with your course. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to Verify (know that they are correct) Queries in SQLite GUI Manager Firefox add-on?
I am a student with no Tech or IT background what so ever. I am new to Databases and IT in general. I am taking an accelerated class in database basics and within the last three weeks I just learned what databases were. I know very little and Databases are complex and intimidating. I figured out how to run queries but I don't know if they are correct/accurate, as in what I requested from the 'RUN' results? How do you 'VERIFY' your query results? My instructor wants me to explain how do I KNOW that the records are accurate. Here is an example of what is expected in the assignment... *VERIFICATION: *What is verification? Each time you retrieve data, you should ask yourself, "How do I know I selected the correct data?". For example, if you were asked to pull all records written by an author named Fred Smith, your query might be based on last name equal to Smith. However, if you might get records for someone with the first name of Fred, Mary and Ginger. What would you do to insure you are pulling only Fred? The person who has requested the data will always want assurance from you that you are 100% positive you pulled the correct records. Look at the records returned and always as yourself, did I pull the correct records? How would I verify it? "Capture each query, number of records returned and *explain your validation of the query.*" Example: /** First query 1. List all employees **/ SELECT dbo.Firstname, dbo.Lastname FROM dbo.employees --records returned: 24 *--Validation: I did a quick listing of top 200 records and 4 were returned*. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Artificially slow VACUUM by injecting a sleep() somewhere?
On 8 Dec 2014, at 12:43am, David Barrettwrote: > Other alternatives we're considering are to fork and nice the process, or > to call ioprio_set() directly, but I'm curious if there's a simpler way to > do it. Thanks! VACUUM does the same job (in a very different way) as copying all the TABLEs, then creating the VIEWS, INDEXes and TRIGGERs on the new tables. These can all be done using SQL statements. Had you considered creating a VACUUMed copy yourself ? You could do one table/view/index/trigger at a time. And you could engineer a pause of a few seconds after every ten thousand rows are put in a table. But I'm wondering why you need to VACUUM often enough that anything it does is a problem. It can save filespace after deletion (before new data is put in to take up the released filespace), and it can increase speed, but the speed increase is small. It's not needed in normal use. It should be kept for a maintenance routine, perhaps once a month at most. If your users are putting in more data than they are deleting, VACUUM has no noticable effect and I know of SQLite databases which have been amended daily for years without ever once having been VACUUMed. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Artificially slow VACUUM by injecting a sleep() somewhere?
Hi! I have a large database, and running the VACUUM pragma hammers disk IO so badly that it dramatically affects performance on the box. I'm not in a hurry for the results, so I'm curious if you can think of a way using the API (or any other way) to essentially "nice" the process by inserting a short "sleep" into whatever loop runs inside the VACUUM command. (I had initially thought of putting a sleep() into the callback to sqlite3_exec() callback, but that's only called once for each result row -- I need something that's called consistently through the running of the query.) Other alternatives we're considering are to fork and nice the process, or to call ioprio_set() directly, but I'm curious if there's a simpler way to do it. Thanks! -david ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users