Easy SQL

Assignments TABLE CREATE TABLE STUDIES (     PNAME VARCHAR(500),     INSTITUTE VARCHAR(500),     COURSE VARCHAR(500),     --COURSE_FEE decimal (10,2) ----------or COURSE_FEE int ); INSERT INTO STUDIES (PNAME, INSTITUTE, COURSE, COURSE_FEE) VALUES     ('ANAND', 'SABHARI', 'PGDCA', 4500),     ('ALTAF', 'COIT', 'DCA', 7200),     ('JULIANA', 'BDPS', 'MCA', 22000),     ('KAMALA', 'PRAGATHI', 'DCA', 5000),     ('MARY', 'SABHARI', 'PGDCA', 4500),     ('NELSON', 'PRAGATHI', 'DAP', 6200),     ('PATRICK', 'PRAGATHI', 'DCAP', 5200),     ('QADIR', 'APPLE', 'HDCA', 14000),     ('RAMESH', 'SABHARI', 'PGDCA', 4500),     ('REBECCA', 'BRILLIANT', 'DCAP', 11000),     ('REMITHA', 'BDPS', 'DCS', 6000),     ('REVATHI', 'SABHARI...

 Q.  What is the difference between count(0), count(1), and count(*) in MySQL/SQL? Ans.  The difference in “count(<whatever>)” is mostly if you have count(<fixed value>) versus count(<expression>)   select count(<fixed value>) - as long as it isn’t a literal  NULL  - ends up being the same  as count(*) .  And no, 0 isn’t special; select count(0) is the same as select count(1). select count(<expression>)  is actually interesting. It adds 1 for each row where <expression> evaluates to non- NULL , and 0 otherwise. So, you can do the following: select count(somecol) from sometab;   returns the total count of records where somecol IS NOT NULL select count(if(somecol > 50, 1, NULL)) from sometab;   returns the total count of records where somecol is > 50 The above may seem silly, until you realize that you can use count() multiple times, ie select count(if(somecol > 50, 1, NULL)),   ...

Q.  What is the SQL query to find the count of records in a table that have a NULL value in a column? Ans.  If you want to find this in any specific column, it’s pretty straightforward… select count(*) from sometab where somecol IS NULL; or select count(somecol) from sometab; In the latter, each record that has a somecol that is non-NULL will add one to the count, or zero otherwise. If you want to find a count of records where _any_ column is NULL, unfortunately there aren’t any ways that avoid just listing the columns and OR IS NULL stuff (or something logically similar), ie select count(*) from sometab where col1 IS NULL or col2 IS NULL or ... or colN IS NULL;