Wogdi Tvet College
Web Development and Database Administration
Project 4: Create a database named ABC_CAMPANY on SQL server 2008/12 and save on desktop by
your group name
Under ABC_CAMPANY database, create the tables by using the following given information
Table 1: customer
FIELD NAME Type Size Default value Constraint
CID VARCHAR 10 ----- Not null
CNAME CHAR 30 ----- Not null
SEX CHAR 6 Female Male or female
CTYPE CHAR 10
ADDRESS CHAR 12 -------
SALARY MONEY >2000 and 5000
Table 2: product
Field name Type Size Constraint
PID VARCHAR 8 Not null
PNAME VARCHAR 40 Not null
PDATE DATETIME default Getdate()
QUANTITY INT
PRICE MONEY
Table 3: order_details
Field name Type Size Constraint
CID VARCHAR 10 Not null
PID VARCHAR 8 Not null
QUALITY CHAR 12 brand (level-I or level-II)
1. Add the pk on customer table and Add the pk on product table by using alter command
2. show the relationship the above three table by using alter command
Set By Bamlaku Gizachew February 2016 E.C 1
Wogdi Tvet College
Web Development and Database Administration
3. insert the sample records in to the tables as shown:
customer
CID CNAME SEX CTYPE ADDRESS SALARY
C101 Helen Female Private D/markos 4000
C102 Hana Female Trader Bahir dar 2500
C103 Abel Male Private D/markos 3500
C104 Dawit Male Contractor Gondar 4000
C105 Tringo Female Trader Bahir dar 3500
product
order_details Cid pid qty
Pid Pname Pdate Quantity price C101 P001 Level-I
P001 Computer 2014/01/02 150 200 C101 P002 Level-I
P002 Paper 2014/02/21 140 500 C102 P003 Level-I
P003 Printer 2014/01/16 200 400 C103 P001 Level-II
P004 Scanner 2014/03/12 100 1000 C104 P003 Level-II
4. Display the name of all customer name that do not have any order details
5. Retrieve names of product that have 2 or more order details
6. Display all product name that product date is current date
7. Display the name of all customer name that have any order details
8. Find the name of product whose name start with ‘P’
9. Display all customer name in upper case
10. Display ctype, address and count for which similar address count would be >= 2 for customer table
11. Display record for the same total salary would be more than or equal to 3000 for customer table
12. Delete the constraint of salary for customer table
13. Updates salary by 25% times in customer table for all the customers whose salary is >=3000
14. Display salary and count for which similar salary count would be more than one for customer table
15. Retrieve product name, quantity, price and customer name
16. Display customer id, customer name, pid, product name and price of the product is more than 300
17. Delete the record of product from product table whose price is less than or equal to 400 birrs
18. Sort customer in descending order of their name and display only the top three customer record
19. Rename the database ABC_CAMPANY to ABC
20. Write SQL statement that create a backup for ABC database in local disk (d:)
21. Assume ABC database was dropped accidentally. Write SQL query that restore dropped database from
the backup.
Set By Bamlaku Gizachew February 2016 E.C 2
