SQL Server Mentalist

Hi Folks,

This post is part of Series Database Management Systems

Currently running topic for this series is listed as below :

Series of Database Management Systems

>>Chapter 1 : DBMS [Database Management Systems]

>>Chapter 2 : Database Core Concepts and  Applications

>>Chapter 3 : Record Storage and Primary File Organization

>>Chapter 4 : Index Structures of Files

>>Chapter 5 : Entity-Relationship Model

>>Chapter 6 : Relational Algebra

>>Chapter 7 : SQL<You are Here>

Continuing from my previous post on this series.

We are going to Cover the Following Points in this article

• Introduction
• SQL

Introduction

In this unit you study about the Structured Query language which is used for programming the database. The history of SQL began in an IBM laboratory in San Jose, California, where SQL was developed in the late 1970’s. SQL stands for structured Query Language. It is a non-procedural language, meaning that SQL describes what data to retrieve delete or insert, rather than how to perform the operation. It is the standard command set used to communicate with the RDBMS.

A SQL query is not-necessarily a question to the database. It can be command to do one of the following.

Ø Create or delete a table.

Ø Insert, modify or delete rows.

Ø Search several rows for specifying information and return the result in order.

Ø Modify security information.

THE SQL STATEMENT CAN BE GROUPED INTO FOLLOWING CATEGORIES.

1. DDL(Data Definition Language)

2. DML(Data Manipulation Language)

3. DCL(Data Control Language)

4. TCL(Transaction Control Language)

DDL: Data Definition Language

The DDL statement provides commands for defining relation schema i,e for creating tables, indexes, sequences etc. and commands for dropping, altering, renaming objects.

DML: (Data Manipulation Language)

The DML statements are used to alter the database tables in someway. The UPDATE, INSERT and DELETE statements alter existing rows in a database tables, insert new records into a database table, or remove one or more records from the database table.

DCL: (Data Control Language)

The Data Control Language Statements are used to Grant permission to the user and Revoke permission from the user, Lock certain Permission for the user.

SQL DBA>Revoke Import from Akash; SQL DBA>Grant all on emp to public;

SQL DBA>Grant select, Update on EMP to L.Suresh;

SQlDBA>Grant ALL on EMP to Akash with Grant option;

Revoke: Revoke takes out privilege from one or more tables or views.

SQL DBA>rEOKE UPDATE, DELETE FROM l.sURES; SQL DBA>Revoke all on emp from Akash

TCL: (Transaction Control Language)

It is used to control transactions.

Eg: Commit

Rollback: Discard/Cancel the changes up to the previous commit point. SQL* COMMANDS:

This subsection discusses the often used commands in sql environment. For example, if your SQL commands are saved in a file (typically in note pad) you can execute this file using an "at" @command, similarly there are a number of such commands:

@<filename> Runs the command file stored in <filename>

Ø / Runs the SQL command or PL/SQL block currently stored in the SQL buffer

Ø EXEC[UTE] Runs a single PL/SQL statement.

Ø R[UN] Runs the SQL command or PL/SQL block currently stored in the SQL buffer.

Ø R<filename> Runs the file specified in <filename>

Ø EXIT or QUIT Exits from SQL.

Ø LIST Lists the content of the buffer

Ø A[PPEND] <text> Adds text at the end of the line

Ø CLEAR BUFFER Deletes all the lines in the buffer

Ø GER<filename> Loads host OS file into SQL BUFFER (does not execute it)

Ø SAV[E]<filename> Saves contents of buffers to OS file.

Ø DEFIN_EDITOR=’notepad’ Define notepad as the editor.

Ø ED[IT] Invokes the editor defined through

Ø DEFINE_EDITOR>

DATA TYPES IN ORACLE 8i SQL:

The fig. shows the complete listing of the data types allowed in oracle.

