TCS Xplore Software Foundation SQL Final Assessment

SQL Final Assessment

Q1. Which SQL statement returns a numeric value?

Answer: SELECT sysdate-hire_date FROM EMP;

Q2. Which of the following is true with respect to Referential integrity ?

Answer: Every foreign key value must match a primary key value in the associated table ie Parent table.

Q3. We can use Transactional control statements in Database Triggers ? Select TRUE or FALSE

Answer: False

Q4. Select the suitable option from below to create an index on "job" column in emp table?

Answer: Create Index emp_id on emp(job);

Q5. Which of the below constraints will make sure to allow, only the values of a column (in a master) to the respective column of a child table?

Answer: foreign key

Q6. Select suitable option for Normalization importance on a Database?

Answer: To avoid redundancy
     Data would not lost
     To fetch the perfect and exact data, required
     Applying changes to the table data without effecting the other attributes

Q7. Which of the following is a type of constraint?

Answer: CHECK
     UNIQUE KEY
     DEFAULT
     PRIMARY KEY

Q8. DCL Commands are used to enforce database security in a multiple user environment.Select TRUE or False

Answer: TRUE

Q9. Which is the below statement is used to remove access or privileges on database object to users?

Answer: Revoke

Q10. Select the suitable option for SubQuery?

Answer: Inner query executes first and then based on the output of inner query the outer query executes

Q11. The formal classifications describing the level of database normalization in a data model are called Normal Forms. Select TRUE or False

Answer: TRUE

Q12. A column with reference to a record in a table should not have multiple values Which of the normal form, Talks about the above statement?

Answer: First

Q13. The DDL commands are

Answer: DROP
      ALTER
      CREATE

Q14. What is the grant statement to grant select, insert delete privileges on table: Emp to user: TestUser? Emp is the table Names; TestUser is the User Name.

Answer: Grant Select insert delete on Emp to TestUser

Q15. Types of SQL statements are

Answer: DML
       TCL
       DDL

Q16. In the below table: EmpId|EmpName|ProjId 1 a p1> 2 b p1 >> 3 c p2 >>> 4 d p1
By referring the above table, Select a suitable query which gets all employees whose project is same as project of 'd'?

Answer: SELECT * FROM EMP WHERE Projid=(SELECT Projid FROM EMP WHERE EMPNAME='d')

Q17. Table structure:
EmpId|EmpName|ProjId|MNGR-Id|Location|Designation
Assume that the respective data values / attributes referred in the options were exists in the table By referring to the above table structure, For which of the below questions, we would require to apply the concept of SubQuery?

Answer: Query to get employee whose designation is same of SWARNA'S designation
       Query to get employee details belonging to dept of RAJA

Q18. From the table below:
EmpId | EmpName | ProjId | Projname | No.ofHoursWorked
What are the probable and decomposed tables after performing second normal form?Let us assume that, an employee can work for multiple projects for the above scenario.

Answer: Table:EmpId | EmpNameTable2:ProjId | ProjNameTable3:EmpId | ProjId | No.of Hours worked

Q19. Table: Emp
EmpId | EmpName | ProjId > 1  a  p1 >> 2  b  p2 >>>3  c  p3
        Table2: Proj
Proj | ProjId | Projname > p  1  AAA >> p  2  BBB
By referring to the above table structure Select the Query which would letch EMPID EMPNAME Projld, ProjName for each employee?

Answer: SELECT EMPID.EMPNAME. B.Projld. B.ProjNAME FROM EMP A,Proj B WHEREA. PROTID=B.PROJID


Q20. From the table below:
EmpId | EmpName | ProjId | Projname | No.ofHoursWorked
Let us assume that, an employee can work for multiple projects for the above scenario. What would be the primary key to get unique record from the table?

Answer: EmpId and projId together