Introduction to Structured Query Language (SQL)

This page contains the NCERT Informatics Practices class 11 chapter 8 Introduction to Structured Query Language (SQL). You can find the solutions for the chapter 8 of NCERT class 11 Informatics Practices Exercise. So is the case if you are looking for NCERT class 11 Informatics Practices related topic Introduction to Structured Query Language (SQL) questions and answers for the Exercise.

Buy Class 11 Informatics Practices Books NOW!

Exercise

1. Match the following clauses with their respective functions.

Insert the values in a table

Restrictions on columns

Table definition

Change the name of a column

Update existing information in a table

Delete an existing row from a table

Create a database

The matching of the given clauses with their respective functions is as follows:

Clause

Match

ALTER

Change the name of a column

UPDATE

Update existing information in a table

DELETE

Delete an existing row from a table

INSERT INTO

Insert the values in a table

CONSTRAINTS

Restrictions on columns

DESC

Table definition

CREATE

Create a database

2. Choose appropriate answer with respect to the following code snippet.

CREATE TABLE student (

name CHAR(30),
student_id INT,
gender CHAR(1),
PRIMARY KEY (student_id)

);

What will be the degree of student table?

ii)

iii)

3 ✔

iv)

What does ‘name’ represent in the above code snippet?

a table

ii)

a row

iii)

a column ✔

iv)

a database

What is true about the following SQL statement?

SelecT * fROM student;

Displays contents of table ‘student’

ii)

Displays column names and contents of table ‘student’ ✔

iii)

Results in error as improper case has been used

iv)

Displays only the column names of table ‘student’

What will be the output of following query?

INSERT INTO student
VALUES (“Suhana”,109,’F’),
VALUES (“Rivaan”,102,’M’),
VALUES (“Atharv”,103,’M’),
VALUES (“Rishika”,105,’F’),
VALUES (“Garvit”,104,’M’),
VALUES (“Shaurya”,109,’M’);

Error ✔

ii)

No Error

iii)

Depends on compiler

iv)

Successful completion of the query

In the following query how many rows will be deleted?

DELETE student
WHERE student_id=109;

1 row ✔

ii)

All the rows where student ID is equal to 109

iii)

No row will be deleted

iv)

2 rows

The following are the answers and the explanations:

a. iii) 3 is the correct answer.

Explanation: Degree of a table refers to the number of columns in a table. As the table student has 3 columns (name, student_id, gender), the degree of this table is 3.

b. iii) a column

Explanation: As ‘name‘ is the name of a column in the student table as per the syntax used for creating a table.

c. ii) Displays column names andd contents of table ‘student‘. Note that the column names will be displayed as header/heading and then content (the matching rows in the table) will be displayed.

Explanation: When we query a table for rows, the contents/rows will be displayed. To indicate which column in the displayed table corresponds to which column in the database table, the column name will be displayed first. This will help us to understand which displayed column belongs to which column in the database table.

d. i) Error.

Explanation: The syntax for inserting multiple records into the table requires us to use the keyword ‘VALUES‘ only once. In addition to this, the student_id which should be unique for each student is used in two rows i.e., row 1 and row 6 are using the same student_id. Note that the sql will first report about the incorrect syntax of using the keyword ‘VALUES‘ multiple times. And then once it is resolved, it’ll result in error due to the duplicate value of the student_id.

The following is the correct syntax of the above query.

INSERT INTO student VALUES ("Suhana", 109, 'F'),
    ("Rivaan", 102, 'M'),
    ("Atharv", 103, 'M'),
    ("Rishika", 105, 'F'),
    ("Garvit", 104, 'M'),
    ("Shaurya", 109, 'M');

e. a) 1 row

Explanation: As the student_id is primary key in the table student, there will be only one row that contains the student_id value as 109. So, only one row will be deleted.

Also note that this explanation is based on the assumption that the student table has a row that has student_id = 109. If there is no such row in the student table, then no row will be deleted. Well the answer here is a bit tricky.

