SQL Keywords and Functions.pptx

Database Slides for DIT students

1. SQL keywords and Functions By: Ms. Rubab [email protected] IBA ITC Sobhodero Managed By Khairpur Campus, IBA Sukkur University By: Ms. Rubab For DIT

2. The SQL ORDER BY Keyword The  ORDER BY  keyword is used to sort the result-set in ascending or descending order. The  ORDER BY  keyword sorts the records in ascending order by default. To sort the records in descending order, use the  DESC  keyword. ORDER BY Syntax SELECT   column1 ,  column2, ... FROM   table_name ORDER   BY   column1, column2, ...  ASC | DESC ; By: Ms. Rubab For DIT

3. Example SELECT  *  FROM  students ORDER   BY  address  ASC ; The above query selects all the students from student table and sorts them in ascending order by their address SELECT  *  FROM  students ORDER   BY  address  DESC ; The above query selects all the students from student table and sorts them in descending order by their address SELECT  *  FROM  Customers ORDER   BY  address  ASC , Name  DESC ; The above query selects all the students from student table and sorts them in ascending order by their address and descending order by their names By: Ms. Rubab For DIT

4. SQL NULL Values A field with a NULL value is a field with no value. If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value. Note:  A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation! By: Ms. Rubab For DIT

5. How to test Null values It is not possible to test for NULL values with comparison operators, such as =, <, or <>. We will have to use the  IS NULL  and  IS NOT NULL  operators instead. Syntax: SELECT   column_name (s) FROM   table_name WHERE   column_name   IS   NULL ; The   IS NULL   operator is used to test for empty values (NULL values). By: Ms. Rubab For DIT

6. IS NOT Null Syntax SELECT   column_names FROM   table_name WHERE   column_name   IS   NOT   NULL ; The   IS NOT NULL   operator is used to test for non-empty values (NOT NULL values). Example: SELECT  *  FROM  students Where address  IS NOT NULL ; By: Ms. Rubab For DIT

7. SQL AS Keyword The  AS  command is used to rename a column or table with an alias. NOTE : An alias only exists for the duration of the query. Syntax: SELECT   column_name AS Alias, column2 AS Alias FROM   table_name WHERE condition By: Ms. Rubab For DIT

8. Example SELECT  fees  AS  STUDENT_FEES  FROM  students By: Ms. Rubab For DIT

9. The SQL SELECT TOP Clause The  SELECT TOP  clause is used to specify the number of records to return. The  SELECT TOP  clause is useful on large tables with thousands of records. Returning a large number of records can impact performance. Syntax: SELECT   column_name (s) FROM   table_name WHERE   condition LIMIT   number ; By: Ms. Rubab For DIT

10. Example SELECT  ID,NAME  FROM  students  WHERE  ID < 26   LIMIT   10 The above query selects top 10 records from students table where id is less than 26 SELECT  *  FROM  students. LIMIT   3 ; By: Ms. Rubab For DIT

11. SQL MIN() and MAX() Functions The  MIN()  function returns the smallest value of the selected column. The  MAX()  function returns the largest value of the selected column. Min Syntax: SELECT  MIN( column_name ) FROM   table_name WHERE   condition ; Max Syntax: SELECT  Max( column_name ) FROM   table_name WHERE   condition ; By: Ms. Rubab For DIT

12. Examples SELECT  MIN( fees ) FROM   students; SELECT  MAX( fees ) FROM   students; By: Ms. Rubab For DIT

13. SQL COUNT(), AVG() and SUM() Functions More SQL Functions By: Ms. Rubab For DIT

14. SQL COUNT () Function The  COUNT()  function returns the number of rows that matches a specified criterion Syntax: SELECT   COUNT ( column_name ) FROM   table_name WHERE   condition ; Example: SELECT   COUNT (ID) FROM   stuents ; By: Ms. Rubab For DIT

15. SQL AVG() Function The  AVG()  function returns the average value of a numeric column. Syntax: SELECT  AVG( column_name ) FROM   table_name WHERE   condition ; Example: SELECT  AVG(fees) FROM  students; By: Ms. Rubab For DIT

16. SQL SUM() Function The  SUM()  function returns the total sum of a numeric column.  SELECT  SUM( column_name ) FROM   table_name WHERE   condition ; Example: SELECT  SUM(fees) FROM  students; By: Ms. Rubab For DIT