2009-nov-2

November 2, 2009 Notes

SQL scripts - .sql

Comments are ala shell and perl - #

Homework header

# Mi appellido
# mxhunter
# Assignment1
# November 5, 2009

# Question 1
select * from foo

# Question 2

Combining Tales

Cross Product

SELECT * FROM Foo, Bar
;

mxn is the dimension of the cross product coming from the # of rows in Foo and Bar resp.

Join

?? Using is not standard ??
?? Natural Inner ??

Row based results -> row objects

Assignment 1

Day of Week

select * from Classes where 
(
    MondaySchedule    = 1 
and TuesdaySchedule   = 0
and WednesdaySchedule = 1 
and ThursdaySchedule  = 0
and FridaySchedule    = 0
and SaturdaySchedule  = 0
)
or
(
    MondaySchedule    = 0
and TuesdaySchedule   = 1
and WednesdaySchedule = 0
and ThursdaySchedule  = 1
and FridaySchedule    = 0
and SaturdaySchedule  = 0
)
;

Not available Monday 1 - 2:30pm

SELECT * 
FROM Classes as C
WHERE MondaySchedule = 0
   OR
(
MondaySchedule = 1
  AND 
  (hour(StartTime)*60 + minute(StartTime) > 14*60 + 30
    OR
   hour(StartTime)*60 + minute(StartTime) + duration < 13*60
  )
)
;

Duration

select duration as "class duration in minutes", count(*) as "number of classes" 
from Classes
group by duration
;

duration	count(*)
50	51
70	1
80	5
110	14
140	3
170	1
240	1

Classes starting after 2:30pm = 14:30 = 14*3600 + 30*60

Searching on ‘algorithm’ in Subject

Tables:

• Subjects
• Classes

SELECT count(*) 
FROM Classes as classes
JOIN Subjects as subjects
ON subjects.SubjectID=classes.SubjectID
Where subjects.SubjectName LIKE '%algorithm%'
   Or subjects.SubjectDescription LIKE '%algorithm%'
;

Restrict to columns returned:

SELECT ClassID, subjects.SubjectID, ClassRoomID, Credits, StartTime, Duration, SubjectName  
FROM Classes as classes 
JOIN Subjects as subjects 
  ON subjects.SubjectID=classes.SubjectID 
Where subjects.SubjectName LIKE '%algorithm%'    
Or subjects.SubjectDescription LIKE '%algorithm%';

Resultat

ClassID	SubjectID	ClassRoomID	Credits	StartTime	Duration	SubjectName
2431	27	3322	5	09:00:00	50	Programming in BASIC
2451	27	1639	5	13:00:00	50	Programming in BASIC

List Categories and Number of Each

  select CategoryID, count(*) 
    from Subjects
group by CategoryID
order by CategoryID
;

ECO, HIS and MAT classes

select * from Classes as C 
  join Subjects as S 
    on S.SubjectID = C.SubjectID
 where S.CategoryID IN ('ECO', 'HIS', 'MAT')
;

Classes at most 60 minutes long

SELECT * FROM Classes as C
WHERE duration <= 60
;

Saturday or Monday Morning Classes

SELECT * FROM Classes
WHERE SaturdaySchedule = 1
OR
(MondaySchedule = 1 
 AND hour(StartTime)*60 + minute(StartTime) < 12*60
)
;

NOT Saturday or Monday Morning Classes

SELECT * FROM Classes
WHERE SaturdaySchedule = 0
AND
(
MondaySchedule = 0
OR
  (
   MondaySchedule = 1 AND hour(StartTime)*60 +    
   minute(StartTime) >= 12*60
  )
)
;

101 Subjects

SELECT SubjectName, SubjectCode 
  FROM Subjects 
 WHERE SubjectCode LIKE '%101%';

My tags:

 

Popular tags: