Objectives In this lesson, you will learn to: Use wildcards

1. Objectives In this lesson, you will learn to: • Use wildcards • Use the IS NULL and IS NOT NULL keywords • Use the ORDER BY clause • Use the TOP keyword • Use the DISTINCT keyword • Use aggregate functions in queries • Group result sets • Use the COMPUTE and COMPUTE BY clause

2. Retrieving Rows Based on Pattern Matching • A author Sheryl, staying at Blonde street is to be contacted. However, there are many address that contain the word “Blonde” along with other words. To ensure that the right person is contacted, details like the names of the person, the address, and the telephone numbers of the author, which have “Blonde” in their address need to be displayed.

3. Task List • Create a format for query output • Draft the query • Execute the query • Verify that the query output is as per the required results

4. Draft the query • String Operator • You can use the LIKE keyword to search for a string with the wildcard mechanism • The LIKE keyword is used to select those rows that match the specified portion of character string • Result: • The required information is available in the Authors table • Since the address must have “Blonde”, and it could be prefixed or suffixed by anything, the wild card to be used is %

5. Draft the query (Contd.) • Therefore, the query using the SELECT statement should be: select * from authors where address like '%Blonde%'

6. Execute the query • Action: • In the Query Analyzer window, type the query • Execute the query

7. Verify that the query output is as per the required results • Check whether: • The required columns are displayed • All the rows that meet the condition specified in the WHERE clause are displayed

8. Wait a while… • Write a query to display the details of all the Jobsjob description begin with “M”. • Use Jobs Table of pubs.

9. Displaying Rows With Missing Values • The list of books for whom royalty is not yet decided is required.

10. Task List • Create a format for the query output • Draft the query • Execute the query • Verify that the query output is as per the required results

11. Draft the query • The IS NULL and IS NOT NULL Keywords • NULL is an unknown value or a value for which data is not available • Syntax SELECT column_list FROM table_name WHERE column_name unknown_value_operator • Result: • The information is available in the Titles table of pubs • The condition is that the royalty should be NULL

12. Draft the query • Therefore, the query using the SELECT statement should be: SELECT * FROM titles WHERE royalty IS NULL

13. Verify that the query output is as per the required results • Check whether: • The required columns are displayed • All rows that have a NULL value in the Royalty attribute are displayed

14. Wait a while… • A list of publishers is required where state information must be available. • Use publishers table of pubs

15. Displaying Data in a Specific Order • A report of all employee is required as inputs for further reviewing. A report in the ascending order of the FirstName is to be generated.

16. Task List • Create a format for the query output • Draft the query • Execute the query • Verify that the query output is as per the required results

17. Draft the query • The ORDER BY Clause • Syntax SELECT select_list FROM table_name [ORDER BY column_name [ASC|DESC ]]

18. Draft the query (Contd.) • Result: • The information is available in the Employee table • Therefore, the query using the SELECT statement should be: SELECT * FROM employee ORDER BY fname

19. Displaying the Top Few Rows • Based on price, the top 3 Book details have to be displayed.

20. Task List • Create a format for the query output • Draft the query • Execute the query • Verify that the query output is as per the required results

21. Create a format for the query output • Result: • The output required from the query is details of the top 3 costlier Books. • The column headings required by the report are the attribute names of the table Titles.