3. Fill in the blanks:

declares that an index in one table is related to that in another table.

Primary Key

ii)

Foreign Key ✔

iii)

Composite Key

iv)

Secondary Key

The symbol Asterisk (*) in a select query retrieves .

All data from the table ✔

ii)

Data of primary key only

iii)

NULL data

iv)

None of the mentioned

4. Consider the following MOVIE database and answer the SQL queries based on it.

MovieID

MovieName

Category

ReleaseDate

ProductionCost

BusinessCost

001

Hindi_Movie

Musical

2018-04-23

124500

130000

002

Tamil_Movie

Action

2016-05-17

112000

118000

003

English_Movie

Horror

2017-08-06

245000

360000

004

Bengali_Movie

Adventure

2017-01-04

72000

100000

005

Telugu_Movie

Action

–

100000

–

006

Punjabi_Movie

Comedy

–

30500

–

Retrieve movies information without mentioning their column names.

List business done by the movies showing only MovieID, MovieName and BusinessCost.

List the different categories of movies.

Find the net profit of each movie showing its ID, Name and Net Profit.

(Hint: Net Profit = BusinessCost – ProductionCost)

Make sure that the new column name is labelled as NetProfit. Is this column now a part of the MOVIE relation. If no, then what name is coined for such columns? What can you say about the profit of a movie which has not yet released? Does your query result show profit as zero?

List all movies with ProductionCost greater than 80,000 and less than 1,25,000 showing ID, Name and ProductionCost.

List all movies which fall in the category of Comedy or Action.

List the movies which have not been released yet.

Note: It is given that the database name is ‘MOVIE‘. Let’s assume that the table name is also ‘MOVIE‘.

a) Retrieve movies information without mentioning their column names:

SELECT * FROM MOVIE;

b) To list the business done by the movies showing only MovieID, MovieName and BusinessCost:

SELECT MovieID, MovieName, BusinessCost
FROM MOVIE;

c) List the different categories of movies:

SELECT DISTINCT Category
FROM MOVIE;

d) To find the net profit of each movie showing its ID, Name and Net Profit

SELECT MovieID, MovieName, (BusinessCost - ProductionCost) AS NetProfit
FROM MOVIE;

Is this column now a part of the MOVIE relation?: No, this new column ‘NetProfit‘ is not part of the movie relation.

If no, then what name is coined for such columns?: Such columns are called as ‘alias names‘.

What can you say about the profit of a movie which has not yet released?: The profit of the movie that is not yet released will be displayed as NULL. (Note that it will not be ‘0’)

Does your query result show profit as zero?: No, the query result doesn’t show profit as zero. It’ll show it as NULL. This is becasue the profit can only be calculated when a movie is released and its business cost is known.

e) To list all movies with ProductionCost greater than 80,00 and less than 1,25,000 showing ID, Name and ProductionCost.

SELECT MovieID, MovieName, ProductionCost
FROM MOVIE
WHERE ProductCost > 80000 AND ProductionCost < 125000

Alternatively the following query can also be used:

SELECT MovieID, MovieName, ProductionCost
FROM MOVIE
WHERE ProductCost BETWEEN 80000 AND 125000

f) To list all movies which fall in the category of Comedy or Action

SELECT *
FROM MOVIE
WHERE Category = 'Comedy' OR Category = 'Action'

Alternatively, the following query can also be used

SELECT *
FROM MOVIE
WHERE Category IN ('Comedy', 'Action')

g) To list the movies which have not been released yet:

SELECT *
FROM MOVIE
WHERE ReleaseDate IS NULL

5. Suppose your school management has decided to conduct cricket matches between students of class XI and Class XII. Students of each class are asked to join any one of the four teams — Team Titan, Team Rockers, Team Magnet and Team Hurricane. During summer vacations, various matches will be conducted between these teams. Help your sports teacher to do the following:

Create a database “Sports”.

Create a table “TEAM” with following considerations:

It should have a column TeamID for storing an integer value between 1 to 9, which refers to unique identification of a team.

ii)

Each TeamID should have its associated name (TeamName), which should be a string of length not less than 10 characters.

Using table level constraint, make TeamID as primary key.

Show the structure of the table TEAM using SQL command.

As per the preferences of the students four teams were formed as given below. Insert these four rows in TEAM table:
Row 1: (1, Team Titan)
Row 2: (2, Team Rockers)
Row 3: (3, Team Magnet)
Row 4: (4, Team Hurricane)

Show the contents of the table TEAM.

Now create another table below. MATCH_DETAILS and insert data as shown in table. Choose appropriate domains and constraints for each attribute.

Table: MATCH_DETAILS

MatchID

MatchDate

FirstTeamID

SecondTeamID

FirstTeamScore

SecondTeamScore

2018-07-17

2018-07-18

2018-07-19

2018-07-20

2018-07-21

Use the foreign key constraint in the MATCH_DETAILS table with reference to TEAM table so that MATCH_DETAILS table records score of teams existing in the TEAM table only.

a) To create a database “Sports”:

CREATE DATABASE Sports;

b) To create a table “TEAM” with the given considerations.

* To create the table ‘TEAM’ in the database ‘Sports’, we should first execute the statement to use the created database. Otherwise, we may get an error that indicates that the database is not selected.

-- Use the following statement before creating the table.
USE Sports;
-- The following SQL statement the answer to part b) of the question
CREATE TABLE Team (
TeamID INT(1),
TeamName VARCHAR(20) NOT NULL
);

Note:

Note that in the above sql statement, we restricted the length of INT to ‘1’. So, it’ll automatatically store the one-digit numbers from 1 to 9 (can not store more than 9).

ii)

As the length of the TeamName column should not be less than 10, we have considered it as equal to 20..

c) To make TeamID as primary key using table level constraint:

ALTER TABLE TEAM
    ADD PRIMARY KEY (TeamID)

d) To Show the structure of the table TEAM using SQL command:

DESCRIBE TEAM;

You can also use

DESC TEAM;

e) To Insert the given four rows in TEAM table:

INSERT INTO TEAM (TeamID, TeamName)
VALUES
(1, 'Team Titan'),
(2, 'Team Rockers'),
(3, 'Team Magnet'),
(4, 'Team Hurricane');

f) To Show the contents of the table TEAM:

SELECT * FROM TEAMS;

g) To create another table MATCH_DETAILS with appropriate domain and constraints for each attribute and insert the given data:

CREATE TABLE MATCH_DETAILS (
    MatchID VARCHAR(5) PRIMARY KEY,
    MatchDate DATE NOT NULL,
    FirstTeamID INT(1) NOT NULL,
    SecondTeamID INT(1) NOT NULL,
    FirstTeamScore INT NOT NULL,
    SecondTeamScore INT NOT NULL
);
INSERT INTO MATCH_DETAILS (MatchID, MatchDate, FirstTeamID, SecondTeamID, FirstTeamScore, SecondTeamScore)
VALUES
('M1', '2018-07-17', 1, 2, 90, 86),
('M2', '2018-07-18', 3, 4, 45, 48),
('M3', '2018-07-19', 1, 3, 78, 56),
('M4', '2018-07-19', 2, 4, 56, 67),
('M5', '2018-07-20', 1, 4, 32, 87),
('M6', '2018-07-21', 2, 3, 67, 51);

h) To use the foreign key constraint in the MATCH_DETAILS table with reference to TEAM table so that MATCH_DETAILS table records score of teams existing in the TEAM table only:

ALTER TABLE MATCH_DETAILS ADD FOREIGN KEY(FirstTeamID) REFERENCES TEAM(TeamID);
ALTER TABLE MATCH_DETAILS ADD FOREIGN KEY(SecondTeamID) REFERENCES TEAM(TeamID);

6. Using the sports database containing two relations (TEAM, MATCH_DETAILS), answer the following relational algebra queries.