DATA TYPE	DESCRIPTION
CHAR (sizs)	Fixed length character. Max = 2000
VARCHAR2(size)	Variable length character. Max=4000
DATE	Date, valid range is from jan1,4712 B.C to. DEC 31,4712 A.D.
BLOB	Binary large object Max =4GB
CLOB	Character large object Max=4G.B.
BFILE	Pointer to binary OS file
LONG	Character data of variable size, Max=2G.B.
LONG RAW	Raw binary data. Rest is same as long
NUMBER (size)	Numbers. Max. size =40 digits
NUMBER(size,d)	Numbers, range=1.0E-130 to 9.9E125
DECIMAL	Same as NUMBER. Size /d can’t be specified
FLOAT	Same as NUMBER
INTEGER	Same as NUMBER Size /d can’t be specified
SMALLINT	Same as NUMBER

EXAMPLE TABLES:

To study the SQL commands of various types we need some tables. Let us consider two tables shown in the fig., which will be used throughout our discussion.

EMPLOYEE

SSN	NAME	BDATE	SALARY	MgrSSN	Dno
1111	Deepak	5-jan-62	22000	4444	1
2222	Yadav	27-feb-84	30000	4444	3
3333	Venkat	22-jan-65	18000	2222	2
4444	Prasad	2-feb-68	32000	Null	3
5555	Reena	4-aug-79	8000	4444	3

DEPARTMENT

DNO	DNAME	LOC
1	Admin	Chennai
2	Research	Bangalore
3	Accounts	Bangalore

SQL

Data Retrieval Statement (SELECT)

The select statement is used to extract information from one or more tables in the database. To demonstrate SELECT we must have some tables created within a specific user session. Let us assume Scott as the default user and create two tables Employee and Department using CREATE STATEMENT

CREATE TABLE Department (

Dno number (d) not null Dname varchar2 (10) not null Loc varchar2 (15)

Primary key (Dno));

Create table employee (

SSN number (4) not null

Name varchar3=2(20) not null

Bdate date,

Salary number (10,2) MgrSS number (4)

DNo number (2) not null

Primary key (SSN)

Foreign key [MgrSSN] reference employee (SSN) Foreign key (DNo) reference department (DNo))

The syntax of SELECT STATEMENT is given below:

Syntax:

Select* | {[DISTINCT] column | expression \) From table(s);

The basic select statement must include the following;

Ø A SELECT clause

Ø A FROM clause

Selecting all columns

Ø Example – 1

Select * From employee

The * indicates that it should retrieve all the columns from employee table. The out put of this query shown below:

Ø Out put-1

Dno	SSN	NAME	BDATE	SALARY	MgrSSN
1	1111	Deepak	5-jan-62	20000	4444
3	2222	Yadav	27-feb-60	30000	4444
2	3333	Venkat	22-jan-65	18000	2222
3	4444	Prasad	2-feb-84	32000	Null
3	5555	Reena	4-aug-65	8000	4444

Example – 2

SELECT * FROM employee

ORDER BY SSN;

Ø Output-2

SSN	NAME	BDATE	SALARY	MgrSSN	Dno
1111	Deepak	27-feb-84	30000	4444	3
2222	Yadav	15-jan-65	8000	4444	1
3333	Venkat	22-jan-85	20000	2222	2
4444	Prasad	27-feb-84	32000	Null	3
5555	Reena	15-jan-65	8000	4444	1

SELECTING SPECIFIC COLUMNS:

We wish to retrieve only name and salary of the employees.Example-3

SELECT name, salary FROM employee:

OUT PUT-3

NAME	SALARY
Prasad	32000
Reena	8000
Deepak	22000
Venkat	30000
Yadav	18000

Using arithmetic operators:

SELECT name, salary, salary * 12

FROM employee;

Ø Output-4

NAME	SALARY	SALARY *12
Prasad	32000	384000
Reena	8000	96000
Deepak	22000	264000
Yadav	18000	360000
Venkat	30000	216000

USING ALIASES (Alternate name given to columns):

SELECT \ (Name, Salary, Salary *12 "YRLY SALARY" FROM Employee

Ø OUT PUT

NAME	SALARY	SALARY *12
Prasad	32000	384000
Reena	8000	96000
Deepak	22000	264000
Yadav	18000	360000
Venkat	30000	216000

Eliminating duplicate rows:

To eliminate duplicate rows simply use the keyword DISTINCT. SELECT DISTINCT MGRSSN FROM Employee

Ø OUTPUT

MGRSSN

2222

4444

DISPLAYING TABLE STRUCTURE:

To display schema of table use the command DESCRIEBE or DESC.

DESC Employee;

Ø OUTPUT

NAME	NULL?	TYPE
Ssn	NOT NULL	NUMBER [4]
NAME	NOT NULL	VARCHAR2 [20]
BDATE	DATE
SALARY	NUMBER [10,20]
MGRSSN	NUMBER [4]
DNO	NOT NULL	NUMBER [2]

SELECT statement with WHERE clause

The conditions are specified in the where clause. It instructs sql to search the data in a table and returns only those rows that meet search criteria.

SELECT * FROM emp

WHERE name = ‘yadav’

Ø Out put-1

SSN	NAME	BDATE	SALARY	MGRSSN	DNO
2222	yadav	10-dec-60	30000	4444	3

SELECT Name, Salary FROM Employee

WHERE Salary > 20000;

Ø OUT PUT

NAME	SALARY
Prasad	32000
Deepak	22000
Yadav	18000

RELATIONAL OPERATOR S AND COMPARISON CONDITIONS:

=	Equal to
>	Greater than
>=	Greater than or equal
<	Less than
<=	Less than or equal

<>	Not equal
BETWEEN<a>ABD<b>	Range between <a> and <b> inclusive
1N<set>	True when the member is in the <set>
LIKE<pattern>	Matches a specified pattern
IS NULL	Is a null value

BETWEEN AND OR OPERATOR:

To illustrate the between and or operators.

SQL supports range searches. For eg If we want to see all the employees with salary between 22000 and 32000

SELECT * from employee

WHERE salary between 22000 and 32000

Ø Example

NAME	SALARY
Prasad	32000
Yadav	30000
Deepak	22000

IS NULL OR IS NOT NULL:

The null tests for the null values in the table

Ø Example:

SELECT	Name FROM	Employee
WHERE	Mgrssn IS NULL;

Ø OUT PUT: NAME Prasad

SORTING (ORDER BY CLAUSE):

It gives a result in a particular order. Select all the employee lists sorted by name, salary in descending order.

Select* from emp order by basic;

Select job, ename from emp order by joindate desc;

Desc at the end of the order by clause orders the list in descending order instead of the default [ascending] order.

Ø Example:

SELECT* FROM EMPLOYEE ORDER BY name DESC.

Ø OUT PUT: NAME Reena Pooja Deepak Aruna

LIKE CONDITION:

Ø	Where clause with a pattern searches for sub string comparisons.
Ø	SQL also supports pattern searching through the like operator.	We
	describe patterns using two special characters.
Ø	Percent [%]. – The % character matches any sub string.
Ø	Underscore ( _ ) – The underscore matches any character.

Example:

SELECT emp_name from emp

WHERE emp_name like ‘Ra%’

Ø Example: 1

NAME Raj Rama Ramana

Ø Select emp_name from emp where name starts with ‘R’ and has j has third caracter.

SELECT *from Emp

WHERE EMP_NAME LIKE ‘r_J%’; WHERE clause using IN operator

SQL supports the concept of searching for items within a list; it compares the values within a parentheses.

1. Select employee, who are working for department 10&20.

SELECT * from emp

WHERE deptno in (10, 20)

2. Selects the employees who are working in a same project as that raja works onl

SELECT eno from ep

WHERE (pno) IN (select pno from works_on where empno=E20);

AGGREGATE FUNCTIONS AND GROUPING:

Group by clause is used to group the rows based on certain common criteria; for e.g. we can group the rows in an employee table by the department. For example, the employees working for department number 1 may form a group, and all employees working for department number 2 form another group. Group by clause is usually used in conjunction with aggregate functions like SUM, MAX, Min etc.. Group by clause runs the aggregate function described in the SELECT statement. It gives summary information.

Ø Example: For each department, retrieve the department number, the number of employees in the department and their average salary. SELECT Dno, count (*) "No. of Employees"

FROM employee

GROUP BY DNO;

Ø OUT PUT

DNO	No. of Employees
30	2
40	5
52	3
53	4

Ø Select total salary for each department.

SELECT deptno sum (salary) from emp

GROU BY depno;

Ø OUT PUT:

DNO	SUM(SALARY)
1	22000
2	18000
3	7000

Ø For each project, retrieve the project number, project name and the number of employees who work on total project.

Ø SELECT Pnumber, Pname, count(*) FROM project, works on

WHERE Pnumber=PNO GROUP BY P number, Pname;

Ø Retrieve total number of employees in the research department.

Ø SELECT COUNT(*) FROM employee, department

WHERE Dno=Dnumber and

Dname=’Research’

Ø Find the sum of salaries, the maximum and minimum salary of all the employees.

Ø SELECT Sum(salary), max(salary),

Min (salary) FROM emp;

Sum(salary)	Mad(salary)	Min(salary)
50000	32000	8000

Ø Find the sum of the salaries of all employees of the ‘Research’ department, as well as the maximum salary, minimum salary in this department.

Ø SELECT SUM(salary), max(salary), min(salary) FROM emp, department

Where DNO=Dnumber and

Dname=’Researhc’;

HAVING CLAUSE:

The having clause filters the rows returned by the group by clause. Eg: 1>Select job, count (*) from emp group by job having count (*)>20;

2>Select Deptno,max(basic), min(basic)from emp group by Detno having salary>30000

find the average salary of only department 1.

Ø SELECT DnO,avg(salary) FROM Employee

GROUP BY Dno

HAVING Dno = 1;

Ø For each department, retrieve the department number, Dname and number of employees working in that department, so that department should contain more than three employees.

Ø SELECT Dno, Dname, count(*) FROM Emp, Dept.

WHERE Emp.Dno=dept.Dno

GROUP BY Dno

HAVING count (*) 3;

Here where_clause limits the tuples to which functions are applied, the having clause is used to select individual groups of tuples.

Ø For each department that has more than three employees, retrieve the department number and the number of its employees, who are earning more than 10,000.

Ø Example:

SELECT Dno, AVG (salary) FROM Employee

WHERE Bdate LIKE ‘%jan%’ GROUP BYDno

HAVING max (salary) > 10000;

Ø OUT PUT:

DNO AVG (SALARY)

1	22000
2	18000
3	20000

Hope you will like Series of Database Management Systems series !

If you have not yet subscribe this Blog , Please subscribe it from “follow me” tab !

So that you will be updated @ real time and all updated knowledge in your mail daily for free without any RSS subscription OR news reading !!

Happy Learning and Sharing !!

For More information related to BI World visit our all Mentalist networks Blog

SQL Server Mentalist … SQL Learning Blog

Business Intelligence Mentalist … Business Intelligence World

Microsoft Mentalist … MVC,ASP.NET, WCF & LinQ

MSBI Mentalist … MS BI and SQL Server

NMUG Bloggers …Navi Mumbai User Group Blog

Architectural Shack … Architectural implementation and design patterns

DBA Mentalist …Advance SQL Server Blog

MVC Mentalist … MVC Learning Blog

Link Mentalist … Daily Best link @ your email

Infographics Mentalist … Image worth explaining thousand Words

Hadoop Mentalist … Blog on Big Data

BI Tools Analysis … BI Tools

Connect With me on

| Facebook |Twitter | LinkedIn| Google+ | Word Press | RSS | About Me |