Author: Moulton Kendall

SQL stands for Structured Query Language. SQL is a computer language used to store, manipulate and retrieve data in the relational databases.

SQL PARTS:

SQL Data Definition Language:

The important commands in DDL are

SQL Data Manipulation Language:

The important commands in DML are

STANDARD SQL COMMANDS:

Although there are many different versions of SQL available but all of them support the standard SQL commands. The standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to accomplish almost everything that one needs to do with a database.

SQL IN ACTION

Let us develop a simple database with three tables USERS, GAME and USER_GAME.

USER - Stores information about user
GAME - Stores information about games
USER_GAME - Stores information about games purchased by the user.

SQL - CREATE DATABASE:

You can create a SQL database by executing a simple command

CREATE DATABASE MY_DB; will create the database MY_DB. If the database already exists then it will generate error.
CREATE DATABASE IF NOT EXISTS MY_DB; creates the database if it does not exist previously.

Apply IF NOT EXISTS check while creating database or tables.

SQL - USE DATABASE:

USE MY_DB; SQL USE command selects the database.

SQL - DROP DATABASE:

DROP DATABASE MY_DB;

SQL - CREATE TABLE:

CREATE TABLE IF NOT EXISTS USERS(
id INTEGER NOT NULL AUTO_INCREMENT,
fname VARCHAR(45) NOT NULL,
lname VARCHAR(45),
country VARCHAR(45) NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE IF NOT EXISTS GAME(
gameID INTEGER NOT NULL AUTO_INCREMENT,
gameName VARCHAR(100) NOT NULL,
size INTEGER,
price INTEGER,
PRIMARY KEY(gameID)
);
CREATE TABLE IF NOT EXISTS USER_GAME(
id INTEGER NOT NULL,
gameID INTEGER NOT NULL,
purchaseDate DATE,
PRIMARY KEY(id,gameID)
);

NOT NULL means the field can not be empty. AUTO_INCREMENT automatically generates a unique integer value for each record you add.

SQL - ALTER TABLE:

ALTER TABLE USER_GAME
ADD FOREIGN KEY (id) REFERENCES USERS(id);
ALTER TABLE USER_GAME
ADD FOREIGN KEY ( gameID ) REFERENCES GAME( gameID );

SQL - DROP TABLE:

DROP TABLE USERS;

SQL - INSERT VALUES:

Below two SQL queries inserts values into USERS table one by one.

INSERT INTO USERS(fname, lname, country) VALUES("John","Leber"," UK ");
INSERT INTO USERS(fname, lname, country) VALUES("Chris","Dino"," UK ");

You can also add values in a single query

INSERT INTO USERS(fname, lname, country) VALUES
("Melford","Daniel"," USA "),
(" Clark ","Athen"," USA "),
("Jones","Blake"," UK ");
INSERT INTO GAME(gameName, size, price) VALUES
("Tricksy","1250","180"),("Shooter X","78","100"),("Bampoo","120","50");
INSERT INTO USER_GAME VALUES
(1,1,"12=12-2006"),(1,2," 23-12-2006 "),(1,3," 30-12-2006 "),
(2,3," 14-08-2007 "),(3,1," 14-08-2007 "),(4,2," 14-08-2007 "),
(5,1," 14-08-2007 "),(5,3," 03-09-2007 "),(4,1," 03-09-2007 "),(3,3," 03-09-2007 ");

SQL - UPDATE TABLE:

UPDATE USERS SET country=”Japan” WHERE id=5;

SQL - SELECT QUERY:

SELECT query is used to retrieve data from the database table. SELECT query is made up of two basic clauses SELECT and FROM. The basic syntax of SQL query is

SELECT "column_name" FROM "table_name"

WHERE clause is used for selection purpose. You can use Boolean operators or comparison operators to retrieve a subset of data from the database table.

The basic syntax of SELECT statement with WHERE clause is

SELECT "columnName" FROM "tableName" WHERE “columnValue” operator someValue

Now let's practice a number of SELECT queries

SELECT * FROM USERS;

SELECT fname,lname from USERS;

SELECT QUERY - DISTINCT:

SELECT DISTINCT country FROM USERS;

DISTINCT displays unique values with no repetition.

SELECT QUERY - ALIAS:

SELECT id AS MY_ID, fname, lname FROM USERS;

The keryword ‘AS' is used to define an alias. It renames the column name for display purpose only. Actual column name in the database remains the same. For example our query has displayed the column name id as MY_ID.

SELECT QUERY WITH WHERE CLAUSE:

SELECT * FROM USERS WHERE country=” UK ”;

SQL - BOOLEAN OPERATORS:

You can use Boolean operators AND, OR and NOT in WHERE clause of SQL SELECT query.

SELECT * FROM USERS WHERE country=” UK ” OR country=” USA ”;

SQL - COMPARISON OPERATORS:

You can also use comparison operators like <, >, <=, >=, =, <> in WHERE clause of SQL SELECT query.

SELECT * FROM USERS WHERE country=” UK ” OR id>3;

SELECT QUERY - IN AND NOT IN: 

SELECT * FROM USERS WHERE country IN(‘ UK ',' USA ',' Japan ');

IN and NOT IN are used for a list of values. It is often used in nested queries. IN is equivalent to = and NOT IN is equivalent to <>.

SELECT QUERY - WILD CARDS:

SELECT * FROM USERS WHERE fname LIKE ‘Jo%';

The above query uses LIKE ‘Jo%' wild card. It returns all records from the database table USERS where first name is like Jo---. Jo% means that retrieve all records whose first names start with Jo. The above query will match names Joe, John, Jones, Joseph and so on.

SQL - AGGREGATE FUNCTIONS:

You can also use AGGREGATE functions like SUM, COUNT, AVG, MAX, MIN in SQL queries. You can not use different aggregate functions in one query except if you use GROUP BY.

SELECT COUNT(*) FROM USERS;

SQL - ORDER BY:

SELECT * FROM USERS ORDER BY id DESC;

ORDER BY clause in the SQL query is used to sort the results. If you do not write DESC then by default the results are sorted in ascending order.

SQL - GROUP BY CLAUSE:

GROUP BY clause in SQL is used in two conditions. First when you want to get subgroup information. Secondly you have different values and you want to apply aggregation.

SELECT country,COUNT(*) AS num_users FROM USERS GROUP BY country;

This query subgroups the users into country and gives the number of users from each country.

HAVING:

Any condition on GROUP BY clause can only be applied by using HAVING. HAVING limits the group based on condition.

GROUP BY may not have HAVING but HAVING must have a GROUP BY

SELECT country,COUNT(*) AS num_users FROM USERS GROUP BY country HAVING num_users>=3 ORDER BY num_users

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Beware of Oracle LOG_ARCHIVE_MAX_PROCESSES Parameter!!
   Oracle Guide for Disk I/O tuning!!
   DBA Tips: Switching Oracle Users and Back!!
   Important Oracle Issues and Solutions: A Must Read!!