22. Draft the query • The TOP Keyword • The TOP clause limits the number of rows returned in the result set • Syntax SELECT [TOP n [PERCENT]] column_name [,column_name…] FROM table_name WHERE search_conditions [ORDER BY [column_name[,column_name…]

23. Draft the query (Contd.) • Result: • Therefore, the query using the SELECT statement should be: • SELECT TOP 3 * FROM titles ORDER BY Price DESC

24. The Distinct Keyword • The DISTINCT keyword removes duplicate rows from the result set • Syntax SELECT [ALL|DISTINCT] column_names FROM table_name WHERE search_condition

25. Wait a while… • Write a query that displays a list of states from where publishers are staying. • Use publishers table.

26. Displaying Aggregate Functions • The total number of authors who are having contract value as 1 is required.

27. Task List • Create a format for the query output • Draft the query • Execute the query • Verify that the query output is as per the required results

28. Draft the query • Aggregate Functions • Summarize the values for a column or a group of columns within a table for which they are applied, and produce a single value

29. Result: • The information is available in the Authors table • The aggregate function to be used is COUNT • Therefore, the query using the SELECT statement should be: • select count(*) from authors where contract = 1

30. Grouping Result Sets • The following clauses are used to group result sets: • GROUP BY: Summarizes the result set into groups defined in the query using aggregate functions • GROUP BY ALL: The ALL keyword of the GROUP BY clause is used to display all groups, including those excluded from the WHERE clause • COMPUTE and COMPUTE BY: The COMPUTE clause with the SELECT statement is used to generate summary rows using the aggregate functions in the query results. The COMPUTE BY clause further summarizes the result set by columns

31. Generating a Summary Report • The information of employees is required in the following format: No. Of Employees Job ID

32. Task List • Draft the query • Execute the query • Verify that the query output is as per the required results

33. Draft the query • The GROUP BY Clause • Syntax SELECT column_list FROM table_name WHERE condition GROUP BY expression [, expression] [HAVING search_condition]

34. Draft the query (Contd.) • Result: • The information is available in the Employee table • The output needs to be grouped Job_ID wise, so the GROUP BY clause has to be used

35. Draft the query (Contd.) • Therefore, the query using the SELECT statement should be: SELECT job_id, 'Total Count'=COUNT(emp_id) FROM employee Group BY job_id

36. WHERE CLAUSE IN GROUP BY • Example SELECT job_id, 'Total Count'=COUNT(emp_id) FROM employee WHERE job_lvl IN (100,200) Group BY job_id

37. The HAVING keyword in the SELECT query can be used to select rows from the intermediate result set • SELECT job_id, 'Total Count'=COUNT(emp_id) FROM employee Group BY job_id HAVING COUNT(job_id) >= 4

38. COMPUTE and COMPUTE BY • The COMPUTE clause with the SELECT statement is used to generate summary rows using aggregate functions in the query results • The COMPUTE BY clause can be used to calculate summary values of the result set on a group of data • Syntax SELECT column_list FROM table_name ORDER BY column_name COMPUTE aggregate_function (column_name) [, aggregate_function (column_name)...] [BY column_name [, column_name]...]

39. A list of all the books from the titles table is to be displayed along with the sum of advance. • The COMPUTE clause with the SELECT statement will have to be used.

40. The query can be written as: select * from Titles compute sum(advance)

41. A list of all the books from the titles table is to be displayed along with the sum of advance for each publisher id. • The COMPUTE BY clause can be used to calculate summary values of the result set on a group of data

42. The query can be written as: SELECT * FROM Titles ORDER BY pub_id COMPUTE SUM(advance)BY pub_id

43. Summary In this lesson, you learned that: • SQL Server provides a pattern-matching method for string expressions by using the LIKE keyword with the wildcard mechanism • The LIKE keyword is used to select those rows that match the specified portion of character string • In SQL Server terms, NULL is an unknown value or a value for which data is not available • The NULL values can be retrieved from the table using the IS NULL keyword in the WHERE clause

44. Summary (Contd.) • The DISTINCT keyword in the SELECT statement is used to eliminate duplicate rows • The TOP clause limits the number of rows returned in the result set • The GROUP BY clause organizes the summarized result set into groups defined in a table with the help of the aggregate functions • The HAVING clause restricts the result set to produce the data based on a condition

45. Summary (Contd.) • The ALL keyword of the GROUP BY clause is used to display all groups, including those excluded from the WHERE clause • SQL Server provides the COMPUTE clause with the SELECT statement to produce summary rows using aggregate functions in the query results • The COMPUTE BY clause can be used to calculate summary values of the result set on a group of data