• SQL stands for “Structured Query Language” and can be pronounced as “SQL” or “sequel – (Structured English Query Language)”. It is a query language used for accessing and modifying information in the database.

• IBM first developed SQL in 1970s. Also it is an ANSI/ISO standard. It has become a Standard Universal Language used by most of the relational database management systems (RDBMS).

• Some of the RDBMS systems are: Oracle, Microsoft SQL server, Sybase etc. Most of these have provided their own implementation thus enhancing its feature and making it a powerful tool.

• Few of the SQL commands used in SQL programming are SELECT Statement, UPDATE Statement, INSERT INTO Statement, DELETE Statement, WHERE Clause, ORDER BY Clause, GROUP BY Clause, ORDER Clause, Joins, Views, GROUP Functions, Indexes etc.

TO GET STARTED

Software that you need to install in order to work with RDBMS (SQL)

1. Oracle Database

Oracle Database 12c Release 2

https://www.oracle.com/in/database/technologies/oracle-database-software-downloads.html

NOTE:

1. When it asks for the password for "system", standard password is "manager". You need to remember this password forever. This "system" will have all the privileges of database such as creating users, deleting users, drop table, drop database etc. It can also grant these resources to other users as well using the commands.

2. ALERT!!!!!!!! You should not drop entire database at any cost using the commands.

2. SQL Plus tool (Client Application)

https://www.oracle.com/in/database/technologies/instant-client/winx64-64-downloads.html

SQL*Plus Package - instantclient-sqlplus-windows.x64-19.6.0.0.0dbru.zip

Once you install these software, follow these steps

1. In Start Menu, search for SQL Plus and create shortcut on Desktop to access it easily

2. Open SQL Plus command prompt and type these commands

You just need to type username as system and password as manager as shown below.

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 31 12:00:00 2020

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Enter user-name: system

Enter password: manager

When you press enter, You should get following message

Last Successful login time: Fri Jul 31 2020 11:56:06 +05:30

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

3. Next, create an user, say, for example veeresh.

create user veeresh

identified by veeresh;

When you press enter, You should get following message

USER CREATED.

NOTE: In case if you are unable to create user and get the following error,

ERROR at line 1:

ORA-65096: invalid common user or role name

then type the following command so that you can create user from system.

alter session set "_ORACLE_SCRIPT"=true;

4. Next, you need to grant resources to user veeresh so that I can create tables, insert values, delete values, alter values and also drop tables (In other words, I can manage my database).

grant resource,connect to veeresh;

grant unlimited tablespace to veeresh;

You can give specific quota privileges to user on tablespace as follows.

ALTER USER veeresh QUOTA 1024M ON system;

Here, veeresh is username and

system is tablespace_name

Or you can give unlimited quota to user on related tablespace as follows.

ALTER USER veeresh quota unlimited on system;

5. Next, connect to user veeresh.

connect veeresh

Enter password: veeresh

When you press enter, You should get following message

Connected.

6. From here onward, SQL starts (create tables, insert values, delete values, alter values and also drop tables, delete rows, delete columns and finally Execution of Queries)

TABLE BASICS

• A relational database system contains one or more objects called tables.

• The data or information for the database are stored in these tables.

• Tables are uniquely identified by their names and are comprised of columns and rows.

• Columns contain the column name, data type, and any other attributes for the column.

• Rows contain the records or data for the columns. Here is a sample table called "STUDENT DETAILS".

USN, NAME, MOBILE NO., 10th% and PUC% are the columns. The rows contain the data for this table:

STUDENT DETAILS

USN NAME MOBILE NO. 10th% PUC%

1AT15IS001 ABC 1234567890 65 67

1AT15IS002 XYZ 1234567891 75 77

1AT16IS001 PQR 1234567892 85 87

1AT16IS002 WXY 1234567893 95 97

1AT17IS001 EFG 1234567894 80 90

How to create Table

create table student_details

(usn varchar2(10), name varchar2(10), mob number(10),

tenth number(10), puc number(10));

When you press enter, you should get following message.

Table created.

Inserting values into tables

insert into student_details

values('&usn', '&name', '&mob', '&tenth', '&puc');

Enter value for usn: 1AT15IS001

Enter value for name: ABC

Enter value for mob: 1234567890

Enter value for tenth: 85

Enter value for puc: 95

old 2: values('&usn', '&name', '&mob', '&tenth', '&puc')

