LAB - 1
1. You are given a responsibility to design a database for an online sale company. In the initial phase
you are required to create the following tables using appropriate data type for each column.
a. Table name CUSTOMER
Column names:
CustomerID
CustomerName
Address
City
PostalCode
Country
b. Table name PRODUCT
Column names:
ProdID BrandID ColorID Size Dimension is_available
c. Table name EMPLOYEE
Column names:
FNAME LNAME EMPID DOB ADDRESS SALARY DNO JOININGDATE
d. Table name Color
Column names:
ColorID ColorName
create table CUSTOMER(
CustomerId nvarchar(15),
CustomerName nvarchar(25),
Address nvarchar(35),
City nvarchar(15),
PostalCode int,
Country nvarchar(15)
);
create table PRODUCT(
ProdId nvarchar(15),
BrandId nvarchar(15),
ColourId nvarchar(15),
Size nvarchar(15),
Dimension nvarchar(15),
is_available nvarchar(15)
);
create table EMPLOYEE(
FNAME NVARCHAR(15),
LNAME NVARCHAR(15),
EMPID NVARCHAR(15),
DOB DATE,
ADDRESS NVARCHAR(35),
SALARY INT,
DNO INT,
JOININGDATE DATE
);
create table color(
ColorId nvarchar(15),
ColorName nvarchar(15)
);
2. After creation of the tables, it was observed that some of the details are missing from the tables.
Make the following changes to the above created table
a. Insert a new Column ContactNumber in table CUSTOMER
b. Assuming the online sales is considering no classification into department remove the column
DNO from EMPLOYEE
c. Insert a new Column Color in table PRODUCT
alter table customer add ContactNumber int;
alter table employee drop COLUMN DNO;
ALTER TABLE PRODUCT ADD COLOR NVARCHAR(15);
3. At later stage of database management it was decided that Color table was not essential and can be
removed from the database. Write a query to remove Color table
Drop table color;
4.
Insert the following records in the created tables
a.
CustomerID
AZMC_00001
AZMC_00002
AZMC_00014
CustomerName
Amar
Alex
Aleen
AZMC_00341
Arun
AZMC_00145
Raj
AZMC_00162
Bob
Address
NIT Delhi Narela
145 Downtown
New York
23-B Midtown
Boston
123 street lane,
Campus Town
Florida
56 Railway Line
Colony
Lucknow
78 Street hill station
Utha
City
New Delhi
New York
PostalCode
110040
Country
India
USA
Boston
02108
USA
Florida
32003
USA
Lucknow
India
227105
Utah
84101
USA
INSERT INTO CUSTOMER VALUES ('AZMC_00001','Amar','NIT Delhi
Narela','New Delhi','110040','India','99999999');
INSERT INTO CUSTOMER VALUES ('AZMC_00002','Alex','145 Downtown New
York','New York',520145,'USA',98989898);
INSERT INTO CUSTOMER VALUES('AZMC_00014','Aleen','23-B Midtown
Boston','Boston',02108,'USA',89898989);
INSERT INTO CUSTOMER VALUES('AZMC_00341','Arun','123 street lane, Campus
Town','Florida',32003,'USA',85859898);
INSERT INTO CUSTOMER VALUES('AZMC_00145','Raj','56 Railway Line Colony
Lucknow','Lucknow',227105,'India',87878787);
INSERT INTO CUSTOMER VALUES('AZMC_00162','Bob','78 Street hill station
Utha','Utah',84101,'USA','69696969');
b.
ProdID
PROD_001x2
PROD_010x2
PROD_231x2
PROD_131x2
PROD_141x2
BrandID
Samsung
LG
Samsung
Sony
Toshiba
ColorID
C_001
C_001
C_255
C_035
C_005
Size
43
24
50
123
24
Dimension
1920 x 1080
1366 x 768
1920 x 1080
3840 x 2160
1280 x 800
is_available
yes
no
yes
no
no
INSERT INTO PRODUCT(prodid,BrandId,ColourId,Size,Dimension,is_available)
VALUES('PROD_001x2','Samsung','C_001','43','1920x1080','yes');
INSERT INTO PRODUCT(prodid,BrandId,ColourId,Size,Dimension,is_available)
VALUES('PROD_010x2','LG','C_001','24','1366 x 768','no');
INSERT INTO PRODUCT(prodid,BrandId,ColourId,Size,Dimension,is_available)
values('PROD_231x2','Samsung','C_255','50','1920 x 1080','yes');
INSERT INTO PRODUCT(prodid,BrandId,ColourId,Size,Dimension,is_available)
values('PROD_131x2','Sony','C_035','123','3840 x 2160','no');
INSERT INTO PRODUCT(prodid,BrandId,ColourId,Size,Dimension,is_available)
values('PROD_141x2','Toshiba','C_005','24','1280 x 800','no');
c.
FNAME
LNAME
EMPID
DOB
ADDRESS
SALARY
DNO
JOININGDATE
Ram
Pankaj
Rahul
Thakur
Makhija
Kumar
Emp_0001
Emp_0011
Emp_0191
12-4-1976
16-09-1983
19-12-1991
42000
16000
25000
D_01
D_11
D_01
10-5-2012
16-12-2010
4-09-2015
Dinesh
Chauhan
Emp_0231
4-05-1987
Moti Nagar
Rohini
Ramesh
Nagar
Rajiv
Chowk
12000
D_25
15-9-2009
insert into employee values('Ram','Thakur','Emp_0001','12-41976','Moti Nagar','42000','10-5-2012');
insert into employee values('Pankaj','Makhija','Emp_0011','12-91983','Rohini','16000','12-8-2010');
insert into employee values('Rahul','Kumar','Emp_0191','12-191991','Ramesh Nagar','25000','4-09-2015');
insert into employee values('Dinesh','Chauhan','Emp_0231','4-051987','Rajiv Chowk','12000','9-15-2009')
5. Write queries to display the following output [Select specific columns from table]
a.
CustomerID
AZMC_00001
AZMC_00002
AZMC_00014
CustomerName
Amar
Alex
Aleen
AZMC_00341
Arun
AZMC_00145
Raj
AZMC_00162
Bob
Address
NIT Delhi Narela
145 Downtown
New York
23-B Midtown
Boston
123 street lane,
Campus Town
Florida
56 Railway Line
Colony
Lucknow
78 Street hill station
Utha
City
New Delhi
New York
PostalCode
110040
Country
India
USA
Boston
02108
USA
Florida
32003
USA
Lucknow
India
227105
Utah
'84101
USA
SELECT CustomerId,CustomerName,Address,City,PostalCode,Country FROM
CUSTOMER;
b.
FNAME
Ram
Pankaj
Rahul
LNAME
Thakur
Makhija
Kumar
EMPID
Emp_0001
Emp_0011
Emp_0191
Dinesh
Chauhan
Emp_0231
ADDRESS
Moti Nagar
Rohini
Ramesh
Nagar
Rajiv
Chowk
DNO
D_01
D_11
D_01
D_25
SELECT fname, lname, empid,address FROM employee;
c.
ProdID
BrandID
PROD_001x2
PROD_010x2
PROD_231x2
PROD_131x2
PROD_141x2
Samsung
LG
Samsung
Sony
Toshiba
select prodid, brandid from product;
d.
EMPID
DOB
JOININGDATE
Emp_0001
Emp_0011
Emp_0191
Emp_0231
12-4-1976
16-09-1983
19-12-1991
4-05-1987
10-5-2012
16-12-2010
4-09-2015
15-9-2009
select empid, dob, joiningdate from employee;
6. Write queries to display the following output [Select specific records from table]
a. Display records of employee whose EMPID is Emp_0191
select * from employee where empid='emp_0191';
b. Display product details whose BrandID is Samsung
select * from product where brandid='samsung';
c. Display Employee name who's residence is in Rajiv Chowk
select * from employee where address='rajiv chowk';
LAB 2
1. On Update (Tables from assignment 1)
a. A customer having id AZMC_00001 made a request for an address change. write a query
for the same, change address to '145 Patel Nagar'
update customer set address='145 Patel Nagar' where cutomerid = 'AZMC_00001'
b. A customer named raj has now moved from Lucknow to Allahabad make appropriate
changes in the database
update customer set address='allahabad',city='allahabad' where( city='lucknow' and
customername='raj')
c. Change the dimension for PROD_010x2 to 1300 x 678
update product set dimension='1300*678' where prodid='prod_010x2'
2. On Delete (Tables from assignment 1)
a. Remove the record for the customer with id = AZMC_00014
delete from CUSTOMER where cutomerid='AZMC_00014'
b. Remove information of customer who lives in New York
DELETE FROM CUSTOMER WHERE CITY='NEW YORK'
c. Remove the product description for Samsung
DELETE FROM PRODUCT WHERE BRANDID='SAMSUNG'
3. Create the following table in the database and answer the following queries
CREATE TABLE EMP(
EID INT,
ENAME NVARCHAR,
AGE INT,
SALARY INT)
CREATE TABLE WORKS (
EID INT,
DID INT,
PCT_TIME INT
)
CREATE TABLE DEPT (
DID INT,
DNAME NVARCHAR(15),
BUDGET INT,
MANAGERID INT)
INSERT INTO EMP VALUES(123,'AMAN',19,100000)
INSERT INTO EMP VALUES(124,'SHASHI',20,200000)
INSERT INTO EMP VALUES(125,'AJAY',64,50000)
INSERT INTO EMP VALUES(126,'ATUL',20,20000)
INSERT INTO EMP VALUES(127,'ALI',20,80000)
INSERT INTO DEPT VALUES(101,'COMPUTER',1000000,501)
INSERT INTO DEPT VALUES(102,'ELECTRICAL',1200000,502)
INSERT INTO DEPT VALUES(103,'MECHANICAL',10000000,503)
INSERT INTO WORKS VALUES(123,101,10)
INSERT INTO WORKS VALUES(123,102,10)
INSERT INTO WORKS VALUES(124,101,10)
INSERT INTO WORKS VALUES(125,105,10)
INSERT INTO WORKS VALUES(126,102,10)
INSERT INTO WORKS VALUES(127,101,10)
