INSTR(x, find_string [, start] [, occurrence]); Searches for find_string in x and returns the position at which it occurs. 6 INSTRB(x); Returns the location of a string within another string, but returns the value in bytes. 7 LENGTH(x); Returns the number of characters in x. 8 LENGTHB(x); Returns the length of a character string in bytes for single byte character set. 9 LOWER(x);
Converts the letters in x to lowercase and returns that string. 10 LPAD(x, width [, pad_string]) ; Pads x with spaces to the left, to bring the total length of the string up to width characters. 11 LTRIM(x [, trim_string]); Trims characters from the left of x. 12 NANVL(x, value); Returns value if x matches the NaN special value (not a number), otherwise x is returned. 13 NLS_INITCAP(x);
Same as the INITCAP function except that it can use a different sort method as specified by NLSSORT. 14 NLS_LOWER(x) ; Same as the LOWER function except that it can use a different sort method as specified by NLSSORT. 15 NLS_UPPER(x); Same as the UPPER function except that it can use a different sort method as specified by NLSSORT. 16 NLSSORT(x); Changes the method of sorting the characters. Must be specified before any NLS function; otherwise, the default sort will be used. 17
NVL(x, value); Returns value if x is null; otherwise, x is returned. 18 NVL2(x, value1, value2); Returns value1 if x is not null; if x is null, value2 is returned. 19 REPLACE(x, search_string, replace_string); Searches x for search_string and replaces it with replace_string. 20 RPAD(x, width [, pad_string]); Pads x to the right. 21 RTRIM(x [, trim_string]);
Trims x from the right. 22 SOUNDEX(x) ; Returns a string containing the phonetic representation of x. 23 SUBSTR(x, start [, length]); Returns a substring of x that begins at the position specified by start. An optional length for the substring may be supplied. 24 SUBSTRB(x); Same as SUBSTR except that the parameters are expressed in bytes instead of characters for the single-byte character systems. 25
TRIM([trim_char FROM) x); Trims characters from the left and right of x. 26 UPPER(x); Converts the letters in x to uppercase and returns that string. Let us now work out on a few examples to understand the concept − Example 1 DECLARE greetings varchar2(11) := 'hello world'; BEGIN dbms_output.put_line(UPPER(greetings)); dbms_output.put_line(LOWER(greetings)); dbms_output.put_line(INITCAP(greetings));
/* retrieve the first character in the string */ dbms_output.put_line ( SUBSTR (greetings, 1, 1)); /* retrieve the last character in the string */ dbms_output.put_line ( SUBSTR (greetings, -1, 1)); /* retrieve five characters, starting from the seventh position. */ dbms_output.put_line ( SUBSTR (greetings, 7, 5)); /* retrieve the remainder of the string, starting from the second position. */ dbms_output.put_line ( SUBSTR (greetings, 2)); /* find the location of the first \"e\" */ dbms_output.put_line ( INSTR (greetings, 'e')); END; / When the above code is executed at the SQL prompt, it produces the following result − HELLO WORLD
hello world Hello World h d World ello World 2 PL/SQL procedure successfully completed. Sample code Output:
PRACTICAL UNIT 9: USE OF AGGREGATE FUNCTIONS 1 Write a SQL statement to get the minimum purchase amount of all the orders. Sample table: orders AGGREGATE FUNCTION: An aggregate function performs a calculation on a set of values, and returns a single value. Except for COUNT(*), aggregate functions ignore null values. Aggregate functions are often used with the GROUP BY clause of the SELECT statement. All aggregate functions are deterministic. In other words, aggregate functions return the same value each time that they are called, when called with a specific set of input values. Types of Aggregate Functions: COUNT – counts the number of elements in the group defined. SUM – calculates the sum of the given attribute/expression in the group defined. AVG – calculates the average value of the given attribute/expression in the group defined. MIN – finds the minimum in the group defined. MAX – finds the maximum in the group defined. Program:
Statement Output of the Query 2 Write a query in SQL to find the number of employees in each department along with the department code. Sample table: emp_details Query
Output of the Query:
PRACTICAL UNIT 10: USE OF USE OF GRANT AND REVOKE Use of GRANT 1 How to grant permission to a user to select only from localhost. Syntax Example: 2 How to grant a user permission to create, insert, update, delete and create temporary tables from localhost Syntax Example
Use of REVOKE 1 Torevoke a CREATE TABLE privilege from testing ROLE The Syntax for the REVOKE command is: Example 2 Revoking the INSERT Privilege, revokes the INSERT privilege from UserA on the employee table: Statement
PRACTICAL UNIT 11: USE OF IN, BETWEEN, LIKE, ORDER BY, GROUP BY AND HAVING USE OF IN, BETWEEN, LIKE, ORDER BY, GROUP BY and HAVING The HAVING clause is like WHERE but operates on grouped records returned by a GROUP BY. HAVING applies to summarized group records, whereas WHERE applies to individual records. Only the groups that meet the HAVING criteria will be returned. HAVING requires that a GROUP BY clause is present. Both WHERE and HAVING can be used in the same query at the same time. The SQL HAVING syntax The general syntax is SELECT column-names FROM table-name WHERE condition GROUP BY column-names HAVING condition The general syntax with ORDER BY is: SELECT column-names FROM table-name WHERE condition GROUP BY column-names HAVING condition ORDER BY column-names CUSTOMER Id FirstName
LastName City Country Phone SQL GROUP BY Examples Problem: List the number of customers in each country. Only include countries with more than 10 customers. SELECT COUNT(Id), Country FROM Customer GROUP BY Country HAVING COUNT(Id) > 10 Result: 3 records Count Country 11 France 11 Germany 13 USA LIKE OPERATOR: The LIKE in SQL is used to extract records where a particular pattern is present. For example, imagine that we want to extract a person’s name that includes ‘john’. So, if there are three people with names johnathan, johnny, and marcus, then with help of the LIKE in SQL, we can extract records where the names are Johnathan and johnny, since they have the required pattern present in them. The LIKE in SQL is used in conjunction with wildcard characters, such as: Percentage symbol: It is a substitution for zero, one, or more characters. Underscore symbol: It is a substitution for a single character. BETWEEN OPERATOR The BETWEEN operator in SQL is used to select values within a given range. For example, to extract only those records where the age of the person is between 20 and 25, we have to use the SQL BETWEEN operator.
SQL Between Syntax SELECT column_list FROM tablename Where column_N BETWEEN val1 AND val2; where SELECT, FROM, WHERE, BETWEEN, and column are the keywords, column_list is a list of columns, tablename is the name of the table, column_N is the column name, and val1 is the minimum value of the range and val2 is the maximum value of the range, followed by a semicolon. Let’s, now, display employees aging between 25 and 35 from the employee table Select * From employee where e_age BETWEEN 25 AND 35; Use of IN 1 Use the IN operator to select all the records where Country is either \"Norway\" or \"France\". Query: SELECT * FROM Customers 'France' ; Output: 2 Use the IN operator to select all the records where Country is NOT \"Norway\" and NOT \"France\". Query
SELECT * FROM Customers ('Norway', 'France'); Output USE of BETWEEN 1 Use the BETWEEN operator to select all the records where the value of the Price column is between 10 and 20. Query: SELECT * FROM Products WHERE Price ; Output 2 Use the BETWEEN operator to select all the records where the value of the Price column is NOT between 10 and 20. Query:
SELECT * FROM Products WHERE Price ; Output Use of LIKE 1 Select all records where the value of the City column starts with the letter \"a\". Query: SELECT * FROM Customers ; Output 2 Select all records where the value of the City column ends with the letter \"a\". Query SELECT * FROM Customers ;
Output Use of ORDER BY 1 Select all records from the Customers table, sort the result alphabetically by the column City. Query: SELECT * FROM Customers Output 2 Select all records from the Customers table, sort the result reversed alphabetically by the column City. Query: SELECT * FROM Customers ; Output
Use ofGROUP BY 1 List the number of customers in each country. Query: SELECT (CustomerID), Country FROM Customers ; Output 2 List the number of customers in each country, ordered by the country with the most customers first. Query: SELECT (CustomerID), Country FROM Customers
ORDER BY ; Output Use of HAVING The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions. HAVING Syntax SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s); 1 Find the number of available computers produced by the maker 1. Code
2. 3. Output 2 Find the minimal and maximal prices for PCs: Code 4. Output
PRACTICAL UNIT 12: PROGRAMS RELATED TO TRIGGER 1 Create a trigger which ensures that we don’t enter any Admin who has a salary less than 1000. TRIGGER: Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events − A database manipulation (DML) statement (DELETE, INSERT, or UPDATE) A database definition (DDL) statement (CREATE, ALTER, or DROP). A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN). Triggers can be defined on the table, view, schema, or database with which the event is associated. Benefits of Triggers Triggers can be written for the following purposes − Generating some derived column values automatically Enforcing referential integrity Event logging and storing information on table access Auditing Synchronous replication of tables Imposing security authorizations Preventing invalid transactions
Creating Triggers The syntax for creating a trigger is − CREATE [OR REPLACE ] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF } {INSERT [OR] | UPDATE [OR] | DELETE} [OF col_name] ON table_name [REFERENCING OLD AS o NEW AS n] [FOR EACH ROW] WHEN (condition) DECLARE Declaration-statements BEGIN Executable-statements EXCEPTION Exception-handling-statements END; Where,
CREATE [OR REPLACE] TRIGGER trigger_name − Creates or replaces an existing trigger with the trigger_name. {BEFORE | AFTER | INSTEAD OF} − This specifies when the trigger will be executed. The INSTEAD OF clause is used for creating trigger on a view. {INSERT [OR] | UPDATE [OR] | DELETE} − This specifies the DML operation. [OF col_name] − This specifies the column name that will be updated. [ON table_name] − This specifies the name of the table associated with the trigger. [REFERENCING OLD AS o NEW AS n] − This allows you to refer new and old values for various DML statements, such as INSERT, UPDATE, and DELETE. [FOR EACH ROW] − This specifies a row-level trigger, i.e., the trigger will be executed for each row being affected. Otherwise the trigger will execute just once when the SQL statement is executed, which is called a table level trigger. WHEN (condition) − This provides a condition for rows for which the trigger would fire.
This clause is valid only for row-level triggers. Example To start with, we will be using the CUSTOMERS table we had created and used in the previous chapters − Select * from customers; ID Name Age Address Salary Ahmedabad 2000.00 1 Ramesh 32 Delhi 1500.00 Kota 2000.00 2 Khilan 25 Mumbai 6500.00 Bhopal 8500.00 3 kaushik 26 MP 4500.00 4 Chaitali 25 5 Hardik 27 6 Komal 22 The following program creates a row-level trigger for the customers table that would fire for INSERT or UPDATE or DELETE operations performed on the CUSTOMERS table. This trigger will display the salary difference between the old values and new values −
CREATE OR REPLACE TRIGGER display_salary_changes BEFORE DELETE OR INSERT OR UPDATE ON customers FOR EACH ROW WHEN (NEW.ID > 0) DECLARE sal_diff number; BEGIN sal_diff := :NEW.salary - :OLD.salary; dbms_output.put_line('Old salary: ' || :OLD.salary); dbms_output.put_line('New salary: ' || :NEW.salary); dbms_output.put_line('Salary difference: ' || sal_diff); END; / When the above code is executed at the SQL prompt, it produces the following result − Trigger created. Code:
Output 2 Code Implementation for trigger creation: Output of the above code: BEFORE INSERT 1 Write a trigger to ensure that no employee of age less than 25 can be inserted in the database.
BEFORE DELETE 1 Create a trigger which will work before deletion in employee table and create a duplicate copy of the record in another table employee_backup. AFTER INSERT 1 Write a trigger to count number of new tupples inserted using each insert statement.
PRACTICAL UNIT 13: CREATION OF VIEWS, USERS, ALIASES OF COLUMN Creation of Views In SQL, a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL statements and functions to a view and present the data as if the data were coming from one single table. A view is created with the CREATE VIEW statement. CREATE VIEW Syntax CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition; example: CREATE VIEW [Brazil Customers] AS SELECT CustomerName, ContactName FROM Customers WHERE Country = 'Brazil'; Output: You have made changes to the database. 1 Write a query to create a view for those salesmen belongs to the city New York. Sample table: salesman
Sample code Sample Output: 2 Write a query to create a view for all salesmen with columns salesman_id, name, and city. Sample table: salesman
Sample code: Output Now UPDATE the city name which salesman_id is '5007'. Output
Creation of users 1 create a user and granting no privileges 2 Create renames a user Syntax: Example aliases of column 1 When displaying the Customers table, make an ALIAS of the PostalCode column, the column should be called Pno instead. Query :
SELECT CustomerName, Address, PostalCode FROM Customers; Output 2 When displaying the Customers table, refer to the table as Consumers instead of Customers Query: SELECT * FROM Customers ; Output
PRACTICAL UNIT 14: PROGRAMS TO IMPLEMENT STORED PROCEDURES STORED PROCEDURES: A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed. Stored Procedure Syntax CREATE PROCEDURE procedure_name AS sql_statement GO; Execute a Stored Procedure EXEC procedure_name; 1 Displays The Welcome Message On The Screen Code:
Output 2 Below is a selection from the \"Customers\" table in the Northwind sample database: Creates a stored procedure that selects Customers from a particular City from the \"Customers\" table Code:
PRACTICAL UNIT 15: PROGRAMS TO IMPLEMENT EXCEPTION HANDLING 1. EXCEPTION HANDLING An exception is an error condition during a program execution. PL/SQL supports programmers to catch such conditions using EXCEPTION block in the program and an appropriate action is taken against the error condition. There are two types of exceptions − System-defined exceptions User-defined exceptions Syntax for Exception Handling The general syntax for exception handling is as follows. Here you can list down as many exceptions as you can handle. The default exception will be handled using WHEN others THEN − DECLARE <declarations section> BEGIN <executable command(s)> EXCEPTION <exception handling goes here > WHEN exception1 THEN exception1-handling-statements WHEN exception2 THEN exception2-handling-statements WHEN exception3 THEN exception3-handling-statements ........ WHEN others THEN exception3-handling-statements END; Example
Let us write a code to illustrate the concept. We will be using the CUSTOMERS table we had created and used in the previous chapters − DECLARE c_id customers.id%type := 8; c_name customerS.Name%type; c_addr customers.address%type; BEGIN SELECT name, address INTO c_name, c_addr FROM customers WHERE id = c_id; DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name); DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr); EXCEPTION WHEN no_data_found THEN dbms_output.put_line('No such customer!'); WHEN others THEN dbms_output.put_line('Error!'); END; / When the above code is executed at the SQL prompt, it produces the following result − No such customer! PL/SQL procedure successfully completed. 1 Simple PL/SQL code block, to demonstrate the use of Named Exception Handler Code:
Output User-defined Exception 1 Using the below table student to demonstrate the use of User-defined Exception. Code
Output Numbered Exception Handling 1 Using the below table student to demonstrate the use of Numbered Exception.
Code Output
Search
