While using SQL there are some queries we can use on a frequent basis, since these queries play a vital role in storing the data into the database. But, before explaining these queries, let us first understand what "SQL" is.
Structured Query Language (SQL)
SQL stands for Structured Query Language, and is a standard computer language for querying Relational Database Management Systems (RDBMSs) and to store, manipulate and retrieve the data stored in a relational database. All the RDBMS systems use SQL as the standard database language.
Now the question is, what is a RDBMS?
As said above, it stands for Relational Database Management System. It is a database engine that is based on relational model invented by E. F. Codd and uses SQL to access the database.
The data in a RDBMS is stored in database objects called tables. It is the basis for SQL and other modern database systems like:
- MySQL
- Microsoft Access
- ORACLE Sybase
- Informix
- Postgres
- SQL Server
- DB2
SQL commands are mainly divided into six parts and these are as follows.
1. Data Definition Language Commands (DDL)
DDL includes:
| CREATE TABLE |
CREATE SEQUENCE |
DROP USER |
| CREATE VIEW |
CREATE SYNONYM |
DROP INDEX |
| CREATE USER |
ALTER TABLE |
DROP TRIGGER |
| CREATE DATABASE |
ALTER VIEW |
DROP PROCEDURE |
| CREATE INDEX |
ALTER USER |
DROP SCHEMA |
| CREATE TRIGGER |
ALTER TRIGGER |
DROP FUNCTION |
| CREATE PROCEDURE |
ALTER SEQUENCE |
DROP SEQUENCE |
| CREATE SCHEMA |
ALTER SESSION |
DROP SYNONYM |
| CREATE FUNCTION |
DROP TABLE |
DROP JAVA |
| CREATE JAVA |
DROP VIEW |
TRUNCATE TABLE |
CREATE TABLE, ALTER TABLE, DROP TABLE and TRUNCATE TABLE are the commands that are generally used.
2. Data Manipulation Language (DML)
DML includes:
3. Data Control Language (DCL)
DCL includes:
4. Transaction Control Commands (TCC)
TCC includes:
| COMMIT |
ROLLBACK |
SAVE POINT |
5. Clauses
Clauses includes:
| CONSTRAINT CLAUSE |
DROP CLAUSE |
6. Pseudocolumns
Pseudocolumns include:
| LEVEL PSEUDOCOLUMN |
ROWNUM PSEUDOCOLUMN |
CURRVAL and NEXTVAL PSEUDOCOLUMN |
| ROWID PSEUDOCOLUMN |
OL ROW STATUS PSEUDOCOLUMN |
Now, assume the following table to understand the examples.
DDL Commands
1. Create Table Command
It is one of the most fundamental components of SQL. We need to create a table when we want to store some data, and the CREATE TABLE command helps us to do that. And it is compulsory that each table must have its name. Integrity Constraints like primary key, foreign key and unique key can be defined for the columns while creating a table, we can also specify a default value for a column. Different RDBMS systems have different CREATE statements.
Syntax
CREATE TABLE table_name
(
Col1 Datatype Constraint
Col2 Datatype Constraint
Col1 Datatype Constraint
Constraint-list
)
Example
CREATE TABLE Student
(
Stu_ID Number(10),
Stu_Name varchar2(20),
RollNo Number(10),
Stream varchar2(20)
);
Result
Student Table
| Stu_ID |
Stu_Name |
RollNo |
Stream |
| 101 |
Shreya |
5 |
Commerce |
| 102 |
Rohan |
9 |
Science |
| 103 |
Ronit |
17 |
Science |
While creating a table, the following characteristics may be assigned INDEXes, auto incrementing columns, and constraints like:
- NOT NULL
- DEFAULT
- UNIQUE
- CHECK
- Primary Key
- Foreign Key and so on.
2. CREATE VIEW Command
A new view is created by a CREATE VIEW command. A view looks like a table since it also has rows and columns. As in a table, its fields are also the same as the table fields but the difference is when we create a view it extracts the given fields and records from one or more related tables.
It provides the flexibility, security and simplified database queries and the complexity of the database is hidden.
Syntax
CREATE View View_Name AS "SQL Statement";
Example
Employee Table
| Emp_ID |
Stu_Name |
Emp_Name |
ContactNo |
Designation |
Salary |
| 111 |
Jimmy |
8751845624 |
CommercelNo |
Soft. Eng |
50Km |
| 222 |
Alberta |
8655146564 |
ScienceNo |
Tech Lead |
65KM |
| 333 |
Harrison |
6774526673 |
Scienceo |
Sr. Soft. Engg |
60km |
CREATE View View_Employee;
AS SELECT Emp_ID, Designation, salary
FROM Employee;
Result
| Emp_ID |
Designation |
Salary |
| 111 |
Soft. Eng |
50Km |
| 222 |
Tech Lead |
65KM |
| 333 |
Sr. Soft. Engg |
60km |
3. Create User Command
This command adds a user to the current database, in other words it creates a database account through which you can log into the database using a username that is unique.
Syntax
CREATE USER username
IDENTIFIED { By Password | EXTERNALLY |GLOBALLY AS }
Example
CREATE USER Joseph
IDENTIFIED BY JSH123;
In the preceding example we are creating a user called "Joseph" in the Oracle database whose password is JSH123, in other words that the identification of Joseph is his Password.
4. Create Database Command
The Create database command is the simplest SQL command to create a database in Oracle. To use the RDBMS for storing the data we need to first create the database that is a collection of organized data.
Syntax
CREATE DATABASE "database_name";
Example: Suppose we want to create a new database known as "Quotation" then the command will be:
CREATE DATABASE Quotation;
5. Create Index Command
This command creates the indexes in the tables, so that the information can be quickly and efficiently retrieved from the SQL database without reading the entire table.
Syntax
CREATE INDEX index_name
ON table_name (Col1, Col2, Col3);
Example: Assuming a Customer Table as in the following:
| Cust_ID |
Stu_Name |
Cust_Name |
City |
| 1001 |
Jimmy |
vikas |
Jalandar |
CREATE INDEX MyIndex
ON Customer (Cust_Name);
This query will create an index called "MyIndex" on the Customer table.
Next Article: Oracle Commands : Part 2