• No results found

Jahangir Alam

N/A
N/A
Protected

Academic year: 2023

Share "Jahangir Alam"

Copied!
102
0
0

Loading.... (view fulltext now)

Full text

(1)

1

Introduction to SQL

Jahangir Alam

University Women’s Polytechnic

AMU Aligarh (India) - 202002

(2)

Mar 22, 2023 Jahangir Alam 2

Introduction to SQL*Plus

Structured Query Language (SQL) is a tool that helps in organizing, managing and retrieving data in a database.

SQL was originally developed by IBM in 1970s and during 1986 it was accepted as standard query language on relational databases.

Unlike conventional programming languages SQL is a set oriented language that acts upon a set of records rather than single record.

Thus step by step procedural instructions need not be given to perform a task. It is sufficient if we state as to what is required.

Clearly, SQL falls under the category of non procedural languages.

Two main reasons for its popularity are:

(3)

Mar 22, 2023 Jahangir Alam 3

Ease of use: SQL statements are made up of simple English like sentences that convey the meaning of statement clearly. So it allows users to build complex ad-hoc queries easily.

Portability: Porting an SQL based application across various platforms requires little or no changes.

We shall study SQL*Plus – Implementation of SQL from ORACLE RDBMS (World’s Most Widely Used RDBMS) Corp.

This will be taught from the point of view of SQL commands syntax, however most of the time we shall focus on MS – Access/ MySql RDBMSs when executing the SQL queries and shall modify our SQL statements accordingly, whenever required.

(4)

Mar 22, 2023 Jahangir Alam 4

Sample Database and Associated Tables

To study SQL * Plus we shall utilize the EMPLOYEE database consisting of the following tables:

Emp <Empno, Ename, Desig, Salary, Grade, Joindate, Deptno>

Dept <Deptno, Deptname>

Increments <Empno, Incdate, Amount>

(5)

Mar 22, 2023 Jahangir Alam 5

1. Creating a Table:

CREATE TABLE command is used.

Its general for is:

CREATE TABLE <table-name> (column- definition1, column-definition2, column- definition3 … … … );

CREATE TABLE command is used.

Its general form is:

CREATE TABLE <table-name> (column-

definition1, column-definition2, column-

definition3 … … … );

(6)

Mar 22, 2023 Jahangir Alam 6

Examples

Ex 1:

CREATE TABLE Dept (Deptno CHAR(5) NOT NULL PRIMARY KEY, Deptname CHAR(20));

Ex 2:

CREATE TABLE Emp (Empno CHAR(5) NOT NULL PRIMARY KEY, Ename CHAR(20) NOT NULL, Desig CAHR(20), Grade CHAR(2), Salary NUMBER(10,2) CHECK (Salary > 10000.00), Joindate DATE DEFAULT

SYSDATE, Deptno CHAR(5) NOT NULL REFERENCES

Dept.Deptno);

(7)

Mar 22, 2023 Jahangir Alam 7

Note the Following:

When executed successfully CREATE TABLE command returns the message TABLE CREATED.

In SQL*Plus, the SQL commands get executed when the ; or a / is encountered.

Another way of running the SQL command is using the RUN command.

If a value is not assigned to DATE it takes current system date as default value.

SQL is not case sensitive.

NUMBER (10,2) indicates the #######.## format.

Execution of above two examples in MySql (with changes in syntax wherever required) has been demonstrated in the following figures:

(8)
(9)
(10)

Mar 22, 2023 Jahangir Alam 10

2. Inserting Data in a Table

INSERT command is used.

Its general form is:

INSERT INTO <table-name> Column_List

(Set of Values/ Parameter Substitution)

(11)

Mar 22, 2023 Jahangir Alam 11

Examples

Ex 1:

INSERT INTO Emp

(empno,ename,desig,grad e,salary,deptno) VALUES (‘100’,’Rajan

Singh’,’Engineer’,’A’,20000 ,’213’);

Ex 2:

INSERT INTO Emp

(empno,ename,desig,grad e,salary,deptno) VALUES (‘&1’,’&2’,’&3’,’&4’,&5,’&6’)

;

(12)

Mar 22, 2023 Jahangir Alam 12

Note the Following

Ex-2 makes use of parameter substitution. Unfortunately, parameter substitution is not available with MySql. When EX-2 is executed in SQL * Plus, it prompts the user to enter the values of respective columns. By executing the same command again and again user can create several rows in the table. In MySql we have to edit the same command again and again using up/ down arrow keys to add sevaral rows.

