Lab Report 05
Submitted To:
Engr. Shahid Ali Bhutta
Submitted By:
Muhammad Ali
22-CP-57
DBMS Lab
Computer Engineering Department
University of Engineering & Technology, Taxila
Task 01:
Consider the following table:
A. Write an SQL statement to convert the above table into the following table.
B. Write SQL statement(s) to change “Birth_Date” to “Age” with data type Integer.
C. Create an Index on the “Customer” table using “First_Name” and “Age”.
Code:
create database LAB_05
use LAB_05
--Task_01
CREATE TABLE Customer(
First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(50),
Birth_Date datetime
);
select *from Customer;
Output:
(A)
Code:
--Task_02
ALTER TABLE Customer
ADD Gender char(1);
select *from Customer;
Output:
(B)
Code:
--Task_03
ALTER TABLE Customer
ALTER COLUMN Birth_Date VARCHAR(50);
ALTER TABLE Customer
ALTER COLUMN Birth_Date INT;
EXEC sp_rename 'Customer.Birth_Date' , 'Age';
select *from Customer;
Output:
(C)
Code:
use LAB_05
--Task_04
CREATE INDEX MyIndex
ON Customer(First_Name , Age);
select *from Customer
Output:
Task 02:
Code:
use LAB_03
--Task_02
CREATE TABLE Product(
ProductID int,
ProductName varchar(40),
SupplierID int,
CategoryID int,
Unit varchar (40),
Price float
)
INSERT INTO Product
VALUES (1, 'Chais' , 1,1, '10 boxes x 20 bags',18)
INSERT INTO Product
VALUES (2, 'Chang' , 1,1, '24-12 oz bottles',19)
INSERT INTO Product
VALUES (3, 'Aniseed Syrup' , 1,2, '12 - 550ml bottles',10)
INSERT INTO Product
VALUES (4, 'Chef Antons Cajun Seasoning' , 1,2, '48-6 oz jars',22)
INSERT INTO Product
VALUES (5, 'Chef Antons Gumbo Mix' , 1,2, '36 boxes',21.35)
ALTER TABLE Product
DROP COLUMN ProductName;
select *from Product;
Output:
1.
2.
Task 03:
Consider the following tables: Student.
Student_ID Student_Name
38214 Ali
54907 Ahsan
66324 Bilal
70542 Naeem
Create the above table by keeping their first columns as primary key. After the creation of the table, solve the following:
A. Write a query to add an attribute, Class to the Student table.
B. Write a query to change the field for Student_Name from 25 characters to 40 characters.
C. Write a query to add another column in the student table with an auto increment field.
D. Write a query to add another column Department in the Student table. The column must not contain any value
other than the values COMPUTER or SOFTWARE.
E. Write a query to change the auto increment field to start from 50.
F. Write a query to remove the student table.
Code:
use LAB_05
--Task_03
CREATE TABLE Students(
Student_ID int PRIMARY KEY,
Student_Name varchar(30) )
INSERT INTO Students VALUES (38214 , 'Ali')
INSERT INTO Students VALUES (54907 , 'Ahsan')
INSERT INTO Students VALUES (66324 , 'Bilal')
INSERT INTO Students VALUES (70542 , 'Naeem')
select *from Students
Output:
(A)
Code:
--Task_03_01
ALTER TABLE Students
ADD Class char(50);
select *from Students
Output:
(B)
Code:
--Task_03_02
ALTER TABLE Students
ALTER COLUMN Student_Name varchar(40);
select *from Students
Output:
(C)
Code:
--Task_03_03
ALTER TABLE Students
ADD Roll_NO int IDENTITY
select *from Students
Output:
(D)
Code:
--Task_03_04
ALTER TABLE Students
ADD Department varchar(50) CONSTRAINT CHK_DEP
CHECK( Department = 'Computer' or Department = 'Software')
select *from Students
Output:
(E)
Code:
--Task_03_05
ALTER TABLE Students
ADD Roll_NO int IDENTITY(50,1)
select *from Students
Output:
(F)
Code:
--Task_03_06
DROP TABLE Students
select *from Students;
Output:
