[macosxnerd101]

This week's challenge is Structured Query Language, more commonly known as SQL. Today, SQL is the standard tool for working with relational databases, and its functionalities can be broken down into the categories of Queries, Clauses, Expressions, and Statements. However, SQL is not always used by itself, but often in conjunction with an application to interact with the Database Management System.

Getting Started
Pick your favorite database engine. MySQL and PostgreSQL are two commonly used free options. There is also Microsoft SQL Server, which has a demo version, and Oracle database engines as well.

Possible Ideas

• Use your favorite language to work with a SQL Engine
  
• Define a Database or Table
  
• Select a random record from the table
  
• Perform a join
  
• Alter table structure

Resources
W3Schools
MySQL Manual
DIC Database Tutorials
DIC Database Help Forum

[dorknexus]

If I use Django does that count?

[macosxnerd101]

Django is fine, so long as you use it to work with SQL somehow!

[dorknexus]

well it generates all my sql automagically. so does that count?

[mostyfriedman]

I may use rails for that

[macosxnerd101]

Dark_Nexus, on 29 September 2010 - 03:18 AM, said:

well it generates all my sql automagically. so does that count?

No. The point is to write these yourself.

[raziel_]

Heh i just do this kind of stuff today

            string strSQL = "SELECT Sells.DocType, Sells.ArtID, Sells.SPrice, Sells.SSum, " +      
                "Sells.SQty, Sells.RSum, Sells.RPrice, Sells.DocNum, Sells.IDTrans, " +
                "Transactions.IDOper, Transactions.DocDate, Transactions.TransDate FROM Sells "+
                "INNER JOIN Transactions ON Transactions.ID=Sells.IDTrans WHERE Sells.DocType>19";

or

        strSQL = _
            "SELECT " & _
                "store_objects.quantity, storeplaces.idstore as idstore, storeplaces.id as idplace, storeplaces.idstruct, storeplaces.struct_data, " & _
                "object_types.serial, object_types.name, object_types.signature, objects.id, object_types.id as objID "
                
        strSQL = strSQL & _
            "FROM " & _
                "object_types " & _
                "INNER JOIN objects ON objects.idobjecttype=object_types.id " & _
                "INNER JOIN store_objects ON store_objects.idobject=objects.id " & _
                "INNER JOIN storeplaces ON storeplaces.id=store_objects.idplace "
                        

or

    With cmd
        .ActiveConnection = cnn.ConnectionString
        .CommandType = adCmdText
        .CommandText = " ALTER TABLE " & Tbl & " ADD COLUMN " & fld & " Integer DEFAULT 0"
        .Execute
    End With

SQL is easy

This post has been edited by NoBrain: 30 September 2010 - 04:02 AM

[Munawwar]

I was working on MySQL triggers and random password generation a couple of days back.

Using the MySQL console -
The random password generation procedure:

#Before any do anything, change delimiter to //
delimiter //

CREATE PROCEDURE dbs.randomPass(OUT _password VARCHAR(7))
BEGIN
	DECLARE count INT DEFAULT 0;
	DECLARE alphanum INT;
	DECLARE randomCharacter CHAR(1);
	DECLARE password VARCHAR(10) DEFAULT "";
	
	WHILE count<7 DO
		SET count=count+1;
		SELECT ROUND(RAND()*10) INTO alphanum;

		IF alphanum<5 THEN
			#Generate a random digit
			SELECT CHAR(48+MOD(ROUND(RAND()*100),10)) INTO randomCharacter;
			
			#Debug message
			#SELECT alphanum AS 'RAND','less than 5' AS 'flow', randomCharacter AS 'random character';
		ELSE
			#Generate a random digit
			SELECT CHAR(65+MOD(ROUND(RAND()*100),26)) INTO randomCharacter;
			
			#Debug message
			#SELECT alphanum AS 'RAND','more than or equal to 5' AS 'flow', randomCharacter AS 'random character';
		END IF;
		#CONCAT function concatenates two or more strings and returns result
		SELECT CONCAT(password,randomCharacter) INTO password;
	END WHILE;
	
	#Debug message
	#SELECT password AS 'Password';
	SET _password=password;
END; //

And the MySQL trigger:

CREATE TRIGGER dbs.user_insert BEFORE INSERT ON dbs.users
	FOR EACH ROW BEGIN
		CALL dbs.randomPass(NEW.password);
	END;//

#and change delimiter back to semi-colon after testing
delimiter ;

[aniri]

I've used sql with my latest php project. Here's a select statement from it:

$result = mysql_query("select distinct id_unitate from c_afisari a, c_unitati u where u.stare='activa' and u.harta_localitate=".$idloc." and u.id=a.id_unitate");

[motcom]

Some of my code:

WITH IncomeTaxSubmissiosUnion AS (
SELECT 
	I.ID AS Code,
	I.ITaxNo + ' 011' AS IncomeTaxNo,
	'28/Feb/' + IR.ITYear AS YearEnd, 
	I.Surname + ', ' + I.Name AS ClientName,
	ISNULL(I.ITaxStartDate, ISNULL(I.ClientStartDate,ISNULL(I.DOBIncorporation,'01-Jan-2004'))) AS StartDate, 
	ISNULL(IR.ExtensionHeldTo, IR.DueDate) AS DueDate,
	IR.ExtensionHeldTo,
	IR.DateSubmitted,
	IR.ITYear,
	'Individuals' AS EntityType,
	'' AS PublicOfficer,
	ISNULL(P.Abbre, 'No Partner') AS [Partner],
	ISNULL(C.Abbre, 'No Consultant') AS [Consultant],
                ISNULL(IC.Abbre, 'No In Charge') AS [InCharge],
	ISNULL(G.GroupName, 'Not Grouped') AS GroupName
FROM Individuals I 
INNER JOIN IncometaxReturn IR ON I.ID = IR.EntityID 
AND (CAST(DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, ('01-' + ISNULL((SELECT IndivYearEnd FROM IncomeTaxSettings),'Feb') + '-' + IR.ITYear)) + 1, 0)) AS DATETIME)  BETWEEN @StartDate AND @EndDate) 
AND (ISNULL(I.ITaxStartDate, ISNULL(I.ClientStartDate,ISNULL(I.DOBIncorporation,'01-Jan-2004'))) <=CAST(DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, ('01-' + ISNULL((SELECT IndivYearEnd FROM IncomeTaxSettings),'Feb') + '-' + IR.ITYear)) + 1, 0)) AS DATETIME))  
AND (I.Client = 1) AND  (I.IsDeleted = 0 OR
                      I.IsDeleted IS NULL)
LEFT OUTER JOIN Consultants P ON P.ID = I.PartnerID AND (P.EndDate IS NULL OR
                      P.EndDate = '') AND (P.IsOnSecretarial = 1)
LEFT OUTER JOIN Consultants C ON C.ID = I.ConsultantID AND (C.EndDate IS NULL OR
                      C.EndDate = '') AND (C.IsOnSecretarial = 1)
LEFT OUTER JOIN GroupList GL ON GL.EntityID = I.ID
LEFT OUTER JOIN Groups G ON GL.GroupID = G.GroupID
LEFT OUTER JOIN Consultants IC ON IC.ID = I.InCharge AND (IC.EndDate IS NULL OR
                      IC.EndDate = '') AND (IC.IsOnSecretarial = 1)
WHERE  (I.ClientEndDate IS NULL OR I.ClientEndDate = '')
UNION ALL
SELECT 
	I.ID AS Code, 
	I.ITaxNo + ' 011' AS IncomeTaxNo,
	I.YearEnd + '/' + IR.ITYear AS YearEnd, 
	I.CCName AS ClientName,
	ISNULL(I.ITaxStartDate, ISNULL(I.TakeOnDate,ISNULL(I.DateOfRegistration,'01-Jan-2004'))) AS StartDate, 
	ISNULL(IR.ExtensionHeldTo, IR.DueDate) AS DueDate,
	IR.ExtensionHeldTo,
	IR.DateSubmitted,
	IR.ITYear,
	'CCs' AS EntityType,
	I.PublicOfficer + ', ' + I.PublicOfficerInitials AS PublicOfficer,
	ISNULL(P.Abbre, 'No Partner') AS [Partner],
	ISNULL(C.Abbre, 'No Consultant') AS [Consultant],
                ISNULL(IC.Abbre, 'No In Charge') AS [InCharge],
	ISNULL(G.GroupName, 'Not Grouped') AS GroupName
