Structured Query Language (SQL)

For data analyst roles, SQL is again the most in-demand skill, listed in 57.4% of all data analyst jobs for year 2021. SQL appears in 1.5 times as many “data analyst” job postings as Python, and nearly 2.5 times as many job postings as R.

Figure. Data Base Server (Source: PNGlib)

Do you know the software language used for database applications such as Amazon, Facebook, E-Bay, handling of structured data, data management in relational database management system (RDBMS)? Let me give you a hint, it was one of the first commercial languages used on relational database created by Edgar Frank (Ted) Codd. He was an English computed scientist employed at IBM and often referred to as the forefather of this software language. If you still have not guessed it, it is called structured query language (SQL). In 1981, the forefather of SQL received Nobel Prize of computing Award called the Turing Award. It is considered as the highest distinction in computer science. SQL is still considered as the most widely used database language and became a standard in 1986 by American National Standards Institute (ANSI).

SQL and NoSQL

If you want to decipher the business patterns and have technological skills to build large and scalable database systems. Then, add SQL to your resume. Sometimes, you have to store complex, heterogeneous and structureless data efficiently utilizing NoSql databases. The features of NoSQL differ from SQL systems. Few of them are listed below. The examples of SQL databases are SQLite, MySQL, Oracle, PostregeSQL, Microsoft SQL server etc while NoSQL databases examples include DynamoDB, Cassandra, Redis, CouchDB, RethinkDB, RavenDB, and MongoDB.

The SQL databases are called relationship database management systems and they are used to execute queries, retrieve data, and edit data. While NoSQL databases are called non relational SQL databases which stands for “Not Only SQL”, and they are capable of using other types of query language.

Both SQL and NoSQL database systems have begun to merge and support storing and querying JSON data but don’t include NoSQL features such as horizontal scaling and the user-friendly interface. Please bear in mind that there are four different types of NoSQL databases such as:

1. Document-oriented databases — Also known as a document store, this database is designed for storing, retrieving and managing document-oriented information. Document databases usually pair each key with a complex data structure (called a document).
2. Key-Value Stores — This is a database that uses different keys where each key is associated with only one value in a collection. Think of it as a dictionary. This is one of the simplest database types among NoSQL databases.
3. Wide-Column Stores — this database uses tables, rows, and columns, but unlike a relational database, the names and format of the columns can vary from row to row in the same table.
4. Graph Stores — A graph database uses graph structures for semantic queries with nodes, edges, and properties to represent and store data.

Now, in the next section, we will explore about SQL Database Management Software, the best enterprise software used in worldwide industries.

SQL Database Management Software

Database Management Software (Source: Stambia)

To keep data guarded and safe, we use database management software. It maintains not only the efficiency of data but also helps reducing the data redundancy. The personal database management software is considered small when compared to enterprise database software. The enterprise software can handle parallel query, clustering and multi-process support. The best enterprise software used words wide are listed below.

1. Altibase
2. MySQL
3. PostgreSQL
4. Maria DB
5. MongoDB
6. Cassandra
7. SQLite
8. Cubrid

The popular ones are

1. SolarWinds Database Performance Analyzer
2. DbVisualizer
3. ManageEngine Applications Manager
4. Oracle RDBMS
5. IBM DB2
6. Microsoft SQL Server
7. SAP Sybase ASE
8. Teradata
9. ADABAS
10. MySQL
11. FileMaker
12. Microsoft Access
13. Informix
14. SQLite
15. PostgresSQL
16. AmazonRDS
17. MongoDB
18. Redis
19. CouchDB
20. Neo4j
21. OrientDB
22. Couchbase
23. Toad
24. phpMyAdmin
25. SQL Developer
26. Seqel PRO
27. Robomongo
28. Hadoop HDFS
29. Cloudera
30. MariaDB
31. Informix Dynamic Server
32. 4D (4th Dimension)
33. Altibase

SQL Database Design Techniques

MySQL has attracted many web developers who had no prior IT experience. Now, we will implement a database using SQL. When you are implementing a data design, you need to learn about data by collecting it. Then, organize these data elements by naming them. Afterwards, regroup the data into elements and take into account the needed keys. Finally, implement it with MySQL's structure. Also, follow the Codd’s rules when implementing data design. The technique most frequently used by database developers is building entity relationship diagram (Reference: see figure below as an example). The diagram examples include inventory management database for warehouse, library books database, healthcare billing database, once you completely layout the final diagram. Then, you have to apply a technique called normalization which is an effective tool for transforming diagram into a model consisting of tables and columns and refining the mode into an effective data structure.

Figure. An example of Entity Relationship Diagram (Source: guru99.com)

Sample Database Example

If you want to find out what databases currently exist on the MySQL server, enter the SQL statement show databases;

SQL statement show databases

Now, we will use Python Jupyter Notebook to create our first database application, let’s get started.

Importing Libraries in Python Jupyter Notebook

Creating a new database

Now, we will check if database college is created successfully on the MySQL server, enter the SQL statement show databases; it was created successfully.

SQL statement SHOW databases

Modifying Server Connection

Defining Query Execution

Creating Tables

Let us find out the structure of teacher table by entering SQL statement, DESCRIBE teacher; it should display information about each of a table’s columns.

SQL Statement DESCRIBE

Creating Remaining Tables

Defining Foreign Key Relationships

Populating Tables

If you want to select all the fields available in the teacher table, we will use the following syntax: SELECT * FROM teacher;

SQL statement SELECT * FROM table

Populating remaining tables

Reading Data

Query 1

Query 2

In the above python code for query 2, if we just want specific attributes from the table, we can use the following SQL statement such as SELECT last_name,dob FROM teacher;

SQL Statement SELECT statement 1, statement 2 FROM table

Query 3

SQL Statement WHERE

If we want to get results in different formats which will make them more useful, then let us implement the code which will return the results of our query as a list of tuples. A typical example of tuple is having a list in terms of indexing.

Formatting Output into a list

Now, the last step for our data analysts, we will use pandas to convert the output from college database into a Data Frame. “Voila!” there it is.

Output from SQL database into Pandas Data frame

Conclusion

So, we learned the history of SQL, basics, difference between SQL and no SQL and then finally created a database college in python and verified the results in MySQL. So, with every new project, database developers always have to make few evaluations. Please bear in mind that MySQL itself doesn’t care from which language it gets called, so you can basically use any language you like, as long as the language can call the MySQL functions in some way. If you want to learn more about SQL or pursue to become data scientist or efficient database developer, then click on the following links and start learning away.

Code Academy W3School Tutorials Point