Download Intro to Database - Lab Manual 3 and more Lecture notes Introduction to Database Management Systems in PDF only on Docsity!
USMAN INSTITUTE OF TECHNOLOGY
Department of Computer Science
CS311 Introduction to Database
Systems
Lab#
Objective:
- Single-row and multiple-row functions in SQL .
Name of Student: _____________________________________________
Roll No: ______________________________Sec. ___________
Date of Experiment: ___________________________________________
Marks Obtained/Remarks: _____________________________
Signature: _____________________________
THEORY
SQL functions
Functions are a very powerful feature of SQL and can be used to do the following tasks: ■ Perform calculations on data ■ Modify individual data items ■ Manipulate output for groups of rows ■ Format dates and numbers for display ■ Convert column datatypes
Database Management Systems Lab Session 04 NED University of Engineering & Technology – Department of Computer & Information Systems Engineering
OutputInputFunctionargargargResult Value^123
SQL functions may accept arguments and always return a value as illustrated in figure 4.1.
Figure 4.1: Functions accept arguments and return a value
Types of SQL functions
There are two distinct types of functions: ■ Single-row ■ Multiple-row Single-Row functions These functions operate on single rows only and return one result per row. There are different types of single-row functions. This session covers the following ones: ■ Character ■ Number ■ Date ■ Conversion Multiple-Row functions These functions manipulate groups of rows to give one result per group of rows.
Single-Row Functions
Character Functions Single-row character functions accept character data as input and can return both character and number values. Character functions can be divided into the following: -
■ Case conversion functions ■ Character manipulation functions
Case Conversion Functions Convert case for character strings Function Result LOWER(‘SQL Course’) sql course UPPER(‘SQL Course’) SQL COURSE INITCAP(‘SQL Course’) Sql Course Table 4. Examples i. To print an employee name (first letter capital) and job title (lower case) SELECT ‘The job title for ‘ || INITCAP(ename) || ‘ is ‘ || LOWER(job) AS “EMPLOYEE DETAILS” FROM emp; ii. To display the employee number, name (in upper case) and department number for employee Blake. SELECT empno, UPPER(ename), deptno FROM emp WHERE LOWER(ename) = ‘blake’; Note : Since the actual case of the letters in the employee name column may not be known, so it is necessary for comparison to convert the name to either uppercase or lowercase.
Character manipulation functions Manipulate character strings
Lab No 3
SYSDATE is a date function that returns the current date and time. The current date can be displayed by selecting SYSDATE from a table. It is customary to select SYSDATE from a dummy table called DUAL. The DUAL table is owned by the user SYS and can be accessed by all users. It contains one column, DUMMY, and one row with the value X. It is useful for returning a value once only – for instance, the value of a constant, pseudocolumn, or expression that is not derived from a table with user data. For example, to display the current date using the DUAL table as SELECT SYSDATE FROM DUAL;
Arithmetic with Dates We can add or subtract a number to or from a date for a resultant date value. For example, to display the name and the number of weeks employed for all employees in department 10. SELECT ename, (SYSDATE – HIREDATE) / 7 “Number of Weeks” FROM emp WHERE deptno = 10;
Date functions operate on Oracle dates. All date functions return a value of DATE datatype except MONTHS_BETWEEN, which returns a numeric value.
Function Result Description MONTHS_BETWEEN(’01-SEP-95’, ’11-JAN-94’) (^) 19.6774194 Number of months between two dates
ADD_MONTHS(’11-JAN-94’, 6) (^) ’11-JUL-94’ Add calendar months to dates
NEXT_DAY(’01-SEP-95’, ‘FRIDAY’) (^) ’08-SEP-95’ Next day of the date specified
LAST_DAY(’01-SEP-95’) (^) ’30-SEP-95’ Last day of the month
ROUND(TO_DATE(’25-JUL-95’, ‘DD-MON-YY’), ‘MONTH’)
01-AUG-95 Round date
ROUND(TO_DATE(’25-JUL-95’, ‘DD-MON-YY’), ‘YEAR’)
01-JAN-96 Round date
TRUNC(TO_DATE(’25-JUL-95’, ‘DD-MON-YY’), ‘MONTH’)
01-JUL-95 Truncate date
TRUNC(TO_DATE(’25-JUL-95’, ‘DD-MON-YY’), ‘YEAR’)
01-JAN-95 Truncate date
Table 4.
Examples
i. For all employees employed for fewer than 200 months, display the employee number, hiredate, number of months employed, six-month review date, first Friday after hiredate and last day of the month hired. SELECT empno, hiredate, MONTHS_BETWEEN(SYSDATE, hiredate) TENURE, ADD_MONTHS(hiredate, 6) REVIEW, NEXT_DAY(hiredate, ‘FRIDAY’), LAST_DAY(hiredate) FROM emp WHERE MONTHS_BETWEEN(SYSDATE, hiredate) < 200; ii. Comparing the hire dates for all employees who started in 1982, display the employee number, hiredate, and month started using the
Lab No 3
ROUND and TRUNC functions. SELECT empno, hiredate, ROUND(hiredate, ‘MONTH’),TRUNC (hiredate, ‘MONTH’) FROM emp WHERE hiredate like ‘%82’;
Conversion Functions
Figure 4.
SQL provides three functions to convert a value from one data type to another. TO_CHAR TO_NUMBER TO_DATE
TO_CHAR function with Dates
i. To display the employee number, the month number and year of hiring SELECT empno, TO_CHAR(hiredate, ‘MM/YY’) Month_Hired FROM emp WHERE ename = ‘BLAKE’; The second argument of TO_CHAR is called format model , is in single quotation marks and is case sensitive. ii. To display the employee name and hiredate for all employees. The hiredate appears as 17 November, 1981.
SELECT ename, TO_CHAR(hiredate, 'fmDD Month YYYY') HIREDATE FROM emp; The fm element is used to remove padded blanks or suppress leading zeros. iii. To print the employee name and time of joining in format HH:MI:SS (Assuming that hiredate column were used for storing joining time) SELECT ename, TO_CHAR(hiredate, ‘HH:MI:SS’) HIREDATE FROM emp;
TO_CHAR function with Numbers
It is used to display a number value as a character string. This technique is especially useful for concatenating a numeric value to a character string. i. To display the salary of employee SCOTT with $ sign preceded SELECT TO_CHAR(sal, ‘$99,999’) SALARY FROM emp WHERE ename = ‘SCOTT’; The oracle server displays a string of pound signs (#) in place of a whole number whose digits exceed the number of digits provided in the format model. The oracle server rounds the stored decimal value to the number of decimal places provided in the format model.
Database Management Systems Lab Session 04 NED University of Engineering & Technology – Department of Computer & Information Systems Engineering
[WHERE condition ] [GROUP BY column ] [ORDER BY column ];
Applying multiple-row functions to all rows in a table Examples i. (^) To show the average salary, minimum salary, maximum salary and count of employees in the organization SELECT AVG (SAL), MIN(SAL), MAX(SAL), COUNT() FROM EMP; ii. To show the minimum and maximum hiredate for employees SELECT MIN (hiredate), MAX(hiredate) FROM emp; iii. To return the number of rows in a table SELECT COUNT() FROM emp WHERE deptno = 30; iv. To return the number of nonnull rows in a table SELECT COUNT(comm) FROM emp WHERE deptno = 30; v. The group function like AVG do not include null rows. The NVL function forces group functions to include null values. SELECT AVG(NVL(comm, 0)) FROM emp;
Applying Multiple-row functions to groups of rows in a table Examples i. To show the department-wise average salary, SELECT deptno, AVG(sal) AVERAGE_SALARY FROM emp GROUP BY deptno; Note that all columns in the SELECT list that are not in group functions must be in the GROUP BY clause. ii. To show the job-wise total salary for each department SELECT deptno, job, sum(sal) FROM emp GROUP BY deptno, job;
Excluding groups result In the same way that we use the WHERE clause to restrict the rows that we select, the HAVING clause is used to restrict groups. First the group function is applied and the groups matching the HAVING clause are displayed. The syntax of the SELECT statement showing the HAVING clause along with the GROUP BY clause is shown below:-
SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression]
Database Management Systems Lab Session 04 NED University of Engineering & Technology – Department of Computer & Information Systems Engineering
[HAVING group_condition] [ORDER BY column];
The HAVING clause can precede the GROUP BY clause but it is recommended that the GROUP BY clause come first because it is more logical. Examples
i. To show the department-wise average and maximum salary, in the descending order of average salary, for all departments having average salary higher than 4500. SELECT DEPTNO, AVG(SAL), MAX(SAL) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) > 2000 ORDER BY AVG(SAL) ii. To display the job title and total monthly salary for each job title with a total payroll exceeding 5000. SELECT JOB, SUM(SAL) PAYROLL FROM EMP WHERE JOB NOT LIKE 'SALES%' GROUP BY JOB HAVING SUM(SAL) > 5000 ORDER BY SUM(SAL);
Nesting Group Functions i. To display the maximum average salary by nesting group functions SELECT max(avg(sal)) FROM emp GROUP BY deptno;
EXERCISES
- Write down the result of the function calls in the first column to the second column.
Function Call Result SUBSTR(CONCAT(‘HIGH’, ‘SALARY’), 4, 6) CONCAT(SUBSTR(‘INFORMATION’, 3, 4), ‘TECH’)
Lab No 3
vi. To list the name, hiredate, and day of the week (labeled DAY ) on which job was started. Order the result by day of week starting with Monday.
vii. To display the job-wise count of employees in each department as follows:- DEPTNO JOB NUM_EMP
viii. (^) To display the department name, location name, number of employees and the average salary for all employees in that department. Label the columns DNAME, LOC, NUMBER OF PEOPLE and SALARY, respectively. Round the average salary to two decimal places.
ix. To display the employee name, department number and job title for all employees whose department location is Dallas.
x. To display the difference between the highest and lowest salaries (Labeled as DIFFERENCE )
Lab No 3
xi. To show the manager name, MANAGER, and the number of employees, NUM, working under him.
Database Management Systems Lab Session 04 NED University of Engineering & Technology – Department of Computer & Information Systems Engineering
