Help me Pls - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help me Pls

Author

Topic

ghjkla
Starting Member

1 Post

Posted - 2013-05-20 : 09:39:13

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)

17689 Posts

Posted - 2013-05-20 : 22:37:25

You are not getting any response cause we don't do homework here.

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources