SELECT
The SELECT statement is used to select data from a database.
SYNTAX
SELECT column_name,column_name
FROM table_name;
FROM table_name;
and
SELECT * FROM table_name;
SAMPLE CODE
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
SELECT CustomerName, City FROM Customers;
Output:
| CustomerName | City |
|---|---|
| Alfreds Futterkiste | Hamburg |
| Ana Trujillo Emparedados y helados | México D.F. |
JOIN
SQL joins are used to combine rows from two or more tables.
SYNTAX
INNER JOIN table_name
SAMPLE CODE
Customer Table
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
Order Table
| OrderID | CustomerID | EmployeeID | OrderDate | ShipperID |
|---|---|---|---|---|
| 10308 | 2 | 7 | 1996-09-18 | 3 |
| 10309 | 37 | 3 | 1996-09-19 | 1 |
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID
Output:
| CustomerName | OrderID |
|---|---|
| Ana Trujillo Emparedados y helados | 10308 |
| Antonio Moreno Taquería | 10365 |
INSERT
The INSERT INTO statement is used to insert new records in a table.
SYNTAX
INSERT INTO table_name
VALUES (value1,value2,value3,...); AND
VALUES (value1,value2,value3,...); AND
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
VALUES (value1,value2,value3,...);
SAMPLE CODE
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 87 | Wartian Herkku | Pirkko Koskitalo | Torikatu 38 | Oulu | 90110 | Finland |
| 88 | Wellington Importadora | Paula Parente | Rua do Mercado, 12 | Resende | 08737-363 | Brazil |
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;
Output:
| OrderID | CustomerName | OrderDate |
|---|---|---|
| 10248 | Wilman Kala | 1996-07-04 |
| 10249 | Tradição Hipermercados | 1996-07-05 |
UPDATE
The UPDATE statement is used to update records in a table
SYNTAX
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
SAMPLE CODE
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
UPDATE Customers
SET ContactName='Alfred Schmidt', City='Hamburg'
WHERE CustomerName='Alfreds Futterkiste';
Output:
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Alfreds Futterkiste | Alfred Schmidt | Obere Str. 57 | Hamburg | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
DELETE
The DELETE statement is used to delete records in a table.
SYNTAX
DELETE FROM table_name
WHERE some_column=some_value;
SAMPLE CODE
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
WHERE CustomerName='Alfreds Futterkiste' AND ContactName='Maria Anders';
Output:
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
| 3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
DROP
Indexes, tables, and databases can easily be deleted/removed with the DROP statement.
SYNTAX
DROP TABLE table_name
and
DROP INDEX table_name.index_name
CREATE TABLE
The CREATE TABLE statement is used to create a table in a database.
SYNTAX
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);
SAMPLE CODE
CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
Output:
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
Output:
| PersonID | LastName | FirstName | Address | City |
|---|---|---|---|---|
Ref:
