SQL Query

SQL JOIN

A JOIN is used to combine rows from two or more tables, based on a related column between them.

• (INNER) JOIN: Returns records that have matching(intersection) values in both tables.
• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
• SELF JOIN:A self JOIN is a regular join, but the table is joined with itself.

        eg: SELECT * FROM customer c1 JOIN customer c2 ON c1.Id=c2.Id;'

• Equi Joins: EQUI JOIN performs a JOIN against equality or matching column(s) values of the associated tables. An equal sign (=) is used as comparison operator in the where clause to refer equality.

  You may also perform EQUI JOIN by using JOIN keyword followed by ON keyword and then specifying names of the columns along with their associated tables to check equality.

        eg: select * from customer c1 join order o1 on c1.id=o1.id;

1 SELECT SQL Query:  SELECT s_id, name, age FROM student;

2.  SELECT * FROM student WHERE name = 'Abhi';

3. write a query to display all students name whose name is of 6 characters length.

Select * from student where length(firstName)=6;

4. write a query from copy one table data to another table

Insert into employee(id,name,depart) select id,name,depart from student;

5. Crud operations:

 a. SELECT * FROM EMPLOYEE;

 b. INSERT INTO EMPLOYEE(id, name, city) VALUES(1,'PANKAJ','KALAYAN';

 c. DELETE FROM employee WHERE id=1;

 d. UPDATE employee SET name='PANKAJ' WHERE id=1;

 e. CREATE TABLE employee(id INT NOT NULL PRIMARY KEY, name CHAR(30), city CHAR(30));

f. SEVENTH SALARY:

SELECT * FROM employee ORDER BY salary DESC LIMIT 6,1;

g. IN CLAUSE: SELECT * FROM employee WHERE salary in(10000,20000);

h. Whole List : SELECT * FROM marksheet WHERE  physics>33 and maths>30 and chemistry>33 ORDER BY (physics+chemistry+maths) DESC LIMIT 1,10;

i. NOT MATCH: SELECT * FROM employee e WHERE NOT EXISTS(SELECT * FROM order 0 where e.id=o.id);

Group By:- Group by clause is used to group the results of a SELECT query based on one or more columns. It is also used with SQL functions to group the result from one or more tables.

NOTE:- You must remember that Group By clause will always come at the end of the SQL query, just like the Order by clause.