+ 1

Can i please get assistence on this question using language C

Using the database table declaration at the end Write a database sql select statement to return the TOTAL NUMBER OF TYRES that have NEVER been on a vehicle (ie. not location = 2)

7th Apr 2021, 7:10 AM
Andile
9 Answers
+ 3
Josh Greig I agree with your approach as well. I would only point out that I tend to recommend the ISO standard version of the not equals operator (<>) over the non-ISO version (!=). Although, I'm certain I use (!=) more often than I realize. 😉 I'd also recommend using an alias for the column containing the count results. Also, not that it matters... I was surprised to see: count(1) ... rather than the following equivalent: count(*) While these are identical in both function and performance, I don't recall having noticed the former used since before 2005. So... seeing it here really stood out for me. I'm curious if count(1) is actually more common than I might be aware. Perhaps there are a some databases I use less frequently where count(1) is actually preferred over count(*). Anyway... here's my equivalent alternative query: SELECT COUNT(*) AS Total_Unused FROM tyres WHERE location <> 2;
7th Apr 2021, 6:29 PM
David Carroll
David Carroll - avatar
+ 3
Brian Here's a link to a comment on a relevant article you might find interesting: http://sql.standout-dev.com/2017/01/count-vs-count1-again/#comment-119 This, and various other sources I've reviewed since my last post, have similar references that indicate count(constant) [ie count(1)] was introduced at some point to address the "mythic" concerns developers had about the performance of count(*). I do recall personally thinking count(*) was less performant early on as well until getting clarifications from much more experienced developers back then. Keep in mind... this was when SQL-92 was the modern standard of the day for DBs. Surprisingly... much of that version from 30 years ago is still predominant in modern SQL today. Now I'm just rambling. 😉
8th Apr 2021, 11:37 PM
David Carroll
David Carroll - avatar
+ 2
You mention the c programming language but a lot of your question is related to SQL. Mentioning c was a mistake, right? When you write tyres, you mean "tires", right? Tires can be on a vehicle. For your SQL question, what do your tables look like? What are the names of your tables? What fields are on the tables? Here is a blind guess in case it clarifies my previous question about table structure. Assuming you had a table called tires with a field called location, you could write this select query which counts the number of records that don't have a location equal to 2: select count(1) from tires where location <> 2;
7th Apr 2021, 9:34 AM
Josh Greig
Josh Greig - avatar
7th Apr 2021, 3:19 PM
Brian
Brian - avatar
+ 1
re: Tyre is British English... Brian Mah mynd is blown! 🤯😱😂 As an American English native speaker from the USA, I consider myself as someone who has had decent exposure to American / British English variants. So much so, I've also been able to identify words that seem to be Indian English specific as opposed to assuming it was British English. That said... I never would have considered American / British English variants for the word "tire" / "tyre". It's like... I know nothing and now must question just how little I actually know about English variants, or anything for that matter. 🤪🙃 Thanks for pointing this bit of information out. 😉👌
7th Apr 2021, 5:46 PM
David Carroll
David Carroll - avatar
+ 1
David Carroll yah, same for me regarding language background, and more-so. My family has deep roots in America, from before the time of Noah Webster's version of American English spelling. But I have an advantage in recognizing differences from across the pond: my brother-in-law is British.
7th Apr 2021, 6:23 PM
Brian
Brian - avatar
+ 1
I replaced the != with <> as David Carroll suggested to follow the ISO standard more. Yeah maybe tyre is correctly spelled then. Andile should be able to clear it up if he/she gives more information about the table structure and confirms that c was a mistake.
7th Apr 2021, 9:28 PM
Josh Greig
Josh Greig - avatar
+ 1
David Carroll back when query optimizers [optimisers (Brit.)] were rare, using count(*) was much slower than count(1) because the * triggered an unnecessary retrieval of all fields in each record before performing the count, versus returning a smaller dataset of constant 1s to count. I believe Oracle was the first to optimize this type of query automatically.
8th Apr 2021, 4:28 PM
Brian
Brian - avatar
+ 1
Brian Interesting... I'll defer to your knowledge as my memory from a couple decades back aren't as clear for me. 😉 That said... it was my understanding that, long before I started, devs might have been confused about the cardinality in: count(*). Since count(columnA) would return the count of non null values in that column, people thought count(*) checked all columns. However, this wouldn't be consistent with the results as all rows were always counted regardless of containing all NULL values. However, based on my vague recollection, count(*) was always designed to indicate returning all rows regardless of null state. As such, there would be no perf differences between a table of 2 million rows with 500 nullable varchar nonindexed columns and a table with the same count containing a single non nullable int column with a unique constraint. This was something I always thought was long understood by the time I started learning about this back in the mid 90s. Now... I'll have to research this further. 🙃
8th Apr 2021, 8:11 PM
David Carroll
David Carroll - avatar