Introduction to SQL Jahangir Alam University Womens Polytechnic

Introduction to SQL Jahangir Alam University Women’s Polytechnic AMU Aligarh (India) - 202002 1

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 1970 s 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: 18 Jan 22 Jahangir Alam 2

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 18 Jan 22 Jahangir Alam of the time we shall 3 focus on MS – Access/ My. Sql RDBMSs when executing

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> 18 Jan 22 Jahangir Alam 4

1. Creating a Table: • CREATE TABLE command is used. • Its general form for is: CREATE TABLE <table-name> (columndefinition 1, column-definition 2, columndefinition 3 … … … ); 18 Jan 22 Jahangir Alam 5

Examples Ex 1: CREATE TABLE Dept (Deptno CHAR(5) NOT NULL PRIMARY KEY, Deptname CHAR(20)); 18 Jan 22 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 Deptno); Jahangir Alam 6

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 My. Sql (with changes in syntax wherever required) has been demonstrated in the following figures: 18 Jan 22 Jahangir Alam 7

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) 18 Jan 22 Jahangir Alam 10

Examples Ex 1: INSERT INTO Emp (empno, ename, desig, grad e, salary, deptno) VALUES (‘ 100’, ’Rajan Singh’, ’Engineer’, ’A’, 20000 , ’ 213’); 18 Jan 22 Ex 2: INSERT INTO Emp (empno, ename, desig, grad e, salary, deptno) VALUES (‘&1’, ’&2’, ’&3’, ’&4’, &5, ’&6’) ; Jahangir Alam 11

Note the Following • Ex 2 makes use of parameter substitution. Unfortunately, parameter substitution is not available with My. Sql. 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 user can create several rows in the table. In My. Sql we have to edit the same command 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 query 1 18 Jan 22 Jahangir Alam 12

• SQL * Plus saves the contents of buffer in a file called query 1. sql. Now to run the query we can use following commands SQL> GET query 1 SQL> RUN • Im My. Sql 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 My. SQL or MYSql Yog or My. Sql Query Browser etc. • The execution of EX 1 in My. Sql is shown in the figure on the next page: 18 Jan 22 Jahangir Alam 13

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> 18 Jan 22 Jahangir Alam 15

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; • 18 Jan 22 Jahangir Alam 16

• Conditional SELECT takes the following general form: SELECT */ Colunm_List FROM <table_name> WHERE <condition> 18 Jan 22 Jahangir Alam 18

Conditional SELECT Examples 1. SELECT * FROM emp WHERE grade = ‘A’; 2. SELECT 3. 4. 5. 6. 7. empno, ename, grade, salary FROM emp WHERE desig = ‘Lecturer’; SELECT * FROM emp WHERE grade = ‘A’ OR grade = ‘B’; SELECT * FROM emp WHERE salary>=31000 AND salary<=40000; SELECT * from emp WHERE (salary+(salary*1. 5))>80000; SELECT empno, ename, desig FROM emp WHERE grade <> ‘A’ AND salary > 5000. 00; SELECT * FROM emp WHERE NOT (grade = ‘A’); 18 Jan 22 Jahangir Alam 19

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. 18 Jan 22 Jahangir Alam 22

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. 18 Jan 22 Jahangir Alam 23

Examples on Special Operators • Get details of only those employees who have joined in the period September 1 st 1990 – March 31 st 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’; 18 Jan 22 Jahangir Alam 24

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>] 18 Jan 22 Jahangir Alam 30

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. 18 Jan 22 Jahangir Alam 31

5. Inserting Records from an Existing Table into another Table • Its general form is: INSERT INTO <table name>[Column List] query; Example INSERT INTO OEmp (empno, ename, deptno, joindate, salary) SELECT empno, ename, deptno, joindate, salary FROM emp WHERE desig=‘Engineer’; 18 Jan 22 Jahangir Alam 33

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: 18 Jan 22 Jahangir Alam 35

Operator UNION INTERSECT MINUS 18 Jan 22 Purpose Combines the rows from two or more queries to return all distinct rows selected by each of the individual queries. Combines the rows from two or more queries to return only those rows which are common to both the individual queries. 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 Jahangir Alam 36

