Basic SQL Concepts - Part 1

Introduction

SQL or Structured Query Language is a programming language that we use to communicate with our database. We write statements called 'queries' in SQL to create, update, and delete database objects and data stored in the database.

Database:

A database, in simple terms, is an organized collection of structured data stored usually in a computer system. Oracle is a Relational Database Management System (RDBMS) that is used as an interface to retrieve, update, and delete data stored in the database.

Schema:

In Oracle, a database schema is a logical container for data structures, called schema objects. Database users own the schemas with the same name. For example, the hr schema is owned by the hr user. We use SQL to access schema objects. Some of the schema objects are described below:

Table: Tables are entities with names and a set of columns and rows. Columns are defined with data type, name and other details and rows are the data stored in the table. Tables are a must-have in a Relational DBMS and they are physically stored in the database.

View: Views are logical structures created with one or more tables. Views are queries and do not store any data, hence they only take up space to store the query that defines the view. Views are used to hide data complexity and increase security by restricting access to tables.

Index: Indexes are optional schema objects that are created on tables or table clusters to improve performance in terms of data retrieval from a table. If a table has no index on it, the database performs a full table scan on it to retrieve the data whereas if the table is indexed, the data can be retrieved directly.

Sequences: Sequences are the schema objects that can generate unique integers. We have to have the name of the sequence, the number it should start from and the interval between numbers. There are other optional characteristics like CYCLE/NOCYCLE, CACHE/NOCACHE etc. that we can use too.

SQL Commands

Now, let's see how we can use SQL to interact with the Oracle database.

COMMENTS

First of all, let's see how to write single-line and multi-line comments in SQL. Comments are useful to increase the readability of your code and provide context. Example:

--This is a single line comment

/*
This comment is
multi-line.
*/

CREATE

We can create databases, and schema objects using CREATE command. Example:

--To create database provide database name
CREATE DATABASE mydatabase;
/* To create a table, we provide tablename, list of columns
with the data types of those columns */
CREATE TABLE mytable (id NUMBER, name VARCHAR(20));

--To create index provide index name and table on which it should be created
CREATE INDEX idx_name ON mytable(name);

/*Below statement will create a view on mytable TABLE
with all the columns present in mytable */
CREATE VIEW mycourse
AS SELECT * FROM mytable;

/* Below statement will create a sequence named id, 
which starts from 1, gets incremented by 1 and the 
max value for it is 100 */
CREATE SEQUENCE id
INCREMENT BY 1
START WITH 1
MINVALUE 1
MAXVALUE 100;

ALTER TABLE

ALTER TABLE allows us to remove or add a column from a table. We can modify any number of columns with a single query. Example:

--ALTER TABLE query to add a column to the table
ALTER TABLE mytable 
ADD contact NUMBER;

--ALTER TABLE query to drop a column to the table
ALTER TABLE mytable 
DROP COLUMN contact;

SELECT

SELECT is used to retrieve data from the database. We can use SELECT * to fetch all the data from a table or SELECT DISTINCT to get only distinct records for a particular column. Example:

--To select all the columns from a table
SELECT * FROM mytable;
--To select only desired columns
SELECT id, name FROM mytable;
--To select only distinct rows and remove the duplicates from result set
SELECT DISTINCT name FROM mytable;

INSERT INTO

This clause is used to insert data into the database. Example:

INSERT INTO mytable (id, name)
VALUES (1, 'Ellie');

WHERE

If we need to retrieve data based on a specific condition we can do that with the help of the WHERE clause. Example:

SELECT * FROM mytable
WHERE name ='Ellie';

AND, OR, IN, BETWEEN, and NOT

We can use AND, OR, IN, BETWEEN, and NOT operators when we have multiple conditions based on which we need to fetch data from a table.

We can combine two or more conditions using AND. All of the conditions must be true to fetch the desired result set.

We can use OR when only one of the conditions has to be true from multiple conditions in the WHERE clause.

