Referring to Two Tables:always compare two tables which have one referencing column as common;
- SELECT Employees.Name, Orders.Product FROM Employees, Orders WHERE employees.Employee_ID=Orders.Employee_ID;--both column should match(mostly primary keys will be ref)
- SELECT Employees.Name FROM Employees, Orders WHERE Employees.Employee_ID=Orders.Employee_ID AND Orders.Product='Printer';-Comparing two tables and taking out condition from one table;
Using Joins:join used to compare & combine the results of two tables.
The INNER JOIN returns all rows from both tables where there is a match. If there are rows in Employees that do not have matches in Orders, those rows will not be listed.
- SELECT Employees.Name, Orders.Product FROM Employees INNER JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID;
The LEFT JOIN returns all the rows from the first table (Employees), even if there are no matches in the second table (Orders). If there are rows in Employees that do not have matches in Orders, those rows also will be listed.
- SELECT Employees.Name, Orders.Product FROM Employees LEFT JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID;
The RIGHT JOIN returns all the rows from the second table (Orders), even if there are no matches in the first table (Employees). If there had been any rows in Orders that did not have matches in Employees, those rows also would have been listed.
- SELECT Employees.Name, Orders.Product FROM Employees RIGHT JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID;
SQL UNION and UNION ALL
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type excluding Duplicate values
- SELECT E_Name FROM Employees_Norway UNION SELECT E_Name FROM Employees_USA;
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values including Duplicate Values.
- SELECT E_Name FROM Employees_Norway UNION ALL SELECT E_Name FROM Employees_USA;
SQL Create Database, Table, and Index
- CREATE DATABASE database_name
- CREATE TABLE table_name( column_name1 data_type,column_name2 data_type);
- CREATE TABLE Person
(
LastName varchar,
FirstName varchar,
Address varchar,
Age int
);
Create Index
Indices are created in an existing table to locate rows more quickly and efficiently.
A Unique Index
Creates a unique index on a table. A unique index means that two rows cannot have the same index value.
- CREATE UNIQUE INDEX index_name ON table_name (column_name);
- CREATE INDEX PersonIndex ON Person (LastName);
SQL Drop Index, Table and Database:
- DROP INDEX index_name ON table_name;
- DROP TABLE table_name;
Difference between TRUNCATE, DELETE and DROP commands
DELETE
The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.
TRUNCATE
TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.
DROP
The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.