In SQL*Plus the & symbol acts as the parameter substitution operator. When a substitution variable is used SQL*Plus prompts for the value of the variable, accepts it and substitutes it in place of variable.

In SQl*Plus we can use EDIT command to correct the SQL Syntax errors. Another way of doing the same thing may be saving the query in a file and then edit that file as:

SQL> SAVE query1

(13)

Mar 22, 2023 Jahangir Alam 13

SQL * Plus saves the contents of buffer in a file called query1.sql. Now to run the query we can use following commands

SQL> GET query1 SQL> RUN

Im MySql sysnatx errors in the query are fixed using up/ down arrow keys.

Queries can be saved in files but to do so we require a front end tool like Toad for MySQL or MYSql Yog or MySql Query Browser etc.

The execution of EX-1 in MySql is shown in the figure on the next page:

(14)
(15)

Mar 22, 2023 Jahangir Alam 15

3. Retrieving the Data (Query)

The SELECT command is used.

There are two forms of SELECT command:

Unconditional SELECT Conditional SELECT

Unconditional SELECT takes the following general form:

SELECT */ Colunm_List FROM <table_name>

(16)

Mar 22, 2023 Jahangir Alam 16

Unconditional SELECT Examples

1. SELECT * FROM Emp;

2. SELECT empno, ename FROM Emp;

In case a particular column repeats certain values again and again the set of values can be listed using DISTINCT clause with SELECT.

3. SELECT DISTINCT deptno from emp;

4. SELECT DISTINCT grade from emp;

(17)
(18)

Mar 22, 2023 Jahangir Alam 18

Conditional SELECT takes the following general form:

SELECT */ Colunm_List FROM <table_name>

WHERE <condition>

(19)

Mar 22, 2023 Jahangir Alam 19

Conditional SELECT Examples

1. SELECT * FROM emp WHERE grade = ‘A’;

2. SELECT empno,ename,grade,salary FROM emp WHERE desig = ‘Lecturer’;

3. SELECT * FROM emp WHERE grade = ‘A’ OR grade =

‘B’;

4. SELECT * FROM emp WHERE salary>=31000 AND salary<=40000;

5. SELECT * from emp WHERE (salary+

(salary*1.5))>80000;

6. SELECT empno,ename,desig FROM emp WHERE grade <> ‘A’ AND salary > 5000.00;

7. SELECT * FROM emp WHERE NOT (grade = ‘A’);

(20)
(21)
(22)

Mar 22, 2023 Jahangir Alam 22

Note the Following

<, >, <=, >=, <> are called Relational Operators.

AND, OR and NOT are called Logical Operators.

All the operators have their usual meanings and you are well aware of them from C.

(23)

Mar 22, 2023 Jahangir Alam 23

Special Conditional Operators

Operator Purpose

IN Compares the value of column with every member given in the IN set for equality.

NOT IN Compares the value of column with every member given in the IN set for inequality.

BETWEEN Checks if the value of the column is in the range specified (both limits inclusive)

NOT BETWEEN Checks if the value of the column is outside the range specified (both limits inclusive)

LIKE Matches the specified pattern string with a part of the character column value. The wild card matching characters % (percentage sign) which matches zero or more characters and _ (underscore) which matches exactly one character can be used in pattern string.

(24)

Mar 22, 2023 Jahangir Alam 24

Examples on Special Operators

Get details of only those employees who have joined in the period September 1st 1990 – March 31st 1985.

SELECT * FROM emp WHERE joindate BETWEEN ‘1985-3-31’ AND ‘1990-9-1’;

Get details of all Engineers working in Departments with deptno 10 and 30.

SELECT * FROM emp where (deptno=’10’ OR deptno=’30’) AND desig=‘Engineer’;

List details of all employees having ‘a’ as second character in their names.

SELECT * FROM emp where ename LIKE ‘_a%’;

List all employees having Gupta as their sir name:

SELECT * FROM emp where ename LIKE ‘%Gupta’;

(25)
(26)
(27)
(28)
(29)
(30)

Mar 22, 2023 Jahangir Alam 30

4. Creating a Table Using an Existing Table

The general form of the command when we want to create a table from an existing table is:

CREATE TABLE <New table-name>[Column-

List] AS [<CRITERIA>]

(31)

Mar 22, 2023 Jahangir Alam 31

Example:

CREATE TABLE OEmp (empno, ename, deptno, joindate, salary) AS SELECT empno, ename, deptno, joindate, salary FROM Emp WHERE salary BETWEEN 5000.00 AND 50000.00;

