WHERE clauses and OPERATORS

 WHERE CLAUSE

CLAUSE - conditions using which we can extract particular data

SYNTAX:

SELECT col1,col2 FROM table_name WHERE conditions;

SELECT name,age FROM users WHERE age>20;

here OPERATORS are used

ARITHMETIC: +, - , * , / , %

select  name,age from users3 WHERE followers+1=101;



COMPARISON : < , > , = , <=, >=, != 

select  name,age from users3 WHERE age>=20;



LOGICAL : 

AND,OR,BETWEEN,IN, NOT, ALL, LIKE, ANY

select  name,age,followers from users3 WHERE  followers <100 AND age>20;


OR

select  name,age,followers from users3 WHERE  followers <100 OR age>20;




BETWEEN

select name,age from users3 WHERE age BETWEEN 20 AND 23




IN

select name,age from users3 WHERE age IN (21,14);


NOT

 select name,age from users3 WHERE age NOT IN (21,14);




BITWISE : & , | 

select  name,age from users3 WHERE followers | age <100;




Comments

Post a Comment

Popular posts from this blog

DATABASE RELATIONSHIPS

Image
 RELATIONSHIPS INCLUDE  ONE - TO -ONE (1x 1)   ONE ENTITY OF A TABLE IS IN RELATION WITH ONLY ONE ENTITY OF ANOTHER. ONE - TO -MANY (1x N)   ONE ENTITY IS IN RELATION WITH MANY MANY - TO - MANY(NxN)   MANY ENTITIES ARE IN RELATION WITH MANY OTHER. ONE- TO-MANY IS MORE HARDER TO BE REPRESENTED THAN ONE - TO - ONE AND MANY - TO - MANY ONE- TO -MANY IT IS CATEGORIZED INTO 3 BASED ON MANY MANY CAN BE IN 3 TYPES 1. ONE -TO-FEW:  SMALL NUMBER (2,5 ,100,500) STORE THE CHILD DOCUMENT INSIDE PARENT TAKE ZOMATO OR AMAZON FOR EXAMPLE, A SINGLE USER CAN HAVE MULTIPLE ADDRESSES. HERE WE DONOT CREATE TWO MODELS FOR USER AND ADDRESS, AS ADDRESS IS ASSOCIATED WITH USER DIRECTILY, WE STORE THE CHILD DOCUMENT INSIDE PARENT. const mongoose = require ( "mongoose" ); const { Schema } = mongoose ; main ()   . then (() => {     console . log ( "connection successful" );   })   . catch (( err ) => {     console . log ( err );...
Read more

ROUTING (GET /) home route

Image
  app . get ( "/" , ( req , res ) => {   let q = 'SELECT COUNT(*) FROM user' ;   try {   connection . query ( q , ( err , result ) => {     if ( err ) throw err ;     let count = result [ 0 ][ 'COUNT(*)' ];     console . log ( count );     res . render ( "home.ejs" ,{ count });   }); } catch ( err ) {   console . log ( err );   res . send ( "error occured" ); } }); app . listen ( port , () => {   console . log ( `listening to port ${ port } ` ); }); HOME.EJS <! DOCTYPE html > < html lang = "en" > < head >     < meta charset = "UTF-8" >     < meta name = "viewport" content = "width=device-width, initial-scale=1.0" >     < title > home </ title > </ head > < body >     < h2 > Total count of users: <%= count %> </ h2 > </ body > </ html >
Read more

Query Strings

Image
 req.query() - used to get the query string(searched) app . get ( "/search" ,( req , res ) => {   let { q } = req . query ;   // console.log(q);   if ( ! q ){     res . send ( "<h1>nothing searched</h1>" );   }   res . send ( `<h1>search results for ${ q } </h1>` ); });
Read more