13 Replies - 9438 Views - Last Post: 17 November 2010 - 12:28 AM

#1 macosxnerd101  Icon User is offline

  • Self-Trained Economist
  • member icon




Reputation: 10185
  • View blog
  • Posts: 37,603
  • Joined: 27-December 08

Week #34- SQL

Post icon  Posted 28 September 2010 - 10:23 AM

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

Is This A Good Question/Topic? 0
  • +

Replies To: Week #34- SQL

#2 dorknexus  Icon User is offline

  • or something bad...real bad.
  • member icon

Reputation: 1255
  • View blog
  • Posts: 4,618
  • Joined: 02-May 04

Re: Week #34- SQL

Posted 28 September 2010 - 05:08 PM

If I use Django does that count?
Was This Post Helpful? 0
  • +
  • -

#3 macosxnerd101  Icon User is offline

  • Self-Trained Economist
  • member icon




Reputation: 10185
  • View blog
  • Posts: 37,603
  • Joined: 27-December 08

Re: Week #34- SQL

Posted 28 September 2010 - 05:15 PM

Django is fine, so long as you use it to work with SQL somehow!
Was This Post Helpful? 0
  • +
  • -

#4 dorknexus  Icon User is offline

  • or something bad...real bad.
  • member icon

Reputation: 1255
  • View blog
  • Posts: 4,618
  • Joined: 02-May 04

Re: Week #34- SQL

Posted 29 September 2010 - 12:18 AM

well it generates all my sql automagically. so does that count?
Was This Post Helpful? 0
  • +
  • -

#5 mostyfriedman  Icon User is offline

  • The Algorithmi
  • member icon

Reputation: 726
  • View blog
  • Posts: 4,473
  • Joined: 24-October 08

Re: Week #34- SQL

Posted 29 September 2010 - 12:21 AM

I may use rails for that
Was This Post Helpful? 0
  • +
  • -

#6 macosxnerd101  Icon User is offline

  • Self-Trained Economist
  • member icon




Reputation: 10185
  • View blog
  • Posts: 37,603
  • Joined: 27-December 08

Re: Week #34- SQL

Posted 29 September 2010 - 04:22 AM

View PostDark_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. :)
Was This Post Helpful? 0
  • +
  • -

#7 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 463
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: Week #34- SQL

Posted 30 September 2010 - 04:01 AM

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 :P

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

Was This Post Helpful? 1
  • +
  • -

#8 Munawwar  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 162
  • View blog
  • Posts: 457
  • Joined: 20-January 10

Re: Week #34- SQL

Posted 16 October 2010 - 04:04 AM

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 ;


Was This Post Helpful? 3
  • +
  • -

#9 aniri  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 54
  • View blog
  • Posts: 657
  • Joined: 24-November 09

Re: Week #34- SQL

Posted 18 October 2010 - 01:01 AM

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");

Was This Post Helpful? 1
  • +
  • -

#10 motcom  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 290
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: Week #34- SQL

Posted 18 October 2010 - 05:18 AM

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



Was This Post Helpful? 3
  • +
  • -

#11 Munawwar  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 162
  • View blog
  • Posts: 457
  • Joined: 20-January 10

Re: Week #34- SQL

Posted 18 October 2010 - 12:47 PM

WOW, motcom, you should get a noble prize for writing that SQL query.
Was This Post Helpful? 0
  • +
  • -

#12 RandomlyKnighted  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 115
  • View blog
  • Posts: 1,332
  • Joined: 14-January 10

Re: Week #34- SQL

Posted 20 October 2010 - 05:58 PM

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. :w00t: 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]


Was This Post Helpful? 1
  • +
  • -

#13 motcom  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 290
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: Week #34- SQL

Posted 21 October 2010 - 01:11 AM

@Munawwar, Yours is not to shabby either... :)
Was This Post Helpful? 0
  • +
  • -

#14 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,979
  • Joined: 19-May 09

Re: Week #34- SQL

Posted 17 November 2010 - 12:28 AM

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'


Was This Post Helpful? 1
  • +
  • -

Page 1 of 1