Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

Intro to SQL for Project Managers (and those who are curious)

A tutorial for the project managers at my company who occasionally flirt with SQL.
by

Andrew Brampton

on 11 July 2011

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Intro to SQL for Project Managers (and those who are curious)

You can use boolean logic in the WHERE clause.
AND
OR
NOT SQL For Project Managers
(and for those who are curious) SELECT Structured Query Language for accessing and manipulating data
pronouced Sequel or S.Q.L. date_sent
2011-06-06 14:17:05
2011-06-06 14:17:06
2011-06-06 14:37:11
2011-06-06 14:37:12
2011-06-06 14:37:41
2011-06-06 14:37:41
2011-06-06 14:37:51
2011-06-06 14:37:51
2011-06-06 14:39:18
2011-06-06 14:39:18 shortcode
99222
99222
99222
99222
99222
99222
99222
99222
99222
99222 number
15551234567
5551234567
15557654321
5557654321
15551234567
5551234567
15715555555
15715555555
15715555555
15715555555 carrier
cingular
cingular
cingular
cingular
cingular
cingular
verizon
verizon
verizon
verizon direction
0
1
0
1
0
1
0
1
0
1 message
Ssq
Thanks, u r now subscribed 2 Salmans Campaign....
Sleep uv
We are sorry but SLEEP UV is not a valid keyword....
Ssq
U have already subscribed 2 Salmans Campaign....
Ssq
Thanks, u r now subscribed 2 Salmans Campaign....
A
We are sorry but A is not a valid keyword... Column or Field Date/time Numbers Strings Rows Table Used to retreive rows from a table SELECT * FROM message; date_sent
2011-06-06 14:17:05
2011-06-06 14:17:06
2011-06-06 14:37:11
2011-06-06 14:37:12
2011-06-06 14:37:41
2011-06-06 14:37:41
2011-06-06 14:37:51
2011-06-06 14:37:51
2011-06-06 14:39:18
2011-06-06 14:39:18 shortcode
99222
99222
99222
99222
99222
99222
99222
99222
99222
99222 number
15551234567
5551234567
15557654321
5557654321
15551234567
5551234567
15715555555
15715555555
15715555555
15715555555 carrier
cingular
cingular
cingular
cingular
cingular
cingular
verizon
verizon
verizon
verizon direction
0
1
0
1
0
1
0
1
0
1 message
Ssq
Thanks, u r now subscribed 2 Salmans Campaign....
Sleep uv
We are sorry but SLEEP UV is not a valid keyword....
Ssq
U have already subscribed 2 Salmans Campaign....
Ssq
Thanks, u r now subscribed 2 Salmans Campaign....
A
We are sorry but A is not a valid keyword... SELECT number, carrier FROM message; number
15551234567
5551234567
15557654321
5557654321
15551234567
5551234567
15715555555
15715555555
15715555555
15715555555 carrier
cingular
cingular
cingular
cingular
cingular
cingular
verizon
verizon
verizon
verizon You can specify just the fields you wish to see SELECT number, carrier FROM message
WHERE carrier="cingular"; number
15551234567
5551234567
15557654321
5557654321
15551234567
5551234567 carrier
cingular
cingular
cingular
cingular
cingular
cingular You can filter the rows by using the WHERE clause

Note the quotes around "cingular". This is to indicate you are writing a string date_sent
2011-06-06 14:37:41
2011-06-06 14:37:41
2011-06-06 14:37:51
2011-06-06 14:37:51
2011-06-06 14:39:18
2011-06-06 14:39:18 shortcode
99222
99222
99222
99222
99222
99222 number
15551234567
5551234567
15715555555
15715555555
15715555555
15715555555 carrier
cingular
cingular
verizon
verizon
verizon
verizon direction
0
1
0
1
0
1 message
Ssq
U have already subscribed 2 Salmans Campaign....
Ssq
Thanks, u r now subscribed 2 Salmans Campaign....
A
We are sorry but A is not a valid keyword... SELECT * FROM message WHERE date_sent > "2011-06-06 14:37:30"; Selects all rows with the field date_sent after 2:37pm on the 6th June SELECT number, carrier FROM message
WHERE carrier="cingular" AND direction=0; number
15551234567
15557654321
15551234567 carrier
cingular
cingular
cingular shortcode="99222" carrier="cingular" WHERE shortcode="99222" AND carrier="cingular" shortcode="99222" carrier="cingular" WHERE shortcode="99222" OR carrier="cingular" shortcode="99222" carrier="cingular" WHERE shortcode="99222" AND carrier <> "cingular" Operators
= Equals
<> Not equals
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal NOT by Andrew Brampton (2ergo) date_sent
2011-06-06 14:17:05
2011-06-06 14:17:06
2011-06-06 14:37:41
2011-06-06 14:37:41 shortcode
99222
99222
99222
99222 number
15551234567
5551234567
15551234567
5551234567 carrier
cingular
cingular
cingular
cingular direction
0
1
0
1 message
Ssq
Thanks, u r now subscribed 2 Salmans Campaign....
Ssq
U have already subscribed 2 Salmans Campaign.... SELECT * FROM message
WHERE number="15551234567" or number="5551234567" date_sent
2011-06-06 14:17:05
2011-06-06 14:17:06
2011-06-06 14:37:11
2011-06-06 14:37:12
2011-06-06 14:37:41
2011-06-06 14:37:41
2011-06-06 14:37:51
2011-06-06 14:37:51
2011-06-06 14:39:18
2011-06-06 14:39:18 shortcode
99222
99222
99222
99222
99222
99222
99222
99222
99222
99222 number
15551234567
5551234567
15557654321
5557654321
15551234567
5551234567
15715555555
15715555555
15715555555
15715555555 carrier
cingular
cingular
cingular
cingular
cingular
cingular
verizon
verizon
verizon
verizon direction
0
1
0
1
0
1
0
1
0
1 message
Ssq
Thanks, u r now subscribed 2 Salmans Campaign....
Sleep uv
We are sorry but SLEEP UV is not a valid keyword....
Ssq
U have already subscribed 2 Salmans Campaign....
Ssq
Thanks, u r now subscribed 2 Salmans Campaign....
A
We are sorry but A is not a valid keyword... SELECT * FROM message
WHERE date_sent BETWEEN
"2011-06-06 00:00:00" AND "2011-06-06 23:59:59" date_sent
2011-06-06 14:17:06
2011-06-06 14:37:41
2011-06-06 14:37:51 shortcode
99222
99222
99222 number
5551234567
5551234567
15715555555 carrier
cingular
cingular
verizon direction
1
1
1 message
Thanks, u r now subscribed 2 Salmans Campaign....
U have already subscribed 2 Salmans Campaign....
Thanks, u r now subscribed 2 Salmans Campaign.... SELECT * FROM message
WHERE message LIKE "%Salman%" date_sent
2011-06-06 14:17:05
2011-06-06 14:17:06
2011-06-06 14:37:11
2011-06-06 14:37:12
2011-06-06 14:37:41 shortcode
99222
99222
99222
99222
99222 number
15551234567
5551234567
15557654321
5557654321
15551234567 carrier
cingular
cingular
cingular
cingular
cingular direction
0
1
0
1
0 message
Ssq
Thanks, u r now subscribed 2 Salmans Campaign....
Sleep uv
We are sorry but SLEEP UV is not a valid keyword....
Ssq SELECT * FROM message LIMIT 5; date_sent
2011-06-06 14:39:18
2011-06-06 14:39:18
2011-06-06 14:37:51
2011-06-06 14:37:51
2011-06-06 14:37:41
2011-06-06 14:37:41
2011-06-06 14:37:12
2011-06-06 14:37:11
2011-06-06 14:17:06
2011-06-06 14:17:05 shortcode
99222
99222
99222
99222
99222
99222
99222
99222
99222
99222 number
15715555555
15715555555
15715555555
15715555555
15551234567
5551234567
5557654321
15557654321
5551234567
15551234567 carrier
verizon
verizon
verizon
verizon
cingular
cingular
cingular
cingular
cingular
cingular direction
0
1
0
1
0
1
0
1
0
1 message
A
We are sorry but A is not a valid keyword...
Ssq
Thanks, u r now subscribed 2 Salmans Campaign...
Ssq
U have already subscribed 2 Salmans Campaign...
We are sorry but SLEEP UV is not a valid keyword...
Sleep uv
Thanks, u r now subscribed 2 Salmans Campaign...
Ssq SELECT * FROM message
ORDER BY date_sent DESC carrier
cingular
verizon count(*)
6
4 SELECT carrier, count(*) FROM message
GROUP BY carrier; SELECT number, carrier FROM message
WHERE number IN("15551234567", "5551234567") ASC - Ascending
DESC - Descending Aggregation Function
count(*) Number in each group
avg(*) Average value for each group
sum(*) Sum for each group carrier
verizon
cingular count(*)
4
6 SELECT carrier, count(*)
FROM message
WHERE shortcode="99222"
GROUP BY carrier
ORDER BY carrier DESC; The End
SQL For Project Managers
(and for those who are curious)

Next Time:
How to INSERT data
How to UPDATE data

More Information:
http://www.w3schools.com/sql/default.asp
http://dev.mysql.com/doc/refman/5.1/en/select.html by Andrew Brampton (2ergo) count(*)
3
3
2
2 SELECT carrier, direction, count(*) FROM message
GROUP BY carrier, direction; carrier
cingular
cingular
verizon
verzion direction
0
1
0
1
Full transcript