Examples 1. SELECT DISTINCT desig FROM Emp WHERE deptno = ’ 10’ UNION SELECT DISTINCT desig FROM Emp WHERE deptno = ’ 30’; 2. SELECT DISTINCT desig FROM Emp WHERE deptno = ’ 10’ INTERSECT SELECT DISTINCT desig FROM Emp WHERE deptno = ’ 30’; 3. SELECT DISTINCT desig FROM Emp WHERE deptno = ’ 10’ MINUS SELECT DISTINCT desig FROM Emp WHERE deptno =Jahangir ’ 30’; 18 Jan 22 Alam 37

My. SQL doesn’t support INTERSECT and MINUS

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 E 2 1103 E 2 1109 1110 E 3 1114 E 4 18 Jan 22 Jahangir Alam 39

• Now, consider the following query: SELECT empno, grade FROM Emp WHERE grade <>’E 2’ AND deptno=‘ 100’; • The answer would be: EMPNO GRADE 1110 E 3 1114 E 4 • However the answer should be: EMPNO GRADE 1109 1110 E 3 1114 E 4 18 Jan 22 Jahangir Alam 40

• 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 or IS NOT NULL operators. Following example illustrates. 18 Jan 22 Jahangir Alam 41

EXAMPLES 1. SELECT empno, ename FROM emp WHERE grade IS NULL; 2. SELECT empno, ename FROM emp WHERE grade IS NOT NULL; 18 Jan 22 Jahangir Alam 42

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. 18 Jan 22 Jahangir Alam 44

Examples Ex 1: SELECT empno, ename, grade, joindate, deptno FROM Emp WHERE joindate BETWEEN ‘ 1 01 92’ AND ’ 10 04 98’ ORDER BY joindate; 18 Jan 22 Ex 2: SELECT empno, ename, grade, joindate, deptno FROM Emp WHERE deptno = ’ 100’ ODRER BY salary DESC; Jahangir Alam 45

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] 18 Jan 22 Jahangir Alam 47

• 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 FROM increments WHERE incdate BETWEEN ‘ 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 <> (101, 103, 107); 18 Jan 22 Jahangir Alam 48

• 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: 18 Jan 22 Jahangir Alam 49

Operator IN NOT IN Purpose 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. 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. 18 Jan 22 Jahangir Alam 50

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. 18 Jan 22 Jahangir Alam 53

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. 18 Jan 22 Jahangir Alam 54

Arithmetic Functions • Following table shows some important arithmetic functions and the next two figures show they are supported by My. SQL: 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. *My. Sql supports POWER(m, n) as POW(m, n). **Not Supported by My. Sql. 18 Jan 22 Jahangir Alam 55

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 (/My. SQL). 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: 18 Jan 22 Jahangir Alam 58

1. Natural Join • Consider the E (created as emp in My. Sql) and S (Created as Salary in My. SQL) relations (discussed in previous unit’s lecture) and consider the following query: “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 FROM Emp, Salary WHERE EMP. ID = SALARY. ID; 18 Jan 22 Jahangir Alam 60

2. Theta Join & Equi Join • SQL*Plus command to execute the class example would be: SELECT emp. ID, emp. NAME, status. STATUS FROM emp, status WHERE emp. ID < status. ID; • If we revert the condition the command would be: SELECT emp. ID, emp. NAME, status. STATUS FROM 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, status. STATUS FROM emp, status WHERE emp. ID = status. ID; • Following figure shows the results of various theta (conditional) join operation: 18 Jan 22 Jahangir Alam 62

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 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 ASSIGNMENT, COASSIGN WHERE ASSIGNMENT. prodno = COASSIGN. prodno; • Following figures illustrate the self join implementation in My. SQL: 18 Jan 22 Jahangir Alam 64

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. My. SQL provides LEFT JOIN and RIGHT JOIN operators 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 = salary. ID; Similarly the right outer join may be obtained as: SELECT * FROM salary RIGHT JOIN emp ON salary. ID = emp. ID; Following figure shows the two outer joins 18 Jan 22 Jahangir Alam 67

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? 18 Jan 22 Jahangir Alam 69