Retrieve the MatchID of all those matches where both the teams have scored > 70.

Retrieve the MatchID of all those matches where FirstTeam has scored < 70 but SecondTeam has scored > 70.

Find out the MatchID and date of matches played by Team 1 and won by it.

Find out the MatchID of matches played by Team 2 and not won by it.

In the TEAM relation, change the name of the relation to T_DATA. Also change the attributes TeamID and TeamName to T_ID and T_NAME respectively.

a) To retrieve the MatchID of all those matches where both the teams have scored > 70:

SELECT MatchID
FROM MATCH_DETAILS
WHERE FirstTeamScore > 70 AND SecondTeamScore > 70;

b) To Retrieve the MatchID of all those matches where the FirstTeam has scored < 70 but SecondTeam has scored > 70

SELECT MatchID
FROM MATCH_DETAILS
WHERE FirstTeamScore < 70 AND SecondTeamScore > 70;

c) To find out the MatchID and date of matches played by Team 1 and won by it:

SELECT MatchID, MatchDate
FROM MATCH_DETAILS
WHERE FirstTeamID = 1 AND FirstTeamScore > SecondTeamScore

Note that in the given table the Team 1 is apearing as the FirstTeam. It is also possible that the Team 1 be the SecondTeam. So, ideally, the following query should be used. But for the given case, the above query works fine.

SELECT MatchID, MatchDate
FROM MATCH_DETAILS
WHERE (FirstTeamID = 1 AND FirstTeamScore > SecondTeamScore)
OR (SecondTeamID = 1 AND SecondTeamScore > FirstTeamScore)

d) To find out the MatchID of matches played by Team 2 and not won by it:

SELECT MatchID, MatchDate
FROM MATCH_DETAILS
WHERE (FirstTeamID = 2 AND FirstTeamScore < SecondTeamScore)
OR (SecondTeamID = 2 AND SecondTeamScore < FirstTeamScore)

e) To change the name of the relation to T_DATA and also change the attributes TeamID and TeamName to T_ID and T_NAME respectively:

For renameing the table, we can use either of the following syntax:

RENAME TABLE TEAM TO T_DATA;

ALTER TABLE TEAM RENAME TO T_DATA;

So, from now on, we can not use the name TEAM for referring this table. We should use T_DATA. And then, for changing the column nams, we can use the following syntax:

ALTER TABLE T_DATA RENAME COLUMN TeamID TO T_ID
ALTER TABLE T_DATA RENAME COLUMN TeamName TO T_NAME

7. Differentiate between the following commands:

ALTER and UPDATE

DELETE and DROP

a) Differentiation between ALTER and UPDATE:

Basis

ALTER

UPDATE

Usage

Used to alter the structure of the table.

Used to modify the existing date.

Target

Operates on schema

Operates on data.

Language

Part of the Data Definition Language (DDL)

Part of the Data Manipulation Language (DML)

Result

Will result in an altered table or altered one or more column.

Will result in an update one or more column values in one or more rows in a table.

b) Differentiation between DELETE and DROP:

Basis

DELETE

DROP

Usage

Used to delete one or more rows in a table.

Used to remove an entire database object (Ex: table, index, view etc)

Target

Operates on data

Operates on schema.

Language

Part of the Data Manipulation Language (DML)

Part of the Data Definition Language (DDL)

8. Create a database called STUDENT_PROJECT having the following tables. Choose appropriate data type and apply necessary constraints.

Table: STUDENT

RollNo

Name

Stream

Section

RegistrationID

* The values in Stream column can be either Science, Commerce, or Humanities.

* The values in Section column can be either I or II.

Table: PROJECT_ASSIGNED

RegistrationID

ProjectID

AssignDate

Table: PROJECT

ProjectID

ProjectName

SubmissionDate

TeamSize

GuideTeacher

Populate these tables with appropriate data.

Write SQL queries for the following.

Find the names of students in Science Stream.

What will be the primary keys of the three tables?

What are the foreign keys of the three relations?

