Home > Link, Microsoft SQL Server, MSBI, Optimization, Query, SQL Mentalist, SQL PraRup, SQL Query, SQL Server, SQL Tricks, Technology,, Vishal Pawar > BISQL – Laymen to SQL Developer # 27 – SQL#1 – Introduction, Tuple Relational Calculus

BISQL – Laymen to SQL Developer # 27 – SQL#1 – Introduction, Tuple Relational Calculus

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 |

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: