Your SQL questions answered

"Quiz" of March 9, 2017.

Andrew asked:

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

Anna asked:

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.

Katherine asked:

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

Efficiency, robustness, data compartmentalization.

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.

Robert asked:

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

Margaret asked:

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

Mia asked:

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.

Adhish asked:

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

Megan asked:

Is there a way to save your work in sqlite to return to later?

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

.read mycommands.txt

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

Anthony asked:

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;

Nicholas asked:

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.

Adi asked:

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.

Sakar asked:

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.

Aishani asked:

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.

Jonathan asked:

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.

Edward asked:

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.

Jonathan asked:

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.

Hedy asked:

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 (;).

Samuel asked:

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

Sanjeevani asked:

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

Hui asked:

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

Maggie asked:

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.

Nicholas asked:

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 (;).

Wenjie asked:

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.

Prashant asked:

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?

Krithika asked:

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.

Daniel asked:

I am wondering, in an engineering position, when should SQL be used over Excel or even Python data manipulation?
Excel/LibreOffice spreadsheets only for very light work. Python Pandas is probably much more convenient and faster if only one person is accessing the data at a time and it can all fit into memory at once. RDMBS supporting SQL like MySQL, Oracle, etc are useful when several (or many) people are reading and writing the database at the same time.

Xuli asked:

What's the difference between SQL and Pandas?
See answer to Daniel immediately above.

Yiqing asked:

what are the different type of joins? outer/linner joint and left/ right joint
See this document on joins that I have prepared for you.