Joins

(INNER) JOIN: Returns records that have matching 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
SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
Select EmpName from Emp INNER JOIN Company on Emp.empid = Company.CompanyID
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
Select EmpName from Emp LEFT JOIN Company on Emp.empid = Company.CompanyID
SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
Select EmpName from Emp RIGHT JOIN Company on Emp.empid = Company.CompanyID
SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition;
Select EmpName from Emp FULL OUTER JOIN Company on Emp.empid = Company.CompanyID
SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition;
Select EmpName from Emp a, Emp b where Empid =1111
SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;
Select EmpName from Emp UNION ALL SELECT column_name(s) FROM Company;

WildCards

% the percentage

_ underscore wildcard

NOT Like

Escape keyword.

1.SELECT * FROM members WHERE postal_address = 'Austin , TX' OR  postal_address = Dallas , TX OR postal_address = Iola,TX OR postal_adress = Houston ,TX';

eg : SELECT * FROM  members  WHERE postal_address  like '% TX';

SELECT statements... WHERE fieldname LIKE 'xxx%';

SELECT * FROM movies WHERE title LIKE '%code%';

SELECT * FROM movies WHERE title LIKE '%code';

SELECT * FROM movies WHERE title LIKE 'code%';

SELECT * FROM movies WHERE year_released LIKE '200_';

SELECT * FROM movies WHERE year_released NOT LIKE '200_';

SELECT * FROM movies WHERE title LIKE '67#%%' ESCAPE '#';

SELECT * FROM movies WHERE title LIKE '67=%%' ESCAPE '=';

=======================================

Group by with example --- SELECT D_empID, avg(D_salary) AS salary  FROM developers  GROUP BY D_empID  ORDER BY D_empID DESC;  

Delete duplicates qry   ---  SELECT DISTINCT column1, column2,....columnN FROM table _name WHERE [conditions]

Qry for getting count of records ---- SELECT COUNT(*) FROM orders; count with column name : SELECT COUNT(coname) FROM listofitem;