Download Intro to Database - Lab Manual 2 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:
Basic data retrieval operations in SQL*Plus.
Name of Student: _____________________________________________
Roll No: ______________________________Sec. ___________
Date of Experiment: ___________________________________________
Marks Obtained/Remarks: _____________________________
Signature: _____________________________
THEORY
SELECT Statement
To extract data from the database, the SQL SELECT statement is used. Capabilities of SELECT statement Following are the various operations that can be performed using SELECT:- i. Selection : The selection capability can be used to choose rows in a table depending on the criteria to selectively restrict the rows.
Compiled by: Miss shabina mushtaque
Examples i. Selecting all employees whose salary is between 3500 and 5000 and who were hired after 31st^ July, 1981. SELECT * FROM EMP WHERE (SAL BETWEEN 3500 AND 5000) AND HIREDATE > TO_DATE('31-JUL-1981', 'DD-MON-YYYY'); ii. Selecting all employees whose job is either clerk or analyst and were hired between 23rd^ July, 1981 and 14th^ May, 1982.
SELECT * FROM EMP WHERE (JOB = 'CLERK' OR JOB = 'ANALYST') AND HIREDATE BETWEEN TO_DATE('23-JUL-1981', 'DD-MON-YYYY') AND TO_DATE('14-MAY-1982', 'DD-MON-YYYY');
Figure 2.1: Data in a single table can be useful for several employees
ii. Projection : It refers to choosing the columns in a table that are to be returned by a query. We can choose as few or as many columns of the table as we require. Examples i. Selecting employee number, name and their job SELECT EMPNO, ENAME, JOB FROM EMP; ii. Selecting employee number, name and their salary who do not earn commission SELECT EMPNO, ENAME, SAL FROM EMP WHERE COMM IS NULL; iii. Join : To bring together data that is stored in different tables by creating a link through a column that both the tables share. Example To retrieve the employee name, their job and department name, we need to extract data from two tables, EMP and DEPT. This type of join is called equijoin -that is, values in the DEPTNO column on both tables must be equal. Equijoin is also called simple join or inner join. The output is shown in figure 2.2. SELECT E.ENAME, E.JOB, D.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;
Compiled by: Miss shabina mushtaque
FROM EMP
WHERE ENAME LIKE ‘S%’;
Note : Above query performs wildcard searches using LIKE operator. Here % symbol represents any sequence of zero or more characters. d. To display the names of all employees with second character of name as A, SELECT ENAME FROM EMP WHERE ENAME LIKE ‘_A%’; Note : Here _ character represents any single character
Logical Operators A logical operator combines the result of two component conditions to produce a single result based on them or to invert the result of a single condition. Three logical operators are available in SQL as shown below:-
Operator Meaning AND Returns TRUE if both component conditions are TRUE OR Returns TRUE if either component condition is TRUE NOT Returns TRUE if the following condition is FALSE Table 2. Examples ■ To display record of all clerks who earn more than 1100 SELECT empno, ename, job, sal FROM emp WHERE sal >= 1100 AND job = ‘CLERK’; ■ To display record of all employees who are either clerks or earn more than 1100. SELECT empno, ename, job, sal FROM emp WHERE sal >= 1100 OR job = ‘CLERK’; ■ (^) To display name and job title of all the employees whose are not CLERK, MANAGER, or ANALYST. SELECT ename, job FROM emp WHERE job NOT IN (‘CLERK’, ‘MANAGER’, ‘ANALYST’); Rules of Precedence
Order Evaluated
Operator
1 All comparison operators 2 NOT 3 AND 4 OR Table 2. For example, consider the following statement:- SELECT ename, job, sal FROM emp WHERE job = ‘SALESMAN’ OR job = ‘PRESIDENT’ AND sal > 1500; In the above example, there are two conditions: ■ The first condition is that job is SALESMAN. Compiled by: Miss shabina mushtaque
■ The second condition is that job is CLERK and salary is greater than 1000. Therefore the SELECT statement reads as follows:- Select the row if an employee is a SALESMAN or an employee is a CLERK and earns more than 1000. In order to force the OR operator to be evaluated before AND, use parentheses as follows:-. SELECT ename, job, sal FROM emp WHERE (job = ‘SALESMAN’ OR job = ‘PRESIDENT’) AND sal > 1500;
Ordering Data
The order of rows returned in a query result is undefined. The ORDER BY clause can be used to sort the rows. This clause comes last in the SELECT statement. ASC at the end of the ORDER BY clause specifies ascending order where as DESC specifies descending order. ASC is the default order. Examples
i. (^) To select data in the increasing order of hiredate, SELECT ENAME, JOB, DEPTNO, HIREDATE FROM EMP ORDER BY HIREDATE;
ii. To select data in the decreasing order of hiredate, SELECT ENAME, JOB, DEPTNO, HIREDATE FROM EMP ORDER BY HIREDATE DESC;
iii. To sort by column alias, SELECT EMPNO, ENAME, SAL*12 ANNSAL FROM EMP ORDER BY ANNSAL.
iv. To sort by multiple columns, SELECT ENAME, DEPTNO, SAL FROM EMP ORDER BY DEPTNO, SAL DESC; Note : The DESC applies only to SAL column. The DEPTNO appears in ascending order.
v. To select list of names and jobs of all employees hired in 1987 in the alphabetical order of name SELECT UPPER(ENAME) “EMP NAME”, JOB FROM EMP WHERE TO_CHAR(HIREDATE, ‘YYYY’) = 1987 ORDER BY ENAME;
vi. To print employee number, name, job, annual salary of all managers and clerks whose monthly salary is between 3000 and 5500 in descending order of annual salary. SELECT EMPNO, ENAME, JOB, 12*SAL + NVL(COMM, 0) ANNUAL_SALARY FROM EMP WHERE JOB = ‘MANAGER’ OR JOB = ‘CLERK’ AND SAL BETWEEN 3000 AND 5500 ORDER BY ANNUAL_SALARY DESC;
EXERCISES
- Define the different capabilities of SELECT statement? Give an example of each.
2.vi. Display the name and salary for all employees whose salary is not in range of $1500 and $2850.
2.vii. Display the name, salary and commission for all employees whose commission amount in greater than their salary increased by 10%.