•The above command creates a new table OEmp with the specified columns list and with all those employees records of Emp table having salaries between 5,000.00 and 50,000.00.

(32)
(33)

Mar 22, 2023 Jahangir Alam 33

5. Inserting Records from an Existing Table into another Table

Its general form is:

INSERT INTO <table-name>[Column-List]

query;

Example Example

INSERT INTO OEmp (empno, ename, deptno,

joindate, salary) SELECT empno, ename,

deptno, joindate, salary FROM emp WHERE

desig=‘Engineer’;

(34)
(35)

Mar 22, 2023 Jahangir Alam 35

SET OPERATORS

They act upon two or more sets of data.

These operators are used to combine the results of multiple queries into a single result.

The set operators have been named as:

UNION

INTERSECT MINUS

Following table explains the purpose of each of them:

(36)

Mar 22, 2023 Jahangir Alam 36

Operator Purpose

UNION Combines the rows from two or more queries to return all distinct rows selected by each of the individual queries.

INTERSECT Combines the rows from two or more queries to return only those rows which are common to both the individual queries.

MINUS Combines the rows from two or more

queries to return only those rows

which appear in first query but don’t

appear in the second

(37)

Mar 22, 2023 Jahangir Alam 37

Examples

1. SELECT DISTINCT desig FROM Emp WHERE deptno = ’10’

UNION UNION

SELECT DISTINCT desig FROM Emp WHERE deptno = ’30’;

2. SELECT DISTINCT desig FROM Emp WHERE deptno = ’10’

INTERSECT INTERSECT

SELECT DISTINCT desig FROM Emp WHERE deptno = ’30’;

3. SELECT DISTINCT desig FROM Emp WHERE deptno = ’10’

MINUS MINUS

SELECT DISTINCT desig FROM Emp WHERE deptno = ’30’;

(38)

MySQL doesn’t support INTERSECT and MINUS

(39)

Mar 22, 2023 Jahangir Alam 39

Handling NULL Values

When a NULL value is encountered for a column SQL*Plus assumes that the value for that column is unknown.

When these NULL values are encountered in expressions or search conditions, a problem arises.

Consider the following instance of Emp at any time for deptno=‘100’

EMPNO GRADE

1100 E2

1103 E2

1109

1110 E3

1114 E4

(40)

Mar 22, 2023 Jahangir Alam 40

Now, consider the following query:

SELECT empno, grade FROM Emp WHERE grade

<>’E2’ AND deptno=‘100’;

The answer would be:

EMPNO GRADE

1110 E3

1114 E4

However the answer should be:

EMPNO GRADE 1109

1110 E3

1114 E4

(41)

Mar 22, 2023 Jahangir Alam 41

Now, Consider the following query:

SELECT empno, grade FROM Emp WHERE grade = NULL AND deptno=‘100’;

Interestingly, this time no records are selected.

Both of the queries we considered give inconsistent results. This is because of the fact that when a NULL value is encountered, irrespective of the condition it always evaluates to false. Then how to check the existence and non-existence of NULL values?

The only possible method is to make use of IS NULL IS NULL or IS NOT NULLIS NOT NULL operators.

Following example illustrates.

(42)

Mar 22, 2023 Jahangir Alam 42

EXAMPLES

1. SELECT empno, ename FROM emp WHERE grade IS NULL;

2. SELECT empno, ename FROM emp WHERE grade IS NOT NULL;

(43)
(44)

Mar 22, 2023 Jahangir Alam 44

Arranging Query Results

Normally result of a query appears in the same way in which the rows had been entered in the database.

However, we can display the result in ascending/

descending order using ORDER BY clause with the select statement.

The general form of using ORDER BY is:

SELECT */ Colunm_List FROM <table_name>

WHERE <condition> ORDER BY

<Column_Name> [ASC/DESC]

•Default order is ascending.

(45)

Mar 22, 2023 Jahangir Alam 45

Examples

Ex 1:

SELECT empno, ename, grade, joindate, deptno FROM Emp WHERE

joindate BETWEEN ‘1-01- 92’ AND ’10-04-98’ ORDER BY joindate;

Ex 2:

SELECT empno, ename, grade, joindate, deptno FROM Emp WHERE deptno

= ’100’ ODRER BY salary DESC;

(46)
(47)

Mar 22, 2023 Jahangir Alam 47

Sub Queries

So far we have been concentrating on retrieving information from a single table.

However, in most practical situations there may be a need for retrieving information from more than one table.