FROM CCs I 
INNER JOIN IncometaxReturn IR ON I.ID = IR.EntityID 
AND CAST(I.YearEnd + '/' + IR.ITYear AS DateTime) BETWEEN @StartDate AND @EndDate
AND ISNULL(I.ITaxStartDate, ISNULL(I.TakeOnDate,ISNULL(I.DateOfRegistration,'01-Jan-2004'))) <=CAST(I.YearEnd + '/' + IR.ITYear AS DateTime) AND (I.ArchiveDate IS NULL OR I.ArchiveDate = '') AND  (I.IsDeleted = 0 OR
                      I.IsDeleted IS NULL) AND (I.TaxClient = 1)
LEFT OUTER JOIN Consultants P ON P.ID = I.PartnerID AND (P.EndDate IS NULL OR
                      P.EndDate = '') AND (P.IsOnSecretarial = 1)
LEFT OUTER JOIN Consultants C ON C.ID = I.ConsultantID AND (C.EndDate IS NULL OR
                      C.EndDate = '') AND (C.IsOnSecretarial = 1)
LEFT OUTER JOIN GroupList GL ON GL.EntityID = I.ID
LEFT OUTER JOIN Groups G ON GL.GroupID = G.GroupID
LEFT OUTER JOIN Consultants IC ON IC.ID = I.InCharge AND (IC.EndDate IS NULL OR
                      IC.EndDate = '') AND (IC.IsOnSecretarial = 1)
UNION ALL
SELECT 
	I.ID AS Code, 
	I.ITaxNo + ' 011' AS IncomeTaxNo, 
	I.YearEnd + '/' + IR.ITYear AS YearEnd, 
	I.CompanyName AS ClientName,
	ISNULL(I.ITaxStartDate, ISNULL(I.TakeOnDate,ISNULL(I.DateOfRegistration,'01-Jan-2004'))) AS StartDate, 
	ISNULL(IR.ExtensionHeldTo, IR.DueDate) AS DueDate,
	IR.ExtensionHeldTo,	
	IR.DateSubmitted,
	IR.ITYear,
	'Companies' AS EntityType,
	I.PublicOfficer + ', ' + I.PublicOfficerInitials AS PublicOfficer,
	ISNULL(P.Abbre, 'No Partner') AS [Partner],
	ISNULL(C.Abbre, 'No Consultant') AS [Consultant],
                ISNULL(IC.Abbre, 'No In Charge') AS [InCharge],
	ISNULL(G.GroupName, 'Not Grouped') AS GroupName
FROM Companies I 
INNER JOIN IncometaxReturn IR ON I.ID = IR.EntityID AND CAST(I.YearEnd + '/' + IR.ITYear AS DateTime)  BETWEEN @StartDate AND @EndDate AND ISNULL(I.ITaxStartDate, ISNULL(I.TakeOnDate,ISNULL(I.DateOfRegistration,'01-Jan-2004'))) <= CAST(I.YearEnd + '/' + IR.ITYear AS DateTime) AND (I.ArchiveDate IS NULL OR I.ArchiveDate = '') AND (I.TaxClient = 1) AND  (I.IsDeleted = 0 OR
                      I.IsDeleted IS NULL)
LEFT OUTER JOIN Consultants P ON P.ID = I.PartnerID AND (P.EndDate IS NULL OR
                      P.EndDate = '') AND (P.IsOnSecretarial = 1)
LEFT OUTER JOIN Consultants C ON C.ID = I.ConsultantID AND (C.EndDate IS NULL OR
                      C.EndDate = '') AND (C.IsOnSecretarial = 1)
LEFT OUTER JOIN GroupList GL ON GL.EntityID = I.ID
LEFT OUTER JOIN Groups G ON GL.GroupID = G.GroupID
LEFT OUTER JOIN Consultants IC ON IC.ID = I.InCharge AND (IC.EndDate IS NULL OR
                      IC.EndDate = '') AND (IC.IsOnSecretarial = 1)
UNION ALL
SELECT 
	I.ID AS Code, 
	I.ITaxNo + ' 011' AS IncomeTaxNo, 
	I.YearEnd + '/' + IR.ITYear AS YearEnd, 
	I.Name AS ClientName,
	ISNULL(I.ITaxStartDate, ISNULL(I.ClientStartDate,ISNULL(I.DOBIncorporation,'01-Jan-2004'))) AS StartDate, 
	ISNULL(IR.ExtensionHeldTo, IR.DueDate) AS DueDate,
	IR.ExtensionHeldTo,
	IR.DateSubmitted,
	IR.ITYear,
	'Trusts' AS EntityType,
	I.PublicOfficer + ', ' + I.PublicOfficerInitials AS PublicOfficer,
	ISNULL(P.Abbre, 'No Partner') AS [Partner],
	ISNULL(C.Abbre, 'No Consultant') AS [Consultant],
                ISNULL(IC.Abbre, 'No In Charge') AS [InCharge],
	ISNULL(G.GroupName, 'Not Grouped') AS GroupName
FROM Trusts I 
INNER JOIN IncometaxReturn IR ON I.ID = IR.EntityID AND CAST(I.YearEnd + '/' + IR.ITYear AS DateTime)  BETWEEN @StartDate AND @EndDate AND ISNULL(I.ITaxStartDate, ISNULL(I.ClientStartDate,ISNULL(I.DOBIncorporation,'01-Jan-2004'))) <= CAST(I.YearEnd + '/' + IR.ITYear AS DateTime) AND (I.ArchiveDate IS NULL OR I.ArchiveDate = '' ) AND  (I.IsDeleted = 0 OR
                      I.IsDeleted IS NULL) AND (I.TaxClient = 1)
LEFT OUTER JOIN Consultants P ON P.ID = I.PartnerID AND (P.EndDate IS NULL OR
                      P.EndDate = '') AND (P.IsOnSecretarial = 1)
LEFT OUTER JOIN Consultants C ON C.ID = I.ConsultantID AND (C.EndDate IS NULL OR
                      C.EndDate = '') AND (C.IsOnSecretarial = 1)
LEFT OUTER JOIN GroupList GL ON GL.EntityID = I.ID
LEFT OUTER JOIN Groups G ON GL.GroupID = G.GroupID
LEFT OUTER JOIN Consultants IC ON IC.ID = I.InCharge AND (IC.EndDate IS NULL OR
                      IC.EndDate = '') AND (IC.IsOnSecretarial = 1)
) SELECT * FROM IncomeTaxSubmissiosUnion 

This bit is used to fetch data from 4 different entity types with related records lots of UNIONS and Date Operations and JOINS all incorporated in a WITH statement

or this one to find the next available number.... as this needs leeding zeor's

with digits (i) as(
                             select 1 as i union all  select 2 as i union all select 3 union all
                             select 4 union all select 5 union all select 6 union all select 7 union all 
                             select 8 union all select 9 union all select 0)
,sequence (i) as (
SELECT  D1.i 
        + (10*D2.i)
        + (100*D3.i)
        + (1000*D4.i)
             
FROM digits AS D1
     ,digits AS D2
     ,digits AS D3
     ,digits AS D4     
)

SELECT s.i AS AvailableCode FROM sequence s  LEFT OUTER JOIN GLAccounts GL ON s.i = GL.GLMainAccountCode WHERE GL.GLMainAccountCode IS NULL AND s.i >= @StartCode AND s.i <= @EndCode ORDER BY s.i

[Munawwar]

WOW, motcom, you should get a noble prize for writing that SQL query.

[RandomlyKnighted]

haha I haven't done any of the 52 weeks of code before but I read this one and what's funny is I learned all this about 2 months ago. That makes me feel good now haha

Here is my left outer join:

SELECT students.first_name AS 'Student First Name', students.last_name AS 'Student Last Name', colleges.college AS 'College Name'
FROM students LEFT OUTER JOIN colleges
ON students.college_id = colleges.college_id
ORDER BY [College Name]

[motcom]

@Munawwar, Yours is not to shabby either...

[BobRodes]

Here's a bit of SQL Server code that runs a query and persists the result to an XML file. I don't know if sp_makewebtask is available on other DBMS's.

ALTER PROCEDURE [dbo].[spe_SaveXML10] 
@DealerCode int
AS
declare @strVal varchar(300)
select @strval = 'select distinct m.mnm_id, o.* from options o  
join bodycodes b on o.opt_body_code = b.body_code and o.opt_year = b.body_year and o.opt_make_id = b.body_make_id  
join modelnames m on b.body_mnm_id = m.mnm_id 
where o.opt_dlr_code in (0, ' + cast(@DealerCode as varchar(12)) + ') --Dealer Code 0 is factory-installed option
order by m.mnm_id, o.opt_code, o.opt_year
for xml raw
exec sp_makewebtask 
@outputfile = 'c:\website\edge 3\tempfiles\test1.xml', 
@query = @strval,  
@templatefile = 'c:\website\edge 3\xml10.tpl'