SECTION – A : QUESTIONS
Base Table: CUSTOMER
CID CName Gender SID Area
1001 R SHARMA FEMALE 101 NORTH
1002 M R TIWARY MALE 102 SOUTH
1003 M K KHAN MALE 103 EAST
1004 A K SINGH MALE 102 EAST
1005 S SEN FEMALE 101 WEST
1006 R DUBEY MALE 104 NORTH
1007 M AGARWAL FEMALE 104 NORTH
1008 S DAS FEMALE 103 SOUTH
1009 R K PATIL MALE 102 NORTH
1010 N KRISHNA MURTY MALE 102 SOUTH
(i) The company wants to store details of its customers in a new table
named CUSTOMER.
Each customer must have a unique identification number, and their
name must never be left blank.
The system should store the gender of the customer and also record
the salesman ID (SID) who handles the customer.
A customer can be assigned only to a salesman whose ID is a positive
value.
The geographical area of the customer should automatically be stored
as 'NORTH' if no area is provided.
Based on the above requirements, write the SQL command to
create the table CUSTOMER.
(ii) Write SQL commands to add the following constraints to the table
CUSTOMER:
a. Add a UNIQUE constraint on column CName.
b. Add a CHECK constraint so that Gender can be only
'MALE' or 'FEMALE'.
(iii) Write an SQL command to modify the column Area to increase its size
from VARCHAR(20) to VARCHAR(30).
(iv) Write an SQL command to add a new column Contact of type
CHAR(10) to the table CUSTOMER.
(v) Write an SQL command to delete the column SID from the table
CUSTOMER.
(vi) Write an SQL command to change the table CUSTOMER to CLIENT.
(vii) Write an SQL command to drop the table CUSTOMER permanently from
the database.
(viii) Write an SQL command to add PRIMARY KEY to column CID if it
was not already added.
(ix) Write SQL command(s) to change the data type of column CName from
VARCHAR(30) to VARCHAR(50).
(i) Create table CUSTOMER
CREATE TABLE CUSTOMER (
CID INT PRIMARY KEY,
CName VARCHAR(30) NOT NULL,
Gender VARCHAR(10),
SID INT CHECK (SID > 0),
Area VARCHAR(20) DEFAULT 'NORTH'
);
(ii) Add constraints (without ADD CONSTRAINT)
a. UNIQUE on CName
ALTER TABLE CUSTOMER
ADD Cname varchar(20) UNIQUE;
b. CHECK on Gender
ALTER TABLE CUSTOMER
MODIFY Gender VARCHAR(10) CHECK (Gender IN ('MALE', 'FEMALE'));
or
ALTER TABLE CUSTOMER
ADD CHECK (Gender IN ('MALE', 'FEMALE'));
(iii) Modify size of Area from VARCHAR(20) to VARCHAR(30)
ALTER TABLE CUSTOMER
MODIFY Area VARCHAR(30);
(iv) Add new column Contact of type CHAR(10)
ALTER TABLE CUSTOMER
ADD Contact CHAR(10);
(v) Delete the column SID from CUSTOMER
ALTER TABLE CUSTOMER
DROP COLUMN SID;
(vi) Change table name CUSTOMER to CLIENT
RENAME TABLE CUSTOMER TO CLIENT;
(or)
ALTER TABLE CUSTOMER
RENAME TO CLIENT;
(vii) Drop the table CUSTOMER permanently
DROP TABLE CUSTOMER;
(viii) Create a copy of CUSTOMER with structure only (no rows)
Using LIKE (MySQL style):
CREATE TABLE CUSTOMER_COPY
LIKE CUSTOMER;
Using AS SELECT (general method):
CREATE TABLE CUSTOMER_COPY AS
SELECT * FROM CUSTOMER
WHERE 1 = 2;
(ix) Add PRIMARY KEY to CID (if not already added)
ALTER TABLE CUSTOMER
ADD PRIMARY KEY (CID);
Or
Alter table customer modify CID varchar(20) primary key;
(x) Change data type of CName from VARCHAR(30) to VARCHAR(50)
ALTER TABLE CUSTOMER
MODIFY CName VARCHAR(50);