SQL {Structured Query Language}
What is SQL?
- SQL stands for Structured Query Language
- SQL lets you access and manipulate databases
- SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987
What Can SQL do?
- SQL can execute queries against a database
- SQL can retrieve data from a database
- SQL can insert records in a database
- SQL can update records in a database
- SQL can delete records from a database
- SQL can create new databases
- SQL can create new tables in a database
- SQL can create stored procedures in a database
- SQL can create views in a database
- SQL can set permissions on tables, procedures, and views
SQL is a Standard - BUT....
Although SQL is an ANSI/ISO standard, there are different versions of the SQL language.
However, to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner.
Using SQL in Your Web Site
To build a web site that shows data from a database, you will need:
- An RDBMS database program (i.e. MS Access, SQL Server, MySQL)
- To use a server-side scripting language, like PHP or ASP
- To use SQL to get the data you want
- To use HTML / CSS to style the page
RDBMS
RDBMS stands for Relational Database Management System.
RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.
The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows.
Look at the "Customers" table:
Programming Language vs. General-Purpose Programming Language
ACCORDING TO WEBOPEDIA, “a programming language is a vocabulary and set of grammatical rules for instructing a computer or computing device to perform specific tasks.” SQL is definitely a programming language given this definition.
It has certain vocabulary and strict syntax that should be followed. For example, all SQL statements start with specific keywords (e.g., SELECT, INSERT, CREATE, UPDATE, DELETE) and end with a semicolon. The order of clauses is also important. For example, GROUP BY should follow the WHERE clause and precede the ORDER BY clause:
SELECT column_one, column_twoFROM tableWHERE column_one > 2000GROUP BY column_one, column_twoORDER BY column_one; |
Furthermore, messages that are written using this vocabulary and syntax instruct your computer to perform specific tasks, like accessing certain data in the database; creating, updating, or deleting tables in the database; etc.
Then why do we have all this controversy around recognizing SQL as a programming language? The problem is that people often mean “general-purpose programming language” when saying “programming language.” And there is a big difference between these terms.
WIKIPEDIA SAYS that “a general-purpose programming language is a programming language designed to be used for writing software in the widest variety of application domains.” Basically, you can create all kinds of applications using a general-purpose programming language, including desktop, mobile, or web applications. The most widely used programming languages from this category include Java, JavaScript, Python, C++, and Ruby.
In contrast to these languages, SQL has a very niche role of communicating with relational databases. Thus, you cannot build an application using only SQL. This leads us to the conclusion that SQL is not a general-purpose programming language.
SQL as a Domain-Specific Language
Now we know that SQL satisfies the definition of a programming language but not a general-purpose programming language. So, where does SQL belong?
Apart from general-purpose programming languages, there are also domain-specific languages (DSLs). WIKIPEDIA DEFINES a domain-specific language as “a computer language specialized to a particular application domain.” HTML, or Hypertext Markup Language, is one of the most popular domain-specific languages used for structuring webpages.
Similarly, SQL, with its specific application domain, can be defined as a domain-specific language. Structured Query Language is a highly targeted language for “talking” to databases. While being an effective and powerful tool for data management and access, SQL has limited usage compared to general-purpose programming languages. However, this drawback comes with certain benefits.
Because of its narrow application domain, SQL is usually easier to learn than a general-purpose programming language. You don’t need a computer science background to start with the SQL BASICS course. Moreover, even when starting with zero programming knowledge, you can become an SQL expert in a few months after taking the SQL FROM A TO Z track.
SQL and Turing Completeness
If you are not yet convinced that SQL is a programming language, here is another point to consider.
To evaluate how “powerful” a certain programming language is, computer scientists often use the concept of Turing completeness. According to the WIKIPEDIA DEFINITION, a programming language “is said to be Turing complete or computationally universal if it can be used to simulate any Turing machine.” In simple terms, a Turing machine is a hypothetical machine, named by computer scientist Alan Turing, that can take any program of any complexity and run it.
So, we could say that SQL is Turing complete if any program that can be written to run for a Turing machine can also be written in SQL. And in fact, SQL, with the addition of recursive queries, satisfies this condition.
If interested, you can learn more about SQL recursive queries in this COMPREHENSIVE GUIDE. You can also learn how to create recursive queries or common table expressions (CTEs) in our RECURSIVE QUERIES course.
Now you know that SQL is Turing complete and thus, computationally universal. But this is not yet the end of the story…
SQL and Procedural Languages
SQL can be extended with procedural languages that allow users to define their own functions and procedures. Therefore, procedural languages are programming languages in either sense of the term.
All database management systems (DBMSs) accept one or more dialects of procedural languages. Let’s have a look at some examples.
PL/SQL, or Procedural Language for SQL, is an extension for SQL in the Oracle database management system. Similar to general-purpose programming languages, PL/SQL includes elements like conditions and loops. With PL/SQL, you can also declare constants, variables, variable types, procedures, and functions.
PL/pgSQL, or Procedural Language/PostgreSQL, is a procedural language supported by the PostgreSQL object-relational database management system. It is very similar to Oracle’s PL/SQL and allows loops and conditions as well as user-defined functions. You can learn how to create user-defined functions in PostgreSQL with our COMPREHENSIVE COURSE.
MySQL is a database management system that doesn’t have a separate name for SQL extensions that allow the creation of functions and procedures, but it supports this functionality. MySQL has CREATE PROCEDURE AND CREATE FUNCTION STATEMENTS that create stored routines. User-defined functions are also supported in MySQL—they are regarded as externally stored functions.
As you can see, SQL is a powerful tool for data management and access. It can handle huge analytical queries with all kinds of data. But, it can also be used for fun! Read about HOW TO DRAW A CHRISTMAS TREE IN SQL.
Time to Learn SQL!
Now you know that SQL is a powerful programming language that is worth your attention. While Structured Query Language has been around for decades, it’s still the go-to tool for data access and management in all kinds of modern applications. Business analysts, data analysts, data scientists, and many other roles in today’s companies need SQL to perform their jobs.
Except for its apparent usefulness as a domain-specific language, SQL can be also the first step towards programming and learning a general-purpose programming language.
Are you excited about where SQL can bring your career? Then check out the following courses:
- SQL Basics is an easy-to-follow introduction to SQL queries. No computer science background required!
- SQL from A to Z is a track designed for ambitious and dedicated students who are ready to go from complete newbies, through intermediate and advanced topics, to an SQL guru level.
- Writing User-Defined Functions in PostgreSQL is for those who feel confident with SQL and are ready to master the procedural extension of SQL to write user-defined functions.
Thanks for reading, and happy learning!
Summary: There is a long-lasting debate about recognizing SQL as a programming language. With SQL, you give your computer instructions to perform huge analytical queries, which looks like programming. However, you cannot build an application using only SQL. So, is SQL a programming language? Learn why the answer is definitely yes.
Is SQL Worth Learning?
What Is SQL? Who Uses It?
Relational databases store and logically organize large amounts of data. The data “lives” in tables, which can be linked (i.e. to show the relationships between the data in the tables).
Look at the example below. There’s a sample table called doctor, where you can find information about doctors in the US. And there’s another sample table called hospital, with data about US hospitals.
| ID | f_name | l_name | specialization | hospital |
|---|---|---|---|---|
| 1 | Mick | Meister | oncology | 4 |
| 2 | Mekisha | Abdul | dentist | 5 |
| ... | ... | ... | ... | ... |
The doctor table
| ID | name | city | address | postal_code |
|---|---|---|---|---|
| ... | ... | ... | ... | ... |
| 4 | Saint Francis Memorial Hospital | San Francisco | 900 Hyde St | CA 94109 |
| 5 | Total Health Dental Care | San Francisco | 1880 Pleasant Valley Ave | CA 94611 |
| ... | ... | ... | ... | ... |
The Hospital table
Looking at such a small amount of data – what we call a small sample size – you can spot links. It’s obvious that Dr. Mick Meister must work at Saint Francis Memorial Hospital and Dr. Mekisha Abdul must work at Total Health Dental Care. But what if you have a list of thousands of US doctors and you need to connect each one with the hospital where they work? Or what if you only wanted to see a list of oncologists? Or only oncologists in San Francisco? You can do this and more with SQL!
SQL is the most common language for dealing with databases. If you learn SQL, you can communicate with databases; you “ask a question” using SQL queries, which are instructions that you send to a database to retrieve information you want.
Why Learn SQL?
While SQL is mainly associated with the IT industry, it’s becoming standard in the fields of finance, banking, and marketing. Additionally, SQL is essential for anyone who works with large amounts of data, such as data analysts or business analysts.
Knowing SQL lets you quickly retrieve and process data without any assistance from the IT department. (And we all know they have a lot of other work, most of it more important than getting your data.) Fortunately, LEARNING SQL IS EASY LIKE SUNDAY MORNING (to quote a Lionel Richie song) and doesn’t take much time. Moreover, you don’t need any prior programming knowledge to start learning SQL.
But what if you don’t work in a company that deals with large amounts of data? You might still wonder why to learn SQL. Is SQL worth your time in your particular situation? It sure is! Analyzing data is incredibly important for staying ahead of the competition, and most modern organizations seek specialists who have analysis skills. Knowing SQL and understanding databases might help you find a job with a higher salary.
Besides the money, it’s simply smart to learn SQL. It allows you to build reports on how the business is performing. And, with just a few adjustments, you can reuse your SQL code instead of writing future reports from scratch. This way you save time, effort, and stress.
Where Should You Learn SQL?
Although each database has its own version of SQL syntax, I suggest learning standard SQL, as it will let you work in all databases. The only situation where you might want to learn a SQL dialect (such as PostgreSQL) is if you’re working at a company using that particular database management system.
In my opinion, the best way to learn SQL is with an interactive online platform. Look for one that explains the theory and then gives you a chance to write your own queries. You should be given a lot of hands-on exercises where you have to write your own code.
Once you learn the basics, you will be eager to BROADEN YOUR SQL KNOWLEDGE.
Comments
Post a Comment