SQL 101: Introduction to SQL

What is SQL?

Many are the times you've heard the acronym SQL maybe from your friends, colleagues or from your teachers, but what really is SQL? SQL stands for Structured Query Language and it's the lingua franca that is used to manage, create, manipulate, and retrieve information/data from databases. It was developed in the 1970s by the IBM computer scientists.

now that you know what is SQL, what does it really do? SQL can perform various tasks in a database which include:-

• execute queries against databases.
• create new tables in databases.
• insert records in databases.
• update records in a database.
• Create and maintaindatabase users.
• delete records.
• retrieve data from databases e.t.c.

SQL is a very effective, easy to learn and use language. It is also very funtionaly complete due to it's ability to let users define, retrieve and manipulate data in tables.

SQL Statements

Statements in SQL are a set of instructions that consists of identifiers, parameters, variables, data types and SQL reserved key words. An SQL statement must compile successfully. e.g DELETE TABLE Users

1. Data Manipulation Language(DML)
Includes:-
SELECT- Retrieves certain records from one or more tables.
INSERT- Creates a new record.
UPDATE- Modifies an existing record.
DELETE- Deletes particular record(s).
MERGE - combines the separate INSERT, UPDATE, and DELETE statements into a single SQL query.

2.Data Definition Language(DDL)
Includes:-
CREATE- Creates a new table, a view of a table, or other object in the database.
ALTER - Modifies an existing database object, e.g a table.
DROP - Deletes an entire table, a view of a table or other objects in the database.
RENAME- Used together with ALTER to modify objects in a database.
TRUNCATE- Deletes all data from the table.
COMMENT - Starts with /* and ends with */, this part of the code is not executed.

3.Data Control Language(DCL)
Includes:-
GRANT - Gives a privilege to user(s).
REVOKE- Takes backs privileges that were previously granted to the user.

4.Transaction Control Language(TCL)
Includes:-
COMMIT - Stores changes invoked by a transaction to the database.
ROLLBACK- Reverts all the changes that were made since the last COMMIT.
SAVEPOINT- Used together with Rollback to get a certain transaction at a particular point.

Writing SQL statements.

While writing SQL Statements it's good to note:-
i. SQL statements are not case sensitive.
ii. SQL can be entered on many lines.
iii. Keywords cannot be split across lines.
iv. Clauses are usually placed on separate lines for readability and ease of editing.
v. Indents make it more readable.
vi. Keywords may be entered in caps and all others in lowercase.

Why Learn SQL?

If you're a professional in the software development domain, or a student who wants to become a Software Engineer, SQL is a very essential query language that you should equip yourself with. In most application softwares, developers tend to use SQL to store and manipulate data. Also, in Most Relational Database Management Systems(RDBMS) like MYSQL,Oracle, Postgres, Sybase, MS Access use SQL as their standard database language.

How SQL works.

Let's say you're executing SQL commands for any given SQL task, the system on which you're running the code determines which is the best way to carry out your request while the SQL engine interprets the code.

Sounds like a complicated process but it's not, now let's see the steps that are involved in Query Processing(this process involves translating the High Level SQL Queries into low level expressions that are used in the physical level of the file system, as well as in the query optimization and of course in the actual execution of the query).

Step-1:

Parser-

During this stage, the database performs the following checks- Syntax check, and Semantic check, this is after converting the query into relational algebra.

Syntax Check:

involves checking whether the rules of writing an SQL command have been satisfied/followed ( This rules are what we call Syntax)
e.g SELECT * FORM students
The above command can't be executed and thus will result into an error, this is due to the mispelling of the keyword FROM.

Semantic Check:

During this check, the parser determines whether a statement is meaningful or not. Example: Let's say you requested for a table named Students from the database but you haven't created it yet which technically means it doesn't exist, this check is performed by Semantic Check

Step-2

Optimizer:

During the optimization stage, database must perform a hard parse(is when your SQL commands are re-loaded into the shared pool)for atleast one unique DML(Data Manipulation Language) statement and of course perform optimization during the parse.

Step-3

Execution Engine:

The query is finally executed and the output is displayed.

Hands-on SQL Practicals.

Now let's get down to the nitty-gritty aspect of SQL.

Creating Tables

In SQL you create tables using the CREATE TABLEstatement. When creating tables, you must provide three basic essentials:

• Table name.
• Column names.
• Data types for each column.

Guidelines for creating tables

Table and column naming rules

• Must start with a letter, which is followed by a sequence of letters, numbers,_,#,0r $.
• Must be 1 to 30 characters long.
• Must not be an SQL reserved word.

data types

• VARCHAR2(n): Variable length charater string up to n characters.

• CHAR(n): Fixed length charater string of n characters.

• NUMBER(n): Integer number of up to n digits.

• NUMBER(precision, scale): Fixed-point decimal number. “precision” is the total number of digits; “scale” is the number of digits to the right of the decimal point. The decimal point is not counted.

• NUMBER: Floating-point decimal number.

• DATE: DD-MON-YY (or YYYY) HH:MM:SS A.M. (or P.M.) form date-time.

• LONG: Variable-length character string up to 2 GB.

• NCHAR: LONG for international character sets (2-byte per character).

• CLOB: Single-byte ASCII character data up to 4 GB.

• BLOB: Binary data (e.g., program, image, or sound) of up to 4 GB.

• BFILE: Reference to a binary file that is external to the database (OS file).

• RAW(size) or LONG_RAW: raw binary data.

• ROWID: Unique row address in hexadecimal format.

Example:
CREATE TABLE employees
(
employee_id number(7) not null,
first_name varchar2(20),
last_name varchar2(20),
cellphone varchar2(12),
email varchar2(20),
hire_date date,
job_id varchar2(5),
salary number(12,2),
manager_id number(6),
department_id number(4)
);

Adding data into tables

You use the key word INSERT to add data into any table in SQL.
Example:
INSERT INTO employees
VALUES(1000,'Simon','Otieno','0722456789','[e-mail]','01-jan-90','5500',32000,5000,10);

