This week I used Command Line Interfaces (CLIs) and Graphical User Interfaces (GUIs) to create databases. I also needed to become very familiar with SQL or Structured query language which is used to create and work with databases in MySQL. My new database language includes terms such as numeric and string operations, the order by statement, and the where clause. The concept of joins or of using data from more than one table was introduced and was used to query data in databases.
Origin of SQL
According to Wikipedia, “SQL was one of the first commercial languages for Edgar F. Codd‘s relational model as described in his influential 1970 paper “A Relational Model of Data for Large Shared Data Banks” Wikipedia also has a a good section on syntax which may be helpful to beginners at http://en.wikipedia.org/wiki/SQL#Syntax
SQL as a Language.
SQL is different than HTML and CSS but most experts agree that it is a programming language. See http://www.techrepublic.com/article/is-sql-a-legitimate-programming-language/ Some consider SQL a fourth generation programming language as it is closer to human language. It is, I think , easier to learn than CSS and perhaps easier than HTML. I am also jsut a beginner at learning Linux and found that SQL is much easier to learn than Linux as SQL is indeed closer to human language than Linux. Linux, for instance, has more shortcuts and uses terms like cd for change directory. SQL often uses full words such as create table.
I have not worked with databases or SQL before so I knew I needed to become familiar with some of the basic concepts before I tackled concepts such as joins. What, for instance were tables and how were they related to databases? I found http://www.sqlbasics.com/SQL-Basics-Introduction-To-Tables.aspx helpful as I tried to understand the basic relationships. I also created a graphical illustration to help me remember the relationships.
The creating a table assignment that we had helped me to get a better idea of how tables are constructed using SQL statements such as varchar and char. Of course, I needed to create a database before I created the table which I realized after trying to create a table first in the mysql monitor without creating a database. The columns of the table in the code below are on different lines except for the primary key.
mysql>Create table images, -> (title varchar(100), ->height int, ->width int, ->unit char(20), ->image_id int not null auto_increment, ->format varchar(100), ->primary key(image_id) -> );
I carefully reviewed Joshua Mostafa’s course at http://www.vtc.com/products/mysql.htm before I attempted to create tables or work with SQL. Reviewing his unit on SQL’s Basic syntax was particularly helpful and I printed it out for future exercises. Mostafa also explained that each item in a statement such as creating a table needs to be separated by a comma and that a semicolon or ; needs to be at the end of SQL statement. These issues may be minor issues but to a beginner it is very important to realize that many errors come from not understanding where a comma or a semicolon is supposed to go. Understanding these issues will mean that the practice sessions in the mysql monitor will be much easier and will not cause as much frustration when table queries cannot be carried out or when tables cannot be created because of a missed comma
One of the most difficult ideas this week was the concept of joins. A join links data from two or more tables. A good explanation of joins with some good basic examples can be found at http://www.sqlcourse2.com/joins.html A visual explanation of joins can be found at http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
Mostafa’s unit on joins was helpful and W3Schools definition was particularly good. See http://www.w3schools.com/sql/sql_join_inner.asp However, the W3 Schools tutorial has large tables with many columns and the concepts of inner joins, right joins and left joins are not as clear as they would be with a simple table with fewer columns. Practicing examples of joins with databases in the mysql monitor was an invaluable aid in gaining more of an understanding of joins.
Overall it was easy to learn SQL. I benefited from the hands-on exercises and the SQL query assignments which enabled me to think through the different types of joins. I now have a much better understanding of how databases work and look forward to creating more databases.
Codd, Edgar F (June 1970). “A Relational Model of Data for Large Shared Data Banks”. Communications of the ACM (Association for Computing Machinery) 13 (6): 377–87. doi:10.1145/362384.362685. Retrieved 2007-06-09
- What is MySQL? (mysqlhostingstuff.wordpress.com)
- When it’s faster to use SQL in MySQL NDB Cluster over memcache API (architects.dzone.com)
- MySQL (kayleighobrien1989.wordpress.com)
- Understanding Big Data: The Easy Way to Become Data Literate (siliconangle.com)
- Chapter 11: MySQL Database… (rajapuri.wordpress.com)
- Three table MySQL LEFT JOIN not returning rows where one table isn’t involved (stackoverflow.com)
- Extract pdf and doc files from database field to folder (daniweb.com)
- Tips and Tricks to Optimize MySQL (mysqlhostingstuff.wordpress.com)
- MySQL 5th Edition (i-programmer.info)
- MySQL many-to-many relationship with FOREIGN KEYS (stackoverflow.com)