Important Announcements for all students including short course students



Submission schedule has been updated in an attached file of lab project description.



Class Quiz-3: Lecture slides 8 and 9. (covered until this week sat., up to Cardinalities 1:1, 1:N only)


Following are the class HW files have been received, if for some reason your file is not includes this means your email may not be received. Please inform me ASAP with sent email date so that I can search email list again:



If students are from different sections then file name contains any one section. Please include yours ID inside the file. Do not use any drop or alter statement etc. except Insert/Select. In question-2, do not use Direct function means do not use Date related method. Students are still using and they will get zero marks. You can limit your answer up to 4 or 5 numeric letters.

Use of SET, COLUMN and BREAK statements are allowed in Q4.



As Data modeling contents are not yet covered so phases of Lab project are cancelled and will be announced with different schedule.

Class Quiz for sections 3, 4 will be conducted on Wed. 03, November 2004. Material includes Data Modeling covered until Sat. 30, October 2004.



27-OCT-2004(IMPORTANT ICS334-52, 53 LABS)

Details (OrdId, ProdId, Price)

Products (ProdId, ProdDesc, Price)


--change the column name Prodid to prodNo from

--tables Details and Product

--also change datatype Number to Number(5)


--Hint: disable or drop related contraints

--use alter table command

--put back constraints again

--create file InsertTable.sql to create around 4 records

--in every table. Test all scripts carefully using spool

--Complete it until next lab, and marks will be assigned

--during the next lab.


Final requirements:

SQL>spool C:\log.txt





SQL>spool off


please use

prompt create table detail. you can see demobld.sql



Attendance Status from 11 Sep - 20 Oct 2004 (total number of class lectures 19)

ID††††††††††††††††††† Name†††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††† Number of Lec Absence

202397††††††††††† JAN, IBRAHIM MUHAMMAD SIR5

203072††††††††††† WADU, HUSAIN SAHEL HUSAIN7

205821††††††††††† AL-OGAILI, WAJDI ABDALLAH†††† 5

207091††††††††††† AL-SHAKHS, MUHAMMAD ADNAN††††††† 5

213159††††††††††† HAMDAN, AHMAD ABDUL-QADER†††††††† 4

214031††††††††††† AL-SAYED, ABDULLAH MUHAMM††††††††† 6

994291††††††††††† AL-RASHID, ALI NOUR ABDUL†††† 8

202561††††††††††† AL-DARWEESH, ABDALLAH IBR5

205273††††††††††† AL-SHAREEF, MOAADH THAMER††††††††††† 6

208614††††††††††† NASER, MALIK ABDUL-MUNEM5

208625††††††††††† ZAITONAH, MUHAMMAD HANI G†††††††††† 5

212687††††††††††† AL-GUMAIZ, ALI MUHAMMAD A 5



208625††††††††††† ZAITONAH, MUHAMMAD HANI G†††††††††† 3

(Please make convenient to discuss with me or do not miss the class, otherwise warning will be issued)



Results of Lab and classes are posted on link Studentís Results. Please resolve all corrections issue this week.


Class Home Work Groups

Group 1: 226810, 217289

Group 2: 215467, 212859

Group 3: 220492, 994260

Group 4: 214107, 989239

Group 5: 995230, 200154

Group 6: 996213, 210131

Group 7: 214271, 214737

Group 8: 982498, 214031

Group 9: 226820, 226836

Group 10:202397, 205821

Group 11:205821, 202107

Group 12:994291, 207091

Group 13:226854, 226806

Group 14:215193, 211837

Group 15:981037, 202496

Group 16:216287, 214513

Group 17:202561, 205273

Group 18:212391, 212793

Group 19:226842, 215083

Group 20:212049, 212109

Group 21:213259, 208625

Group 22:215643,††††††† (Please contact me why there is one student)

Group 23:201597, 201749

Group 24:212819, 212389

Group 25:200154, 995230

Group 26:216015, 214041

Group 27:208601, 208697

Group 28:216241, 216151

Group 29:208625, 208614

Group 30:212687, 206841

Group 31:214387, 205794

Group 32:217059, 215759





203465 (LG-01)




213259 (LG-02)




208702 (LG-03)




216151 (LG-04)




203072 (LG-05)




998147 (LG-06)




214107 (LG-07)




208614 (LG-08)





976150 (LG-09)





LG-10:991409, 226792, 202496

LG-11:202561, 217019, 220492

LG-12:215083, 990428, 995230

LG-13:989234, 215241, 217059 (Two students cannot be from the same group)

LG-14:215759, 200154







Due to studentís genuine reason, it is decided to extend submission date of class HW1 to 01-NOV-2004 (during class timings). Because of vacation it is advice to students that they must submit HW as per requirements given on front page of HW. No late submission is accepted. HW will be evaluated and marked after vacations.



Example of correlated query

List of employees who have same job title and same manager (supervisor) as employee number 7369.



select *

from emp

where (job,mgr) =

(select job,mgr

from emp

where empno = 7369);


Alternate sol:

select e.*

from emp e

where e.job =

(select e1.job

from emp e1

where e1.mgr = e.mgr

and e1.empno=7369);


Alternate sol:

select e.*

from emp e

where exists

††† (select e1.job, e1.mgr

††† from emp e1

††† where e1.mgr = e.mgr

††† and e1.empno=7369)




Quiz-1: Sat and Wed in week starting 16-OCT-2004 for ICS-334-52, 53 (SQL quick work book and Lecture notes covered until 13-OCT-2004.).


Help Session (2 alternate) attend either of them on SUN, MON this week at 8:30pm-10:00pm 22/124-6


13-OCT-2004 (Class Home Work, please follow up the following issues regarding HW1)

In question no. 1 in the homework


create table Student

(StuId CHAR(9),




PostalCode VARCHAR2(6),

Constraint PK_Stuid PRIMARY KEY (StuId)






create table MM_Student



PostalCode VARCHAR2(5),

Constraint PK_Stuid PRIMARY KEY (StuId)




When I tried to create the second table

Oracle said that there is a problem in the primary key of the second table

I changed it to (PK_MM_Stuid) and it worked properly


The second problem is that when I tried to insert the addresses from Student table into MM_Student

Oracle said the value is too long


When I changed  (PostalCode VARCHAR2(5))  to (PostalCode VARCHAR2(6)) it worked properly


I think this record is the one making this problem

('000523135','ALI Sadaq Muhammad Samir','50 Young street','Toronto','M9V4Y2')


I hope you will replay to my question and tell me weather I am right or wrong




Link on student page is updated (ICS334 Misc Sample R.Alg/Constraints). Please download again.

ppt slides are updated to around 16MB in size. Please download again to use


15-SEP-2004 (ICS334)

Class Quiz-1 will be conducted on Wed 22-09-2004 (covered material from first lecture until Monday 20-09-2004)


13-SEP-2004 (ICS334)


Exam-1 time has been changed to 8:30pm-10:00pm (05/203) because of Ramadan

Flash player slides will no longer be used, please use only ppt slides as lecture slides