Hypertechblog

What is sub query Sub query is a query with in other query is known is Sub query Use if you want to get data from table1 by using value form table2 we will go for subquery not for joins. syntax. select * from table_name where....(select * from table_name where...(select * from table_name)...)... // Inner most query execute first Type of Sub Query Single row sub query - the sub query which is returning only one output value. example- get department name of employee 708. select dname from department where deptno = (select deptno from employee where empno=708) example-  get department detailsof employee 708. select * from department where deptno = (select deptno from employee where empno=708) Multi row sub query - the sub query which is returning more than one output value. example-  get department detailsof employee 708.709 select dname from department where dept_no IN (select deptno from employee where empno IN( 708, 709)); If we want

What is Data Integrity Constraints Data Integrity means apply some business rules on data fields . for Example-  Employee id must be unique and not null. Employee salary should not be zero etc. Employee Table e_id             e_name             e_sal        e_designation                        100             Rahul                25000       software developer          // (valid record) 0                 duke                30000         manager            // (invalid because E_id must be greater than 0) 101             Shweta            0                Designer           // (invalid because E_sal must be greater than 0)        So this are basic standard that we must have to Follow. so we have constraints for avoiding invalid insertion of invalid record. Types of Data Integrity Constraints- Keys Constraints Unique not null primary key Domain constraints Check Referential Integrity Constraints Foreign Key Unique Key constraints - its

What are Joins Joins are used when we have to fetch data from multiple table with multiple conditions on column. we can also do this by Set Operators but there are a limitation set operator always work on single column and single column data. Example- we want to display name of employee and department of employee. so we have to fetch data from employee table and department table . Types of JOINS- Cross joins/cartesian product. Equi joins/Inner joins. Self joins outer joins. What is cross join or cartesian product cross join concept is similar to cartesian product in mathematics. suppose we have to tables with this data. table_a = {x, y, z}; table_b = {10, 20}; if we apply cross join here then we will get data like this. table_a x table_b = {(x, 10), (x, 20), (y, 10), (y, 20), (z, 10), (z, 20)} What is Equi join if we want to get only valid combination of records then we use Equi joins because in cross join we are getting all combination of data. sup

What is Logical Operator AND vs OR Logical operators are used to check certain condition or multiple condition. . For Example- If we want to check two or more than two condition in a single query and all condition must be true then we use - AND OPERATOR If we want to check two or more than two condition in a single query and any one condition will be true then we use -  OR OPERATOR AND ____ if we are looking for employee whose is manager and salary is greater than 50000. means here are two condition employee job is manager and salary is greater than 50000 Select * from employee where job="manager" AND salary>=50000; if we are looking for employee whose is manager and salary is greater than 50000 and city is indore. means here are two condition  employee job is manager  and  salary is greater than 50000 and city is indore Select * from employee where job="manager" AND salary>=50000 AND city="indore"; // it wil

What is Negation Operator NOT LIKE VS NOT BETWEEN VS NOT IN VS IS NOT NULL Negation Operator NOT BETWEEN, IS NOT NULL, NOT LIKE,NOT IN these operators are just opposite of special Operator. For Example- if we want to display salary not greater than 2000 and not less than 10000 use- BETWEEN if we want to display all employee whose surname is not null use- IS NULL if we want to display all employee whose firstname is not Rahul use- LIKE if we want to display all customer who are not in MUMBAI and Channai use- IN (Specific list) NOT BETWEEN _________ if we dont want record within a range then we use Between operator example if want all employee details whose salary is not between 5000 to 10000. Select * from employee where salary NOT BETWEEN '5000' AND '10000'; NOT IN __ if we dont want to display record with specific list. Example- List of employee whose salary is not 10000, 50000, 70000. select * from employee where salary NOT IN(

What is Special Operator LIKE VS BETWEEN VS IN VS IS NULL Special Operator BETWEEN, IS NULL, LIKE,IN these operators are used when we display record with specific range, limit, or any specific record. For Example- if we want to display salary greater than 2000 and less than 10000 use- BETWEEN if we want to display all employee whose surname is null use- IS NULL if we want to display all employee whose firstname is Rahul use- LIKE if we want to display all customer who are MUMBAI and Channai use- IN (Specific list) BETWEEN _________ if we want record within a range then we use Between operator example if want all employee details whose salary between 5000 to 10000. Select * from employee where salary BETWEEN '5000' AND '10000'; IN __ if we want to display record with specific list. Example- List of employee whose salary is 10000, 50000, 70000. select * from employee where salary IN(10000, 50000, 70000); IS NULL _______ sel