Finds names of all the students studying in class ‘Commerce stream’ and are guided by same teacher, even if they are assigned different projects.

The following is the schema to create the STUDENT_PROJECT database with the given tables. The following points can be noted before going through the create statement:

* After creating the database we have to use the ‘USE STUDENT_PROJECT;’ statement so that we’re using the database.

* We have to create the table ‘PROJECT’, before creating ‘PROJECT_ASSIGNED’ so that the ‘ProjectID’ column from the ‘PROJECT’ table can be used as a FOREIGN KEY in the ‘PROJECT_ASSIGNED’ table.

* ‘CHECK‘ keyword used to restrict the values of a column to specified set of values. For instance, the column ‘Stream’ can only take the values ‘Science’, ‘Commerce’ and ‘Humanities’. So, if you try to assign a value (ex: ‘Sports’), it will not allow and result in an error. Similary, the column ‘Section’ can only accept the values ‘I’ and ‘II’. So, if you try to assign a value ‘V’ to the column ‘Section’, it’ll result in an error.

* We have directly added the ‘FOREIGN KEY‘ constraint while creating the table itself. Alternatively you can create the table without the ‘FOREIGN KEY‘ constrants and then add the ‘FOREIGN KEY‘ constraints using the ‘ALTER TABLE‘ command.

* The table ‘PROJECT_ASSIGNED’ has a composite primary key.

CREATE DATABASE STUDENT_PROJECT;

USE STUDENT_PROJECT;

CREATE TABLE STUDENT (
    RollNo INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Stream VARCHAR(20) NOT NULL CHECK (Stream IN ('Science', 'Commerce', 'Humanities')),
    Section VARCHAR(2) NOT NULL CHECK (Section IN ('I', 'II')),
    RegistrationID VARCHAR(10) UNIQUE NOT NULL
);

CREATE TABLE PROJECT (
    ProjectID INT PRIMARY KEY,
    ProjectName VARCHAR(50) NOT NULL,
    SubmissionDate DATE NOT NULL,
    TeamSize INT NOT NULL,
    GuideTeacher VARCHAR(50) NOT NULL
);

CREATE TABLE PROJECT_ASSIGNED (
    RegistrationID VARCHAR(10) NOT NULL,
    ProjectID INT NOT NULL,
    AssignDate DATE,
    PRIMARY KEY (RegistrationID, ProjectID),
    FOREIGN KEY (RegistrationID) REFERENCES STUDENT (RegistrationID),
    FOREIGN KEY (ProjectID) REFERENCES PROJECT (ProjectID)
);

As specified in the note above, the ‘PROJECT_ASSIGNED’ table can be first created and then the forign key constraints can be added using the ‘ALTER TABLE‘ as below:

SQL Statement to create the ‘PROJECT_ASSIGNED’ table:

CREATE TABLE PROJECT_ASSIGNED (
    RegistrationID VARCHAR(10) NOT NULL,
    ProjectID INT NOT NULL,
    AssignedDate DATE NOT NULL,
    PRIMARY KEY (RegistrationID, ProjectID)
);

SQL Statement to create add the ‘FOREIGN KEY‘ constraints to the ‘PROJECT_ASSIGNED’ table:

ALTER TABLE PROJECT_ASSIGNED
    ADD FOREIGN KEY (RegistrationID) REFERENCES STUDENT(RegistrationID),
    ADD FOREIGN KEY (ProjectID) REFERENCES PROJECT(ProjectID);

a) To populate the tables with appropriate data:

-- Insert data into STUDENT table
INSERT INTO STUDENT (RollNo, Name, Stream, Section, RegistrationID)
VALUES
(101, 'Rahul', 'Science', 'I', 'SCI_101'),
(102, 'Priya', 'Commerce', 'II', 'COM_102'),
(103, 'Nikhil', 'Humanities', 'I', 'HUM_103'),
(104, 'Kiran', 'Science', 'II', 'SCI_104'),
(105, 'Amit', 'Commerce', 'I', 'COM_105'),
(106, 'Anjali', 'Science', 'I', 'SCI_106'),
(107, 'Vivek', 'Humanities', 'II', 'HUM_107');

