A beginners guide to SQL with Examples – basic sql queries

Introduction

The world generates tons of data each day knowingly or unknowingly. Weather you are placing an order on Amazon,browsing on Facebook,uploading your selfie on Instagram . We are generating data in different forms and variety.So these data which is generated by you or me needs to be stored somewhere. So that we derive something useful out of it.Most of the times you store the data in a database like oracle,MySQL etc. To access them you need to know basic sql queries.

SQL stands for structured query language. It a tool to fetch data out of databases . Every database will have its own SQL . Having said that ,the syntax will be more or less similar.The basic concepts will be the same across different SQL’s.

For this post ,I am using Oracle SQL. It is probably the most used database in the world.Learning to write SQL’s will give you the power to access data and derive something useful out of it.

Audience for sql basic queries:

This post is an introductory tutorial for using Oracle SQL. Its meant for beginners who wants to learn basic sql queries or an experienced person who needs to brush up basics.

Sample Table for Practice:

To keep things simple and interesting , I will create a table called item .It will store item data such as name,price etc . I will show how run basic sql commands against it.

basic sql queries

Create table:

This command will be used to create a table in oracle.The Data_Type Refers to the type of data which gets stored in the column.It can be an alphanumeric /numbers / date etc.

Basic Syntax:
Create Table tableName {
Column_Name1 DataType1 Constraints,
Column_Name2 DataType2 Constraints,
}
Example command
CREATE TABLE item
  (
    item_id         VARCHAR(10) PRIMARY KEY,
    item_name       VARCHAR(25),
    manufacturer    VARCHAR(20),
    unit_price      INT,
    unit_of_measure VARCHAR(10),
    launch_date     DATE
  );
Result:
table ITEM created.

Insert into SQL:

This command is used to insert data into the table.Every record inserted into the table is called a row .

Basic Syntax:
INSERT INTO table
(column1, column2, ... column_n )
VALUES
(expression1, expression2, ... expression_n );
Example command
INSERT
INTO item
  (
    item_id,
    item_name,
    manufacturer,
    unit_price,
    unit_of_measure,
    launch_date
  )
  VALUES
  (
    '001',
    'Iphone XR',
    'Apple',
    399,
    'EACH',
    TO_DATE('12/01/2018', 'DD/MM/YYYY')
  );
Result:
1 rows inserted.

You can use below statements to insert multiple rows for setting up item table.

INSERT INTO item (item_id,item_name,manufacturer,unit_price,unit_of_measure,launch_date) values ('002','Ipad pro','Apple',599,'EACH',TO_DATE('05/06/2017', 'DD/MM/YYYY'));
INSERT INTO item (item_id,item_name,manufacturer,unit_price,unit_of_measure,launch_date) values ('003','Ipad mini','Apple',500,'EACH',TO_DATE('24/01/2015', 'DD/MM/YYYY'));
INSERT INTO item (item_id,item_name,manufacturer,unit_price,unit_of_measure,launch_date) values ('004','Windows 10','Microsoft',150,'UNIT',TO_DATE('29/07/2019', 'DD/MM/YYYY'));
INSERT INTO item (item_id,item_name,manufacturer,unit_price,unit_of_measure,launch_date) values ('005','Soundsport','Bose',199,'EACH',TO_DATE('12/07/2017', 'DD/MM/YYYY'));
INSERT INTO item (item_id,item_name,manufacturer,unit_price,unit_of_measure,launch_date) values ('006','Rubberbands','XYZ corporation',2,'DOZEN',TO_DATE('17/06/2011', 'DD/MM/YYYY'));
INSERT INTO item (item_id,item_name,manufacturer,unit_price,unit_of_measure,launch_date) values ('007','Cannon E95','Cannon corporation',587,'EACH',TO_DATE('12/09/2014', 'DD/MM/YYYY'));

Select command:

You will use Select command to select records from the table. The expression will be select * if you want to select all the columns of the table .

Basic Syntax:
SELECT expression
FROM tables
[WHERE conditions];
Example command
SELECT * FROM item;
Result
ITEM_ID ITEM_NAME MANUFACTURER UNIT_PRICE UNIT_OF_MEASURE LAUNCH_DATE
001 Iphone XR Apple 399 EACH 12-JAN-18
002 Ipad pro Apple 599 EACH 05-JUN-17
003 Ipad mini Apple 500 EACH 24-JAN-15
004 Windows 10 Microsoft 150 UNIT 29-JUL-19
005 Soundsport Bose 199 EACH 12-JUL-17
006 Rubberbands XYZ corporation 2 DOZEN 17-JUN-11
007 Cannon E95 Cannon corporation 587 EACH 12-SEP-14

Update Command:

This command is used to update a row of a table.You can use WHERE clause to specify the criteria and update only those records.

Basic Syntax:
UPDATE tableName SET columnName=Value WHERE expression1 ;
Example command
UPDATE item SET unit_price=399 WHERE item_id='001';
Result:
1 rows updated.

Delete command:

This SQL is used to delete rows from the table matching a criteria.In this example, I want to delete all the records which is older than 5 years.So i will use the expression sysdate - 1825 . Because 5 years means 1825 days.

Basic Syntax:
DELETE FROM tableName WHERE expression1 ;
Example command
DELETE FROM item WHERE launch_date < sysdate - 1825;
Result
3 rows deleted.

DROP TABLE command

DROP TABLE command deletes the table from the database.

Basic Syntax:
DROP TABLE tableName;
Example command
DROP TABLE item;
Result
table ITEM dropped.

ADD Column command

This SQL can be used to add a column to a table. In the example I am adding a new column description of type varchar(100) . The column can hold 100 characters.

Basic Syntax:
ALTER TABLE tableName ADD columnName datatype;
Example command
ALTER TABLE item ADD description VARCHAR(100);
Result
table ITEM altered.

