Notes SQL

Contents
=========
DB Termininology
Select Statement
Update Statement
Insert Statement
Delete Statement
Examples
Exporting DB
Replace Text / String
GROUP BY, DISTINCT
INNER JOIN ... ON
LEFT, RIGHT, FULL JOIN
DB tasks
COUNT


DB Termininology
=================
DDL Data Definition Language
CREATE
DROP
ALTER
TRUNCATE




DML Data Manipulation Language
SELECT
UPDATE
INSERT
DELETE


DCS Data Control Statements
GRANT
CONNECT
REVOKE
COMMIT
ROLLBACK
LOCK TABLE
AUDIT


Other Commands
desc
select count(*) from table    - count the total records for the table.


SQLPlus
select * from cat         - list all tables


Select Statement
=================
SELECT [columns] FROM [tables] WHERE [search_condition] LIKE [pattern] ORDER BY [order_expression ASC|DSC]
           *                           col1='blah'             'B%'
        col1,col2                       col1 < '123'           '%B'
                             cond1 AND cond2          '[abcd]%'
           
        [Agg func]


... where
[Agg func] = aggregate function = Avg(), Sum(), Min(), Max(), Count(*), Count(DISTINCT col)

WHERE logical operators
=  <  >  >=  <=  <> NOT  !=
BETWEEN foo AND bar
IN
    The IN operator implements comparison to a list of values, that is, it tests whether a value matches any value in a list of values. IN comparisons have the following general format:
           value-1 [NOT] IN ( value-2 [, value-3] ... )
     This comparison tests if value-1 matches value-2 or matches value-3, and so on. It is equivalent to the following logical predicate:
           value-1 = value-2 [ OR value-1 = value-3 ] ...
     or if NOT is included:
           NOT (value-1 = value-2 [ OR value-1 = value-3 ] ...)
     For example,
           SELECT name FROM s WHERE city IN ('Rome','Paris')




Update Statement
=================
UPDATE [table] SET [set_expression] WHERE [search_condition]

Insert Statement
==================
INSERT INTO [table] ([column list]) VALUES ([value list])
                    (col1, col2)            ('one', 'two', 3)

INSERT INTO [table] ([column list])  SELECT "column3", "column4", ...  FROM "table2"

INSERT INTO [table] ([column list])
SELECT "column3", "column4", ...
FROM ( MERGE ................. OUTPUT ....... )
AS [dummy table name] ([column list])



Delete Statement
=================
DELETE FROM [table] WHERE [search condition]


Examples
=========
select menuid from menu where foodid not in (select foodid from food);
--- check if there are any rows in menu with a foodid that is not in the food table. Note that
menuid and foodid are the primary keys for the menu and food table respectively.

    Compare values in one table but not the other.
select  distinct B.ex  from B
where B.ex not in (select  distinct F.ex from F)

Exporting DB
=============
When exporting a DB or table within a DB, we can use the Export function in HeidiSQL.

Note: Need to remove "MySQL specific comments" such as "/*«number» ... */" when uploading
to phpAdmin.

In the example below, need to remove "/*!32312 IF NOT EXISTS*/" or remove "/*!32312 */"

CREATE TABLE /*!32312 IF NOT EXISTS*/ `stateau` (
  `StateID` int(10) unsigned NOT NULL auto_increment,
  `Name` varchar(50) default NULL,
  `Code` varchar(3) default NULL,
  `Postcode` mediumint(8) unsigned default NULL,
  PRIMARY KEY  (`StateID`)
) TYPE=InnoDB AUTO_INCREMENT=4 /*!40100 DEFAULT CHARSET=latin1*/;

In MySQL syntax, the number in /*«number» ... */ stands for the version number.


Replace Text / String
======================
UPDATE Products_Descriptions SET ProductDescription = REPLACE(LTRIM(RTRIM(ProductDescription)), '©', '©')
update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, ‘find this string’, ‘replace found string with this string’);


GROUP BY, DISTINCT
====================

O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen

Now we want to find the total sum (total order) of each customer.
We will have to use the GROUP BY statement to group the customers.

SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer

The result-set will look like this:

Customer SUM(OrderPrice)
Hansen 2000
Nilsen 1700
Jensen 2000


Using GROUP BY by more than one column. When grouping, all columns that appear in the SELECT column list, that are not aggregated (used along with one of the SQL aggregate functions), have to appear in the GROUP BY clause too. For example:
      SELECT CustomerName, OrderDate, SUM(OrderPrice) FROM Sales
      GROUP BY CustomerName, OrderDate




DISTINCT cannot be used to get the results above.
Note that the following statements are the same:
   SELECT DISTINCT(Customers), OrderPrice FROM Orders
   SELECT DISTINCT Customers, OrderPrice FROM Orders
   SELECT Customers, OrderPrice FROM Orders

Their results are:
1000 Hansen
1600 Nilsen
700 Hansen
300 Hansen
2000 Jensen
100 Nilsen

Distinct always applies across all column names listed in the SELECT statement.



INNER JOIN ... ON
===================
The "Persons" table:

P_Id LastName FirstName Address City
1 Hansen   Ola   Timoteivn 10 Sandnes
2 Svendson Tove  Borgvn    23 Sandnes
3 Pettersen Kari Storgt    20 Stavanger

The "Orders" table:

O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 15

Now we want to list all the persons with any orders.

We use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

The result-set will look like this:

LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
Pettersen Kari 77895
Pettersen Kari 44678

LEFT, RIGHT, FULL JOIN
========================
The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).

SQL LEFT JOIN Syntax
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

PS: In some databases LEFT JOIN is called LEFT OUTER JOIN.



The RIGHT JOIN keyword Return all rows from the right table (table_name2), even if there are no matches in the left table (table_name1).

SQL RIGHT JOIN Syntax
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

PS: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.


The FULL JOIN keyword return rows when there is a match in one of the tables.

SQL FULL JOIN Syntax
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name

DB tasks
==========
   Copy table from one DB to another DB, given that the structure of the table has been created in the
   destination DB.
(MSSQL)
insert into DB2.dbo.table1
select * from DB1.dbo.table1


    List all column names in a table
(MSSQL)
SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'TableName'
SELECT Column_Name + ',' FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'TableName'

SELECT table_name=sysobjects.name,
         column_name=syscolumns.name,
         datatype=systypes.name,
         length=syscolumns.length
    FROM sysobjects
    JOIN syscolumns ON sysobjects.id = syscolumns.id
    JOIN systypes ON syscolumns.xtype=systypes.xtype
   WHERE sysobjects.xtype='U'
ORDER BY sysobjects.name,syscolumns.colid


    Select First N rows
(MSSQL)
SELECT TOP 10 name, description FROM TABLEA ORDER BY name
    The above query will return the first 10 rows sorted by name. How do I get the bottom 10 rows? Use the DESC keyword in the ORDER BY clause and it will sort it in reverse order.


    Counting the number of categories
select count(*) , ENTITY_ID
from Merge_CBA_ASBCommProp
group by ENTITY_ID

    The results are:
(name) ENTITY_ID
957240 3
830842 1
492520 2


COUNT
========
If the following code returns 15 records:
SELECT distinct a, b
FROM Tab
then to count the number for rows returned, we need:
SELECT count(*)
FROM
(
SELECT distinct a, b
FROM Tab
) AS outt
Note "AS outt" is needed for the inner results table to be ALIASED, otherwise the outer select cannot use it.