A shop called Wonderful Garments which sells school uniforms maintains a database SCHOOLUNIFORM as shown below. It consisted of two relations - UNIFORM and COST. They made UniformCode the primary key for UNIFORM relations. Further, they used UniformCode and Size to be composite keys for COSTrelation. By analyzing the database schema and database state, specify SQL queries to rectify the following anomalies. M/S Wonderful Garments also keeps handkerchiefs of red colour, medium size of Rs. 100 each. INSERT INTO COST (UCode, Size, Price) values (7, 'M', 100);When the above query is used to insert data, the values for the handkerchief without entering its details in the UNIFORM relation are entered. Make a provision so that the data can be entered in the COST table only if it is already there in the UNIFORM table. Further, they should be able to assign a new UCode to an item only if it has a valid UName. Write a query to add appropriate constraints to the SCHOOLUNIFORM database. Add the constraint so that the price of an item is always greater than zero.

INSERT INTO UNIFORM (UCode, UName, Color) VALUES(7, 'HANDKERCHIEF', 'RED'); This query will insert a new row in the UNIFORM table for Red HANDKERCHIEF. ALTER TABLE COST ADD FOREIGN KEY(UCode) REFERENCES UNIFORM(UCode); ALTER TABLE UNIFORM ADD UName VARCHAR (20) NOT NULL; ALTER TABLE COST ADD Cost INT CHECK( COST > 0);

A shop called Wonderful Garments which sells school uniforms INSERT INTO COST (UCode, Size, Price) values (7, 'M', 100); When the above query is used to insert data, the values - Computer Science (Python)

Question

A shop called Wonderful Garments which sells school uniforms maintains a database SCHOOLUNIFORM as shown below. It consisted of two relations - UNIFORM and COST. They made UniformCode the primary key for UNIFORM relations. Further, they used UniformCode and Size to be composite keys for COSTrelation. By analyzing the database schema and database state, specify SQL queries to rectify the following anomalies.

M/S Wonderful Garments also keeps handkerchiefs of red colour, medium size of Rs. 100 each.
INSERT INTO COST (UCode, Size, Price) values (7, 'M', 100);
When the above query is used to insert data, the values for the handkerchief without entering its details in the UNIFORM relation are entered. Make a provision so that the data can be entered in the COST table only if it is already there in the UNIFORM table.
Further, they should be able to assign a new UCode to an item only if it has a valid UName. Write a query to add appropriate constraints to the SCHOOLUNIFORM database.
Add the constraint so that the price of an item is always greater than zero.

One Line Answer

Solution

INSERT INTO UNIFORM (UCode, UName, Color) VALUES(7, 'HANDKERCHIEF', 'RED'); This query will insert a new row in the UNIFORM table for Red HANDKERCHIEF.
ALTER TABLE COST ADD FOREIGN KEY(UCode) REFERENCES UNIFORM(UCode);
ALTER TABLE UNIFORM ADD UName VARCHAR (20) NOT NULL;
ALTER TABLE COST ADD Cost INT CHECK( COST > 0);

shaalaa.com

SQL for Data Definition

Is there an error in this question or solution?

Q 6.Q 5.Q 7.

Chapter 9: Structured Query Language (SQL) - Exercise [Page 179]

APPEARS IN

NCERT Computer Science [English] Class 12

Chapter 9 Structured Query Language (SQL)
Exercise | Q 6. | Page 179

RELATED QUESTIONS

Each table comprises of ______ and ______.

Case Based

Consider the table STUDENT with the following detail.

STU_ID	NAME	STREAM	MARKS	CLASS
1.	Aditya	Science	87.5	12A
2.	Vikram	Commerce	88.7	12B
3.	Astha	Humanities	76.8	12C
4.	Varsha	Science	79.5	12A
5.	Kanishka	Science	77.9	12A
6.	Anand	Commerce	86.7	12B

Now answer the question given below:

Command to select all Science students from the table STUDENT

Case Based

Consider the table STUDENT with the following detail.

STU_ID	NAME	STREAM	MARKS	CLASS
1.	Aditya	Science	87.5	12A
2.	Vikram	Commerce	88.7	12B
3.	Astha	Humanities	76.8	12C
4.	Varsha	Science	79.5	12A
5.	Kanishka	Science	77.9	12A
6.	Anand	Commerce	86.7	12B

Now answer the question given below:

Delete records that belongs to 'Humanities' stream.

Which of the following are included in defining a schema?

To know the names of existing databases, we use the statement:

How will you add two columns coll and col2(composite key) as primary key to a relation?

Which statement is used to remove a table from the database?

Raju's date of birth is 21/06/2006. How will his date be added to database?

Differentiate between the following statement:

ALTER and UPDATE

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 the following considerations:
i) It should have a column TeamID for storing an integer value between 1 to 9, which refers to the 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 the primary key.
Show the structure of the table TEAM using a SQL statement.
As per the preferences of the students four teams were formed as given below. Insert these four rows in the TEAM table:
Row 1: (1, Team Titan)
Row 2: (2, Team Rockers)
Row 3: (3, Team Magnet)
Row 3: (4, Team Hurricane)
Show the contents of the table TEAM using a DML statement.
Now create another table MATCH_DETAILS and insert data as shown below. Choose appropriate data types and constraints for each attribute.

Table: MATCH_DETAILS
MatchID	MatchDate	FirstTeamID	SecondTeamID	FirstTeamScore	SecondTeamScore
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-18	1	4	32	87
M6	2018-07-17	2	3	67	51

Using the CARSHOWROOM database given in the chapter, write the SQL queries for the following:

Add a new column Discount in the INVENTORY table.
Set appropriate discount values for all cars keeping in mind the following:
(i) No discount is available on the LXI model.
(ii) VXI model gives a 10 percent discount.
(iii) A 12 percent discount is given on cars other than the LXI model and VXI model.
Display the name of the costliest car with the fuel type “Petrol”.
Calculate the average discount and total discount available on Baleno cars.
List the total number of cars having no discount.

To establish a connection between Python and SQL databases, connect() is used. Which of the following arguments may not necessarily be given while calling connect()?

Write the command to view all tables in a database.

Write the command to view all databases.

Name any two DDL commands.

The code given below deletes the record from the table employee, which contains the following record structure:

E_code - String
E_name - String
Sal - Integer
City - String

Note the following to establish connectivity between Python and MySQL:

Username is root
Password is root
The table exists in a MySQL database named emp.
The details (E_code, E_name, Sal, City) are the attributes of the table.

Write the following statements to complete the code:

Statement 1 – to import the desired library.

Statement 2 – to execute the command that deletes the record with E_code as 'E101'.

Statement 3 – to delete the record permanently from the database. ____________

import ____________ as mysql #Statement1
def delete():
      mydb=mysql.connect(host="localhost",use r="root", 
      passwd="root",database="emp")

mycursor=mydb.cursor()
____________ #Statement 2
____________ #Statement 3
print ("Record deleted")

Ms Shalini has just created a table named “Employee” containing columns Ename, Department and Salary. After creating the table, she realized that she has forgotten to add a primary key column in the table. Help her in writing an SQL command to add a primary key column EmpId of integer type to the table Employee.

Thereafter, write the command to insert the following record in the table:

EmpId- 999
Ename- Shweta
Department: Production
Salary: 26900

Consider the table Personal given below:

Table: Personal
P_ID	Name	Desig	Salary	Allowance
P01	Rohit	Manager	89000	4800
P02	Kashish	Clerk	NULL	1600
P03	Mahesh	Superviser	48000	NULL
P04	Salil	Clerk	31000	1900
P05	Ravina	Superviser	NULL	2100

Based on the given table, write SQL queries for the following:

Increase the salary by 5% of personals whose allowance is known.
Display Name and Total Salary (sum of Salary and Allowance) of all personals. The column heading ‘Total Salary’ should also be displayed.
Delete the record of Supervisors who have salary greater than 25000.

A shop called Wonderful Garments which sells school uniforms INSERT INTO COST (UCode, Size, Price) values (7, 'M', 100); When the above query is used to insert data, the values - Computer Science (Python)

Advertisements

Advertisements

Question

Solution

APPEARS IN

RELATED QUESTIONS

Select a course

A shop called Wonderful Garments which sells school uniforms INSERT INTO COST (UCode, Size, Price) values (7, 'M', 100); When the above query is used to insert data, the values - Computer Science (Python)

Advertisements

Advertisements

Question

SolutionShow Solution

APPEARS IN

RELATED QUESTIONS

Select a course

Solution