MODIFY Column Command

This SQL can be used to modify an existing column .In the example , I am changing the datatype of the description column.Previously it could hold upto 100 characters and now it can hold 150.

Basic Syntax:
ALTER TABLE tableName MODIFY column datatype;
Example command
ALTER TABLE item MODIFY description VARCHAR(150);
Result
table ITEM altered.

DELETE column command:

Use this SQL to delete any column . Be careful if the column is having constrains such as foreign key's with tables.Oracle will throw an error while deleting such columns.

Basic Syntax:
ALTER TABLE tabeName DROP COLUMN columnName;
Example command
ALTER TABLE item DROP column description;
Result
table ITEM altered.

CREATE INDEX command

Indexes enable you to retrieve faster results.If your table has less than 10,000 records you may not need it.But if your table has millions of records , you will require indexes for faster responses.

Basic Syntax:
CREATE INDEX indexName ON tableName(columnName1,columnName2, ...);
Example command
CREATE INDEX item_name_index ON item (item_name );
Result
index ITEM_NAME_INDEX created.

DROP INDEX command

This SQL can be used to drop an index.

Basic Syntax:
DROP INDEX indexName ;
Example command
DROP INDEX item_name_index;
Result
index ITEM_NAME_INDEX dropped.

DESCRIBE table command

Describe table can be used to know the structure of your table to get information such as:

  • Name of columns
  • datatype of columns
  • Constraints such as nullable,non nullable
  • Primary key, foreign keys
Basic Syntax:
DESC tableName;
Example command
DESC item;
Result
Name            Null     Type         
--------------- -------- ------------ 
ITEM_ID         NOT NULL VARCHAR2(10) 
ITEM_NAME                VARCHAR2(25) 
MANUFACTURER             VARCHAR2(20) 
UNIT_PRICE               NUMBER(38)   
UNIT_OF_MEASURE          VARCHAR2(10) 
LAUNCH_DATE              DATE    

TRUNCATE table command

This SQL command can be used to empty the table in one shot.

Basic Syntax:
TRUNCATE TABLE tableName;
Example command
TRUNCATE TABLE item;
Result
table ITEM truncated.

ALIAS command

ALIAS is basically an operator which can be used to give meaningful names to your columns while viewing the results.

Basic Syntax:
columnName AS aliasName
Example command
SELECT item_name AS ProductName,unit_price AS Price FROM item;
Result
PRODUCTNAME PRICE
Iphone XR 399
Ipad pro 599
Ipad mini 500
Windows 10 150
Soundsport 199
Rubberbands 2
Cannon E95 587

ORDER BY command

ORDER BY clause can be used to sort the results in ascending or descending order.Trust me, you will use this clause a lot while working with databases.This is one of the most basic sql queries.

Basic Syntax:
SELECT * FROM tableName ORDER BY columnName ASC/DESC;
Example command
SELECT * FROM item ORDER BY launch_date DESC;
Result
ITEM_ID ITEM_NAME MANUFACTURER UNIT_PRICE UNIT_OF_MEASURE LAUNCH_DATE
004 Windows 10 Microsoft 150 UNIT 29-JUL-19
001 Iphone XR Apple 399 EACH 12-JAN-18
005 Soundsport Bose 199 EACH 12-JUL-17
002 Ipad pro Apple 599 EACH 05-JUN-17
003 Ipad mini Apple 500 EACH 24-JAN-15
007 Cannon E95 Cannon corporation 587 EACH 12-SEP-14
006 Rubberbands XYZ corporation 2 DOZEN 17-JUN-11

GROUP BY command

You can use GROUP BY clause to group the result set by one or more columns.In the example , I want to find out number of items for every manufacturer. Hence , I am using GROUP BY manufacturer clause.

Basic Syntax:
SELECT columnName,COUNT(*) FROM tableName GROUP BY columnName;
Example command
SELECT manufacturer,COUNT(*) FROM item GROUP BY manufacturer;
Result

MANUFACTURER COUNT(*)
Bose 1
Apple 3
XYZ corporation 1
Cannon corporation 1
Microsoft 1

LIKE command

The LIKE operator is mostly used in a searching context. It supports pattern matching. In the example query , I am searching for all records where manufacturer has the text 'Cannon'.

Basic Syntax:
expression LIKE pattern [ ESCAPE 'escape_character' ]
Example command
SELECT * FROM item WHERE manufacturer LIKE 'Cannon%';
Result

ITEM_ID ITEM_NAME MANUFACTURER UNIT_PRICE UNIT_OF_MEASURE LAUNCH_DATE
007 Cannon E95 Cannon corporation 587 EACH 12-SEP-14

HAVING command

HAVING clause is used along with GROUP BY to restrict the result set with some condition.In the example , I want to find out the manufacturers whose items are worth more than $1000 cumulatively.

Basic Syntax:
SELECT expression1, .. expression_n, 
       aggregate_function (aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1,.. expression_n
HAVING having_condition;
Example command
SELECT manufacturer,
  SUM(unit_price)
FROM item
GROUP BY manufacturer
HAVING SUM(unit_price)>1000;
Result

MANUFACTURER SUM(UNIT_PRICE)
Apple 1498

SQL Functions:

Count command

Basic Syntax:
SELECT COUNT(expression) from tableName;
Example command
SELECT COUNT(*) FROM item;
Result
COUNT(*)
------------
7

SUM command

Basic Syntax:
SELECT SUM(columnName) AS aliasName FROM tableName WHERE expression;
Example command
SELECT SUM(unit_price) AS total FROM item WHERE manufacturer='Apple';
Result
TOTAL
----------
1498

Read more on Java8 here.

5 Responses

Leave a Reply

Your email address will not be published.