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
   304   305   306   307   308   309   310   311   312   313   314