Page 295 - Information_Practice_Fliipbook_Class11
P. 295

Check whether the following tuples can be added to the PERSON table? Justify your answer.
                  (i)  <799575933699, NULL, "Pooja", "1990-10-01", 9776626565>
                  (ii)  <712349049911, "Singh", "Gagan", "1990-11-11", 9812476543>
              13.  Consider a Food and Beverage department having Dept_No 6 located in Pune. Also, consider the following tuple for an
                  employee with id E0011 who is the manager of this department.
            +-------+--------+-------------+--------+-------------------------+---------------+----------+------------+--------+---------+
            | E0010 | Rashmi | Singhania   | F      | 7, First Floor, MSFC    | Pune          | 411016   | 08-Oct-1990 | 90000 |       4 |
            |       |        |             |        | Building Shivajinagar   |               |          |             |       |         |
            +-------+--------+-------------+--------+-------------------------+---------------+----------+-------------+-------+---------+
                  You need to insert a tuple both for a new employee and a new manager. Now if you attempt to insert a tuple for a new
                  department first, it will be rejected by DBMS since it violates referential integrity constraint as there is no employee with id
                  E0011. Similarly, adding employee entry working in department 6 will also be rejected since there is no department with
                  this number. Propose a solution for this problem that does not violate referential integrity constraints.
              14.  For each  of the following,  point out the SQL statement that will  enable us to perform  the specific  operation  on the
                  database:
                  (i)  To view the list of databases.

                  (ii)  To start using the database named TEST.
                  (iii)  To view the structure of table PRACTICE.
                  (iv)  To display all the records from table PRACTICE.
              15.  Differentiate between ORDER BY and GROUP BY clauses of SQL.
              16.  What are the two wildcard characters used for pattern matching while using the keyword LIKE?
              17.  Consider the table TEACHER given below and answer the questions that follow:

                                                         Table: TEACHER
                     +-------+------------+-----------+-------------------+-------------+--------+------------------------+
                     | ID    | First_Name | Last_Name | Dept              | Contact_Num | Salary | Email_ID               |
                     +-------+------------+-----------+-------------------+-------------+--------+------------------------+
                     | P0001 | Hema       | Priya     | Political Science | 9953799955  |  75000 | hemapriya@gmail.com    |
                     | P0002 | Allu       | Arjun     | English           | 8010235773  |  35000 | arjunallu@gmail.com    |
                     | P0003 | Naishadh   | Kumar     | History           | 9965789799  |  75000 | naishadh.k@yahoo.com   |
                     | P0004 | Sameer     | Reddy     | Computer Science  | 9899417155  |  90000 | sameer@hotmail.com     |
                     | P0005 | Sathvik    | Bal       | Political Science | 9653367876  |  85000 | NULL                   |
                     | P0006 | Tenzin     | Wangdi    | Economics         | 8023456780  |  65000 | tenzin@yahoo.com       |
                     | P0007 | Krishan    | Kumar     | Economics         | 9977885566  |  70000 | krishnakumar@gmail.com |
                     +-------+------------+-----------+-------------------+-------------+--------+------------------------+
                  (i)  Write a CREATE TABLE statement for table TEACHER.
                  (ii)  Write a SELECT statement for each of the following:
                     a.  List the salary of those teachers whose name start with 'S'.
                     b.  List the first name and last name of the teachers who have salary more than 70000.
                     c.  List the count of number of teachers of each department.
                     d.  List the first name and contact number of teachers whose mail id is not known.
                     e.  Display the rows from the table TEACHER in descending order of salary.
                     f.  Add an attribute Subject to the table TEACHER.
                     g.  Drop attribute Email_ID from the table TEACHER.
                  (iii)  For each of the following SQL queries, write the output that will be produced on its execution:
                     a. SELECT AVG(Salary) FROM TEACHER
                       WHERE Dept='Economics';
                     b. SELECT First_Name, Last_Name,Contact_Num
                       FROM TEACHER
                       WHERE Salary BETWEEN 40000 AND 80000;
                     c. SELECT DISTINCT Dept FROM TEACHER;
                     d. SELECT MAX(Salary) FROM TEACHER
                       GROUP BY Dept
                       HAVING Dept='Political Science';

                                                                Database Concepts and the Structured Query Language  281
   290   291   292   293   294   295   296   297   298   299   300