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

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

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 5²= 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 *****

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

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-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

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.