WDP - Database Queries

SELECT

The SELECT statement is used to select data from a database.

SYNTAX

SELECT column_name,column_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

INSERT INTO table_name (column1,column2,column3,...)
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

DELETE FROM Customers
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),
....
);

SAMPLE CODE

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);



Output:

PersonID	LastName	FirstName	Address	City

Ref:

http://www.w3schools.com/sql/sql_select.asp

http://www.w3schools.com/sql/sql_join.asp
http://www.w3schools.com/sql/sql_update.asp

http://www.w3schools.com/sql/sql_insert.asp

http://www.w3schools.com/sql/sql_delete.asp
http://www.w3schools.com/sql/sql_drop.asp