vnd.openxmlformats-officedocument.presentationml.presentation&rendition=1.pptx

vnd.openxmlformats-officedocument.presentationml.presentation&rendition=1.pptx

1. Form of basic SQL query in DBMS . The basic form of an SQL query, specifically when Retrieving data, is composed of a combination of clauses. SELECT [ DISTINCT ] column1 , column2 , ... FROM tablename WHERE condition ;

2. SELECT:  Retrieves data from one or more tables. FROM:  Specifies the table from which you're retrieving the data. WHERE:  Filters the results based on a condition. GROUP BY:  Groups rows that have the same values in specified columns. HAVING:  Filters the result of a GROUP BY. ORDER BY:  Sorts the results in ascending or descending order. JOIN:  Combines rows from two or more tables based on related columns.

3. 1. Retrieve all columns from a table: Syntax SELECT * FROM tablename ; 2. Retrieve specific columns from a table: Syntax SELECT column1 , column2 FROM tablename ;

4. 3. Retrieve data with a condition: Syntax SELECT column1 , column2 FROM tablename WHERE column1 = 'value' ; 4. Sort retrieved data: Syntax SELECT column1 , column2 FROM tablename ORDER BY column1 ASC ;

5. Regular expressions in the SELECT Command Regular expressions:  is a sequence of characters that define a search pattern, mainly for use in pattern matching with strings, or string matching. Examples: Finds Names that start or ends with "a“ Finds names that start with "a" and are at least 3 characters in length. LIKE:  The LIKE operator is used in a 'WHERE' clause to search for a specified pattern in a column Wild cards: percent sign (%)  Represents zero, one, or multiple characters underscore sign(_)  Represents a single character

6. Find values that start with a specific pattern: Syntax SELECT column_name FROM table_name WHERE column_name LIKE 'pattern%’ ; For example, to find all customers whose names start with "Ma": SELECT FirstName FROM Customers WHERE FirstName LIKE 'Ma%' ;

7. Find values that end with a specific pattern SELECT column_name FROM table_name WHERE column_name LIKE '%pattern’ ; For instance, to find all products that end with " ing ": SELECT ProductName FROM Products WHERE ProductName LIKE '% ing ' ;

8. Find values that have a specific pattern anywhere : SELECT column_name FROM table_name WHERE column_name LIKE '%pattern%' ; Example, to find all books that have the word "life" anywhere in the title: SELECT BookTitle FROM Books WHERE BookTitle LIKE '%life%' ;

9. Using `_` Wildcard 1. Find values of a specific length where you only know some characters: Syntax SELECT column_name FROM table_name WHERE column_name LIKE ' p_ttern ' ; For instance, if you're looking for a five-letter word where you know the first letter is "h" and the third letter is "l", you could use: SELECT Word FROM Words WHERE Word LIKE ' h_l __' ;

10. Combining `%` and `_` You can use both wildcards in the same pattern. For example, to find any value that starts with "A", followed by two characters, and then "o": Example SELECT column_name FROM table_name WHERE column_name LIKE ' A__o %' ;

11. UNION in DBMS   The  UNION  operator in DBMS is used to combine the result sets of two or m ore  SELECT  statements. However, it will only select distinct values. SELECT City FROM Customers UNION SELECT City FROM Suppliers ; If you wanted to include duplicates, you would use  UNION ALL : SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers ;

12. INTERSECT in DBMS The  INTERSECT  operator in a DBMS is used to combine two SELECT statements and return only the records that are common to both. SELECT order_id FROM Orders INTERSECT SELECT order_id FROM Deliveries ; Here is an example of how to emulate INTERSECT using  INNER JOIN : Syntax SELECT table1.id FROM table1 INNER JOIN table2 ON table1.id = table2.id; This would also return ids that exist in both table1 and table2 .

13. Except in DBMS SELECT order_id FROM Orders EXCEPT SELECT order_id FROM Deliveries ; This would return a list of  order_ids  that appear in the Orders table but not in the Deliveries table.

14. you can simulate EXCEPT using a combination of  LEFT JOIN  or  NOT EXISTS . Here's how you might do it with  LEFT JOIN : Syntax SELECT column_name (s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name WHERE table2.column_name IS NULL;

15. For example, if you have two tables,  Orders  and  Deliveries , and you want to find all orders that have not been delivered yet (assuming order_id is a common column), you could write: Example: SELECT o . OrderID FROM Orders o LEFT JOIN Deliveries d ON o . OrderID = d . OrderID WHERE d . OrderID IS NULL