INSERT
Query: INSERT INTO
orders (customer,day_of_order,product, quantity)
VALUES('Tizag','8/1/08','Pen',4);
------------------------------------------------------------------
Select
query: SELECT * FROM
orders;
---------------------------------------------------
Update
query: UPDATE orders SET
quantity = '6'
WHERE
id = '1'
---------------------------------------------------
Create
query: CREATE TABLE
orders
(
Name varchar(20),Userid varchar(6),Branchname varchar(6),
Email
varchar(30));
---------------------------------------------
Where
query: SELECT *
FROM
orders
WHERE
customer = 'Tizag'`
-------------------------------------------
As
query: SELECT
day_of_order AS "Date",
customer
As "Client",
product,
quantity
FROM
orders;
---------------------------------------
Operator
using >: SELECT
customer,day_of_order
FROM
orders
WHERE day_of_order > '7/31/08'
---------------------------------------
And
query: SELECT *
FROM
orders
WHERE
customer = 'Tizag'
AND
day_of_order = '08/01/08'
AND
product = 'Pen';
-----------------------------------------
Or
query: SELECT
*
FROM
orders
WHERE
product = 'Pen'
OR
product = '19" LCD Screen';
-----------------------------------------------
And
& Or query: SELECT
*
FROM
orders
WHERE
(quantity > 2 AND customer = 'Tizag')
OR
(quantity > 0 AND customer = 'Gerald Garner')
---------------------------------------------------
Between
query: SELECT
*
FROM
orders
WHERE
day_of_order BETWEEN '7/20/08' AND '8/05/08';
----------------------------------------------------
SQL
Select Between:
SELECT
*
FROM
orders
WHERE
day_of_order >= '7/20/08'
AND
day_of_order <= '8/05/08';
----------------------------------------------
Order
By query: SELECT
*
FROM orders
WHERE customer = 'Tizag'
ORDER BY day_of_order;
-------------------------------------------
sql - ascending &
descending
SQL Order by Descending: SELECT *FROM
orders
WHERE customer = 'Tizag'
ORDER BY day_of_order
DESC
SELECT *
FROM orders
ORDER BY customer,
day_of_order;
-----------------------------------------------
SQL
Order by Multiple columns:
UPDATE orders
SET quantity = '6'
WHERE id = '1'
------------------------------------------------
Update
query: UPDATE orders
SET quantity = (quantity
+ 2)
WHERE id = '1'
SQL
Verification Query: orders
SELECT *
FROM orders
WHERE id = '1'
sql - update incrementing a value:
UPDATE orders
SET quantity = '11',
Product = 'Hanging Files'
WHERE id = '1'
-------------------------------------
Alter
query: ALTER TABLE
orders
ADD discount VARCHAR(10);
sql -
alter table: modify column
ALTER TABLE orders
ALTER COLUMN discount
DECIMAL(18,2);
-----------------------------------------
sql -
sql alter table: drop
ALTER TABLE orders
DROP COLUMN discount;
-----------------------------
SQL
Select Distinct: SELECT DISTINCT
customer
FROM orders
WHERE day_of_order
BETWEEN '7/31/08' AND '9/1/08';
---------------------------------------------------------
SQL
Subquery Preview: SELECT MAX(day_of_order)
FROM orders
------------------------------------------------------------
SQL
Select Subquery Code: SELECT *
FROM orders
WHERE day_of_order =
(SELECT MAX(day_of_order) FROM orders)
-------------------------------------------------------------
SQL
Join Query Code: SELECT *
FROM orders
JOIN inventory
ON orders.product =
inventory.product;
-------------------------------------------------------
SQL
Join: SELECT
orders.customer,
orders.day_of_order,
orders.product,
orders.quantity as
number_ordered,
inventory.quantity as
number_instock,
inventory.price
FROM orders
JOIN inventory
ON orders.product =
inventory.product
1)sql - right join
SELECT *
FROM orders
RIGHT JOIN inventory
ON orders.product = inventory.product
2)sql - left join
SELECT *
FROM orders
LEFT JOIN inventory
ON orders.product = inventory.product
----------------------------------------------------------
SQL
In: SELECT *
FROM orders
WHERE customer IN
('Gerald Garner','A+Maintenance');
SQL
In: SELECT product
FROM orders
WHERE customer IN
('Gerald Garner','A+Maintenance');
SQL
In: SELECT *
FROM inventory
WHERE product in
(SELECT product
FROM orders
WHERE customer IN ('Gerald Garner','A+Maintenance'));
----------------------------------------------------------------
SQL
Not In: SELECT *
FROM inventory
WHERE product NOT IN
(SELECT product
FROM orders
WHERE customer IN ('Gerald
Garner','A+Maintenance'));
-----------------------------------------------------------------
SQL
Select Case Code:
SELECT product,
'Status' = CASE
WHEN quantity > 0 THEN 'in stock'
ELSE
'out of stock'
END
FROM dbo.inventory;
-------------------------------------------------------------------
SQL
Join Query:
SELECT dbo.orders.id,
dbo.orders.customer,
dbo.orders.quantity,
dbo.inventory.product,
dbo.inventory.price
FROM orders
JOIN inventory
ON orders.product =
inventory.product
-------------------------------------------------
SQL
Select Case Code:
SELECT dbo.orders.id,
dbo.orders.customer,
dbo.orders.quantity,
dbo.inventory.product,
dbo.inventory.price,
'SALE_PRICE'
= CASE
WHEN price > 0 THEN (price * .75)
END
FROM orders
JOIN inventory
ON orders.product =
inventory.product
----------------------------------------------------
SQL
Group By:
SELECT customer
FROM orders
GROUP BY customer;
SQL
Code: SELECT customer,
SUM(quantity) AS "Total Items"
FROM orders
GROUP BY customer;
Taking
a look at another example,
we can
also figure out how many of each product
was
ordered with the use of a single query statement.
SQL
Code: SELECT product,
SUM(quantity) AS "Total Items"
FROM orders
GROUP BY product;
GROUP
BY would also be a great way to calculate
how much total cash of our customers has
spent.
Let's
take a look at what that query may look like.
SQL
Code: SELECT customer,
SUM((orders.quantity * inventory.price)) AS
"COST"
FROM orders
JOIN inventory
ON orders.product =
inventory.product
GROUP BY customer;
sql - grouping by multiple columns
Like
the ORDER BY clause,
GROUP
BY can accept a list of table columns on which to group by.
SQL
Code: SELECT day_of_order,
product,
SUM(quantity) as "Total"
FROM orders
GROUP BY
day_of_order,product
ORDER BY day_of_order;
--------------------------------------------------------------
sql –
having
SQL
Having: SELECT day_of_order,
product,
SUM(quantity) as "Total"
FROM orders
GROUP BY
day_of_order,product,quantity
HAVING quantity > 7
ORDER BY day_of_order;
---------------------------------------------------------------
SQL
Create View Code: CREATE VIEW
virtualInventory
AS
SELECT * FROM inventory;
SQL
View Code: SELECT
*
FROM virtualInventory;
SQL Verification
Query Code:
SELECT *
FROM virtualInventory
WHERE product = 'Pen';
SQL
Drop
SQL Drop View: DROP
VIEW virtualInventory;
SQL
SELECT GETDATE():
USE
mydatabase;
SELECT
GETDATE();
Timestamp
Result:
2004-06-22
10:33:11.840
----------------------------------------------------
Using
a built in function, ISDATE()
we can do some testing on date values
to see
if they meet the formatting requirements.
SQL
Code:
USE
mydatabase;
SELECT
ISDATE('8/24/08')
AS "MM/DD/YY",
ISDATE('2004-12-01')
AS "YYYY/MM/DD";
---------------------------------------------------
sql -
month(), day(), year()
1) SQL
Year(): SELECT YEAR(GETDATE()) as
"Year";
SELECT YEAR('8/14/04') as
"Year";
2)SQL
Month(): SELECT MONTH(GETDATE()) as
"Month";
SELECT MONTH('8/14/04')
as "Month";
3)SQL
Day(): SELECT DAY(GETDATE()) as
"Day";
SELECT DAY('8/14/04') as
"Day";
-----------------------------------------------------------
SQL
Datepart:
SELECT DATEPART(year,
'2007-06-01') AS "Year";
DATEPART()
requires 2 parameters separated by a comma (,).
The first parameter specifies what type of
date data will be extracted,
and
the second parameter is a timestamp value.
SQL
Datepart:
SELECT DATEPART(year, '2007-06-01') AS
"Year",
DATEPART(month, '2007-06-01') AS
"Month",
DATEPART(day, '2007-06-01') AS
"Day",
DATEPART(dayofyear, '2007-06-01') AS
"DayofYear",
DATEPART(weekday, '2007-06-01') AS
"Weekday";
----------------------------------------------------------------------------
sql -
dateadd()
SQL
Code: SELECT DATEADD(year, 1,
getdate()) AS "+1 Year";
This
example shows how to use DATEADD()
to
take a specified date value and increment
it by
the 'year' date part. By replacing
the
middle parameter with a negative value,
we can
utilize the same DATEADD() function
to
subtract dates as well.
SQL
Code: SELECT DATEADD(day,-1,
'2006-06-01') AS "-1 Day";
SELECT DATEADD(day,-30,
(SELECT MAX(day_of_order) FROM orders)) AS "-30 Days";
SELECT
*
FROM
orders
WHERE
day_of_order >
(SELECT
DATEADD(day,-30, (SELECT MAX(day_of_order) FROM orders)) AS "-30
Days");
-----------------------------------------------------------------------------
SQL
Delete Commands:
DELETE
- Deletes any number of rows from a data object.
DROP -
Removes table columns, tables, and all data objects SQL applications.
TRUNCATE
- Empties out data without removing the object itself.
sql –
delete:
DELETE
queries work much like UPDATE queries and like UPDATE,
it is much advised to always use a WHERE
condition when running
any
delete query or else you risk deleting too much data.
SQL
Delete Query:
USE
mydatabase;
DELETE
FROM
orders
WHERE
customer = 'A+Maintenance';
sql –
truncate:
SQL
TRUNCATE is the fastest way to remove all data from a SQL table,
leaving
nothing but an empty shell. You might choose to use this command
when all the data inside of a table needs to
be removed but you'd like
the
table column definitions to remain intact.
SQL
Truncate Table Code:
USE
mydatabase;
TRUNCATE
TABLE orders;
sql –
drop:
SQL
DROP is another command that removes data from the data store.
The DROP command must be performed on SQL
objects including databases,
tables, table columns, and SQL views. Dropping
any of these objects
removes
them completely from your SQL application and all data contained
in any of the data objects dropped are lost
forever.
SQL
Drop Examples:
DROP
TABLE orders;
DROP
DATABASE mydatabase;
DROP
VIEW viewname;
DROP
INDEX orders.indexname;
FOR
USE WITH ALTER COMMANDS
DROP
COLUMN column_name
DROP
FOREIGN KEY (foreign_key_name)
--------------------------------------------------------------
SQL
Select Union Code:
SELECT
* FROM employees
UNION
SELECT
* FROM employees2;
SQL
Code: SELECT employees.Lastname,
employees.Firstname, invoices.Sale, invoices.Price
FROM employees
INNER JOIN invoices
ON employees.id =
invoices.EmployeeID
UNION
SELECT
employees2.Lastname, employees2.Firstname, invoices.Sale, invoices.Price
FROM employees2
INNER JOIN invoices
ON employees2.id =
invoices.EmployeeID;
**********************************************************************************
sql -
union all
UNION
ALL selects all rows from each table and combines them into a single table.
The
difference between UNION and UNION ALL is that UNION ALL will not eliminate
duplicate rows. Instead, it just pulls all
rows from all tables fitting your
query specifics and combines them into a
table.
SQL
Code: SELECT * FROM employees
UNION ALL
SELECT * FROM employees2;
***********************************************************************************
SELECT
employees.Lastname, employees.Firstname, invoices.Sale, invoices.Price
FROM
employees
INNER
JOIN invoices
ON
employees.id = invoices.EmployeeID
UNION
ALL
SELECT
employees2.Lastname, employees2.Firstname, invoices.Sale, invoices.Price
FROM
employees2
INNER
JOIN invoices
ON
employees2.id = invoices.EmployeeID;
