Level 3 Coc Project

Download as docx, pdf, or txt
Download as docx, pdf, or txt
You are on page 1of 4

Project 1

Task 1:

Designing a database for the SQL server

You are administrator in soft company and assigned to create a database the following information about the
company customer and their orders.

1. Create a database named sales using SQL server 2008.


2. Create the following table in sales database using the information given
Table 1: customer

Attribute Type Size


Cust_ID Varchar 12
Firstname Char 20
Lastname Char 20
City Varchar 30
Industerytype char 20

Table: salesperson

Attribute Type Size Additional information


SP_ID
Name
Gender
Salary

Table orders

Attribute Type Size


Number Varchar 10
Order_date Datetime
SP_ID Varchar 10
Cust_ID Varcahr 20
Amount Money
3. set cust_ID for customer, SP_ID for salesperson and Number for Order table as a primary key
4. create a relationship for the three table

SP_ID Name gender salary


Cust_ID fname lname city industrytype

Relationship (1:M) SP_ID( FK) Relationship (1:M) cust_id (FK)


NUMBER Order_id SP_ID CUST_ID AMOUNT
5. Add a new column named “Age” as type int to salesperson table .an age should between 22 and 50.
6. Insert the following records in to the respective table.

Salesperson

SP_ID Name Gender Salary Age


101 Hailu Male 1400 53
102 Aster Femal;e 2400 47
103 Azeb Female 2000 38
104 Muluken Male 2500 31
105 Tigist Femal 1500 59
106 Mesert Female 2800 26

Customer

Cust_id fmane lname city Industrytype


201 Hailu Teshome Bahirdar P
202 Aster Abebe Gonder P
203 Azeb Belete Markos H
204 Richard Bale Dessie H

Orders

number Order_date SP_ID CUST_ID AMOUNT


1 05/2/2000 102 201 640
2 01/30/2000 105 204 1200
3 07/14/2000 101 204 560
4 01/29/2000 102 203 2500
5 02/03/2000 104 202 800
6 03/02/2000 104 202 920
7 05/06/2000 104 204 350
Task 2: Develop queries
7. Write SQL statement that display the named of all female salespeople who earneasalary of 2400 or
more and save the result set by the name more salary in my document folder
Select female from salespeople where salary>2400
8. Design SQL statement that display the names of all salespeople that do not have any order and save
the result set by the name noorders in local D:
Select *from salespeople any order
9. Design SQL statement that displays name,SP_ID, Gander and salary the salepeople that have 2 or
more order and save the result set in your flesh disk by the name moreorders.
10. Develop SQL statement that remove all customer who live in ‘dessie’ from the customer table
11. Design SQL statement to take backup for your sales database and save the backup by the name sales
backup in your flash disk
12. Assume that the sales database was failed accidentally .design SQL statement that recovery the failed
database into earlier satatfrom the backup

Database level –III COCsample exam.


Project 2:
Task: 1
Design ERD for soft company database
1.Requirements for the soft companyuper database
- The company is organized into departments .each department has a unique name and a uiniqe number
- The database needs to store each employee’sID,Name,sex,Age ,salary ,supervision ID, and depname.
- An Employee is assigned to work for only one department ,but the department may contin one or more
employee .the database needs to keep track of the direct supervision of each employee .
- Then database went to keep track of dependant of each employee for insurance purposes .the system also needs
to keep each dependant‘sFname ,sex, birthdates,empid and relationship to the employee. each employee may
have zero more dependant .but each dependant belong to an employee .

2.Design ERD for ICT department database

- a professor teaches zero, one or many classes and a class is taught by one professor
- a course may generate zero, one or many classes and a class comes from one course
- a class is held in one room but a room has many classes
Task :2

1. Design the Pseudocode that sums all the even numbers between 1 and 20 inclusive and then displays the sum.
It uses a repeat loop and contains a null else within the repeat loop.
Start
Sum = 0 
count = 1 
REPEAT 
  IF count is even THEN sum = sum + count 
  count = count + 1 
UNTIL count > 20 
DISPLAY sum

2. The following pseudo code describes an algorithm which will accept number from the keyboard and .

Calculate the sum of n numbers and design the corresponding flowchart.

StartSum = 0Display “Input value n”Input nFor (I = 1, n, 5)Input a valueSum = sum + valueIncrement
ENDFOROutput sum

Stop

3. The following pseudo code describes an algorithm which will accept student result in number from the keyboard and
returns ’satisfactory’ if the result is greater than or equal to 50.otherwise ‘not satisfactory’ will be returned.

Start

Use the variable result as type float

Display “Input the result”

If(result>=50)

Display “satisfactory”

End if

stop

You might also like