Scribd
Upload
5 views2 pages

SQL Project

The document outlines a project for creating a database named ABC_College, including the design of three tables: Student, Course, and Grade_report, with specified fields and constraints. It also includes instructions for adding a new column, inserting data into the tables, and writing various SQL queries for data retrieval and manipulation. Additionally, it provides tasks for backing up and restoring the database, as well as specific queries to filter and display student information based on grades and courses.

Uploaded by

solomonbaye10909
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
5 views2 pages

SQL Project

The document outlines a project for creating a database named ABC_College, including the design of three tables: Student, Course, and Grade_report, with specified fields and constraints. It also includes instructions for adding a new column, inserting data into the tables, and writing various SQL queries for data retrieval and manipulation. Additionally, it provides tasks for backing up and restoring the database, as well as specific queries to filter and display student information based on grades and courses.

Uploaded by

solomonbaye10909
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Web Development and Database Administration

Level III

Basic structured query language Project

1. Create the database name ABC_College


2. Create the following tables
Student

field datatype size constraint


Student_id varchar 10 Primary key
Name char 30 Not null
Sex char 6 default
Birthdate Datetime Not null
Section char 6
Department char 40 Not null

Course

Field Datatype Size constraint


Course_code Varchar 8 Primary key
Course_title Char 40 Not null
Credit Int Not null

Grade_report

Field Datatype Size Constraint


Student_id Varchar 10 Foreign key
Course_id Varchar 8 Foreign key
Grade Char 1 (A+,A,A-,B+,B,B-,c+,C,F)
3. Add new column into student table named “email_Address” with data type char and size 25
4. Insert the following data into tables
Student table

Student_id Name Sex Birthdate Section Department Email_Address


R101 Kiros Male 20/02/80 Room1 Computer science kiros@[Link]
R102 Mulu Female 12/06/78 Room1 Computer science alemu@[Link]
R103 Getac Male 17/01/70 Room2 Electrical gechb@[Link]
hew
R104 Melka Male 10/09/73 Room1 Computer science melkamu@[Link]
mu
R105 seble Female 19/0-1/82 Room2 Electrical Seble_gasha@gmail.c
om
Course table

Course_code Course_title Credit


ICT001 Calculus 80
ICT002 Software 104
Elec003 Electrical 200
Grade_Report table

Student_id Course_code Grade


R101 ICT001 B
R101 ICT002 C
R102 ICT001 A
R103 Elec003 C
R104 ICT001 B
R104 ICT002 A
R105 Elec003 B
5. Query analyzer
A. Write a query to display the student name for all students who score grade “B”.
B. Write a query to display the student id and name for all female students those who score
grade “A’ and who learn in software course.
C. Write a query to display the student for all students those who score grade “A” and who
learn in computer science department they sort by department name descending and name
ascending order.
D. Write a query code in query analyzer to back up the project ABC_college database.
E. Create a query to update the student section for all student with “room4 “ those who score
Grade “A” or “B” and who learn software course.
F. Write a query code in query analyzer to restore the project ABC_college database.
G. Write a query to display the student for all students those who score grades between A and D
and who learn in calculus course.
H. Write a query to display the student_id and name for all student who work in a computer
science department with any student whose name contain a “T”

You might also like