Sql*Plus enables this through the use of sub queries (and joins). Actually, a sub query is a SELECT statement that appears in WHERE clause of another SELECT statement.

The general form of sub query statement is

SELECT */ Colunm_List FROM <table_name>

WHERE <Column_Name><Operator>

[Another SELECT statement]

(48)

Mar 22, 2023 Jahangir Alam 48

The SELECT statements may be from same table or from multiple tables.

When they are from multiple tables, sub query serves almost the same purpose served by “join”.

Consider the following Query:

SELECT empno, ename, desig, deptno FROM emp WHERE empno SELECT empno, ename, desig, deptno FROM emp WHERE empno

<>

<> (SELECT empno FROM increments WHERE incdate BETWEEN (SELECT empno FROM increments WHERE incdate BETWEEN

‘1996-01-01’ AND ‘1999-31-12’);

‘1996-01-01’ AND ‘1999-31-12’);

The second SELECT statement is expected to return multiple empno, say 101,103,107… so the above query becomes:

SELECT empno, ename, desig, deptno FROM emp WHERE empno SELECT empno, ename, desig, deptno FROM emp WHERE empno

<> (101,103,107);

<> (101,103,107);

(49)

Mar 22, 2023 Jahangir Alam 49

The above statement is syntactically wrong, because not equal to operator can only act on a single value and not on a set of values.

To overcome this problem the following subquery comparison operators are used:

(50)

Mar 22, 2023 Jahangir Alam 50

Operator Purpose

IN Compares a single data value to a set of values produced by suquery. If the data value matches even one of the values given in the set, the comparison returns TRUE.

NOT IN Compares a single data value to a set of values produced by suquery for inequality.

ANY While the IN operator can be used to check the equality, the ANY operator can be used in conjunction with any relational operator (=, <>, <, >. <=, >=) to compare a single value against the set of values produced by subquery. If the data value matches any of the values in the set returned by the subquery, the comparison returns TRUE.

ALL The ALL operator is similar to ANY operator, except that the data value is compared to every data value in the set returned by the subquery. If everyone of the individual comparisons yields a TRUE result, the comparison yields a TRUE value.

(51)
(52)
(53)

Mar 22, 2023 Jahangir Alam 53

Please Note……

• ORDER BY clause can’t be used as a part of the nested query.

This is due to the fact that

nested query is used internally

by the main query and is never

visible to the user. Thus it is

unnecessary to sort the data.

(54)

Mar 22, 2023 Jahangir Alam 54

Functions in SQL*Plus

The standard library functions supported by SQL*Plus can be classifies as:

Arithmetic Functions Character Functions Date Functions

Group Functions

We shall discuss only Arithmetic Functions and

later Group Functions because both the

categories are important from the point of view of

calculations.

(55)

Mar 22, 2023 Jahangir Alam 55

Arithmetic Functions

Following table shows some important arithmetic functions and the next two figures show how they are supported by MySQL:

ABS(n) Returns the absoloute value of n.

CEIL(n) Returns the smallest integer greater than or equal to n.

FLOOR(n) Returns the largest integer less than or equal to n

*POWER (m,n) mn , n must be an integer.

SQRT(n) Returns Square root of n.

**SGN(n) Signum Function

ROUND(m,n) Rounds off m on n digits.

*MySql supports POWER(m,n) as POW(m,n).

**Not Supported by MySql.

(56)
(57)
(58)

Mar 22, 2023 Jahangir Alam 58

Joins

Requests for queries that require data from two or more tables can be solved using either “subqueries” or

“joins”.

We have already discussed subqueries, so the second approach will be discussed here.

You are well aware of basic join operation and its various flavours from the previous unit lectures.

Here we shall look into how they are practically implemented in Sql*Plus (/MySQL).

For your convenience, I will use the same tables that I had used in my lecture in Unit - III.

Table E has been created as Emp, Another table having the SALARY_CODE field has been created as SALARY and the last table having STATUS field has been created as STATUS.

Following figure shows instances of these tables:

(59)
(60)

Mar 22, 2023 Jahangir Alam 60

1. Natural Join

Consider the E (created as emp in MySql) and S (Created as Salary in MySQL) relations (discussed in previous unit’s lecture) and consider the following query:

““Get Salary Code for All Employees”Get Salary Code for All Employees”

Solution to query requires the two tables to be joined on the ID field. To express the natural join we shall execute the following command:

SELECT EMP.ID, EMP.NAME, SALARY.SCODE SELECT EMP.ID, EMP.NAME, SALARY.SCODE

