For getting data in descending order of deptno and ascending order of sal we

For getting data in descending order of deptno and

This preview shows page 5 - 8 out of 24 pages.

For getting data in descending order of deptno and ascending order of sal, we will use the following syntax: SELECT * FROM emp ORDER BY deptno DESC, sal ASC; Preventing Duplicate Rows To see only the unique rows, we have to use the DISTINCT clause along with the column name(s) for which the uniqueness is required. The example is given below SELECT DISTINCT deptno FROM emp; It will return all the department numbers without any repeat ion, though all the departments may have multiple occurrences in the table. The concatenation Operator (||) It allows columns to be linked to others columns, arithmetic expressions or constant values to create a character expression. Columns on either side of the operator are combined to make one single column. e.g.; SELECT ename||’ IS ‘||designation||’ . ‘FROM emp; ENAME | | ‘IS’ | | DESIG ------------------------------------------------------------------------ Axay IS CEO
Image of page 5
78 Data Base Management systems Notes Ashish IS Manager Sparsh IS Manager Meenal IS Sales Person SQL OperatorsBETWEEN …AND…:To retrieve the data within a specified range of values, the BETWEEN operator is used. For exampleSELECT * FROM emp WHERE sal BETWEEN 5000 AND 10000;The above SELECT statement retrieves the information of those employees who are getting salary in between 5000 and 10000 (inclusive of both the values). IN:If we have to compare a list of values against a column, then we have to use IN operator. For exampleSELECT * FROM emp WHERE deptno IN (10,20);This syntax will give the information of all those employees who are either in department number 10 or 20. Like: This operator is applicable in case of character columns. It allows for a comparison of one string value with another string value which is not identical. We use two wildcards (% and _ ) for this purpose. The different forms of like are given as :SELECT * FROM emp WHERE ename LIKE ‘A%’;It gives the details of those employees whose name starts from character A.SELECT * FROM emp WHERE ename LIKE ‘%h’;It returns the rows in which the ename column ends with character ‘N’SELECT * FROM emp WHERE ename LIKE ‘%r%’;It will display the information about those employees who include ‘r’ in their names. SELECT * FROM emp WHERE ename LIKE ‘Ta__’;It will display the information of those employees where length of name is just four characters and first two letters are ‘T’and ‘a’respectively.
Image of page 6
Image of page 7
Image of page 8

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture