Understanding WHERE Clause in DBMS
The WHERE clause in a database management system (DBMS) is used to fetch filtered data based on specific criteria or patterns. Operators such as >, >=,
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
Where WHERE keyword is used for fetching filtered data in a result set. It is used to fetch data according to a particular criteria. WHERE keyword can also be used to filter data by matching patterns.
List of operators that can be used with where clause: operator description > Greater Than >= Greater than or Equal to < Less Than <= Less than or Equal to = Equal to <> Not Equal to BETWEEN In an inclusive Range LIKE Search for a pattern IN To specify multiple possible values for a column
Quaries To fetch record of students with age equal to 20 SELECT * FROM Student WHERE Age=20; Output ROLL_NO NAME ADDRESS PHONE Age 3 SUJIT ROHTAK XXXXXXXXXX 20 3 SUJIT ROHTAK XXXXXXXXXX 20
To fetch Name and Address of students with ROLL_NO greater than 3 SELECT ROLL_NO,NAME,ADDRESS FROM Student WHERE ROLL_NO > 3; Output ROLL_NO NAME ADDRESS 4 SURESH Delhi
BETWEEN operator It is used to fetch filtered data in a given range inclusive of two values. Basic Syntax: SELECT column1,column2 FROM table_name WHERE column_name BETWEEN value1 AND value2; Queries To fetch records of students where ROLL_NO is between 1 and 3 (inclusive) SELECT * FROM Student WHERE ROLL_NO BETWEEN 1 AND 3;
Output ROLL_NO NAME ADDRESS PHONE Age 1 Ram Delhi XXXXXXXXXX 18 2 RAMESH GURGAON XXXXXXXXXX 18 3 SUJIT ROHTAK XXXXXXXXXX 20 3 SUJIT ROHTAK XXXXXXXXXX 20 2 RAMESH GURGAON XXXXXXXXXX 18
Cont.. To fetch NAME,ADDRESS of students where Age is between 20 and 30 (inclusive) SELECT NAME,ADDRESS FROM Student WHERE Age BETWEEN 20 AND 30; Output NAME ADDRESS SUJIT Rohtak SUJIT Rohtak
LIKE operator It is used to fetch filtered data by searching for a particular pattern in Syntax: SELECT column1,column2 table_name WHERE column_name LIKE pattern; where clause. Basic FROM pattern: exact value extracted from the pattern to get related data in result set. Note: The character(s) in pattern are case sensitive.
Queries To fetch records of students where NAME starts with letter S. SELECT * FROM Student WHERE NAME LIKE 'S%'; The %'(wildcard) signifies the later characters here which can be of any length and value.More about wildcards will be discussed in the later set. Output: ROLL_NO NAME ADDRESS PHONE Age 3 SUJIT ROHTAK XXXXXXXXXX 20 4 SURESH Delhi XXXXXXXXXX 18 3 SUJIT ROHTAK XXXXXXXXXX 20
To fetch records of students where NAME contains the pattern AM . SELECT * FROM Student WHERE NAME LIKE '%AM%'; ROLL_NO NAME ADDRESS PHONE Age 1 Ram Delhi XXXXXXXXXX 18 2 RAMESH GURGAON XXXXXXXXXX 18 2 RAMESH GURGAON XXXXXXXXXX 18
IN operator It is used to fetch filtered data same as fetched by = operator just the difference is that here we can specify multiple values for which we can get the result set. Basic Syntax: SELECT column1,column2 FROM table_name WHERE column_name IN (value1,value2,..); value1,value2,..: exact value matching the values given and get related data in result set.
Queries To fetch NAME and ADDRESS of students where Age is 18 or 20. SELECT NAME,ADDRESS FROM Student WHERE Age IN (18,20); NAME ADDRESS Ram Delhi RAMESH GURGAON SUJIT ROHTAK SURESH Delhi SUJIT ROHTAK RAMESH GURGAON
2 To fetch records of students where ROLL_NO is 1 or 4. SELECT * FROM Student WHERE ROLL_NO IN (1,4); ROLL_NO NAME ADDRESS PHONE Age 1 Ram Delhi XXXXXXXXXX 18 4 SURESH Delhi XXXXXXXXXX 18
INTERSECT As the name suggests, the intersect clause is used to provide the result of the intersection of two select statements. This implies the result contains all the rows which are common to both the SELECT statements. Syntax- SELECT column-1, column-2 FROM table 1 WHERE .. INTERSECT SELECT column-1, column-2 FROM table 2 WHERE ..
Example Table 1 containing Employee Details Table 2 containing details of employees who are provided bonus
GROUP BY SQL GROUP BY statement is used to arrange identical data into groups. The GROUP BY statement is used with the SQL SELECT statement. The GROUP BY statement follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause. The GROUP BY statement is used with aggregation function.
Next 1.SELECT column 2.FROM table_name 3.WHERE conditions 4.GROUP BY column 5.ORDER BY column
Product PRODUCT COMPANY QTY RATE COST Item1 Com1 2 10 20 Item2 Com2 3 25 75 Item3 Com1 2 30 60 Item4 Com3 5 10 50 Item5 Com2 2 20 40 Item6 Cpm1 3 25 75 Item7 Com1 5 30 150 Item8 Com1 3 10 30 Item9 Com2 2 25 50 Item10 Com3 4 30 120
Com1 5 Com2 3 Com3 2 Example 1.SELECT COMPANY, COUNT(*) 2.FROM PRODUCT_MAST 3.GROUP BY COMPANY; Output Com1 5 Com2 3 Com3 2
HAVING HAVING clause is used to specify a search condition for a group or an aggregate. Having is used in a GROUP BY clause. If you are not using GROUP BY clause then you can use HAVING function like a WHERE clause.
Syntax 1.SELECT column1, column2 2.FROM table_name 3.WHERE conditions 4.GROUP BY column1, column2 5.HAVING conditions 6.ORDER BY column1, column2;
Example 1.SELECT COMPANY, COUNT(*) 2.FROM PRODUCT_MAST 3.GROUP BY COMPANY 4.HAVING COUNT(*)>2;