FROM Emp, Salary WHERE EMP.ID = SALARY.ID;

FROM Emp, Salary WHERE EMP.ID = SALARY.ID;

(61)
(62)

Mar 22, 2023 Jahangir Alam 62

2. Theta Join & Equi Join

SQL*Plus command to execute the class example would be:

SELECT emp.ID, emp.NAME, status.STATUS FROM SELECT emp.ID, emp.NAME, status.STATUS FROM

emp, status WHERE emp.ID < status.ID;

emp, status WHERE emp.ID < status.ID;

If we revert the condition the command would be:

SELECT emp.ID, emp.NAME, status.STATUS FROM SELECT emp.ID, emp.NAME, status.STATUS FROM

emp, status WHERE emp.ID > status.ID;

emp, status WHERE emp.ID > status.ID;

As we know equi join is a special case of theta join when the condition of join is equality, plus it is different from natural join in the way that it contains both copies of duplicate columns, so SQL*Plus command for equi join would be:

SELECT emp.ID, emp.NAME, status.ID, SELECT emp.ID, emp.NAME, status.ID, status.STATUS FROM emp, status WHERE emp.ID = status.STATUS FROM emp, status WHERE emp.ID =

status.ID;

status.ID;

Following figure shows the results of various theta (conditional) join operation:

(63)
(64)

Mar 22, 2023 Jahangir Alam 64

3. Self Join

Consider the example discussed during the lecture of Self Join in which we were interested to “Find the co-workers in all projects (but were not necessarily doing the same job)”, for the ASSIGNMENT relation.

Here we shall answer the query using SQL*Plus commands.

The steps for self join would be as follows:

Copy the ASSIGNMENT table into another table (say COASSIGN).

The command would be:

CREATE TABLE COASSIGN AS SELECT empno, prodno, jobno CREATE TABLE COASSIGN AS SELECT empno, prodno, jobno

FROM ASSIGNMENT;

FROM ASSIGNMENT;

Now we shall join ASSIGNMENT and COASSIGN on prodno field and will select ASSIGNMENT.empno and COASSIGN.empno. That would finally lead to our answer. The command would be:

SELECT ASSIGNMENT.empno, COASSIGN.empno from SELECT ASSIGNMENT.empno, COASSIGN.empno from ASSIGNMENT, COASSIGN WHERE ASSIGNMENT.prodno = ASSIGNMENT, COASSIGN WHERE ASSIGNMENT.prodno =

COASSIGN.prodno;

COASSIGN.prodno;

Following figures illustrate the self join implementation in MySQL:

(65)
(66)
(67)

Mar 22, 2023 Jahangir Alam 67

4. Outer Join

Outer Join is implemented using special operators in SQL.

Each SQL implementation provides its own special operators to implement left and right outer joins.

MySQL provides LEFT JOIN and LEFT JOIN RIGHT JOIN operators RIGHT JOIN for the purpose.

So, the command to implement the left join operation discussed during last unit’s lecture would be:

SELECT * FROM emp LEFT JOIN salary ON emp.ID SELECT * FROM emp LEFT JOIN salary ON emp.ID

= salary.ID;

= salary.ID;

Similarly the right outer join may be obtained as:

SELECT * FROM salary RIGHT JOIN emp ON SELECT * FROM salary RIGHT JOIN emp ON

salary.ID = emp.ID;

salary.ID = emp.ID;

Following figure shows the two outer joins

(68)
(69)

Mar 22, 2023 Jahangir Alam 69

Grouped Queries

So far we have been concentrating on generating detailed lists of results.

In real life there is a huge demand to generate summary of information. For example each of the following situations expects one or more columns that summarizes the data in a table:

How many employees have been retired in last three months?

How many employees are there in each department?

What is the average salary of an employee in a department?

What are the minimum and maximum salaries in a department?

How many employees have not received increments in last six months?

(70)

Mar 22, 2023 Jahangir Alam 70

• SQL provides solution to these queries through the use of column functions or

group functions

group functions in conjunction with the

GROUP BY

GROUP BY and HAVING HAVING

clauses.

(71)

Mar 22, 2023 Jahangir Alam 71

Group Functions

A group function acts on a column of data and produces a single value that summarizes the column.

The argument of a group function may be a column name or an expression. Following Group Functions are supported by SQL*Plus:

Function Name Description

AVG(D/A expr) Compute the average of expr value across the rows.

COUNT(D/A expr) Computes the number of rows when expr is not null.

COUNT(*) Compute the total no. of rows including NULL.