INSERT INTO employees
VALUES(1001,'Alice','Mwangi','0720766659','[e-mail]','02-feb-80,'5600',42000, 5000,10);

Retrieving data from database objects using SELECT Statement

As mentioned earlier, SELECT is used to retrieve data from the database. With the SELECT statement you can have the following capabilities.

• Projection- choose columns/fields from a table through a query.
• Selection- choose rows in a table.
• Joining – bring together data that is stored in different tables by specifying the link between them.

Examples:
SELECT * FROM employees;

SELECT employee_id,first_name,last_name, email , job_id, salary FROM employees;

Arithmetic Expressions in SQL

Arithmetic expressions in SQL perfom arithmetic operations on the numeric operads/values stored in the database tables.

Expressions used include:-
+ - for Addition.
- - for Subtraction operations.
* - for multiplication.
/ - for division.
% - for modulus.

Example:
SELECT employee_id, first_name,last_name, salary, salary + 3000 FROM employees;

SELECT employee_id, first_name,last_name, salary, 12* salary + 700 FROM employees;

SELECT employee_id, first_name,last_name, salary, 12* (salary + 700) FROM employees;

SELECT employee_id, first_name,last_name, salary, 12* (salary - 2000) FROM employees;

Restricting and sorting data using the SELECT statement.

use of the WHERE clause

The WHERE clause is used to filter records. It only extracts those records that fulfill a specified condition.

Comparison conditions

= -Equal to.
> -Greater than.
>=-Greater than or equal to.
< -Less than.
<=-Less than or equal to.
<>-Not equal to.
IS Null is a null value.
IN (set) Match any of the list.

examples:
SELECT employee_id “Employee ID”,first_name “First Name”,last_name “Last Name”, email “Email” , job_id “Job ID”,salary “ Monthly Pay “ FROM employees WHERE employee_id=1000;

SELECT employee_id “Employee ID”,first_name “First Name” ,last_name “Last Name”, email “Email” , job_id “Job ID”,salary “ Monthly Pay “ FROM employees WHERE salary > 10000;

SELECT employee_id “Employee ID”,first_name “First Name” ,last_name “Last Name”, email “Email” , job_id “Job ID”,salary “ Monthly Pay “ FROM employees WHERE salary IN(10000,20000,30000);

Logical Conditions

AND- Returns true if both are true
OR- Returns true if one is true
NOT- Return true if condition is false

Examples:
SELECT employee_id,last_name FROM employees WHERE salary >=10000 AND manager_id=5000;

SELECT employee_id,last_name FROM employees WHERE department_id NOT IN(90,60,30);

Using the ORDER BY Clause.

This keyword, sorts out the recordes in a particular order. By default, it sorts records in ascending order.

Example:
SELECT last_name,job_id, department_id FROM employees ORDER BY hire_date DESC;

SELECT last_name,job_id, department_id FROM employees ORDER BY hire_date ASC;

NB : asc=ascending, desc=descending.

UPDATE COMMAND
As mentioned earlier in the article, this command is used to update data in given tables.

examples:
UPDATE employees
SET salary= 50000
WHERE employee_id=1001;

UPDATE employees
SET last_name='Opiyo';
WHERE employee_id=1000;

DELETE COMMAND
It is Used to delete or remove records from tables in a database.

example:
DELETE from employees
where employee_id=1000;

ROLLBACK COMMAND.
To undo some transactions in the database,ROLLBACK Command is used. It is used together with data manipulation language(DML) commands.

example:
ROLLBACK;

COMMIT COMMAND.
Ensures that records are permanently saved. used with data manipulation language commands.

example:
COMMIT;

SQL Constrains

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.
They iclude:-

• NOT NULL: Ensures that the column contains no null or empty values.

Example:
CREATE TABLE Sales (
Sale_Id int NOT NULL,
Sale_Amount int NOT NULL,
Vendor_Name varchar(255) NOT NULL,
Sale_Date date,
Profit int
);

• UNIQUE: Requires that every value must be unique.

Example:
CREATE TABLE employees2
(
employee_id number(6),
last_name varchar2(20) not null,
email varchar2(20),
salary number(10,2),
hire_date date not null,
constraint emp_email_uk unique(email)
);

• PRIMARY KEY: creates a primary key for the table. Only one primary key can be created for each table.

example:
CREATE TABLE Sales (
Sale_Id int NOT NULL,
Sale_Amount int NOT NULL,
Vendor_Name varchar(255),
Sale_Date date,
Profit int,
PRIMARY KEY (Sale_Id)
);

• FOREIGN KEY: designates a column or combination of columns as a foreign key and establishes a relationship between a primary key or a unique key in the same table or different table.

example:
CREATE TABLE employees3
(
employee_id number(6) constraint emp_id_pk primary key,
last_name varchar2(20) not null,
first_name varchar2(20),
salary number(10,2),
hire_date date not null,
department_id number(4) constraint emp_dept_fk foreign key(department_id) references department (department_id);

From the above SQL commands, the table department must exist with primary key on department_id.

• CHECK:The CHECK constraint is used to ensure that all the records in a certain column follow a specific rule.

Example:
CREATE TABLE Sales (
Sale_Id int NOT NULL UNIQUE,
Sale_Amount int NOT NULL,
Vendor_Name varchar(255) CHECK (Vendor_Name<> ’ABC’),
Sale_Date date,
Profit int
);

When you have constrains in place on columns, an error is returned if you try to violate the constrant rule.

ALTER TABLE Statements.

This command is used to:-
i. Add a new column to a table.
ii. Modify an existing column.
iii. Define default value for a new column.
iv. Drop a column from a table.

Example:
ALTER TABLE employees
ADD constraint emp_id_pk primary key;

ALTER TABLE employees2
DROP constraint emp_dept_fk;

Notice the ADD and DROP constraint commands, the former is used to create a UNIQUE, PRIMARY KEY, FOREIGN KEY, or CHECK constraint while the latter is used to delete the same. This is done only after a table is already created.

DATA OBJECTS

Objects are used in databases to store or reference data. An object can only be accessed by using its identifier. There are various data objects in SQL, they include:-

• Table-Basic unit of storage.
• View-Logically represents subsets of data from one or more tables.
• Sequence-GenerateS numeric values.
• Index-Improves the performance of some queries.
• Synonyms-Gives alternative names to objects.

Let's look at VIEW.

VIEW.

This is a logical table based on a table or another view.

Why use VIEW?

1. To restrict data access.
2. To make complex queries easy.
3. To provide data independence.
4. To present different views of the same data.

creating a view:
CREATE VIEW emp1
AS SELECT * FROM employees;

To remove a view use the DROP command.
e.g DROP VIEW emp1