First published on http://tugrul.dbsdataprojects.com on 25th of March 2017.
Sql stands for Structured Query Language and is being used to query and manipulate relational databases. Most of the Relational Database Management Systems use SQL as standard database language. I will be using MS SQL in these examples and learning process.
Dr Edgar F. Codd is known as the “Codd Father” of the relational databases. He described a relational model for databases in 1970. First SQL appeared in 1974 and IBM has worked to develop the ideas of Codd and released a product System/R. In 1986, IBM developed first prototype of relational database and it was standardized by ANSI.
Capabilities of SELECT statements
SELECT statements can give us a projection, we can get a subset of a column. Secondly, you can filter the number of rows with SELECT and also you can join different tables by primary and foreign keys. It allows to get data from different tables and show as a table.
Basis SELECT statement identifies the columns o be displayed and you also need to add FROM to tell which tables you will get the data from.
If you want to select all columns you just need to put in “*” after SELECT, i.e. SELECT * FROM result.
You can use IDENTITY to create an auto increment, it is very useful for Primary Key. If you type in IDENTITY(1,1) your column will start with “1” and it will increase by 1 each time you enter a new value.
You can see all tables created with this code;
You can also get subsets from your tables. That is the projection part of SELECT statement.
This will show you below table in SQL Server Management Studio. You can change the order of columns by changing the order in SELECT statement
SQL Statements – Things To Remember
- SQL statements are not case sensitive.
- SQL statements can be entered on one or more
- Keywords cannot be abbreviated or split across
- Clauses are usually placed on separate
- Indents are used to enhance
- In SQL Developer, SQL statements can be optionally terminated by a semicolon (;). Semicolons are required when you execute multiple SQL
- In SQL*Plus, you are required to end each SQL statement with a semicolon (;).
Arithmetic Operators in SQL
You can do any arithmetic operations in SQL such as +,-,*,/.
Also you can add values to a column as a new column. You can give this column a name by adding AS to the SELECT statement.
It is better to check your outputs before getting your reports/tables. In below case although we want to get 48 we will get a result of 15. If we add () after 12 the problem will be solved. Be careful with the calculations you do.
NULL values are different than having “0” in the field. Zero means there is a value but it is zero, null means there is no value in that row/column. Below visualization explains the difference between zero and NULL perfectly.
While writing queries, you need to be careful about NULL values. If there is a NULL value while querying a database you will get a different answer than expected answer. As NULL means undefined value, SQL will perpetrate that differently and you will get below answers in a Boolean way.
DISTINCT gives you the first occurrence of a record in the table and will remove the duplicates in the result. You can use DISTINCT in SELECT statement after SELECT keyword.
As in below example; if you just SELECT moduleid FROM result, you will get 8 rows including duplicates. Using DISTINCT will give you the 6 distinct values by removing duplicates.
You can also use DISTINCT in different functions such as COUNT and can count the results without duplicates.
You can use CONCAT statement to concatenate different values from different columns and add strings to the result. Let’s create table called employees with below data in it;
After creating this table we can use CONCAT to create a sentence which gives us the employee ID of each employee.
So, that wraps up the first part of Learning SQL, where we created tables, did arithmetic operations, concatenate, get unique/distinct values, and also learnt what are the NULL values and what you need to be careful about them.