SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help me Pls
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ghjkla
Starting Member

1 Posts

Posted - 05/20/2013 :  09:39:13  Show Profile  Reply with Quote
Create 3 tables and insert data as follows
Students (Student list)
StudentID (int) Name (VarChar(50)) Age (Tinyint) stGender (bit)
1 Joe Hart 25 1
2 Colin Doyle 20 1
3 Paul Robinson 16 Null
4 Luis Garcia Paulson 17 0
5 Ben Foster 30 1

Projects (Project list)
PID(int) PName (Varchar (50)) Cost(float) Type (varchar (10))
1 NewYork Bridge 100 Null
2 Tenda Road 60 Null
3 Google Road 200 Null
4 The Star Bridge 50 Null

StudentProject
(Describe Student Work in Project) Example: The first row of following table denote ‘Joe Hart work in The Star Bridge project on 15/05/09 for 3 month’
StudentID (int) PID (int) WorkDate (datetime) Duration (int)
1 4 15/05/09 3
2 2 14/05/09 5
2 3 20/05/09 6
2 1 16/05/09 4
3 1 16/05/09 6
3 4 19/05/09 7
4 4 21/05/09 8
Constraint
a. Apply the Check Constraint to the Age column of Students table so that the Age is greater than 15 and less than 33.
b. Apply the Primary Key Constraint for the StudentID for Students table, PID for Projects, (StudentID, PID) for StudentProject table
c. Apply the Default Constraint to the Duration column of StudentProject so that the Duration is 0 if not input
d. Apply the Foreign Key Constraint on StudentProject for StudentID column rference Students (StudentID), for PID column references Project (PID)
2. Update the Type for all prodect of the Projects table in case:
- Type= ‘Education’ if Cost <80
- Type=’Normal’ if Cost between 80 and 150
- Type=’Government’ if Cost >150
After updated

PID Pname Cost Type
1 1 NewYork Bridge 100 Normal
2 2 Tenda Road 60 Education
3 3 Google Road 200 Govemment
4 4 The Star Bridge 50 Education

3. Display a list of students worked more than one project

Student Name Number Of Project Works
1 Colin Doyle 3
2 Paul Robinson 2

4. Display a list of students with a total time (Sum of the Duration) working in projects is the largest

Student Name Time Works
Colin Doyle 15

5. Display a list of students which Student Name like ‘Paul’ and working in “The Star Bridge” Project
Student Name Project Name
Paul Robison The Star Bridge
Luis Garcia Paulson The Star Bridge

6. Display a list of students that do not work in any project
StudentID Name Age stGender
5 Ben Foster 30 1

7.Create a view named ‘vwStuentProject’ that list all information of Students table and Projects with (Student Name, Project Name, WordDate, Duration) order by StudentName
Student Name Project Name WorkDate Duration
Colin Doyle NewYork Bridge 16/05/2009 4
Colin Doyle Tenda Road 14/05/2009 5
Colin Doyle Google Road 20/05/2009 6
Joe Hart The Star Bridge 15/05/2009 3
Luis Garcia Paulson The Star Bridge 21/05/2009 8
Paul Robinson NewYork Bridge 16/05/2009 6
Paul Robinson The Star Bridge 19/05/2009 7

8. Create an index named ‘ixStudentName on the [StudentName] column and [Project Name] Column of the ‘vwStudentProject’ view ( You can remove “order by”, “top”... in vwStudentProject).
9. Create a sored procedure named ‘spworkin’ that accept the Student Name as parameter
- If the student name available on Students table, display information about the student and worked project as following:
Example: exec spWorkln ‘Colin Doyle’
Student Name Project Name WorkDate
Colin Doyle NewYork Bridge 16/05/2009
Colin Doyle Tenda Road 14/05/2009
Colin Doyle Google Road 20/05/2009
- If ‘any passed as a parameter for student name, display information about all student and worked project as above.
10. Create a update Trigger named ‘ tgUpdateTrig’ on the Students table this trigger has ensure when StudentID on Students table is updated, the StudentID on StudentProject will be updated
11. Create a stored procedure named ‘spDropOut’ that accept the Project Name as parameter. If the project name available on Projects table it will drop all information of.... project from Project database

khtan
In (Som, Ni, Yak)

Singapore
17645 Posts

Posted - 05/20/2013 :  22:37:25  Show Profile  Reply with Quote
You are not getting any response cause we don't do homework here.



KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000