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
