29 July 2013

sql queries




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;


No comments:

Post a Comment