1.
Write SQL query to create a database with a name DBSTUDENROLL
Note: The following tables, views and stored procedures are objects of DBSTUDENROLL
2. Write SQL query to create tblCOURSE table with the following sample data ,select
appropriate datatype and set important properties
CourseId CourseTitle CourseCode CrdtHrs
1 Fundamentals of Programming INSY2022 5
Advanced Computer
2 Programming INSY2031 5
3 Fundamentals of Database Systems INSY2013 5
Introduction to Information
4 Systems and Society INSY2033 4
Introduction to Information Storage &
5 Retrieval INSY3093 4
3. Write SQL query to Create tblSTUDENT table with the following attributes, select
appropriate datatype and set important properties
StudentID , StuName , StuFname , StuGFName ,StuGender , StuDoB , StuTelNo
,StuNationality
Note
Person’s names should contain only A-Z & a-z
Telephone number contains only 0-9, + and - symbols
Age should be between 16 and 80
Student Nationality has Default value ‘Ethiopian’
4. Write SQL query to Create tblENROLLMENT table with the following attributes and
properties ,select appropriate datatype and set important properties
EnrollId
CourseId, which references CourseId from tblCOURSE table
StudentID, which references StudentID from tblSTUDENT table
DateofEnrollment
MidExResult
ProjectResult
FinalExResult
5. Write SQL query to add StuEmail into tblSTUDENT
6. Write SQL query to add a constraint which check StuEmail is written in a format containing @
and . as expected
7. Write SQL query to change data type of StuName from tblSTUDENT table to be
nvarchar(40)
8. Write SQL query to insert the sample data given in question 2 into tblCOURSE table
9. Prepare sample Student data and write SQL query to insert the sample data into tblSTUDENT
table
10. Prepare sample student enrollment data based on records in tblCOURSE and tblSTUDENT
and write SQL query to insert the sample data into tblENROLLMENT table
11. Write SQL query to fetch(get) details of Female students from tblSTUDENT table
12. Write SQL query to fetch details of students whose age is not less than 20 from tblSTUDENT
table
13. Write SQL query to create VStudentEnroll view which contains details of students with
courses they take including Mid,Project and final results
14. Write SQL query to fetch details of students from tblSTUDENT table
a. Whose StuNAME begins with ‘bi’.
b. Whose StuNAME contains ‘bi’.
c. Whose StuNAME ends with ‘bi’.
d. Whose StuNAME begins with ‘bi’ and have exactly 6 characters.
15. Write a stored procedure to fetch(get) details of students from tblSTUDENT whose gender is
given at execution time
16. Write stored procedure to fetch details of students from tblSTUDENT whose age is between a
given age range during execution
17. Write SQL query which contains details of students from tblSTUDENT with calculated Age
18. Write SQL query which contains details of students with courses including Mid,Project and
final results ,calculated TotalResult and calculated Grade based on the following conditions
If TotalResult >= 85 then Grade =’A’
ElseIf TotalResult >=70 and TotalResult<85 then Grade=’B’
ElseIf TotalResult>=55 and TotalResult <70 then Grade =’C’
ElseIf TotalResult >=40 and TotalResult <55 then Grade=’D’
Else
Grade =’F’
19. Write SQL query which displays count of Students Group by Gender
20. Write SQL query which displays count of Students Group by Course Name
21. Write SQL query which Update student name for a given student Id .