What is SQL…generally…

SQL stands for Structured Query Language. What does that mean? Better yet, how do you pronounce SQL? Well, originally it was named Structured English Query Language allowing for the acronym SEQUEL. It was later changed to SQL for copyright purposes.

What do you use SQL for? It is a language, so as with any language it allows you to create a series of commands. SQL is used to command a the data in a database. More specifically, it can allow you to navigate and retrieve data from a relational database management system, which is a common choice for the storage of information including, financial records, health records, inventory, logistical information, and other data.

SQL can be subdivided into different elements:

  • Clauses – components of statements and queries
  • Expressions – which produce either scalar values, or tables consisting of columns and rows of data
  • Predicates –  which specify conditions that can be evaluated to SQL and are used to limit the effects of statements and queries, or to adjust program flow
  • Queries – which retrieve the data based on specific criteria
  • Statement – which may have an effect on schemata and data, or may control transactions, program flow, connections, sessions, or diagnostics
  • Insignificant whitespace – take a guess

Like other languages, SQL works with numerous data types. Here are the most common Data types:

char(size) Fixed-length character string. Size is specified in parenthesis. Max 255 bytes.
varchar(size) Variable-length character string. Max size is specified in parenthesis.
number(size) Number value with a max number of column digits specified in parenthesis.
date Date value
number(size,d) Number value with a maximum number of digits of “size” total, with a maximum number of “d” digits to the right of the decimal.

Now that a basic understanding of SQL is had, here are some basic commands.

Select – used to query the database and retrieve selected data

From – specifies the table that will be queried

Where – this clause specifies which data values or rows will be returned, based on criteria. Can use conditional selections like: =, <, >, <=, >=, <>

Like – pattern matching operator used in conditional selection of where clause.

select first, last

  from empinfo

  where last LIKE ‘%s’;

This statement will match any last names that end in a ‘s’.

Create Table – used to create new table

create table “tablename”

     (“column1” “data type”,

            “column2” “data type”,

                 “column3” “data type”);

Insert – statement is used to insert or add row of data into a table

insert into “tablename”

(first_column,…last_column)

 values (first_value,…last_value);

Update – Statement is used to update or change records that match a specified criteria

update “tablename”

set “columnname” =  “newvalue”

[,”nextcolumn” =  “newvalue2″…]

where “columnname”

 OPERATOR “value”

[and|or “column”

 OPERATOR “value”];

Delete – statement is used to delete records or rows from the table

delete from “tablename”

where “columnname”

 OPERATOR “value”

[and|or “column”

 OPERATOR “value”];

Drop Table – command used to delete a table and all rows in the table

drop table “tablename”

Aggregate Functions:

MIN returns the smallest value in a given column
MAX returns the largest value in a given column
SUM returns the sum of the numeric values in a given column
AVG returns the average value of a given column
COUNT returns the total number of values in a given column
COUNT(*) returns the number of rows in a table

Group By – will gather all of the rows together that contain data in the specified column and will allow aggregate functions to be performed

Having – allows you to specify conditions on the rows for each group. Should follow the GROUP BY clause

Order By – allows you to display the results of your query in a sorted order

In – select info “in” certain categories. Ex: last name

Between – select info between certain categories. Ex: between ages

Operators:

+ addition
subtraction
* multiplication
/ division
% modulo

*The modulo operator determines the integer remainder of the division.

ABS(x) returns the absolute value of x
SIGN(x) returns the sign of input x as -1, 0, or 1 (negative, zero, or positive respectively)
MOD(x,y) modulo – returns the integer remainder of x divided by y (same as x%y)
FLOOR(x) returns the largest integer value that is less than or equal to x
CEILING(x) or CEIL(x) returns the smallest integer value that is greater than or equal to x
POWER(x,y) returns the value of x raised to the power of y
ROUND(x) returns the value of x rounded to the nearest whole integer
ROUND(x,d) returns the value of x rounded to the number of decimal places specified by the value d
SQRT(x) returns the square-root value of x

Join – allow you to link data from two or more tables together into a single query result

SELECT “list-of-columns”

FROM table1,table2

WHERE “search-condition(s)”

SQL is a great skill to have in today’s world. Manipulating and navigating databases is very useful as there is more data being recorded and more analysis is being done. A 2016 study done by Coding Dojo, showed that SQL was the most listed coding language on Indeed and their subsequent 2017 study saw the number of job descriptions increase by 50,000. So if a job is what you seek, SQL skills are on fleek.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s