Can i please get assistence on this question using language C

Question

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)

Answer 1

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;

Answer 2

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. 😉

Answer 3

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;

Answer 4

Josh Greig re: "tyre" vs "tire"

https://british-american-dictionary.com/bad-words/tyre-uk/

Answer 5

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. 😉👌

Answer 6

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.

Answer 7

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.

Answer 8

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.

Answer 9

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. 🙃