NCERT Exercise Solution Ch 8 – SQL
Introduction to Structured Query Language NCERT Exercise Solution
9. An organization ABC maintains a database EMPDEPENDENT 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:
a) Find the names of employees with their dependent names.
Answer :-
SELECT NAME AS 'EMPLYOEE NAME', DEPENDENTNAME FROM EMPLOYEE E, DEPENDENT D WHERE E.EMPID = D.EMPID;
b) Find employee details working in a department, say, ‘PRODUCTION’.
Answer :-
SELECT * FROM EMPLOYEE WHERE DEPARTMENT = 'PRODUCTION';
c) Find employee names having no dependent.
Answer :-
SELECT NAME AS 'EMPLYOEE NAME' FROM EMPLOYEE E WHERE E.EMPID IN (SELECT D.EMPID FROM DEPENDENT D WHERE DEPENDENTNAME IS NULL);
d) Find names of employees working in a department, say, ‘SALES’ and having exactly two dependents.
Answer :-
SELECT NAME AS 'EMPLYOEE NAME' FROM EMPLOYEE E WHERE E.DEPARTMENT = SALES AND E.EMPID IN (SELECT D.EMPID FROM DEPENDENT D GROUPBY D.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 |
| 1 | Shirt | White |
| 2 | Pant | Grey |
| 3 | Skirt | Grey |
| 4 | Tie | Blue |
| 5 | Socks | Blue |
| 6 | Belt | Blue |
PRICE
| UCode | Size | Price |
| 1 | M | 500 |
| 1 | L | 580 |
| 1 | XL | 620 |
| 2 | M | 810 |
| 2 | L | 890 |
| 2 | XL | 940 |
| 3 | M | 770 |
| 3 | L | 830 |
| 3 | XL | 910 |
| 4 | S | 150 |
| 4 | L | 170 |
| 5 | S | 180 |
| 5 | L | 210 |
| 6 | M | 110 |
| 6 | L | 140 |
| 6 | XL | 160 |
a) 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.
Answer :-
ALTER TABLE Price RENAME TO Cost;
b) M/S Wonderful Garments also keeps handkerchiefs of red color, medium size of `100 each. Insert this record in COST table.
Answer :-
INSERT INTO Cost VALUES(7, 'm', 100);
c) 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.
Answer :-
ALTER TABLE Cost ADD FOREIGN KEY(UCode) REFERENCES UNIFORM(UCode);
d) 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.
Answer :-
ALTER TABLE Uniform MODIFY UName VARCHAR(30) NOT NULL;
e) ALTER table to add the constraint that price of an item is always greater than zero.
Answer :-
ALTER TABLE Cost ADD CONSTRAINT CheckPrice CHECK(Price > 0);
NCERT Book Exercise Solution – Class 11 Informatics Practices
- Class 11 – Informatics Practices – NCERT Exercise Solution
- Class 11 Informatics Practices – Ch 1. Computer System NCERT Solution
- Class 11 Informatics Practices – Ch. 2 Emerging Trends NCERT Solutions
- Class 11 Informatics Practices Chapter 3 Brief Overview of Python NCERT Solution
- Class 11 Informatics Practices Chapter 4 Working with Lists and Dictionaries NCERT Solution
- Class 11 Informatics Practices Chapter 5 Understanding Data NCERT Solution
- Class 11 Informatics Practices Chapter 6 Introduction of NumPy NCERT Exercise Solution
- Class 11 Informatics Practices Chapter 7 Database Concepts NCERT Exercise Solution
- Class 11 Informatics Practices Chapter 8 Introduction to Structured Query Language SQL NCERT Exercise Solution
