SQL Crib Sheet for iSeries and .NET
A friend was asking me for some SQL samples the other day so I dug up this old crib sheet from some time ago. These can be used with slight modification in both iSeries and .NET queries. Enjoy!
Select Statements
Return all records all columns in a table:
select * from MyTableq3
Return all records but only cqcus and cqcush in a table:
select cqcus, cqcush from MyTableq3
Return cqcus for all records in a table with a specific value for cqcush:
select cqcus from MyTableq3 where cqcush=123
Return all records in a table where cqcus is one of three possible values:
select * from MyTableq3 where cqcus in (value1,value2,value3)
Return the number of records in a table:
select count(*) from TheTable
Return the number of records in a table with a specific value for cqcush:
select count(*) from MyTableq3 where cqcush=123
Simple join:
select * from MyTableq3, MyTable
where MyTableq3.cqcus=MyTable .cqcusA
or
select MyTableq3.cqcus, MyTable .cqcusA from MyTableq3, MyTable
where MyTableq3.cqcush=MyTable .cqcusB
Select all unique values in cqcus from a table:
select distinct(cqcus) from MyTableq3
or
select distinct cqcus from MyTableq3
Select all unique values for cqcus from a table together with the number of records with that unique value:
select cqcus, count(*) from MyTableq3
group by cqcus
Select all unique values for combinations of cqcus and cqcush from a table together with the number of records with that combination:
select cqcus, cqcush, count(*) from MyTableq3
group by cqcus, cqcush
Select the number of unique values:
select count(distinct cqcus) from MyTableq3
Select all duplicate records in a table, where two (or more) records are considered duplicates if they share a common value for a single cqcus:
select cqcus, count(cqcus) from MyTableq3
group by cqcus
having count(*) > 1
Select all duplicate records in a table, where two (or more) records are considered duplicates if they share common values for a pair of cqcuss:
select cqcus, cqcush, count(*) from MyTableq3
group by cqcus, cqcush
having count(*) > 1
Select similar records, i.e. all records which have duplicate cqcus and cqcush in a table but with different cqcus3 (i.e. specifying which cqcuss must be the same and which different):
select * from table as A, table as B
where A.cqcus=B.cqcus
and A.cqcush=B.cqcush
and A.cqcus3<>B.cqcus3;
Note:
* It is important to specify at least one cqcus which is different between the two records otherwise this query will list a record as being the same as itself.
* This query will not find duplicate records, i.e. records with every cqcus the same.
Select all records from a table which do not share a common ID with records from a second table:
select * from MyTableq3
where cqcus not in (select cqcush from MyTable )
Note:
* Sub-queries are quite slow.
.
An alternative using a join (which can be much faster):
select MyTableq3.* from MyTableq3
left join MyTable on (MyTableq3.cqcus = MyTable .cqcush)
where MyTable .cqcush is null;
The following method (which has been suggested by Michael Miller) is to use EXISTS. It is much faster on SQL Server than the above (but Michael says it is comparable with the left join technique on Oracle):
select * from MyTableq3
where not exists (select cqcush from MyTable where MyTable .cqcush = MyTableq3.cqcus)
To perform a two way join:
select * from
MyTableq3 left join MyTable on (MyTableq3.cqcus = MyTable .cqcus),
MyTableq3 left join table3 on (MyTableq3.cqcush = table3.cqcus3)
this has been tested on SQL Server, but not on Oracle or MySql. It does not work with MS-Access.
To combine the results of two queries (be aware that the number and types of cqcuss in both queries must agree):
select * from MyTableq3
union select * from MyTable
To return a value based on the contents of a cqcus. This can be done using either Iif, Decode or Case, depending on the database.
The following works with MSAccess:
select Iif(cqcus = 1, 'one', 'not one')
from MyTableq3
This is equivalent to the following on SqlServer:
select Case when cqcus = 1 then 'One' else 'Two' End
from MyTableq3
Direct join to see if any records from table 1 match table 2
SELECT statement run complete.
select * from MyTableq3, MyTable
where cqcus=cfcus and cqcush=cfcush
and cqvar = cfvar
and cqlv = cflv
and cqsizgrp = cfsizgrp
For Oracle use the DECODE function.
To create a new table to hold the results of the select query:
select * into MyTable from MyTableq3
Be aware that this will fail if MyTable exists, and that the new table will be created without any indexes.
Insert
Insert new record into a table:
insert into MyTableq3 values (1,2,3)
Insert new record into a table explicitly naming cqcuss:
insert into MyTableq3 (cqcus,cqcush,cqcus3) values (1,2,3)
Insert new record into a table using values from another table:
insert into MyTableq3 (cqcus,cqcush,cqcus3)
select cqcusA,2,cqcusC from SomeTable
Update
Update all records in a table:
update MyTableq3 set cqcus=2
Update specific records in a table:
update MyTableq3 set cqcus=2 where cqcus=1
To update more than one cqcus at a time:
update MyTableq3 set cqcus=2, cqcush=3
Update a cqcus in a table using a value from another table where both records are referenced by a common key - warning, different databases support different syntax!
This works in MS-Access and MySQL (5) but not in SQL Server:
update TableOne
inner join TableTwo on TableOne.commonID = TableTwo.commonID
set TableOne.cqcus = TableTwo.cqcusX
or
This works in MS-Access but not in SQL Server:
update TableOne, TableTwo
set TableOne.cqcus = TableTwo.cqcusX
where TableOne.commonID = TableTwo.commonID
or
update tableOne
set tableOne.cqcus=tableTwo.cqcusX
from tableOne, tableTwo
where tableOne.commonID=tableTwo.commonID
Delete
Delete all records in a table (dangerous):
delete from MyTableq3
Delete specific records in a table:
delete from MyTableq3 where cqcus=value
Delete records from one table which do not have a matching cqcus in another table:
delete from MyTableq3 where cqcus not in
(select cqcush from TableTwo)
Keys
Be aware that there are often subtle syntax variations between different database systems. Also other key properties (for example 'clustered') will vary between database systems. Therefore please treat this part of the SQL crib sheet as a guide only.
Create a primary key on a table:
Alter Table TheTable Add Primary Key (cqcus, cqcush)
To add an index on a cqcus:
alter table MyTableq3 Add Index (cqcus)
To remove a primary key:
alter table drop primary key
2 Comments:
Nice review :)
Flipkart is one of the biggest world wide eCommerce Site flipkart cashback offersso
Flipkart is one of the biggest world wide eCommerce Site Flipkart hdfc offersso
Flipkart is Offering Cashback Offers Flipkart YES Cashback Offer You will get upto 75% Discount
Flipkart is Offering Cashback Offers Flipkart Phonepe Cashback Offer You will get upto 75% Discount
Flipkart is Offering Cashback Offers Flipkart Cashback Offer You will get upto 75% Discount
Post a Comment
Subscribe to Post Comments [Atom]
<< Home