Learn Sql With Me sql Commands-part 1

Learn Sql With Me

sql Commands-part 1

I finished school and thought 'yaay ,I never have to read again!'. Lies! I know for sure that for as long as I am around, I will keep up-skilling. The good thing though is that I now have the time to focus on the things I am actually interested in. Hop on as we learn and hopefully you will keep me accountable in my learning journey .I hope to document every bit of it.

INTRODUCTION TO SQL

SQL(Structured Query Language) also pronounced as SEQUEL,is a language used to interact with databases. Think of a database as a collection of tables. We have SQL and NoSQL databases but i will focus on SQL databases.

SQL BEST PRECTICES

SQL queries are not case-sensitive therefore characters can be written in upper-case and lower-case. The order of the key words also does matter! The following sets of commands will still achieve the same goal:

SELECT account_id
FROM orders

The above piece code is similar to:

select account_id
from orders

And is also similar to

SelEcT aCcouNt_Id
FRoM oRDers

It is important however, to capitalize SELECT and FROM, while we leave table and column names in lower case like in the first example.This is because even though SQL is case-insensitive, it is common and best practice to capitalize all SQL commands, like SELECT and FROM, and keep everything else in your query lower case.

Capitalizing command words makes queries easier to read, which will matter more as you write more complex queries.

sql-programmers-be-like-hitting-caps-lock-before-starting-work.jpg

One other note: The text data stored in SQL tables can be either upper or lower case, and SQL is case-sensitive in regard to this text data.

Avoid Spaces in Table and Variable Names It is common to use underscores and avoid spaces in column names. It is a bit annoying to work with spaces in SQL. In Postgres if you have spaces in column or table names, you need to refer to these columns/tables with double quotes around them (Ex: FROM "Table Name" as opposed to FROM table_name). In other environments, you might see this as square brackets instead (Ex: FROM [Table Name]).

Use White Space in Queries SQL queries ignore spaces, so you can add as many spaces and blank lines between code as you want, and the queries are the same.

SELECT... FROM...

SELECT indicates which column(s) you want to be given the data for. FROM specifies from which table(s) you want to select the columns. To fetch the data from all columns in the table, you use "*", like so:

SELECT * FROM orders

SELECT *
FROM orders
LIMIT BY 100

To fetch the data from a given number of columns in the table, you list the column names .This SELECT statement select three columns from the orders table.

SELECT id,account_name ,account_number
FROM orders
LIMIT BY 100

LIMIT BY

LIMIT statement is useful when you want to see just the first few rows of a table. This can be much faster for loading than if we load the entire dataset. An example of showing just the first 10 rows of the orders table with all of the columns might look like the following:

SELECT *
FROM orders
LIMIT BY 10

ORDER BY

The ORDERBY statement is used when we want to sort the query results using data in a given column. When you use ORDER BY in a SQL query, your output will be sorted that way, but then the next query you run will encounter the unsorted data again. The ORDER BY statement always comes in a query after the SELECT and FROM statements, but before the LIMIT statement.

Pro Tip Remember DESC can be added after the column in your ORDER BY statement to sort in descending order, as the default is to sort in ascending order.

SELECT id,account_name ,account_number
FROM orders
ORDER BY account_number
LIMIT BY 100

The query below will order the account_numbers in descending order

SELECT id,account_name ,account_number
FROM orders
ORDER BY account_number DESC
LIMIT BY 100

Querying more than one row

It is also possible to query a dataset using two columns. In this case, the query will order based on the most left column first then proceed to the next column. As shown in the query below, the query will order the results using the is column in descending order followed by the account_number column. ''' SELECT id,account_name ,account_number FROM orders ORDER BY id desc,account_number LIMIT BY 100 '''

ALIAS

Creating a new column that is a combination of existing columns is known as a derived column (or "calculated" or "computed" column). Usually you want to give a name, or "alias," to your new column using the AS keyword. This derived column, and its alias, are generally only temporary, existing just for the duration of your query. The next time you run a query and access this table, the new column will not be there. If you are deriving the new column from existing columns using a mathematical expression, then these familiar mathematical operators will be useful:

  • (Multiplication)
  • (Addition)
  • (Subtraction) / (Division) Consider this example:
