"Quiz" of March 9, 2017.

Are conditional statements available to be used In SQL, such as an IF statement that produces one of two answers based on the value of a particular field?
Yes, there is a CASE statement:
SELECT
customerid,
firstname,
lastname,
CASE country
WHEN 'USA' THEN
'Dosmetic'
ELSE
'Foreign'
END CustomerGroup
FROM
customers


In the terminal when we are in SQL if I press the up key, instead of getting the previous command I get ^[[A , how do I fix this?
I had the same problem using the Anaconda-supplied version of sqlite3. It apparently has something to do with the library "readline". The native version in Ubuntu, /usr/bin/sqlite3 works fine.

Why should we use multiple tables instead of one table that holds all the data?

Avoiding inconsistencies (and also efficiency, robustness, data compartmentalization).

If for example, every row referring to a specific movie had not only the mID but also the title, there would be the possibility of getting the title wrong in some rows. Having a separate table for movies allows the title to appear once and only once in the database.

It is more efficient and more robust, for example, to use an ID number to denote a student in a bunch of tables, than to put their full name everywhere. When you need the name (and often you won't, such as if you're just counting), you can "look it up" by joining with a table that provides that information.

Responsibility for different parts of a database may reside with different people and may be conceptually very disparate. For example, it would not be sensible to clutter a table about movies with personal details of anyone who has reviewed the movie.

Is there a way to show the column type in SQL?
Yes. In SQLite3
pragma table_info(foo);


What are the advantages to the different SQL implementations? E.g. MIcrosoft SQL Server, SQLite, Oracle
Some are free and open source (SQLite, MySQL, PostgreSQL, ...). Some are designed to handle many simultaneous users, while others are designed only for a single user (SQLite).

Can SQL be used as a substitute for Python, or is it more of an additional tool?
If you're doing your own computations on your own data, then Python tools are better in most cases. However if you have many users reading and modifying data at the same time, DBMSs are the way to go.

Can we export SQL databases to say a JSON or a CSV?
Since SQL deals only with "relational" i.e. tabular data, there is usually no need to export to anything fancier than CSV. (Exceptions would be if a column contained images, or audio files, etc.)
.headers on
.mode csv
.output mytable.csv
select * from mytable;
.output stdout

The last command restores sending output to the terminal (stdout = standard output).

Yes. First, all your commands are stored in a plain text file called .sqlite_history in your home folder, and are also available via the arrow keys during a sqlite3 session.

Second, for all but the lightest work, I recommend you compose all your commands (SQL queries and sqlite meta-commands) in a text file using a text editor, and then just do

in SQLite. This allows you to modify it and re-run it easily.

Can you change the data type of a column after it is created?
Some SQL systems allow this with ALTER TABLE, but SQLite3 does not. But you can create a new column of the desired type.
ALTER TABLE foo ADD COLUMN mynewcol integer;
INSERT INTO foo (mynewcol) select oldcol from foo;


Is there a way to define functions in SQL to do more advanced computation with the data?
SQLite does not support user-defined functions but Oracle, for example, does, and MySQL has a CREATE FUNCTION command.

What is the difference between noSQL and SQL databases, and why has noSQL become more popular than SQL in recent times?
NoSQL databases support database structures more elaborate than just tables. See Wikipedia article.

How come do we use * and % in SQL to include 'everything'? In which case do we use * and when do want to use %?
* is used to mean all columns, and % to mean an arbitrary string. Different symbols for different things.

Is there any better way to convert the column datatype? CAST or ALTER or MODIFY?
I don't know of one. See answer to Anthony's question below.

How do I get sqlite3 to allow me to edit commands as I type them and to recall previous commands? Currently if I use the arrow keys in the command line I get ^[[A (up) ^[[B (down) ^[[D (left) ^[[C (right) where I'd like to be able to cycle through past commands with up/down and edit a command with left/right.
Install SQLite3 natively and don't use Anaconda's version.

Is there a way to see all the views currently active on your database?
Views are treated like tables. So if you list the tables, you'll also see the views.

Is there a simple syntax in sql to look at two tables side by side
No. I think they would say that without joining them in some specific way, it's not meaningful to arbitrarily concatenate rows. If you really insist on doing this, you could use an index row in each table and join them on that index.

What does it mean if command window gives us --> and how to leave that and go back to sqlite3?
It means your SQL command is not complete. Usually it means you have forgotten to end with a semicolon (;).

What is the difference between a left join and a left outer join
None. In SQLite3 at least a left join is a left outer join. (Apologies if I misspoke about this last week.)

Is there anyway to run multiple lines at once? Kind of like a script?
Yes. Just put your commands in a text file and use
.read mycommands.txt


How to update the table file from SQL with update dates shown?
Time-stamping is available: see this.

Why are right joins not allowed in SQLite?
They just didn't bother implementing it. It's only a matter of convenience, not functionality: you can choose any table to be the "left" (first-listed) one.

What does it mean when you try to execute a line and get a '...' without the '[sqlite>' on the left?
It means your SQL command is not complete. Usually it means you have forgotten to end with a semicolon (;).

when I use my own made .csv Error: file is encrypted or is not a database I don't know why.
Did you try to run the following?
sqlite3 myfile.csv

If so, that is the reason. The command line argument to sqlite3 is the name of a database. If you want to import a csv file, you need to do
.import myfile.csv

inside sqlite3.

How to make sure that the comparison of the quantities done( during joining ) are both integers
Can you give me an example where this would be needed?

My question is, what's the maximum size of databases that sql can handle?
40TB. There are other limits that might come into play first.