SQL in RDBMS





SQL (pronounced as “ess-queel”) is used to communicate with database. It is a standard language for RDBMS. So, it is used in Oracle, Sybase, Microsoft SQL Server, Ingress etc. It uses simple commands to work (with database). It is a declarative language which codes are interpreted by engine of DBMS.

It’s one statement is enough to query a lot of information but in the case of procedural languages (BASIC, PASCAL, COBOL, C etc) many lines of codes are needed to control very simple tasks. Procedural languages are interpreted or compiled by special types of language translators.

 

1. SQL

 

It is a standard relational database language. It stands for Structured Query Language. It is a 4GL, used in database package such as ORACLE, SYBASE, INGRESS, and INFOMIX etc. It is designed for database handling.

Its original version was developed at IBM’s San Jose Research Laboratory .Its original name SEQUEL (Structured English Query Language) is changed into SQL. It was developed to design interface of relational database system called SYSTEM R in the early 1970. The joint effort of ANSI (American National Standards Institute) and ISO (International Standards Organization) had presented its standard in 1986 which is called SQL1 or SQL-86.

Its second revised version was presented in 1992 which is popularly called SQL 2 or SQL-92. It is a comprehensive database language with data definition, query, and update.

The SQL editor is required to write and execute SQL statements. Oracle’s SQL *Plus tool allows you to pass SQL statements to the Oracle’s DBA. It also allows you to access to PL/SQL(Procedural Language of Oracle). Some syntax are given to create, update, sort, display and search database.

 

2. Data types

  • CHAR: maximum length 255 characters.
  • VARCHAR and VARCHAR2: maximum character 2000
  • NUMBER: 38 digits of precision
  • DATA: DD-Mon-YY format
  • LONG: 65,536 characters

 

 

3. Working with SQL

 

(a) Creating table: The CREATE command is used to create new table.

CREATE TABLE tablename (field1 datatype(size), Field2 datatype(size));

Example:

SQL> CREATE TABLE mytable (SSS number (6), FNAME varchar2(20), LNAME varchar2 (20), ADDRESS varchar2(30), SEX varchar2(2));

 

(b) Insertion of data in table: INSERT INTO command is used to enter data into table.

Example:

SQL>INSERT INTO mytable (SSN, FNAME, LNAME, ADDRESS, SEX) VALUES (1001, ‘Shatavadi’, ’Singh’, ’Hasanpur, Kailali’, ‘F’);

You have to repeat INSERT INTO statement for each record of table. For this purpose, Editor should be invoked:

Edit > Editor >-Invoke Editor

 

(c) Updating the contents of a table: If you have to edit data, UPDATE command helps to edit it.

UPDATE tablename SET field1 = expression, field2 = expression WHERE field = expression;

Example:

SQL>UPDATE mytable SET FNAME = ‘Shaila’ LNAME= ‘Sharma’ WHERE SSN=1001;

 

(d) Displaying the contents of table: The contents of table are displayed using SELECT command. The wildcard (*) is used to display whole contents.

SELECT field1 field2 *FROM tablename;

SELECT *FROM tablename;

Example:

SQL>SELECT *FROM mytable;

The following syntax is used to display contents of particular fields.

SELECT field1, field2,… FROM tablename;

Example:

SQL>SELECT SSN, FNAME, ADDRESS *FROM mytable;

Example:

SQL> SELECT SSN, FNAME, ADDRESS FROM mytable;

 

Mytable is the name of the SQL database table.

SN.

FNAME

LNAME

ADDRESS

SEX

1001

Shatavadi

Singh

Hasanpur, Kailali

F

1002

Shishir

Shrestha

Mahendranagar

M

1003

Shatavadi

Jha

Birjung

F

1004

Shambhulal

Shrivastava

Biratnagar

M

1005

Shishir

Manandhar

Dhanusha

M

1006

Sakila

Begam

Nepalganj

F

 

(e) Inserting Fields (columns): ALTER is used to insert column in existing table.

ALTER tableName ADD (newfield datatype(size)…);

Example:

ALTER mytable ADD (sale, number (6, 2));

 

(f) Information about Fields (columns): DESCRIBE is used to display information of field status.

DESCRIBE tableName;

Example:

SQL> DESCRIBE salemans;

 

(g) Sorting of table: The SELECT . . . ORDER BY is used to sort data in SQL environment.

SELECT field1, field2,… FROM tablename ORDER BY field, field;

SQL>SELECT SSN, FNAME, ADDRESS FROM mytable ORDER BY SEX;

 

(h) Renaming table: The existing old table is renamed using RENAME command.

RENAME oldTable TO newTable;

Example:

SQL> RENAME salesman TO salesperson;

 

(i) Deleting a table: The DROP command is used to remove table.

DROP tablename;

Example:

SQL> DROP mytable;

 

4. Logical and Relational Operators

 

AND, OR and NOT are logical operators in computer words which are used as logical filters in the SQL environment. IN SQL, WHERE clause can be made up of multiple predicates by chaining them together with the AND, OR, and NOT operators for query purposes.

 

(a) AND: The AND operator specifies multiple conditions which a column must match in order to be returned.

Consider the case in which we want to return only rows from the SALESPERSON table in which the saleman was SALE_D number “south”, but we only want sales in which the price was greater then 4000.00

SALE_ID

NAME

SALE-D

SALE

S01

Pankaj Bhatta

East

5000

S02

Deepak Khanal

West

4500

S03

Hari Regmi

South

4200

S04

Manoj Singh

North

5600

S05

Kapil joshi

South

3400

S06

Kanchan Pathak

South

1200

 

SELECT SALE_ID, SALE

FROM Salesperson

WHERE SALE_D= ‘SOUTH’ AND SALE > 4000;

In this case, our database would return:

SALE_ID          SALE

————————–

S03                 4200

 

(b) OR: The OR operator specifies multiple acceptable conditions. If any one condition is matched, it may returns multiple records (rows).

In this case, the row was returned because SALE was greater than “500″ “AND” SALE_ID was equal to “SO1″. Now consider the same query using OR:

SELECT SALE_ID, SALE

FROM Salesperson

WHERE SALE_D =‘SOUTH’ OR SALE > 4000;

In this case, we would get the following response:

SALE_ID      SALE
———————-
S01                 5000

So2               4500

S03              4200

So4               5600

 

(c) NOT: NOT operator specifies negativity in a match and used to specify the reverse condition.

If you want a list of all the employees other than “manoj Singh” from the sample database, you would use:

SELECT SALE_ID, NAME

FROM saleperson

WHERE NOT (NAME =”Manoj Singh”);

In this case, the database would return the following:

SALE_ID           SALE
——————————-
S01                Pankaj Bhatta

       So2              Deepak Khanal

 S03              Hari Regmi

So4              kapil Joshi

       So5              Kancha pathak

Relational operators are designed by <, >, and = signs and their combinations. These are also used with WHERE clause for query purpose.

 

5. Query in SQL

 

The query statements are build by SELECT, WHERE and logical operators. The power and popularity of SQL is hidden in its query capabilities.

Selecting a dataset from table: There are three very important clauses in SQL used for data query, are SELECT, FROM, and WHERE, The logical connectors AND, OR, and NOT are frequently used according to query problems.

 

SELECT: contains field name and some arithmetic expressions like as +, -, * and /.

Example: SELECT ITEM, PRICE*100

FROM: it contains table name.

Example: FROM mytable

WHERE: it contains logical connectives like as AND, OR, and NOT. The BETWEEN, LIKE etc are used with it.

 

Example:

SQL>SELECT FNAME, LNAME

FROM mytable

WHERE FNAME = ‘Shatavadi’ AND LNAME=’Singh’;

 

i. Rabging Searching: you use BETWWEEN n1 AND n2 or NOT BETWEEN n1 AND n2 clause after WHERE for range searching.

Example:

SQL>SELECT FNAME, LNAME FROM mytable WHERE SSN BETWEEN 1003 AND 1006;

Example:

SQL>SELECT FNAME, LNAME FROM mytable WHERE SSN NOT BETWEEN 1001 AND 1003;

The BETWEEN operator is used to modify the WHERE clause. The BETWEEN operator works much like the combination of >=, AND, and <=.

Example:

Table: Salesmen

NAME

SALE

DATE

Niranjan Ojha

2500

May-05-2012

Shiv Gurung

1250

May-07-2012

Mamata Baskota

1300

May-08-2012

Priti baral

2700

May-08-2012

 

Thus, to get a listing of all the employees with sale between the range of 1000 and 2500, you could use the long hand version such as:

SELECT NAME, SALE

FROM Salesmen

WHERE SALE>= 1000 AND SALE <=2500;

Or,

You could use the BETWEEN operator such as:

SELECT NAME, SALE

FROM Sales,am

WHERE SALE BETWEEN 1000 AND 2500;

Result:

NAME                 SALE
———————————
Nirajan Ojha             2500

Shiv Gurung               1250

   Mamata Naskota          1300

 

ii. Pattern matching: % is used to match any type of string but_ (underscore) is used for any single character. The LIKE keyword is used for this purpose.

Example:

SQL>SELECT FNAME, LNAME FROM mytable WHERE FNAME LIKE ‘S%’;

Example:

SQL>SELECT FNAME, LNAME FROM mytable WHERE FNAME LIKE ‘S%’ OR LNAME LIKE ‘M%’;

 

6. VIEW

 

The view is a virtual table of existing base table. It is a window to display selected or specified portion of base table. It provides excellent and safe access of specified data. Modification can be done in view table and such type of table is called updateable views. Modification done in view will be passed to the base table. It supports INSERT, UPDATE, DELETE and other editing commands of SQL.

 

Example:

CREATE VIEW viewStu AS

SELECT *FROM mytable

WHERE SALE> 2500;

ViewStu is the name of virtual table of base table mytable.

The following command is used to delete or remove view table from database:

DROP VIEW <viewTable>

Example:

DROP VIEW viewStu;

If view table is dropped there is no hazardous effect falls on base table. When base table is dropped, the view table becomes illogical and display erroneous message.

 

7. SQL Functions

 

Some built-in functions are available in SQL to manipulate numbers and strings.

(a) Numerical Functions: These functions act on numerical expression.

Sn. Functions Comments
1. SUM(numExp) It is used to sum data of numerical fields.SELECT SUM(fields)

FROM tablename

WHERE optional_where_clause;

Example:

SELECT SUM(SALE) “Total sale”

FROM Salesman;

2. AVG(numExp) It is used to find out average of any numerical data fields.SELECT AVG(field)

FROM tablename

WHERE optional_where_clause;

Example:

Select AVG (SALE) “Average Sale”

FROM Salesman;

That SQL code would yield;

—————-

Average sale

—————–

90000

 

3. MAX(numExp) It returns largest numerical data from table.
4. MIN(numExp) It returns smallest numerical data from table.
5. COUNT() It returns total count of records of table.Example:

SELECT COUNT (Address) FROM mytable;

COUNT(*) :returns the number of rows including duplicate and those with null.

Example:

SELECT COUNT(*) FROM mytable;

6. POWER(numExp, n) It returns numExp*Example:

POWER(5,2) returns 52= 25

7.

SQRT(numExp)

It returns square root of given expression.

Example:

SQRT (144) returns 12

 

(b) String manipulating functions: These functions are dedicated for manipulating of strings.

Sn

Function

Comments

1.

LENGTH (strings)

It returns number of characters present in string.

Example: LENGTH (‘psychology’) will return 10.

2.

Lower (strings)

It returns lowercase strings.

Example:

LOWER (‘PSYCHOLOGY’) returns phychology.

3.

UPPER(string)

It returns uppercase string.

Example: UPPER (‘psychology’) returns PHYCHOLOGY