-- Insert data into PROJECT table
INSERT INTO PROJECT (ProjectID, ProjectName, SubmissionDate, TeamSize, GuideTeacher)
VALUES
(1, 'Data Analysis', '2023-06-30', 4, 'Dr. Rao'),
(2, 'Web Development', '2023-08-31', 3, 'Prof. Singh'),
(3, 'Marketing Research', '2023-07-31', 5, 'Dr. Gupta');

-- Insert data into PROJECT_ASSIGNED table
INSERT INTO PROJECT_ASSIGNED (RegistrationID, ProjectID, AssignDate)
VALUES
('SCI_101', 1, '2023-05-01'),
('COM_102', 2, '2023-05-02'),
('HUM_103', 3, '2023-05-01'),
('SCI_104', 1, '2023-05-03'),
('COM_105', 2, '2023-05-02'),
('SCI_106', 1, '2023-05-04'),
('HUM_107', 3, '2023-05-03');

b) To write SQL queries for the following: (This is just instruction and not an actual question)

c) To write SQL query to find the names of students in Science Stream:

SELECT Name 
FROM STUDENT
WHERE Stream = 'Science';

d) The primary keys of the three tables:

The following will be the primary keys in the three tables:

Table Name

Primary Key(s)

STUDENT

RollNo

PROJECT_ASSIGNED

(RegistrationID, ProjectID)

PROJECT

ProjectID

e) The foreign keys of the three relations:

The following are the foreign keys of the three relations:

Table Name

Foreign Key

REFERENCE TABLE

STUDENT

–

PROJECT

–

PROJECT_ASSIGNED

RegistrationID

STUDENT

PROJECT_ASSIGNED

ProjectID

PROJECT

f) To find the names of all the students studying in class ‘Commerce stream’ and are guided by same teacher, even if they are assigned different projects:

* We’re going to build this query assuming that the GuidedTeacherName is given. For example as per the data inserted into this table (refer to answer 7 (a)), let’s assume that the GuidedTeacherName is ‘Prof. Singh‘.

* When the same column is present in multiple table,, SQL expects that the column name should be pre-fixed with the corresponding table name or alias name. For instance, the column name ‘ProjectID‘ is present both in ‘PROJECT_ASSIGNED‘ as well as ‘PROJECT‘ tables. So, when using it we should use ‘PROJECT.ProjectID‘ and ‘PROJECT_ASSIGNED.ProjectID‘ depending on which table’s ‘ProjectID‘ we’re referring to.

SELECT Name 
FROM STUDENT, PROJECT_ASSIGNED, PROJECT
WHERE STUDENT.RegistrationID = PROJECT_ASSIGNED.RegistrationID
    AND PROJECT_ASSIGNED.ProjectID = PROJECT.ProjectID 
    AND Stream = 'Commerce'
    AND GuideTeacher = 'Prof. Singh';

9. An organization ABC maintains a database EMP-DEPENDENT to record the following details about its employees and their dependents.

EMPLOYEE(AadhaarNo, Name, Address, Department, EmpID)
DEPENDENT(EmpID, DependentName, Relationship)

Use the EMP-DEPENDENT database to answer the following SQL queries:

Find the names of employees with their dependent names.

Find employee details working in a department, say, ‘PRODUCTION’.

Find employee names having no dependent

Find names of employees working in a department, say, ‘SALES’ and having exactly two dependents.

a) SQL Query to find the names of employees with their dependent names:

SELECT Name, DependentName
FROM EMPLOYEE, DEPENDENT
WHERE EMPLOYEE.EmpID = DEPENDENT.EmpID;

b) SQL Query to find the employee details working in a department, say, ‘PRODUCTION’:

SELECT *
FROM EMPLOYEE
WHERE DEPARTMENT = 'PRODUCTION'

