1 / 51

sql BEGINEER Level-4 Sub-level-3 by: ars

sql BEGINEER Level-4 Sub-level-3 by: ars. Agenda :: Where on one column string with the use of like operator. SqlServerSchool.com. S S S. Data Introduction. Seven Different People in the House. Hi I am Alok. My Id is 1 and date of birth is 15/Aug/1980.

riona
Download Presentation

sql BEGINEER Level-4 Sub-level-3 by: ars

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. sqlBEGINEER Level-4Sub-level-3by: ars Agenda :: Where on one column string with the use of like operator Slides by SqlServerSchool.com

  2. SqlServerSchool.com SSS Slides by SqlServerSchool.com

  3. Data Introduction Seven Different People in the House. Hi I am Alok. My Id is 1 and date of birth is 15/Aug/1980 Hi I am Aman and my Id is 4 and date of birth is 16/Dec/1980 Hi I am Shree and my Id is 7 and date of birth is 20/Jan/1989 Hi I am Manish. My Id is 2 and date of birth is 20/Jan/1985 Hi I am Chandra. My Id is 5 and date of birth is 17/Jan/1980 Hi I am Ambrish. My Id is 3 and date of birth is 25/Nov/1975 Hi I am Shivam. My Id is 6 and date of birth is 18/Jan/1999 Skip Current Page >> Slides by SqlServerSchool.com

  4. Keyword And Term Introduction • Input Data: Data Stored in Customer Table • Output Data:Data to show when requirement gets • filled (or data to show as Output). Or Expected OutputOr Output Imagination • Reqstands for “Requirement” • T-SQL stands for “Sql Server Executable code” Slides by SqlServerSchool.com

  5. TIME SAVER TIPSFollowing are Concept slides to examine whether you require this course study or not… Slides by SqlServerSchool.com

  6. Concept 1 : Where condition on STRING column with equal to operator Input Data Output Data SELECT all the information FROM CUSTOMER Whose Name is Alok SELECT * FROM CUSTOMER WHERE Name=‘Alok’ Slides by SqlServerSchool.com

  7. Concept 2 : Where condition on STRING column with NOT equal to operator Input Data Output Data SELECT all the information FROM CUSTOMER Whose Name is Not Alok SELECT * FROM CUSTOMER WHERE Name!=‘Alok’ Slides by SqlServerSchool.com

  8. Concept 3 : Pick records start with one/ more character Input Data Output Data REQ: SELECT all the information FROM CUSTOMER Whose Name Start with ‘AM’ T-SQL: SELECT * FROM CUSTOMER WHERE Name LIKE ‘AM%’ Slides by SqlServerSchool.com

  9. Concept 4 : Pick records end with one/ more character Input Data Output Data REQ: SELECT all the information FROM CUSTOMER Whose Name Ends with ‘SH’ T-SQL: SELECT * FROM CUSTOMER WHERE Name LIKE ‘%SH’ Slides by SqlServerSchool.com

  10. Concept 5 : Pick records which consist of one/ more character Input Data Output Data REQ: SELECT all the information FROM CUSTOMER Whose Name consist of with ‘A’ i.e. “A” can come in between of name or at the start of name or at then end of name T-SQL: SELECT * FROM CUSTOMER WHERE Name LIKE ‘%A%’ Slides by SqlServerSchool.com

  11. Concept 6 : Pick records which does not consist of one/ more character Input Data Output Data REQ: SELECT all the information FROM CUSTOMER Whose Name does not consist of with ‘SH’ i.e. “SH” can come in between of name or at the start of name or at then end of name T-SQL: SELECT * FROM CUSTOMER WHERE Name NOT LIKE ‘%SH%’ Slides by SqlServerSchool.com

  12. Concept 7 : Pick records which start with one character with ascending and descending order Input Data Output Data REQ: SELECT all the information FROM CUSTOMER whose Name start with A In the order of ascending Name and then in descending DOB i.e. increasing order of Name and then in decreasing order of DOB T-SQL: SELECT * FROM CUSTOMER WHERE NAME like ‘A%’ ORDER BY Name asc, DOB desc Slides by SqlServerSchool.com

  13. If you are able to answer all quick check correctly then you don’t require this course study…If you proceed with course study then at end of study always go through quick check slides for confirm you understood this courseware correct ? Thanks, ARS Slides by SqlServerSchool.com

  14. HOW QUERY WORKS WHEN IT IS FETCHING DATA FROM A TABLE SSMS (Sql Server Management Studio) Ask a question to each row of the table and get the answer in YES OR NO only. The question will be where condition of the Requirement or T-Sql statement. A row can give the answer in YES or NO only. If a Row gives Answer as NO, Meaning this row is not qualified . This row will not part of the Output. If a Row gives Answer as YES, Meaning this row is qualified . This row will be part of the Output. Slides by SqlServerSchool.com

  15. CONCEPT 1 Slides by SqlServerSchool.com

  16. How to filterData on STRING Column with EQUAL to operator- Effort-1 Input Data REQ: SELECT all the information FROM CUSTOMER Whose Name is Alok Output Data T-SQL: SELECT * FROM CUSTOMER WHERE Name=‘Alok’ You learned, Filtering Table Data on STRING Column with EQUAL to operator. Slides by SqlServerSchool.com

  17. Query Execution Result in SQL Server Slides by SqlServerSchool.com

  18. CONCEPT 2 Slides by SqlServerSchool.com

  19. How to filterData on STRING Column with not EQUAL to operator- Effort-1 Input Data REQ: SELECT all the information FROM CUSTOMER Whose Name is Not Alok Output Data T-SQL: SELECT * FROM CUSTOMER WHERE Name!=‘Alok’ You learned, Filtering Table Data on STRING Column with NOT EQUAL to operator. Slides by SqlServerSchool.com

  20. Query Execution Result in SQL Server Slides by SqlServerSchool.com

  21. CONCEPT 3 Slides by SqlServerSchool.com

  22. How to pick data on STRING Column with start with one or more character - Effort-1 Input Data REQ: SELECT all the information FROM CUSTOMER Whose Name Start with ‘A’ Output Data T-SQL: SELECT * FROM CUSTOMER WHERE Name LIKE ‘A%’ You learned, Filtering Table Data on STRING Column with LIKE operator. Slides by SqlServerSchool.com

  23. Query Execution Result in SQL Server Slides by SqlServerSchool.com

  24. How to pick data on STRING Column with start with one or more character - Effort-2 Input Data REQ: SELECT all the information FROM CUSTOMER Whose Name Start with ‘S’ Output Data T-SQL: SELECT * FROM CUSTOMER WHERE Name LIKE ‘S%’ You learned, Filtering Table Data on STRING Column with LIKE operator. Slides by SqlServerSchool.com

  25. Query Execution Result in SQL Server Slides by SqlServerSchool.com

  26. How to pick data on STRING Column with start with one or more character - Effort-3 Input Data REQ: SELECT all the information FROM CUSTOMER Whose Name Start with ‘AM’ Output Data T-SQL: SELECT * FROM CUSTOMER WHERE Name LIKE ‘AM%’ You learned, Filtering Table Data on STRING Column with LIKE operator. Slides by SqlServerSchool.com

  27. Query Execution Result in SQL Server Slides by SqlServerSchool.com

  28. How to pick data on STRING Column with start with one or more character - Effort-4 Input Data REQ: SELECT all the information FROM CUSTOMER Whose Name Start with ‘AMA’ Output Data T-SQL: SELECT * FROM CUSTOMER WHERE Name LIKE ‘AMA%’ You learned, Filtering Table Data on STRING Column with LIKE operator. Slides by SqlServerSchool.com

  29. Query Execution Result in SQL Server Slides by SqlServerSchool.com

  30. How to pick data on STRING Column with start with one or more character - Effort-5 Input Data REQ: SELECT all the information FROM CUSTOMER Whose Name Start with ‘SH’ Output Data T-SQL: SELECT * FROM CUSTOMER WHERE Name LIKE ‘SH%’ You learned, Filtering Table Data on STRING Column with LIKE operator. Slides by SqlServerSchool.com

  31. Query Execution Result in SQL Server Slides by SqlServerSchool.com

  32. What is “LIKE” and what is “%” • LIKE operator is used when user don’t know complete information but he know part information. • As Name start with “AL” then we have to use ““WHERE Name LIKE ‘AL%’””, % sign will search for Zero to any of length string. • Records eligible for out put Example: • AL • ALA • ALOK • ALAM • ALOKA Slides by SqlServerSchool.com

  33. CONCEPT 4 Slides by SqlServerSchool.com

  34. How to pick data on STRING Column with ends with one or more character - Effort-1 Input Data REQ: SELECT all the information FROM CUSTOMER Whose Name Ends with ‘H’ Output Data T-SQL: SELECT * FROM CUSTOMER WHERE Name LIKE ‘%H’ You learned, Filtering Table Data on STRING Column with LIKE operator. Slides by SqlServerSchool.com

  35. Query Execution Result in SQL Server Slides by SqlServerSchool.com

  36. How to pick data on STRING Column with ends with one or more character - Effort-2 Input Data REQ: SELECT all the information FROM CUSTOMER Whose Name Ends with ‘SH’ Output Data T-SQL: SELECT * FROM CUSTOMER WHERE Name LIKE ‘%SH’ You learned, Filtering Table Data on STRING Column with LIKE operator. Slides by SqlServerSchool.com

  37. Query Execution Result in SQL Server Slides by SqlServerSchool.com

  38. What when user know ends with only • As Name ends with “SH” then we have to use ““WHERE Name LIKE ‘%SH’””, % sign will search for Zero to any of length string. • Records eligible for out put Example: • SH • MANISH • LOKESH • RAMESH • SUDESH • ASH Slides by SqlServerSchool.com

  39. CONCEPT 5 Slides by SqlServerSchool.com

  40. How to pick data on STRING Column with consist of one or more character - Effort-1 Input Data REQ: SELECT all the information FROM CUSTOMER Whose Name consist of with ‘SH’ i.e. “SH” can come in between of name or at the start of name or at then end of name Output Data T-SQL: SELECT * FROM CUSTOMER WHERE Name LIKE ‘%SH%’ You learned, Filtering Table Data on STRING Column with LIKE operator. Slides by SqlServerSchool.com

  41. Query Execution Result in SQL Server Slides by SqlServerSchool.com

  42. How to pick data on STRING Column with consist of one or more character - Effort-2 Input Data REQ: SELECT all the information FROM CUSTOMER Whose Name consist of with ‘A’ i.e. “A” can come in between of name or at the start of name or at then end of name Output Data T-SQL: SELECT * FROM CUSTOMER WHERE Name LIKE ‘%A%’ You learned, Filtering Table Data on STRING Column with LIKE operator. Slides by SqlServerSchool.com

  43. Query Execution Result in SQL Server Slides by SqlServerSchool.com

  44. CONCEPT 6 Slides by SqlServerSchool.com

  45. How to pick data on STRING Column with NOT consist of one or more character - Effort-1 Input Data REQ: SELECT all the information FROM CUSTOMER Whose Name does not consist of with ‘SH’ i.e. “SH” can come in between of name or at the start of name or at then end of name Output Data T-SQL: SELECT * FROM CUSTOMER WHERE Name NOT LIKE ‘%SH%’ You learned, Filtering Table Data on STRING Column with NOT LIKE operator. Slides by SqlServerSchool.com

  46. Query Execution Result in SQL Server Slides by SqlServerSchool.com

  47. What when user know it can be at start or end or between • As Name contain with “SH” then we have to use ““WHERE Name LIKE ‘%SH%’””, % sign will search for Zero to any of length string at both the end. • Records eligible for out put Example: • SH • MANISH • LOKESH • RAMESH • SUDESH • ASH • SHEKHAR • SHASHANK • SUSHRUT • SUSHMITA Slides by SqlServerSchool.com

  48. CONCEPT 7 Slides by SqlServerSchool.com

  49. Let’s Learn New concept Input Data REQ: SELECT all the information FROM CUSTOMER whose Name start with A In the order of ascending Name and then in descending DOB i.e. increasing order of Name and then in decreasing order of DOB Output Data T-SQL: SELECT * FROM CUSTOMER WHERE NAME like ‘A%’ ORDER BY Name asc, DOB desc You learned, Ordering Table Data. Slides by SqlServerSchool.com

  50. Query Execution Result in SQL Server Slides by SqlServerSchool.com

More Related