Database Management Systems

Lab 4: SQL Constraints

Dr. Mohsin Dar September 7, 2025

Lab Assignment

Objective: To understand and apply the concept of data constraints in SQL, focusing on Primary Key and Foreign Key constraints.

Tools Required: MySQL Workbench or any SQL client

1. Table Creation with Constraints

1.1 CLIENT_MASTER Table

Column Name Data Type Size Constraints
CLIENTNO VARCHAR 6 Primary Key, First letter must be 'C'
NAME VARCHAR 20
ADDRESS1 VARCHAR 30
ADDRESS2 VARCHAR 30 Not Null
CITY VARCHAR 15
PINCODE INTEGER 8
STATE VARCHAR 15
BALDUE DECIMAL 10,2
CREATE TABLE CLIENT_MASTER ( CLIENTNO VARCHAR(6) PRIMARY KEY CHECK (CLIENTNO LIKE 'C%'), NAME VARCHAR(20), ADDRESS1 VARCHAR(30), ADDRESS2 VARCHAR(30) NOT NULL, CITY VARCHAR(15), PINCODE INT(8), STATE VARCHAR(15), BALDUE DECIMAL(10,2) );

1.2 PRODUCT_MASTER Table

CREATE TABLE PRODUCT_MASTER ( PRODUCTNO VARCHAR(6) PRIMARY KEY CHECK (PRODUCTNO LIKE 'P%'), DESCRIPTION VARCHAR(15) NOT NULL, PROFITPERC DECIMAL(4,2) NOT NULL, UNITMEASURE VARCHAR(10) NOT NULL, QTYONHAND INT(8) NOT NULL, REORDERLVL INT(8) NOT NULL, SELLPRICE DECIMAL(8,2) NOT NULL, COSTPRICE DECIMAL(8,2) NOT NULL );

1.3 SALESMAN_MASTER Table

CREATE TABLE SALESMAN_MASTER ( SALESMANNO VARCHAR(6) PRIMARY KEY CHECK (SALESMANNO LIKE 'S%'), SALESMANNAME VARCHAR(20) NOT NULL, ADDRESS1 VARCHAR(30) NOT NULL, ADDRESS2 VARCHAR(30), CITY VARCHAR(20), PINCODE INT(8), STATE VARCHAR(20), SALAMT DECIMAL(8,2) NOT NULL CHECK (SALAMT > 0), TGTTOGET DECIMAL(6,2) NOT NULL CHECK (TGTTOGET > 0), YTDSALES DOUBLE(6,2), REMARKS VARCHAR(60) );

2. Data Insertion

2.1 Inserting into CLIENT_MASTER

INSERT INTO CLIENT_MASTER VALUES ('C00001', 'Ivan bayross', 'A/14', 'Worli', 'Mumbai', 400054, 'Maharashtra', 15000), ('C00002', 'Mamta Muzumdar', '65', 'Nariman', 'Madras', 780001, 'Tamil Nadu', 0), ('C00003', 'Chhaya Bankar', 'P-7', 'Bandra', 'Mumbai', 400057, 'Maharashtra', 5000), ('C00004', 'Ashwini Joshi', 'A/5', 'Juhu', 'Mumbai', 400001, 'Maharashtra', 2000), ('C00005', 'Hansel Colaco', 'B/2', 'MG Road', 'Bangalore', 560001, 'Karnataka', 0), ('C00006', 'Deepak Sharma', 'D-45', 'Koramangala', 'Bangalore', 560001, 'Karnataka', 0);

2.2 Inserting into PRODUCT_MASTER

INSERT INTO PRODUCT_MASTER VALUES ('P00001', 'T-Shirt', 5, 'Piece', 200, 50, 350, 250), ('P0345', 'Shirts', 6, 'Piece', 150, 50, 500, 350), ('P06734', 'Cotton', 5, 'Piece', 100, 20, 600, 450), ('P07865', 'Jeans', 5, 'Piece', 100, 20, 750, 500), ('P07868', 'Trousers', 2, 'Piece', 150, 50, 850, 550), ('P07885', 'Pull Overs', 2.5, 'Piece', 80, 30, 700, 450), ('P07965', 'Denim Jeans', 4, 'Piece', 100, 40, 350, 250), ('P07975', 'Lycra Tops', 5, 'Piece', 70, 30, 300, 175), ('P08865', 'Skirts', 5, 'Piece', 75, 30, 450, 300);