MAX(D/A expr) Gives maximum expression value across rows.

MIN(D/A expr) Gives minimum expression value across rows.

STDDEV(D/A

expr) Gives standard deviation of expression values across rows.

SUM(D/A expr) Computes the sum of expression values across rows.

(72)

Mar 22, 2023 Jahangir Alam 72

Please Note… …

In the above table:

D stands for DISTINCT A stand for ALL

By default all is assumed unless otherwise explicitly stated.

All the group functions except COUNT(*) ignore NULL values.

(73)

Mar 22, 2023 Jahangir Alam 73

Examples

1. List total number of employees.

SELECT COUNT(*) FROM emp;

SELECT COUNT(*) FROM emp;

2. Find how many employees joined the department after 1996?

SELECT COUNT(*) FROM emp WHERE joindate > ‘1996- SELECT COUNT(*) FROM emp WHERE joindate > ‘1996- 12-31’;

12-31’;

3. List total number of employees and average salary.

SELECT COUNT(*), AVG(salary) FROM emp;

SELECT COUNT(*), AVG(salary) FROM emp;

4. Find how many increments Mr. Rajan Singh have got?Also find the sum of these increments.

SELECT COUNT(*), SUM(AMOUNT) FROM increments, SELECT COUNT(*), SUM(AMOUNT) FROM increments, emp WHERE empno=emp.empno AND emp.ename = emp WHERE empno=emp.empno AND emp.ename =

‘Rajan Singh’;

‘Rajan Singh’;

4. How many designations are being used in the organization?

SELECT COUNT(DISTINCT desig) FROM emp;

SELECT COUNT(DISTINCT desig) FROM emp;

(74)
(75)

Mar 22, 2023 Jahangir Alam 75

Please Note… …

Refer to the figure on the next page.

It shows that mixing of group columns with no group columns is illegal if there is no GROUP BY clause.

Clearly the following query is illegal:

SELECT empno, COUNT(empno) from emp;

SELECT empno, COUNT(empno) from emp;

(76)
(77)

Mar 22, 2023 Jahangir Alam 77

All the above examples presented on Grouped Queries produce a single row of summary result.

In more practical situations there may be more summary rows that may be required in the result.

For example, an organization might require department wise the number of employees working, the total salary to be paid to them etc.

In other words the facility for providing subtotals is required.

SQL provides this facility through the GROUP BY GROUP BY clause.

Following examples illustrate the concept:

(78)

Mar 22, 2023 Jahangir Alam 78

1. How many employees are there in each department?

Also calculate the total salary each department is distributing to its employees?

SELECT deptno, COUNT(*),SUM(salary) FROM SELECT deptno, COUNT(*),SUM(salary) FROM

emp GROUP BY deptno;

emp GROUP BY deptno;

(Check how the group columns and non group columns have been mixed with the use of GROUP BY clause)

2. For each department list maximum salary, minimum salary, average salary along with the number of employees in that department.

SELECT deptno, MAX(salary), MIN(salary), SELECT deptno, MAX(salary), MIN(salary), AVG(salary), COUNT(*) FROM emp GROUP BY AVG(salary), COUNT(*) FROM emp GROUP BY

deptno;

deptno;

(79)
(80)

Mar 22, 2023 Jahangir Alam 80

There may be situations when we need to extract certain detailed information on the basis of the results of a group function.

For example we need to print only those departments having “more than 10 employees”.

In such situations HAVING clause is used. For HAVING example consider the following SQL statement:

SELECT deptno, COUNT(*) FROM emp GROUP BY SELECT deptno, COUNT(*) FROM emp GROUP BY

deptno HAVING COUNT(*) > 10;

deptno HAVING COUNT(*) > 10;

Here only those departments will be listed whose

strength will be more than 10.

(81)
(82)

Mar 22, 2023 Jahangir Alam 82

The next example discusses another aspect of grouped queries. Consider the following query:

“List the total number of Lecturers and Programmers for each department”

There may be two solutions to the above query as shown below:

SELECT deptno,desig,count(*) FROM emp SELECT deptno,desig,count(*) FROM emp WHERE desig IN (‘Lecturer’,’Programmer’) WHERE desig IN (‘Lecturer’,’Programmer’)

GROUP BY deptno,desig;

GROUP BY deptno,desig;

OR

SELECT deptno,desig,count(*) FROM emp SELECT deptno,desig,count(*) FROM emp GROUP BY deptno,desig HAVING desin IN GROUP BY deptno,desig HAVING desin IN

(‘Lecturer’,’Programmer’);

(‘Lecturer’,’Programmer’);

(83)
(84)

Mar 22, 2023 Jahangir Alam 84

As a rule the columns in a SELECT statement, that are not group functions will have to be mentioned as a part of the GROUP BY clause.

For example in the above example the columns deptno and desig have been specified in the GROUP BY clause.

There are some restrictions on the use of GROUP BY and HAVING:

The columns to be grouped should be columns from the table(s) specified in the FROM clause and not a calculated column.

The search condition in the having clause can be only one of the following:

Constant

Group Function(s)

Normally HAVING is used only when group functions are

included in search criteria. In other words HAVING clause will be used if group functions appear in the search condition

otherwise a WHERE clause will be used.

(85)

Mar 22, 2023 Jahangir Alam 85

Database Maintenance Activities

1. Modifying (Updating) the Records

UPDATE command is used.

Its general form is:

UPDATE <table_name> SET

<Column_name = expression> WHERE

<condition>

(86)

Mar 22, 2023 Jahangir Alam 86

Examples

1. Change the name of employee Rajan Singh to Mohan Singh.

UPDATE emp SET ename= ‘Mohan Singh’

UPDATE emp SET ename= ‘Mohan Singh’

WHERE ename = ‘Rajan Singh’;

WHERE ename = ‘Rajan Singh’;

2. Change the designations of all Lecturers to Sr.

Lecturers.

UPDATE emp SET desig = ‘Sr. Lecturer’ WHERE UPDATE emp SET desig = ‘Sr. Lecturer’ WHERE

desig = ‘Lecturer’;

desig = ‘Lecturer’;

3. An increment of 10% has been made in the salaries of all Programmers. Make these changes.

UPDATE emp SET salary=(salary +0.1*salary) UPDATE emp SET salary=(salary +0.1*salary)

WHERE desig = ‘Programmer’;

WHERE desig = ‘Programmer’;

(87)
(88)
(89)

Mar 22, 2023 Jahangir Alam 89

2. Deleting Records from a Table

DELETE command is used.

Its general form is:

DELETE FROM <table_name>

WHERE <condition>

If WHERE clause is absent with DELETE, all the records from the table will be deleted.

In Sql*Plus the command ZAP also deletes all records of a ZAP table. This command is not supported by MySql.

(90)

Mar 22, 2023 Jahangir Alam 90

Please Note… …

In SQL*Plus this command just marks the record (s) for deletion (i.e. performs logical deletion).

It simply means that the deleted record (s) can be called back by executing the ROLLBACK command.ROLLBACK

To permanently delete the record (s) we need to execute the COMMIT command, as soon as we mark COMMIT the record for deletion. Once committed, record (s) can’t be called back.

In MySql this is not the default behaviour. Here, a record (s) once deleted with DELETE command is deleted forever.

(91)

Mar 22, 2023 Jahangir Alam 91

Examples

1. Delete all B grade employees from the emp table.

DELETE FROM emp WHERE grade=‘B’;

DELETE FROM emp WHERE grade=‘B’;

2. DELETE all records form the table salary.

DELETE FROM salary;

DELETE FROM salary;

(92)

Mar 22, 2023 Jahangir Alam 92

3. Changing the Existing Structure

By changing the structure we mean that we that want to modify our table definition (i.e. length of columns, their types) etc. The current structure of a table can be listed using the DESCRIBE <table_name> (or DESCRIBE <table_name> (or

DESC <table_name>)

DESC <table_name>) command.

In Sql*Plus, we can perform following operations on a table structure:

We can add a new column in the existing table structure.

We can also change/ modify the data_type of a column.

But, we can’t delete/ rename a column of a table directly. Though, it can be done indirectly.

From this point of view there are major differences between Sql*Plus and MySql, so we shall not discuss examples from MySql for this topic.

(93)

Mar 22, 2023 Jahangir Alam 93

Anyways, for modifying the tables ALTER TABLE command is used.

Its general form is:

ALTER TABLE <table_name> ADD <column_name characteristics>

OR

ALTER TABLE <table_name>

MODIFY <column_name new-

data-type>

(94)

Mar 22, 2023 Jahangir Alam 94

Please Note:

When a column is added, it is appended to the end of the column definitions for the table, and appears as the rightmost column in subsequent queries.

The RDBMS automatically places null values for the new column in all rows of the table. Therefore NOT NULL attribute can’t be specified as a part of ADD clause.

If more than one columns are to be added, they will be added using separate ALTER TABLE commands.

