{"id":718,"date":"2018-06-06T03:24:44","date_gmt":"2018-06-06T00:24:44","guid":{"rendered":"http:\/\/kusuaks7\/?p=323"},"modified":"2026-05-22T12:40:48","modified_gmt":"2026-05-22T12:40:48","slug":"know-sql-in-2018","status":"publish","type":"post","link":"https:\/\/www.experfy.com\/blog\/bigdata-cloud\/know-sql-in-2018\/","title":{"rendered":"know-sql-in-2018"},"content":{"rendered":"\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\tReady to learn SQL? Browse courses<\/a> like \u00a0Apache Spark SQL<\/a> developed by industry thought leaders and Experfy in Harvard Innovation Lab<\/em><\/strong>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\"\"\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t

As a technical recruiter I came across many positions where SQL was a critical skill. To be a Business Analyst, Data Scientist, Backend Developer, Marketing professional or UX Design Researcher understanding SQL queries can be fundamental to your work.<\/p>\n

Let me back up.\u00a0What is SQL anyway?\u00a0<\/strong>SQL is a query language for talking to structured databases. Pretty much all databases that have tables and rows will accept SQL based queries. SQL has many flavors but the fundamentals stay the same. Professionals and amateurs alike use SQL to find, create, update and delete information from their sources of record. It can be used with a ton of different databases like MySQL, Postgres, SQL Server and Oracle. It powers the logic behind popular server-side frameworks like\u00a0Ruby On Rails and Laravel<\/a>. If you want to find information associated with a particular account or query what buttons users click in your app there is a good chance SQL can help you out!<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t
SQL: Pronounced \u201cS-Q-L\u201d or \u201cSequel\u201d\u200a\u2014\u200ayou\u00a0choose.<\/strong><\/span><\/blockquote>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t

SQL alternatives<\/strong><\/h4><\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t

Before we hop on the SQL train to Database Town I\u2019d like to acknowledge some alternatives. You can use ORMs to query databases. ORM stands for\u00a0Object Relational Mapper<\/strong>, which is a fancy way of saying that you can write code in a programming language like PHP or Node.js that translates to SQL queries. Popular ORMs are Active Record for\u00a0Ruby On Rails<\/strong>, Eloquent for\u00a0Laravel<\/strong>\u00a0and Sequelize for\u00a0Node.js<\/strong>. All of these services allow you to write code that translates to SQL under the hood. SQL is important for building applications with these technologies.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t

There are many databases that do not use SQL, such as MongoDB and GraphQL. These are newer technologies and not as widely adopted as relational databases. Relational databases have been around a very long time and power the majority of data storage on the internet. To fully appreciate\u00a0NoSQL technologies<\/strong>\u00a0and the reasons they came about it\u2019s helpful to know how relational databases and SQL work.<\/p>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t

Create a\u00a0table<\/strong><\/h4><\/h4>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t

The first thing to know is that relational databases (such as MySQL, SQLite or PostgreSQL) are made up of tables. One database can hold many tables and each table consists of a particular category of record. For example, in an eCommerce site we might have tables for orders, users, transactions and products. In many web applications you\u2019ll create tables through\u00a0migrations<\/a>, but it\u2019s still helpful to be able to read and write SQL create statements.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t
create table exercise_logs ( <\/span>\n\u00a0 id integer primary key autoincrement,\u00a0 <\/span>\n\u00a0 type text, <\/span>\n\u00a0 minutes integer,\u00a0 <\/span>\n\u00a0 calories integer,\u00a0 <\/span>\n\u00a0 heart_rate integer<\/span>\n);<\/span><\/span><\/div>\n \n

The above SQL statement creates a table called exercise_logs with five columns (id, type, minutes, calories and heart_rate). Each column has a specific\u00a0data type<\/a>, such as integer or text.<\/p>\n

On the second line of our create statement we specify that each new record will have a unique id, known as the table\u2019s primary key.<\/p>\n

The structure and definition of a database\u2019s tables is known as the database\u2019s\u00a0schema<\/strong>.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t

Insert data into a\u00a0table<\/strong><\/h4><\/h4>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t

To add an exercise log to our table we can write a SQL insert statement.<\/p>\n\n

insert into exercise_logs(type, minutes, calories, heart_rate) <\/span>\nvalues (‘biking’, 30, 100, 110);<\/span><\/span><\/div>\n \n

In this example we specify the table name, exercise_logs and the columns for which we\u2019d like to insert data. After the values keyword we include the data to be inserted into the database.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t

Select statements<\/strong><\/h3>\n<\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t

For many professions, querying data using SQL select statements is their primary\u00a0bread and butter.<\/strong><\/p>\n

To select\u00a0all records<\/em>\u00a0from a database:<\/p>\n\n

select * from exercise_logs;<\/span><\/span><\/div>\n

To find all the activities a user engaged in and the total amount of calories they burned doing that activity you could write:<\/p>\n\n

select type, sum(calories) as total_calories <\/span>\nfrom exercise_logs <\/span>\ngroup by type;<\/span><\/span><\/div>\n \n

You can do all kinds of funky stuff like determine the number of students had which letter grade.<\/p>\n

You could group each of the exercises by heart rate zones.<\/p>\n\n

\/* Group the count of exercises by each heart rate zone *\/<\/span>\nselect count(*),\u00a0\u00a0\u00a0 <\/span>\n\u00a0 case\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>\n\u00a0\u00a0\u00a0 when heart_rate > 220 – 30 then ‘above max’\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>\n\u00a0\u00a0\u00a0 when heart_rate > round(.9 * (220 – 30)) then ‘above target’<\/span>\n\u00a0\u00a0\u00a0 when heart_rate > round(.5 * (220 – 30)) then ‘within target’<\/span>\n\u00a0\u00a0\u00a0 else ‘below target’\u00a0\u00a0\u00a0 <\/span>\nend as ‘heart_rate_zone’<\/span>\nfrom exercise_logs<\/span>\ngroup by heart_rate_zone;<\/span><\/span><\/div>\n \n

SQL is very helpful for reporting purposes. It can be used to filter marketing results, find customer information, search server logs or create reporting dashboards.<\/p>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t

Subqueries and\u00a0Like<\/strong><\/h4>\n<\/h4>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t

Sometimes you\u2019d like to dynamically grab data with a query and use that result in another query. For this case we have subqueries. For example, we could have a table called\u00a0drs_favorites\u00a0<\/strong>that holds doctor recommended activities:<\/p>\n\n

\/* This is a comment *\/<\/span>\ncreate table drs_favorites ( <\/span>\n\u00a0 id integer primary key,\u00a0\u00a0\u00a0 \/* Unique identifier *\/<\/span>\n\u00a0 type text,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/* Type of activity *\/<\/span>\n\u00a0 reason text\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/* Why the doctor recommends it *\/<\/span>\n);<\/span><\/span><\/div>\n
insert into drs_favorites<\/span>\n\u00a0 (type, reason) <\/span>\n\u00a0 values (‘running’, ‘improves cardiovascular health.’);<\/span><\/span><\/div>\n \n

For our main query we would like to find all the activities in the activity_log table that doctors recommended for improving cardiovascular health.<\/p>\n\n

SELECT * FROM exercise_logs WHERE type IN (SELECT type FROM <\/span>\ndrs_favorites WHERE reason LIKE “%cardiovascular%”);<\/span><\/span><\/div>\n \n

In between the parenthesis we have a subquery that selects all the records where the word \u201ccardiovascular\u201d is included somewhere in the reason the doctor recommends it. Read more about the\u00a0LIKE operator<\/a>. Note that SQL can use capital or lowercase letters.<\/p>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t

Conclusion<\/strong><\/h4>\n<\/h4>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t

That\u2019s the tip of the iceberg! The exercise code, including joins and SQL statements to talk to multiple databases are available on\u00a0this Github repository<\/a>.<\/p>\n

If you\u2019d like to play around with a test database and write some queries, W3schools has a SQL editor available here:<\/p>\nSQL Tryit Editor v1.5<\/strong>\nEdit description<\/em>www.w3schools.com<\/a>\n

Happy coding!<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"

SQL is a critical skill. What is SQL anyway? SQL is a query language for talking to structured databases. Pretty much all databases that have tables and rows will accept SQL based queries. SQL has many flavors but the fundamentals stay the same. Professionals and amateurs alike use SQL to find, create, update and delete information from their sources of record. Understanding SQL queries can be fundamental to your work. Professionals and amateurs alike use SQL to find, create, update and delete information from their sources of record.<\/p>\n","protected":false},"author":288,"featured_media":3840,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[187],"tags":[94],"ppma_author":[1899],"class_list":["post-718","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-bigdata-cloud","tag-data-science"],"authors":[{"term_id":1899,"user_id":288,"is_guest":0,"slug":"connor-leech","display_name":"Connor Leech","avatar_url":"https:\/\/secure.gravatar.com\/avatar\/?s=96&d=mm&r=g","author_category":"","user_url":"","last_name":"Leech","first_name":"Connor","job_title":"","description":"Connor Leech is a Technical Master Stitcher at Stitch Labs, developing with Laravel, Javascript, and PHP. He is passionate about enabling developer productivity."}],"_links":{"self":[{"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/posts\/718","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/users\/288"}],"replies":[{"embeddable":true,"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/comments?post=718"}],"version-history":[{"count":0,"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/posts\/718\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/media\/3840"}],"wp:attachment":[{"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/media?parent=718"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/categories?post=718"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/tags?post=718"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.experfy.com\/blog\/wp-json\/wp\/v2\/ppma_author?post=718"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}