#FUNDAMENTAL SQL The most popular SQL-based databases include PostgreSQL, MySQL, Oracle, MS SQL Server, and SQLite.
Most databases store your data in a hidden-away part of your computer’s filesystem. You’d never interact with the data directly, only through the tools provided by the database engine. SQLite, instead, stores its data in a plain-text file. This makes it incredibly easy to move, rename, delete, or transfer the database.
###Experimenting with SQLite
-
Firing up the Sqlite db:
- In the terminal: sqlite3 example.sqlite3
-
Creating a table in the terminal:
sqlite> CREATE TABLE fruits(id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(31), quantity INT); -
Insert data into the table in the terminal:
sqlite> INSERT INTO fruits(name, quantity) VALUES('apples', 6); -
Displaying data from tables in the terminal:
sqlite> .mode column sqlite> .header on sqlite> SELECT * FROM fruits; id name quantity ---------- ---------- ---------- 1 apples 6 2 bananas 9 3 grapes 12 -
Scoping the data - Searching for specifics:
sqlite> SELECT * FROM fruits WHERE name='apples'; id name quantity ---------- ---------- ---------- 1 apples 6 sqlite> SELECT * FROM fruits WHERE quantity=12; id name quantity ---------- ---------- ---------- 3 grapes 12 -
Calculating Data (using the length function):
sqlite> SELECT * FROM fruits WHERE LENGTH(name)=7; id name quantity ---------- ---------- ---------- 2 bananas 9 -
Querying a subset of a row's data:
sqlite> SELECT name FROM fruits WHERE id=3; name ---------- grapes -
Delecting Rows:
sqlite> DELETE FROM fruits WHERE name='grapes';Note that the id 3 will not be re-used. The next row will have an id of 4. 1, 2, 4,…
-
Updating (changing) rows:
sqlite> UPDATE fruits SET quantity=100 WHERE name='apples'; sqlite> SELECT * FROM fruits WHERE name='apples'; id name quantity ---------- ---------- ---------- 1 apples 100 -
Adding Columns to the table:
sqlite> ALTER TABLE fruits ADD COLUMN country_of_origin VARCHAR(127); sqlite> SELECT * FROM fruits; id name quantity country_of_origin ---------- ---------- ---------- ----------------- 1 apples 100 2 bananas 9 sqlite> UPDATE fruits SET country_of_origin='Mexico'; sqlite> SELECT * FROM fruits; id name quantity country_of_origin ---------- ---------- ---------- ----------------- 1 apples 100 Mexico 2 bananas 9 Mexico sqlite> UPDATE fruits SET country_of_origin='Merica' WHERE name='apples'; sqlite> SELECT * FROM fruits; id name quantity country_of_origin ---------- ---------- ---------- ----------------- 1 apples 100 Merica 2 bananas 9 Mexico -
Ordering:
sqlite> SELECT * FROM fruits ORDER BY quantity; or sqlite> SELECT * FROM fruits ORDER BY quantity ASC; id name quantity country_of_origin ---------- ---------- ---------- ----------------- 2 bananas 9 Mexico 1 apples 100 Merica sqlite> SELECT * FROM fruits ORDER BY -quantity; or SELECT * FROM fruits ORDER BY quantity DESC; id name quantity country_of_origin ---------- ---------- ---------- ----------------- 1 apples 100 Merica 2 bananas 9 Mexico -
Relationships (joining tables):
sqlite> SELECT * FROM sales; id fruit_id sold_at ---------- ---------- ------------------- 1 1 2013-03-01 20:37:41 2 1 2013-03-01 20:38:11 3 2 2013-03-01 20:38:44 sqlite> SELECT fruits.name, sales.sold_at FROM fruits INNER JOIN sales ON fruits.id=sales.fruit_id; name sold_at ---------- ------------------- apples 2013-03-01 20:37:41 apples 2013-03-01 20:38:11 bananas 2013-03-01 20:38:44 -
Three Way Relationships:
-
Lets first look at all our tables:
sqlite> select * from fruits; id name quantity country_of_origin ---------- ---------- ---------- ----------------- 1 apples 100 Merica 2 bananas 9 Mexico sqlite> select * from sales; id fruit_id sold_at customer_id ---------- ---------- ------------------- ----------- 1 1 2013-03-01 20:37:41 2 2 1 2013-03-01 20:38:11 2 3 2 2013-03-01 20:38:44 1 sqlite> select * from customers; id name ---------- ---------- 1 Jeff 2 DANNY 3 Frank -
Lets now create relationships between them:
sqlite> SELECT customers.name, fruits.name, sales.sold_at FROM fruits INNER JOIN sales ON fruits.id=sales.fruit_id INNER JOIN customers ON sales.customer_id=customers.id; name name sold_at ---------- ---------- ------------------- DANNY apples 2013-03-01 20:37:41 DANNY apples 2013-03-01 20:38:11 Jeff bananas 2013-03-01 20:38:44or
sqlite> SELECT customers.name, fruits.name, sales.created_at FROM customers INNER JOIN sales ON customers.id=sales.customer_id INNER JOIN fruits ON sales.fruit_id=fruits.id; name name created_at ---------- ---------- ------------------- DANNY apples 2013-03-01 20:37:41 DANNY apples 2013-03-01 20:38:11 Jeff bananas 2013-03-01 20:38:44
-
###Conclusion This all being said, many programmers prefer to use intermediary libraries(ORM's) to generate SQL. If you accept that SQL is a programming language, then these libraries do a form of metaprogramming: code that writes code.
######Object-Relational Mappers (ORMs)
In the Ruby world, we prefer to work in object. SQL is not object oriented, it’s based on relationships.
Most Ruby libraries for interacting with databases are "Object-Relational Mappers" which means they convert, on the fly, the relational data from the database into Ruby objects.
This means that, in your Ruby code, you usually don’t have to think about SQL. You get to stay in the context of objects with attributes and methods, not raw chunks of text like we’ve been outputting from the SQLite console.
######ActiveRecord
In the Ruby world, ActiveRecord (often abbreviated to AR) is by far the dominant player. It’s the default database library for Rails. It is an awesomely powerful library.
However, it works hard to hide the database from you. If you’re just getting started with databases, it’s valuable for you to be a little closer to the SQL(by using another library such as Sequel). An effective programmer might write code using AR, but simultaneously understand the SQL that’s being generated behind the scenes.
Knowing what’s happening at the SQL level can be important for security, performance, and data integrity.