We can use the IN operator when we have many values and we want to avoid using OR multiple times.

We can use BETWEEN when we want to fetch rows based on an expression that evaluates to be true between the given range. for example numbers or dates.

We can use the NOT to exclude rows from a result set based on the condition in the WHERE clause and also with any of the above operators. Example:

/* A query to select all the rows from the table 
where id is 1 and name is Ellie */
SELECT * FROM mytable
WHERE id = 1 AND name = 'Ellie';

/* A query to select all the rows from the table 
where either id is 1 or name is Ellie */
SELECT * FROM mytable
WHERE id = 1 OR name = 'Ellie';

/* A query to select all the rows from the table 
where name is either Ellie or Michael or Raven */
SELECT * FROM mytable
WHERE name IN ('Ellie', 'Michael', 'Raven');

/* A query to select all the rows from the table 
where id is between the range 1-10 */
SELECT * FROM mytable
WHERE id BETWEEN 1 AND 10;

/* A query to select all the rows from the table 
where name is not either Ellie or Michael or Raven */
SELECT * FROM mytable
WHERE name NOT IN ('Ellie', 'Michael', 'Raven');

/* A query to select all the rows from the table 
where id is not between the range 1-10 */
SELECT * FROM mytable
WHERE id NOT BETWEEN 1 AND 10;

LIKE AND WILDCARDS

If we need to search for a specific pattern for a value in a column, we can use the LIKE operator.

If we don't know the exact string value in the column, we use wildcards with the LIKE operator to substitute one or more characters. We can create and use regular expressions (regex) for our specific needs with the LIKE operator, I'll list the basic wildcard characters below:

• % is for zero or more characters

• _ is for one character

Example:

/* Below query will return rows where name column value
is Ellie */
SELECT * FROM mytable WHERE name LIKE 'Ellie';

/* Below query will return rows where name begins with letter E, 
it can contain any number of letters after E. */
SELECT * FROM mytable WHERE name LIKE 'E%';

/* Below query will return rows where name begins with letter E,
ends with letter e and has 3 characters in the middle. */
SELECT * FROM mytable WHERE name LIKE 'E___e';

/* We can combine multiple wildcard characters too.
Below query will return rows where the second letter is L, 
there is exactly one letter before L and it can have any number
of letter after L */
SELECT * FROM mytable WHERE name LIKE '_L%';

UPDATE

We can use UPDATE to update already existing records in the table. UPDATE can be used with WHERE if we want to update a specific record. Example:

UPDATE mytable
SET name ='Mike'
WHERE name = 'Michael';

ALIASES

SQL has aliases too! Sometimes the queries get too big and too complex, we can use aliases for tables, and columns to make it more readable. Aliases are important when we work with JOINS and aggregate functions like SUM(), COUNT() etc.

Aliases are temporary names that we create using AS. Example:

--Using alias for column name
SELECT name AS first_name 
FROM mytable;

DELETE, TRUNCATE and, DROP

If we need to remove a certain record based on a condition or all the rows from the table, we can use DELETE.

We can use TRUNCATE to remove all the records from a table as well. TRUNCATE is generally faster, it deletes all the rows from a table without checking for specific rows and retains the table structure.

We can use DROP to delete the entire database, a table in the database or a column in a table. DROP gets rid of the table data along with the table structure from the database.

--DELETE all the data from a table
DELETE FROM mytable;
--DELETE specific data from a table
DELETE FROM mytable
WHERE name ='Mike';

--TRUNCATE table
TRUNCATE TABLE mytable;

--DROP a database
DROP DATABASE mydatabase;
--DROP a table
DROP TABLE mytable;
--DROP a column
ALTER TABLE mytable 
DROP COLUMN contact;

Conclusion

That's all for this article! Please remember that these are just the basics of SQL, if you want me to write about any of the topics from this article in-depth, please let me know.

Questions and feedback are most welcome :)