2.3 Inserting into SALESMAN_MASTER

INSERT INTO SALESMAN_MASTER VALUES ('S00001', 'Aman', 'A/14', 'Worli', 'Mumbai', 400002, 'Maharashtra', 3000, 100, 50, 'Good'), ('S00002', 'Omkar', '65', 'Nariman', 'Mumbai', 400001, 'Maharashtra', 3000, 200, 100, 'Good'), ('S00003', 'Raj', NULL, 'Bandra', 'Mumbai', 400032, 'Maharashtra', 3000, 200, 100, 'Good'), ('S00004', 'Ashish', 'A/5', 'Juhu', 'Mumbai', 400044, 'Maharashtra', 3500, 200, 150, 'Good');

3. Exercises on Retrieving Records

3.1 Find out the names of all the clients.

SELECT NAME FROM CLIENT_MASTER;

3.2 Retrieve the entire contents of the Client_Master table.

SELECT * FROM CLIENT_MASTER;

3.3 Retrieve the list of names, city and the state of all the clients.

SELECT NAME, CITY, STATE FROM CLIENT_MASTER;

3.4 List the various products available from the Product_Master table.

SELECT DESCRIPTION FROM PRODUCT_MASTER;

3.5 List all the clients who are located in Mumbai.

SELECT * FROM CLIENT_MASTER WHERE CITY = 'Mumbai';

3.6 Find the names of salesman who have a salary equal to Rs.3000.

SELECT SALESMANNAME FROM SALESMAN_MASTER WHERE SALAMT = 3000;

4. Exercises on Updating Records

4.1 Change the city of ClientNo 'C00005' to 'Bangalore'.

UPDATE CLIENT_MASTER SET CITY = 'Bangalore' WHERE CLIENTNO = 'C00005';

4.2 Change the BalDue of ClientNo 'C00001' to Rs.1000.

UPDATE CLIENT_MASTER SET BALDUE = 1000 WHERE CLIENTNO = 'C00001';

4.3 Change the cost price of 'Trousers' to Rs.950.00.

UPDATE PRODUCT_MASTER SET COSTPRICE = 950.00 WHERE DESCRIPTION = 'Trousers';

4.4 Change the city of the salesman to Pune.

UPDATE SALESMAN_MASTER SET CITY = 'Pune';

5. Exercises on Deleting Records

5.1 Delete all salesman from the Salesman_Master whose salaries are equal to Rs.3500.

DELETE FROM SALESMAN_MASTER WHERE SALAMT = 3500;

5.2 Delete all products from Product_Master where the quantity on hand is equal to 100.

DELETE FROM PRODUCT_MASTER WHERE QTYONHAND = 100;

5.3 Delete from Client_Master where the column state holds the value 'Tamil Nadu'.

DELETE FROM CLIENT_MASTER WHERE STATE = 'Tamil Nadu';

6. Exercises on Altering Table Structure

6.1 Add a column called 'Telephone' of data type integer to the Client_Master table.

ALTER TABLE CLIENT_MASTER ADD TELEPHONE INT;

6.2 Change the size of SellPrice column in Product_Master to 10,2.

ALTER TABLE PRODUCT_MASTER MODIFY SELLPRICE DECIMAL(10,2);

7. Exercise on Deleting Table Structure

7.1 Destroy the table Client_Master along with its data.

Warning

This operation will permanently delete the table and all its data. Make sure you have a backup if needed.

DROP TABLE CLIENT_MASTER;

Submission Guidelines

  1. Create all the tables with proper constraints as specified.
  2. Insert the sample data provided.
  3. Execute all the SQL queries for each exercise.
  4. Take screenshots of the output for each query.
  5. Create a PDF document containing:
    • All the SQL commands you executed
    • Screenshots of the outputs
    • Any observations or challenges faced
  6. Submit the PDF file on the learning management system before the deadline.

Note

Make sure to test your queries thoroughly before submission. Pay special attention to the constraints while inserting and updating data.