4.

LTRIM ()

It returns string after trimming specified character from left side of the orginal strings.

Example: LTRIM (‘PSYCHOLOGY’,’Y’) returns SYCHOLOG

5.

RTRIM()

It returns string after trimming from right side of original string.

Example: RTRIM (‘PSYCHOLOGY’,’Y’) return PSYCHOLOG

6.

SUBSTR (string, startpos, n)

It returns n character from specified Satartposition.

Example: SUBSTR (‘PHYCHOLOGY’,7,4) returns LOGY

7.

REPLICATE (char, n)

It repeats character n times.

Example: REPLICATE (‘*’,6) returns ******

 

(c) GETDATE(): It returns current system date.

 

8. Join, Union and Intersection

 

(a) JOIN: To create a JOIN, you simply define two or more tables in your SELECT-FROM statement. For example, consider the following which joins the TEACHER and STUDENT tables into a single view:

Table: Teacher

TCODE

T-NAME

DEPARTMENT

T001

Madhav Dhungana

Mathematics

T002

Anil Ojha

Physics

T003

Trith Bhatta

Economics

T004

Bishwas Sharma

Computer

 

Table: Student

DEPARTMENT

S_NAME

Mathematics

Mohit Bhandari

Physics

Diksha Joshi

Economics

Jyoti Mahara

Computer

Anisha Pant

 

Example:

SELECT t_name, department, s_name

FROM Teacher, Student

WHERE student.department = teacher.department;

Result:

T_NAME

DEPARTMENT

S_NAME

Madhav Dhungana

Mathematics

Mohit Bhandari

Anil Gurung

Physics

Diksha Joshi

Trith Bhatt

Economics

Jyoti Mahara

Bishwas Sharma

Computer

Anisha pant

 

(b) UNION: The purpose of the UNION command is to combine the results of two queries together.

UNION

UNION


In this respect, UNION is somewhat similar to JOIN in that they are both used to related information from multiple tables. One restriction of UNION is that all corresponding columns need to be of the same data type. Also, when using UNION, only distinct values are selected (similar to SELECT DISTINCT).

Syntax:

SQL Statement 1

UNION

SQL Statement 2

Let’s assume that we have the following two tables,

Table Salesmen:

NAME

SALE

DATE

Niranjan Ojha

2500

May-05-2012

Shiv Gurung

1250

May-07-2012

Mamata Baskota

1300

May-08-2012

Priti Baral

2700

May-08-2012

 

Table Customers

DATE

SALE

May-07-2012

1250

May-10-2012

1135

May-11-2012

1320

May-11-2012

1750

 

And we want to find out all the dates where there is a sales transaction. To do so, we use the following SQL statement:

SELECT DATE FROM Salesmen

UNION

SELECT DATE FROM Customers

 

Result:

DATE

————-

May-05-2012

May-07-2012

May-08-2012

May-10-2012

May-11-2012

May-12-2012

The purpose of the UNION ALL command is also to combine the results of two queries together. The difference between UNION ALL and UNION is that, while UNION only selects distinct values, UNION ALL selects all values.

Syntax:

SQL Statement 1

UNION ALL

SQL Statement 2

 

Example:

SELECT DATE FROM Salesman

UNION ALL

SELECT DATE FROM Customers

Result:

DATE

—————-

May-05-2012

May-07-2012

May-08-2012

May-08-2012

May-07-2012

May-10-2012

May-11-2012

May-12-2012

——————

 

(c) INTERSECT: Similar to the UNION command, INTERSECT also operates on two SQL statements. The difference is that, while UNION essentially acts as an OR operator (value is selected if it appears in either the first or the second statement), the INTERSECT command acts as an AND operator (value is selected only if it appears in both statements).

Intersect

Intersect


Syntax:

SQL Statement 1

INTERSECT

SQL Statement 2

 

Example:

SELECT Date FROM Salesmen

INTERSECT

SELECT Date FROM Customers

 

Result:

DATE May

————–

May-07-2005

No related posts.