{"id":2177,"date":"2020-09-30T03:04:37","date_gmt":"2020-09-30T03:04:37","guid":{"rendered":"https:\/\/data-science.gotoauthority.com\/2020\/09\/30\/introduction-to-sql-part-i\/"},"modified":"2020-09-30T03:04:37","modified_gmt":"2020-09-30T03:04:37","slug":"introduction-to-sql-part-i","status":"publish","type":"post","link":"https:\/\/wealthrevelation.com\/data-science\/2020\/09\/30\/introduction-to-sql-part-i\/","title":{"rendered":"Introduction To SQL: Part I"},"content":{"rendered":"
\n
\"\"\"\"
Database Computing Representing Information Storage 3d Rendering<\/figcaption><\/figure>\n

What is SQL?<\/h4>\n

“SQL (pronounced “ess-que-el”) stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems.” \u00a0ANSI will come into play later as there are often many ways to execute the same task in SQL. \u00a0Make sure to check with your employer if they maintain ANSI standards in their code. \u00a0 “SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database.” \u00a0There are several different companies that operate database management systems with SQL, including Microsoft and Oracle. \u00a0While SQL standard commands are largely transferable, most systems also have unique property extensions. \u00a0 There are also NoSQL databases, however we will not be covering these. \u00a0“NoSQL databases are increasingly used in big data<\/a> and real-time web<\/a> applications.” \u00a0Some notable NoSQL databases include MongoDB, Oracle NoSQL Database, and (the best named) Voldemort.<\/p>\n

SQL is a Relational Database Management System – RDMS for short. \u00a0The data held within a RDBMS is stored in tables like the one below.<\/p>\n

\"\"\"\"
A table from SQLDeveloper<\/figcaption><\/figure>\n

All SQL tables are composed of fields. \u00a0A field is simply a column within a table. \u00a0As an example, in the table above, the fields are MAKE, MODEL, MODEL_YEAR, MILAGE, etc. \u00a0Each field contains information for every record in the table and each record has its own row in the table (for example, there are 30 records in the table above). \u00a0While small tables such as the one above are relatively simple to interpret with the eye, tables can have millions of entries. Therefore, we need an efficient way to retrieve the information we’re looking for. \u00a0<\/strong><\/p>\n

Select, From, Where… <\/h4>\n

Three of the most widely used keywords in SQL are SELECT, FROM and WHERE (note: you do not have to capitalize these words but I do). \u00a0\u00a0<\/p>\n

SELECT<\/h6>\n

The SELECT statement tells SQL what fields (or columns) you would like to use in the query. \u00a0Within the statement, aggregation functions such as COUNT, MAX, MIN and AVG can also be used (these will be covered in a later post). \u00a0Finally, the * operator can be used to represent all. \u00a0As an example, in our table above we could write a statement to select all fields in two ways:<\/p>\n

-- OPTION 1: LIST ALL FIELDS\nSELECT \n    MAKE, \n    MODEL, \n    MODEL_YEAR, \n    MILEAGE, \n    COLOR, \n    PRICE, \n    CONDITION_CD \n\n-- OPTION 2: USE THE * OPERATOR\nSELECT *\n<\/code><\/pre>\n
As we continue forward, the use of the * operator will become more natural. \u00a0Also, new lines are not required for each field, however for readability I often will put each field on its own line. \u00a0This is especially true with a long list so referencing back becomes significantly easier.<\/p>\n
FROM<\/h6>\n
The FROM statement lets SQL know which table(s) should be referenced in the query. \u00a0It may seem logical that FROM should begin the query – and you would be largely correct! \u00a0When an SQL query is run, the FROM command is the first item executed (along with JOIN, which we’ll get into in the next post). \u00a0Nevertheless, for writing SQL queries we keep the structure of SELECT followed by FROM. \u00a0<\/p>\n
Within the context of our query, FROM specifies the table(s) that should be referenced.  Using our LOT_CARS table  from earlier:<\/p>\n
-- EXAMPLE 1: USING ONE TABLE\n\nSELECT \n  MAKE, \n  MODEL_YEAR, \n  CONDITION_CD\nFROM LOT_CARS\n;\n\n-- EXAMPLE 2: USING MULTIPLE TABLES\n\nSELECT \n  LC.MAKE, \n  LC.MODEL_YEAR, \n  LC.CONDITION_CD,\n  CT.DESCRIPTION\nFROM LOT_CARS AS LC, \n     CONDITION_TBL AS CT\n;<\/code><\/pre>\n
note<\/strong>: notice the semicolon after each statement. \u00a0“The semicolon character is a statement terminator. It is a part of the ANSI SQL-92 standard”<\/p>\n
While the first example above should be readily understood, we’ll go into some details about the second example. \u00a0First, let’s explain the AS next to LOT_CARS and CONDITION_TBL. \u00a0The AS allows us to alias each table however we wish (there are some rare exceptions). \u00a0We can also use AS to alias how columns are labeled when our query returns a result. \u00a0There is no actual need to use the AS operator (we can simply place a space between the table name and the alias). \u00a0However, including the AS operator enhances readability. \u00a0Finally, we have added LC. and CT. to our selected fields. \u00a0Because we are referencing two different tables, we need to specify which table we would like to use if more than one of the selected tables contain a field with the same value as selected in our query. \u00a0When field names are unique, the prefix is not needed.\u00a0<\/p>\n
We can also return a result utilizing only SELECT and FROM:<\/p>\n
\/* RETURNS THE MAKE AND MODEL_YEAR FIELDS FROM THE LOT CARS TABLE *\/\n\nSELECT \n  MAKE, \n  MODEL_YEAR\nFROM LOT_CARS\n;<\/code><\/pre>\n
note: \u00a0to comment out a line in SQL we use two dashes and to comment out multiple lines we begin with \/* and end with *\/<\/p>\n
WHERE<\/h6>\n
While a table can be returned using only SELECT and FROM, the WHERE clause adds an immense amount of power to queries by filtering the returned results to only those that meet the condition of the WHERE clause. There are many conditional statements in SQL that can be used within a WHERE clause (as well as throughout a query) and will be familiar to someone with even minimal programming experience. \u00a0\u00a0<\/p>\n
\n\n\n\n\n\n\n\n\n\n\n\n
OPERATOR<\/td>\nDESCRIPTION<\/td>\nEXAMPLE<\/td>\n<\/tr>\n
=<\/td>\nChecks if operands are equal<\/td>\nMAKE = “Ford”<\/td>\n<\/tr>\n
!=<\/td>\nChecks if operands are not equal<\/td>\nMODEL != “Civic”<\/td>\n<\/tr>\n
<><\/td>\nChecks if the  two operands are equal or not.  If values are not equal<\/em> then the result is true.  This is the same as != but is “correct” under ANSI standards<\/td>\nMODEL <> “Civic”<\/td>\n<\/tr>\n
><\/td>\nChecks if the left operand is greater than the right operand<\/td>\n5 > 4 (True)<\/td>\n<\/tr>\n
<<\/td>\nChecks if the left operand is smaller than the right operand<\/td>\n5 < 4 (False)<\/td>\n<\/tr>\n
!<<\/td>\nChecks if the left operand is not smaller than the right operand<\/td>\n5 !< 4 (True)<\/td>\n<\/tr>\n
!><\/td>\nChecks if the left operand is smaller than the right operand<\/td>\n5 !> 4 (False)<\/td>\n<\/tr>\n
>=<\/td>\nChecks if the left operand is greater than or equal to the right operand<\/td>\n5 >= 4 (False)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/figure>\n
SQL also has many logical operators. \u00a0Some of the most important are listed below.<\/p>\n
\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
OPERATOR<\/td>\nDESCRIPTION<\/td>\n<\/tr>\n
ALL<\/td>\nreturns true if ALL of the subquery values meet given condition<\/td>\n<\/tr>\n
ANY<\/td>\nreturns true if ANY of the subquery values meet given condition<\/td>\n<\/tr>\n
AND<\/td>\nThe AND operator allows the existence of multiple conditions in an SQL statement’s WHERE clause<\/td>\n<\/tr>\n
BETWEEN<\/td>\nThe BETWEEN operator is used to search for values that are within a given minimum and maximum value<\/td>\n<\/tr>\n
EXISTS<\/td>\nThe EXISTS operator is used to search for the presence of a specified record<\/td>\n<\/tr>\n
IN<\/td>\nThe IN operator is used to determine if a record’s value is contained in a specified list<\/td>\n<\/tr>\n
LIKE<\/td>\nThe LIKE operator is used to search for a specified pattern<\/td>\n<\/tr>\n
NOT<\/td>\nThe NOT operator reverses the meaning of the logical operator it preceeds<\/td>\n<\/tr>\n
OR<\/td>\nThe OR operator is used to combine multiple conditions in an SQL statement’s WHERE clause.<\/td>\n<\/tr>\n
UNIQUE<\/td>\nThe UNIQUE operator returns distinct occurrences within a specified field<\/td>\n<\/tr>\n
<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/figure>\n
We are now ready to use what we’ve learned to write queries in SQL. \u00a0Let’s add in a simple where clause to our previous example:\u00a0<\/p>\n
--EXAMPLE 1\nSELECT \n  MAKE, \n  MODEL_YEAR, \n  CONDITION_CD\nFROM LOT_CARS\nWHERE MAKE = \"Ford\"\n;\n\n--EXAMPLE 2\nSELECT \n    MAKE, \n    MODEL_YEAR, \n    CONDITION_CD\nFROM LOT_CARS\nWHERE \n    CONDITION_CD BETWEEN 1 AND 3 \n    AND MODEL_YEAR <> 2017\n;<\/code><\/pre>\n
You can now go out and write your own queries in SQL! \u00a0Next time we’ll discuss joins and their use. \u00a0If you have any questions leave a comment below!<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
https:\/\/nycdatascience.com\/blog\/student-works\/introduction-to-sql-part-i\/<\/p>\n","protected":false},"author":0,"featured_media":2178,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[2],"tags":[],"_links":{"self":[{"href":"https:\/\/wealthrevelation.com\/data-science\/wp-json\/wp\/v2\/posts\/2177"}],"collection":[{"href":"https:\/\/wealthrevelation.com\/data-science\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wealthrevelation.com\/data-science\/wp-json\/wp\/v2\/types\/post"}],"replies":[{"embeddable":true,"href":"https:\/\/wealthrevelation.com\/data-science\/wp-json\/wp\/v2\/comments?post=2177"}],"version-history":[{"count":0,"href":"https:\/\/wealthrevelation.com\/data-science\/wp-json\/wp\/v2\/posts\/2177\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/wealthrevelation.com\/data-science\/wp-json\/wp\/v2\/media\/2178"}],"wp:attachment":[{"href":"https:\/\/wealthrevelation.com\/data-science\/wp-json\/wp\/v2\/media?parent=2177"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wealthrevelation.com\/data-science\/wp-json\/wp\/v2\/categories?post=2177"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wealthrevelation.com\/data-science\/wp-json\/wp\/v2\/tags?post=2177"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}