1. What are differences between the DELETE and TRUNCATE statement?
Answer
Delete only deletes a row and is slower than truncate. Truncate deletes all the rows and cannot be rolled back.
2. What are the different subsets of SQL?
Answer
Data Definition Language (Allows you to create and delete objects), Data Manipulation Language (Allows manipulation of objects) and Data Control Language (Allows you to control permissions).
3. What do you mean by DBMS? The different types?
Answer
Database Management System allows a user to interact with a database. The two types are Relational Database Management System (Data is stored in tables MYSQL) and Non-Relational Database Management System (No concept Mongo).
4. What do you mean by table and field in SQL?
Answer
A table is a collection of columns and rows. Where field is just the columns.
5. What are the joins in SQL?
Answer
Inner, Right, Left and Full Join. They are used to combine rows from different tables based on columns.
6. What is the difference between CHAR and VARCHAR2 datatype in SQL?
Answer
VARCHAR2 is variable length in memory where CHAR is fixed at the value you set it.
7. What is a Primary Key?
Answer
A column that is used to uniquely identify a row. Never NULL and Always unique.
8. What are Constraints?
Answer
They are used to make limits on data types. Some are NOT NULL, UNIQUE and DEFAULT.
9. What is the difference between SQL and MYSQL?
Answer
SQL is the language Structured Query Language where MySQL is a database.
10. What is a Unique Key?
Answer
It is used to uniquely identify a row. No NULL values are allowed and two values cannot be the same.
11. What is a Foreign Key?
Answer
It references a primary key in another table.
12. What do you mean by data integrity?
Answer
The accuracy and consistency of data stored in a database.
13. What is the difference between clustered and non clustered index in SQL?
Answer
Clustered is used to sort out rows based on the column that is clustered. It’s faster to retrieve then unclustered columns. There can only be one cluster per table.
14. Write a SQL query to display the current date?
Answer
You use GetDate()
15. What are the different types of joins?
Answer
Inner, Right, Left, and Full Join
16. What do you mean by Denormalization?
Answer
A technique used to optimize a database so that reading from it is faster but writing to it can be slower.
17. What are Entities and Relationships?
Answer
The data a table stores represents entities and the relationships is the links between the entities.
18. What is an Index?
Answer
It allows for fast retrieval of data by creating an entry for each value.
19. Explain the different types of Index.
Answer
Unique – does not allow a field to have more than one of the same value. Clustered is where the index is reordered based on key value. Non-Clustered maintains the order of the logical data.
20. What is Normalization and what are the advantages?
Answer
Normalization is the process of organizing data. Some advantages are speed, efficiency and security.
21. What is the difference between the drop and Truncate commands?
Answer
Drop removes a hole table and cannot be rolled back where truncate just removes all the rows.
22. Explain different types of Normalization.
Answer
1NF, no repeating groups in a row. 2NF, every column in a row depends on a key that depends on the primary key. 3NF every column in a row depends on the primary key.
23. What is ACID property in a database?
Answer
Atomicity, Consistency, Isolation and Durability. Used to make sure data is processed reliably.
24. What do you mean by trigger in SQL?
Answer
Special type of stored procedures that run when an event occurs. Some DML triggers are INSERT, UPDATE or DELETE.
25. What are the different operators available in SQL?
Answer
There are three: Arithmetic, Logical and Comparison.
26. Are null values the same as zero or blank space?
Answer
Zero and Blank Space are characters where NULL means no value can be found.
27. What is the difference between cross join and natural join?
Answer
Natural Join requires that both tables have the same columns. Cross join will create a new column.
28. What is subquery in SQL?
Answer
It’s a query inside another query.
29. What are the different types of a subquery?
Answer
Correlated relies on the values of the other queries it’s nested in to work where Non-Correlated does not need the other values to work.
30. List the ways to get the count of records in a table.
Answer
SELECT * FROM table1;SELECT COUNT(*) FROM table1;
31. Write a SQL query to find the names of employees that begin with ‘A’
Answer
SELECT * FROM aTable WHERE name LIKE 'A%';
32. Write a SQL query to get the third highest salary of an employee from employee_table.
Answer
SELECT TOP 3 salary FROM employee_table;
33. What is the need for group functions in SQL?
Answer
They are mathematical functions to operate on a set of rows to give one result per set.
34. What is a Relationship and what are they?
Answer
Links between entities that have something to do with each other. One to One, One to Many, Many to One and Self-Referencing.
35. How can you insert NULL values in a column while inserting the data?
Answer
Either omit the column or write NULL.
36. What is the main difference between ‘BETWEEN’ and ‘IN’ condition operators?
Answer
BETWEEN is used to check in a range of rows where IN is the specific rows.
37. Why are SQL functions used?
Answer
To perform calculations and modify data.
38. What is the need of MERGE statement?
Answer
It makes changes in one table based on the values of another table.
39. What do you mean by recursive stored procedure?
Answer
It’s stored procedure that calls itself until it reaches a condition.
40. What is the CLAUSE in SQL?
Answer
It limits the results by providing a condition to query.
41. What is the difference bewteen ‘HAVING’ CLAUSE and a ‘WHERE’ CLAUSE?
Answer
The WHERE clause is only used to filter rows where the HAVING clause allows you to filter groups.
42. List ways in which Dynamic SQL can be executed?
Answer
A query with parameters, using EXEC and using sp_executesql.
43. What are the various levels of constraints?
Answer
Column and table level constraints.
44. How can you fetch common records from the two tables?
Answer
Using INTERSECT.
45. List some manipulation functions in SQL.
Answer
LOWER, UPPER, INITCAP.
46. What are the different operators available in SQL?
Answer
Union, Intersect and Minus.
47. What is an ALIAS command?
Answer
A name given to a table or column.
48. What are aggregate and scalar functions?
Answer
49. How can you fetch alternate records from a table?
Answer
50. Name the operator which is used in the query for pattern matching?
Answer
51. How can you select unique records from a table?
Answer
52. How can you fetch the first 5 characters of a string?
Answer
53. What is the main difference between SQL and PL/SQL?
Answer
54. What is a View?
Answer
It’s a virtual table based on the results of an SQL statement.
55. What are Views used for?
Answer
56. What is a stored Procedure?
Answer
57. List some advantages and disadvantages of Stored Procedures?
Answer
58. List all the types of user-defined functions?
Answer
59. What do you mean by Collation?
Answer
60. What are the different types of Collation Sensitivity?
Answer
61. What are Local and Global variables?
Answer
62. What is Auto Increment in SQL?
Answer
63. What is a Datawarehouse?
Answer
64. What are the different authentication modes in SQL Server? How can it be changed?
Answer
65. What are STUFF and REPLACE function?
Answer
Links I used to answer these questions:
Gregory Reed 