c) To find employee names having no dependents:

SELECT Name
FROM EMPLOYEE
WHERE EmpID NOT IN (
    SELECT DISTINCT EmpID
    FROM DEPENDENT
);

d) SQL Query to find the names of employees working in a department, say, ‘SALES’ and having exactly two dependents:

* The query can not be achieved without using the ‘GROUP BY‘ clause along with ‘HAVING‘ and ‘count(*)‘ clauses. These are database concepts that you’ll learn in class 12 Informatics Practices.

SELECT Name
FROM EMPLOYEE, DEPENDENT
WHERE EMPLOYEE.EmpID = DEPENDENT.EmpID AND Department = 'SALES'
GROUP BY EMPLOYEE.EmpID
HAVING COUNT(*) = 2;

10. A shop called Wonderful Garments that sells school uniforms maintain a database SCHOOL_UNIFORM as shown below. It consisted of two relations — UNIFORM and PRICE. They made UniformCode as the primary key for UNIFORM relation. Further, they used UniformCode and Size as composite keys for PRICE relation. By analysing the database schema and database state, specify SQL queries to rectify the following anomalies.

UNIFORM

UCode

UName

UColor

Shirt

White

Pant

Grey

Skirt

Grey

Tie

Blue

Socks

Blue

Belt

Blue

PRICE

UCode

Size

Price

500

580

620

810

890

940

770

830

910

150

170

180

210

110

140

160

The PRICE relation has an attribute named Price. In order to avoid confusion, write SQL query to change the name of the relation PRICE to COST.

M/S Wonderful Garments also keeps handkerchiefs of red color, medium size of ₹ 100 each. Insert this record in COST table.

When you used the above query to insert data, you were able to enter the values for handkerchief without entering its details in the UNIFORM relation. Make a provision so that the data can be entered in COST table only if it is already there in UNIFROM table.

Further, you should be able to assign a new UCode to an item only if it has a valid UName. Write a query to add appropriate constraint to the SCHOOL_UNIFORM database.

ALTER table to add the constraint that price of an item is always greater than zero.

a) SQL Query to change the name of the relation PRICE to COST:

ALTER TABLE PRICE RENAME TO COST;

b) SQL Query to insert the medium sized handkerchief record costing ₹ 100

* Let’s assume that the uniform item ‘handkerchief’ will be inserted (not yet inserted) into the ‘UNIFORM’ table with the ‘UCode’ value ‘7’ at a later point of time. So, now we’re using the UCode of ‘handkerchief’ to insert a record into the ‘COST’ table (though it is not yet present in the ‘UNIFORM’ table).

INSERT INTO COST 
VALUES (7, 'M', 100)

c) SQL Query to make a provsion so that the data can be entered in COST table only if it is already there in UNIFORM table:

* You can add the foreign key constraint to the COST table only after deleting the record that we have added in b) above.

* Otherwise, you’ve to insert a record with UCode ‘7’ into the ‘UNIFORM’ table, before attempting to add the ‘FOREIGN KEY‘ constraint to the ‘COST’ table.

ALTER TABLE COST
    ADD FOREIGN KEY (UCode) REFERENCES Uniform(UCode);

d) SQL Query to add appropriate constraint to the SCHOOL_UNIFORM database so that we should be able to assign a new UCode to an item only if it has a valid UName:

ALTER TABLE UNIFORM MODIFY UName VARCHAR(20) NOT NULL

💡 Note that we need to specify the data type information also, even though our aim is to make the UName column as ‘NOT NULL‘

💡 In the question as it is not clear regarding what it meass by a valid name, we just assumed that it should be ‘NOT NULL‘

e) SQL statement to ALTER the table to add constraint that price of an item is always greater than zero:

ALTER TABLE COST
ADD CONSTRAINT Cost_Check CHECK (Price > 0);

💡 Just like the tables/columns have a name, the Constraints can also have a name. Note that we’ve used the name ‘Cost_Check‘ for the constraint above.