When a column with NOT NULL attribute is modified, the column automatically loses this attribute.

(95)

Mar 22, 2023 Jahangir Alam 95

Examples

1.1. ALTER TABLE emp ADD REMARKS CHAR(40);ALTER TABLE emp ADD REMARKS CHAR(40);

2.2. ALTER ALTER TABLE TABLE emp emp MODIFY MODIFY salary salary NUMBER(13,4);

NUMBER(13,4);

3.3. ALTER TABLE emp MODIFY REMARKS CHAR(50);ALTER TABLE emp MODIFY REMARKS CHAR(50);

(96)

Mar 22, 2023 Jahangir Alam 96

How to Delete (or Change Name) of a Column

Create a new table say xemp without the column to be deleted (or without the new column name if the case is of renaming the column) as shown below (say we want to delete the column desig):

CREATE TABLE xemp AS SELECT CREATE TABLE xemp AS SELECT

empno,ename,grade,salary,deptno,joindate empno,ename,grade,salary,deptno,joindate

FROM emp;

FROM emp;

Remove the table emp DROP TABLE emp;

DROP TABLE emp;

Rename xemp as emp

RENAME xemp TO emp;

RENAME xemp TO emp;

(97)

Mar 22, 2023 Jahangir Alam 97

4. Deleting a Table

DROP TABLE command is used.

Its general form is:

DROP TABLE <table_name>

(98)

Mar 22, 2023 Jahangir Alam 98

Views

In a typical database environment, there may be a necessity for more than one user to access the same data.

In such cases there may be a need to restrict access to one or more tables to certain specified columns.

This is where the concept of views comes into picture.

In brief the benefits of using the views are:

Complex queries can be simplified.

Repetitive tasks can be eliminated/ reduced( As view can be created from multiple tables, multiple select statements can be avoided).

Increased data security and integrity.

(99)

Mar 22, 2023 Jahangir Alam 99

Creating Views

CREATE VIEW command is used.

Its general form is:

CREATE VIEW <view_name>

[column_list] AS Query

A view can consist columns from a single table or from multiple tables.

(100)

Mar 22, 2023 Jahangir Alam 100

Consider the following query:

“List employees along with their deptno,dname and joindate”

We will use the view approach and will create the view of columns specified in the query as:

CREATE VIEW voemp (empno, ename, deptno, CREATE VIEW voemp (empno, ename, deptno, deptname, joindate) AS SELECT empno, ename, deptname, joindate) AS SELECT empno, ename, dept.deptno, dept.deptname, joindate FROM dept.deptno, dept.deptname, joindate FROM

emp,dept WHERE emp.deptno = dept.deptno;

emp,dept WHERE emp.deptno = dept.deptno;

Once view has been created, it can be used just like any table. SELECT, INSERT, UPDATE and DELETE operations can be performed on views in the same way as they can be performed on tables.

Following example illustrates:

(101)
(102)

Mar 22, 2023 Jahangir Alam 102

Destroying Views

DROP VIEW command is used.

Its general form is:

DROP VIEW <view_name>

A very obvious disadvantage of creating the views is performance degradation.

Actually, when SQL executes the SELECT statement, it scans the data dictionary for the presence of the view/

table.

Once it determines that a particular view exists, it picks up the data for the view from the data dictionary and applies them on the base table to get the required result.

Clearly the process slows down the query response.

References

Related documents

Corporations such as Coca Cola (through its Replenish Africa Initiative, RAIN, Reckitt Benckiser Group and Procter and Gamble have signalled their willingness to commit

These gains in crop production are unprecedented which is why 5 million small farmers in India in 2008 elected to plant 7.6 million hectares of Bt cotton which

INDEPENDENT MONITORING BOARD | RECOMMENDED ACTION.. Rationale: Repeatedly, in field surveys, from front-line polio workers, and in meeting after meeting, it has become clear that

Section 2 (a) defines, Community Forest Resource means customary common forest land within the traditional or customary boundaries of the village or seasonal use of landscape in

The scan line algorithm which is based on the platform of calculating the coordinate of the line in the image and then finding the non background pixels in those lines and

From the first left half under source head click on CORROSPONDING, from 2 nd half under Files section click “ Select All”. In the 3 rd part click on “Receipt” and then click

The Education Cess on income-tax shall continue to be levied at the rate of two per cent on the amount of tax computed inclusive of surcharge. In addition, the amount of tax

Lower intensity in small-scale structures could also hint at CNM that has already converted to molecular gas (see Sect. If we want to infer the global multiphase column density