Q1 – Write a query to find the total number of rows in a table
A1 – Select count(*) from t_employee;
Q2 – Write a query to eliminate duplicate records in the results of a table
A2 – Select distinct * from t_employee;
Q3 – Write a query to insert a record into a table
A3 – Insert into t_employee values (’empid35′,’Barack’,’Obama’);
Q4 – Write a query to delete a record from a table
A4 – delete from t_employee where id=’empid35′;
Q5 – Write a query to display a row using index
A5 – For this, the indexed column of the table needs to be set as a parameter in the where clause
select * from t_employee where id=’43’;
Q6 – Write a query to fetch the highest record in a table, based on a record, say salary field in the t_salary table
A6 – Select max(salary) from t_salary;
Q7 – Write a query to fetch the first 3 characters of the field designation from the table t_employee
A7 – Select substr(designation,1,3) from t_employee; — Note here that the substr function has been used.
Q8 – Write a query to concatenate two fields, say Designation and Department belonging to a table t_employee
Select Designation + ‘ ‘ + Department from t_employee;
Q9 -What is the difference between UNION and UNION ALL in SQL?
A9 – UNION is an SQL keyword used to merge the results of two or more tables using a Select statement, containing the same fields, with removed duplicate values. UNION ALL does the same, however it persists duplicate values.
Q10 – If there are 4 SQL Select statements joined using Union and Union All, how many times should a Union be used to remove duplicate rows?
A10 – One time.
Q11 – What is the difference between IN and BETWEEN, that are used inside a WHERE clause?
A11 – The BETWEEN clause is used to fetch a range of values, whereas the IN clause fetches data from a list of specified values.
Q12 – Explain the use of the ‘LIKE’ keyword used in the WHERE clause? Explain wildcard characters in SQL.
A12 – LIKE is used for partial string matches. The symbol ‘%’ ( for a string of any character ) and ‘_’ (for any single character ) are the two wild card characters used in SQL.
Q13 – What is the need to use a LIKE statement?
A13 – When a partial search is required in a scencario, where for instance, you need to find all employees with the last name having the sequence of characters “gat”, then you may use the following query, to match a search criteria:
Select empid, firstname, lastname from t_employee where lastname like ‘%gats%’
This might search all employees with last name containing the character sequence ‘gats’ like Gates, Gatsby, Gatsburg, Sogatsky, etc.
% is used to represent remaining all characters in the name. This query fetches all records contains gats in the e middle of the string.
Q14 – Explain the use of the by GROUP BY and the HAVING clause.
A14 – The GROUP BY partitions the selected rows on the distinct values of the column on which the group by has been done. In tandem, the HAVING selects groups which match the criteria specified.
Q15 – In a table t_employee, the department column is nullable. Write a query to fetch employees which are not assigned a department yet.
A11. Select empid, firstname, lastname from t_employee where department is null;
Q16 -What are the large objects supported by oracle and db2? What are the large objects supported in MS SQL?
A16 – In Oracle and DB2 BLOB , CLOB ( Binary Large Objects, Character Large Objects) are used.
In MS SQL – the data types are image and varbinary.
Q17 – Whats the capacity of the image data type in MS SQL?
A17 – Variable-length binary data with a maximum length of 2^31 – 1 (2,147,483,647) bytes.
Q18 – Whats the capacity of varbinary data type in MS SQL?
A18 – Variable-length binary data with a maximum length of 8,000 bytes.
Q19 – What’s the difference between a primary key and a unique key?
A19 – Both Primary key and Unique key enforce the uniqueness of a column on which they are defined. However, a Primary key does not allow nulls, whereas unique key allow nulls.
Q20 – What are the different types of joins in SQL?
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
Q21 – What is a Self join?
A21 – A join created by joining two or more instances of a same table.
Query: Select A.firstname , B.firstname
from t_employee A, t_employee B
where A.supervisor_id = B.employee_id;
Q22 – What is a transaction and ACID?
A22 – Transaction – A transaction is a logical unit of work. All steps must be committed or rolled back.
ACID – Atomicity, Consistency, Isolation and Durability, these are the unique entities of a transaction.