SQL BIT column must be blank vs TRUE on output, NULL vs 1 on input

Question

This is what my table needs to look like:

MovieID  Title            YearReleased  RatingID  BW     Subtitles
1        Quiet Man, The   1952          1       
2        McLintock!       1963          3       
3        Thin Man, The    1934          0         TRUE  
4        Tampopo          1985          4                TRUE

This is the code I have so far:

CREATE TABLE MOVIE
(MOVIE_ID VARCHAR (2) PRIMARY KEY,
TITLE VARCHAR(20),
YEAR_RELEASED DATE,
RATING_ID VARCHAR(1),
BW BIT,
SUBTITLES BIT);

INSERT INTO MOVIE
VALUES 
(1,'Quiet Man, The',1952,'1',NULL,NULL),        
(2,'McLintock!',1963,'3',NULL,NULL),    
(3,'Thin Man,The',1934,'0',(1),NULL),
(4,'Tampopo',1985,'4',NULL,(1));

I don't understand how to insert the data for a blank spot and the true values. :\ What I get is something that looks like this:

MovieID     Title             YearReleased RatingID BW      Subtitles
    1       Quiet Man, The    1952         1        NULL    NULL
    2       McLintock!        1963         3        NULL    NULL
    3       Thin Man, The     1934         0        1       Null
    4       Tampopo           1985         4        NULL    1

My professor told me to go online and find the answer because it's there. -_-

are you working on MySql or Oracle Sql? for MySql follow [here](http://stackoverflow.com/questions/289727/which-mysql-datatype-to-use-for-storing-boolean-values) — saikumarm, Jan 29 '15 at 08:19
in the last table where there are nulls need to be blank and the 1 need to say tru i don't get how to write the code to make it look like that — MICHAEL TURKIELA, Jan 29 '15 at 08:20
A table is one thing and a query result is another. What was your assignment? Do you have a choice about the table definition, the table contents, the update expression and/or a final query? What were you given and what were you supposed to do? — philipxy, Jan 30 '15 at 07:23

Mahmoud Gamal · Accepted Answer · 2015-01-29T08:42:27.890

Nothing wrong, in your query to display the values use the CASE statement to replace the NULL values with empty string, 0 with False, and 1 with true. Something like

SELECT MOVIE_ID,
  Title,
  YEAR_RELEASED,
  RATING_ID,
  CASE 
    WHEN BW IS NULL THEN ' ' 
    WHEN BW = 1     THEN 'TRUE'
    WHEN BW = 0     THEN 'FALSE' 
  END AS BW,
  CASE
    WHEN Subtitles IS NULL THEN ' '
    WHEN Subtitles = 1     THEN 'TRUE'
    WHEN Subtitles = 0     THEN 'FALSE' 
  END AS Subtitles
FROM Movie;

SQL Fiddle Demo

This will give you:

| MOVIE_ID |          TITLE | YEAR_RELEASED | RATING_ID |   BW | SUBTITLES |
|----------|----------------|---------------|-----------|------|-----------|
|        1 | Quiet Man, The |          1952 |         1 |      |           |
|        2 |     McLintock! |          1963 |         3 |      |           |
|        3 |   Thin Man,The |          1934 |         0 | TRUE |           |
|        4 |        Tampopo |          1985 |         4 |      |      TRUE |

You can't insert it as blank string as the column data type is bit.

Note that:

NULL is different from the empty string.
False values are entered as 0 in the bit column data type.
You can't enter the values 1952, 1963 etc in a DATE data type column, instead use the YEAR data type as in the sql fiddle demo.

No dbms specified, so I'd recommend COALESCE instead of ISNULL. — jarlh, Jan 29 '15 at 08:28
@jarlh - the user just decided it is Mysql, I updated my answer. Thanks — Mahmoud Gamal, Jan 29 '15 at 08:47

score -1 · Answer 2 · answered Jan 29 '15 at 08:19

Is this what you looking for?

INSERT INTO MOVIE
VALUES 
(1,'Quiet Man, The',1952,'1','',''),        
(2,'McLintock!',1963,'3','',''),    
(3,'Thin Man,The',1934,'0',(1),''),
(4,'Tampopo',1985,'4','',(1));

you can also set an default empty string in your create table

SQL BIT column must be blank vs TRUE on output, NULL vs 1 on input

2 Answers2