SELECT id, ( discount/price * 100%) as disc_percent
FROM orders
ORDER BY account_number
LIMIT BY 100

WHERE

The where keyword introduces a condition that has to be met. When the keyword is a string we use single quotes. Using the WHERE statement, we can display subsets of tables based on conditions that must be met. You can also think of the WHERE command as filtering the data. Common symbols used in WHERE statements include:

(greater than)

< (less than)

= (greater than or equal to)

<= (less than or equal to)

= (equal to)

!= (not equal to)

The WHERE statement can also be used with non-numeric data. We can use the = and != operators here. You need to be sure to use single quotes with the text data, not double quotes.

Commonly when we are using WHERE with non-numeric data fields, we use the LIKE, NOT, or IN operators. We will see those before the end of this lesson!

SELECT id, account_number
FROM orders
WHERE id=1001
LIMIT BY 100

Logical Operators

In the next concepts, you will be learning about Logical Operators. Logical Operators include:

  • LIKE This allows you to perform operations similar to using WHERE and =, but for cases when you might not know exactly what you are looking for.

  • IN This allows you to perform operations similar to using WHERE and =, but for more than one condition.

  • NOT This is used with IN and LIKE to select all of the rows NOT LIKE or NOT IN a certain condition.

  • AND & BETWEEN These allow you to combine operations where all combined conditions must be true.

  • OR This allows you to combine operations where at least one of the combined conditions must be true. arithmetic operations

LIKE

The LIKE operator is extremely useful for working with text. You will use LIKE within a WHERE clause. The LIKE operator is frequently used with %. The % tells us that we might want any number of characters leading up to a particular set of characters or following a certain set of characters. The query below selcts fileds that contain the words 'aol' in it.

SELECT id, account_number
FROM orders
WHERE name LIKE '% AOL%'
LIMIT BY 10

IN

The IN operator is useful for working with both numeric and text columns. This operator allows you to use an =, but for more than one item of that particular column. We can check one, two or many column values for which we want to pull data, but all within the same query. In the upcoming concepts, you will see the OR operator that would also allow us to perform these tasks, but the IN operator is a cleaner way to write these queries.

SELECT name, primary_poc, sales_rep_id
FROM accounts
WHERE name IN ('Walmart', 'Target', 'Nordstrom');

NOT

The NOT operator is an extremely useful operator for working with the previous two operators we introduced: IN and LIKE. By specifying NOT LIKE or NOT IN, we can grab all of the rows that do not meet a particular criteria.

NOT IN

SELECT name, primary_poc, sales_rep_id
FROM accounts
WHERE name NOT IN ('Walmart', 'Target', 'Nordstrom');

NOT LIKE

SELECT name
FROM accounts
WHERE name NOT LIKE 'C%';

AND AND BETWEEN

The AND operator is used within a WHERE statement to consider more than one logical clause at a time. You may link as many statements as you would like to consider at the same time. This operator works with all of the operations we have seen so far including arithmetic operators (+, *, -, /). LIKE, IN, and NOT logic can also be linked together using the AND operator.

BETWEEN Operator Sometimes we can make a cleaner statement using BETWEEN than we can using AND.

Instead of writing :

WHERE column >= 6 AND column <= 10

we can instead write, equivalently:

WHERE column BETWEEN 6 AND 10

OR

Similar to the AND operator, the OR operator can combine multiple statements. Each time you link a new statement with an OR, you will need to specify the column you are interested in looking at. You may link as many statements as you would like to consider at the same time. This operator works with all of the operations we have seen so far including arithmetic operators (+, *, -, /), LIKE, IN, NOT, AND, and BETWEEN logic can all be linked together using the OR operator.

When combining multiple of these operations, we frequently might need to use parentheses to assure that logic we want to perform is being executed correctly. The video below shows an example of one of these situations.

SELECT *
FROM orders
WHERE standard_qty = 0 AND (gloss_qty > 1000 OR poster_qty > 1000);

That is it for part one.You are well on your way to master SQL.Next up we will do SQL JOINS.