Page 309 - Information_Practice_Fliipbook_Class11
P. 309
Ans. a. Inserting Records into table Student
INSERT INTO Student
VALUES(1, 'AMRIT', 'SCIENCE', 'I', 12546);
...
Inserting Records into table Project
INSERT INTO Project
VALUES(231, 'Employee Management', '2021-12-31', 1, 'Mr. Anjeev Singh');
...
...
Inserting Records into Project_Assigned
INSERT INTO Project_Assigned
VALUES (12546, 231, '2021-12-01');
...
...
b. i. SELECT * FROM STUDENT
WHERE Stream = 'Science';
ii. Table Student : RollNo ,
Table Project : ProjectID,
Table Project_Assigned : RegistrationID + ProjectID
iii. Table Student: No Foreign Key,
Table Project: No Foreign Key,
Table Project_Assigned: RegistrationID references with Student table, ProjectID references with Project table.
iv) SELECT NAME FROM Student WHERE Stream = 'Commerce' AND RegistrationID IN(Select
RegistrationID FROM Project_Asssined Where Proejct ID IN (Select PojectID form Project
Where GuideTeacher = 'Anjeev Singh')
22. 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.
b. Find employee details working in a department, say, 'PRODUCTION'.
c. Find employee names having no dependent
d. Find names of employees working in a department, say, 'SALES' and having exactly two dependents.
Ans. a. SELECT NAME AS 'EMPLYOEE NAME', DEPENDENTNAME
FROM EMPLOYEE E, DEPENDENT D
WHERE E.EMPID = D.EMPID;
b. SELECT * FROM EMPLOYEE
WHERE DEPARTMENT = 'PRODUCTION';
c. SELECT NAME AS 'EMPLYOEE NAME' FROM EMPLOYEE E
WHERE E.EMPID IN (SELECT D.EMPID FROM DEPENDENT D
WHERE DEPENDENTNAME IS NULL);
d. 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);
23. 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.
Database Concepts and the Structured Query Language 295