new 2: values('1AT15IS001', 'ABC', '1234567890', '85', '95')

1 row created.

To Describe table properties, run the following command.

desc student_details

Name Null? Type

----------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------------

USN VARCHAR2(10)

NAME VARCHAR2(10)

MOB NUMBER(10)

TENTH NUMBER(10)

PUC NUMBER(10)

NOTE: If the above table is not in proper order, then run the following command.

set lines 200

set pages 200

To check values that are inserted in table student_details, type the following command.

select * from student_details;

USN NAME MOB TENTH PUC

---------- ---------- ---------- ---------- ----------

1AT15IS001 ABC 1234567890 85 95

To add another column to the table "student_details"

ALTER TABLE student_details

ADD age number(3);

To drop age column from the "student_details"

ALTER TABLE student_details

DROP COLUMN age;

To modify the column salary in the employee table, the query would be like

ALTER TABLE student_details

MODIFY USN varchar2(15);

Syntax to add PRIMARY KEY constraint:

ALTER TABLE student_details

ADD CONSTRAINT MyPrimaryKey

PRIMARY KEY USN;

Syntax to drop PRIMARY KEY constraint:

ALTER TABLE student_details

DROP PRIMARY KEY;

DROP TABLE statement is used to delete a table.

DROP TABLE student_details;

TRUNCATE TABLE STATEMENT:

What if we only want to delete the data inside the table, and not the table itself?

Then, use the TRUNCATE TABLE statement:

TRUNCATE TABLE student_details;

UPDATE Statement

The UPDATE statement is used to update existing records in a table.

UPDATE student_details

SET USN='123', NAME='hello'

WHERE USN='1AT15IS001';

DELETE statement

The DELETE statement is used to delete rows in a table.

DELETE FROM student_details

WHERE USN=1AT15IS001;

Transaction Control Language (TCL) commands are used to manage transactions in database.These are used to manage the changes made by DML statements. It also allows statements to be grouped together into logical transactions

COMMIT, SAVEPOINT, ROLLBACK

COMMIT

Commit command:

Commit command is used to permanently save any transaction into database.

Following is Commit command's syntax,

commit

SAVEPOINT

savepoint usn1;

ROLLBACK

This command restores the database to last committed state. It is also use with savepoint

command to jump to a savepoint in a transaction.

Following is Rollback command's syntax

rollback to usn1;

CAUTION:

To drop user from database-

1. Login as system and manager.

   1. connect system

   2. password: manager

2. Type the following command

alter session set "_oracle_script"=true;

Session altered.

drop user APEX_050000 cascade;

User dropped.

SQL COMMANDS

SQL commands are instructions used to communicate with the database to perform specific task that work with data.

SQL commands can be used not only for searching the database but also to perform various other functions like, for example, you can create tables, add data to tables, or modify data, drop the table, set permissions for users.

SQL commands are grouped into four major categories depending on their functionality:

Data Definition Language (DDL)

These SQL commands are used for creating, modifying, and dropping the structure of database objects. The commands are

CREATE, ALTER, DROP, RENAME, and TRUNCATE.

Data Manipulation Language (DML)

These SQL commands are used for storing, retrieving, modifying and deleting data. These commands are SELECT, INSERT, UPDATE, and DELETE.

Transaction Control Language (TCL)

These SQL commands are used for managing changes affecting the data. These commands are COMMIT, ROLLBACK, and SAVEPOINT.

Data Control Language (DCL)

These SQL commands are used for providing security to database objects. These commands are GRANT and REVOKE.

CREATE TABLE STATEMENT

The CREATE TABLE Statement is used to create tables to store data. Integrity Constraints like primary key, unique key and foreign key can be defined for the columns while creating the table. The integrity constraints can be defined at column level or table level. The implementation and the syntax of the CREATE Statements differs for different RDBMS.

The Syntax for the CREATE TABLE Statement is:

CREATE TABLE table_name

(column_name1 datatype constraint,

column_name2 datatype, ...

column_nameNdatatype);

table_name - is the name of the table.

column_name1, column_name2.... - is the name of the columns

datatype - is the datatype for the column like char, date, number etc.

SQL Data Types:

char(size) - Fixed-length character string. Size is specified in parenthesis. Max 255 bytes.

Varchar2(size) - Variable-length character string. Max size is specified in parenthesis.

number(size) or int - Number value with a max number of column digits specified in parenthesis.

Date - Date value in ‘dd-mon-yy’. Eg., ’07-jul-2004’

number(size,d) or real - Number value with a maximum number of digits of "size" total, with a maximum number of "d" digits to the right of the decimal.

Integrity Constraints are used to apply business rules for the database tables.The constraints available in SQL are Foreign Key, Primary key, Not Null, Unique, Check.

Constraints can be defined in two ways:

1. The constraints can be specified immediately after the column definition. This is called column-level definition.

2. The constraints can be specified after all the columns are defined. This is called table-level definition.

1) Primary key:

This constraint defines a column or combination of columns which uniquely

identifies each row in the table.

Syntax to define a Primary key at column level:

Column_namedatatype [CONSTRAINT constraint_name] PRIMARY KEY

Syntax to define a Primary key at table level:

[CONSTRAINT constraint_name] PRIMARY KEY (Column_name1, Column_name2, .......)

• column_name1, column_name2 are the names of the columns which define the primary key.

• The syntax within the bracket i.e. [CONSTRAINT constraint_name] is optional.

2) Foreign key or Referential Integrity:

This constraint identifies any column referencing the PRIMARY KEY in another

table. It establishes a relationship between two columns in the same table or between

different tables. For a column to be defined as a Foreign Key, it should be a defined as a

Primary Key in the table which it is referring. One or more columns can be defined as

Foreign key.

Syntax to define a Foreign key at column level:

[CONSTRAINT constraint_name] REFERENCES referenced_table_name(column_name);

Syntax to define a Foreign key at table level:

[CONSTRAINT constraint_name] FOREIGN KEY (column_name) REFERENCES referenced_table_name(column_name);

3) Not Null Constraint:

This constraint ensures all rows in the table contain a definite value for the column which is specified as not null. Which means a null value is not allowed.

Syntax to define a Not Null constraint:

[CONSTRAINT constraint_name] NOT NULL

4) Unique Key:

This constraint ensures that a column or a group of columns in each row have a

distinct value. A column(s) can have a null value but the values cannot be duplicated.

Syntax to define a Unique key at column level:

[CONSTRAINT constraint_name] UNIQUE

Syntax to define a Unique key at table level:

[CONSTRAINT constraint_name] UNIQUE(column_name)

5) Check Constraint:

This constraint defines a business rule on a column. All the rows must satisfy this

rule. The constraint can be applied for a single column or a group of columns.

Syntax to define a Check constraint:

[CONSTRAINT constraint_name] CHECK (condition)

ALTER TABLE STATEMENT

The SQL ALTER TABLE command is used to modify the definition structure) of a table by modifying the definition of its columns. The ALTER command is used to perform the following functions.

1) Add, drop, modify table columns

2) Add and drop constraints

3) Enable and Disable constraints

Syntax to add a column:

ALTER TABLE table_name

ADD column_namedatatype;

For Example: To add a column "experience" to the employee table, the query would be like

ALTER TABLE employee

ADD experience number(3);

Syntax to drop a column:

ALTER TABLE table_name DROP column_name;

For Example: To drop the column "location" from the employee table, the query would be like

ALTER TABLE employee

DROP location;

Syntax to modify a column:

ALTER TABLE table_name

MODIFY column_namedatatype;

For Example: To modify the column salary in the employee table, the query would be like

ALTER TABLE employee

MODIFY salary number(15,2);

Syntax to add PRIMARY KEY constraint:

ALTER TABLE table_name

ADD CONSTRAINT constraint_name

PRIMARY KEY column_name;

Syntax to drop PRIMARY KEY constraint:

ALTER TABLE table_name DROP PRIMARY KEY;

DROP TABLE STATEMENT

The DROP TABLE statement is used to delete a table.

DROP TABLE table_name;

TRUNCATE TABLE STATEMENT

What if we only want to delete the data inside the table, and not the table itself?

Then, use the TRUNCATE TABLE statement:

TRUNCATE TABLE table_name;

SELECT STATEMENT

The SELECT statement is used to select data from a database.The result is stored in a

result table, called the result-set.

SELECT Syntax:

SELECT * FROM table_name;

SELECT DISTINCT Statement

In a table, some of the columns may contain duplicate values. This is not a problem, however, sometimes you will want to list only the different (distinct) values in a table.The DISTINCT keyword can be used to return only distinct (different) values.

SELECT DISTINCT Syntax:

SELECT DISTINCT column_name(s)

FROM table_name;

WHERE Clause

The WHERE clause is used to extract only those records that fulfil a specified criterion.

WHERE Syntax:

SELECT column_name(s)

FROM table_name

WHERE column_name operator value;

AND & OR Operators

The AND operator displays a record if both the first condition and the second condition is true.

The OR operator displays a record if either the first condition or the second condition is true.

The ORDER BY Clause

The ORDER BY clause is used to sort the result-set by a specified column. The ORDER BY clause sort the records in ascending order by default.

If you want to sort the records in a descending order, you can use the DESC keyword.

ORDER BY Syntax-

SELECT column_name(s)

FROM table_name

ORDER BY column_name(s) ASC|DESC;

GROUP BY Clause

The GROUP BY clause can be used to create groups of rows in a table. Group functions can

be applied on such groups.

GROUP BY Syntax-

SELECT column_name(s)

FROM table_name

WHERE column_name operator value

GROUP BY column_name(s);

Group functions Meaning

AVG([DISTINCT|ALL],N]) Returns average value of n

COUNT(*|[DISTINCT|ALL]expr) Returns the number of rows in the query. When you specify expr, this function considers rows where expr is not null. When you specify the asterisk (*), this function Returns all rows, including duplicates and nulls. You can count either all rows, or only distinct values of expr.

MAX([DISTINCT|ALL]expr) Returns maximum value of expr

MIN([DISTINCT|ALL]expr) Returns minimum value of expr

SUM([DISTINCT|ALL]n) Returns sum of values of n

HAVING clause

The HAVING clause can be used to restrict the display of grouped rows. The result of the grouped query is passed on to the HAVING clause for output filtration. HAVING Syntax;

SELECT column_name(s)

FROM table_name

WHERE column_name operator value

GROUP BY column_name(s)

HAVING condition;

INSERT INTO STATEMENT

INSERT INTO Statement

The INSERT INTO statement is used to insert a new row in a table.

SQL INSERT INTO Syntax:

It is possible to write the INSERT INTO statement in two forms.

The first form doesn't specify the column names where the data will be inserted, only their values:

INSERT INTO table_name VALUES (value1, value2, value3,...);

OR

INSERT INTO table_name VALUES(&column1, &column2, &column3,...);

The second form specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3,...)

VALUES (value1, value2, value3,...);

UPDATE STATEMENT

UPDATE Statement

The UPDATE statement is used to update existing records in a table.

SQL UPDATE Syntax:

UPDATE table_name

SET column1=value, column2=value2,...

WHERE some_column=some_value;

DELETE STATEMENT

The DELETE statement is used to delete rows in a table.

SQL DELETE Syntax:

DELETE FROM table_name

WHERE some_column=some_value;

TRANSACTION CONTROL LANGUAGE (TCL)

Transaction Control Language (TCL) commands are used to manage transactions in database.These are used to manage the changes made by DML statements. It also allows statements to be grouped together into logical transactions

COMMIT, ROLLBACK, SAVEPOINT

COMMIT

Transaction Control Language (TCL) commands are used to manage transactions in database.These are used to manage the changes made by DML statements. It also allows statements to be grouped together into logical transactions

Commit command:

Commit command is used to permanently save any transaction into database.

Following is Commit command's syntax,

commit

ROLLBACK

Rollback command

This command restores the database to last committed state. It is also use with savepoint

command to jump to a savepoint in a transaction.

Following is Rollback command's syntax

rollback to savepoint_name;

SAVEPOINT

Savepoint command

savepoint command is used to temporarily save a transaction so that you can rollback to that point whenever necessary.

Following is savepoint command's syntax,

savepoint savepoint_name;

DATA CONTROL LANGUAGE (DCL)

Data Control Language(DCL) is used to control privilege in Database. To perform any operation in the database, such as for creating tables, sequences or views we need privileges.

Privileges are of two types,

• System : creating session, table etc are all types of system privilege.

• Object : any command or query to work on tables comes under object privilege.

DCL defines two commands,

• Grant : Gives user access privileges to database.

• Revoke : Take back permissions from user.

To Allow a User to create Session

grant create session to username;

To Allow a User to create Table

grant create table to username;

To provide User with some Space on Tablespace to store Table

alter user username quota unlimited on system;

To Grant all privilege to a User

grant sysdba to username

To Grant permission to Create any Table

grant create any table to username