• SQL provides solution to these queries through the use of column functions or group functions in conjunction with the GROUP BY and HAVING clauses. 18 Jan 22 Jahangir Alam 70

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 AVG(D/A expr) Description 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. 18 Jan 22 Jahangir Alam 71

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. 18 Jan 22 Jahangir Alam 72

Examples 1. List total number of employees. 2. 3. 4. SELECT COUNT(*) FROM emp; Find how many employees joined the department after 1996? SELECT COUNT(*) FROM emp WHERE joindate > ‘ 199612 -31’; List total number of employees and average salary. SELECT COUNT(*), AVG(salary) FROM emp; Find how many increments Mr. Rajan Singh have got? Also find the sum of these increments. SELECT COUNT(*), SUM(AMOUNT) FROM increments, emp WHERE empno=emp. empno AND emp. ename = ‘Rajan Singh’; How many designations are being used in the organization? SELECT COUNT(DISTINCT desig) FROM emp; 18 Jan 22 Jahangir Alam 73

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; 18 Jan 22 Jahangir Alam 75

• 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 clause. Following examples illustrate the concept: 18 Jan 22 Jahangir Alam 77

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 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), AVG(salary), COUNT(*) FROM emp GROUP BY deptno; 18 Jan 22 Jahangir Alam 78

• 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 example consider the following SQL statement: SELECT deptno, COUNT(*) FROM emp GROUP BY deptno HAVING COUNT(*) > 10; Here only those departments will be listed whose strength will be more than 10. 18 Jan 22 Jahangir Alam 80

• 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 WHERE desig IN (‘Lecturer’, ’Programmer’) GROUP BY deptno, desig; OR SELECT deptno, desig, count(*) FROM emp GROUP BY deptno, desig HAVING desin IN (‘Lecturer’, ’Programmer’); 18 Jan 22 Jahangir Alam 82

• 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: p p 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. 18 Jan 22 Jahangir Alam 84

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> 18 Jan 22 Jahangir Alam 85

Examples 1. Change the name of employee Rajan Singh to Mohan Singh. UPDATE emp SET ename= ‘Mohan Singh’ WHERE ename = ‘Rajan Singh’; 2. Change the designations of all Lecturers to Sr. Lecturers. UPDATE emp SET desig = ‘Sr. Lecturer’ WHERE 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) WHERE desig = ‘Programmer’; 18 Jan 22 Jahangir Alam 86

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 table. This command is not supported by My. Sql. 18 Jan 22 Jahangir Alam 89

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. • To permanently delete the record (s) we need to execute the COMMIT command, as soon as we mark the record for deletion. Once committed, record (s) can’t be called back. • In My. Sql this is not the default behaviour. Here, a record (s) once deleted with DELETE command is deleted forever. 18 Jan 22 Jahangir Alam 90

Examples 1. Delete all B grade employees from the emp table. DELETE FROM emp WHERE grade=‘B’; 2. DELETE all records form the table salary. DELETE FROM salary; 18 Jan 22 Jahangir Alam 91

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 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 My. Sql, so we shall not discuss examples from My. Sql for this topic. 18 Jan 22 Jahangir Alam 92

• 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 newdata-type> 18 Jan 22 Jahangir Alam 93

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. 18 Jan 22 Jahangir Alam 94

Examples 1. ALTER TABLE emp ADD REMARKS CHAR(40); 2. ALTER TABLE NUMBER(13, 4); emp MODIFY salary 3. ALTER TABLE emp MODIFY REMARKS CHAR(50); 18 Jan 22 Jahangir Alam 95

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 empno, ename, grade, salary, deptno, joindate FROM emp; • Remove the table emp DROP TABLE emp; • Rename xemp as emp RENAME xemp TO emp; 18 Jan 22 Jahangir Alam 96

4. Deleting a Table • DROP TABLE command is used. • Its general form is: DROP TABLE <table_name> 18 Jan 22 Jahangir Alam 97

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. 18 Jan 22 Jahangir Alam 98

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. 18 Jan 22 Jahangir Alam 99

• 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, deptname, joindate) AS SELECT empno, ename, deptno, deptname, joindate FROM emp, dept WHERE emp. deptno = 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: 18 Jan 22 Jahangir Alam 100

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. 18 Jan 22 Jahangir Alam 102